java中如何使用QueryRunner进行增、删、改、查操作呢？

下文笔者讲述QueryRunner进行CRUD操作的示例分享,如下所示

实现思路:
    1.引入相应的jar包
	2.设置相应的配置文件
	3.编写相应的代码即可

例:
使用C3P0连接池连接MySql

导入相应的JARs

MySQL驱动jar包:
  mysql-connector-Java-5.1.28-bin.jar
C3P0需要的jar包:
  c3p0-0.9.2-pre1.jar和mchange-commons-0.2.jar

在src下添加C3P0配置文件
c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>  
<c3p0-config>  
    <default-config>   
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/testdb</property>  
        <property name="driverClass">com.mysql.jdbc.Driver</property>  
        <property name="user">root</property>  
        <property name="password">123456</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> 

封装为JdbcUtils
用来获取Connection对象
以及开启和关闭事务
有以下接口

Connection getConnection():
   从c3p0连接池获取Connection对象，所以需要提供c3p0-config.xml配置文件；

beginTransaction():
   为当前线程开启事务；

commitTransaction():
   提交当前线程的事务；

rollbackTransaction():
   回滚当前线程的事务；

releaseConnection(Connection):
  当参数连接对象不是当前事务的连接对象，那么关闭它，否则什么都不做；

例:
测试JdbcUtils

@Test  
    public void testJdbcUtils() throws SQLException {  
        Connection con = JdbcUtils.getConnection();  
        System.out.println(con);  
        JdbcUtils.releaseConnection(con);  
        System.out.println(con.isClosed());  
    }  

封装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是参数(多个参数)

例:
测试TxQueryRunner

@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","maomao","13789017890","China","Shenzhen","518001"};  
		Object[] params2 = {"0003","chengcheng","13666910123","China","tianqin","439087"};  
		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());  
}