DBUtils

 

环境搭建：

1、 复制库文件

c3p0的jar及c3p0-config.xml

dbuitls的jar包

mysql驱动包

2、复制并修改DBManager

插入

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","by@wer.com","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);

}

}

}