Java-留言板-服务接口的JDBC实现

服务接口的JDBC实现
系统辅助类 DBUtil.java    
查看复制到剪切板打印
package net.java2000.notepad.service.impl.jdbc;        
    
import java.sql.Connection;        
import java.sql.DriverManager;        
import java.sql.ResultSet;        
import java.sql.Statement;        
    
/**    
* 自定义的数据库连接类    
*        
* @author 赵学庆，Java世纪网([url]http://[/url])    
*        
*/    
public class DBUtil {        
    // 数据库的名字        
    String dbName = "notepad";        
    // 登录数据库的用户名        
    String username = "notepad";        
    // 登录数据库的密码        
    String password = "notepad";        
    // 数据库的IP地址，本机可以用 localhost 或者 127.0.0.1        
    String host = "localhost";        
    // 数据库的端口，一般不会修改，默认为1433        
    int port = 3306;        
    String connectionUrl = "jdbc:mysql://" + host + ":" + port + "/" + dbName        
            + "?useUnicode=true&characterEncoding=UTF-8";        
    
    /**    
     * 私有的构造器    
     */    
    private DBUtil() {        
        // 注册驱动        
        try {        
            Class.forName("com.mysql.jdbc.Driver");        
        } catch (ClassNotFoundException e) {        
            e.printStackTrace();        
        }        
    }        
    
    // 初始化的单例类        
    private static final DBUtil countDB = new DBUtil();        
    
    /**    
     * 获得实例的方法    
     *        
     * @return 实例    
     */    
    public static DBUtil getInstance() {        
        return countDB;        
    }        
    
    /**    
     * 获得数据库连接    
     *        
     * @return 数据库的链接，失败返回null    
     */    
    public Connection getConnection() {        
        try {        
            return DriverManager.getConnection(connectionUrl, username, password);        
        } catch (Exception ex) {        
            ex.printStackTrace();        
            return null;        
        }        
    }        
    
    /**    
     * 关闭资源    
     *        
     * @param con    
     *                    数据库连接    
     * @param stmt    
     *                    Statement    
     * @param rs    
     *                    结果集    
     */    
    public void close(Connection con, Statement stmt, ResultSet rs) {        
        // 关闭我们使用过的资源        
        if (rs != null)        
            try {        
                rs.close();        
            } catch (Exception e) {        
            }        
        if (stmt != null)        
            try {        
                stmt.close();        
            } catch (Exception e) {        
            }        
        if (con != null)        
            try {        
                con.close();        
            } catch (Exception e) {        
            }        
    }        
}    

