DButils的用法

DBUtils

环境搭建：

1、复制库文件

c3p0的jar及c3p0-config.xml

dbuitls的jar包

mysql驱动包

2、复制并修改DBManager

CRID

插入

public void insert() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
String sql = "insert into 
 
user(id,name,password,email,birthday) values(null,?,?,?,?)";
 
    Object[] param={"by","sss","[e-mail]","1980-03-04"};
 
    runner.update(sql, param);
 
}

删除

public void delete() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "delete from user where name=?";
 
    Object[] param ={"by"};
 
    runner.update(sql, param);
 
}

查询

public User find() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "select * from user where id=?";
 
           
 
    User u = (User)runner.query(sql,newBeanHandler(User.class),1);
 
    
 
    return u;
 
}

public void getAll() throws SQLException{
 
    QueryRunner runner =
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "select * from user";
 
    List<User> list = (List<User>)  runner.query(sql, 
 
newBeanListHandler(User.class));
 
    System.out.println(list.size());
 
}
 

批处理

public void bath() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "insert into user(id,name,password) values(?,?,?)";
 
    Object[][] params = 
 
{{5,"Jake","aaa"},{6,"John","bbb"},{7,"Jansen","ccc"}};
 
    runner.batch(sql, params);
 
}

ResultSetHandler 接口的实现类

ArrayHandler

public void testArrayHandler() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "select * from user";
 
    Object[] result = runner.query(sql, new ArrayHandler());
 
    System.out.println(Arrays.asList(result));
 
}

ArrayListHandler

 
public void testArrayListHandler() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "select * from user";
 
    List<Object[]> result = 
 
(List<Object[]>) runner.query(sql, new  ArrayListHandler());
 
    for(int i=0; i<result.size(); i++){
 
       System.out.println(Arrays.asList(result.get(i)));
 
    }
 
}

ColumnListHandler

public void testColumnListHandler() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "select * from user";
 
    List<Object> result = 
 
(List<Object>) runner.query(sql, new  ColumnListHandler("name"));
 
    System.out.println(result);     
 
}

KeyedHandler

public void testKeyedHandler() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "select * from user";
 
    Map<Integer,Map<String,Object>>  map =
 
(Map) runner.query(sql, new  KeyedHandler("id"));
 
    for(Map.Entry<Integer,Map<String,Object>>  me : map.entrySet()){
 
       int id = me.getKey();
 
       Map<String,Object> innermap =  me.getValue();
 
       for(Map.Entry<String, Object> innerme:  innermap.entrySet()){
 
           String name = innerme.getKey();
 
           Object value = innerme.getValue();
 
           System.out.println(name + "=" + value);
 
              
 
       }
 
    }
 
}

MapListHandler

public void testMapListHandler() throws SQLException{
 
    QueryRunner runner = 
 
new  QueryRunner(DBManager_c3p0.getDataSource());
 
    String sql = "select * from user";
 
    List<Map<String,Object>>  result = 
 
(List<Map<String,Object>>) 
 
runner.query(sql, new MapListHandler());
 
    for(int i=0; i<result.size(); i++){
 
       Map<String, Object> map =  result.get(i);
 
       
 
       System.out.println("---------------------");
 
       
 
       for(Map.Entry<String,  Object> me : map.entrySet() ){
 
           String colName = me.getKey();
 
             Object value = me.getValue();
 
           
 
           System.out.println(colName + " = " + value);
 
       }
 
    
 
    }
 
}