工具类-JDBC连接

最新推荐文章于 2022-11-08 17:07:24 发布

最新推荐文章于 2022-11-08 17:07:24 发布 · 109 阅读

文章标签：

#JDBC #SQL #Java #多线程

javaSE 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一个简单的数据库连接池管理类DBPool，该类用于统一管理数据库连接的创建、使用及关闭等操作，通过单例模式确保资源的有效利用。文章详细展示了如何通过读取配置文件设置数据库驱动、连接URL、用户名及密码，并提供了执行查询、更新和特殊SQL语句的方法。

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

/**
 * DBPool.java
 * utils class
 * @author fanfq
 * @version 1.0  2009-6-4
 * */
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 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");
			
			Class.forName(sDBDriver).newInstance();
			return DriverManager.getConnection(sConnection,pr);
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	/**关闭数据库连接*/
	public static synchronized void closeConnection(){
		try{
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = 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 {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			return rs;
		}
	}
	
	/**执行特殊SQL语句专用*/
	public static void exeSQL(String sql){
		if(null == stmt){
			getStatement();
		}
		try {
			stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**执行update，inster，delete语句专用*/
	@SuppressWarnings("finally")
	public static int exeUpdate(String sql){
		if(null == stmt){
			getStatement();
		}
		try {
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			return count;
		}
	}
	

	//测试	
//	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