Data source in Java
last modified July 6, 2020
In this tutorial, we learn how to set up a data source in Java. We use MySQL database system. ZetCode has a complete e-book for MySQL Java: MySQL Java programming e-book.
We use the MySQL Connector/J driver. It is the official JDBC driver for MySQL.
There are two basic ways of creating a connection to a database in Java: a) with a driver manager, b) using a data source. The data source has several advantages over a driver manager:
- it supports distributed transactions
- it provides a connection pooling technique
- it can be managed by a server, i.e. outside an application
The driver manager hampers the application performance as the connections are created and closed in Java classes. A driver manager can be used in simple testing applications; for complex application a data source is always recommended. Refer to MySQL Java tutorial to see how to use a driver manager in a Java application.
An object that implements the data source interface will typically be registered with a naming service based on the Java Naming and Directory Interface (JNDI) API.
JDBC
JDBC is an API for the Java programming language that defines how a
client may access a database. It provides methods for querying and updating
data in a database. JDBC is oriented towards relational databases.
From a technical point of view, the API is as a set of classes in the java.sql
package. To use JDBC with a particular database, we need a JDBC driver for that database.
MySQL
MySQL is a leading open source database management system. It is a multi-user, multi-threaded database management system. MySQL is especially popular on the web. MySQL comes in two versions: MySQL server system and MySQL embedded system.
mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.02 sec)
We create a new testdb
database. We only need a database object in this
tutorial; we will not work with tables. We will use a SELECT VERSION()
statement to get the version of MySQL database.
Command line application
In this example, we connect to the database with a command line Java application.

This is how the project structure looks like in NetBeans.
The MysqlDataSource
is a class for creating datasources.
# mysql properties mysql.driver=com.mysql.jdbc.Driver mysql.url=jdbc:mysql://localhost:3306/testdb mysql.username=testuser mysql.password=test623
These are the properties for the MySQL database. The db.properties
file
is located in the src/resources
subdirectory in this project.
package com.zetcode; import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.PreparedStatement; import java.util.Properties; public class ComLineDSEx { public static MysqlDataSource getMySQLDataSource() throws FileNotFoundException, IOException { Properties props = new Properties(); FileInputStream fis = null; MysqlDataSource ds = null; fis = new FileInputStream("src/resources/db.properties"); props.load(fis); ds = new MysqlConnectionPoolDataSource(); ds.setURL(props.getProperty("mysql.url")); ds.setUser(props.getProperty("mysql.username")); ds.setPassword(props.getProperty("mysql.password")); return ds; } public static void main(String[] args) throws IOException, SQLException { Connection con = null; PreparedStatement pst = null; ResultSet rs = null; MysqlDataSource ds = getMySQLDataSource(); try { con = ds.getConnection(); pst = con.prepareStatement("SELECT VERSION()"); rs = pst.executeQuery(); if (rs.next()) { String version = rs.getString(1); System.out.println(version); } } finally { if (rs != null) { rs.close(); } if (pst != null) { pst.close(); } if (con != null) { con.close(); } } } }
In this example, we connect to the database using a datasource and get the version of MySQL.
fis = new FileInputStream("src/main/Resources/db.properties"); props.load(fis);
The database properties are read from the db.properties
file
with the FileInputStream
class.
ds = new MysqlConnectionPoolDataSource(); ds.setURL(props.getProperty("mysql.url")); ds.setUser(props.getProperty("mysql.username")); ds.setPassword(props.getProperty("mysql.password"));
A MysqlConnectionPoolDataSource
is created and
the datasource properties are set.
con = ds.getConnection();
A connection object is created from the datasource with the getConnection()
method.
pst = con.prepareStatement("SELECT VERSION()");
An SQL statement is created. The SELECT VERSION()
command returns the version of MySQL.
rs = pst.executeQuery();
The query is executed. It returns a result set.
if (rs.next()) { String version = rs.getString(1); System.out.println(version); }
We get the first value from the result set and print it to the console.
} finally { if (rs != null) { rs.close(); } if (pst != null) { pst.close(); } if (con != null) { con.close(); } }
In the end, the resources are released.
A web application in Tomcat
We create a web application which will retrieve the version of MySQL. The application is deployed on Tomcat.

