DBCP连接池
DBCP数据源
DBCP 是 Apache 软件基金组织下的开源连接池实现,要使用DBCP数据源,需要应用程序应在系统中增加如下两个 jar 文件:
- Commons-dbcp.jar:连接池的实现
- Commons-pool.jar:连接池实现的依赖库
Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用,也可由应用程序独立使用。
在应用程序中加入dbcp连接池
1.导入相关jar包
commons-dbcp-1.2.2.jar、commons-pool.jar
2、在类目录下加入dbcp的配置文件:dbcpconfig.properties
dbcpconfig.properties的配置信息如下:
#连接设置 driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbc username=root password=XDP #初始化连接 initialSize=10 #最大连接数量 maxActive=200 #最大空闲连接 maxIdle=20 #最小空闲连接 minIdle=5 #超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;] #注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交(auto-commit)状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读(read-only)状态。 #如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix) defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。 #可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
package com.adapter; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.util.Arrays; import java.util.HashMap; import java.util.Map; import java.util.Properties; import org.apache.log4j.Logger; public class ReadProperties { private static Logger logger = Logger.getLogger(ReadProperties.class); private static Map<String, String> map = new HashMap<String, String>(); private static String username; private static String url; private static String password; private static String initialSize; private static String maxActive; private static String maxIdle; private static String minIdle; private static String maxWait; static{ // String filePath = System.getProperty("user.dir") + "/setting.properties"; InputStream in = ReadProperties.class.getResourceAsStream("/dbcpconfig.properties"); Properties p = new Properties(); try { // InputStream in = new BufferedInputStream(new FileInputStream(filePath)); p.load(new InputStreamReader(in, "utf-8")); } catch (IOException e) { logger.error("ReadProperties读取失败", e); } username = p.getProperty("jdbc.username"); url = p.getProperty("jdbc.url"); password = p.getProperty("jdbc.password"); initialSize = p.getProperty("jdbc.initialSize"); maxActive = p.getProperty("jdbc.maxActive"); maxIdle = p.getProperty("jdbc.maxIdle"); minIdle = p.getProperty("jdbc.minIdle"); maxWait = p.getProperty("jdbc.maxWait"); } public static Map<String, String> getSettingInfo() { map.put("username", username); map.put("url", url); map.put("password", password); map.put("initialSize", initialSize); map.put("maxActive", maxActive); map.put("maxIdle", maxIdle); map.put("minIdle", minIdle); map.put("maxWait", maxWait); return map; } }
1 package com.util; 2 3 import java.sql.Connection; 4 import java.sql.ResultSet; 5 import java.sql.SQLException; 6 import java.sql.Statement; 7 import java.util.Map; 8 import java.util.Properties; 9 import javax.sql.DataSource; 10 import org.apache.commons.dbcp.BasicDataSourceFactory; 11 import com.szht.gpy.adapter.ReadProperties; 12 13 public class DBCPUtil { 14 private static DataSource dSource = null; 15 static{ 16 Map<String, String> map = ReadProperties.getSettingInfo(); 17 Properties prop = new Properties(); 18 try { 19 prop.setProperty("driverClassName", "oracle.jdbc.driver.OracleDriver"); 20 prop.setProperty("url", map.get("url")); 21 prop.setProperty("password", map.get("password")); 22 prop.setProperty("username", map.get("username")); 23 prop.setProperty("maxActive", map.get("maxActive")); 24 prop.setProperty("maxIdle", map.get("maxIdle")); 25 prop.setProperty("maxWait", map.get("maxWait")); 26 prop.setProperty("initialSize", map.get("initialSize")); 27 prop.setProperty("minIdle", map.get("minIdle")); 28 dSource = BasicDataSourceFactory.createDataSource(prop); 29 System.out.println(dSource); 30 } catch (Exception e) { 31 e.printStackTrace(); 32 throw new ExceptionInInitializerError(e); 33 } 34 } 35 36 public static Connection getConnection(){ 37 Connection connection = null; 38 try { 39 connection = dSource.getConnection(); 40 } catch (SQLException e) { 41 e.printStackTrace(); 42 } 43 return connection; 44 } 45 46 public static void release(Connection conn,Statement st,ResultSet rs) { 47 if (rs != null) { 48 try { 49 //关闭存储查询结果的ResultSet对象 50 rs.close(); 51 } catch (Exception e) { 52 e.printStackTrace(); 53 } 54 rs = null; 55 } 56 if (st != null) { 57 try { 58 //关闭负责执行SQL命令的Statement对象 59 st.close(); 60 } catch (Exception e) { 61 e.printStackTrace(); 62 } 63 } 64 if (conn != null) { 65 try { 66 //将Connection连接对象还给数据库连接池 67 conn.close(); 68 } catch (Exception e) { 69 e.printStackTrace(); 70 } 71 } 72 } 73 74 } 75
package com.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import org.junit.Test; import me.gacl.util.JdbcUtils_DBCP; public static void main(String[] args) { public void dbcpDataSourceTest() { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ //获取数据库连接 conn = DBCPUtil.getConnection(); String sql = "insert into test1(name) values(?)"; st = conn.prepareStatement(sql); st.setString(1, "gacl"); st.executeUpdate(); //获取数据库自动生成的主键 rs = st.getGeneratedKeys(); if(rs.next()){ System.out.println(rs.getInt(1)); } }catch (Exception e) { e.printStackTrace(); }finally{ //释放资源 DBCPUtil.release(conn, st, rs); } } }