ZetCode

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:

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.

Project structure
Figure: Project structure

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.

Project libraries
Figure: Project libraries

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.