重新学习QueryRunner

　　虽然很菜，也要摇摇晃晃的上路。17:51:54 2017-04-11

　　其实QueryRunner主要还是在jdbc的基础上进行了一些封装，以前看的迷迷糊糊，今天重新学习理解了一下，记下来帮助学习。

　　使用的C3P0连接池，通过jdbcUtils工具类来获得连接池对象。

 1 package cn.itcast.jdbc;
 2 
 3 import java.sql.Connection;
 4 import java.sql.SQLException;
 5 
 6 import javax.sql.DataSource;
 7 
 8 import com.mchange.v2.c3p0.ComboPooledDataSource;
 9 
10 public class JdbcUtils {
11     // 配置文件的默认配置！要求你必须给出c3p0-config.xml！！！
12     private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
13     
14     /**
15      * 使用连接池返回一个连接对象
16      * @return
17      * @throws SQLException
18      */
19     public static Connection getConnection() throws SQLException {
20         return dataSource.getConnection();
21     }
22     
23     /**
24      * 返回连接池对象！
25      * @return
26      */
27     public static DataSource getDataSource() {
28         return dataSource;
29     }
30 }

　　原始版本，这时还是用的jdbc得preparedStatement

  1 package cn.itcast.dbutils;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.SQLException;
  7 
  8 import org.junit.Test;
  9 
 10 import cn.itcast.jdbc.JdbcUtils;
 11 
 12 public class Demo1 {
 13     @Test
 14     public void fun1(Stu stu){
 15         Connection con = null;
 16         try{
 17             con = JdbcUtils.getConnection();
 18             System.out.println(con);
 19         }catch(Exception e){
 20             
 21         }finally{
 22             
 23         }
 24     }
 25     //添加
 26     @Test
 27     public void addStu(Stu stu){
 28         Connection con = null;
 29         PreparedStatement pstmt = null;
 30         try {
 31             con = JdbcUtils.getConnection();
 32             String sql = "insert into t_stu values (?,?,?,?)";
 33             pstmt = con.prepareStatement(sql);
 34             pstmt.setInt(1, stu.getSid());
 35             pstmt.setString(2, stu.getSname());
 36             pstmt.setInt(3, stu.getAge());
 37             pstmt.setString(4, stu.getGender());
 38 //            pstmt.setInt(1, 1);
 39 //            pstmt.setString(2, "张三");
 40 //            pstmt.setInt(3, 16);
 41 //            pstmt.setString(4, "男");
 42             pstmt.executeUpdate();
 43         } catch (SQLException e) {
 44             // TODO Auto-generated catch block
 45             e.printStackTrace();
 46         }finally{  //关闭
 47             
 48         }
 49     }
 50     //更新操作
 51     @Test
 52     public void updateStu(Stu stu){
 53         Connection con = null;
 54         PreparedStatement pstmt = null;
 55         try {
 56             con = JdbcUtils.getConnection();
 57             String sql = "update t_stu set sname=?,age=?,gender=? where sid=?";
 58             pstmt = con.prepareStatement(sql);
 59             pstmt.setInt(4, stu.getSid());
 60             pstmt.setString(1, stu.getSname());
 61             pstmt.setInt(2, stu.getAge());
 62             pstmt.setString(3, stu.getGender());
 63             pstmt.executeUpdate();
 64         } catch (SQLException e) {
 65             // TODO Auto-generated catch block
 66             e.printStackTrace();
 67         }finally{  //关闭
 68             
 69         }
 70     }
 71     //删除操作
 72     @Test
 73     public void deleteStu(int sid){
 74         Connection con = null;
 75         PreparedStatement pstmt = null;
 76         try {
 77             con = JdbcUtils.getConnection();
 78             String sql = "delete from t_stu where sid=?";
 79             pstmt = con.prepareStatement(sql);
 80             pstmt.setInt(1, sid);
 81             pstmt.executeUpdate();
 82         } catch (SQLException e) {
 83             // TODO Auto-generated catch block
 84             e.printStackTrace();
 85         }finally{  //关闭
 86             
 87         }
 88     }
 89     //从表中查找返回一个对象
 90     public Stu load(int sid){
 91         Connection con = null;
 92         PreparedStatement pstmt = null;
 93         ResultSet rs = null;
 94         try {
 95             con = JdbcUtils.getConnection();  
 96         
 97             String sql = "select * from t_stu where sid=?";
 98             
 99             pstmt = con.prepareStatement(sql);
100             pstmt.setInt(1, sid);
101             System.out.println("看看");
102             rs = pstmt.executeQuery();  //返回结果集
103             System.out.println(rs);
104             if(!rs.next()) return null;  //当结果集的内容为空则返回为空
105             System.out.println("hello");
106             Stu stu = new Stu();   //否则，创建对象
107             stu.setSid(rs.getInt("sid"));
108             stu.setSname(rs.getString("sname"));
109             stu.setAge(rs.getInt("age"));
110             stu.setGender(rs.getString("gender"));
111             return stu;
112         } catch (SQLException e) {
113             // TODO Auto-generated catch block
114             throw new RuntimeException();
115         }
116     }
117     public static void main(String[] args){
118         Demo1 demo1 = new Demo1();
119         Stu stu = new Stu();
120         stu = demo1.load(1);
121         System.out.println(stu);
122         //添加
123 //        Stu stu1 = new Stu(3,"李四",15,"男");
124 //        demo1.addStu(stu1);
125         //更新操作
126 //        Stu stu2 = new Stu(3,"王昭君",15,"女");
127 //        demo1.updateStu(stu2);
128         //删除操作
129         demo1.deleteStu(3);
130     }
131 }

