limit result through JDBC

[avihai marchiano]

I need to go other around 100,000 rows (row has 7 columns from join between tables) in bulk of 500-1000, do my process (around 20 sec for bulk) and take additional bulk ....

My question:

from performance perspective should i leave the result set open or for each bulk create result set and set beginning (by absolute ) at the end of the last bulk row?

I understand that when you use "absolute" the database iterate over all rows until this point , dosnt the database has any algorithm to do this without iterate over all rows?

Thank you

[Arunkumar Subramaniam]

Hi

you can use SQL cached row result set for achive your goal

like :

package com.javaorigin.jdbc.sample.cachedrowset;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;


public class CachedRowSetTester {

	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con= DriverManager.getConnection("jdbc:oracle:thin:@javaorigin.com:1521:orcl","mydb","password123");
        Statement st=con.createStatement();      
        st.setMaxRows(10);
        ResultSet rs= st.executeQuery("select * from users");
        CachedRowSet cachedRowSet=new CachedRowSetImpl();
        cachedRowSet.populate(rs);
        rs.close();
        st.close();
        con.close();
        
        while (cachedRowSet.next()) {
			System.out.println(cachedRowSet.getString(1));
		}
        
	}

}

Arunkumar S

[avihai marchiano]

If you have 1000 rows in the resultset, so it will copy all rows?

[Jeanne Boyarsky]

Or you could use the SQL query itself to only return the rows you want to each connection is independent. Then you don't have to worry about using resources between requests. You could use rownum or the primary key of your table in the query.