Java经典封装JDBC模板(充分体现面向对象思想)(转)

程序清单一览

bean类

 1 package com.software.usermanager.bean;
 2 
 3 public class Users {
 4     private String id;
 5     private String name;
 6     private String age;
 7     public String getId() {
 8         return id;
 9     }
10     public void setId(String id) {
11         this.id = id;
12     }
13     public String getName() {
14         return name;
15     }
16     public void setName(String name) {
17         this.name = name;
18     }
19     public String getAge() {
20         return age;
21     }
22     public void setAge(String age) {
23         this.age = age;
24     }
25     
26 
27 }

dao类

 1 package com.software.usermanager.dao;
 2 
 3 
 4 import com.software.usermanager.bean.Users;
 5 import com.software.usermanager.util.PageModel;
 6 import java.util.List;
 7 
 8 public interface UsersDAO {
 9     public boolean insert(Users user);    //增
10     public boolean delete(String id);   //单条删除
11     public boolean delete(String[] userIds);  //批量删除
12     public boolean update(Users user);  //修改
13     public List<Users> query();     //全部查询
14     public Users query(String id);  //单记录查询
15     public PageModel query(int pageNo, int pageSize); //分页查询
16     public PageModel query(int pageNo, int pageSize,String condition); //分页模糊查询
17     public boolean Login(String name,String password); //登录
18 
19 }

daoimpl类

  1 package com.software.usermanager.dao;
  2 import com.software.usermanager.util.OptTemplate;
  3 import java.sql.ResultSet;
  4 import java.util.List;
  5 
  6 import com.software.usermanager.bean.Users;
  7 import com.software.usermanager.util.*;
  8 
  9 public class UsersDAOImpl implements UsersDAO {
 10 
 11     private OptTemplate optTemplate = null;
 12 
 13     public UsersDAOImpl(OptTemplate optTemplate) {
 14         super();
 15         this.optTemplate = optTemplate;
 16     }
 17     public boolean Login(String name, String password) {
 18         // TODO Auto-generated method stub
 19         return false;
 20     }
 21 
 22     public boolean delete(String id) {
 23         String sql = "delete from users where id=?";
 24         Object[] obj = { id };
 25         return optTemplate.update(sql, obj, false);
 26     }
 27 
 28     public boolean delete(String[] userIds) {
 29         StringBuffer sbStr = new StringBuffer();
 30         Object[] obj = userIds;
 31         ;
 32         for (int i = 0; i < userIds.length; i++) {
 33             sbStr.append("?,");
 34         }
 35         String sql = "delete from users where id in("
 36                 + sbStr.substring(0, sbStr.length() - 1) + ")";
 37         return optTemplate.update(sql, obj, false);
 38     }
 39 
 40     public boolean insert(Users user) {
 41         String sql = "insert into users(id,name,age) values(?,?,?)";
 42         Object[] obj = {user.getId(),user.getName(),user.getAge()};
 43         return optTemplate.update(sql, obj, false);
 44     }
 45 
 46     @SuppressWarnings("unchecked")
 47     public List<Users> query() {
 48         String sql = "select * from users";
 49         Object[] obj = {};
 50         return (List<Users>) optTemplate.query(sql, obj, new UsersDAOObjectMapper());
 51         
 52     }
 53 
 54     public Users query(String id) {
 55         String sql = "select * from users";
 56         Object[] obj = {};
 57         return (Users) optTemplate.query(sql, obj, new UsersDAOObjectMapper()).get(0);
 58     }
 59 
 60     public PageModel query(int pageNo, int pageSize) {
 61         String sql1 = "select * from users";
 62         Object[] obj1 = {};
 63         List<Users> list1 = (List<Users>) optTemplate.query(sql1, obj1,
 64                 new UsersDAOObjectMapper());
 65         int i = list1.size();
 66         String sql="select * from (select j.*,rownum rn from (select * from users) j where rownum<=?) where rn>?";
 67         Object[] obj = {pageNo * pageSize, (pageNo - 1) * pageSize};
 68         List<Users> list = (List<Users>) optTemplate.query(sql, obj,
 69                 new UsersDAOObjectMapper());
 70         PageModel pagemodel = new PageModel();
 71         pagemodel.setPageNo(pageNo);
 72         pagemodel.setPageSize(pageSize);
 73         pagemodel.setList(list);
 74         pagemodel.setTotalRecords(i);
 75         return pagemodel;
 76     }
 77 
 78     public PageModel query(int pageNo, int pageSize, String condition) {
 79         String sql1 = "select * from users";
 80         Object[] obj1 = {};
 81         List<Users> list1 = (List<Users>) optTemplate.query(sql1, obj1,
 82                 new UsersDAOObjectMapper());
 83         int i = list1.size();
 84         String sql="select * from (select j.*,rownum rn from (select * from users where id like '"+condition+"%' or name like '"+condition+"%') j where rownum<=?) where rn>?";
 85         Object[] obj = {pageNo * pageSize, (pageNo - 1) * pageSize};
 86         List<Users> list = (List<Users>) optTemplate.query(sql, obj,
 87                 new UsersDAOObjectMapper());
 88         PageModel pagemodel = new PageModel();
 89         pagemodel.setPageNo(pageNo);
 90         pagemodel.setPageSize(pageSize);
 91         pagemodel.setList(list);
 92         pagemodel.setTotalRecords(i);
 93         return pagemodel;
 94     }
 95 
 96     public boolean update(Users user) {
 97         String sql = "update users set name=?,age=? where id=?";
 98         Object[] obj = {user.getName(),user.getAge(),user.getId()};
 99         return optTemplate.update(sql, obj, false);
100     }
101 
102 
103 }
104 class UsersDAOObjectMapper implements ObjectMapper{
105     public Object mapping(ResultSet rs){
106         Users u=new Users();    
107             try{
108                 
109                 u.setId(rs.getString("id"));
110                 u.setName(rs.getString("age"));
111                 u.setName(rs.getString("name"));
112 
113                 
114             }catch(Exception ex){
115                 ex.printStackTrace();
116             }
117 
118         return u;
119     }
120     
121 }

