Hi,
i'm using tomcat 5.5.9 for a web server application where many clients connect
and do stuffs.
each time they login to the server, for example, a database query is performed.
Application works, but reading
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#Common%20Problems
some doubt come to my mind. I just want to be sure that i'm using connection
pooling correctly.
In that article there is written:
Here is an example of properly written code to use a db connection obtained
from a connection pool:
Connection conn = null;
Statement stmt = null; // Or PreparedStatement if needed
ResultSet rs = null;
try {
conn = ... get connection from connection pool ...
...
But what's the best way to "get connection from connection pool"?
Here is what i've done:
My context.xml file:
<Context debug="5" reloadable="true" crossContext="true">
<Resource
name="jdbc/someDB"
auth="Container"
type="javax.sql.DataSource"
maxActive="300"
maxIdle="30"
maxWait="15000"
username="someUser"
password="somePass"
driverClassName="com.mysql.jdbc.Driver"
validationQuery="SELECT 1"
url="jdbc:mysql://1.1.1.1:3306/somePath"/>
</Context>
My web.xml file:
<web-app xmlns="http://java.sun.com/xml/ns/j2ee";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"; version="2.4">
<display-name>Some name</display-name>
<listener>
<listener-class>somePackage.ApplicationWatch</listener-class>
</listener>
<servlet>
<servlet-name>htmlcontent</servlet-name>
<servlet-class>somePackage.HtmlContentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>htmlcontent</servlet-name>
<url-pattern>/htmlcontent.view</url-pattern>
</servlet-mapping>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/someDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
Then i have Database.java class where i have the method:
private Connection getConnection() throws Exception {
// get context: provides the starting point for resolution of
names
Context ctx = new InitialContext();
if (ctx == null) {
throw new Exception("No Context");
}
// retrieve datasource
DataSource ds = (DataSource)
ctx.lookup("java:comp/env/jdbc/someDB");
if (ds == null) {
throw new Exception("No Datasource");
}
// return db connection
return ds.getConnection();
}
that get connection for all of the 20-30 methods in Database.java that do a
query each. An example of method in Database.java that use it is:
// get user info
public UserData getUserData(UserData res, String un, String pw) {
Connection con = null;
Statement stmt = null;
ResultSet rst = null;
try {
// get connection
con = getConnection();
if (con == null) {
throw new Exception("No Connection");
}
stmt = con.createStatement();
// perform query
rst = stmt.executeQuery("some sql syntax");
if (rst.next()) {
.....
}
else {
throw new Exception("No results");
}
}
catch (Exception e1) {
return null;
}
// close resources
finally {
try {
rst.close();
rst = null;
}
catch (Exception e2) {
}
try {
stmt.close();
stmt = null;
}
catch (Exception e3) {
}
try {
con.close();
con = null;
}
catch (Exception e4) {
}
}
return res;
}
At the moment, methods are not static, so each time a client thread need to
login, for example, i do (new Database()).getUserData(res, un, pw).
First question is if that solution is correct or create each time a new
Database class is useless/stupid. If it's stupid, should i make the methods
static? or should i create a static database object each time server is started?
Second, is that getConnection() method a correct way for what the article
(http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#Common%20Problems)
meant about "get connection from connection pool"?
Thanks for any suggestion.
---------------------------------------------------------------------
To start a new topic, e-mail: [email protected]
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
