结果集关闭后不允许SQLException操作

Question

我试图为通过html形式的文本框检索的查询编写一个JUnit测试。文本检索已经过测试并有效，但我的单元测试失败了。我使用了两个相关的类：QueryController和QueryControlllerTest。我一直在玩弄我在这两个类中关闭的时间和内容，并不断得到错误：结果集关闭后不允许操作。

QueryController测试。Java语言

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import static org.junit.Assert.*;

public class QueryControllerTest {

@Test
public void testQuery() {
    ResultSet testRs = null;
    Connection conn = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");

        String connectionUrl = "jdbc:mysql://localhost:3306/test";
        String connectionUser = "root";
        String connectionPassword = "GCImage";
        conn = DriverManager.getConnection(connectionUrl,
                connectionUser, connectionPassword);
        Query testQuery = new Query();
        testQuery
                .setQuery("select * from service_request where FN_contact = 'Greg'");
        testRs = QueryController.executeSelect(conn, testQuery);

        assertEquals("Laughlin", testRs.getString("LN_contact"));
        assertEquals("Hello World", testRs.getString("Notes"));
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            testRs.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
}

QueryController。Java语言

import java.util.Map;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class QueryController {
@RequestMapping(value = "/query")
public String processRegistration(@ModelAttribute("query") Query query,
        Map<String, Object> model) {

    String queryString = query.getQuery();

    if (queryString != null && !queryString.isEmpty()) {
        System.out.println("query (from controller): " + queryString);
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String connectionUrl = "jdbc:mysql://localhost:3306/test";
            String connectionUser = "root";
            String connectionPassword = "GCImage";
            conn = DriverManager.getConnection(connectionUrl,
                    connectionUser, connectionPassword);
            if (queryString.toLowerCase().startsWith("select")) {
                ResultSet rs = executeSelect(conn, query);
            } else {
                int rowsUpdated = executeUpdate(conn, query);
                System.out.println(rowsUpdated + " rows updated");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 

    }

    return "query";
}

public static ResultSet executeSelect(Connection conn, Query query) {
    ResultSet rs = null;
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query.getQuery());
        while (rs.next()) {
            String id = rs.getString("ID");
            String firstName = rs.getString("FN_Contact");
            String lastName = rs.getString("LN_Contact");
            String notes = rs.getString("Notes");
            System.out.println("ID: " + id + ", First Name: " + firstName
                    + ", Last Name: " + lastName + ", Notes: " + notes);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if(rs!=null){
                rs.close();
            }
            if(stmt != null){
                stmt.close();
            }
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return rs;
}
}

Answer 1

我认为问题是因为你正在创建两个连接。尝试仅为测试实例化QueryController类的连接。您需要提供连接。将其存储在变量中以运行查询后。

连接con=QueryController。getConnection（）；

Answer 2

QueryController.executeSelect正在调用rs.close（），但是QueryControlllerTest.testQuery中的assertEquals正在调用testRS上的方法。由于执行选择返回结果集，因此首先关闭它没有意义。此外，执行选择正在传递连接，因此它也不应该关闭该连接（如果调用者想要对同一连接进行两个不同的选择会发生什么？）。