util类

  1 package com.software.usermanager.util;
  2 import java.sql.Connection;
  3 import java.sql.PreparedStatement;
  4 import java.sql.ResultSet;
  5 import java.sql.SQLException;
  6 import java.sql.Statement;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import com.software.usermanager.db.DBConnection;
 11 
 12 public class OptTemplate {
 13     public Object find(String sql,Object[] obj,ObjectMapper mapper){
 14         Object o=null;
 15         Connection conn=null;
 16         PreparedStatement pstmt=null;
 17         try{
 18             conn=DBConnection.getConn();
 19             pstmt=conn.prepareStatement(sql);
 20             for(int i=0;i<obj.length;i++){
 21                 pstmt.setObject(i+1, obj[i]);
 22                 ResultSet rs=pstmt.executeQuery();
 23                 if(rs.next()){
 24                     o=mapper.mapping(rs);
 25                 }
 26             }
 27             }catch(Exception ex){
 28                 ex.printStackTrace();
 29             }finally{
 30                 try{
 31                     pstmt.close();
 32                     conn.close();
 33                 }catch(SQLException ex){
 34                     ex.printStackTrace();
 35                 }
 36             }
 37             return o;
 38         }
 39 
 40     public List<? extends Object> query(String sql,Object[] obj,ObjectMapper mapper){
 41         Object o=null;
 42         List<Object> list=new ArrayList<Object>();
 43         Connection conn=null;
 44         PreparedStatement pstmt=null;
 45         try{
 46             conn=DBConnection.getConn();
 47             pstmt=conn.prepareStatement(sql);
 48             for(int i=0;i<obj.length;i++){
 49                 pstmt.setObject(i+1, obj[i]);
 50             }
 51                 ResultSet rs=pstmt.executeQuery();
 52 
 53                 while(rs.next()){
 54                     
 55                     o=mapper.mapping(rs);
 56                     list.add(o);
 57                 }
 58             
 59             
 60         }catch(SQLException ex){
 61             ex.printStackTrace();
 62         }finally{
 63             try{
 64                 pstmt.close();
 65                 conn.close();
 66             }catch(SQLException ex){
 67                 ex.printStackTrace();
 68             }
 69         }
 70         return list;
 71     }
 72     public boolean update(String sql,Object[] obj,boolean isGenerateKey){
 73         Connection conn=null;
 74         PreparedStatement pstmt=null;
 75         boolean bFlag=false;
 76         try{
 77             conn=DBConnection.getConn();
 78             pstmt=isGenerateKey ? conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS):conn.prepareStatement(sql);
 79             for(int i=0;i<obj.length;i++){
 80                 pstmt.setObject(i+1, obj[i]);
 81             }
 82             conn.setAutoCommit(false);
 83             int i=pstmt.executeUpdate();
 84             conn.commit();
 85             if(i>0)
 86                 bFlag=true;
 87         }catch(SQLException ex){
 88             ex.printStackTrace();
 89         }finally{
 90             try{
 91                 conn.close();
 92                 pstmt.close();
 93                 
 94             }catch(SQLException ex){
 95                 ex.printStackTrace();
 96             }
 97         }
 98         return bFlag;
 99     }
