Where Do I Have to Place the Jdbc Driver for Tomcat's Connection Pool

Where do I have to place the JDBC driver for Tomcat's connection pool?

The JDBC driver has to be visible to the same classloader as the data source factory itself. The data source factory library is placed in Tomcat's own /lib folder and thus loaded by Tomcat's "common" classloader.

Your problem sounds much like that you dropped the JDBC driver in webapp's /WEB-INF/lib. The webapp's /WEB-INF/lib is invisible to the "common" classloader. So technically, you have to place the JDBC driver in Tomcat's own /lib folder (or, at least, in a configurable path as specified by common.loader setting in /conf/catalina.properties) in order to make it visible to the data source factory.

Or, as you attempted, copying the data source factory into /WEB-INF/lib will also fix it. The webapp's /WEB-INF/lib has namely higher precedence in classloading than Tomcat's /lib folder. So if the data source factory is found in /WEB-INF/lib, it will be loaded from there. As the JDBC driver is also there, it will be seen. This is however not the right solution to your concrete problem, this is more a workaround, so you shouldn't do this.

There isn't exactly documentation which is specifically targeted to this issue. The Tomcat Class Loader HOW-TO will however help in understanding the class loading hierarchy in Tomcat.

screen shot showing Tomcat folder with nested "lib" folder with nested JDBC driver .jar file

See also:

  • The infamous java.sql.SQLException: No suitable driver found
  • How should I connect to JDBC database / datasource in a servlet based application?
  • Is it safe to use a static java.sql.Connection instance in a multithreaded system?

Where should I place the JDBC driver for tomcat pool under openshift using scalable apps?

The action hook solved the problem!
First I created an action hook called "pre_start_jbossews" at the action_hooks folder, with the chmod to execute. The name MUST be in the form pre_start_(cartrigde).
They are described here:
http://openshift.github.io/documentation/oo_user_guide.html#action-hooks

inside the hook I put this command:

wget -P $OPENSHIFT_DATA_DIR/driver "http://central.maven.org/maven2/org/postgresql/postgresql/9.3-1100-jdbc41/postgresql-9.3-1100-jdbc41.jar"

and then I edited my catalina.properties, appending the driver folder "${catalina.home}/../app-root/data/driver/*.jar"

