You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Todd O'Bryan <to...@mac.com> on 2003/12/03 04:18:45 UTC
Servlets with JDBC connectivity
This may not be the right place to ask this, but if you can direct me
to the right place, I'd appreciate it.
I'm looking for a design pattern that someone must have already thought
through so that my head can stop hurting. Here's the problem:
I'm designing a webapp that has several servlets that all access a
database (or a couple of databases, actually) to either update or
retrieve information.
Rather than initializing these connections multiple times, dealing with
SQLExceptions in every class that uses JDBC queries, and generally
doing things multiple times, I thought I'd put all the database stuff
into a single class. I created a setUp() method that initialized the
database connection and then used static methods so I could do
something like
SQLUtils.executeQuery("a SQL statement");
SQLUtils.executeUpdate("another one");
anywhere in my webapp. If the database connection had not been created,
it got created before executing the SQL statement. If it was already
created, it just got done. I handled all the nastiest exceptions in the
SQLUtils class, so I didn't have to deal with them elsewhere.
You can probably guess the next part. I've discovered I need to connect
to more than one database, and this design does not support that.
Creating instances of a SQLUtil class would be a big pain, because then
I have to pass those around between my servlets and I lose one of the
huge advantages of this approach, namely a single, globally visible
interface to the database.
I thought about multiple classes, one for each database I'm connecting
to, but I know that can't be right on so many levels. Meanwhile, I'm a
little stumped.
How do people handle this elegantly? The requirements are: a single,
globally visible (within a webapp) database interface and the ability
to access multiple databases easily.
Thanks in advance for any ideas,
Todd
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Todd O'Bryan <to...@mac.com>.
On Dec 3, 2003, at 6:59 PM, Kwok Peng Tuck wrote:
> >But this means I still have to get a connection, create a statement,
> and execute a query or update on the statement >in every servlet where
> I want to use the connection. Yes, it locates the connection details
> (i.e., the JDBC >connection method, the database name, user and
> password) somewhere centrally so that I don't have to keep >
> coding it, but all of the connection overhead still has to be dealt
> with in every servlet in a webapp.
>
> The datasource solution that a few people in the list (including me :)
> ) have been asking you to look at provides a connection pool to your
> web application. In your code where you ask for a connection from
> JNDI, you are actually getting a connection from the pool, which is
> already setup. When you call the close() method of this connection,
> it is not actually destoryed but returned to the connection pool,
> ready to be used.
>
>
I'm sorry. When I said "connection overhead," I didn't mean the
overhead of creating a Connection object, I meant the overhead of
having to write all the code to create one. I understand that there's a
connection pool in a central location, but getting a connection from
that pool is no less complicated for the programmer than creating one
from scratch.
I'm incredibly lazy. It seems that if I'm only going to be connecting
to two or three databases in a webapp, it should be possible to
maintain open connections to those databases somewhere so that anywhere
in my webapp I can issue a statement that runs a SQL statement. In
other words, I want to encapsulate the connection creation so that my
other programs can just assume that it's done. This may not be
possible, but it seems a reasonable OO thing to do.
Does that make more sense, or am I still just confused?
Todd
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Kwok Peng Tuck <pe...@makmal.net>.
>But this means I still have to get a connection, create a statement,
and execute a query or update on the statement >in every servlet where I
want to use the connection. Yes, it locates the connection details
(i.e., the JDBC >connection method, the database name, user and
password) somewhere centrally so that I don't have to keep >
coding it, but all of the connection overhead still has to be dealt with
in every servlet in a webapp.
The datasource solution that a few people in the list (including me :)
) have been asking you to look at provides a connection pool to your
web application. In your code where you ask for a connection from JNDI,
you are actually getting a connection from the pool, which is already
setup. When you call the close() method of this connection, it is not
actually destoryed but returned to the connection pool, ready to be used.
Todd O'Bryan wrote:
>
> On Dec 3, 2003, at 2:59 AM, Nikola Milutinovic wrote:
>
>> Peter Harrison wrote:
>>
>>> On Wed, 03 Dec 2003 16:18, Todd O'Bryan wrote:
>>>
>>>> How do people handle this elegantly? The requirements are: a single,
>>>> globally visible (within a webapp) database interface and the ability
>>>> to access multiple databases easily.
>>>
>>> The first point is to use a singleton to set up the database
>>> connection - or more correctly the connection pool. This way you can
>>> request a connection and return it to the pool easily. Of course
>>> every time you use one you will have to use try-catch blocks. Sorry
>>> no way around that.
>>
>>
>> Both Tomcat and J2EE specification support javax.sql.DataSource
>> objects over JNDI. That is how we handle it elegantly.
>>
>> A DataSource object is specified by the administrator and created by
>> the container. Container then deploys it under specified JNDI name. A
>> servlet (or EJB) can then lookup this object and use it, something
>> like this:
>>
>> import java.sql.*;
>> import javax.sql.*;
>> import javax.naming.*;
>>
>> InitialContext ic = new InitialContext();
>> DataSource ds = (DataSource)ic.lookup(
>> "java:comp/env/jdbc/MyDataSource" );
>> Connection conn = ds.getConnection();
>>
>
>
>
> At least, I think that's what it's doing. Am I missing something?
>
> Here's what I want. In every servlet in my webapp, I'm only going to
> be using one of a very few database connections. I'd like to be able
> to do something like:
>
> Test.executeQuery("SQL")
>
> without doing any setup because the Test class handles all the setup,
> initializes the connection the first time it's called, etc.
>
> Basically, it's a singleton class that never gets an instance created
> because if I create an instance, I'd have to pass it around to all the
> servlets in the webapp, which would kill the convenience of having it
> be available with minimal overhead.
>
> Oo, oo, oo...
>
> As I've been sitting here writing this, I had a brainstorm. What if I
> create an abstract class that has all the database connectivity built
> into it as static methods, but is just waiting for a connection
> method, a username, and a password. Then creating a new JDBC
> connection which is visible to all my servlets is just a matter of
> making a concrete subclass that defines those three variables and
> inherits all the functionality from its abstract parent class.
>
> Any reason this is a horrible idea?
>
> Thanks for being patient,
> Todd
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Todd O'Bryan <to...@mac.com>.
On Dec 3, 2003, at 5:40 AM, Todd O'Bryan wrote:
>
> On Dec 3, 2003, at 2:59 AM, Nikola Milutinovic wrote:
>
>> Peter Harrison wrote:
>>> On Wed, 03 Dec 2003 16:18, Todd O'Bryan wrote:
>>>> How do people handle this elegantly? The requirements are: a single,
>>>> globally visible (within a webapp) database interface and the
>>>> ability
>>>> to access multiple databases easily.
>>> The first point is to use a singleton to set up the database
>>> connection - or more correctly the connection pool. This way you can
>>> request a connection and return it to the pool easily. Of course
>>> every time you use one you will have to use try-catch blocks. Sorry
>>> no way around that.
>>
>> Both Tomcat and J2EE specification support javax.sql.DataSource
>> objects over JNDI. That is how we handle it elegantly.
>>
>> A DataSource object is specified by the administrator and created by
>> the container. Container then deploys it under specified JNDI name. A
>> servlet (or EJB) can then lookup this object and use it, something
>> like this:
>>
>> import java.sql.*;
>> import javax.sql.*;
>> import javax.naming.*;
>>
>> InitialContext ic = new InitialContext();
>> DataSource ds = (DataSource)ic.lookup(
>> "java:comp/env/jdbc/MyDataSource" );
>> Connection conn = ds.getConnection();
>>
>
> But this means I still have to get a connection, create a statement,
> and execute a query or update on the statement in every servlet where
> I want to use the connection. Yes, it locates the connection details
> (i.e., the JDBC connection method, the database name, user and
> password) somewhere centrally so that I don't have to keep coding it,
> but all of the connection overhead still has to be dealt with in every
> servlet in a webapp.
>
> At least, I think that's what it's doing. Am I missing something?
>
> Here's what I want. In every servlet in my webapp, I'm only going to
> be using one of a very few database connections. I'd like to be able
> to do something like:
>
> Test.executeQuery("SQL")
>
> without doing any setup because the Test class handles all the setup,
> initializes the connection the first time it's called, etc.
>
> Basically, it's a singleton class that never gets an instance created
> because if I create an instance, I'd have to pass it around to all the
> servlets in the webapp, which would kill the convenience of having it
> be available with minimal overhead.
>
> Oo, oo, oo...
>
> As I've been sitting here writing this, I had a brainstorm. What if I
> create an abstract class that has all the database connectivity built
> into it as static methods, but is just waiting for a connection
> method, a username, and a password. Then creating a new JDBC
> connection which is visible to all my servlets is just a matter of
> making a concrete subclass that defines those three variables and
> inherits all the functionality from its abstract parent class.
>
> Any reason this is a horrible idea?
>
> Thanks for being patient,
> Todd
>
Never mind. The compiler just reminded me that abstract and static
together are nonsensical. It must be too early in the morning.
I suppose I could write a constructor that initializes the database,
user, and password settings, and then use the instance. Instead of
ProductionDB.executeUpdate("a SQL statement");
I'd have
ProductionDB prod = new ProductionDB();
prod.executeUpdate("a SQL statement");
slightly more complicated than what I envisioned, but I could create
any connection I need in each servlet's constructor so it would only
have to be done once.
Once more, any obvious (or, better for my self-esteem, non-obvious)
reason this is a bad idea?
Thanks,
Todd
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Todd O'Bryan <to...@mac.com>.
On Dec 3, 2003, at 2:59 AM, Nikola Milutinovic wrote:
> Peter Harrison wrote:
>> On Wed, 03 Dec 2003 16:18, Todd O'Bryan wrote:
>>> How do people handle this elegantly? The requirements are: a single,
>>> globally visible (within a webapp) database interface and the ability
>>> to access multiple databases easily.
>> The first point is to use a singleton to set up the database
>> connection - or more correctly the connection pool. This way you can
>> request a connection and return it to the pool easily. Of course
>> every time you use one you will have to use try-catch blocks. Sorry
>> no way around that.
>
> Both Tomcat and J2EE specification support javax.sql.DataSource
> objects over JNDI. That is how we handle it elegantly.
>
> A DataSource object is specified by the administrator and created by
> the container. Container then deploys it under specified JNDI name. A
> servlet (or EJB) can then lookup this object and use it, something
> like this:
>
> import java.sql.*;
> import javax.sql.*;
> import javax.naming.*;
>
> InitialContext ic = new InitialContext();
> DataSource ds = (DataSource)ic.lookup(
> "java:comp/env/jdbc/MyDataSource" );
> Connection conn = ds.getConnection();
>
But this means I still have to get a connection, create a statement,
and execute a query or update on the statement in every servlet where I
want to use the connection. Yes, it locates the connection details
(i.e., the JDBC connection method, the database name, user and
password) somewhere centrally so that I don't have to keep coding it,
but all of the connection overhead still has to be dealt with in every
servlet in a webapp.
At least, I think that's what it's doing. Am I missing something?
Here's what I want. In every servlet in my webapp, I'm only going to be
using one of a very few database connections. I'd like to be able to do
something like:
Test.executeQuery("SQL")
without doing any setup because the Test class handles all the setup,
initializes the connection the first time it's called, etc.
Basically, it's a singleton class that never gets an instance created
because if I create an instance, I'd have to pass it around to all the
servlets in the webapp, which would kill the convenience of having it
be available with minimal overhead.
Oo, oo, oo...
As I've been sitting here writing this, I had a brainstorm. What if I
create an abstract class that has all the database connectivity built
into it as static methods, but is just waiting for a connection method,
a username, and a password. Then creating a new JDBC connection which
is visible to all my servlets is just a matter of making a concrete
subclass that defines those three variables and inherits all the
functionality from its abstract parent class.
Any reason this is a horrible idea?
Thanks for being patient,
Todd
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Nikola Milutinovic <Ni...@ev.co.yu>.
Peter Harrison wrote:
> On Wed, 03 Dec 2003 16:18, Todd O'Bryan wrote:
>
>
>>How do people handle this elegantly? The requirements are: a single,
>>globally visible (within a webapp) database interface and the ability
>>to access multiple databases easily.
>
>
> The first point is to use a singleton to set up the database connection - or
> more correctly the connection pool. This way you can request a connection and
> return it to the pool easily. Of course every time you use one you will have
> to use try-catch blocks. Sorry no way around that.
Both Tomcat and J2EE specification support javax.sql.DataSource objects over
JNDI. That is how we handle it elegantly.
A DataSource object is specified by the administrator and created by the
container. Container then deploys it under specified JNDI name. A servlet (or
EJB) can then lookup this object and use it, something like this:
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup( "java:comp/env/jdbc/MyDataSource" );
Connection conn = ds.getConnection();
Nix.
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Peter Harrison <pe...@nothingbutnet.co.nz>.
On Wed, 03 Dec 2003 16:18, Todd O'Bryan wrote:
> How do people handle this elegantly? The requirements are: a single,
> globally visible (within a webapp) database interface and the ability
> to access multiple databases easily.
The first point is to use a singleton to set up the database connection - or
more correctly the connection pool. This way you can request a connection and
return it to the pool easily. Of course every time you use one you will have
to use try-catch blocks. Sorry no way around that.
However, if you are clever you will create data objects to hide all that
stuff. Even better - why not get something to write all those horrid data
objects - sql and trycatch blocks for you.
Last year we (another developer any myself) developed a project called sysmod,
which essentially takes a xml description of a database schema and turns it
into Java code and a real SQL schema. This is by no means a unique approach -
tools like JBuilder allow you to use graphical UI's and UML to do something
similar - and is even two way.
However, our approach is somewhat lighter. All we have is the XML model. The
model generates the Java data objects. The code includes comments such that
it creates nice JavaDoc as well, so in effect you have a self documenting
business model from the XML. At this stage we have to write very little SQL,
and no SQL at all for updating and adding records.
The actions (we use struts) become very simple because in addition to being
able to load and save themselves from a database they can also load
themselves from a request, therefore our code looks something like this:
Client client = ClientFactory.newClient();
client.loadFromRequest( "client", request );
client.saveChanges();
This code essentially loads all the fields from the request prefixed by
"client" into the data object. Then the call to saveChanges() saves the
record to the database. The data objects also handle their own SQL
exceptions, although they throw their own exceptions.
The Factory for each data object also provides lists of various kinds, ie you
can call ClientFactory.findAll() and it will return a list of Client objects
prefilled by a single query. You can also do a findByQuery with parameters to
define the query.
The approach works very well, but is not without its drawbacks. It is quite
easy to code things which look logical but create hundreds of queries and is
inefficient. We are still working on ways to improve the system, such as
caching data. It is also limiting - in that while it creates DataObjects it
is not a trrue object hiracy. This is because the system is still based on a
relational database. In other words this system is not a relational mapping
tool. It is also not a object database.
However, it does provide a quick and easy way to prototype databases and the
Java code to access it. Currently the system creates objects that are not JDO
or EJB.
If you are interested I can provide it -
Regards,
Peter Harrison
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Doug Parsons <do...@rev.net>.
Based on the how-to and modified for your app:
package yourpackage;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
public class Conn {
/**Takes desired database as a string and returns a connection.
*/
public static Connection getConn(String dBase) {
Connection connection = null;
String osName = System.getProperty("os.name");
try {
//Start of Tomcat connect
Context ctx = new InitialContext();
if (ctx == null) {
System.err.println("Conn.getConn ctx is null");
throw new Exception("Boom - No Context");
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/" + dBase);
if (ds != null)
connection = ds.getConnection();
//End of Tomcat connect
} catch (Exception e) {
System.err.println("Conn.getConn " + e);
}
return connection;
}
}
> I think this link over here, might give you a hand.
>
>
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html
>
> There are samples there for databases like mysql, but I think you should
> be ok.
>
>
> Todd O'Bryan wrote:
>
> > This may not be the right place to ask this, but if you can direct me
> > to the right place, I'd appreciate it.
> >
> > I'm looking for a design pattern that someone must have already
> > thought through so that my head can stop hurting. Here's the problem:
> >
> > I'm designing a webapp that has several servlets that all access a
> > database (or a couple of databases, actually) to either update or
> > retrieve information.
> >
> > Rather than initializing these connections multiple times, dealing
> > with SQLExceptions in every class that uses JDBC queries, and
> > generally doing things multiple times, I thought I'd put all the
> > database stuff into a single class. I created a setUp() method that
> > initialized the database connection and then used static methods so I
> > could do something like
> >
> > SQLUtils.executeQuery("a SQL statement");
> > SQLUtils.executeUpdate("another one");
> >
> > anywhere in my webapp. If the database connection had not been
> > created, it got created before executing the SQL statement. If it was
> > already created, it just got done. I handled all the nastiest
> > exceptions in the SQLUtils class, so I didn't have to deal with them
> > elsewhere.
> >
> > You can probably guess the next part. I've discovered I need to
> > connect to more than one database, and this design does not support
> > that. Creating instances of a SQLUtil class would be a big pain,
> > because then I have to pass those around between my servlets and I
> > lose one of the huge advantages of this approach, namely a single,
> > globally visible interface to the database.
> >
> > I thought about multiple classes, one for each database I'm connecting
> > to, but I know that can't be right on so many levels. Meanwhile, I'm a
> > little stumped.
> >
> > How do people handle this elegantly? The requirements are: a single,
> > globally visible (within a webapp) database interface and the ability
> > to access multiple databases easily.
> >
> > Thanks in advance for any ideas,
> > Todd
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
> >
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Kwok Peng Tuck <pe...@makmal.net>.
I think this link over here, might give you a hand.
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html
There are samples there for databases like mysql, but I think you should
be ok.
Todd O'Bryan wrote:
> This may not be the right place to ask this, but if you can direct me
> to the right place, I'd appreciate it.
>
> I'm looking for a design pattern that someone must have already
> thought through so that my head can stop hurting. Here's the problem:
>
> I'm designing a webapp that has several servlets that all access a
> database (or a couple of databases, actually) to either update or
> retrieve information.
>
> Rather than initializing these connections multiple times, dealing
> with SQLExceptions in every class that uses JDBC queries, and
> generally doing things multiple times, I thought I'd put all the
> database stuff into a single class. I created a setUp() method that
> initialized the database connection and then used static methods so I
> could do something like
>
> SQLUtils.executeQuery("a SQL statement");
> SQLUtils.executeUpdate("another one");
>
> anywhere in my webapp. If the database connection had not been
> created, it got created before executing the SQL statement. If it was
> already created, it just got done. I handled all the nastiest
> exceptions in the SQLUtils class, so I didn't have to deal with them
> elsewhere.
>
> You can probably guess the next part. I've discovered I need to
> connect to more than one database, and this design does not support
> that. Creating instances of a SQLUtil class would be a big pain,
> because then I have to pass those around between my servlets and I
> lose one of the huge advantages of this approach, namely a single,
> globally visible interface to the database.
>
> I thought about multiple classes, one for each database I'm connecting
> to, but I know that can't be right on so many levels. Meanwhile, I'm a
> little stumped.
>
> How do people handle this elegantly? The requirements are: a single,
> globally visible (within a webapp) database interface and the ability
> to access multiple databases easily.
>
> Thanks in advance for any ideas,
> Todd
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Todd O'Bryan <to...@mac.com>.
Thanks, Doug. I'll have a look at this today and make sure I understand
it.
Todd
On Dec 3, 2003, at 11:30 PM, Doug Parsons wrote:
> The whole class I need, apparently.
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Doug Parsons <do...@rev.net>.
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: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Todd O'Bryan <to...@mac.com>.
I hadn't noticed that ResultSets need to be closed. But why couldn't I
close it after dealing with it in whichever servlet I call it from?
Also, if the Connection is a static variable in SQLUtils that all the
servlets use, it won't ever get closed.
I'm just realizing that there's probably a major scalability issue here
that I need to worry about, but I'm still thinking this has to be
easier...
Todd
P.S. A little context, so you all don't tell me to get another job. I
teach high school math and computer science, and in one of my advanced
classes we decided to create a webapp. One group is working on the
database backend and another group is writing Velocity templates to
display the information. I'm learning as we go, because I've written
about 5 or 6 total servlets since last summer. I've got the Java
Servlets book and the JDBC book from O'Reilly, but I haven't had time
to read them cover to cover, so there may be gaps in my knowledge. All
my database knowledge has also been picked up since last summer, and I
spend maybe an hour a day actually writing code.
My concern is that we set this up in a way that is sound, but perhaps
more importantly, clean and easy to understand. Since it's me and a
bunch of high school seniors who'll be messing up the code, something
straightforward seems the way to go.
Thanks again for your patience.
On Dec 3, 2003, at 12:27 PM, Christopher Schultz wrote:
> 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: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Servlets with JDBC connectivity
Posted by Christopher Schultz <ch...@comcast.net>.
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: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org