jdbc访问数据库详解数据库

 1 //单例 读取配置文件的工具类 
 2 public class ConfigManager { 
 3  
 4     // 01.创建自身的静态对象 
 5     private static ConfigManager manager = new ConfigManager(); 
 6     private static Properties properties; 
 7  
 8     // 02.私有化构造 
 9     private ConfigManager() { 
10         // 获取配置文件的路径 
11         String path = "jdbc.properties"; 
12         properties = new Properties(); 
13         // 创建输入流 
14         InputStream stream = ConfigManager.class.getClassLoader() 
15                 .getResourceAsStream(path); 
16         try { 
17             properties.load(stream); 
18         } catch (IOException e) { 
19             e.printStackTrace(); 
20         } finally { 
21             try { 
22                 stream.close(); 
23             } catch (IOException e) { 
24                 e.printStackTrace(); 
25             } 
26         } 
27  
28     } 
29  
30     // 03.提供供外部访问的接口 
31     public static synchronized ConfigManager getInstance() { 
32         return manager; 
33     } 
34  
35     // 提供一个 根据key取得value的方法 
36     public static String getValue(String key) { 
37         return properties.getProperty(key); 
38     } 
39  
40 }

  1 /** 
  2  *  连接数据库 通用的 工具类 
  3  * 
  4  */ 
  5 public class BaseDao { 
  6     // 创建需要得到JDBC API 
  7     protected static Connection connection = null; 
  8     protected static PreparedStatement ps = null; 
  9     protected static ResultSet rs = null; 
 10  
 11     // 01.获取数据库连接 
 12     public static boolean getConnection() { 
 13         /** 
 14          * 获取数据库连接的4要素    
 15          * 连接数据库的前提 
 16          */ 
 17         String driver = ConfigManager.getInstance().getValue("jdbc.driver"); 
 18         String url = ConfigManager.getInstance().getValue("jdbc.url"); 
 19         String userName = ConfigManager.getInstance().getValue("jdbc.userName"); 
 20         String password = ConfigManager.getInstance().getValue("jdbc.password"); 
 21  
 22         try { 
 23             Class.forName(driver); // 加载驱动 
 24             connection = DriverManager.getConnection(url, userName, password); 
 25         } catch (ClassNotFoundException e) { 
 26             e.printStackTrace(); 
 27             return false; 
 28         } catch (SQLException e) { 
 29             e.printStackTrace(); 
 30             return false; 
 31         } 
 32         return true; 
 33     } 
 34  
 35     /** 
 36      * 03.增删改  executeUpdate() 返回int  代表影响数据库中的行数 
 37      *  delete from user; 
 38      *  delete from user where  id=? and name=?; 
 39      */ 
 40     public static int executeUpdate(String sql, Object... params) { 
 41         int rowNum = 0; 
 42         if (getConnection()) { // 操作数据库 肯定现有连接 
 43             try { 
 44                 ps = connection.prepareStatement(sql); 
 45                 // 循环给sql语句中的？占位符 赋值 
 46                 for (int i = 0; i < params.length; i++) { 
 47                     ps.setObject(i + 1, params[i]); 
 48                 } 
 49                 // 执行sql语句 
 50                 rowNum = ps.executeUpdate(); 
 51             } catch (SQLException e) { 
 52                 e.printStackTrace(); 
 53             } finally { 
 54                 closeConnection(); // 关闭连接 
 55             } 
 56  
 57         } 
 58  
 59         return rowNum; 
 60     } 
 61  
 62     /** 
 63      * 04.查询  executeQuery() 返回ResultSet   
 64      *  select * from user; 
 65      *  select * from user where  id=? and name=?; 
 66      */ 
 67     public static ResultSet executeQuery(String sql, Object... params) { 
 68         if (getConnection()) { // 操作数据库 肯定现有连接 
 69             try { 
 70                 ps = connection.prepareStatement(sql); 
 71                 // 循环给sql语句中的？占位符 赋值 
 72                 for (int i = 0; i < params.length; i++) { 
 73                     ps.setObject(i + 1, params[i]); 
 74                 } 
 75                 // 执行sql语句 
 76                 rs = ps.executeQuery(); 
 77             } catch (SQLException e) { 
 78                 e.printStackTrace(); 
 79             } 
 80         } 
 81         return rs; 
 82     } 
 83  
 84     // 02.释放资源 
 85     public static boolean closeConnection() { 
 86         // 如果对象都没有创建 ？ 能关闭吗？ 必须进行非空判断 
 87         if (rs != null) { 
 88             try { 
 89                 rs.close(); 
 90             } catch (SQLException e) { 
 91                 e.printStackTrace(); 
 92                 return false; 
 93             } 
 94         } 
 95         if (ps != null) { 
 96             try { 
 97                 ps.close(); 
 98             } catch (SQLException e) { 
 99                 e.printStackTrace(); 
100                 return false; 
101             } 
102         } 
103         if (connection != null) { 
104             try { 
105                 connection.close(); 
106             } catch (SQLException e) { 
107                 e.printStackTrace(); 
108                 return false; 
109             } 
110         } 
111         return true; 
112     } 
113  
114 }

 1 /** 
 2  * 2017-2-7下午4:31:18 
 3  *  
 4  * 分页显示的工具类 
 5  */ 
 6 public class PageUtil { 
 7     private Integer pageIndex;// 当前页数 
 8     private Integer pageSize;// 每页显示的条数 
 9     private Integer totalCountSize;// 总记录数 
10     private Integer totalPageCount;// 总页数 
11  
12     public Integer getPageIndex() { 
13         return pageIndex; 
14     } 
15  
16     public void setPageIndex(Integer pageIndex) { 
17         this.pageIndex = pageIndex; 
18     } 
19  
20     public Integer getPageSize() { 
21         return pageSize; 
22     } 
23  
24     public void setPageSizeInteger(Integer pageSize) { 
25         this.pageSize = pageSize; 
26     } 
27  
28     public Integer getTotalCountSize() { 
29         return totalCountSize; 
30     } 
31  
32     public void setTotalCountSize(Integer totalCountSize) { 
33         if (totalCountSize > 0) {// 如果总记录数大于0，可以进行分页 
34             this.totalCountSize = totalCountSize; 
35             // 计算总页数 
36             totalPageCount = totalCountSize % pageSize == 0 ? (totalCountSize / pageSize) 
37                     : (totalCountSize / pageSize + 1); 
38         } 
39     } 
40  
41     public Integer getTotalPageCount() { 
42         return totalPageCount; 
43     } 
44  
45     public void setTotalPageCount(Integer totalPageCount) { 
46         this.totalPageCount = totalPageCount; 
47     } 
48  
49 }

  1 /** 
  2  * 2017-5-23上午11:58:03 
  3  *  
  4  */ 
  5 public class BookDaoImpl extends BaseDao implements BookDao { 
  6     // 查询数据库中所有的新闻信息 
  7     public List<Books> findBooksList() { 
  8         String sql = "select * from book_info"; 
  9         List<Books> books = new ArrayList<Books>(); 
 10         rs = executeQuery(sql); 
 11         try { 
 12             while (rs.next()) { 
 13                 Books book = new Books(); 
 14                 book.setBookId(rs.getInt("bookId")); 
 15                 book.setBookCde(rs.getString("bookCde")); 
 16                 book.setBookName(rs.getString("bookName")); 
 17                 book.setBookType(rs.getInt("bookType")); 
 18                 book.setBookAuthor(rs.getString("bookAuthor")); 
 19                 book.setPublishPress(rs.getString("publishPress")); 
 20                 book.setPublishDate(rs.getDate("publishDate")); 
 21                 book.setBorrowed(rs.getInt("borrowed")); 
 22                 book.setCreatedBy(rs.getString("createdBy")); 
 23                 book.setCreationTime(rs.getDate("creationTime")); 
 24                 book.setLastUpdateTime(rs.getDate("lastUpdateTime")); 
 25                 books.add(book); 
 26             } 
 27         } catch (SQLException e) { 
 28             // TODO Auto-generated catch block 
 29             e.printStackTrace(); 
 30         } 
 31         return books; 
 32     } 
 33  
 34     // 查询指定的新闻信息 
 35     public Books findBooksById(Books books) { 
 36         String sql = "select * from book_info where id=?"; 
 37         Object[] params = { books.getBookId() }; 
 38         rs = executeQuery(sql, params); 
 39         Books book = null; 
 40         try { 
 41             while (rs.next()) { 
 42                 book = new Books(); 
 43                 book.setBookId(rs.getInt("bookId")); 
 44                 book.setBookCde(rs.getString("bookCde")); 
 45                 book.setBookName(rs.getString("bookName")); 
 46                 book.setBookType(rs.getInt("bookType")); 
 47                 book.setBookAuthor(rs.getString("bookAuthor")); 
 48                 book.setPublishPress(rs.getString("publishPress")); 
 49                 book.setPublishDate(rs.getDate("publishDate")); 
 50                 book.setBorrowed(rs.getInt("borrowed")); 
 51                 book.setCreatedBy(rs.getString("createdBy")); 
 52                 book.setCreationTime(rs.getDate("creationTime")); 
 53                 book.setLastUpdateTime(rs.getDate("lastUpdateTime")); 
 54             } 
 55         } catch (SQLException e) { 
 56             // TODO Auto-generated catch block 
 57             e.printStackTrace(); 
 58         } 
 59         return book; 
 60     } 
 61  
 62     // 删除指定的新闻信息 
 63     public int deleteBooksById(Books books) { 
 64         String sql = "delete * from book_info where id=?"; 
 65         Object[] params = { books.getBookId() }; 
 66         int rows = executeUpdate(sql, params); 
 67  
 68         return rows; 
 69     } 
 70  
 71     // 修改指定的新闻信息 
 72     public int updateBooksById(Books books) { 
 73         String sql = "update book_info set bookCde=?,bookName=?,bookType=?," 
 74                 + "bookAuthor=?,publishPress=?,borrowed=?,createdBy=?,creationTime=?"; 
 75         Object[] params = { books.getBookCde(), books.getBookName(), 
 76                 books.getBookType(), books.getBookAuthor(), 
 77                 books.getPublishPress(), books.getBorrowed(), 
 78                 books.getCreatedBy(), books.getCreationTime() }; 
 79         return executeUpdate(sql, params); 
 80     } 
 81  
 82     // 新增新闻信息 
 83     public int addBooks(Books books) { 
 84         String sql = "insert into book_info set bookCde=?,bookName=?,bookType=?," 
 85                 + "bookAuthor=?,publishPress=?,borrowed=?,createdBy=?,creationTime=?"; 
 86         Object[] params = { books.getBookCde(), books.getBookName(), 
 87                 books.getBookType(), books.getBookAuthor(), 
 88                 books.getPublishPress(), books.getBorrowed(), 
 89                 books.getCreatedBy(), books.getCreationTime() }; 
 90         return executeUpdate(sql, params); 
 91     } 
 92  
 93     // 获取总记录数 
 94     public int getTotalCountSize() { 
 95         String sql = "select count(1) as count from book_info"; 
 96         rs = executeQuery(sql); 
 97         int totalCount = 0; 
 98         try { 
 99             while (rs.next()) { 
100                 totalCount = rs.getInt("count"); 
101  
102             } 
103         } catch (SQLException e) { 
104             // TODO Auto-generated catch block 
105             e.printStackTrace(); 
106         } 
107         return totalCount; 
108     } 
109  
110     // 分页查询 
111     public List<Books> getBooksByPageList(int pageIndex, int pageSize) { 
112         String sql = "select * from book_info limit ?,?"; 
113         List<Books> list = new ArrayList<Books>(); 
114         pageIndex = (pageIndex - 1) * pageSize; 
115         Object[] params = { pageIndex, pageSize }; 
116         rs = executeQuery(sql, params); 
117         try { 
118             while (rs.next()) { 
119                 Books book = new Books(); 
120                 book.setBookId(rs.getInt("bookId")); 
121                 book.setBookCde(rs.getString("bookCde")); 
122                 book.setBookName(rs.getString("bookName")); 
123                 book.setBookType(rs.getInt("bookType")); 
124                 book.setBookAuthor(rs.getString("bookAuthor")); 
125                 book.setPublishPress(rs.getString("publishPress")); 
126                 book.setPublishDate(rs.getDate("publishDate")); 
127                 book.setBorrowed(rs.getInt("borrowed")); 
128                 book.setCreatedBy(rs.getString("createdBy")); 
129                 book.setCreationTime(rs.getDate("creationTime")); 
130                 book.setLastUpdateTime(rs.getDate("lastUpdateTime")); 
131                 list.add(book); 
132             } 
133         } catch (SQLException e) { 
134             // TODO Auto-generated catch block 
135             e.printStackTrace(); 
136         } 
137         return list; 
138     } 
139  
140 }