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 the Author

author image
mkyong
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

avatar
19 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
19 Comment authors
Koray TugayNeoheuristmoditejasdCoty Condryswathi Recent comment authors
newest oldest most voted
gevix
Guest
gevix

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.

Neoheurist
Guest
Neoheurist

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
Guest
Koray Tugay

Yes, you are right. It is not required.

moditejasd
Guest
moditejasd

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
Guest
Coty Condry

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

swathi
Guest
swathi

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

malcolm
Guest
malcolm

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
Guest
Pavan Ravikanth

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
Guest
Panu

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
Guest
Vladimir Bourdine

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
Guest
Shaon

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
Guest
John

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
Guest
venkat teki

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
Guest
ruly

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
Guest
Luis

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

Dickens
Guest
Dickens

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

Saravanan K
Guest
Saravanan K

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
Guest
Irfan Griffin

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
Guest
Test

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… Read more »

Test
Guest
Test

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; } }

trackback
JSF 2.0 + JDBC integration example

[…] Configure a MySQL datasource named “jdbc/mkyongdb“, follow this article – How to configure MySQL DataSource in Tomcat 6 […]

trackback
javax.naming.NameNotFoundException: Name jdbc is not bound in this Context

[…] datasource is not configure properly in Tomcat, see this guide for detail – How to configure MySQL DataSource in Tomcat 6 Oracle Magazine – […]