jdbc访问数据库

//单例 读取配置文件的工具类
public class ConfigManager {

    // 01.创建自身的静态对象
    private static ConfigManager manager = new ConfigManager();
    private static Properties properties;

    // 02.私有化构造
    private ConfigManager() {
        // 获取配置文件的路径
        String path = "jdbc.properties";
        properties = new Properties();
        // 创建输入流
        InputStream stream = ConfigManager.class.getClassLoader()
                .getResourceAsStream(path);
        try {
            properties.load(stream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                stream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    // 03.提供供外部访问的接口
    public static synchronized ConfigManager getInstance() {
        return manager;
    }

    // 提供一个 根据key取得value的方法
    public static String getValue(String key) {
        return properties.getProperty(key);
    }

}

/**
 *  连接数据库 通用的 工具类
 *
 */
public class BaseDao {
    // 创建需要得到JDBC API
    protected static Connection connection = null;
    protected static PreparedStatement ps = null;
    protected static ResultSet rs = null;

    // 01.获取数据库连接
    public static boolean getConnection() {
        /**
         * 获取数据库连接的4要素   
         * 连接数据库的前提
         */
        String driver = ConfigManager.getInstance().getValue("jdbc.driver");
        String url = ConfigManager.getInstance().getValue("jdbc.url");
        String userName = ConfigManager.getInstance().getValue("jdbc.userName");
        String password = ConfigManager.getInstance().getValue("jdbc.password");

        try {
            Class.forName(driver); // 加载驱动
            connection = DriverManager.getConnection(url, userName, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 03.增删改  executeUpdate() 返回int  代表影响数据库中的行数
     *  delete from user;
     *  delete from user where  id=? and name=?;
     */
    public static int executeUpdate(String sql, Object... params) {
        int rowNum = 0;
        if (getConnection()) { // 操作数据库 肯定现有连接
            try {
                ps = connection.prepareStatement(sql);
                // 循环给sql语句中的？占位符 赋值
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
                // 执行sql语句
                rowNum = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeConnection(); // 关闭连接
            }

        }

        return rowNum;
    }

    /**
     * 04.查询  executeQuery() 返回ResultSet  
     *  select * from user;
     *  select * from user where  id=? and name=?;
     */
    public static ResultSet executeQuery(String sql, Object... params) {
        if (getConnection()) { // 操作数据库 肯定现有连接
            try {
                ps = connection.prepareStatement(sql);
                // 循环给sql语句中的？占位符 赋值
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
                // 执行sql语句
                rs = ps.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rs;
    }

    // 02.释放资源
    public static boolean closeConnection() {
        // 如果对象都没有创建 ？ 能关闭吗？ 必须进行非空判断
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

}

/**
 * 2017-2-7下午4:31:18
 * 
 * 分页显示的工具类
 */
public class PageUtil {
    private Integer pageIndex;// 当前页数
    private Integer pageSize;// 每页显示的条数
    private Integer totalCountSize;// 总记录数
    private Integer totalPageCount;// 总页数

    public Integer getPageIndex() {
        return pageIndex;
    }

    public void setPageIndex(Integer pageIndex) {
        this.pageIndex = pageIndex;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSizeInteger(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalCountSize() {
        return totalCountSize;
    }

    public void setTotalCountSize(Integer totalCountSize) {
        if (totalCountSize > 0) {// 如果总记录数大于0，可以进行分页
            this.totalCountSize = totalCountSize;
            // 计算总页数
            totalPageCount = totalCountSize % pageSize == 0 ? (totalCountSize / pageSize)
                    : (totalCountSize / pageSize + 1);
        }
    }

    public Integer getTotalPageCount() {
        return totalPageCount;
    }

    public void setTotalPageCount(Integer totalPageCount) {
        this.totalPageCount = totalPageCount;
    }

}

/**
 * 2017-5-23上午11:58:03
 * 
 */
public class BookDaoImpl extends BaseDao implements BookDao {
    // 查询数据库中所有的新闻信息
    public List<Books> findBooksList() {
        String sql = "select * from book_info";
        List<Books> books = new ArrayList<Books>();
        rs = executeQuery(sql);
        try {
            while (rs.next()) {
                Books book = new Books();
                book.setBookId(rs.getInt("bookId"));
                book.setBookCde(rs.getString("bookCde"));
                book.setBookName(rs.getString("bookName"));
                book.setBookType(rs.getInt("bookType"));
                book.setBookAuthor(rs.getString("bookAuthor"));
                book.setPublishPress(rs.getString("publishPress"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setBorrowed(rs.getInt("borrowed"));
                book.setCreatedBy(rs.getString("createdBy"));
                book.setCreationTime(rs.getDate("creationTime"));
                book.setLastUpdateTime(rs.getDate("lastUpdateTime"));
                books.add(book);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return books;
    }

    // 查询指定的新闻信息
    public Books findBooksById(Books books) {
        String sql = "select * from book_info where id=?";
        Object[] params = { books.getBookId() };
        rs = executeQuery(sql, params);
        Books book = null;
        try {
            while (rs.next()) {
                book = new Books();
                book.setBookId(rs.getInt("bookId"));
                book.setBookCde(rs.getString("bookCde"));
                book.setBookName(rs.getString("bookName"));
                book.setBookType(rs.getInt("bookType"));
                book.setBookAuthor(rs.getString("bookAuthor"));
                book.setPublishPress(rs.getString("publishPress"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setBorrowed(rs.getInt("borrowed"));
                book.setCreatedBy(rs.getString("createdBy"));
                book.setCreationTime(rs.getDate("creationTime"));
                book.setLastUpdateTime(rs.getDate("lastUpdateTime"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return book;
    }

    // 删除指定的新闻信息
    public int deleteBooksById(Books books) {
        String sql = "delete * from book_info where id=?";
        Object[] params = { books.getBookId() };
        int rows = executeUpdate(sql, params);

        return rows;
    }

    // 修改指定的新闻信息
    public int updateBooksById(Books books) {
        String sql = "update book_info set bookCde=?,bookName=?,bookType=?,"
                + "bookAuthor=?,publishPress=?,borrowed=?,createdBy=?,creationTime=?";
        Object[] params = { books.getBookCde(), books.getBookName(),
                books.getBookType(), books.getBookAuthor(),
                books.getPublishPress(), books.getBorrowed(),
                books.getCreatedBy(), books.getCreationTime() };
        return executeUpdate(sql, params);
    }

    // 新增新闻信息
    public int addBooks(Books books) {
        String sql = "insert into book_info set bookCde=?,bookName=?,bookType=?,"
                + "bookAuthor=?,publishPress=?,borrowed=?,createdBy=?,creationTime=?";
        Object[] params = { books.getBookCde(), books.getBookName(),
                books.getBookType(), books.getBookAuthor(),
                books.getPublishPress(), books.getBorrowed(),
                books.getCreatedBy(), books.getCreationTime() };
        return executeUpdate(sql, params);
    }

    // 获取总记录数
    public int getTotalCountSize() {
        String sql = "select count(1) as count from book_info";
        rs = executeQuery(sql);
        int totalCount = 0;
        try {
            while (rs.next()) {
                totalCount = rs.getInt("count");

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return totalCount;
    }

    // 分页查询
    public List<Books> getBooksByPageList(int pageIndex, int pageSize) {
        String sql = "select * from book_info limit ?,?";
        List<Books> list = new ArrayList<Books>();
        pageIndex = (pageIndex - 1) * pageSize;
        Object[] params = { pageIndex, pageSize };
        rs = executeQuery(sql, params);
        try {
            while (rs.next()) {
                Books book = new Books();
                book.setBookId(rs.getInt("bookId"));
                book.setBookCde(rs.getString("bookCde"));
                book.setBookName(rs.getString("bookName"));
                book.setBookType(rs.getInt("bookType"));
                book.setBookAuthor(rs.getString("bookAuthor"));
                book.setPublishPress(rs.getString("publishPress"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setBorrowed(rs.getInt("borrowed"));
                book.setCreatedBy(rs.getString("createdBy"));
                book.setCreationTime(rs.getDate("creationTime"));
                book.setLastUpdateTime(rs.getDate("lastUpdateTime"));
                list.add(book);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

}