1.导入相应的JARs
MySQL驱动jar包:mysql-connector-Java-5.1.28-bin.jar
C3P0需要的jar包:c3p0-0.9.2-pre1.jar和mchange-commons-0.2.jar
2.在src下添加C3P0配置文件:c3p0-config.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <c3p0-config>
- <default-config>
- <property name="jdbcUrl">jdbc:mysql://localhost:3306/grain</property>
- <property name="driverClass">com.mysql.jdbc.Driver</property>
- <property name="user">root</property>
- <property name="password">123</property>
- <property name="acquireIncrement">3</property>
- <property name="initialPoolSize">10</property>
- <property name="minPoolSize">2</property>
- <property name="maxPoolSize">10</property>
- </default-config>
- </c3p0-config>
3.封装为JdbcUtils,用来获取Connection对象,以及开启和关闭事务。有以下接口:
-
Connection getConnection():从c3p0连接池获取Connection对象,所以需要提供c3p0-config.xml配置文件;
-
beginTransaction():为当前线程开启事务;
-
commitTransaction():提交当前线程的事务;
-
rollbackTransaction():回滚当前线程的事务;
-
releaseConnection(Connection):如果参数连接对象不是当前事务的连接对象,那么关闭它,否则什么都不做;
4.测试JdbcUtils
- @Test
- public void testJdbcUtils() throws SQLException {
- Connection con = JdbcUtils.getConnection();
- System.out.println(con);
- JdbcUtils.releaseConnection(con);
- System.out.println(con.isClosed());
- }
5.封装TxQueryRunner,使用JdbcUtils连接数据库,使用QueryRunner简化数据库操作
引入jar包:commons-dbutils-1.4.jar,有以下接口:
-
int[] batch(String sql, Object[][] params):执行批处理,参数sql是SQL语句模板,params为参数;
-
T query(String sql, ResultSetHandler<T> rh):执行查询,执行查询,参数sql为要执行的查询语句模板,rh是结果集处理,用来把结果集映射成你想要的结果;
-
T query(String sql, ResultSetHandler<T> rh, Object… params):执行查询,参数sql为要执行的查询语句模板,rh是结果集处理,用来把结果集映射成你想要的结果,params是sql语句的参数;
-
int update(String sql):执行增、删、改语句,参数sql是要执行的SQL语句;
-
int update(Stringsql, Object param):执行增、删、改语句,参数sql是要执行的SQL语句,参数param是参数(一个参数);
- int update(String sql, Object… params):执行增、删、改语句,参数sql是要执行的SQL语句,参数params是参数(多个参数);
- @Test
- public void testUpdate() throws SQLException {
- String sql = "insert into user_info(uid,uname,phonenumber,region,address,postcode) values(?,?,?,?,?,?)";
- Object[] params = {"0001","xiaoming","12345678912","China","Beijing","123456"};
- QueryRunner q = new TxQueryRunner();
- q.update(sql,params);
- }
- @Test
- public void testUpdate2() throws SQLException {
- try{
- JdbcUtils.beginTransaction();
- String sql = "insert into user_info(uid,uname,phonenumber,region,address,postcode) values(?,?,?,?,?,?)";
- Object[] params1 = {"0002","xiaoming","12345678912","China","Beijing","123456"};
- Object[] params2 = {"0003","xiaoming","12345678912","China","Beijing","123456"};
- QueryRunner q = new TxQueryRunner();
- q.update(sql,params1);
- q.update(sql,params2);
- JdbcUtils.commitTransaction();
- }catch(Exception e){
- try{
- JdbcUtils.rollbackTransaction();
- }catch(SQLException ee){
- }
- }
- }
- //单行结果集,BeanHandler
- @Test
- public void testQuery() throws SQLException{
- String sql = "select * from user_info where uid = ?";
- QueryRunner q = new TxQueryRunner();
- User u = q.query(sql, new BeanHandler<User>(User.class),"0001");
- System.out.println(u);
- }
- //多行结果集,BeanListHandler
- @Test
- public void testQuery2() throws SQLException{
- String sql = "select * from user_info";
- QueryRunner q = new TxQueryRunner();
- List<User> list_u = q.query(sql, new BeanListHandler<User>(User.class));
- System.out.println(list_u);
- }
- //单行结果集,map
- @Test
- public void testQuery3() throws SQLException{
- String sql = "select * from user_info where uid = ?";
- QueryRunner q = new TxQueryRunner();
- Map<String,Object> map_u = q.query(sql, new MapHandler(),"0001");
- System.out.println(map_u);
- }
- //多行结果集,List<map>
- @Test
- public void testQuery4() throws SQLException{
- String sql = "select * from user_info";
- QueryRunner q = new TxQueryRunner();
- List<Map<String,Object>> list_map_u = q.query(sql, new MapListHandler());
- System.out.println(list_map_u);
- }
- //单行单列,ScalarHandler
- @Test
- public void testQuery5() throws SQLException{
- String sql = "select count(*) from user_info";
- QueryRunner q = new TxQueryRunner();
- Object obj = q.query(sql, new ScalarHandler());
- //select count(*)的结果强转成Number
- Number n = (Number)obj;
- System.out.println(n.longValue());
- }
正文:JAVA中DBUtils的QueryRunner类实现增删改查详解
一.QueryRunner类实现增删改
public static void main(String[] args) throws Exception{ //使用自己的工具类,获取数据库的连接对象
Connection conn = MyJDBCUtils.getConnection(); //创建QueryRunner类对象
QueryRunner qr = new QueryRunner();
String sql = "UPDATE gjp_ledger set money=? , ldesc=? where lid=?";
Object[] params = {998,"买钻石",3};
int result = qr.update(conn, sql, params);
System.out.println(result); DbUtils.close(conn);
}
二.QueryRunner类获取查询结果集ResultSetHandler
1.常用Handler * ArrayHandler 将结果集第一行转成对象数组
public static void main(String[] args) throws Exception{
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger where lid=?"; //结果集处理方式,ArrayHandler 第一行转成对象数组
QueryRunner qr = new QueryRunner();
Object[] objects = qr.query(conn, sql, new ArrayHandler(),3);
for(Object obj : objects){
System.out.println(obj);
}
}
* ArrayListHandler 将结果集中的每一行数据都转成一个对象数组,再将转成的多个对象数组存放到List中
public static void main(String[] args) throws Exception{
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger";
QueryRunner qr = new QueryRunner();
//执行query方法,传递连接对象,SQL语句,结果集处理方式ArrayListHandler
List<Object[]> list = qr.query(conn, sql, new ArrayListHandler());
for(Object[] objects : list)
{ for(Object obj : objects){
System.out.print(obj+" ");
}
System.out.println();
}
}
* BeanHandler 将结果集第一行数据封装到一个对应的业务类实例中 (1): 定义业务数据类Ledger(实体类,javaBean)
public class Ledger {
//要生成空参、有参构造、set和get方法、toString方法
private int lid;
private String parent;
private double money;
private int sid;
private String account;
private String createtime;
private String ldesc;
}
(2)测试案例代码
public static void main(String[] args) throws Exception{
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger";
QueryRunner qr = new QueryRunner();
//BeanHandler泛型 ,构造方法(和反射有关系的对象)
Ledger legder = qr.query(conn, sql, new BeanHandler<Ledger>(Ledger.class));
System.out.println(legder);
}
* BeanListHandler 将结果集中的每一行数据都封装到一个对应的业务类实例中,再将多个业务类实例对象存放到List里。
public static void main(String[] args) throws Exception {
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger";
//结果集处理方式,BeanListHandler 每一行数据封装到业务数据类中
QueryRunner qr = new QueryRunner();
List<Ledger> list = qr.query(conn, sql, new BeanListHandler<Ledger>(Ledger.class));
for(Ledger ledger : list)
{
System.out.println(ledger);
}
}
* MapHandler 将结果集中的第一行数据封装到一个Map中,key是列名,value是对应的值。
public static void main(String[] args) throws Exception {
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger";
//结果集处理方式,MapHandler 封装到一个Map集合中,存储键值对集合
QueryRunner qr = new QueryRunner();
Map<String,Object> map = qr.query(conn, sql,new MapHandler());
for(String key : map.keySet()){
System.out.println(key+" "+map.get(key));
}
}
* MapListHandler 将结果集中的每一行数据都封装到一个Map里,然后再将多个Map存放到List
public static void main(String[] args) throws Exception{
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger";
//结果集处理方式,MapListHandler 数据中的每一行封装成Map集合,多个Map集合存储到List集合
QueryRunner qr = new QueryRunner();
List<Map<String,Object>> list = qr.query(conn, sql,new MapListHandler());
for(Map<String,Object> map : list){
for(String key : map.keySet()){
System.out.print(key+" "+map.get(key));
}
System.out.println();
}
}
* ColumnListHandler 将结果集中某一列的数据存放到List中
public static void main(String[] args)throws Exception {
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger";
//结果集处理方式,ColumnListHandler 数据中一个列,数据存储到List集合
QueryRunner qr = new QueryRunner(); //ColumnListHandler构造方法中,写字符串列名
List<Double> list = qr.query(conn, sql,new ColumnListHandler<Double>("money"));
for(Double money : list){ System.out.println(money);
}
}
* ScalarHandler 进行单值查询,如:select count(*) from
public static void main(String[] args) throws Exception{
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT count(*)as count FROM gjp_ledger"; //结果集处理方式,ScalarHandler 满足只有1列,1个数据
QueryRunner qr = new QueryRunner(); //ColumnListHandler构造方法中,写字符串列名
Long i = qr.query(conn, sql, new ScalarHandler<Long>("count"));
System.out.println(i);
}
* KeyedHandler将结果集中的每一行数据都封装为一个Map(List<Map>),再将这些Map再存到一个Map里,其key为KeyedHandler指定的列,如果没有指定则使用第一个查询的字段。
public static void main(String[] args) throws Exception{
Connection conn = MyJDBCUtils.getConnection();
String sql = "SELECT * FROM gjp_ledger"; //结果集处理方式,KeyedHandler
QueryRunner qr = new QueryRunner();
Map <String,Map<String,Object>> map= qr.query(conn, sql, new KeyedHandler<String>("lid"));
System.out.println(map);
}