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();
//...
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’.
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
Yes, you are right. It is not required.
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.
Thank you for another of your dead-simple example with all the context needed to get this working when starting from scratch.
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
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.
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.
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?
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.
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.
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?
Tomcat creates and manages the connection pool using your context.xml and your web.xml simply tells your application how to reference it.
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?
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?
hi if I’m using GlashFish instead of Tomcat are the same steps for this? or I need to change some stuff
greetings
we are using JBoss AS 6 but every time, I can not deploy the application.
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.
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:
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…
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;
}
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;
}
}