100     
101 
102 }
1 package com.software.usermanager.util;
2 
3 import java.sql.ResultSet;
4 
5 public interface ObjectMapper {
6     public Object mapping(ResultSet rs);
7 
8 }

分页封装类

 1 package com.software.usermanager.util;
 2 
 3 import java.util.List;
 4 
 5 public class PageModel<T> {
 6     
 7     //结果集
 8     private List<T> list;
 9     
10     //记录数
11     private int totalRecords;
12     
13     //每页多少条数据
14     private int pageSize;
15     
16     //第几页
17     private int pageNo;
18 
19     /**
20      * 返回总页数
21      * @return
22      */
23     public int getTotalPages() {
24         return (totalRecords + pageSize - 1) / pageSize;
25     }
26     
27     /**
28      * 首页
29      * @return
30      */
31     public int getTopPageNo() {
32         return 1;
33     }
34     
35     /**
36      * 上一页 
37      * @return
38      */
39     public int getPreviousPageNo() {
40         if (this.pageNo <= 1) {
41             return 1;
42         }
43         return this.pageNo - 1;
44     }
45     
46     /**
47      * 下一页
48      * @return
49      */
50     public int getNextPageNo() {
51         if (this.pageNo >= getButtomPageNo()) {
52             return getButtomPageNo();
53         }
54         return this.pageNo + 1;
55     }
56     
57     /**
58      * 尾页
59      * @return
60      */
61     public int getButtomPageNo() {
62         return getTotalPages();
63     }
64     
65     public List<T> getList() {
66         return list;
67     }
68 
69     public void setList(List<T> list) {
70         this.list = list;
71     }
72 
73     public int getTotalRecords() {
74         return totalRecords;
75     }
76 
77     public void setTotalRecords(int totalRecords) {
78         this.totalRecords = totalRecords;
79     }
80 
81     public int getPageSize() {
82         return pageSize;
83     }
84 
85     public void setPageSize(int pageSize) {
86         this.pageSize = pageSize;
87     }
88 
89     public int getPageNo() {
90         return pageNo;
91     }
92 
93     public void setPageNo(int pageNo) {
94         this.pageNo = pageNo;
95     }
96 }

数据库配置文件dbconf.properties

1 #oracle连接实例
2 driverClass = oracle.jdbc.driver.OracleDriver
3 url = jdbc:oracle:thin:@192.168.137.23:1521:orcl
1 username = 用户名
2 password = 密码

数据库封装类

 1 package com.software.usermanager.db;
 2 
 3 
 4 import java.io.IOException;
 5 import java.sql.Connection;
 6 import java.sql.DriverManager;
 7 import java.sql.SQLException;
 8 import java.util.Properties;
 9 