In our project, we use JSTL and MySQL driver JARs. The JavaServer Pages Standard Tag Library (JSTL) is a collection of useful JSP tags which provide core functionality common to many JSP files.
<?xml version="1.0" encoding="UTF-8"?> <Context path="/TomcatDSEx"> <Resource name="jdbc/testdb" auth="Container" type="javax.sql.DataSource" username="testuser" password="test623" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/testdb" maxActive="10" maxIdle="4"/> </Context>
For Tomcat web server, we create a new resource in the context.xml
file. The file is located in the META-INF
directory.
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" version="3.1"> <resource-ref> <description>DB Connection</description> <res-ref-name>jdbc/testdb</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> </web-app>
Then, in the web.xml
file, we create a reference to the resource.
In our application, we will refer to the data source with the jdbc/testdb
logical
name.
<%@page contentType="text/html" pageEncoding="UTF-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>JSP Page</title> </head> <body> <c:redirect url="/Version"/> </body> </html>
The index.jsp
file redirects to the Version
servlet.
<%@page contentType="text/html" pageEncoding="UTF-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>MySQL version</title> </head> <body> MySQL version: <c:out value="${version}"/> </body> </html>
The showVersion.jsp
is a UI element to display the data
retrieved from the database.
MySQL version: <c:out value="${version}"/>
The JSTL's <c:out>
tag is used to output the
value of the response.
package com.zetcode.version; import com.zetcode.version.service.DBVersionService; import java.io.IOException; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @WebServlet(name = "Version", urlPatterns = {"/Version"}) public class Version extends HttpServlet { protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); String page = "/showVersion.jsp"; String version = DBVersionService.getMySQLVersion(); request.setAttribute("version", version); RequestDispatcher disp = getServletContext().getRequestDispatcher(page); disp.forward(request, response); } @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } @Override public String getServletInfo() { return "Returns version of MySQL"; } }
The Version
servlet calls a service method to get the version
of MySQL. The returned value is set as an attribute to the request object.
String page = "/showVersion.jsp";
At the end, the servlet points to the showVersion.jsp
file.
String version = DBVersionService.getMySQLVersion();
A service method is called to get the version of MySQL.
request.setAttribute("version", version);
The version value is set to the request object with the setAttribute()
method.
RequestDispatcher disp = getServletContext().getRequestDispatcher(page); disp.forward(request, response);
We dispatch to the showVersion.jsp
file.
package com.zetcode.version.service; import com.zetcode.version.Version; import com.zetcode.version.util.ServiceLocator; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; import javax.sql.DataSource; public class DBVersionService { public static String getMySQLVersion() { String version = "no version"; DataSource ds = ServiceLocator.getDataSource("java:comp/env/jdbc/testdb"); Connection con = null; try { con = ds.getConnection(); Statement stm = con.createStatement(); ResultSet rs = stm.executeQuery("SELECT VERSION()"); if (rs.next()) { version = rs.getString(1); } } catch (SQLException ex) { Logger.getLogger(Version.class.getName()).log(Level.SEVERE, null, ex); } finally { if (con != null) { try { con.close(); } catch (SQLException ex) { Logger.getLogger(DBVersionService.class.getName()).log(Level.SEVERE, null, ex); } } } return version; } }
DBVersionService
is a service class which contains a method
to get the version of MySQL.
DataSource ds = ServiceLocator.getDataSource("java:comp/env/jdbc/testdb");
The datasource is created with a ServiceLocator
class.
con = ds.getConnection(); Statement stm = con.createStatement(); ResultSet rs = stm.executeQuery("SELECT VERSION()"); if (rs.next()) { version = rs.getString(1); }
Here we have the JDBC code to connect to the database and execute an SQL statement.
package com.zetcode.version.util; import java.util.logging.Level; import java.util.logging.Logger; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; public class ServiceLocator { public static DataSource getDataSource(String jndiName) { Context ctx = null; DataSource ds = null; try { ctx = new InitialContext(); ds = (DataSource) ctx.lookup(jndiName); } catch (NamingException ex) { Logger.getLogger(ServiceLocator.class.getName()).log(Level.SEVERE, null, ex); } return ds; } }
The ServiceLocator
looks up the data source by its given JNDI name and
returns it to the caller.
$ curl localhost:8084/TomcatDSEx/Version <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>MySQL version</title> </head> <body> MySQL version: 5.5.49-0ubuntu0.14.04.1 </body> </html>
The application responds with a HTML page containing the version of MySQL.
This was the Data source in Java tutorial.
List all Java tutorials.