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 }

ConfigManager

  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 }

BaseDao

 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 }

PageUtil

  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 }

dao层