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.Test
2     public void Query2() throws Exception
3     {
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.Test
2     public void Query3() throws Exception
3     {
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     }

 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     }

1 @org.junit.Test
2     public void Query6() throws Exception
3     {
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     }

1     @org.junit.Test
2     public void Query7() throws Exception
3     {
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     }

1     @org.junit.Test
2     public void Query8() throws Exception
3     {
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     }

导入包包括