Main Tutorials

How to configure MySQL DataSource in Tomcat 6

Here’s a guide to show you how to configure MySQL datasource in Tomcat 6.

1. Get MySQL JDBC Driver

Get JDBC driver here – http://www.mysql.com/products/connector/ , for example, mysql-connector-java-5.1.9.jar, and copy it to $TOMCAT\lib folder.

2. Create META-INF/context.xml

Add a file META-INF/context.xml into the root of your web application folder, which defines database connection detail :

File : META-INF/context.xml


<Context>

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

</Context>

3. web.xml configuration

In web.xml, defines your MySQL datasource again :


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

See a full web.xml example below :

File : web.xml


<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns="http://java.sun.com/xml/ns/javaee" 
	xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 
	id="WebApp_ID" version="2.5">
 
  <display-name>MySQL DataSource Example</display-name>

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

4. Run It

Resource injection (@Resource) is the easiest way to get the datasource from Tomcat, see below :


import javax.annotation.Resource;
public class CustomerBean{

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

	public List<Customer> getCustomerList() throws SQLException{
		
	  //get database connection
	  Connection con = ds.getConnection();
	  //...

Alternatively, you can also get the datasource via context lookup service :


import javax.naming.Context;
import javax.naming.InitialContext;
public class CustomerBean{

	private DataSource ds;

	public CustomerBean(){
	  try {
		Context ctx = new InitialContext();
		ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mkyongdb");
	  } catch (NamingException e) {
		e.printStackTrace();
	  }
	}
	
	public List<Customer> getCustomerList() throws SQLException{
		
	  //get database connection
	  Connection con = ds.getConnection();
	  //...

Reference

  1. Tomcat 6 : JNDI Datasource HOW-TO

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
21 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
AnhVu
3 years ago

Hi,
followed your instruction
ConnPool Success..
ds: org.apache.tomcat.dbcp.dbcp.BasicDataSource@7a256bb4
then got
Cannot create JDBC driver of class ” for connect URL ‘null’.

Neoheurist
8 years ago

Within the web.xml file, is it possible to get rid of the element:

javax.sql.DataSource

the reason that I ask is because the same class (javax.sql.DataSource) is specified in the context.xml file:

Since the web.xml element is associated to the context.xml attribute the repeated declaration of the class javax.sql.DataSource seems redundant because:

jdbc/mkyongdb must equal

If it cannot be removed what is the function of the javax.sql.DataSource element in the web.xml (that couldn’t be derived from context.xml)?

Thanks

Koray Tugay
7 years ago
Reply to  Neoheurist

Yes, you are right. It is not required.

moditejasd
9 years ago

Hello Everyone,
I am asking my AD (Application Development) team do same setup for TomEE1.7.1 but we are failing to lookup Datasource. Does anyone have anidea if this setup works with TomEE? Or have encountered this issue and have fixed it with placing resource configuration inside war file.

Coty Condry
9 years ago

Thank you for another of your dead-simple example with all the context needed to get this working when starting from scratch.

swathi
9 years ago

Hi Mr. mkyong your posts helped me many times. i am very new to windows server2012 and mysql .can you plz help me how to download , install and configure mysql database in in windows server 2012. i want to deploy my project in windows server. i would be very thankful is you solve my problem

gevix
9 years ago

Mr. mkyong, thanks for your work. Your posts helped me many times.

I have a question: how and where obtain the logs for @Resource injections?

When any problem occurs with the injection I only obtain a null reference instead of an Exception or log.

malcolm
9 years ago

Thankyou. The business in web.xml got rid of a weird message in my logs which said that my jndi ref did not exist. – Even though in reality everything worked!
I’ve had this silly message in my logs for over a year and could not work out how to get rid of it. Thanks again.

Pavan Ravikanth
9 years ago

Can i know what must be the URL, if i’m trying to access the object remotely? In this case it is ‘java:comp/env/jdbc/mkyongdb’ what must be in remote access?

Panu
9 years ago

Thanks for the good article! For me @Resource worked fine with Tomcat 6 but when I upgraded to Tomcat 7 I started getting NullPointerException and had to switch to context lookup service. If you/someone find a solution to this please let us know.

Vladimir Bourdine
10 years ago

The annotation variant works in servlets with context, otherwise context has to be collected manualy (second way).
Also you have to be aware, that the context.xml in Catalina/conf/your_host/your_app.xml
will be referenced instead of your META-INF/context.xml if the first one exists.

Shaon
10 years ago

Confused.. 🙁
why do i need to declare the same thing in two places; context.xml & web.xml ?
isn’t context.xml for Tomcat configuration & web.xml for application configuration?

John
10 years ago
Reply to  Shaon

Tomcat creates and manages the connection pool using your context.xml and your web.xml simply tells your application how to reference it.

venkat teki
10 years ago
Reply to  John

I added the code in only in context.xml and not in web.xml. But from my servlet, i am able to get the Datasource using @Resource annotation. So, adding the code in web.xml is not necessary..right?

ruly
10 years ago

Hi, I try using Resource injection but getConnection always return null. but when I get the datasource via context lookup service there is no problem. Can you tell me why I get null when using Resource Injection?

Luis
11 years ago

hi if I’m using GlashFish instead of Tomcat are the same steps for this? or I need to change some stuff
greetings

Dickens
11 years ago

we are using JBoss AS 6 but every time, I can not deploy the application.

Saravanan K
11 years ago

Hi Mr.Yong,

The above example works fine when I use eclipse configured with tomcat, but when I deploy the project in server, @Resource is not working, what do I miss, please help!

Thanks for your help in advance.

Irfan Griffin
11 years ago

I was tripping up on how to implement this (every time I tried, I couldn’t run .getConnection). Then I figured out what my problem was, I need to import this:

import javax.sql.DataSource;

Previously I was importing something else — the wrong thing. So if this source code doesn’t work, it’s because you’re probably using the wrong import (like me).

Also, great post; it would be helpful if you could show an example of a prepared statement etc, but not necessary…

Test
11 years ago

public static String checkIsNull(String Value) {
String returnValue = “”;

if (Value != null && !(Value.trim().equalsIgnoreCase(“”))) {
returnValue = Value;
}
return returnValue;
}

public static String checkIsNullForNumeric(String Value) {
String returnValue = “0”;

if (Value != null && !(Value.trim().equalsIgnoreCase(“”))) {
returnValue = Value;
}
return returnValue;
}

public static String checkIsNullReplaceWithBlankSpace(String Value) {
String returnValue = ” “;

if (Value != null && !(Value.trim().equalsIgnoreCase(“”))) {
returnValue = Value;
}
return returnValue;
}

public static String checkIsNullOrNotForString(String Value) {
String returnValue = “”;

if (!Value.trim().equalsIgnoreCase(“Null”)) {
returnValue = Value;
}
return returnValue;
}

public static String checkIsNullOrNotForNumeric(String Value) {
String returnValue = “0”;

if (!Value.trim().equalsIgnoreCase(“Null”) && !Value.trim().equalsIgnoreCase(“”)) {
returnValue = Value;
}
return returnValue;
}

public static String checkIsNullOrZeroForNumericReplaceWithBlankSpace(String Value) {
String returnValue = “”;

if (Value != null && !(Value.trim().equalsIgnoreCase(“”)) && !(Value.trim().equalsIgnoreCase(“0”))) {
returnValue = Value;
}
return returnValue;
}

Test
11 years ago

package DBConnector;

import CommonPackage.PrintLogEntryClass;
import javax.naming.Context;
import javax.naming.InitialContext;

import java.sql.Connection;
import javax.sql.DataSource;

/**
*
* @author user
*/

public class TestDBConnection {
private static DataSource ds = null;

public static Connection getDBConnection() {
Connection con = null;
try {
if (ds == null) {
java.sql.Timestamp currentDateTime = new java.sql.Timestamp(new java.util.Date().getTime());
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup(“java:/comp/env”);
ds = (DataSource) envContext.lookup(“Test”);
System.out.println(“Test :: Test DB Connection Pool Opened On ” + currentDateTime);
}
con = ds.getConnection();
} catch (Exception e) {
PrintLogEntryClass.PrintExceptionLogEntry(“TestDBConnection”, “getDBConnection”, e, ” ” + ‘_’ + ” “);
e.printStackTrace();
}
return con;
}
}