lundi 28 septembre 2015

SQLException Operation not allowed after ResultSet closed

I am attempting to write a JUnit test for a query which is retrieved via a textbox in an html form. The text retrieval has been tested and works but my unit test is failing. I am using 2 relevant classes: QueryController and QueryControllerTest. I have been playing around with when and what I am closing in these two classes and keep getting the error: Operation not allowed after ResultSet closed.

QueryControllerTest.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;
}
}

Aucun commentaire:

Enregistrer un commentaire