package net.java2000.notepad.service.impl.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
* 自定义的数据库连接类
*    
* @author 赵学庆，Java世纪网([url]http://[/url])
*    
*/
public class DBUtil {
    // 数据库的名字
    String dbName = "notepad";
    // 登录数据库的用户名
    String username = "notepad";
    // 登录数据库的密码
    String password = "notepad";
    // 数据库的IP地址，本机可以用 localhost 或者 127.0.0.1
    String host = "localhost";
    // 数据库的端口，一般不会修改，默认为1433
    int port = 3306;
    String connectionUrl = "jdbc:mysql://" + host + ":" + port + "/" + dbName
            + "?useUnicode=true&characterEncoding=UTF-8";

    /**
     * 私有的构造器
     */
    private DBUtil() {
        // 注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 初始化的单例类
    private static final DBUtil countDB = new DBUtil();

    /**
     * 获得实例的方法
     *    
     * @return 实例
     */
    public static DBUtil getInstance() {
        return countDB;
    }

    /**
     * 获得数据库连接
     *    
     * @return 数据库的链接，失败返回null
     */
    public Connection getConnection() {
        try {
            return DriverManager.getConnection(connectionUrl, username, password);
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    /**
     * 关闭资源
     *    
     * @param con
     *                    数据库连接
     * @param stmt
     *                    Statement
     * @param rs
     *                    结果集
     */
    public void close(Connection con, Statement stmt, ResultSet rs) {
        // 关闭我们使用过的资源
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
            }
        if (stmt != null)
            try {
                stmt.close();
            } catch (Exception e) {
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
            }
    }
}


UserServiceJDBCImpl.java    
查看复制到剪切板打印
package net.java2000.notepad.service.impl.jdbc;        
    
import java.sql.Connection;        
import java.sql.PreparedStatement;        
import java.sql.ResultSet;        
import java.sql.SQLException;        
import java.sql.Statement;        
    
import net.java2000.notepad.User;        
import net.java2000.notepad.service.UserService;        
    
/**    
* 用户服务的JDBC实现版本    
*        
* @author 赵学庆，Java世纪网([url]http://[/url])    
*        
*/    
public class UserServiceJDBCImpl implements UserService {        
    // 公用一个连接辅助类        
    private static final DBUtil db = DBUtil.getInstance();        
    
    /**    
     * 从结果集拿到对象    
     *        
     * @param rs    
     *                    结果集    
     * @return 成功返回对象    
     * @throws SQLException    
     *                     数据库异常    
     */    
    private User getFromResult(ResultSet rs) throws SQLException {        
        User o = new User();        
        o.setId(rs.getLong("id"));        
        o.setUsername(rs.getString("username"));        
        o.setPassword(rs.getString("password"));        
        return o;        
    }        
    
    @Override    
    public User get(long id) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "SELECT * from T_User where id=?";        
            // 创建查询        
            stmt = con.prepareStatement(SQL);        
            // 设置参数        
            stmt.setLong(1, id);        
            // 执行查询，拿到结果集        
            rs = stmt.executeQuery();        
            // 返回        
            if (rs.next()) {        
                return getFromResult(rs);        
            } else {        
                return null;        
            }        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    @Override    
    public User findByUsername(String username) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "SELECT * from T_User where username=?";        
            // 创建查询        
            stmt = con.prepareStatement(SQL);        
            // 设置参数        
            stmt.setString(1, username);        
            // 执行查询，拿到结果集        
            rs = stmt.executeQuery();        
            // 返回        
            if (rs.next()) {        
                return getFromResult(rs);        
            } else {        
                return null;        
            }        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    @Override    
    public boolean checkLogin(String username, String password) {        
        User user = findByUsername(username);        
        return user == null ? false : user.getPassword().equals(password);        
    }        
    
    @Override    
    public User save(User user) {        
        if (user.getId() <= 0) {        
            return insert(user);        
        } else {        
            return update(user);        
        }        
    }        
    
    /**    
     * 增加一个用户    
     *        
     * @param post    
     *                    用户    
     * @return 成功返回用户，失败返回null    
     */    
    private User insert(User user) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "insert into T_User (username,password) values (?,?)";        
            // 创建查询        
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);        
            // 设置参数        
            stmt.setString(1, user.getUsername());        
            stmt.setString(2, user.getPassword());        
            if (stmt.executeUpdate() == 1) {        
                rs = stmt.getGeneratedKeys(); // 用来获得生成的数值        
                if (rs.next()) {        
                    user.setId(Long.parseLong(rs.getString(1))); // 拿到数值        
                    return user;        
                }        
            }        
    
            return null;        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    /**    
     * 更新一个用户    
     *        
     * @param post    
     *                    用户    
     * @return 成功返回用户，失败返回null    
     */    
    private User update(User user) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "update T_User set password=? where id=?";        
            // 创建查询        
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);        
            // 设置参数        
            stmt.setString(1, user.getPassword());        
            stmt.setLong(2, user.getId());        
            if (stmt.executeUpdate() == 1) {        
                return user;        
            }        
            return null;        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
}    

package net.java2000.notepad.service.impl.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.java2000.notepad.User;
import net.java2000.notepad.service.UserService;

/**
* 用户服务的JDBC实现版本
*    
* @author 赵学庆，Java世纪网([url]http://[/url])
*    
*/
public class UserServiceJDBCImpl implements UserService {
    // 公用一个连接辅助类
    private static final DBUtil db = DBUtil.getInstance();

    /**
     * 从结果集拿到对象
     *    
     * @param rs
     *                    结果集
     * @return 成功返回对象
     * @throws SQLException
     *                     数据库异常
     */
    private User getFromResult(ResultSet rs) throws SQLException {
        User o = new User();
        o.setId(rs.getLong("id"));
        o.setUsername(rs.getString("username"));
        o.setPassword(rs.getString("password"));
        return o;
    }

    @Override
    public User get(long id) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "SELECT * from T_User where id=?";
            // 创建查询
            stmt = con.prepareStatement(SQL);
            // 设置参数
            stmt.setLong(1, id);
            // 执行查询，拿到结果集
            rs = stmt.executeQuery();
            // 返回
            if (rs.next()) {
                return getFromResult(rs);
            } else {
                return null;
            }
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    @Override
    public User findByUsername(String username) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "SELECT * from T_User where username=?";
            // 创建查询
            stmt = con.prepareStatement(SQL);
            // 设置参数
            stmt.setString(1, username);
            // 执行查询，拿到结果集
            rs = stmt.executeQuery();
            // 返回
            if (rs.next()) {
                return getFromResult(rs);
            } else {
                return null;
            }
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    @Override
    public boolean checkLogin(String username, String password) {
        User user = findByUsername(username);
        return user == null ? false : user.getPassword().equals(password);
    }

    @Override
    public User save(User user) {
        if (user.getId() <= 0) {
            return insert(user);
        } else {
            return update(user);
        }
    }

    /**
     * 增加一个用户
     *    
     * @param post
     *                    用户
     * @return 成功返回用户，失败返回null
     */
    private User insert(User user) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "insert into T_User (username,password) values (?,?)";
            // 创建查询
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
            // 设置参数
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            if (stmt.executeUpdate() == 1) {
                rs = stmt.getGeneratedKeys(); // 用来获得生成的数值
                if (rs.next()) {
                    user.setId(Long.parseLong(rs.getString(1))); // 拿到数值
                    return user;
                }
            }

            return null;
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    /**
     * 更新一个用户
     *    
     * @param post
     *                    用户
     * @return 成功返回用户，失败返回null
     */
    private User update(User user) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "update T_User set password=? where id=?";
            // 创建查询
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
            // 设置参数
            stmt.setString(1, user.getPassword());
            stmt.setLong(2, user.getId());
            if (stmt.executeUpdate() == 1) {
                return user;
            }
            return null;
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }
}

