Multiple queries on database using jdbc

Question

I have a text file from where i read values

FileInputStream file = new FileInputStream("C:/workspace/table_export.txt");
BufferedReader br = new BufferedReader(new InputStreamReader(file));
String line = null;
while( (line = br.readLine())!= null )
{
        String [] tokens = line.split("\\s+");
        String var_1 = tokens[0];
        System.out.println(var_1);
        getstaffinfo(var_1,connection);
}

The values read from text file is passed to getstaffinfo method to query the db

public static String getstaffinfo(String var_1, Connection connection) throws SQLException, Exception
  // Create a statement
   {
   StringBuffer query = new StringBuffer();
   ResultSet rs = null;
   String record = null;
   Statement stmt = connection.createStatement();
   query.delete(0, query.length());
   query.append("select firstname, middlename, lastname from users where employeeid = '"+var_1+"'");
   rs = stmt.executeQuery(query.toString());
       while(rs.next())
       {
   record = rs.getString(1) + " " +
                  rs.getString(2) + " " +
                  rs.getString(3);
  System.out.println(record);
 }
 return record;
}

I get almost 14000 values read from text file which is passed to getstaffinfo method, all database activities such has loading driver, establishing connectivity all works fine. But while printing

it throws error

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded

Although i understand that this error is to do with database configuration, Is there an efficent way of making one db call and exceute the query for multiple values read from text file. Any inputs would be of great use.

Many Thanks in advance!!

what is resultSet?you have not defined anywhereI think resultSet should be replaced by rs — SpringLearner, Sep 17 '13 at 11:04
I suspect you have a leak in DB Connections / Statements. Make sure they are closed properly when finished. — Fildor, Sep 17 '13 at 11:08

score 3 · Accepted Answer · answered Sep 17 '13 at 11:07

3

Close ResultSet rs.close(); and Statement stmt.close(); after your while loop in getstaffinfo(), preferably inside a finally{}

answered Sep 17 '13 at 11:07

Bimalesh Jha

1,464
9
17

Hi Bimalesh, if i add rs.close(); stmt.close(); return record; this is not returning the record. The method is called for each value, so it needs to return . Following your advise, i dont get any exxception, but values after querying is not returned. Any thoughts would be appreciated – navman Sep 17 '13 at 11:33
Pls modify your question with close() added in the code sample so that we can see what is happening. – Bimalesh Jha Sep 17 '13 at 12:33
public static String getstaffid(String var_1, Connection connection) throws SQLException, Exception // Create a statement { String record = null; ResultSet rs = null; Statement stmt = connection.createStatement(); try { rs = stmt .executeQuery("select firstname, middlename, lastname from globalusers where username = '" + var_1 + "'"); while (rs.next()) { record = rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3); System.out.println(record); } } finally { rs.close(); stmt.close(); } return record; }//Worked fine.Thanks – navman Sep 17 '13 at 16:54

score 0 · Answer 2 · answered Sep 17 '13 at 11:09

You need to close resultSet via rs.close(); and Statement via stmt.close();

while(rs.next()){
   record = rs.getString(1) + " " +
            rs.getString(2) + " " +
            rs.getString(3);
   System.out.println(record);
}
rs.close();
stmt.close();

score 0 · Answer 3 · answered Sep 17 '13 at 11:10

0

The issue is you are not closeing ResultSet and Statement (though it is good that your are working on one Connection), try closing resources, the error should not happen.

Bigger issue is that if you do close, you are hitting DB n number of times where n is number of filtering criteria. One solution to this could be make in clause instead of = selection. e.g:

Say total lines = N, divide into x chunks hence make N/x select statements

For example is N=20000, x=1000; you need to fire 20 selects instead of 20000.

answered Sep 17 '13 at 11:10

harsh

7,502
3
31
32

public static String getstaffid(String var_1, Connection connection) throws SQLException, Exception // Create a statement { String record = null; ResultSet rs = null; Statement stmt = connection.createStatement(); try { rs = stmt .executeQuery("select firstname, middlename, lastname from globalusers where username = '" + var_1 + "'"); while (rs.next()) { record = rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3); System.out.println(record); } } finally { rs.close(); stmt.close(); } return record; } – navman Sep 17 '13 at 16:52

score 0 · Answer 4 · answered Sep 17 '13 at 11:10

0

close resultset and statement like this way

rs.close(); stmt.close();

answered Sep 17 '13 at 11:10

SpringLearner

13,738
20
78
116

score 0 · Answer 5 · edited May 23 '17 at 11:50

Best way is to use IN clause in the query and call the method only once.

 FileInputStream file = new FileInputStream("C:/workspace/table_export.txt");
 BufferedReader br = new BufferedReader(new InputStreamReader(file));
 String line = null;
 String var_1 = "";
 while( (line = br.readLine())!= null )
 {
    String [] tokens = line.split("\\s+");
    var_1 = var_1+tokens[0]+",";
    System.out.println(var_1);
}
var_1 = var_1.subString(0,var_1.lastIndexOf(","));
getstaffinfo(var_1,connection);

change getstaffinfo() like this

public static List<String> getstaffinfo(String var_1, Connection connection) throws SQLException, Exception
// Create a statement
{
 StringBuffer query = new StringBuffer();
 ResultSet rs = null;
 String record = null;
 List<String> list = new ArrayList<String>();
 Statement stmt = connection.createStatement();
 query.delete(0, query.length());
 query.append("select firstname, middlename, lastname from users where employeeid IN ("+var_1+")");
 try{
  rs = stmt.executeQuery(query.toString());
   while(rs.next())
   {
     record = rs.getString(1) + " " +rs.getString(2) + " " +rs.getString(3);
     System.out.println(record);
     list.add(record);
    }
    }finally{
        stmt.close();
        rs.close();
    }
    return list;
 }

Note : Cant we put more than 1000 values in 'in clause'.
Related links.
1.https://forums.oracle.com/thread/235143?start=0&tstart=0
2.java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

Multiple queries on database using jdbc

5 Answers5