What is a suitable way to close a database connection in Java?

Question

I tried to close the database connection. But he had a confusion, let's say

ResultSet rs = null 

Do I need to close it

rs.close();

or

DatabaseUtil.closeResultSet(rs);

What is the difference between the two?

Answer 1

These methods close only ResultSet. You still have to close all instances of Statementand Connection. I recommend doing this in a block finally. Sort of,

Connection conn = null;
Statement stmt = null'
ResultSet rs = null;
try {
  conn = getConnection();
  stmt = conn.prepareStatement(sql);
  stmt.setString(1, "Hello"); 
  rs = stmt.executeQuery();
  while (rs.next()) {
    // ...
  }
} catch (SQLException se) {
  se.printStackTrace();
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  if (conn != null) {
    try {
      conn.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

If you use my utility , then the final block may be, Close

} finally {
  Close.close(rs, stmt, conn);
}

Answer 2

Closing resultSet does not close the database connection. You need to do this separately.

Usually you want to close the resource as follows:

if (resultSet != null) {
     try {
         resultSet.close();
     } catch (SQLException e) {
         log.info("closing resultset caused exception", e);
     }
}

, DatabaseUtil, .

try-with-resources , , . resultSet ( , ), , . , try-with-resources, , close. , resultSet , ( , ), - , , .

. , finally, , , ( , ).

, (, OP , , , , , ), , :

Class Class1<T>
{
  public T getColumn(DataSource ds)
  {
    T value = null;
    Connection con = null;
    Statement st = null;

    try
    {
      con = ds.getConnection();
      st = con.createStatement();
      ResultSet rs = st.executeQuery("select 1 from dual");
      rs.next();
      Object o = rs.getObject(1); // I want an Integer but a BigDecimal is created!
      value = (T) o; // ClassCastException here!
    }
    finally
    {
      if (st != null) { st.close(); }
      if (con != null) { con.close(); }
    }

    return i;
  }
}

statement.close SQLException, finally , , . , , , ( ). . " ! , , .

, - , mapset , . spring -jdbc, .

Answer 3

jdbc api , . . , , . close, , .

Answer 4

, - try-with-resources

try(conn=openConnection()){
  try(rs=conn.getResultSet()){
  }
}

, .

Answer 5

, rs.close();, , , rs , try..catch, , Java7 .

check Java/JDBC:

Answer 6

:

Connection connection = null;
PreparedStatement stmt = null;
ResultSet rs = null;
ResultSet rs2 = null;
try {
  connection = getConnection();
  stmt = connection.prepareStatement("...");

  stmt.setString(":foobar", "Foobar");
  rs = stmt.execute ... ; // I don't remember the method return ResultSet

  while (rs.next()) {
    ...
  }

  stmt.setString(":foobar", "Barfoo");
  rs2 = stmt.execute ... ; 


} finally {
  if (null != rs2) try {rs2.close();} catch (Exception e)
  if (null != rs) try {rs.close();} catch (Exception e) {...}
  if (null != stmt) try {stmt.close();} catch (Exception e) {...}
  if (null != connection) try {connection.close();} catch (Exception e) {...}
}

, Java 7, try-with-resources ( ):

try (Connection connection = getConnection();
     PreparedStatement stmt = connection.prepareStatement("...")) {

  stmt.setString(":foobar", "Foobar");
  try (ResultSet rs = stmt.executeQuery()) {
    while (rs.next()) {
        ...
    }
  }
  stmt.setString(":foobar", "Barfoo");
  try (ResultSet rs2 = stmt.executeQuery()) {
    while (rs2.next()) {
        ...
    }
  }
}

:

• ResultSet - . PreparedStatement: , ResultSet. , , , , , .
• PreparedStatement Statement, . ResultSet.
• Connection, . Statement ( ResultSet).

(, -) , Java, , ( , JDBC ...).

Answer 7

resultSet . , . , , ResultSet, PreparedStatement Statement.

 Connection con = DBUtils.getConnection();
 ....
 PreparedStatemet pstmt = con.getPreparedStatement();
 ResultSet rs = pstmt.execute();

 while(rs.next())
 {
 }

// , ResultSet... / .

    if (resultSet != null) {
    try {
    resultSet.close();
    resultSet = null;
    } catch (SQLException e) {
      // ignore the exceptions.
    }
    }

    // Close the Prepared Statement
    if(theStatement != null)
    {
    try
    {
    theStatement.close();
    theStatement = null;
    }
    catch(Exception ignored)
    {
    }

// Close the Connection
    if(theConnection != null)
    {
    try
    {
    theConnection.close();
    theConnection = null;
    }
    catch(Exception ignored)
    {
    }