This is how I handle mine modified for your application.
import java.sql.*;
public class DBUtil {
/** Retrieves results from query as a DBResults class.
*/
public static DBResults getQueryResults(String query, String dBase) {
Connection connection = Conn.getConn(dBase);
Statement statement = null;
ResultSet resultSet = null;
DBResults dbResults = null;
boolean good = false;
try {
DatabaseMetaData dbMetaData = connection.getMetaData();
String productName = dbMetaData.getDatabaseProductName();
String productVersion = dbMetaData.getDatabaseProductVersion();
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
ResultSetMetaData resultsMetaData = resultSet.getMetaData();
int columnCount = resultsMetaData.getColumnCount();
String[] columnNames = new String[columnCount];
// Column index starts at 1 (a la SQL) not 0 (a la Java).
for (int i = 1; i < columnCount + 1; i++) {
columnNames[i - 1] = resultsMetaData.getColumnName(i).trim();
}
dbResults =
new DBResults(
connection,
productName,
productVersion,
columnCount,
columnNames);
while (resultSet.next()) {
String[] row = new String[columnCount];
// Again, ResultSet index starts at 1, not 0.
for (int i = 1; i < columnCount + 1; i++) {
String entry = resultSet.getString(i);
if (entry != null) {
entry = entry.trim();
}
row[i - 1] = entry;
}
dbResults.addRow(row);
}
good = true;
} catch (SQLException sqle) {
System.err.println("Error connecting: " + sqle);
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
System.err.println("DataBaseUtilities Error closing resultset: " + e);
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
System.err.println("DataBaseUtilities Error closing statement: " + e);
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
System.err.println("DataBaseUtilities Error closing connection: " + e);
}
connection = null;
}
if (good)
return (dbResults);
else
return (null);
}
}
/** Runs update query.
*/
public static void setUpdate(String query, String dBase) {
Connection connection = Conn.getConn(dBase);
Statement statement = null;
boolean good = false;
try {
statement = connection.createStatement();
statement.executeUpdate(query);
} catch (SQLException sqle) {
System.err.println("Error connecting: " + sqle);
} finally {
// Always make sure statements are closed,
// and the connection is returned to the pool
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
System.err.println("DataBaseUtilities Error closing statement: " + e);
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
System.err.println("DataBaseUtilities Error closing connection: " + e);
}
connection = null;
}
}
}
}
And to handle the data from the resultset that would otherwise be
unaccessable it is places in an DBResults object.
package srm;
import java.sql.*;
import java.util.*;
/** Class to store completed results of a JDBC Query.
* Differs from a ResultSet in several ways:
* <UL>
* <LI>ResultSet doesn't necessarily have all the data;
* reconnection to database occurs as you ask for
* later rows.
* <LI>This class stores results as strings, in arrays.
* <LI>This class includes DatabaseMetaData (database product
* name and version) and ResultSetMetaData
* (the column names).
* <LI>This class has a toHTMLTable method that turns
* the results into a long string corresponding to
* an HTML table.
* </UL>
* <P>
* Taken from Core Servlets and JavaServer Pages
* from Prentice Hall and Sun Microsystems Press,
* http://www.coreservlets.com/.
* © 2000 Marty Hall; may be freely used or adapted.
*/
public class DBResults {
private Connection connection;
private String productName;
private String productVersion;
private int columnCount;
private String[] columnNames;
private Vector queryResults;
String[] rowData;
public DBResults(Connection connection,
String productName,
String productVersion,
int columnCount,
String[] columnNames) {
this.connection = connection;
this.productName = productName;
this.productVersion = productVersion;
this.columnCount = columnCount;
this.columnNames = columnNames;
rowData = new String[columnCount];
queryResults = new Vector();
}
public void addRow(String[] row) {
queryResults.addElement(row);
}
public int getColumnCount() {
return(columnCount);
}
public String[] getColumnNames() {
return(columnNames);
}
public Connection getConnection() {
return(connection);
}
public String getProductName() {
return(productName);
}
public String getProductVersion() {
return(productVersion);
}
public String[] getRow(int index) {
return((String[])queryResults.elementAt(index));
}
public int getRowCount() {
return(queryResults.size());
}
/** returns the row and column equivelent from the DBResults */
public String getData(int r, int c){
return(((String[])queryResults.elementAt(r))[c]);
}
/** returns the row and column equivelent from the DBResults or empty string
if null or out of bounds*/
public String getDataP(int r, int c){
try{
return(((String[])queryResults.elementAt(r))[c]);
}catch(ArrayIndexOutOfBoundsException e){return "";}
}
/** Output the results as an HTML table, with
* the column names as headings and the rest of
* the results filling regular data cells.
*/
public String toHTMLTable(String headingColor) {
StringBuffer buffer =
new StringBuffer("<TABLE BORDER=1>\n");
if (headingColor != null) {
buffer.append(" <TR BGCOLOR=\"" + headingColor +
"\">\n ");
} else {
buffer.append(" <TR>\n ");
}
for(int col=0; col<getColumnCount(); col++) {
buffer.append("<TH>" + columnNames[col]);
}
for(int row=0; row<getRowCount(); row++) {
buffer.append("\n <TR>\n ");
String[] rowData = getRow(row);
for(int col=0; col<getColumnCount(); col++) {
buffer.append("<TD>" + rowData[col]);
}
}
buffer.append("\n</TABLE>");
return(buffer.toString());
}
}
This has one drawback, you cannot take advantage of an updatable recordset.
Any changes must be written back to the database with an update.
I added the getData(int,int) method to grab data using row, column position.
Note that these are 0 indexed not 1 indexed like a resultset.
> Todd,
>
> > SQLUtils.executeQuery("a SQL statement");
> > SQLUtils.executeUpdate("another one");
>
> Just out of curiosity, what do these methods return? If the former
> returns a ResultSet object, then you're in for a world of trouble. The
> ResultSet will never get closed, or you'll close the connection over
> which the ResultSet communicates, and then you're hosed, 'cause the
> calling code can't access the data.
>
> -chris
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
