Thread: jdbc, use of nested ResultSet loops.(longish, interesting I hope)
jdbc, use of nested ResultSet loops.(longish, interesting I hope)
From
"bmatthewtaylor@yahoo.co.uk"
Date:
A few days ago I posted a query regarding a problem I had with errors I was
incurring doing some nested queries with the jdbc driver. (having recently
done a re-install my email history is in limbo for the moment)
Found some of my problems were in messy ugly code, fixed some of the
problems by creating a new connection for the loop that was throwing errors.
Still curious on what would be 'good' style to use.
Curious if anyone knows what can/should work and if this varies with
jdbc/odbc drivers. I assume the jdbc standards dictate certain behaviour
for jdbc conformance, is there a spec I should be reading? Does the
conformance of drivers with the spec vary widely from one implementation to
another?? (I'm interested in the differences between the Oracle classes12
and Postgresql-jdbc drivers specifically but general comments sought)
pseudocode examples
1. Non nested loops.
get connection
create statement object.
use statement object to create ResultSet object using SQL query.
scroll thru ResulSet object to retrieve data.
close ResultSet object.
use statement object to create ResultSet object using SQL query.
scroll thru ResulSet object to retrieve data.
close ResultSet object.
.... repeat as many times as required.
close statement object
close database connection object.
2. Nested loops, I use this format when I'm say printing a record of
information to a table, and need to present a combo box selection (inner
nested loop) that can only be generated once the row data is known (outer
loop).
get connection
create statement object A.
create statement object B.
use statement object A to create ResultSet object AA using SQL query.
scroll thru ResulSet object AA to retrieve data.
use statement object B to create ResultSet object BB using SQL query.
scroll thru ResulSet object BB to retrieve data.
close ResultSet object BB.
close ResultSet object AA.
close statement object
close database connection object.
I found (by trial and error, and a little commonsense)
- nested Resultsets do not seem to be the way to go. (couldn't get this
style to work with Oracle classes12 jdbc driver)
- Using multiple statements off the one database connection seems to work OK.
Other 'thing' I picked up was that Prepared Statements are supposed to be
much faster (by 20-40%) according to the sun jdbc API reference book.
Comments anyone??
brief code example below.
(this code compiled and ran OK, you may need to change to suit your
postgres datafile name.)
import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.*;
import javax.servlet.*;
import javax.servlet.http.*;
/*
Adds forecast to nominated project.
*/
public class simpleExample extends HttpServlet {
public static String usernamepassword = "postgres";
private boolean debug=true;
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException,IOException
//doGet > doPost, enables servlet to responde to both types of
http calls.
{
doPost(req, res);
}
//
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException,IOException
{
PrintWriter out = res.getWriter();
res.setContentType("text/html");
Connection m_connection = null;
Statement stmt = null;
ResultSet rs = null;
out.println("servlet simpleExample called.<BR>");
//
try
{ //open data base connection.
out.println("opening connection.<BR>");
m_connection = GetDBConnection(out);
m_connection.setAutoCommit(false);
out.println("connection opened, creating statement.<BR>");
stmt = m_connection.createStatement();
out.println("statement created.<BR>");
//
try
{//
getListPlayers(m_connection, out);
//
stmt.close();
m_connection.commit();
//commit only needed for servlets updating or inserting.
not req'd 4 select use anyway
m_connection.close();
}//
catch(Exception ex)
{//
out.println("Error retrieving project
information.<BR>"+ex.toString()+"<br>");
out.println("</body></html>");
}//
} catch(Exception ex){//
out.println("Error connecting to database. Exception
information<BR>"+ex.toString()+"<br>");
out.println("</body></html>");
}//end try catch
} // end doGet
//methods below.
//
public void getListPlayers(Connection m_connection, PrintWriter out)
{
boolean debug=true;
Statement stmt = null;
String getPersDetails = "select * from simpleperson";
int age = 0;
String fName=null, team = null;
try
{
stmt = m_connection.createStatement();
//
if(debug)out.println("sampleMethod1 executing
getPersDetails="+getPersDetails+"<BR>");
ResultSet rs = stmt.executeQuery(getPersDetails);
out.println("<form><table border='1'>");
out.println("<tr><td>fName</td><td>age</td><td>team</td></tr>");
if (rs !=null)
while (rs.next() )
{
age = rs.getInt("age");
fName = rs.getString("fName");
team = rs.getString("team");
out.println("<tr><td>"+fName+"</td><td>"+age+"</td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeams(m_connection, out, team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeams(m_connection, out, team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeamsWstmt(stmt, out, team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeamsWstmt(stmt, out, team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeamsWstmt(stmt, out, team);
out.println("</select></td>");
out.println("</tr>");
}
out.println("</table></form>");
rs.close();
stmt.close();
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in
getListPlayers. "
+ "<br>ex.toString() = " +
ex.toString() + "<br>");
}//
}
//
public void getListTeams(Connection m_connection, PrintWriter out,
String tName)
{
boolean debug=false, noRecords=true;
Statement stmt = null;
String getTeamInfo = "select * from simpleTeam where name
<> '"+tName+"'";
int age = 0;
String teamName=null;
try
{
stmt = m_connection.createStatement();
//
if(debug)out.println("getListTeams executing
getTeamInfo="+getTeamInfo+"<BR>");
ResultSet rs = stmt.executeQuery(getTeamInfo);
if (rs !=null)
while (rs.next() )
{
teamName = rs.getString("Name");
out.println("<option>"+teamName+"</option>");
noRecords = false;
}
else out.println("<option>No Records
Found.</option>");
rs.close();
stmt.close();
if(noRecords)out.println("<option>noRecords=T.</option>");
else out.println("<option>noRecords=F.</option>");
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in
getListPlayers. "
+ "<br>ex.toString() = " +
ex.toString() + "<br>");
}//
}
//
//
public void getListTeamsWstmt(Statement stmt, PrintWriter out,
String tName)
{
boolean debug=false;
String getTeamInfo = "select * from simpleTeam where name
<> '"+tName+"'";
int age = 0;
String teamName=null;
ResultSet rs = null;
try
{
//
if(debug)out.println("getListTeams executing
getTeamInfo="+getTeamInfo+"<BR>");
rs = stmt.executeQuery(getTeamInfo);
if (rs !=null)
while (rs.next() )
{
teamName = rs.getString("Name");
out.println("<option>"+teamName+"</option>");
}
else out.println("<option>No Records
Found.</option>");
rs.close();
out.println("<option>getListTeamsWstmt</option>");
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in
getListPlayers. "
+ "<br>ex.toString() = " +
ex.toString() + "<br>");
}//
}
//
public Connection GetDBConnection(PrintWriter out)
throws Exception
/*
Returns database connection, enables easy change to connect to different
connections.
*/
{
Connection Parent_connection = null;
//
Properties info = new Properties();
info.put("user",usernamepassword);
info.put("password",usernamepassword);
boolean debug=false;
try
{ //open data base connection.
if(debug)out.println("loading driver.<br>");
Class.forName("org.postgresql.Driver");
if(debug)out.println("driver loaded, getting connection to
database as "+usernamepassword+"<br>");
Parent_connection =
DriverManager.getConnection("jdbc:postgresql:template1","postgres","postgres");
if(debug)out.println("Connected<br>");
} catch(Exception ex)
{ //
out.println("Error connecting to database. Exception
information<BR>"+ex.toString()+"<br>");
}//
return Parent_connection;
}//end
//
//
}//end ReviewForeCast
(script below to generate tables)
--simpleExample.sql
drop table simplePerson;
drop table simpleTeam;
create table simplePerson(
fName Varchar(20) NOT NULL,
age numeric(5),
team Varchar(20)
);
create table simpleTeam(
Name Varchar(20) NOT NULL,
CoachName Varchar(20)
);
--now insert some records.
insert into simplePerson (fName, age, team) values('fred', 15, 'red');
insert into simplePerson (fName, age, team) values('tim', 16, 'red');
insert into simplePerson (fName, age, team) values('mike', 14, 'red');
insert into simplePerson (fName, age, team) values('fred', 20, 'blue');
insert into simplePerson (fName, age, team) values('tim', 21, 'blue');
insert into simplePerson (fName, age, team) values('mike', 22, 'blue');
insert into simpleTeam (Name, coachName) values('red', 'Mr Smith');
insert into simpleTeam (Name, coachName) values('blue', 'Mr Jones');
insert into simpleTeam (Name, coachName) values('green', 'Ms LongName');
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Hi Matthew(?),
I think you're creating a headache for urself because of the nature of
ResultSets. It doesn't look like you're in need of updatable, real time
stuff -- which is the idea behind why a ResultSet is so hard to play with in
the first place. Think about doing the following: running through each
ResultSet and copying the values into a hashtable or ArrayList or Vector,
then close the ResultSet. Now you've got a local copy of the information
which you can do just about whatever you want. Don't worry -- copying the
data is very fast in most cases and the driver is very good at cleaning
itself up, so you won't have "double" the amount of data sitting around.
ArrayList rows = new ArrayList(1000); *
ResultSet RS = stmt.executeQuery(the_query);
while (RS.next()) {
ArrayList oneRow = new ArrayList(3);
oneRow.add(RS.getString("fname"));
oneRow.add(RS.getString("age"));
oneRow.add(RS.getString("team"));
rows.add(oneRow);
}
RS.close();
stmt.close();
con.close();
rows.trimToSize();
etc etc.
* there are other ways of figuring out how many rows you have, since
Collections
are expandable, this just gives an estimate to the underlying class of the
potential size of your ArrayList.
George Johnson
PS: I believe there's another more specific mailing list for driver/
interface implementation questions
-----Original Message-----
From: bmatthewtaylor@yahoo.co.uk [mailto:bmatthewtaylor@yahoo.co.uk]
Sent: Sunday, January 28, 2001 6:22 AM
To: pgsql-general@postgresql.org
Subject: jdbc, use of nested ResultSet loops.(longish, interesting I
hope)
A few days ago I posted a query regarding a problem I had with errors I was
incurring doing some nested queries with the jdbc driver. (having recently
done a re-install my email history is in limbo for the moment)
Found some of my problems were in messy ugly code, fixed some of the
problems by creating a new connection for the loop that was throwing errors.
Still curious on what would be 'good' style to use.
Curious if anyone knows what can/should work and if this varies with
jdbc/odbc drivers. I assume the jdbc standards dictate certain behaviour
for jdbc conformance, is there a spec I should be reading? Does the
conformance of drivers with the spec vary widely from one implementation to
another?? (I'm interested in the differences between the Oracle classes12
and Postgresql-jdbc drivers specifically but general comments sought)
pseudocode examples
1. Non nested loops.
get connection
create statement object.
use statement object to create ResultSet object using SQL query.
scroll thru ResulSet object to retrieve data.
close ResultSet object.
use statement object to create ResultSet object using SQL query.
scroll thru ResulSet object to retrieve data.
close ResultSet object.
.... repeat as many times as required.
close statement object
close database connection object.
2. Nested loops, I use this format when I'm say printing a record of
information to a table, and need to present a combo box selection (inner
nested loop) that can only be generated once the row data is known (outer
loop).
get connection
create statement object A.
create statement object B.
use statement object A to create ResultSet object AA using SQL query.
scroll thru ResulSet object AA to retrieve data.
use statement object B to create ResultSet object BB using SQL query.
scroll thru ResulSet object BB to retrieve data.
close ResultSet object BB.
close ResultSet object AA.
close statement object
close database connection object.
I found (by trial and error, and a little commonsense)
- nested Resultsets do not seem to be the way to go. (couldn't get this
style to work with Oracle classes12 jdbc driver)
- Using multiple statements off the one database connection seems to work
OK.
Other 'thing' I picked up was that Prepared Statements are supposed to be
much faster (by 20-40%) according to the sun jdbc API reference book.
Comments anyone??
brief code example below.
(this code compiled and ran OK, you may need to change to suit your
postgres datafile name.)
import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.*;
import javax.servlet.*;
import javax.servlet.http.*;
/*
Adds forecast to nominated project.
*/
public class simpleExample extends HttpServlet {
public static String usernamepassword = "postgres";
private boolean debug=true;
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException,IOException
//doGet > doPost, enables servlet to responde to both types of
http calls.
{
doPost(req, res);
}
//
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException,IOException
{
PrintWriter out = res.getWriter();
res.setContentType("text/html");
Connection m_connection = null;
Statement stmt = null;
ResultSet rs = null;
out.println("servlet simpleExample called.<BR>");
//
try
{ //open data base connection.
out.println("opening connection.<BR>");
m_connection = GetDBConnection(out);
m_connection.setAutoCommit(false);
out.println("connection opened, creating statement.<BR>");
stmt = m_connection.createStatement();
out.println("statement created.<BR>");
//
try
{//
getListPlayers(m_connection, out);
//
stmt.close();
m_connection.commit();
//commit only needed for servlets updating or inserting.
not req'd 4 select use anyway
m_connection.close();
}//
catch(Exception ex)
{//
out.println("Error retrieving project
information.<BR>"+ex.toString()+"<br>");
out.println("</body></html>");
}//
} catch(Exception ex){//
out.println("Error connecting to database. Exception
information<BR>"+ex.toString()+"<br>");
out.println("</body></html>");
}//end try catch
} // end doGet
//methods below.
//
public void getListPlayers(Connection m_connection, PrintWriter
out)
{
boolean debug=true;
Statement stmt = null;
String getPersDetails = "select * from simpleperson";
int age = 0;
String fName=null, team = null;
try
{
stmt = m_connection.createStatement();
//
if(debug)out.println("sampleMethod1 executing
getPersDetails="+getPersDetails+"<BR>");
ResultSet rs = stmt.executeQuery(getPersDetails);
out.println("<form><table border='1'>");
out.println("<tr><td>fName</td><td>age</td><td>team</td></tr>");
if (rs !=null)
while (rs.next() )
{
age = rs.getInt("age");
fName = rs.getString("fName");
team = rs.getString("team");
out.println("<tr><td>"+fName+"</td><td>"+age+"</td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeams(m_connection, out,
team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeams(m_connection, out,
team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeamsWstmt(stmt, out, team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeamsWstmt(stmt, out, team);
out.println("</select></td>");
out.println("<td><select
name='teamName' size='1'><option>"+team+"</option>");
getListTeamsWstmt(stmt, out, team);
out.println("</select></td>");
out.println("</tr>");
}
out.println("</table></form>");
rs.close();
stmt.close();
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in
getListPlayers. "
+ "<br>ex.toString() = " +
ex.toString() + "<br>");
}//
}
//
public void getListTeams(Connection m_connection, PrintWriter out,
String tName)
{
boolean debug=false, noRecords=true;
Statement stmt = null;
String getTeamInfo = "select * from simpleTeam where name
<> '"+tName+"'";
int age = 0;
String teamName=null;
try
{
stmt = m_connection.createStatement();
//
if(debug)out.println("getListTeams executing
getTeamInfo="+getTeamInfo+"<BR>");
ResultSet rs = stmt.executeQuery(getTeamInfo);
if (rs !=null)
while (rs.next() )
{
teamName = rs.getString("Name");
out.println("<option>"+teamName+"</option>");
noRecords = false;
}
else out.println("<option>No Records
Found.</option>");
rs.close();
stmt.close();
if(noRecords)out.println("<option>noRecords=T.</option>");
else out.println("<option>noRecords=F.</option>");
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in
getListPlayers. "
+ "<br>ex.toString() = " +
ex.toString() + "<br>");
}//
}
//
//
public void getListTeamsWstmt(Statement stmt, PrintWriter out,
String tName)
{
boolean debug=false;
String getTeamInfo = "select * from simpleTeam where name
<> '"+tName+"'";
int age = 0;
String teamName=null;
ResultSet rs = null;
try
{
//
if(debug)out.println("getListTeams executing
getTeamInfo="+getTeamInfo+"<BR>");
rs = stmt.executeQuery(getTeamInfo);
if (rs !=null)
while (rs.next() )
{
teamName = rs.getString("Name");
out.println("<option>"+teamName+"</option>");
}
else out.println("<option>No Records
Found.</option>");
rs.close();
out.println("<option>getListTeamsWstmt</option>");
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in
getListPlayers. "
+ "<br>ex.toString() = " +
ex.toString() + "<br>");
}//
}
//
public Connection GetDBConnection(PrintWriter out)
throws Exception
/*
Returns database connection, enables easy change to connect to different
connections.
*/
{
Connection Parent_connection = null;
//
Properties info = new Properties();
info.put("user",usernamepassword);
info.put("password",usernamepassword);
boolean debug=false;
try
{ //open data base connection.
if(debug)out.println("loading driver.<br>");
Class.forName("org.postgresql.Driver");
if(debug)out.println("driver loaded, getting connection to
database as "+usernamepassword+"<br>");
Parent_connection =
DriverManager.getConnection("jdbc:postgresql:template1","postgres","postgres
");
if(debug)out.println("Connected<br>");
} catch(Exception ex)
{ //
out.println("Error connecting to database. Exception
information<BR>"+ex.toString()+"<br>");
}//
return Parent_connection;
}//end
//
//
}//end ReviewForeCast
(script below to generate tables)
--simpleExample.sql
drop table simplePerson;
drop table simpleTeam;
create table simplePerson(
fName Varchar(20) NOT NULL,
age numeric(5),
team Varchar(20)
);
create table simpleTeam(
Name Varchar(20) NOT NULL,
CoachName Varchar(20)
);
--now insert some records.
insert into simplePerson (fName, age, team) values('fred', 15, 'red');
insert into simplePerson (fName, age, team) values('tim', 16, 'red');
insert into simplePerson (fName, age, team) values('mike', 14, 'red');
insert into simplePerson (fName, age, team) values('fred', 20, 'blue');
insert into simplePerson (fName, age, team) values('tim', 21, 'blue');
insert into simplePerson (fName, age, team) values('mike', 22, 'blue');
insert into simpleTeam (Name, coachName) values('red', 'Mr Smith');
insert into simpleTeam (Name, coachName) values('blue', 'Mr Jones');
insert into simpleTeam (Name, coachName) values('green', 'Ms LongName');
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Quoting "bmatthewtaylor@yahoo.co.uk" <bmatthewtaylor@yahoo.co.uk>: > A few days ago I posted a query regarding a problem I had with errors I > was > incurring doing some nested queries with the jdbc driver. (having > recently > done a re-install my email history is in limbo for the moment) > > Found some of my problems were in messy ugly code, fixed some of the > problems by creating a new connection for the loop that was throwing > errors. > > Still curious on what would be 'good' style to use. > > Curious if anyone knows what can/should work and if this varies with > jdbc/odbc drivers. I assume the jdbc standards dictate certain behaviour > > for jdbc conformance, is there a spec I should be reading? Does the > conformance of drivers with the spec vary widely from one implementation > to > another?? (I'm interested in the differences between the Oracle > classes12 > and Postgresql-jdbc drivers specifically but general comments sought) The JDBC Specifications (1.2, 2.0 and soon 3.0 [May I was told yesterday]). Look on http://java.sun.com > pseudocode examples > 1. Non nested loops. > get connection > create statement object. > > use statement object to create ResultSet object using SQL query. > scroll thru ResulSet object to retrieve data. > close ResultSet object. > > use statement object to create ResultSet object using SQL query. > scroll thru ResulSet object to retrieve data. > close ResultSet object. > > .... repeat as many times as required. Perfectly fine. Technically you can't have two ResultSet's open from one Statement, but you may reuse Statement once the ResultSet's been closed. In fact the specs say that Statement should implicitly close it's open ResultSet before returning the new one. > close statement object > close database connection object. > > 2. Nested loops, I use this format when I'm say printing a record of > information to a table, and need to present a combo box selection (inner > > nested loop) that can only be generated once the row data is known > (outer > loop). > > get connection > create statement object A. > create statement object B. > > use statement object A to create ResultSet object AA using SQL query. > scroll thru ResulSet object AA to retrieve data. > > use statement object B to create ResultSet object BB using SQL query. > scroll thru ResulSet object BB to retrieve data. > close ResultSet object BB. > > > close ResultSet object AA. > > close statement object > close database connection object. Perfectly fine according to the specs. You can have as many Statement/PreparedStatement/CallableStatement objects open at any one time. Some drivers have problems because they don't manage the network protocol correctly and get in a mess. Not sure about Oracle (I use 8i) but PostgreSQL works fine here (see the Thread Safety tests in the source). > > I found (by trial and error, and a little commonsense) > - nested Resultsets do not seem to be the way to go. (couldn't get this > style to work with Oracle classes12 jdbc driver) Hmmm, what version are you using of oracle's classes? I think this is one point we beat them then ;-) > - Using multiple statements off the one database connection seems to > work OK. > > Other 'thing' I picked up was that Prepared Statements are supposed to > be > much faster (by 20-40%) according to the sun jdbc API reference book. Yes, except we currently implement them in the driver. When the backend handles prepared statements, the performance boost will suddenly appear. The boost is where the Query Planner precompiles the query once, then the plan is executed for each update. Currently the plan is compiled for each call. > > Comments anyone?? > > brief code example below. > > > (this code compiled and ran OK, you may need to change to suit your > postgres datafile name.) > > import java.sql.*; > import java.util.*; > import java.io.*; > import java.lang.*; > import javax.servlet.*; > import javax.servlet.http.*; > /* > Adds forecast to nominated project. > */ > public class simpleExample extends HttpServlet { Ooo Servlets, started playing with them yesterday ;-) [snip] Peter -- Peter Mount peter@retep.org.uk PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/