jsp - 使用 Servlet/JSP 执行 CRUD 操作

Question

我正在使用 Servlet 和 JSP 执行 CRUD 操作。以下类用于从服务器 (Tomcat) 维护的连接池中检索连接。

public final class DatabaseConnection {

    private static final DataSource dataSource;

    static {
        try {
            Context initContext = new InitialContext();
            Context context = (Context) initContext.lookup("java:/comp/env");
            dataSource = (DataSource) context.lookup("jdbc/assignment_db");
        } catch (NamingException e) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, e);
            throw new ExceptionInInitializerError("DataSource not initialized.");
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

并且下面类(DAO)中的方法执行CRUD操作。

public final class CountryDao {

    public Long getCurrentRow(Long id) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DatabaseConnection.getConnection();
            preparedStatement = connection.prepareStatement("select rownum from (select @rownum:=@rownum+1 as rownum, tbl.country_id from country_tbl tbl, (select @rownum:=0)t order by tbl.country_id desc)t where country_id=?");
            preparedStatement.setLong(1, id);
            resultSet = preparedStatement.executeQuery();
            return resultSet.next() ? resultSet.getLong("rownum") : 1;
        } finally {
            if (connection != null) {connection.close();}
            if (resultSet != null) {resultSet.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }
    }

    public Long rowCount() throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DatabaseConnection.getConnection();
            preparedStatement = connection.prepareStatement("select count(*) as cnt from country_tbl");
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getLong("cnt");
        } finally {
            if (connection != null) {connection.close();}
            if (resultSet != null) {resultSet.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }
    }

    public List<CountryBean> getData(Long currentPage, Long pageSize) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<CountryBean> countryBeans = new ArrayList<CountryBean>();

        try {
            connection = DatabaseConnection.getConnection();
            preparedStatement = connection.prepareStatement("select * from country_tbl order by country_id desc limit ?,?");

            //preparedStatement.setMaxRows(pageSize);
            preparedStatement.setLong(1, currentPage);
            preparedStatement.setLong(2, pageSize);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                CountryBean countryBean = new CountryBean();
                countryBean.setCountryId(resultSet.getLong("country_id"));
                countryBean.setCountryName(resultSet.getString("country_name"));
                countryBean.setCountryCode(resultSet.getString("country_code"));
                countryBeans.add(countryBean);
            }
        } finally {
            if (connection != null) {connection.close();}
            if (resultSet != null) {resultSet.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }

        return countryBeans;
    }

    public boolean delete(Long id) throws SQLException {
        boolean status = false;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DatabaseConnection.getConnection();
            preparedStatement = connection.prepareStatement("delete from country_tbl where country_id=?");
            preparedStatement.setLong(1, id);

            if (preparedStatement.executeUpdate() == 1) {
                status = true;
            }
        } finally {
            if (connection != null) {connection.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }
        return status;
    }

    public boolean delete(Long[] ids) throws SQLException {
        boolean status = false;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DatabaseConnection.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("delete from country_tbl where country_id=?");
            int len = ids.length;

            for (int i = 0; i < len; i++) {
                preparedStatement.setLong(1, ids[i]);
                preparedStatement.addBatch();
            }

            preparedStatement.executeBatch();
            connection.commit();
            status = true;
        } finally {
            if (connection != null) {connection.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }
        return status;
    }

    public boolean insert(String countryName, String countryCode) throws SQLException {
        boolean status = false;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DatabaseConnection.getConnection();
            preparedStatement = connection.prepareStatement("insert into country_tbl(country_name, country_code)values(?,?)");
            preparedStatement.setString(1, countryName);
            preparedStatement.setString(2, countryCode);
            preparedStatement.executeUpdate();
            status = true;
        } finally {
            if (connection != null) {connection.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }
        return status;
    }

    public boolean update(Long countryId, String countryName, String countryCode) throws SQLException {
        boolean status = false;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DatabaseConnection.getConnection();
            preparedStatement = connection.prepareStatement("update country_tbl set country_name=?, country_code=? where country_id=?");
            preparedStatement.setString(1, countryName);
            preparedStatement.setString(2, countryCode);
            preparedStatement.setLong(3, countryId);
            preparedStatement.executeUpdate();
            status = true;
        } finally {
            if (connection != null) {connection.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }

        return status;
    }
}

这些方法在执行验证后从 Servlet 中适当调用。 Servlet 反过来与 JSP(以及 JSTL/EL)交互。

只有一个问题。目前，connection、preparedStatement和resultSet都是特定方法的局部变量。

我可以只在一个地方将它们声明为类成员(实例变量)吗？这样做能不能精确的保持一致的状态？

_{无需过多关注核心逻辑。请不要只是说，最好使用 MVC 框架 :)}

Answer 1

Can I declare them only at one place as class members (instance variables)?

您可以这样做，但是该类将是线程不安全的。调用者不能跨多个线程重用同一个实例，而不会干扰由不一致状态引起的每个单独方法的行为。在 servlet 作为调用者的情况下，这样您就不能在 servlet 的 init() 中仅创建一次实例并在 doXxx() 方法中多次重用它.您将被迫在线程局部范围内重新创建实例(因此，在 doXxx() 方法内)。这应该清楚地记录在类的 javadoc 中。但是，毕竟设计一个线程不安全的 DAO 类是没有意义的。坚持当前的设计(或者，如果您不只是业余爱好，请切换到 JPA ;))。

---

Can doing so maintain a consistent state precisely?

不!反之，则不一致。您不能在多个查询中共享同一个语句或结果集。每个查询都应该有自己的语句和结果集。实例上的每个方法调用都会更改实例变量，导致其他仍在运行的方法调用在损坏的状态下工作。共享连接是可能的，但这项工作通常已经由连接池完成。鉴于您正在使用数据源，您很可能已经拥有一个数据源。

---

就是说，如果您讨厌重复的代码样板，但又真的想坚持使用优秀的 JDBC API，请查看 Execute Around pattern/idiom和/或 Java 7's automatic resource management (ARM) .有了这个，就必须有可能创建一个带有一堆接口(interface)的帮助 DB 类，并最终得到一个通用的抽象基 DAO 类，其方法只采用 SQL 查询和参数值(如果有的话)。