QueryRunner的实用方法及代码演示原理

我在Eclipse中导入了C3P0和DBUtilsjar包，下面的代码，连接数据库和执行数据库的语句都是用到导入jar包里的封装好的，以下是代码实现

package handle;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import bean.User;
import c3p0_utils.C3P0Utils;

public class TheResultSetHandle {

//ArrayHandle适合取一条记录，比如说登录
public static void test1() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select username,password from user where username=?";
Object[] objects = qr.query(sql, new ArrayHandler(),"zhangsan");
for(int i=0; i < objects.length; i++){
System.out.println(objects[i] + " ");
}
}


//ArrayListHandler适合取多条记录,每一条记录都是object[],再把object[] 放到集合中
public static void test2() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from user";
List<Object[]> list = qr.query(sql, new ArrayListHandler());
for(int i=0; i< list.size(); i++){
for(int j=0; j<list.get(i).length; j++){
System.out.println(list.get(i)[j]);
}
}
}

//3.ColumnListHandler取某一列的数据，默认第一个属性
public static void test3() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select password,username from user";
List<Object> list = qr.query(sql, new ColumnListHandler());
for(int i=0; i < list.size(); i++){
System.out.println(list.get(i));
}
}

//keyedHandler它取的是一个查询集合，返回一个map套map，每一条记录封装到一个map中，每一个字段又在map里的map中
public static void test4() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//Map<Object,Map<String,Object>>
//用这个东西的时候构造方法里面最好穿一个数字n，代表了查询结果n这一列没有重复的值
String sql = "select * from user";
Map<Object,Map<String,Object>> mp = qr.query(sql, new KeyedHandler());
for(Map.Entry<Object, Map<String, Object>> m:mp.entrySet()){
System.out.println(m.getKey());
for(Map.Entry<String, Object> mm: m.getValue().entrySet()){
System.out.println(mm.getKey()+":"+mm.getValue());
}
System.out.println("-------------------");
System.out.println();

}
}


//MapHandler用来查询一条记录，多用于登录功能
public static void test5() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql="select * from user where username=?";
Map<String, Object> map = qr.query(sql, new MapHandler(),"lisi");
for(Map.Entry<String, Object> m:map.entrySet()){
System.out.println(m.getKey()+":"+m.getValue());
}
} 

////6、MapListHandler 每一行都是一个map，把map放入list里表示多行***
public static void test6() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from user ";
List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
for(int i= 0;i<list.size(); i++){
for(Map.Entry<String, Object> m:list.get(i).entrySet()){
System.out.println(m.getKey()+":"+m.getValue());
}
System.out.println("-------------------------------");
}
}

//ScalarHandler取单行单列***   加强记忆
public static void test7() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select username,count(*) from user";
Object object = qr.query(sql, new ScalarHandler(2));
System.out.println(object);
}

//BeanHnadler****
public static void test8() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql="select * from user where username=?";
User user = qr.query(sql, new BeanHandler<User>(User.class),"zhangsan");
System.out.println(user);

}


//、BeanListHandler*****
public static void test9() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql="select * from user";
List<User> list = qr.query(sql, new BeanListHandler<User>(User.class));
for(int i=0; i< list.size(); i++){
System.out.println(list.get(i));
}
}

////insert、update、delete
public static void testInsert() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//String sql = "insert into user (password,username) values(??)";
String sql="insert into user(username,password) values(?,?)";
int rs = qr.update(sql, "zhaoliu","888888");
if(rs>0){
System.out.println("数据插入成功");
}
}
//
public static void testUpdate() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql="update user set password=? where username=?";
int rs=qr.update(sql, "444444","zhaoliu");
if(rs>0){
System.out.println("数据修改成功");
}
}

public static void testDelete() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql="delete from user where username=?";
int rs = qr.update(sql, "lisi");
if(rs>0){
System.out.println("数据删除成功成功");
}
}


public static void main(String[]  args) throws Exception{
test1();
}
}

需要导的jar包

以及自己封装好的c3p0得到数据源和连接的类