环境搭建:
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);
}
}
}