常用開源數據庫連接池與編寫自己的JDBC框架

2024-07-21 02:52:34

字體：大中小

來源：轉載

供稿：網友

常用開源數據庫連接池

數據庫連接池都要實現DataSource接口

DBCP:

連接設置

driverClassName=com.MySQL.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbc username=root passWord=

初始化連接

initialSize=10

最大連接數量

maxActive=50

最大空閑連接

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_COMMITTED

private static DataSource ds=null; static{ try{ InputStream in=JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties prop=new Properties(); prop.load(in); BasicDataSourceFactory factory=new BasicDataSourceFactory(); ds=factory.createDataSource(prop); }catch(Exception e){ throw new ExceptionInInitializerError(e); } } public static Connection getConnection() throws SQLException{ return ds.getConnection(); }

C3P0:

配置文件：

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/day16</property> <property name="user">root</property> <property name="password">root</property> <property name="acquireIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </default-config> <named-config name="yjw"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property> <property name="user">root</property> <property name="password">root</property> <property name="acquireIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </named-config></c3p0-config>

使用方法：

private static ComboPooledDataSource ds = null; static{ try{ ds = new ComboPooledDataSource(); }catch (Exception e) { throw new ExceptionInInitializerError(e); } } public static Connection getConnection() throws SQLException{ return ds.getConnection(); }

Jndi容器

Tomcat數據庫連接池：

配置文件：

不要忘記將數據庫驅動JAR包加入到Tomcat的lib目錄中使用方法：

private static DataSource ds; static { try { Context initCtx = new InitialContext(); Context envCtx = (Context) initCtx.lookup("java:comp/env"); ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB"); } catch (Exception e) { throw new RuntimeException(e); } } public static Connection getConnection() throws SQLException{ return ds.getConnection(); }

編寫自己的JDBC框架

元數據：數據庫，表，列的定義信息。 Connection.getDatabaseMetaData();

PreparedStatement.getParameterMetaData();//獲得代表PreparedStatement元數據的ParameterMetaData對象

ResultSet.getMetaData();//獲得代表ResultSet對象元數據的ResultSetMetaData對象。

public class JdbcUtils { private static DataSource ds = null; static{ try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties prop = new Properties(); prop.load(in); BasicDataSourceFactory factory = new BasicDataSourceFactory(); ds = factory.createDataSource(prop); }catch (Exception e) { throw new ExceptionInInitializerError(e); } } public static Connection getConnection() throws SQLException{ return ds.getConnection(); } public static void release(Connection conn,Statement st,ResultSet rs){ if(rs!=null){ try{ rs.close(); //throw new }catch (Exception e) { e.printStackTrace(); } rs = null; } if(st!=null){ try{ st.close(); }catch (Exception e) { e.printStackTrace(); } st = null; } if(conn!=null){ try{ conn.close(); }catch (Exception e) { e.printStackTrace(); } } } //String sql = "insert into account(id,name,money) values(?,?,?)" object[]{1,"aaa","10000"}; public static void update(String sql,Object params[]) throws SQLException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = getConnection(); st = conn.prepareStatement(sql); for(int i=0;i<params.length;i++){ st.setObject(i+1,params[i]); } st.executeUpdate(); }finally{ release(conn, st, rs); } } // public static Object query(String sql,Object params[],ResultSetHandler handler) throws SQLException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = getConnection(); st = conn.prepareStatement(sql); for(int i=0;i<params.length;i++){ st.setObject(i+1,params[i]); } rs = st.executeQuery(); return handler.handler(rs); }finally{ release(conn, st, rs); } }} interface ResultSetHandler{ public Object handler(ResultSet rs);}class BeanHandler implements ResultSetHandler{ private Class clazz; public BeanHandler(Class clazz){ this.clazz = clazz; } public Object handler(ResultSet rs) { try{ if(!rs.next()){ return null; } //創建封裝結果集的bean Object bean = clazz.newInstance(); //得到結果集的元數據，以獲取結果集的信息 ResultSetMetaData meta = rs.getMetaData(); int count = meta.getColumnCount(); for(int i=0;i<count;i++){ String name = meta.getColumnName(i+1); //獲取到結果集每列的列名 id Object value = rs.getObject(name); //1 //反射出bean上與列名相應的屬性 Field f = bean.getClass().getDeclaredField(name); f.setaccessible(true); f.set(bean, value); } return bean; }catch (Exception e) { throw new RuntimeException(e); } }}class BeanListHandler implements ResultSetHandler{ private Class clazz; public BeanListHandler(Class clazz){ this.clazz = clazz; } public Object handler(ResultSet rs) { List list = new ArrayList(); try{ while(rs.next()){ Object bean = clazz.newInstance(); ResultSetMetaData meta = rs.getMetaData(); int count = meta.getColumnCount(); for(int i=0;i<count;i++){ String name = meta.getColumnName(i+1); Object value = rs.getObject(name); Field f = bean.getClass().getDeclaredField(name); f.setAccessible(true); f.set(bean, value); } list.add(bean); } }catch (Exception e) { throw new RuntimeException(e); } return list; }}

上一篇：Redis學習筆記

下一篇：ORA-28000: the account is locked-的解決辦法