Let's say you have three databases:
data1 data2 data3
And three apps:
app1 app2 app3
If app1 hits data1 and app2 hits data2 and .....
You are on a one to one app to database.
If app1 hit data1 and app2 hits data1 and app3 hits data3 and app4 hits data2
You are many to one and one to one.
If you have app1 hits data1 and app1 hits data2
then you have a special case.
For one to ones you can create you data resource in the context element for the app as no other app will need access to the database.
For many to one you will want to put your resource in the server.xml as a Globalrsource and links in the context for each app that will access that database.
You can have more than one Globalresource.
And here are some class examples for using the datasource.
Servlet for getting connection. ***********************************************************
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;
}
}
Class to make db calls ******************************************************************** package yourPackage; 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 yourPackage;
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.
----- Original Message ----- From: "Krishnakant Mane" <[EMAIL PROTECTED]>
To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
Sent: Tuesday, April 05, 2005 3:37 AM
Subject: Re: still not clear with connection pooling in tomcat
hello doug, I will be using this tomcat server for many web applications. and yes the mysql instance will be the same. I just run one mysqld instance for my server. there will be different databases and different web applications. thanks Krishnakant.
Send instant messages to your online friends http://uk.messenger.yahoo.com
--------------------------------------------------------------------- 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]