10 
11 public class DBConnection {
12 
13     private static Connection conn = null;
14     private static Properties props = null;
15 
16     static {
17         props = new Properties();
18         try {
19             props.load(DBConnection.class.getResourceAsStream("/dbconf.properties"));
20         } catch (IOException e1) {
21             e1.printStackTrace();
22         }
23         try {
24             Class.forName(props.getProperty("driverClass"));
25         } catch (ClassNotFoundException e) {
26             e.printStackTrace();
27         }
28     }
29     
30 
31     public static Connection getConn(){
32         try {
33             conn = DriverManager.getConnection(props.getProperty("url"), props.getProperty("username"), props.getProperty("password"));
34             conn.setAutoCommit(false);
35         } catch (SQLException e) {
36             e.printStackTrace();
37         }
38         return conn;
39     }
40 
41     
42     
43     public void closeConn(){
44         try {
45             if (conn != null)
46                 conn.close();
47         } catch (SQLException e) {
48             e.printStackTrace();
49         }
50         
51     }
52 }

junit测试类

  1 package com.software.usermanager.test;
  2 
  3 import java.util.List;
  4 
  5 import com.software.usermanager.bean.Users;
  6 
  7 import org.apache.tomcat.jni.User;
  8 import org.junit.After;
  9 import org.junit.Before;
 10 import org.junit.Test;
 11 
 12 import com.software.usermanager.dao.UsersDAO;
 13 import com.software.usermanager.dao.UsersDAOImpl;
 14 import com.software.usermanager.db.DBConnection;
 15 import com.software.usermanager.util.OptTemplate;
 16 import com.software.usermanager.util.PageModel;
 17 
 18 public class UserTest {
 19     DBConnection dbConn = null;
 20 
 21     @Before
 22     public void setUp() {
 23         dbConn = new DBConnection();
 24     }
 25 
 26     @After
 27     public void tearDown() {
 28         dbConn.closeConn();
 29 
 30     }
 31     /************测试插入记录***************/
 32 
 33 //    @Test
 34 //    public void testinsert() {
 35 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
 36 //        for(int i=0;i<20;i++){
 37 //        Users u = new Users();
 38 //        u.setId(""+i);
 39 //        u.setName("郑六");
 40 //        u.setAge("2"+i);
 41 //        boolean b=usersdao.insert(u);
 42 //        if(b==false){
 43 //            System.out.println("插入失败");
 44 //        }else{
 45 //            System.out.println("插入成功");
 46 //        }}
 47 //        
 48 //
 49 //    }
 50     /************测试修改记录***************/
 51 
 52 //    @Test
 53 //    public void testupdate() {
 54 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
 55 //        Users u = new Users();
 56 //        u.setId("5");
 57 //        u.setName("郑六");
 58 //        u.setAge("21");
 59 //        boolean b=usersdao.update(u);
 60 //        if(b==false){
 61 //            System.out.println("更新失败");
 62 //        }else{
 63 //            System.out.println("更新成功");
 64 //        }
 65 //        
 66 //
 67 //    }
 68     /************测试删除单条记录***************/
 69 
 70 //    @Test
 71 //    public void testdeleteById() {
 72 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
 73 //        boolean b=usersdao.delete("2");
 74 //        if(b==false){
 75 //            System.out.println("删除失败");
 76 //        }else{
 77 //            System.out.println("删除成功");
 78 //        }
 79 //        
 80 //    }
 81     /************测试批量删除记录***************/
 82 //
 83 //    @Test
 84 //    public void testdeleteByArray() {
 85 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
 86 //        String[] s={"3","4","5"};
 87 //        boolean b=usersdao.delete(s);
 88 //        if(b==false){
 89 //            System.out.println("删除失败");
 90 //        }else{
 91 //            System.out.println("删除成功");
 92 //        }
 93 //        
 94 //    }
 95     /*********查询全部记录结果集为泛型 ************/
 96 //    @Test
 97 //    public void testqueryAll(){
 98 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
 99 //        List<Users> list=usersdao.query();
100 //        for(Users u:list){
101 //            System.out.println(u.getId());
102 //        }
103 //    }
104     /*********查询单条记录结果集为对象 ************/
105 //    @Test
106 //    public void testqueryAll(){
107 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
108 //        Users u=usersdao.query("7");
109 //        System.out.println(u.getName());
110 //        
111 //    }
112 //    /*********分页查询全部记录结果集为pagemodel************/
113 //    @Test
114 //    public void testqueryAll(){
115 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
116 //        PageModel pml=usersdao.query(2,2);
117 //        List<Users> list=pml.getList();
118 //        for(Users u:list){
119 //            System.out.println(u.getId());
120 //        }
121 //    }
122     /*********分页模糊查询全部记录结果集为pagemodel************/
123     @Test
124     public void testqueryAll(){
125         UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
126         PageModel pml=usersdao.query(1,2,"2");
127         List<Users> list=pml.getList();
128         for(Users u:list){
129             System.out.println(u.getId());
130         }
131     }
132 
133 }