PostServiceJDBCImpl.java    
查看复制到剪切板打印
package net.java2000.notepad.service.impl.jdbc;        
    
import java.sql.Connection;        
import java.sql.PreparedStatement;        
import java.sql.ResultSet;        
import java.sql.SQLException;        
import java.sql.Statement;        
import java.util.ArrayList;        
import java.util.List;        
    
import net.java2000.notepad.Post;        
import net.java2000.notepad.service.PostService;        
import net.java2000.notepad.service.UserService;        
    
/**    
* 留言服务的JDBC实现版本    
*        
* @author 赵学庆，Java世纪网([url]http://[/url])    
*        
*/    
public class PostServiceJDBCImpl implements PostService {        
    
    // 公用一个连接辅助类        
    private static final DBUtil db = DBUtil.getInstance();        
    
    private static final UserService userService = new UserServiceJDBCImpl();        
    
    @Override    
    public int countAllSubject() {        
        Connection con = null;        
    
        Statement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return -1;        
            }        
    
            final String SQL = "SELECT count(*) from T_Post where idParent=0";        
            // 创建查询        
            stmt = con.createStatement();        
            // 执行查询，拿到结果集        
            rs = stmt.executeQuery(SQL);        
            if (rs.next()) {        
                return rs.getInt(1);        
            } else {        
                return 0;        
            }        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return -1;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    /**    
     * 从结果集拿到对象    
     *        
     * @param rs    
     *                    结果集    
     * @return 成功返回对象    
     * @throws SQLException    
     *                     数据库异常    
     */    
    private Post getFromResult(ResultSet rs) throws SQLException {        
        Post o = new Post();        
        o.setId(rs.getLong("id"));        
        o.setIdParent(rs.getLong("idParent"));        
        o.setUser(userService.get(rs.getLong("userId")));        
        o.setTitle(rs.getString("title"));        
        o.setContent(rs.getString("content"));        
        o.setDatetime(rs.getTimestamp("datetime"));        
        return o;        
    }        
    
