Question

In this tutorial, we learn how to set up a data source in Java with MySQL.

In this tutorial, 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.

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.

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. You might be also interested in JDBI tutorial , MyBatis tutorial , SQL query tag tutorial , or MySQL tutorial .