注意:以下代码非本程序必须代码,仅供自己笔记之用

Filter

 1 package com.software.usermanager.filter;
 2 
 3 import java.io.IOException;
 4 
 5 import javax.servlet.Filter;
 6 import javax.servlet.FilterChain;
 7 import javax.servlet.FilterConfig;
 8 import javax.servlet.ServletException;
 9 import javax.servlet.ServletRequest;
10 import javax.servlet.ServletResponse;
11 
12     public class FilterEncoding implements Filter {
13 
14         private String encoding = "utf-8";
15         public void destroy() {
16 
17         }
18 
19         public void doFilter(ServletRequest request, ServletResponse response,
20                 FilterChain arg2) throws IOException, ServletException {
21             request.setCharacterEncoding(encoding);
22             response.setCharacterEncoding(encoding);
23             arg2.doFilter(request, response);
24 
25         }
26 
27         public void init(FilterConfig arg0) throws ServletException {
28             encoding = arg0.getInitParameter("encoding");
29         }
30 
31     }

Listener

 1 package com.software.usermanager.listener;
 2 
 3 import java.util.Date;
 4 
 5 import javax.servlet.ServletContextEvent;
 6 import javax.servlet.ServletContextListener;
 7 
 8 public class Listener implements ServletContextListener 
 9 {
10     public void contextDestroyed(ServletContextEvent event) 
11     {
12         // 销毁记录
13     }
14 
15     public void contextInitialized(ServletContextEvent event) 
16     {
17         // 记录登录信息
18         Date date = new Date();
19         event.getServletContext().log(date.toString());
20     }
21 }

web.xml配置文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
 3     <display-name>xkxt</display-name>
 4     <welcome-file-list>
 5         <welcome-file>index.jsp</welcome-file>        
 6     </welcome-file-list>
 7       <filter>
 8       <filter-name>Encoding</filter-name>
 9       <filter-class>com.software.usermanager.FilterEncoding</filter-class>
10  
11     <init-param>
12     <param-name>encoding</param-name>
13     <param-value>UTF-8</param-value>
14     </init-param>
15      </filter>
16        <filter-mapping>
17        <filter-name>Encoding</filter-name>
18        <url-pattern>/*</url-pattern>
19   </filter-mapping>
20     
21     <listener>
22         <listener-class>com.software.usermanager.Listener</listener-class>
23     </listener>
24     <servlet>
25        <servlet-name>xkxt</servlet-name>
26        <servlet-class>com.software.usermanager.XKXTServlet</servlet-class>
27     </servlet>
28     <servlet-mapping>
29       <servlet-name>xkxt</servlet-name>
30       <url-pattern>/action</url-pattern>
31     </servlet-mapping>
32         <servlet>
33        <servlet-name>querydqm</servlet-name>
34        <servlet-class>com.software.usermanager.DQMServlet</servlet-class>
35     </servlet>
36     <servlet-mapping>
37       <servlet-name>querydqm</servlet-name>
38       <url-pattern>/action</url-pattern>
39     </servlet-mapping>
40   
41   
42     
43     
44     
45 </web-app>
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/xingmeng/p/3274372.html