　　然后是自己写的类似QueryRunner的类，其实已经比较接近了，不过比较粗糙，一点点理解，学习

  1 package cn.itcast.dbutils;
  2 
  3 import java.sql.ResultSet;
  4 import java.sql.SQLException;
  5 
  6 import org.junit.Test;
  7 
  8 import cn.itcast.jdbc.JdbcUtils;
  9 
 10 public class Demo2 {
 11     //用来测试 qr 工具的类
 12     Demo1 demo1 = new Demo1();
 13     @Test
 14     public void fun1(){
 15 //        Stu stu = new Stu(1001,"张三",20,"男");
 16 //        addStu(stu);
 17         Stu s = demo1.load(1001);
 18         System.out.println(s);
 19     }
 20     public void addstu(Stu stu){
 21         QR qr = new QR(JdbcUtils.getDataSource());   //在创建对象时给出连接池
 22         String sql = "insert into t_stu values(?,?,?,?)";  //sql的模板
 23         //给出参数
 24         Object[] params = {stu.getSid(),stu.getSname(),stu.getAge(),stu.getGender()}; 
 25         // 调用pstmt执行增删改
 26         qr.update(sql, params);
 27     }
 28     public void updateStu(Stu stu){
 29         QR qr = new QR(JdbcUtils.getDataSource());  //创建对象时给出连接池
 30         String sql = "update t_stu set sname=?,age=?,gender=? where sid=?";  //进行更新操作的SQL模板
 31         //给出参数  注意参数的位置顺序需要和sql 中的插入位置进行匹配
 32         Object[] params = {stu.getSname(),stu.getAge(),stu.getGender(),stu.getSid()};
 33         qr.update(sql, params);
 34     }
 35     public void deleteStu(int sid){
 36         QR qr = new QR(JdbcUtils.getDataSource()); //创建对象时给出连接池
 37         String sql = "delete from t_stu where sid=?";  //进行删除的sql
 38         //给出参数
 39         Object[] params = {sid};
 40         qr.update(sql, params);
 41     }
 42     // 查询并返回一个对象
 43     public Stu load(int sid){
 44         QR qr = new QR(JdbcUtils.getDataSource());
 45         String sql = "select * from t_stu where sid=?";
 46         //给出参数
 47         Object[] params = {sid};
 48         RsHandler<Stu> rh = new RsHandler<Stu>(){
 49 
 50             @Override
 51             public Stu handle(ResultSet rs) throws SQLException {
 52                 // TODO Auto-generated method stub
 53                 if(!rs.next()) return null;   //如果rs的内容为空，则返回 null
 54                 //否则
 55                 Stu stu = new Stu();       //好像还是使用了最底层的东西，不过是使用工具进行了封装
 56                 stu.setSid(rs.getInt("sid"));
 57                 stu.setSname(rs.getString("sname"));
 58                 stu.setAge(rs.getInt("age"));
 59                 stu.setGender(rs.getString("gender"));
 60                 return stu;
 61             }
 62         };
 63         return (Stu)qr.query(sql, rh, params);
 64     }
 65     public void addStu1(Stu stu){
 66         QR2 qr2 = new QR2(JdbcUtils.getDataSource());
 67         String sql = "insert into t_stu values(?,?,?,?)";  //给出sql
 68         //设置好参数
 69         Object[] params = {stu.getSid(),stu.getSname(),stu.getAge(),stu.getGender()};
 70         qr2.updateStu(sql, params);
 71     }
 72     public void updateStu1(Stu stu){
 73         QR2 qr2 = new QR2(JdbcUtils.getDataSource());
 74         String sql = "update t_stu set sname=?,age=?,gender=? where sid=?";
 75         //设置参数胡
 76         Object[] params = {stu.getSname(),stu.getAge(),stu.getGender(),stu.getSid()};
 77         qr2.updateStu(sql, params);
 78     }
 79     public void deleteStu1(int sid){
 80         QR2 qr2 = new QR2(JdbcUtils.getDataSource());
 81         String sql = "delete from t_stu where sid=?";
 82         //设置参数
 83         Object[] params = {sid};
 84         qr2.updateStu(sql, params);
 85     }
 86     public Stu load2(int sid){
 87         QR2 qr2 = new QR2(JdbcUtils.getDataSource());
 88         String sql = "select * from t_stu where sid=?";
 89         //设置参数
 90         Object[] params = {sid};
 91         RsHandler1<Stu> rh1 = new RsHandler1<Stu>(){
 92 
 93             @Override
 94             public Stu handle(ResultSet rs) throws SQLException {
 95                 // TODO Auto-generated method stub
 96                 if(!rs.next()) return null;  //如果rs的内容为空 ,则返回值为空
 97                 //否则
 98                 Stu stu = new Stu();
 99                 stu.setSid(rs.getInt("sid"));
100                 stu.setSname(rs.getString("sname"));
101                 stu.setAge(rs.getInt("age"));
102                 stu.setGender(rs.getString("gender"));
103                 return stu;     //设置好之后将stu 对象返回
104                 
105             }
106             
107         };
108         return (Stu) qr2.query(sql, rh1, params);
109     }
110     public static void main(String[] args){
111         Demo2 demo2 = new Demo2();
112         // 测试添加操作
113 //        Stu stu = new Stu(3,"王昭君",15,"女");
114 //        demo2.addstu(stu);
115         // 测试更新操作
116 //        Stu stu2 = new Stu(3,"杨玉环",20,"女");
117 //        demo2.updateStu(stu2);
118         // 测试删除操作
119         //demo2.deleteStu(3);
120         // 模仿测试添加操作
121 //        Stu stu3 = new Stu(3,"安可",15,"女");
122 //        demo2.addStu1(stu3);
123         // 模仿测试更行操作
124 //        Stu stu4 = new Stu(3,"安妮",16,"女");
125 //        demo2.updateStu1(stu4);
126         // 模仿测试查询操作
127         Stu stu = demo2.load2(3);
128         System.out.println(stu);
129     }
130 }