common.loader=${catalina.base}/lib,${catalina.base}/lib/*.jar,${catalina.home}/lib,${catalina.home}/lib/*.jar,${catalina.home}/../app-root/data/driver/*.jar

It was not obvious, but every time a new gear is created, the start script of jbossews is ALSO executed at THAT GEAR. In fact, a gear boots as an individual machine, the entire OS is loaded.
So, if I could not copy the lib from the root gear, I still can wget it from maven.
However, I think it would be more easier to have a common "data" folder copied to all gears at start (and only at start, to avoiding breaking gear isolation idea).

Tomcat connection pooling, install jdbc driver for web-app

If you don't have control over the server, then you're lost. Just create the connection pool yourself instead of letting the container do it.

I suggest to use c3p0 for this (which is far better than Tomcat's builtin DBCP since it's locked to a single thread). Put the c3p0 libraries in the /WEB-INF/lib and create it as per its documentation:

ComboPooledDataSource dataSource = new ComboPooledDataSource(); 
dataSource.setDriverClass("org.postgresql.Driver");
dataSource.setJdbcUrl("jdbc:postgresql://localhost/testdb");
dataSource.setUser("dbuser");
dataSource.setPassword("dbpassword");
// ...

Connection connection = null;
// ...
try {
connection = dataSource.getConnection();
// ...
} finally {
// ...
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {} // Always close resources in finally!
}

Setting up connection pool with Tomcat 7 (MySQL)

The problem was in Java code. I was looking for the DataSource from the wrong context (the root context). This is wrong:

Context ctx = new InitialContext();
ds = (DataSource) ctx.lookup("jdbc/maakler_new");

Correct way would be this:

Context ctx = new InitialContext();
ds = (DataSource) ctx.lookup("java:comp/env/jdbc/maakler_new");

How should I connect to JDBC database / datasource in a servlet based application?

A common practice is to configure this as a DataSource in the servlet container in question. It will provide you connection pooling facilities which will greatly improve performance. Also a common practice is to externalize the raw settings in some configuration file which is been placed in the classpath.

In case you're using Tomcat as servletcontainer, you need to configure the datasource as per its JNDI documentation. You'll see that there are several ways. Easiest way is to create a /META-INF/context.xml in the webcontent of your dynamic web project (to be clear, the /META-INF is at the same level as the /WEB-INF of the webapp) and fill it with something like:

<?xml version="1.0" encoding="UTF-8"?>

<Context>
<Resource
name="jdbc/db" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
url="jdbc:mysql://hostname.com/db"
driverClassName="com.mysql.jdbc.Driver"
username="user" password="pass"
/>
</Context>

This roughly means that Tomcat server should create a datasource with the JNDI name jdbc/db with a maximum of 100 active connections, a maximum of 30 idle connections and a maximum wait time of 10000 milliseconds before a connection should be returned from your application (actually: closed by your application, so your application has 10 seconds time between acquiring the connection and closing the connection). The remnant of the settings should be familiar and self-explaining enough to you; those are the JDBC settings.

Finally in your web project, edit the file /WEB-INF/web.xml to add the following entry:

<resource-env-ref>
<resource-env-ref-name>jdbc/db</resource-env-ref-name>
<resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
</resource-env-ref>

This roughly means that the webapplication should use the server-provided datasource with the name jdbc/db.

Then change your connection manager to something like this:

private DataSource dataSource;

public Database(String jndiname) {
try {
dataSource = (DataSource) new InitialContext().lookup("java:comp/env/" + jndiname);
} catch (NamingException e) {
// Handle error that it's not configured in JNDI.
throw new IllegalStateException(jndiname + " is missing in JNDI!", e);
}
}

public Connection getConnection() {
return dataSource.getConnection();
}

..and replace all Class.forName(driver) calls by new Database("jdbc/db") and replace all DriverManager.getConnection() calls by database.getConnection(). You can if necessary obtain the value jdbc/db from some config file (Properties file?).

Alternatively, inject the DataSource via the @Resource annotation inside a container managed artifact, such as a @WebServlet servlet class:

@Resource(name="jdbc/db")
private DataSource dataSource;

That should be it. Just deploy your webapplication with the above changes and run it. Don't forget to place the database JDBC driver in the Tomcat/lib or to add its path to the shared.loader property of Tomcat/conf/catalina.properties, because the responsibility of loading the JDBC driver is now moved from the webapplication to the server. For more hints and other basic JDBC/JNDI examples you may find this article useful as well.

See also:

  • How to install JDBC driver in Eclipse web project without facing java.lang.ClassNotFoundexception
  • Where do I have to place the JDBC driver for Tomcat's connection pool?
  • Is it safe to use a static java.sql.Connection instance in a multithreaded system?
  • Show JDBC ResultSet in HTML in JSP page using MVC and DAO pattern
  • How to retrieve and display images from a database in a JSP page?

Tomcat does not find JDBC Driver after importing via Maven

Try in url for localhost port 3306 not 80.

And if you get an error whit time zone, use this string :



String url = "jdbc:mysql://127.0.0.1:3306/DemoJSP";
String timezone = "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
String username = "root";
String password = "";
Connection connection = DriverManager.getConnection(url + timezone, username, password);

Tomcat and JDBC connection pooling

Looks like you are missing Context envCtx = (Context) initCtx.lookup("java:comp/env");
JNDI lookup should be done like this:

// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");

// Look up our data source
DataSource ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB");

// Allocate and use a connection from the pool
Connection conn = ds.getConnection();

documentation from http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html.

Tomcat DataSource - Connection Pooling

The default connection timeout 60sec.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class SimplePOJOExample {

public static void main(String[] args) throws Exception {
PoolProperties p = new PoolProperties();
p.setUrl("jdbc:mysql://localhost:3306/mysql");
p.setDriverClassName("com.mysql.jdbc.Driver");
p.setUsername("root");
p.setPassword("password");
p.setJmxEnabled(true);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1");
p.setTestOnReturn(false);
p.setValidationInterval(30000);
p.setTimeBetweenEvictionRunsMillis(30000);
p.setMaxActive(100);
p.setInitialSize(10);
p.setMaxWait(10000);
p.setRemoveAbandonedTimeout(60);
p.setMinEvictableIdleTimeMillis(30000);
p.setMinIdle(10);
p.setLogAbandoned(true);
p.setRemoveAbandoned(true);
p.setJdbcInterceptors(
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
DataSource datasource = new DataSource();
datasource.setPoolProperties(p);

Connection con = null;
try {
con = datasource.getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from user");
int cnt = 1;
while (rs.next()) {
System.out.println((cnt++)+". Host:" +rs.getString("Host")+
" User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
}
rs.close();
st.close();
} finally {
if (con!=null) try {con.close();}catch (Exception ignore) {}
}
}

}

And here is an example on how to configure a resource for JNDI lookups

<Resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="false"
validationQuery="SELECT 1"
validationInterval="30000"
timeBetweenEvictionRunsMillis="30000"
maxActive="100"
minIdle="10"
maxWait="10000"
initialSize="10"
removeAbandonedTimeout="60"
removeAbandoned="true"
logAbandoned="true"
minEvictableIdleTimeMillis="30000"
jmxEnabled="true"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
username="root"
password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mysql"/>

If you find any more difficulty refer this
https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html



Related Topics



Leave a reply



Submit