Detailed introduction to C3P0 connection pool
- 零下一度Original
- 2017-07-27 15:48:262142browse
C3P0 connection pool
Tool class for creating C3P0 connection pool
* Connection specification interface:
* javax.sql.DataSource interface
* Implementation class object of the interface
* ComboPooledDataSource
* Create a ComboPooledDataSource object at the member position
* Use a static code block to set 4 large amounts of data connection information for ComboPooledDataSource
* Create a static method to return Connection Object
* Create a static method to release resources
1 public class C3P0Utils { 2 //成员位置创建ComboPooledDataSource对象 3 private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); 4 5 //使用静态代码块给ComboPooledDataSource设置4大数据量连接信息 6 static{ 7 try { 8 dataSource.setDriverClass("com.mysql.jdbc.Driver"); 9 dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mybase4");10 dataSource.setUser("root");11 dataSource.setPassword("root");12 } catch (Exception e) {13 throw new RuntimeException("设置连接信息失败!");14 }15 }16 17 //创建一个静态方法返回Connection对象18 public static Connection getConnection(){19 try {20 return dataSource.getConnection();21 } catch (SQLException e) {22 throw new RuntimeException("获取数据库连接信息失败!");23 }24 }25 26 //定义一个释放资源的方法27 public static void close(ResultSet rs,Statement stat,Connection conn){28 if(rs!=null){29 try {30 rs.close();31 } catch (SQLException e) {32 e.printStackTrace();33 }34 }35 if(stat !=null){36 try {37 stat.close();38 } catch (SQLException e) {39 e.printStackTrace();40 }41 }42 if(conn != null){43 try {44 conn.close();45 } catch (SQLException e) {46 e.printStackTrace();47 }48 }49 }50 }Create a C3P0 tool class that reads XML configuration files
1 public class C3P0UtilsReadXML { 2 //成员位置创建ComboPooledDataSource对象 3 private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); 4 5 //使用静态代码块给ComboPooledDataSource设置4大数据量连接信息 6 /*static{ 7 try { 8 dataSource.setDriverClass("com.mysql.jdbc.Driver"); 9 dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mybase4");10 dataSource.setUser("root");11 dataSource.setPassword("root");12 } catch (Exception e) {13 throw new RuntimeException("设置连接信息失败!");14 }15 }*/16 17 //创建一个返回ComboPooledDataSource的方法18 public static DataSource getDataSource(){19 return dataSource;20 }21 22 //创建一个静态方法返回Connection对象23 public static Connection getConnection(){24 try {25 return dataSource.getConnection();26 } catch (SQLException e) {27 throw new RuntimeException("获取数据库连接信息失败!");28 }29 }30 31 //定义一个释放资源的方法32 public static void close(ResultSet rs,Statement stat,Connection conn){33 if(rs!=null){34 try {35 rs.close();36 } catch (SQLException e) {37 e.printStackTrace();38 }39 }40 if(stat !=null){41 try {42 stat.close();43 } catch (SQLException e) {44 e.printStackTrace();45 }46 }47 if(conn != null){48 try {49 conn.close();50 } catch (SQLException e) {51 e.printStackTrace();52 }53 }54 }55 }DBCP connection pool
Tool class for creating DBCP connection pool
* Connection specification interface:
* javax.sql.DataSource interface
*Interface implementation class object
* BasicDataSource
* Override the getConnection method
1 public class DBCPUtils { 2 //创建连接池的实现类对象 3 private static BasicDataSource dataSource = new BasicDataSource(); 4 5 //设置连接数据库的4大变量,使用BasicDataSource中的set方法设置 6 static{ 7 //设置注册的驱动信息 8 dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 9 //设置Url10 dataSource.setUrl("jdbc:mysql://localhost:3306/mybase4");11 //设置用户名12 dataSource.setUsername("root");13 //设置密码14 dataSource.setPassword("root");15 //可选信息16 //dataSource.setInitialSize(100);17 //dataSource.setMaxActive(1000);18 }19 20 //创建获取数据库连接对象的方法21 public static Connection getConnection(){22 try {23 return dataSource.getConnection();24 } catch (SQLException e) {25 throw new RuntimeException("获取数据库连接对象失败");26 }27 }28 29 //定义一个释放资源的方法30 public static void close(ResultSet rs,Statement stat,Connection conn){31 if(rs!=null){32 try {33 rs.close();34 } catch (SQLException e) {35 e.printStackTrace();36 }37 }38 if(stat !=null){39 try {40 stat.close();41 } catch (SQLException e) {42 e.printStackTrace();43 }44 }45 if(conn != null){46 try {47 conn.close();48 } catch (SQLException e) {49 e.printStackTrace();50 }51 }52 }53 }How to read the configuration file
1 /* 2 * 创建DBCP连接池工具类 3 * 使用读取配置文件的方式 4 * DBCP中有一个工厂类BasicDataSourceFactory 5 * 工厂类中有一个静态方法 6 * static DataSource createDataSource(Properties prop) 7 * 此方法返回的就是DataSource接口的实现类对象BasicDataSource 8 *
9 * 创建Propertis集合+IO技术读取配置文件10 * 把配置文件传入BasicDataSourceFactory静态方法createDataSource中11 */12 public class DBCPUtilsReadConfig {13 //创建DataSource变量14 private static DataSource dataSource;15 16 static{17 try {18 //创建Propertis集合+IO技术读取配置文件19 //使用类加载器,扫描包下的文件20 InputStream is = DBCPUtilsReadConfig.class.getClassLoader()21 .getResourceAsStream("dbcpconfig.properties");22 //创建Propertis集合23 Properties prop = new Properties();24 prop.load(is);25 //把配置文件传入BasicDataSourceFactory静态方法createDataSource中26 dataSource = BasicDataSourceFactory.createDataSource(prop);27 } catch (Exception e) {28 throw new RuntimeException("读取配置文件失败");29 }30 }31 32 //创建获取数据库连接对象的方法33 public static Connection getConnection(){34 try {35 return dataSource.getConnection();36 } catch (SQLException e) {37 throw new RuntimeException("获取数据库连接对象失败");38 }39 }40 41 //定义一个释放资源的方法42 public static void close(ResultSet rs,Statement stat,Connection conn){43 if(rs!=null){44 try {45 rs.close();46 } catch (SQLException e) {47 e.printStackTrace();48 }49 }50 if(stat !=null){51 try {52 stat.close();53 } catch (SQLException e) {54 e.printStackTrace();55 }56 }57 if(conn != null){58 try {59 conn.close();60 } catch (SQLException e) {61 e.printStackTrace();62 }63 }64 }65 }
* Use the DBUtils tool class Add, delete, modify and query database tables
* Function of DBUtils tool class: Simplify JDBC development (6 steps)
*
* DBUtils has 3 core classes
* 1.QueryRunner: Use QueryRunner Methods to add, delete, modify and query the database
* 2.DbUtils: Provides a large number of methods to release resources
* 3.ResultSetHandler interface: Provides methods to process query result sets
*
* QueryRunner class
* Construction method:
* QueryRunner() empty parameter construction method
* When calling the update and query methods, you need to pass in the Connection object
* QueryRunner(DataSource ds) with DataSource database connection pool Construction method
* When calling update and query methods, QueryRunner will automatically obtain the database connection object from the DataSource implementation class object, and will automatically return it after use
* Member methods:
* Execute member methods that perform additions, deletions, and modifications
* int update(Connection conn, String sql, Object... params) SQL used to perform additions, deletions and modifications
* int update(String sql, Object... params) SQL used to perform additions, deletions and modifications
* Parameters:
* Connection conn: database connection object
* String sql: spliced sql statement, you can use ? placeholder
* Object... params: ? actual parameters of placeholder , you can use Object[]
* Return value:
* int: the number of valid rows executed
* Member method to execute the query
*
*
* Parameters:
* Connection conn: database connection object
* String sql: spliced sql statement, you can use ? placeholder
* Object... params: ? actual parameters of placeholder, you can use Object[]
* ResultSetHandler
* Return value:
*
1 public class Demo01DBUtils { 2 //创建QueryRunner对象,构造方法中传入连接池的实现类对象 3 QueryRunner qr = new QueryRunner(C3P0UtilsReadXML.getDataSource()); 4 5 /* 6 * 使用QueryRunner,对数据库表进行删除数据 7 */ 8 @Test 9 public void delete(){10 try {11 //拼接增加的sql语句12 String sql = "DELETE FROM category WHERE cid=?";13 //调用QueryRunner中update方法执行sql语句14 //创建对象数据,储存?占位符的实际参数15 //Object[] parmars = {"洗",10};16 int row = qr.update(sql, 7);17 System.out.println(row);18 } catch (Exception e) {19 System.out.println(e);20 }21 }22 23 /*24 * 使用QueryRunner,对数据库表进行修改数据25 */26 @Test27 public void update(){28 try {29 //拼接增加的sql语句30 String sql = "UPDATE category SET cname=? WHERE cid=?";31 //调用QueryRunner中update方法执行sql语句32 //创建对象数据,储存?占位符的实际参数33 Object[] parmars = {"洗",10};34 int row = qr.update(sql, parmars);35 System.out.println(row);36 } catch (Exception e) {37 System.out.println(e);38 }39 }40 41 /*42 * 使用QueryRunner,对数据库表进行增加(插入)数据43 */44 @Test45 public void insert(){46 try {47 //拼接增加的sql语句48 String sql = "INSERT INTO category(cname) VALUES(?)";49 //调用QueryRunner中update方法执行sql语句50 int row = qr.update(sql, "玩具");51 System.out.println(row);52 } catch (Exception e) {53 System.out.println(e);54 }55 }56 }Nine types of queries using QueryRunner to query database tables Way
1 /* 2 * 使用QueryRunner对数据库表进行查询 3 * <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
4 */ 5 public class Demo02DBUtils { 6 //创建QueryRunner对象,构造方法中传入数据库连接池的实现类 7 QueryRunner qr = new QueryRunner(C3P0UtilsReadXML.getDataSource()); 8 9 /* 10 * 第九种查询方式:使用KeyedHandler(Map嵌套Map) 11 * KeyedHandler会把结果集的每一行数据封装到Map集合中 12 * key:字符串类型列名 13 * value:列名对应的值(不同的列,数据类型不同,value使用Object类型) 14 * 多个Map集合存储到另外一个Map集合中 15 * key:指定的字符串列名,不指定则默认使用第一列 16 * value:传入存储每行数据的Map集合 17 *
18 * 构造方法: 19 * KeyedHandler() 不指定列名 20 * KeyedHandler(int columnIndex) 指定列号1,2,3,4 21 * KeyedHandler(String columnName) 指定字符串格式列名 22 */ 23 @Test 24 public void keyedHandler(){ 25 try { 26 //拼接sql语句 27 String sql = "SELECT * FROM category"; 28 //调用QueryRunner中的query方法,结果集使用KeyedHandler 29 //Map<Object,Map<String,Object>> mapmap = qr.query(sql, new KeyedHandler("cid")); 30 Map<Object,Map<String,Object>> mapmap = qr.query(sql, new KeyedHandler("cname")); 31 //遍历Map集合取出存储每一行数据的Map集合 32 for (Object obj: mapmap.keySet()) { 33 //根据key取出值存储每一行数据的Map集合 34 Map<String,Object> map = mapmap.get(obj); 35 //遍历Map集合 36 for(String key : map.keySet()){ 37 System.out.print(obj+":"+key+"..."+map.get(key)+"\t"); 38 } 39 //打印完每一行数据之后换行 40 System.out.println(); 41 } 42 } catch (Exception e) { 43 System.out.println(e); 44 } 45 } 46 47 /* 48 * 第八种查询方式:使用ScalarHandler(重点) 49 * ScalarHandler用于执行返回单个数据的sql语句 50 * 使用聚合函数查询的结果都是单个数据 51 * 或者查询某一行的某一个字段 52 */ 53 @Test 54 public void scalarHandler(){ 55 try { 56 //拼接sql语句 57 String sql = "SELECT SUM(cid) FROM category"; 58 sql = "SELECT cname FROM category WHERE cid=?"; 59 //调用QueryRunner中的query方法,结果集使用ScalarHander 60 //返回的值的数据类型不确定,使用Object类型 61 Object obj = qr.query(sql, new ScalarHandler(),2); 62 System.out.println(obj); 63 } catch (Exception e) { 64 System.out.println(e); 65 } 66 } 67 68 /* 69 * 第七种查询方式:使用MapListHandler 70 * 1.MapListHandler会把多条数据,存储到多个Map集合中 71 * key:字符串类型列名 72 * value:列名对应的值(不同的列,数据类型不同,value使用Object类型) 73 * 2.会把多个Map集合存储List集合中 74 */ 75 @Test 76 public void mapListHandler(){ 77 try { 78 //拼接查询sql语句 79 String sql = "SELECT * FROM category"; 80 //调用QueryRunner中的query方法执行查询sql语句,结果集传入MapListHandler 81 List<Map<String,Object>> list = qr.query(sql, new MapListHandler()); 82 //遍历List集合,取出Map集合 83 for (Map<String, Object> map : list) { 84 //遍历Map集合 85 for(String key: map.keySet()){ 86 System.out.print(key+"..."+map.get(key)+"\t"); 87 } 88 System.out.println();//打印完每行数据之后换行 89 } 90 } catch (Exception e) { 91 System.out.println(e); 92 } 93 } 94 95 /* 96 * 第六种查询方式:使用MapHandler 97 * MapHandler把结果集的第一行数据存储Map集合中 98 * key:字符串类型列名 99 * value:列名对应的值(不同的列,数据类型不同,value使用Object类型)100 */101 @Test102 public void mapHandler(){103 try {104 //拼接查询sql语句105 String sql = "SELECT * FROM category";106 //调用QueryRunner中的query方法执行查询sql语句,结果集传入MapHandler107 Map<String,Object> map = qr.query(sql, new MapHandler());108 //使用keySet遍历Map集合109 Set<String> set = map.keySet();110 for (String key : set) {111 //通过key使用get方法获取value112 Object value = map.get(key);113 System.out.print(key+"..."+value+" ");114 }115 } catch (Exception e) {116 System.out.println(e);117 }118 }119 120 121 /*122 * 第五种查询方式:使用ColumnListHandler123 * ColumnListHandler会把结果集中指定列的数据封装到一个List集合中124 * 如果不指定列,则默认使用第一列数据存储到List集合中125 * ColumnListHandler()
126 * ColumnListHandler(int columnIndex) 列号1,2,3,4127 * ColumnListHandler(String columnName) 列名128 */129 @Test130 public void columnListHandler(){131 try {132 //拼接查询sql语句133 String sql = "SELECT * FROM category";134 //调用QueryRunner中的query方法执行查询sql语句,结果集传入ColumnListHandler135 //返回一个List集合,因为列的数据类型不同,所有元素使用Object类型136 List<Object> list = qr.query(sql, new ColumnListHandler("cname"));137 for (Object object : list) {138 System.out.println(object);139 }140 } catch (Exception e) {141 System.out.println(e);142 }143 }144 145 /*146 * 第四种查询方式:使用BeanListHandler(重点)147 * BeanListHandler会把多个条数据封装到多个JavaBean对象中148 * 多个JavaBean对象存储到List集合中149 *
150 * 注意:151 * JavaBean中必须有空参数构造方法152 */153 @Test154 public void beanListHandler(){155 try {156 //拼接查询sql语句157 String sql = "SELECT * FROM category";158 //调用QueryRunner中的query方法执行查询sql语句,结果集传入BeanListHandler159 List<Category> list = qr.query(sql, new BeanListHandler<>(Category.class));160 //遍历存储Category的list集合161 for (Category category : list) {162 System.out.println(category);163 }164 } catch (Exception e) {165 System.out.println(e);166 }167 }168 169 /*170 * 第三种查询方式:使用BeanHandler(重点)171 * BeanHandler会把结果集的第一条数据,封装到指定的JavaBean对象中172 * 构造方法:173 * BeanHandler(Class<T> type) 传递JavaBean对象的class文件对象174 * BeanHandler内部会根据传递的class文件对象使用反射技术创建JavaBean对象,把查询的结果集中第一行数据存储到JavaBean中175 * 相当于176 * Category c = new Category();177 * c.setXXX(xxx);178 * c.setXXX(xxx);179 * c.setXXX(xxx);180 * 注意:181 * JavaBean中必须有空参数构造方法182 */183 @Test184 public void beanHandler(){185 try {186 //拼接查询sql语句187 String sql = "SELECT * FROM category";188 //调用QueryRunner中的query方法执行查询sql语句,结果集传入BeanHandler189 Category cate = qr.query(sql, new BeanHandler<>(Category.class));190 System.out.println(cate);191 } catch (Exception e) {192 System.out.println(e);193 }194 }195 196 /*197 * 第二种查询方式:使用ArrayListHandler198 * ArrayListHandler中会把多条结果封装到多个对象数组中199 * 一个对象数组封装一行数据200 * 多个对象数组需要存储到list集合中201 */202 @Test203 public void arrrayListHandler(){204 try {205 //拼接查询sql语句206 String sql = "SELECT * FROM category where cid = ?";207 sql = "SELECT * FROM category where cid in(?,?)";208 //调用QueryRunner中的query方法执行查询sql语句,传入ArrayListHandler结果集209 List<Object[]> list = qr.query(sql, new ArrayListHandler(), 1,3);210 //遍历集合211 for (Object[] objects : list) {212 //遍历对象数组213 for (Object obj : objects) {214 System.out.print(obj+" ");215 }216 System.out.println();//换行217 }218 } catch (Exception e) {219 System.out.println(e);220 }221 }222 223 /*224 * 第一种查询方式:使用ArrayHandler225 * ArrayHandler会把结果集第一条数据封装到一个对象(Object)数组中226 * 数组中的每一个元素,就是第一条数据数据的每一列的值227 */228 @Test229 public void arrayHandler(){230 try {231 //拼接查询sql语句232 String sql = "SELECT * FROM category";233 //调用QueryRunner中的query方法执行查询sql语句,传入ArrayHandler结果集234 Object[] objs = qr.query(sql, new ArrayHandler());235 //遍历数组236 for (Object obj : objs) {237 System.out.print(obj+" ");238 }239 } catch (Exception e) {240 System.out.println(e);241 }242 }243 }
The above is the detailed content of Detailed introduction to C3P0 connection pool. For more information, please follow other related articles on the PHP Chinese website!