各连接池需要的jar包
package csdn1; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.List; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.junit.Test; import com.alibaba.druid.pool.DruidDataSourceFactory; import com.mchange.v2.c3p0.ComboPooledDataSource; //2.使用现有的数据库连接池 public class jdbc7 { // 2.1各个连接池获取连接的方式 // DBCP--需导入两个commons-dbcp-1.4.jar和commons-pool-1.5.5.jar @Test public void dbcp() throws Exception { /* * BasicDataSource bds = new BasicDataSource(); * bds.setDriverClassName("com.mysql.jdbc.Driver"); * bds.setUrl("jdbc:mysql:///mydbs2"); bds.setUsername("root"); * bds.setPassword("root"); Connection cn = bds.getConnection(); */ // 更多的是使用配置文件的方式来配置参数--包括其它连接池也一样 Properties p = new Properties(); FileInputStream inputStream = new FileInputStream(new File("src/csdn1/dbcp.properties"));//配置文件内容和位置在文末 p.load(inputStream); DataSource ds = BasicDataSourceFactory.createDataSource(p);// 需导入源码commons-dbcp-1.4-src.zip Connection cn = ds.getConnection(); System.out.println(cn); } // Druid--需导入druid-1.1.10.jar---德鲁伊数据库连接池 @Test public void druid() throws Exception { Properties p = new Properties(); FileInputStream inputStream = new FileInputStream(new File("src/csdn1/druid.properties"));//配置文件内容和位置在文末 p.load(inputStream); DataSource ds = DruidDataSourceFactory.createDataSource(p); Connection cn = ds.getConnection(); System.out.println(cn); } // C3P0--需导入c3p0-0.9.1.2.jar // 稍有不同的是配置文件变为位于src下的c3p0-config.xml文件,配置文件内容和位置在文末 @Test public void c3p0() throws SQLException { ComboPooledDataSource ds = new ComboPooledDataSource("xmlName"); Connection cn = ds.getConnection(); System.out.println(cn); } // 2.2实操--需导入commons.dbutils(开源jdbc类库),实现增删改查--下面的连接获取均使用druid数据库连接池 // 增删改 // update(Connection conn, String sql, Object... params) @Test public void update() { QueryRunner query = new QueryRunner(); try (Connection druidConnection = JDBCUtils.getDruidConnection();) { String sql = "insert into user (name,password) values(?,?)"; query.update(druidConnection, sql, "ming", "ming123"); sql = "update user set password=? where name=?"; int effect = query.update(druidConnection, sql, "mdzone", "ming"); System.out.println("返回影响的记录数:" + effect); } catch (SQLException e) { e.printStackTrace(); } } // 查询query() /* * 用于封装表中的记录--都是实现ResultHandler接口 * 处理对象:BeanHandler,BeanListHandler * 键值对处理:MapHandler * 聚集函数查询处理:ScalarHandler * */ @Test public void query() { QueryRunner qr = new QueryRunner(); try (Connection druidConnection = JDBCUtils.getDruidConnection();) { String sql = "select count(*) from user;"; ScalarHandler handler = new ScalarHandler(); Long count = (Long) qr.query(druidConnection, sql, handler); System.out.println(count); sql = "select id,name,password from user;"; BeanListHandler<User> beanList = new BeanListHandler<>(User.class); List<User> query = qr.query(druidConnection, sql, beanList); for(User u :query) { System.out.println(u); } } catch (SQLException e) { e.printStackTrace(); } } } //获取连接的类JDBCUtils class JDBCUtils { // druid private static DataSource druid = null; static { try { Properties p = new Properties(); FileInputStream inputStream = new FileInputStream(new File("src/csdn1/druid.properties")); p.load(inputStream); druid = DruidDataSourceFactory.createDataSource(p); } catch (Exception e) { e.printStackTrace(); } } public static Connection getDruidConnection() throws SQLException { Connection cn = druid.getConnection(); return cn; } // dbcp private static DataSource dbcp = null; static { try { Properties p = new Properties(); InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties"); p.load(inputStream); dbcp = BasicDataSourceFactory.createDataSource(p); } catch (Exception e) { e.printStackTrace(); } } public static Connection getDbcpConnection() throws SQLException { Connection cn = dbcp.getConnection(); return cn; } // c3p0 public static ComboPooledDataSource c3p0 = new ComboPooledDataSource("xmlName"); public static Connection getC3p0Connection() throws SQLException { Connection cn = c3p0.getConnection(); return cn; } }
User对象
package csdn1; // 一条记录对应一个对象,User必须是公共类 public class User { private String id; private String name; private String password; public User() { } public User(String id, String name, String password) { this.id = id; this.name = name; this.password = password; } public void setId(String id) { this.id = id; } public void setName(String name) { this.name = name; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "User [id=" + id + ", name=" + name + ", password=" + password + "]"; } }
dbcp.properties 和druid.properties一样
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///mydbs2 username=root password=root
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!-- 自定义name以实现文件配置 --> <named-config name="xmlName"> <!-- 获取连接的四个信息 --> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost/mydbs2</property> <property name="user">root</property> <property name="password">root</property> <!-- 数据库连接池的基本信息 申请连接数 初始连接数 维护的最小连接数 维护的最大连接数 维护的最大Statement数 每个连接可使用的最大Statement数 --> <property name="acquireIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">100</property> <property name="maxStatements">50</property> <property name="maxStatementsPerConnection">2</property> </named-config> </c3p0-config>