首先看C3p0這個連接池,最大優勢可以自動讀取預設的配置文件 配置文件中有常規的4個主選項和一些其他配置 只需要使用C3p0中的實現javax.sql.DateSource介面的實現類ComboPooledDataSource 創建對象即可 private static ComboPooledDat ...
首先看C3p0這個連接池,最大優勢可以自動讀取預設的配置文件
<?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/swift_db
</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">20</property>
<property name="acquireIncrement">5</property>
<property name="maxPoolSize">50</property>
<property name="minPoolSize">5</property>
</default-config>
</c3p0-config>
配置文件中有常規的4個主選項和一些其他配置
只需要使用C3p0中的實現javax.sql.DateSource介面的實現類ComboPooledDataSource 創建對象即可
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
這個是放成員變數那,只要new 空參就會自動調用xml配置文件,配置文件需要標簽<default-config>
也可以調用命名的配置文件,需要配置文件<named-config>
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/swift_db
</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">20</property>
<property name="acquireIncrement">5</property>
<property name="maxPoolSize">50</property>
<property name="minPoolSize">5</property>
</named-config>
</c3p0-config>
一般把C3p0做成工具類使用,方便調用
package com.swift.base; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class C3p0Utils { // 數據源連接池c3p0作用不用每次建立連接,浪費資源與等待時間,把連接做好放在連接池中,需要時獲取 // c3p0連接池可以通過配置文件c3p0-config.xml文件自動讀取相關屬性 // c3p0連接池是java中 javax.sql.DataSource的實現類,其中必須實現getConnection方法 private static ComboPooledDataSource dataSource = new ComboPooledDataSource("swift"); // 獲得數據源(連接池)的方法 public static DataSource getDataSource() { return dataSource; } // 從連接池中獲得一個conn資源的 public static Connection getConnection() throws Exception { dataSource.setDriverClass("com.mysql.jdbc.Driver"); dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/swift_db"); dataSource.setUser("root"); dataSource.setPassword("root"); dataSource.setAcquireIncrement(5); dataSource.setInitialPoolSize(20); dataSource.setMinPoolSize(5); dataSource.setMaxPoolSize(50); Connection conn = dataSource.getConnection(); return conn; } public static void close(Connection conn, 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 (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
這個已經調用了xml配置文件,每次又調用Connection都會來一遍屬性配置,浪費資源,可以用靜態代碼塊,只調用一次即可
package com.swift.jinji; import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; /*使用C3P0獲得10個連接對象 要求：不使用配置文件*/ public class C3p0_10con { private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); static { try { dataSource.setDriverClass("com.mysql.jdbc.Driver"); } catch (PropertyVetoException e) { e.printStackTrace(); } dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/swift_db"); dataSource.setUser("root"); dataSource.setPassword("root"); dataSource.setInitialPoolSize(20); dataSource.setAcquireIncrement(5); dataSource.setMaxPoolSize(50); dataSource.setMinPoolSize(5); } public static void main(String[] args) throws PropertyVetoException { for(int i=0;i<10;i++) { System.out.println(C3p0_10con.getConnection()); } } public static DataSource getDataSource() { return dataSource; } public static Connection getConnection() { Connection con = null; try { con = dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return con; } public static void close(Connection con) { if(con!=null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
配合DBUtils的QureyRunner類和ResultSetHandler類使用,更加方便資料庫增刪改與查詢
package com.swift.jinji; import java.sql.SQLException; import java.util.Arrays; import java.util.List; import java.util.Map; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.ColumnListHandler; import org.apache.commons.dbutils.handlers.KeyedHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import com.swift.base.C3p0Utils; import com.swift.domain.Puppy; /*查詢用戶表中的第一條數據。並將數據封裝成對象數組。*/ public class Qurey_Puppy { public static void main(String[] args) { QueryRunner qr=new QueryRunner(C3p0Utils.getDataSource()); String sql="select * from puppy;"; ResultSetHandler<Puppy> rsh=new BeanHandler<Puppy>(Puppy.class); ResultSetHandler<Object[]> rsh1=new ArrayHandler(); ResultSetHandler<List<Object[]>> rsh2=new ArrayListHandler(); ResultSetHandler<Object> rsh3=new ScalarHandler("name"); ResultSetHandler<Map<String, Object>> rsh4=new MapHandler(); ResultSetHandler<List<Map<String, Object>>> rsh5=new MapListHandler(); ResultSetHandler<List<Object>> rsh6=new ColumnListHandler("name"); ResultSetHandler<Map<Object,Map<String,Object>>> rsh7=new KeyedHandler("id"); try { Map<Object, Map<String, Object>> result = qr.query(sql, rsh7); /* for(Object[] puppy:dog) { System.out.println(Arrays.toString(puppy)); }*/ // System.out.println(result); } catch (SQLException e) { e.printStackTrace(); } } }
ResultSetHandler有很多子類,可以放到各種容器中,從資料庫取出數據後,將數據放到對象數組,數組集合,對象元素,Map集合,Map集合列表,對象列表,Map的Map中