    @Override    
    public List<Post> findReplyOfSubject(long postId) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "SELECT * from T_Post where idParent=?";        
            // 创建查询        
            stmt = con.prepareStatement(SQL);        
            // 设置参数        
            stmt.setLong(1, postId);        
            // 执行查询，拿到结果集        
            rs = stmt.executeQuery();        
            // 创建返回的列表        
            List<Post> list = new ArrayList<Post>();        
            while (rs.next()) {        
                list.add(getFromResult(rs));        
            }        
            return list;        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    @Override    
    public List<Post> findSubject(int begin, int number) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "SELECT * from T_Post where idParent=0 limit ?,?";        
            // 创建查询        
            stmt = con.prepareStatement(SQL);        
            // 设置参数        
            stmt.setLong(1, begin);        
            stmt.setLong(2, number);        
            // 执行查询，拿到结果集        
            rs = stmt.executeQuery();        
            // 创建返回的列表        
            List<Post> list = new ArrayList<Post>();        
            while (rs.next()) {        
                list.add(getFromResult(rs));        
            }        
            return list;        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    @Override    
    public Post get(long id) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "SELECT * from T_Post where id=?";        
            // 创建查询        
            stmt = con.prepareStatement(SQL);        
            // 设置参数        
            stmt.setLong(1, id);        
            // 执行查询，拿到结果集        
            rs = stmt.executeQuery();        
            // 返回        
            if (rs.next()) {        
                return getFromResult(rs);        
            } else {        
                return null;        
            }        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    @Override    
    public Post save(Post post) {        
        if (post.getId() <= 0) {        
            return insert(post);        
        } else {        
            return update(post);        
        }        
    }        
    
    /**    
     * 增加一个发言    
     *        
     * @param post    
     *                    发言    
     * @return 成功返回发言，失败返回null    
     */    
    private Post insert(Post post) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "insert into T_Post (idParent,userId,title,content) values (?,?,?,?)";        
            // 创建查询        
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);        
            // 设置参数        
            stmt.setLong(1, post.getIdParent());        
            stmt.setLong(2, post.getUser().getId());        
            stmt.setString(3, post.getTitle());        
            stmt.setString(4, post.getContent());        
            if (stmt.executeUpdate() == 1) {        
                rs = stmt.getGeneratedKeys(); // 用来获得生成的数值        
                if (rs.next()) {        
                    post.setId(Long.parseLong(rs.getString(1))); // 拿到数值        
    
                    return post;        
                }        
            }        
    
            return null;        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
    
    /**    
     * 更新一个发言    
     *        
     * @param post    
     *                    发言    
     * @return 成功返回发言，失败返回null    
     */    
    private Post update(Post post) {        
        Connection con = null;        
    
        PreparedStatement stmt = null;        
    
        ResultSet rs = null;        
        try {        
    
            // 获得一个数据库连接        
            con = db.getConnection();        
            if (con == null) {        
                return null;        
            }        
    
            final String SQL = "update T_Post set title=?,content=? where id=?";        
            // 创建查询        
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);        
            // 设置参数        
            stmt.setString(1, post.getTitle());        
            stmt.setString(2, post.getContent());        
            stmt.setLong(3, post.getId());        
            if (stmt.executeUpdate() == 1) {        
                return post;        
            }        
            return null;        
        } catch (Exception e) {        
            // 捕获并显示异常        
            e.printStackTrace();        
            return null;        
        } finally {        
            db.close(con, stmt, rs);        
        }        
    }        
}    

package net.java2000.notepad.service.impl.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.java2000.notepad.Post;
import net.java2000.notepad.service.PostService;
import net.java2000.notepad.service.UserService;

/**
* 留言服务的JDBC实现版本
*    
* @author 赵学庆，Java世纪网([url]http://[/url])
*    
*/
public class PostServiceJDBCImpl implements PostService {

    // 公用一个连接辅助类
    private static final DBUtil db = DBUtil.getInstance();

    private static final UserService userService = new UserServiceJDBCImpl();

