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

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

阅读量：6094 次

发布时间：2019-06-20

本文共 17255 字，大约阅读时间需要 57 分钟。

程序清单一览

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
     
       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
     
       query() { 48         String sql = "select * from users"; 49         Object[] obj = {}; 50         return (List
      
       ) 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
       
         list1 = (List
        
         ) 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
         
           list = (List
          
           ) 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
           
             list1 = (List
            
             ) 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
             
               list = (List
              
               ) 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
     
       query(String sql,Object[] obj,ObjectMapper mapper){ 41         Object o=null; 42         List

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
     
       { 6      7     //结果集 8     private List
      
        list; 9     10     //记录数11     private int totalRecords;12     13     //每页多少条数据14     private int pageSize;15     16     //第几页17     private int pageNo;18 19     /**20      * 返回总页数21      * @return22      */23     public int getTotalPages() {24         return (totalRecords + pageSize - 1) / pageSize;25     }26     27     /**28      * 首页29      * @return30      */31     public int getTopPageNo() {32         return 1;33     }34     35     /**36      * 上一页 37      * @return38      */39     public int getPreviousPageNo() {40         if (this.pageNo <= 1) {41             return 1;42         }43         return this.pageNo - 1;44     }45     46     /**47      * 下一页48      * @return49      */50     public int getNextPageNo() {51         if (this.pageNo >= getButtomPageNo()) {52             return getButtomPageNo();53         }54         return this.pageNo + 1;55     }56     57     /**58      * 尾页59      * @return60      */61     public int getButtomPageNo() {62         return getTotalPages();63     }64     65     public List
       
         getList() {66         return list;67     }68 69     public void setList(List
        
          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.OracleDriver3 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
     
       list=usersdao.query();100 //        for(Users u:list){101 //            System.out.println(u.getId());102 //        }103 //    }104     /*********查询单条记录结果集为对象 ************/105 //    @Test106 //    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 //    @Test114 //    public void testqueryAll(){115 //        UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());116 //        PageModel pml=usersdao.query(2,2);117 //        List
      
        list=pml.getList();118 //        for(Users u:list){119 //            System.out.println(u.getId());120 //        }121 //    }122     /*********分页模糊查询全部记录结果集为pagemodel************/123     @Test124     public void testqueryAll(){125         UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());126         PageModel pml=usersdao.query(1,2,"2");127         List
       
         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 
      2 
     
       3     
      
       xkxt
       4     
      
        5         
       
        index.jsp
                6     
       7       
      
        8       
       
        Encoding
        9       
       
        com.software.usermanager.FilterEncoding
       10  11     
       
        12     
        
         encoding
        13     
        
         UTF-8
        14     
       15      
      16        
      
       17        
       
        Encoding
       18        
       
        /*
       19   
      20     21     
      
       22         
       
        com.software.usermanager.Listener
       23     
      24     
      
       25        
       
        xkxt
       26        
       
        com.software.usermanager.XKXTServlet
       27     
      28     
      
       29       
       
        xkxt
       30       
       
        /action
       31     
      32         
      
       33        
       
        querydqm
       34        
       
        com.software.usermanager.DQMServlet
       35     
      36     
      
       37       
       
        querydqm
       38       
       
        /action
       39     
      40   41   42     43     44     45