　　类似工具,QueryRunner

 1 package cn.itcast.dbutils;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 
 8 import javax.sql.DataSource;
 9     //queryrunner 的学习使用
10 public class QR<T> {
11     private DataSource dataSource = null;
12     public QR(DataSource datasource) {  //获得数据库连接池
13         this.dataSource = datasource;
14     }
15     //一个工具类
16     public QR() {
17         super();
18         // TODO Auto-generated constructor stub
19     }
20     /*
21      * 做 delete update insert 操作
22      * */
23     public int update(String sql,Object ... params){
24         Connection con = null;
25         PreparedStatement pstmt = null;
26         try {
27             con = dataSource.getConnection();//通过连接池得到连接对象
28             pstmt = con.prepareStatement(sql);//使用sql创建pstmt
29             initParams(pstmt, params);//设置参数
30             return pstmt.executeUpdate();//执行
31         } catch(Exception e) {
32             throw new RuntimeException(e);
33         } finally {
34             try {
35                 if(pstmt != null) pstmt.close();
36                 if(con != null) con.close();
37             } catch(SQLException e1) {}
38         }
39     }
40     // 给参数赋值   这样就可以给多个位置设置参数
41         private void initParams(PreparedStatement pstmt, Object... params) 
42                 throws SQLException {
43             for(int i = 0; i < params.length; i++) {
44                 pstmt.setObject(i+1, params[i]);
45             }
46         }
47         public T query(String sql,RsHandler<T> rh,Object...params){
48             Connection con = null;
49             PreparedStatement pstmt = null;
50             ResultSet rs = null;
51             try {
52                 con = dataSource.getConnection();
53                 pstmt = con.prepareStatement(sql);
54                 initParams(pstmt, params);   //设置参数
55                 rs = pstmt.executeQuery();
56                 return rh.handle(rs);
57             } catch (SQLException e) {
58                 // TODO Auto-generated catch block
59                 throw new RuntimeException();
60             } finally{
61                 try{
62                     if(pstmt!=null) pstmt.close();
63                     if(rs!=null) rs.close();
64                 }catch(Exception e2){
65                     
66                 }
67             }  
68         }
69 }
70 //用来把结果集转换成需要的类型！
71 interface RsHandler<T> {
72     public T handle(ResultSet rs) throws SQLException;
73 }

