JDBC Data Source Example

This is continuation to my jdbc tutorial series. In jdbc tutorial series I explained how to connect to the database using jdbc. Just for your reference, below statements are used to connect to the database.

         /* Load the jdbc driver */

         Class.forName("com.mysql.jdbc.Driver");

        

        /* Open connection to database */

        System.out.println("Connecting to database");

        String url = "jdbc:mysql://localhost/sample";

        String userName = "root";

        String pasword = "tiger";

        Connection conn = DriverManager.getConnection(url, userName, pasword);

Why do we require a data source?

If you are going to connect to the database using DriverManager, you need to provide the username, password, url to connect etc., to connect to the database and you are responsible to open and closing the connections.

In case of data source, application server takes care of the details to connect to the database. You need to provide the JNDI name to connect to the database. Let use first see an example, how to connect to the database using data source and later I will explain how to configure the database properties in tomcat and connect to the database using JNDI.

I am going to use Apache Derby in client-server mode. If you would like to know, how to configure Apache Derby in client-server mode, I recommend you to go through my below tutorial series.

https://self-learning-java-tutorial.blogspot.com/2017/06/apache-derby-tutorial.html

Below snippet is used to create a data source element. Once you get the data source elements, you can get connection objects to that data source.

         public static DataSource getDataSource(String database, String userName, String password) throws SQLException {

                 ClientDataSource clientDataSource = new ClientDataSource();

                 // DatabaseName can include Derby URL Attributes

                 clientDataSource.setDatabaseName(database);

                 clientDataSource.setUser(userName);

                 clientDataSource.setPassword(password);

                 // The host on which Network Server is running

                 clientDataSource.setServerName("localhost");

                 // port on which Network Server is listening

                 clientDataSource.setPortNumber(1527);

                 return clientDataSource;

         }

        

Below snippet is used to create connection to the database 'organization'.

                 String database = "organization;create=true";

                 String userName = "derbyuser";

                 String password = "derbyuser";

                 DataSource dataSource = getDataSource(database, userName, password);

                 Connection conn = dataSource.getConnection();

Find the below working application.

Test.java

package com.sample;

/* Step 1: Import sql package */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.derby.jdbc.ClientDataSource;

public class Test {

 /**
  * Return the data source
  * 
  * @return
  * @throws SQLException
  */
 public static DataSource getDataSource(String database, String userName, String password) throws SQLException {
  ClientDataSource clientDataSource = new ClientDataSource();

  // DatabaseName can include Derby URL Attributes
  clientDataSource.setDatabaseName(database);

  clientDataSource.setUser(userName);

  clientDataSource.setPassword(password);

  // The host on which Network Server is running
  clientDataSource.setServerName("localhost");

  // port on which Network Server is listening
  clientDataSource.setPortNumber(1527);

  return clientDataSource;
 }

 public static void main(String args[]) throws SQLException, ClassNotFoundException {
  String database = "organization;create=true";
  String userName = "derbyuser";
  String password = "derbyuser";

  DataSource dataSource = getDataSource(database, userName, password);
  Connection conn = dataSource.getConnection();

  /* Create table employee */
  String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
  Statement stmt = conn.createStatement();
  stmt.execute(query);

  /* Insert data to employee table */
  query = "INSERT INTO employee values(1, 'Krishna')";
  stmt.execute(query);
  query = "INSERT INTO employee values(2, 'Arjun')";
  stmt.execute(query);

  query = "SELECT * FROM employee";
  ResultSet rs = stmt.executeQuery(query);

  while (rs.next()) {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   System.out.println(id + " " + name);
  }
  rs.close();
  conn.close();
 }
}

Output

1 Krishna
2 Arjun

One great advantage of DataSource over DriverManager is it supports connection pooling and distributed transaction support.

Can I register data source object using JNDI?
Yes, a DataSource object can be registered with a JNDI naming service. An application can use the JNDI API to access that DataSource object. Once you got the reference to the DataSource object, you can create connections to the database.

Previous                                                 Next                                                 Home