首页 > 代码库 > dbutils的使用Demo

dbutils的使用Demo

首先了解一下

    Queryrunner.query    —————只可以执行select语句。

    Queryrunner.update  —————只可以接收update,delte,insert语句

    BeanHandler          ——————返回一个 Bean,结果封装到Bean。只查询第一行。适合根据id查询唯一的一行
    BeanListHandler     ——————返回List<Bean>所有行,都封装到List,适合于查询多个结果

    MapHandler           ——————与BeanHandler应,只返回第一行,封装成Map类型 - 也适合于查询一个结果集的    适合于关联查询,且没有匹配的Bean。
    MapListHandler      ——————与BeanListHandler对应,返回所有,封装成List<Map>类型


    ScalarHandler        ——————标量- 用于select count(1)|sum|avg|max …一些聚合函数

 

Demo(MyDataSource.getDataSource() —— 获取 DataSource)

插入数据

 1 @org.junit.Test 2     public void Query1() throws Exception 3     { 4         QueryRunner runner=new QueryRunner(MyDataSource.getDataSource()); 5         System.out.println(MyDataSource.getDataSource()); 6         //String sql="INSERT INTO users VALUES (‘U002‘,‘tom‘,‘4321‘ )"; 7         //runner.update(sql); 8         String sql="INSERT INTO users VALUES (?,?,? )"; 9         runner.update(sql,"U003","Jim","123456");10     }
插入数据

更新数据

1 @org.junit.Test2     public void Query2() throws Exception3     {4         QueryRunner runner =new QueryRunner(MyDataSource.getDataSource());5         //String sql=" UPDATE users SET NAME=‘Kim‘ WHERE NAME=‘jim‘ ";6         //runner.update(sql);7         String sql=" UPDATE users SET NAME=? WHERE NAME=? ";8         runner.update(sql,"lily","Kim");9     }
更新数据

删除数据

1     @org.junit.Test2     public void Query3() throws Exception3     {4         QueryRunner runner=new QueryRunner(MyDataSource.getDataSource());5         //String sql="DELETE FROM users WHERE NAME=‘lily‘ ";6         //runner.update(sql);7         String sql="DELETE FROM users WHERE NAME=? ";8         runner.update(sql,"tom");9     }
删除数据

查找数据

 1 @org.junit.Test 2     public void Query4() throws Exception 3     { 4         QueryRunner runner=new QueryRunner(MyDataSource.getDataSource()); 5         //String sql="SELECT * FROM users where id =‘U002‘ "; 6         //user u= runner.query(sql, new  BeanHandler<user>(user.class)); 7          8         String sql="SELECT * FROM users where id =? "; 9         user u=runner.query(sql, new BeanHandler<user>(user.class),"U002");10         System.out.println(u);11     }
查找数据——BeanHandler
 1 @org.junit.Test 2     public void Query5() throws Exception 3     { 4         QueryRunner runner=new QueryRunner(MyDataSource.getDataSource()); 5         String sql="select * from users "; 6         List<user> list= runner.query(sql, new BeanListHandler<user>(user.class)); 7         for(user u:list) 8         { 9             System.out.println(u);10         }11     }
查找数据——BeanListHandler
1 @org.junit.Test2     public void Query6() throws Exception3     {4         QueryRunner runner =new QueryRunner(MyDataSource.getDataSource());5         String sql="select Id,name,Pwd from users ";6         Map<String, Object> map= runner.query(sql, new MapHandler());7         System.out.println(map.toString());8     }
查找数据——MapHandler
1     @org.junit.Test2     public void Query7() throws Exception3     {4         QueryRunner runner =new QueryRunner(MyDataSource.getDataSource());5         String sql="Select * from users ";6         List<Map<String , Object>> list= runner.query(sql, new MapListHandler());7         System.out.println(list);8     }
查找数据——MapListHandler
1     @org.junit.Test2     public void Query8() throws Exception3     {4         QueryRunner runner =new QueryRunner(MyDataSource.getDataSource());5         String sql="select count(*) from Users ";6         Object obj=runner.query(sql, new ScalarHandler());7         int i=Integer.valueOf(obj.toString());8         System.out.println(i);9     }
查找数据——ScalarHandler

导入包包括

dbutils的使用Demo