java - DAO 设计模式与 DBUnit 代码

Question

我不确定我的 DAO 或 DBUnit 测试是否需要一些重构。有人可以指导我面临的情况吗？

我有一个 DAO，它通过获取 Connection 对象从数据库获取一些数据，然后关闭所有资源(ResultSet、Statement 和连接)。

我正在使用 DBUnit 框架测试这个 DAO，在这里我在此 DBUnit 测试中执行两个数据库操作:1)创建表，然后从XML数据集文件加载数据2) 测试 DAO 中存在的实际 getDataById(int id) 方法

问题是我的第二个 getDataById(id) 方法无法获取数据库连接对象，因为我的 DAO 在执行上述步骤 1 时已将其关闭。

实际释放所有资源的代码片段如下所示。

DAO.java(代码片段)

public void releaseResources(ResultSet rs, Statement stmt, Connection cn) {
        System.out.println("rs = " + rs);
        System.out.println("stmt = " + stmt);
        System.out.println("cn = " + cn);
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (cn != null) {
                cn.close();
            }
        } catch (SQLException ex) {
            System.out.println("Exception while closing DB resources rs, stmt or cn......." + ex);
        }
    }

因此，为了让我的 DBUnit 测试正常工作，我必须重载上述 releaseResources() 方法，以便它不会关闭可在我的 getDataById(int id) 中使用的连接对象单元测试。如下所示。

**DAO.java(具有重载方法的代码片段)**

//OVERLOADED JUST TO GET DBUNIT TESTS WORKING !!! :(
public void releaseResources(Statement stmt) {
    System.out.println("\nReleasing db resources now.... 1111");
    System.out.println("stmt = " + stmt);
    try {
        if (stmt != null) {
            stmt.close();
        }
    } catch (SQLException ex) {
        System.out.println("Exception while closing DB resources stmt......." + ex);
    }
}

我不确定这是否是正确的设计。有人可以指导我如何改进吗？

完整代码如下所示，供进一步引用。

StateDaoTest.java

public class StateDaoTest {

    protected static Connection connection;
    protected static HsqldbConnection dbunitConnection;
    protected static StateDao dao = new StateDao();

    @BeforeClass
    public static void setupDatabase() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        connection = DriverManager.getConnection("jdbc:hsqldb:mem:my-project-test;shutdown=true");
        dbunitConnection = new HsqldbConnection(connection, null);
    }

    @Before
    public void createTable() throws SQLException {
        dao.setConnection(connection);
        dao.createTables();
    }

    protected IDataSet getDataSet(String name) throws Exception {
        InputStream inputStream = getClass().getResourceAsStream(name);
        assertNotNull("file" + name + " not found in classpath", inputStream);
        Reader reader = new InputStreamReader(inputStream);
        FlatXmlDataSet dataset = new FlatXmlDataSet(reader);
        return dataset;
    }

    @AfterClass
    public static void closeDatabase() throws Exception {
        System.out.println("\ninto the closeDatabase() method...");
        System.out.println("connection = " + connection);
        System.out.println("dbunitConnection = " + dbunitConnection);
        if (connection != null) {
            connection.close();
            connection = null;
        }
        if (dbunitConnection != null) {
            dbunitConnection.close();
            dbunitConnection = null;
        }
    }

    @Test
    public void testGetStateById() throws Exception {
        IDataSet setupDataSet = getDataSet("/states.xml");
        DatabaseOperation.CLEAN_INSERT.execute(dbunitConnection, setupDataSet);
        State state = dao.getStateById(1);
        assertNotNull(state);
        assertEquals("Pennsylvania", state.getName());
        assertEquals("PA", state.getStateCode());
        assertNotNull(state.getTaxPct());
        assertEquals("Y", state.getActive());
    }

}

StateDao.java

public class StateDao {

    private Connection connection;

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    //added for dbunit tests
    public void createTables() throws SQLException {
        String sql = "CREATE TABLE states (stateId INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1), "
                + "stateCd VARCHAR(10), name VARCHAR(20), taxPct NUMERIC, active CHAR(1))";
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            stmt.execute(sql);
        } finally {
            releaseResources(stmt);
        }
    }

    public State getStateById(long id) {
        String sql = "SELECT * FROM states WHERE stateId = " + id;

        Statement stmt = null;
        ResultSet rs = null;
        State state = null;

        System.out.println(sql);

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs != null && rs.next()) {
                String stateId = StringUtils.defaultString(rs.getString("stateId"));
                String stateCd = StringUtils.defaultString(rs.getString("stateCd"));
                String name = StringUtils.defaultString(rs.getString("name"));
                String taxPct = StringUtils.defaultIfEmpty(rs.getString("taxPct"), "0");
                String active = StringUtils.defaultString(rs.getString("active"));
                state = new State(new Integer(stateId), stateCd, name, new BigDecimal(taxPct), active);
                System.out.println("state = " + state);
            }
            System.out.println("state = " + state);
        } catch (SQLException ex) {
            System.out.println("Exception whiile fetching data for a state......." + ex);
        } finally {
            releaseResources(rs, stmt, connection);
        }
        return state;
    }

    public void releaseResources(ResultSet rs, Statement stmt, Connection cn) {
        System.out.println("\nReleasing db resources now....2222");
        System.out.println("rs = " + rs);
        System.out.println("stmt = " + stmt);
        System.out.println("cn = " + cn);
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (cn != null) {
                cn.close();
            }
        } catch (SQLException ex) {
            System.out.println("Exception while closing DB resources rs, stmt or cn......." + ex);
        }
    }

    //added for dbunit tests
    public void releaseResources(Statement stmt) {
        System.out.println("\nReleasing db resources now.... 1111");
        System.out.println("stmt = " + stmt);
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException ex) {
            System.out.println("Exception while closing DB resources stmt......." + ex);
        }
    }  

}

Answer 1

我会继续使用重载的releaseResources方法。如果连接是从外部传入的，那么 dao 不拥有它，也不应该关闭它。相反，连接应该由创建它的同一个类单独关闭。所以getStateById应该改为只关闭resultSet和statement，其他地方应该关闭连接。

您可以添加额外的 releaseResources 重载来获取 resultSet 和语句，或者如果您使用的是 Java 7 - 您可以创建一个方法:releaseResouces(Closeable... closeables) 。我会将这个方法作为一个单独类的公共(public)静态方法，以便任何类都可以使用它。