Re: Servlets with JDBC connectivity
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
* LIResultSet doesn't necessarily have all the data;
* reconnection to database occurs as you ask for
* later rows.
* LIThis class stores results as strings, in arrays.
* LIThis class includes DatabaseMetaData (database product
* name and version) and ResultSetMetaData
* (the column names).
* LIThis 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/.
* copy; 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
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
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]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Servlets with JDBC connectivity
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
