【JavaWeb】2.封装jdbc工具类

最新推荐文章于 2022-05-12 21:24:46 发布

原创最新推荐文章于 2022-05-12 21:24:46 发布 · 339 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #database

JavaWeb教程专栏收录该内容

18 篇文章

订阅专栏

本文探讨了两种不同的JDBC连接管理方式：一是使用ThreadLocal维护每个线程的数据库连接，以确保事务的正确性；二是引入阿里巴巴的Druid数据库连接池，通过配置初始化大小、最大活跃连接数等参数来优化数据库资源的使用。这两种方法都旨在提高应用的性能和并发能力。

初始版本

public final class JDBCUtil{
    //定义数据库连接相关参数
    private static String driver = "";
    private static String url = "";
    private static String user = "";
    private static String password = "";
    
    //私有化构造方法
    private JDBCUtil(){}
    
    //加载配置文件
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        user = bundle.getString("user");
        password = bundle.getString("password");
        
        //注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    //获取数据库连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    
    //关闭资源
    public static void closeAll(Connection con, Statement stmt, ResultSet rs){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace():
            }
        }
        
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

为了保证每个能够开启以及回滚事务，需要改进的JDBCUtil

public final class JDBCUtil {
    private static String driver = "";
    private static String url = "";
    private static String user = "";
    private static String password = "";
    
    //创建ThreadLocal类的对象，用于维护每个线程的数据库连接
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    //保证了每个线程只会有同一个Connection连接对象，如果使用单例的话，所有用户都必须要等待。
    
    //加载配置文件
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        user = bundle.getString("user");
        password = bundle.getString("password");
        
        //注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    //获取数据库连接
    public static Connection getConnection() {
        if (threadLocal.get() == null) {
            try {
                threadLocal.set(DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return threadLocal.get();
    }
    
    //关闭资源
    public static void closeAll(Connection con, Statement stmt, ResultSet rs){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace():
            }
        }
        
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (con != null) {
            try {
                con.close();
                threadLocal.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

以上两种方式的配置文件
jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jgs1904?useSSL=true&serverTimezone=GMT%2B8
user=root
password=root

使用alibaba的druid数据库连接池

public final class DataSourceUtil {

	private DataSourceUtil() {};
	
	private static Properties pro = new Properties();
	private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
	private static DataSource dataSource = null;
	
	static {
		// 1.将配置文件加载到properties集合内 
		InputStream is = Demo02.class.getClassLoader().getResourceAsStream("druid.properties");
		try {
			pro.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		// 2.使用properties集合内的配置参数初始化一个连接池
		try {
			dataSource = DruidDataSourceFactory.createDataSource(pro);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	// 获取数据库连接池
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	// 获取数据库连接
	public static Connection getConnection() {
		if (threadLocal.get() == null) {
			try {
				threadLocal.set(dataSource.getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return threadLocal.get();
	}
	
	// 关闭资源
	public static void closeAll(Connection con, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (con != null) {
			try {
				con.close();
				// 销毁threadLocal中的连接对象
				threadLocal.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
}

配置文件 druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jgs1904?useSSL=true&serverTimezone=GMT%2B8
username=root
password=root

#初始化时池中建立的连接数。
initialSize=2
#最大的可活跃的连接池数量
maxActive=300
#最大等待时间
maxWait=60000
#是否缓存preparedStatement
poolPreparedStatements=true
#池中能够缓冲的preparedStatement语句数量
maxPoolPreparedStatementPerConnectionSize=200