在运行之前记得要导入相应的jar文件
import java.sql.SQLException;
importjava.util.Arrays;
importjava.util.List;
importjava.util.Map;
importorg.apache.commons.dbutils.QueryRunner;
importorg.apache.commons.dbutils.handlers.ArrayHandler;
importorg.apache.commons.dbutils.handlers.ArrayListHandler;
importorg.apache.commons.dbutils.handlers.BeanHandler;
importorg.apache.commons.dbutils.handlers.BeanListHandler;
importorg.apache.commons.dbutils.handlers.ColumnListHandler;
importorg.apache.commons.dbutils.handlers.KeyedHandler;
importcom.hbsi.damain.User;
import com.hbsi.utils.DBManager_C3p0;
public class UserDaoImpl {
//用户表的增删改查
//用户表插入一行
publicvoid insert() {
QueryRunnerrunner = new QueryRunner(DBManager_C3p0.getDataSource());// 提供数据库连接池对象
Stringsql = "insert into users (name,password) values('eee','001')";
try{
runner.update(sql);
}catch (SQLException e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
}
publicvoid delete() {
QueryRunnerrunner1 = new QueryRunner(DBManager_C3p0.getDataSource()); String sql = "delete from userswhere id=8";
try{
runner1.update(sql);
}catch (SQLException e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
}
publicvoid update() {
QueryRunnerrunner2 = new QueryRunner(DBManager_C3p0.getDataSource());
Stringsql = "update users set name='张三',password='2222' where id=2";
try{
runner2.update(sql);
}catch (SQLException e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
}
publicvoid find() throws SQLException {
QueryRunnerrunner = new QueryRunner(DBManager_C3p0.getDataSource());
Stringsql = "select * from users where id=3";
Useru = (User) runner.query(sql, new BeanHandler(User.class));
System.out.println(u.toString());
}
//批处理
public void batch() throws SQLException{
QueryRunner runner = newQueryRunner(DBManager_C3p0.getDataSource());
String sql="insert intousers(name,password) values(?,?)";
Object[][]params={{"111","111"},{"222","222"},{"333","333"}};
runner.batch(sql,params);
}
publicvoid findAll() throws SQLException{
QueryRunnerrunner=new QueryRunner(DBManager_C3p0.getDataSource());
Stringsql="select * from users";
List<User>list=(List<User>) runner.query(sql, new BeanListHandler(User.class));
for(Userlists:list){
System.out.println(lists.toString());
}
}
//输出一条信息,默认为第一条
publicvoid testArrayHandler() {
QueryRunnerrunner = new QueryRunner(DBManager_C3p0.getDataSource());
Stringsql1 = "select *from users";
Object[]result;
try{
result= (Object[]) runner.query(sql1, new ArrayHandler());
System.out.println(Arrays.asList(result));
}catch (SQLException e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
}
//以数组形式输出所有信息
publicvoid testArrayListHandler() throws SQLException {
QueryRunnerrunner = new QueryRunner(DBManager_C3p0.getDataSource());
Stringsql = "select * from users";
List<Object[]>result = (List<Object[]>) runner.query(sql,
newArrayListHandler());
for(int i = 0; i < result.size(); i++) {
System.out.println(Arrays.asList(result.get(i)));
}
}
//输出所有名字
publicvoid testColumnListHandler() throws SQLException {
QueryRunnerrunner = new QueryRunner(DBManager_C3p0.getDataSource());
Stringsql = "select * from users";
List<Object>result = (List<Object>) runner.query(sql,
newColumnListHandler("name"));
System.out.println(result);
}
publicvoid testKeyedHandler() throws SQLException {
QueryRunnerrunner = new QueryRunner(DBManager_C3p0.getDataSource());
Stringsql = "select * from users";
Map<Integer,Map<String, Object>> map = (Map) runner.query(sql,
newKeyedHandler("id"));
for(Map.Entry<Integer, Map<String, Object>> me : map.entrySet()) {
intid = me.getKey();
Map<String,Object> innermap = me.getValue();
for(Map.Entry<String, Object> innerme : innermap.entrySet()) {
Stringname = innerme.getKey();
Objectvalue = innerme.getValue();
System.out.println(name+ "=" + value);
}
}
}
publicstatic void main(String[] args) throws SQLException {
//new UserDaoImpl().insert();
//new UserDaoImpl().delete();
//new UserDaoImpl().update();
//new UserDaoImpl().find();
//new UserDaoImpl().batch();
//newUserDaoImpl().findAll();
newUserDaoImpl().testArrayHandler();
//newUserDaoImpl().testArrayListHandler();
//newUserDaoImpl().testColumnListHandler();
//newUserDaoImpl().testColumnListHandler();
}
}
补充:
Map<Integer , Map<String,Object>>
Integer是key的类型,Map<String,Object>是value的类型
Key value Set
1 aaa 1-aaa
2 bbb 2-bbb
3 ccc => 3-ccc
Map<Integer,String> map=newHashMap<Integer,String>();
map.put(1, "aaa");
map.put(2, "bbb");
map.put(3, "ccc");
//遍历元素,把里面的元素都输出,将Map中存得两列的集合————>单列的set集合
Set<Map.Entry<Integer,String>> set =map.entrySet();//把键值对转换为集合,既把两列转化为一列,方便遍历输出
for(Map.Entry<Integer, String> element : set){
System.out.println(element.getKey()+"————"+element.getValue());
}