Design Question

[Jeremy Hooie]

I'm not sure if I'm asking this in the right forum, but I'm trying to design a class structure for database access. I want to create a class which holds the most frequently used statements. Right now, I have a class which executes the statements and returns the result set.

import java.sql.*;
public class PreparedSQL
{
  private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
  private String url = "jdbc:odbc:database";
  private String user = "user";
  private String password = "password";
  private Connection conn;
  public PreparedSQL() throws ClassNotFoundException
  {
    Class.forName(driver);
  }
  public ResultSet getFormList() throws SQLException
  {
    conn = DriverManager.getConnection(url);
    Statement statement = conn.createStatement();
    ResultSet formList = statement.executeQuery
      ("SELECT form FROM x_form WHERE status = 'Active'");
    conn.close();
    return formList;
  }
}

Then, in my calling class, I instantiate the PreparedSQL class.

import java.sql.*;
public class UserForms extends JFrame
{
  private PreparedSQL prepared;
  public UserForms()
  {
    try
    {
      prepared = new PreparedSQL();
    }
    catch (ClassNotFoundException cnf)
    {
      System.err.println("Prepared Statements not Found" + cnf);
    }
  }
 ResultSet forms
  try
  {
    forms = prepared.getFormList();
  }
  catch (SQLException sql)
  {
    System.err.println("Error Initializing Prepared Statements: " + sql);
  }
}

My question is, is this a good design? What are the pros and cons of doing something else, say returning a Vector instead of the recordset? If anyone has any tips (or knows of any good books), I would be most appreciative.
Sorry, it put smilies for in jdbcdbc!
[This message has been edited by Jeremy Hooie (edited November 09, 2001).]

[Jamie Robertson]

unfortunately this design won't work.
More specifically:

ResultSet formList = statement.executeQuery("SELECT form FROM x_form WHERE status = 'Active'");
conn.close();    
return formList;

The problem here lies in the fact that when you close your connection, it implicitly closes all statements and resultsets associated with the connection. So return formList will have no data after you close the connection. Another design performance error is that everytime you query the database you need to create a new connection. Usually this is a very expensive(timewise) task. I would create a member variable connection, create it once, and use it throughout the life of the prepareSQL object.
Some ideas if you need the design that you have set up is to return a Vector or ArrayList(ArrayList being the preferred data structure).
if I get some time to give you a sample, I'll post it
Jamie

[This message has been edited by Jamie Robertson (edited November 09, 2001).]

[Jamie Robertson]

here is a quick adaptation of your code:

import java.sql.*;
public class PreparedSQL
{  
private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";  
private String url = "jdbc [img]http://www.javaranch.com/ubb/redface.gif[/img]dbc:database";  
private String user = "user";  
private String password = "password";  
private Connection conn;  
public PreparedSQL() throws ClassNotFoundException  
{    
Class.forName(driver);
conn = DriverManager.getConnection(url);
}  
public ResultSet getFormList(int maxRows) throws SQLException  
{    
Statement statement = conn.createStatement();    
statement.setMaxRows(maxRows); //can use this depending on the number of rows that you expect to return
ResultSet formList = statement.executeQuery("SELECT form FROM x_form WHERE status = 'Active'");    
ArrayList row = new ArrayList(1);
ArrayList table = new ArrayList();
int i=0;
while (formList.next())
{
row.add(0,formList.getString(1));
table.add(i,row);
i++;
}
row = null;
return table;
}
public void closeConnection() throws SQLException
{
conn.close();
conn=null;
driver = null;
...
}

}
public class UserForms extends JFrame
{  
private PreparedSQL prepared;  
public UserForms()  
{    
try    
{      
prepared = new PreparedSQL();    
}    
catch (ClassNotFoundException cnf)    
{      
System.err.println("Prepared Statements not Found" + cnf);    
}  
catch (SQLException sql)  
{    
System.err.println("Error Initializing Prepared Statements: " + sql);  
}
} 
  
try  
{    
ArrayList forms = prepared.getFormList();
}  
catch (SQLException sql)  
{    
System.err.println("Error Executing Prepared Statements: " + sql.getMessage());  
sql.printStackTrace();
}
//at end of session after all queries have been completed call prepared.closeConnection();

}

This is only an idea, I haven't tried to compile, and I don't know what your specs need.
Jamie

[Jeremy Hooie]

Thanks, Jamie. I had not thought about the extra overhead caused by creating connections every time . But what, if any, is the benefit of returning an ArrayList instead of using a member variable connection and returning the result set?
Jeremy

[Jamie Robertson]

assuming you are using this method repeatedly for every instance of PrepareSQL. If you keep creating ResultSets then returning them, you are leaving open MANY ResultSets and Statements which can cause resource problems(like too many open cursors). How are you going to close the ResultSets and Statements from the calling class? You only have access to the ResultSet you have been returned, so how do you close the associated statement? It will eventually be released by closeConnection() but until then, it will keep eating up resources. One technique to get around this is to declare the ResultSet and Statement as member variables:

import java.sql.*;
public class PreparedSQL
{  
private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";  
private String url = "jdbc dbc:database";  
private String user = "user";  
private String password = "password";  
private Connection conn;  
private Statement statement;
private ResultSet formList;

public PreparedSQL() throws ClassNotFoundException  
{    
Class.forName(driver);
conn = DriverManager.getConnection(url);
}  

public void setFormList(int maxRows) throws SQLException  
{    
statement = conn.createStatement();    
statement.setMaxRows(maxRows); //can use this depending on the number of rows that you expect to return
formList = statement.executeQuery("SELECT form FROM x_form WHERE status = 'Active'");    
}

public ResultSet getFormList()
{
return formList;
}

public void closeConnection() throws SQLException
{
conn.close();
conn=null;
driver = null;
...
}

public void closeFormList()
{
formList.close();
formList=null;
}

public void closeStatement() throws SQLException
{
statement.close();
statement= null;
}

}
public class UserForms extends JFrame
{  
private PreparedSQL prepared;  
public UserForms()  
{    
try    
{      
prepared = new PreparedSQL();    
}    
catch (ClassNotFoundException cnf)    
{      
System.err.println("Prepared Statements not Found" + cnf.getMessage());    
}  
catch (SQLException sql)  
{    
System.err.println("Error Initializing Prepared Statements: " + sql);  
}
}   
try  
{    
prepared.setFormList();
ResultSet forms = prepared.getFormList();
//process the resultset...
//when done with resultset...
prepared.closeFormList();
prepared.closeStatement();


}  
catch (SQLException sql)  
{    
System.err.println("Error Executing Prepared Statements: " + sql.getMessage());  
sql.printStackTrace();
}
//at end of session after all queries have been completed call prepared.closeConnection();
}

Jamie