CODEWITHSUNDEEP
javasqljdbc
asho
asho

Reputation: 41

Using resultset data after closing the connection

I'm trying to write a small code snippet where I need to get some data from a database and then process the result in some other java file. I tried writing a program and the execution for the same was failing with error "Cannot access resultset as the connection was already closed".

Is there any way can we store the result fetched from database some where (Ex.some arraylist) and use it for computation after closing the connection? If yes, can someone please explain it with example? Slightly handicapped since I'm new to it.

Class A { 

  public Map<String, Object> loadDat(int acc,Map<String,Object> result) 
      throws Exception { 
    Class.forName("com.teradata.jdbc.TeraDriver"); 
    Connection conn = DriverManager.getConnection(connectionString, user, password); 
    query = "select * from mytable where id="+acc; 
    PreparedStatement stmt = conn.prepareStatement(query); 
    ResultSet rs=stmt.executeQuery(); 
    result.put(" Result", rs) ; 
    return result; 
  }

}

Upvotes: 0

Views: 233

Answers (2)

The Impaler
The Impaler

Reputation: 48770

Just to add to @DwB's answer that is correct.

You can 1) retrieve all rows from your query into a Java List, 2) then close the connection, and 3) then use the Java List in another class (for further processing).

If you close the connection after retrieving only part of the result set, you'll lose the rest of it and will receive the error you mention. Don't do it this way.

Upvotes: 0

DwB
DwB

Reputation: 38300

In general, don't code JDBC database access by hand. Libraries already exist that do all the low level JDBC handling now and they do it correctly. You will never do it better than an one of the mature, open source projects already do it.

Instead, learn and use something like MyBatis. If you use Spring, here is a link to the Mybatis-Spring project.

MyBatis conceals all of the data conversion and JDBC junk. Instead, you define your query in a simple XML file and receive a List as the result of a query.

Upvotes: 1

Related Questions