Posted In: JDBC

How to configure MySQL DataSource in Tomcat 9

1. MySQL datasource setup

1. Download MySQL JDBC driver
Location to download MYSQL JDBC CONNECTOR (mysql-connector-java-5.1.40.zip->mysql-connector-java-5.1.40-bin.jar)
Copy connector jar to either TOMCAT/LIB or application’s WEB-INF/lib
Note
In an enterprise application avoid adding it in application’s WEB-INF/lib. Mostly this connector will be configured at server level so that same version will be shared among multiple applications. It helps in upgrading jar at server without touching the application.

or use maven entry in pom.xml

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.40</version>
</dependency>

2. Change context.xml to create MySQL DB datasource in TOMCAT
a. Add Server to Eclipse. Open /Servers/Tomcat v9.0 Server at XXXXXX/context.xml
b. or if you are using direct tomcat without eclipse then change /tomcat_install_dir/context.xml
c. or create META-INF/context.xml in your application

Then add ‘Resource’ to context.xml

Tomcat is installed as same server as MySQL DB
Tomcat 8 and 9 uses org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory which uses maxTotal, maxWaitMillis

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxTotal="50" maxIdle="30" maxWaitMillis="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/javausecasedb"/>

Tomcat is not installed as same server as MySQL DB

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxTotal="50" maxIdle="30" maxWaitMillis="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://DB_SERVER:3306/javausecasedb"/>

Note
maxTotal – Maximum number of database connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to -1 for no limit.
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 total connections.

maxIdle: Maximum number of idle database connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this nd the minEvictableIdleTimeMillis configuration parameter.
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 idle connections.

maxWaitMillis: Maximum time to wait for a database 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.

To use org.apache.tomcat.jdbc.pool

<Resource name="jdbc/javausecasedb" 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/javausecasedb" />

Tomcat 7 and below uses org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory which uses maxActive, maxWait

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxActive="50" maxIdle="30" maxWait="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/javausecasedb"/>

Note
maxActive – (int) The maximum number of active connections that can be allocated from this pool at the same time. The default value is 100
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 active connections.

maxIdle – (int) The maximum number of connections that should be kept in the pool at all times. Default value is maxActive:100 Idle connections are checked periodically (if enabled) and connections that been idle for longer than minEvictableIdleTimeMillis will be released. (also see testWhileIdle)
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 idle connections.

initialSize – (int)The initial number of connections that are created when the pool is started. Default value is 10
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 initial connections.

3. Change web.xml, This is optional step. Rather if you are going to deploy it to Websphere in Production environment do not add this entry.

<resource-ref>
<description>MySQL Datasource example</description>
<res-ref-name>jdbc/javausecasedb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

4. You can even create MySQL DB datasource through spring DBCP configuration. This is useful for testing application without any server.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
destroy-method="close">
   <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
   <property name="url" value="jdbc:mysql://DB_SERVER_IP:3306/javausecasedb"/>
   <property name="username" value="javausecasedbuser"/>
   <property name="password" value="javausecasedbpassword"/>
</bean>

Create MySQL DB datasource through spring DBCP2
Apache Commons DBCP 2.1.1 for JDBC 4.1 (Java 7.0+)

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" 
destroy-method="close">
   <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
   <property name="url" value="jdbc:mysql://DB_SERVER_IP:3306/javausecasedb"/>
   <property name="username" value="javausecasedbuser"/>
   <property name="password" value="javausecasedbpassword"/>
</bean>

Create MySQL DB datasource through java code

BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName("com.mysql.jdbc.Driver");
bds.setUrl("jdbc:mysql://localhost:3306/javausecasedb");
bds.setUsername("javausecasedbuser");
bds.setPassword("javausecasedbpassword");
bds.setInitialSize(5);

2. Use it in your application

1. Once datasource is configured on tomcat access it through Spring configuration

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/javausecasedb" />

2. To access MySQL DB through initial context

import javax.naming.Context;
import javax.naming.InitialContext;
..
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/javausecasedb");

Notice java:comp/env
The InitialContext is configured as a web application is initially deployed, and is made available to web application components (for read-only access). All configured entries and resources are placed in the java:comp/env portion of the JNDI namespace.

3. To access MySQL DB through @Resource (javax.annotation.Resource)

package com.javausecase;

import java.io.IOException;
import java.sql.Connection;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet("/Servlet2")
public class Servlet2 extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Resource(name = "jdbc/javausecasedb")
	private DataSource ds;

	public Servlet2() {
		super();
		System.out.println("Servlet2()");
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		Connection conn = null;
		try {
			// check datasource
			System.out.println("ds=" + ds);
			conn = ds.getConnection();
			System.out.println("ds.getConnection()=" + conn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

3. Common issues

a. Incorrect parameters warning
Jan 22, 2017 12:14:14 PM org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory getObjectInstance
WARNING: Name = javausecasedb Property maxActive is not used in DBCP2, use maxTotal instead. maxTotal default value is 8. You have set value of “50” for “maxActive” property, which is being ignored.
Jan 22, 2017 12:14:14 PM org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory getObjectInstance
WARNING: Name = javausecasedb Property maxWait is not used in DBCP2 , use maxWaitMillis instead. maxWaitMillis default value is -1. You have set value of “10000” for “maxWait” property, which is being ignored.

Solution – Check tomcat version and dbcp version

b. com.mysql.jdbc.Driver Class not found

Solution – Check connector jar is in classpath

c. invalid authorization specification

Solution – Check use id / password in datasource configuration

Reference
1. JNDI Datasource HOW-TO

Tags: , , , , , ,

by , on January 21st, 2017

  • Categories