Many thanks for your help, I managed to set connection pooling up (however, I
don't know whether it uses conn. pooling or not...)
To help others, here are all the configurations that need to be done:
(This works with Tomcat 5.5, MySQL 5.0, Win XP SP2)
1) Place your database driver in your $CATALINA_HOME/common/lib directory along
with the all the required jars (commons-dbcp, commons-pool, commons-collection)
2) open your server.xml and put the following lines between
<GlobalNamingResources></GlobalNamingResources> tags (this is what I missed...)
(Modify it properly...)
<Resource name="jdbc/akr_db" auth="Container"
type="javax.sql.DataSource" removeAbandoned="true"
removeAbandonedTimeout="30" maxActive="100"
maxIdle="30" maxWait="10000" username="balazs"
password="12345"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/akr_db"/>
3) Create a context.xml file in your META-INF directory with the following
contents:
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<ResourceLink global="jdbc/akr_db" name="jdbc/akr_db"
type="javax.sql.DataSource"/>
</Context>
(Modify it properly...)
4) Create a Java class with the following contents to get a connection from the
pool:
public class DBTest {
String foo = "Not Connected";
String bar = "Empty";
public void init() {
Connection conn = null;
Statement stmt = null; // Or PreparedStatement if needed
ResultSet rst = null;
try{
Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
Context envCtx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/akr_db");
if (ds != null) {
conn = ds.getConnection();
if(conn != null) {
foo = "Got Connection "+conn.toString();
stmt = conn.createStatement();
rst =
stmt.executeQuery(
"select ID_paciens, vezeteknev, keresztnev from
torzs_paciens");
if(rst.next()) {
foo=rst.getString(2);
bar=rst.getString(3);
}
stmt.close();
stmt = null;
conn.close();
conn = null;
rst.close();
rst = null;
}
}
}catch(Exception e) {
e.printStackTrace();
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rst != null) {
try { rst.close(); } catch (SQLException e) { ; }
rst = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { ; }
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) { ; }
conn = null;
}
}
}
public String getFoo() { return foo; }
public String getBar() { return bar;}
}
5) Use the class from your JSP:
<%
db_helpers.DBTest tst = new db_helpers.DBTest();
tst.init();
%>
<h2>Results</h2>
Foo <%= tst.getFoo() %><br/>
Bar <%= tst.getBar() %>
--------------------------------------
----- Original Message ----
From: Nuwan Chandrasoma <[EMAIL PROTECTED]>
To: Struts Users Mailing List <[email protected]>
Sent: Tuesday, May 22, 2007 3:38:54 PM
Subject: Re: [OT] Connection Pooling
Hi,
Sorry mate, i think you should ask this from another group like tomcat..,
hope this link would give you some help
http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html
Thanks,
Nuwan
----- Original Message -----
From: "Balazs Michnay" <[EMAIL PROTECTED]>
To: "Struts Users Mailing List" <[email protected]>
Sent: Tuesday, May 22, 2007 7:34 AM
Subject: Re: [OT] Connection Pooling
>I forgot that I also have a resource-ref entry in my web.xml file... just
>in case it counts...
>
> <resource-ref>
> <description>DB Connection</description>
> <res-ref-name>jdbc/akr_db</res-ref-name>
> <res-type>javax.sql.DataSource</res-type>
> <res-auth>Container</res-auth>
> </resource-ref>
>
> But still nothing :(
>
> Thanks,
>
> BM
>
>
> ----- Original Message ----
> From: Nuwan Chandrasoma <[EMAIL PROTECTED]>
> To: Struts Users Mailing List <[email protected]>
> Sent: Tuesday, May 22, 2007 2:33:34 PM
> Subject: Re: [OT] Connection Pooling
>
> hi,
>
> i just had a look, i think your url is wrong.
>
> where is the DB name?
>
> eg:- you have it like jdbc:mysql://localhost:3306
>
> but should be like: jdbc:mysql://localhost:3306/akr_db
>
> Thanks,
>
> Nuwan
>
>
>
>
> ----- Original Message -----
> From: "Balazs Michnay" <[EMAIL PROTECTED]>
> To: "Struts Users Mailing List" <[email protected]>
> Sent: Tuesday, May 22, 2007 6:39 AM
> Subject: Re: [OT] Connection Pooling
>
>
>> Thanks for helping.
>> Sure, here are all my settings.
>> I use MySQL 5.0.37, Tomcat 5.5.17 and Windows XP SP2 and MySQL
>> Connector/J
>> 5.0.5.
>> The name of the database that I'd like to connect to is called "akr_db"
>> and the name of my web-application is called "SZTGKR".
>>
>> 1) I have a context.xml file in my web/Meta-INF directory with the
>> following contents:
>>
>> --------- CONTEXT.XML
>>
>> <?xml version="1.0" encoding="UTF-8"?>
>> <Context crossContext="true" debug="5" docBase="SZTGKR" path="/SZTGKR"
>> reloadable="true">
>> <Logger className="org.apache.catalina.logger.FileLogger"
>> prefix="localhost_akr_db_log." suffix=".txt" timestamp="true"/>
>> <Resource auth="Container" name="jdbc/akr_db"
>> type="javax.sql.DataSource"/>
>> <ResourceParams name="jdbc/akr_db">
>> <parameter>
>> <name>factory</name>
>> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
>> </parameter>
>> <parameter>
>> <name>removeAbandoned</name>
>> <value>true</value>
>> </parameter>
>> <parameter>
>> <name>removeAbandonedTimeout</name>
>> <value>60</value>
>> </parameter>
>> <parameter>
>> <name>logAbandoned</name>
>> <value>true</value>
>> </parameter>
>> <!-- Maximum number of dB connections in pool. Make sure you
>> configure your mysqld max_connections large enough to handle
>> all of your db connections. Set to 0 for no limit.
>> -->
>> <parameter>
>> <name>maxActive</name>
>> <value>100</value>
>> </parameter>
>> <!-- Maximum number of idle dB connections to retain in pool.
>> Set to 0 for no limit.
>> -->
>> <parameter>
>> <name>maxIdle</name>
>> <value>30</value>
>> </parameter>
>> <!-- Maximum time to wait for a dB connection to become available
>> in ms, in this example 10 seconds. An Exception is thrown if
>> this timeout is exceeded. Set to -1 to wait indefinitely.
>> -->
>> <parameter>
>> <name>maxWait</name>
>> <value>10000</value>
>> </parameter>
>> <!-- MySQL dB username and password for dB connections -->
>> <parameter>
>> <name>username</name>
>> <value>balazs</value>
>> </parameter>
>> <parameter>
>> <name>password</name>
>> <value>12345</value>
>> </parameter>
>> <!-- Class name for mm.mysql JDBC driver -->
>> <parameter>
>> <name>driverClassName</name>
>> <value>com.mysql.jdbc.Driver</value>
>> </parameter>
>> <!-- The JDBC connection url for connecting to your MySQL dB.
>> The autoReconnect=true argument to the url makes sure that the
>> mm.mysql JDBC Driver will automatically reconnect if mysqld
>> closed
>> the
>> connection. mysqld by default closes idle connections after 8
>> hours.
>> -->
>> <parameter>
>> <name>url</name>
>> <value>jdbc:mysql://localhost:3306</value>
>> </parameter>
>> </ResourceParams>
>> </Context>
>> -----------------------------END OF CONTEXT.XML
>>
>> I have a java class, that (presumably) creates (or returns...?) my
>> connection pool:
>>
>> ------------- DBTEST.JAVA
>>
>> package db_helpers;
>>
>> import javax.naming.*;
>> import javax.sql.*;
>> import java.sql.*;
>>
>> public class DBTest {
>>
>> String foo = "Not Connected";
>> String bar = "Empty";
>>
>> public void init() {
>> try{
>>
>> Context ctx = new InitialContext();
>> if(ctx == null )
>> throw new Exception("Boom - No Context");
>>
>> Context envCtx = (Context) ctx.lookup("java:comp/env");
>> DataSource ds = (DataSource) envCtx.lookup("jdbc/akr_db");
>>
>> if (ds != null) {
>> Connection conn = ds.getConnection();
>>
>> if(conn != null) {
>> foo = "Got Connection "+conn.toString();
>> Statement stmt = conn.createStatement();
>> ResultSet rst =
>> stmt.executeQuery(
>> "select ID_paciens, vezeteknev, keresztnev
>> from
>> torzs_paciens");
>> if(rst.next()) {
>> foo=rst.getString(2);
>> bar=rst.getString(3);
>> }
>> conn.close();
>> }
>> }
>> }catch(Exception e) {
>> e.printStackTrace();
>> }
>> }
>>
>> public String getFoo() { return foo; }
>> public String getBar() { return bar;}
>> }
>>
>> ----------------------------- END OF DBTEST.JAVA
>>
>> Here, my
>>
>> Connection conn = ds.getConnection();
>>
>> statement fails.
>>
>> And finally I have a JSP, in which I'd like to use my connection pool:
>>
>> ------------------------------ DBTEST.JSP
>> ...
>> <%
>> db_helpers.DBTest tst = new db_helpers.DBTest();
>> tst.init();
>> %>
>>
>> <h2>Results</h2>
>> Foo <%= tst.getFoo() %><br/>
>> Bar <%= tst.getBar() %>
>> ...
>> ------------------------------ END OF DBTEST.JSP
>>
>> Do I have anything else to connect to my db using connection pooling?
>> So again, it's the "Connection conn = ds.getConnection();", where my
>> program fails, it says
>> "org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC
>> driver
>> of class '' for connect URL 'null'"
>>
>> And yes, after the following statement, the url, driverclass and some
>> other properties of my ds is null:
>>
>> DataSource ds = (DataSource) envCtx.lookup("jdbc/akr_db");
>>
>> This might be the problem... why is that?
>>
>> Thanks for helping again!!
>>
>> Regards,
>>
>> BM
>>
>>
>>
>> ----- Original Message ----
>> From: Christopher Schultz <[EMAIL PROTECTED]>
>> To: Struts Users Mailing List <[email protected]>
>> Sent: Monday, May 21, 2007 2:33:11 PM
>> Subject: Re: [OT] Connection Pooling
>>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Balazs,
>>
>> Balazs Michnay wrote:
>>> I still cannot estabilish database connection using connection pooling.
>>> I think I made all the settings I need, but still nothing...
>>> 1) I have a <context> tag in my server.xml
>>
>> Can you show us the connection settings you are using? You only showed
>> the code (which looked fine, except that you don't need to check for
>> null after you create a new InitialContext... I'm pretty sure that an
>> object creation can't return null).
>>
>> - -chris
>>
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.7 (MingW32)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iD8DBQFGUZGG9CaO5/Lv0PARAm8OAJ0cXJTmHSXhX8prghRHixkEbU89KACeL71M
>> LYCgqlaLzn1mIzUZsGo9c8A=
>> =aJtP
>> -----END PGP SIGNATURE-----
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> ____________________________________________________________________________________Pinpoint
>> customers who are looking for what you sell.
>> http://searchmarketing.yahoo.com/
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
>
>
>
>
>
>
> ____________________________________________________________________________________You
>
> snooze, you lose. Get messages ASAP with AutoCheck
> in the all-new Yahoo! Mail Beta.
> http://advision.webevents.yahoo.com/mailbeta/newmail_html.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
____________________________________________________________________________________Got
a little couch potato?
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz
