Java连接数据库时,需要构建连接,执行操作,最后释放连接。但是如果在执行操作上非常简便,那么构建连接和释放连接非常的损耗性能
- Connection是Java和数据库俩个平行系统的桥梁
- 桥梁构建不易,成本很高,单次使用成本贵昂贵
- 运用共享技术来实现数据库连接池(享元模式)
– 降低系统中数据库连接Connection对象数量
– 降低数据库服务器的连接响应消耗
– 提高Connection获取的响应速度
享元模式
- 经典23个设计模式的一种,属于结构型模式
- 一个系统存在大量相同的对象(由于这类对象的大量使用,会造成系统内存的耗费,可以使用享元模式来减少系统中对象的数量)
数据库连接池
- 池Pool的概念
– 初始量(一开始连接池中的数量),最大数(最大的数量),增量(增加连接的数量),超时时间(超过多少时间空闲,释放掉连接)等参数 - 常用的数据库连接池
– DBCP
– C3P0
– Druid
数据库连接池不能代替sql连接,而是管理着这些连接
public class SelectTest {
public static void main(String[] args){
Connection conn = null;
try {
//四种办法获取连接
//从c3p0获取
//conn = C3p0Factory1.getConnection();
//conn = C3p0Factory2.getConnection();
//从Druid获取
//conn = DruidFactory1.getConnection();
conn = DruidFactory2.getConnection();
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功!");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//开始遍历ResultSet数据
while(rs.next())
{
System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
}
rs.close();
stmt.close();
} catch (Exception e){
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
} catch (SQLException e){
e.printStackTrace();
}
}
}
}
public class C3p0Factory1 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass( "com.mysql.jdbc.Driver" );
dataSource.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
dataSource.setUser("root");
dataSource.setPassword("123456");
// the settings below are optional -- c3p0 can work with defaults
dataSource.setMinPoolSize(5);
dataSource.setAcquireIncrement(5);
dataSource.setMaxPoolSize(20);
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
public class C3p0Factory2 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
//dataSource 自动加载c3p0-config.xml文件
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
c3p0-config.xml文件
<?xml version="1.0" encoding="UTF-8"?>
-<c3p0-config>
-<default-config>
<!-- 默认配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
public class DruidFactory1 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
dataSource.setInitialSize(5);
dataSource.setMinIdle(1);
dataSource.setMaxActive(10);
// 启用监控统计功能 dataSource.setFilters("stat");//
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
public class DruidFactory2 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
Properties properties = new Properties();
InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(in);
dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
in.close();
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}