1、创建c3p0文件 (此文件必须为c3p0-config.xml)且目录放在resource下面
<?xml version="1.0" encoding="utf-8" ?> <c3p0-config> <default-config> <property name="user">nest</property> <property name="password">nest</property> <property name="jdbcUrl">jdbc:oracle:thin:@10.25.186.217:1521:orcl</property> <property name="driverClass">oracle.jdbc.driver.OracleDriver</property> ---下边的可以省略-- <!--初始化连接数--> <property name="initialPoolSize">3</property> <!--最大连接数--> <property name="maxPoolSize">50</property> <!--最小连接数--> <property name="minPoolSize">2</property> <!--最大闲置时间--> <property name="maxIdelTime">30</property> </default-config> </c3p0-config>
2、创建获取c3p0的DButil
package com.neu.commons.util; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /*ComboPooledDataSource c3p0链接的专用类*/ public class DBUtil { private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); //返回数据源的方法 public static DataSource getDataSource() { return dataSource; } //返回Connection链接的方法 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } //定义一个关闭连接的方法 public static void close(Connection conn, PreparedStatement ps, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { rs = null; } } if (ps!=null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }finally { ps=null; } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } finally { conn = null; } } } }
3、创建BaseDao 实例化DButil类 与数据库完成交互
package com.neu.dao; import com.neu.commons.util.DBUtil; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.MapListHandler; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Map; public class BaseDao { protected Connection conn; protected PreparedStatement ps; protected ResultSet rs; protected final void getConnection() throws SQLException { conn=DBUtil.getConnection(); } protected final void close(){ DBUtil.close(conn,ps,rs); } /** * 定义一个更新的方法 */ protected final int executeUpdate(String sql,Object...params) { int row = 0; QueryRunner queryRunner=new QueryRunner(DBUtil.getDataSource()); try { row = queryRunner.update(sql, params); } catch (SQLException e) { e.printStackTrace(); } return row; } /** * 定义一个查询的方法 */ protected final List<Map<String,Object>> executeQuery(String sql,Object...params){ QueryRunner queryRunner=new QueryRunner(DBUtil.getDataSource()); List<Map<String,Object>> list=null; try { list=queryRunner.query(sql,new MapListHandler(),params); } catch (SQLException e) { throw new RuntimeException(e); } return list; } //支持批处理的操作,使用同一条语句多次插入数据时,特别是从excel文件中导入数据时 protected final int executeBatch(String sql,List<List<String>> list){ int row=0; try { getConnection(); ps=conn.prepareStatement(sql); if (list != null) { //获取要插入的每一行中的数据 for (List<String> item : list) { //获取每一行中的每一个?号的值 for (int i = 0; i < item.size(); i++) { ps.setObject(i + 1, item.get(i)); } //把该赋值后的sql添加批处理中,等待处理 ps.addBatch(); } } ps.executeBatch(); row = 1; } catch (SQLException e) { e.printStackTrace(); }finally { close(); } return row; } /* public static void main(String[] args) { String sql = "select * from mgr_info"; BaseDao baseDao = new BaseDao(); System.out.println(baseDao.executeQuery(sql)); System.out.println("==============================wwww"); }*/ }