　　使用QueryRunner测试

 1 package cn.itcast.dbutils;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 import java.util.Map;
 6 
 7 import org.apache.commons.dbutils.QueryRunner;
 8 import org.apache.commons.dbutils.handlers.BeanHandler;
 9 import org.apache.commons.dbutils.handlers.BeanListHandler;
10 import org.apache.commons.dbutils.handlers.MapHandler;
11 import org.apache.commons.dbutils.handlers.MapListHandler;
12 import org.apache.commons.dbutils.handlers.ScalarHandler;
13 import org.junit.Test;
14 
15 import cn.itcast.jdbc.JdbcUtils;
16 
17 public class Demo3 {
18     // 在demo2的基础上引入了queryrunner
19     @Test
20     public void fun1() throws SQLException{
21         QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());  //在创建对象的时候给出连接池
22         String sql = "insert into t_stu values(?,?,?,?)";  //给出sql模板
23         Object[] params = {1002,"王五",50,"男"};
24         qr.update(sql, params);
25     }
26     @Test
27     public void fun2() throws SQLException{
28         //接着是查询并返回一个对象
29         //创建QueryRunner对象，并提供数据库连接池对象
30         QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
31         String sql = "select * from t_stu where sid=?";
32         Object[] params = {1001};  //设置好参数
33         //这是 QueryRunner 提供的更简便的方法
34         Stu stu = qr.query(sql, new BeanHandler<Stu>(Stu.class), params);  
35         System.out.println(stu);
36     }
37     /**
38      * BeanListHandler的应用，它是多行处理器
39      * 每行对象一个Stu对象！
40      * @throws SQLException 
41      * @throws Exception
42      */
43     @Test
44     public void fun3() throws SQLException{
45         QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
46         String sql = "select * from t_stu";
47         Object[] params = {};
48         List<Stu> stuList = qr.query(sql, new BeanListHandler<Stu>(Stu.class));
49         System.out.println(stuList);
50     }
51     /**
52      * MapHandler的应用，它是单行处理器，把一行转换成一个Map对象
53      * @throws SQLException 
54      */
55     @Test
56     public void fun4() throws SQLException{
57         QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
58         String sql = "select * from t_stu where sid=?";
59         Object[] params = {1};
60         Map map = qr.query(sql, new MapHandler(),params);
61         System.out.println(map);
62         System.out.println(map.get("sname"));
63     }
64     /**
65      * MapListHandler，它是多行处理器，把每行都转换成一个Map，即List<Map>
66      * @throws SQLException
67      */
68     @Test
69     public void fun5() throws SQLException{
70         QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
71         String sql = "select * from t_stu";
72         List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler());
73         System.out.println(mapList);
74         System.out.println(mapList.get(0));
75     }
76     /**
77      * ScalarHandler，它是单行单列时使用，最为合适！
78      * @throws SQLException
79      */
80     @Test
81     public void fun6() throws SQLException{
82         QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
83         String sql = "select sid from t_stu where sid=?";
84         Object[] params = {1};
85         int sname = (int) qr.query(sql, new ScalarHandler(), params);
86         System.out.println(sname);
87     }
88 }

　　测试的javabean类

 1 package cn.itcast.dbutils;
 2 
 3 public class Stu {
 4     private int sid;
 5     private String sname;
 6     private int age;
 7     private String gender;
 8     public int getSid() {
 9         return sid;
10     }
11     public void setSid(int sid) {
12         this.sid = sid;
13     }
14     public String getSname() {
15         return sname;
16     }
17     public void setSname(String sname) {
18         this.sname = sname;
19     }
20     public int getAge() {
21         return age;
22     }
23     public void setAge(int age) {
24         this.age = age;
25     }
26     public String getGender() {
27         return gender;
28     }
29     public void setGender(String gender) {
30         this.gender = gender;
31     }
32     public Stu() {
33         super();
34         // TODO Auto-generated constructor stub
35     }
36     public Stu(int sid, String sname, int age, String gender) {
37         super();
38         this.sid = sid;
39         this.sname = sname;
40         this.age = age;
41         this.gender = gender;
42     }
43     @Override
44     public String toString() {
45         return "Stu [sid=" + sid + ", sname=" + sname + ", age=" + age
46                 + ", gender=" + gender + "]";
47     }
48     
49 }

posted @ 2017-04-11 18:07 韩立小瓶阅读(575) 评论(0) 收藏举报

刷新页面返回顶部

beth要努力

重新学习QueryRunner

虽然很菜，也要摇摇晃晃的上路。17:51:54 2017-04-11

公告