jdbc 工具类 v2.0

这次有更新，主要是增加了prepareStatement方法，这种方法的优势1，可防止SQL注入。2，效率高。

 

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * DBPool.java
 * utils class
 * @author fanfq
 * @version 2.0  2009-6-18
 * */
public class DBPool {
	
	private static DBPool instance = null;
	private static Connection conn = null;
	private static Statement stmt = null;
	private static ResultSet rs = null;
	private static int count = 0;
	private static PreparedStatement pstmt;
	
	
	/**私有的构造方法*/
	private DBPool(){
		super();
	}
	
	/**形成同步保证多线程访问只有一个实例*/
	private static synchronized Connection getConnection() {
		if(instance == null){
			instance = new DBPool();
		}
		return instance._getConnection();
	}
	
	/**私有的数据源配置*/
	private Connection _getConnection(){
		try{
			String sDBDriver = null;
			String sConnection = null;
			String sUser  = null;
			String sPassword = null;
			
			Properties p = new Properties();
			InputStream is = getClass().getResourceAsStream("/db.properties");
			p.load(is);
			
			sDBDriver = p.getProperty("DBDriver", sDBDriver);
			sConnection = p.getProperty("Connection",sConnection);
			sUser = p.getProperty("User",sUser);
			sPassword = p.getProperty("Password",sPassword);
			
			Properties pr = new Properties();
			pr.put("user", sUser);
			pr.put("password", sPassword);
			pr.put("characterEncoding", "GB2312");
			pr.put("useUnicode", "TRUE");
			
			SysLog.loger("connection db....");
			
			Class.forName(sDBDriver).newInstance();
			return DriverManager.getConnection(sConnection,pr);
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	/**关闭数据库连接*/
	public static synchronized void closeConnection(){
		
		SysLog.loger("connection is closed....");
		try{
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}catch(SQLException exp){
			exp.printStackTrace();
		}
	}
	
	/**获得Statement*/
	@SuppressWarnings("finally")
	private static Statement getStatement(){
		if(null == conn){
			conn = getConnection();
		}
		try {
			stmt = conn.createStatement();			
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			return stmt;
		}
	}	

	/**执行常用查询语句专用*/
	@SuppressWarnings("finally")
	public static ResultSet exeQuery(String sql){
		
		if(null == stmt){
			getStatement();
		}
		try {
			SysLog.loger(sql);
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			return rs;
		}
	}
	
	/**执行特殊SQL语句专用*/
	public static void exeSQL(String sql) throws Exception{
		if(null == stmt){
			getStatement();
		}
		try {
			SysLog.loger(sql);
			stmt.execute(sql);
		} catch (SQLException e) {			
			e.printStackTrace();
		}
	}
	
	/**Statement执行update，inster，delete语句专用*/
	@SuppressWarnings("finally")
	public static int exeUpdate(String sql){
		if(null == stmt){
			getStatement();
		}
		try {
			SysLog.loger(sql);
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			return count;
		}
	}
	
	/**prepareStatement执行update，inster，delete语句专用*/
	@SuppressWarnings("finally")
	public static int exePstmtUpdate(String sql,Object[] params){
		if(sql!=null&&!sql.equals("")){
			if(params==null)
				params=new Object[0];			
		
			if(null == conn){
				conn = getConnection();
			}
			try{		
				SysLog.loger(sql);
				pstmt=conn.prepareStatement(sql);
				for(int i=0;i<params.length;i++){
					pstmt.setObject(i+1,params[i]);
				}
				count = pstmt.executeUpdate();
			}catch(SQLException e){
				SysLog.loger("exePstmtUpdate()方法出错！");				
				e.printStackTrace();
				
			}finally{
				return count;
			}		
		}
		return 0;
	}
	
	
	/**prepareStatement执行Query语句专用*/
	@SuppressWarnings("finally")
	public static ResultSet exePstmtQuery(String sql,Object[] params){
		if(sql!=null&&!sql.equals("")){
			if(params==null)
				params=new Object[0];			
		
			if(null == conn){
				conn = getConnection();
			}
			try{		
				SysLog.loger(sql);
				pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				for(int i=0;i<params.length;i++){
					pstmt.setObject(i+1,params[i]);
				}
				rs = pstmt.executeQuery();
			}catch(SQLException e){
				SysLog.loger("exePstmtQuery()方法出错！");
				e.printStackTrace();
				
			}finally{
				return rs;
			}		
		}
		return null;
	}

	//测试	
//	public static void main(String[] args){
//		
//		String sql = "SELECT * FROM dept";
//		ResultSet rss = exeQuery(sql);
//		try {
//			while(rss.next()){
//					System.out.println(rss.getInt(1) + " " + rss.getString(2)+" "+ rss.getString(3) + " " + rss.getInt(4) );
//				
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		closeConnection();
//		
//	}

}

 

 

#db.properties   
#author fanfq    
DBDriver=jdbcDirver  
Connection=URL  
User=userName  
Password=password  

 

评论

3 楼 xiaoban0514 2009-10-10  

恩，你说的有道理这里确实是疏忽了。

2 楼 wgshengli 2009-09-20  

//  public static void main(String[] args){  
//        
//      String sql = "SELECT * FROM dept";  
//      ResultSet rss = exeQuery(sql);  
//      try {  
//          while(rss.next()){  
//                  System.out.println(rss.getInt(1) + " " + rss.getString(2)+" "+ rss.getString(3) + " " + rss.getInt(4) );  
//                
//          }  
//      } catch (SQLException e) {  
//          e.printStackTrace();  
//      }  
//      closeConnection();  
//        
//  }  

比如比较实时的使用数据库。
当执行了1-2中间时如果有人执行了closeConnection()那么rss得到的是一个null.就会报异常。
1.      ResultSet rss = exeQuery(sql);  
2.          while(rss.next()){  

1 楼 wgshengli 2009-09-20  

不知道博主用没有用过这个类，这个类有问题。