RE: jdbc, use of nested ResultSet loops.(longish, interesting I hope)

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(at)yahoo(dot)co(dot)uk [mailto:bmatthewtaylor(at)yahoo(dot)co(dot)uk]
Sent: Sunday, January 28, 2001 6:22 AM
To: pgsql-general(at)postgresql(dot)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