    @Override
    public int countAllSubject() {
        Connection con = null;

        Statement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return -1;
            }

            final String SQL = "SELECT count(*) from T_Post where idParent=0";
            // 创建查询
            stmt = con.createStatement();
            // 执行查询，拿到结果集
            rs = stmt.executeQuery(SQL);
            if (rs.next()) {
                return rs.getInt(1);
            } else {
                return 0;
            }
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return -1;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    /**
     * 从结果集拿到对象
     *    
     * @param rs
     *                    结果集
     * @return 成功返回对象
     * @throws SQLException
     *                     数据库异常
     */
    private Post getFromResult(ResultSet rs) throws SQLException {
        Post o = new Post();
        o.setId(rs.getLong("id"));
        o.setIdParent(rs.getLong("idParent"));
        o.setUser(userService.get(rs.getLong("userId")));
        o.setTitle(rs.getString("title"));
        o.setContent(rs.getString("content"));
        o.setDatetime(rs.getTimestamp("datetime"));
        return o;
    }

    @Override
    public List<Post> findReplyOfSubject(long postId) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "SELECT * from T_Post where idParent=?";
            // 创建查询
            stmt = con.prepareStatement(SQL);
            // 设置参数
            stmt.setLong(1, postId);
            // 执行查询，拿到结果集
            rs = stmt.executeQuery();
            // 创建返回的列表
            List<Post> list = new ArrayList<Post>();
            while (rs.next()) {
                list.add(getFromResult(rs));
            }
            return list;
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    @Override
    public List<Post> findSubject(int begin, int number) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "SELECT * from T_Post where idParent=0 limit ?,?";
            // 创建查询
            stmt = con.prepareStatement(SQL);
            // 设置参数
            stmt.setLong(1, begin);
            stmt.setLong(2, number);
            // 执行查询，拿到结果集
            rs = stmt.executeQuery();
            // 创建返回的列表
            List<Post> list = new ArrayList<Post>();
            while (rs.next()) {
                list.add(getFromResult(rs));
            }
            return list;
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    @Override
    public Post get(long id) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "SELECT * from T_Post where id=?";
            // 创建查询
            stmt = con.prepareStatement(SQL);
            // 设置参数
            stmt.setLong(1, id);
            // 执行查询，拿到结果集
            rs = stmt.executeQuery();
            // 返回
            if (rs.next()) {
                return getFromResult(rs);
            } else {
                return null;
            }
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    @Override
    public Post save(Post post) {
        if (post.getId() <= 0) {
            return insert(post);
        } else {
            return update(post);
        }
    }

    /**
     * 增加一个发言
     *    
     * @param post
     *                    发言
     * @return 成功返回发言，失败返回null
     */
    private Post insert(Post post) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "insert into T_Post (idParent,userId,title,content) values (?,?,?,?)";
            // 创建查询
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
            // 设置参数
            stmt.setLong(1, post.getIdParent());
            stmt.setLong(2, post.getUser().getId());
            stmt.setString(3, post.getTitle());
            stmt.setString(4, post.getContent());
            if (stmt.executeUpdate() == 1) {
                rs = stmt.getGeneratedKeys(); // 用来获得生成的数值
                if (rs.next()) {
                    post.setId(Long.parseLong(rs.getString(1))); // 拿到数值

                    return post;
                }
            }

            return null;
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }

    /**
     * 更新一个发言
     *    
     * @param post
     *                    发言
     * @return 成功返回发言，失败返回null
     */
    private Post update(Post post) {
        Connection con = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;
        try {

            // 获得一个数据库连接
            con = db.getConnection();
            if (con == null) {
                return null;
            }

            final String SQL = "update T_Post set title=?,content=? where id=?";
            // 创建查询
            stmt = con.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
            // 设置参数
            stmt.setString(1, post.getTitle());
            stmt.setString(2, post.getContent());
            stmt.setLong(3, post.getId());
            if (stmt.executeUpdate() == 1) {
                return post;
            }
            return null;
        } catch (Exception e) {
            // 捕获并显示异常
            e.printStackTrace();
            return null;
        } finally {
            db.close(con, stmt, rs);
        }
    }
}