tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Parsons Technical Services" <parsonstechni...@earthlink.net>
Subject Re: still not clear with connection pooling in tomcat
Date Wed, 06 Apr 2005 02:42:32 GMT
I hate to keep asking questions, but I want to make sure to give you the 
correct setup. Other wise you will have to change it later on.

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/.

* &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 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" <compactsolution@yahoo.co.uk>
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: 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


Mime
View raw message