在java.sql异常关闭ResultSet后不允许进行操作

Question

我正在使用jersey创建简单的rest api，在这里我将从数据库中获取的所有数据分配给变量，然后将这些变量数据作为列表返回，但出现错误。我看过其他示例，但我不明白这是怎么回事，这是我所做的：

public class UserDAOImpl implements UserDAO{

public List<User> readUser() {

    List<User> list = new ArrayList();

    User u = new User();

    try {

    System.out.println("inside readuser impl");

        Class.forName("com.mysql.jdbc.Driver");

        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/userdb", "root", "");

        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("select * from user");

        while (rs.next()) {

            int id = rs.getInt("id");

            String name = rs.getString("name");

            String address = rs.getString("address");

            u.setId(id);

            u.setName(name);

            u.setAddress(address);

            System.out.println(u);

            list.add(u); //adding user object to list

            con.close();

        }

    } catch (Exception e) {

        System.out.println(e);

    }

    return list; //returning to list for json response

 }

}

这是我的控制器，在获取对象后给出json响应

@Path("/rest")

public class UserController {

@GET

@Path("/getall")

@Produces(MediaType.APPLICATION_JSON)

public List<User> getAllUser(){

    UserDAO udao = new UserDAOImpl();

    return udao.readUser();

 }

}

Answer 1

您将在一次迭代后关闭连接，这将导致在第二次迭代时在javasql异常上关闭ResultSet后不允许操作，将con.close移到循环外public class UserDAOImpl implements UserDAO{&nbsp;public List<User> readUser() {&nbsp; &nbsp;List<User> list = new ArrayList();&nbsp; &nbsp;User u = new User();&nbsp; &nbsp;try {&nbsp; &nbsp; &nbsp; &nbsp; System.out.println("inside readuser impl");&nbsp; &nbsp; &nbsp; &nbsp; Class.forName("com.mysql.jdbc.Driver");&nbsp; &nbsp; &nbsp; &nbsp; Connection con =&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; DriverManager.getConnection("jdbc:mysql://localhost:3306/userdb", "root", "");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Statement stmt = con.createStatement();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ResultSet rs = stmt.executeQuery("select * from user");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while (rs.next()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int id = rs.getInt("id");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String name = rs.getString("name");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String address = rs.getString("address");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.setId(id);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.setName(name);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.setAddress(address);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(u);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; list.add(u); //adding user object to list&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; } catch (Exception e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(e);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; con.close();&nbsp; &nbsp; &nbsp; &nbsp; return list; //returning to list for json response&nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; }甚至您可以对资源使用try它将自动关闭所有资源&nbsp;public class UserDAOImpl implements UserDAO{&nbsp; &nbsp; public List<User> readUser() {&nbsp; &nbsp; &nbsp; &nbsp; List<User> list = new ArrayList();&nbsp; &nbsp; &nbsp; &nbsp; User u = new User();&nbsp; &nbsp; &nbsp; &nbsp; try( Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/userdb", "root", "");&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; Statement stmt = con.createStatement();ResultSet rs = stmt.executeQuery("select * from user");){&nbsp; &nbsp; &nbsp; &nbsp; System.out.println("inside readuser impl");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while (rs.next()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int id = rs.getInt("id");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String name = rs.getString("name");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String address = rs.getString("address");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.setId(id);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.setName(name);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.setAddress(address);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(u);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; list.add(u); //adding user object to list&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; } catch (Exception e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(e);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return list; //returning to list for json response&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp;}我没有测试第二个，因为我没有这台机器的环境