C3P0Util 的制作 及DButils 中的 QueryRunner 类的使用

C3P0Util 的制作 及DButils 中的 QueryRunner 类的使用

1. 步骤:
   • 导入jar 包
     • c3p0-0.9.1.2.jar
     • commons-dbutils-1.4.jar
     • mysql-connector-java-5.0.8-bin.jar
   • 写C3p0Util 工具类
     • C3p0Util.jar {写好后转为jar形式，方便下次使用}
   • 写入配置文件
     • c3p0-config.xml
   • 写测试类
   • 准备数据库

关于jar包和配置文件的下载地址:

链接：https://pan.baidu.com/s/1qYGxwRlN3vImgV66eiWHtw 密码：lbx2

1. 工具类的写法: package cn.javabs.util; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; /** * C3p0的工具类 * @author Mryang * 2018.08.05 */ public class C3p0Util { private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); public static DataSource getDataSource(){ return dataSource; } public static Connection getConnection(){ try { return dataSource.getConnection(); } catch (SQLException e) { throw new RuntimeException(e); } } }
2. 写入配置文件： 文件命名为: c3p0-config.xml <?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <!--以下属性name的属性值都不可以更改--> <!--连接数据库驱动--> <property name="driverClass">com.mysql.jdbc.Driver</property> <!--数据库连接，如本地主机，可直接更改数据库名称:user--> <property name="jdbcUrl">jdbc:mysql:///user</property> <!--数据库用户名--> <property name="user">root</property> <!--数据库密码--> <property name="password">sorry</property> <property name="initialPoolSize">10</property> <property name="maxIdleTime">30</property> <property name="maxPoolSize">100</property> <property name="minPoolSize">10</property> <property name="maxStatements">200</property> </default-config> </c3p0-config>
3. 实体类 package cn.javabs.entity; public class User { private Integer id; private String username; private String password; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "User [id=" + id + ", username=" + username + ", password=" + password + "]"; } }
4. 测试类 package cn.javabs.test; import java.sql.SQLException; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.junit.Test; import cn.javabs.entity.User; import cn.javabs.util.C3p0Util; /** * 数据源连接池的测试类 * @author Mryang * 本类中引入了一个新的工具类是 QueryRunner */ public class DataTest { QueryRunner qr = new QueryRunner(C3p0Util.getDataSource()); @Test public void addDataTest(){ User user = new User(); user.setId(9); user.setUsername("aaa"); user.setPassword("123"); try { qr.update("insert into user(id, username,password) values (?,?,?)", // user.getId(),user.getUsername(),user.getPassword()); } catch (SQLException e) { throw new RuntimeException(e); } } @Test public void delDataTest(){ User user = new User(); user.setId(9); try { qr.update("delete from user where id = ?", // user.getId()); } catch (SQLException e) { throw new RuntimeException(e); } } @Test public void updateDataTest(){ User user = new User(); user.setPassword("222"); user.setId(9); try { qr.update("update user set password = ? where id = ? ", // user.getPassword(),user.getId()); } catch (SQLException e) { throw new RuntimeException(e); } } @Test public void queryAllDataTest(){ try { List<User> result = qr.query("select * from user", // new BeanListHandler<User>(User.class)); System.out.println(result); } catch (SQLException e) { throw new RuntimeException(e); } } }
5. 数据库创建 create database user; use user; create table user( id int primary key auto_increment, username varchar(50), password varchar(50) );

关于演示代码的下载地址:

链接：https://pan.baidu.com/s/1mg5_NCAdcGMjuiUmO8rNwQ 密码：9m0x