Clever Geek Handbook

How to set cursor type in JDBC?

I am starting tomcat and have some jsp pages that display a subset of the table. I am displaying 20 lines at a time on one page. When the table has large amounts of data, the jsp page does not render. I am assuming the ResultSet is using a client side cursor. I've worked with ASP in the past and we've always used server-side only for cursors and never had problems with large amounts of data. Our database is oracle 10g.

How can I specify a server side direct access cursor in JDBC?

+1


source to share


3 answers


The oracle driver implements server side cursors through the FetchSize property.

Unfortunately, JDBC does not explicitly allow setting client and server cursors, so different drivers implement it differently. Here are the links that helped:



Sample Size
Cursors
Oracle Driver

+3


source 


Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY);
ResultSet rs = stmt.executeQuery(sql);

This should be installed, but apparently some drivers ignore it.

You can always try and set it again at the ResultSet level.



rs.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY);

Hope it helps.

+2


source


Not completely answering the question, but have you considered adding paging explicitly to your SELECT query using ROWNUM or ROWNUMBER in your WHERE clause?

eg: for the second data page, the page size is 20 elements:

SELECT * 
  FROM MyDataObjects
  WHERE rownum > 20 AND rownum < 41

This will ensure that no more than one page of records is returned, eliminating the large cursor problem.

0


source






More articles:


All Articles