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.

db.properties

# 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.

ComLineDSEx.java

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.

context.xml

<?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.

web.xml

<?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.

index.jsp

<%@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.

showVersion.jsp

<%@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.

Version.java

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.

DBVersionService.java

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.

ServiceLocator.java

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.

Ebooks

Data source in Java

JDBC

MySQL

Command line application

A web application in Tomcat