Java sql datasource example

Tech Tutorials

Tutorials and posts about Java, Spring, Hadoop and many more. Java code examples and interview questions. Spring code examples.

Wednesday, December 23, 2020

DataSource in Java-JDBC

In the examples given in the previous post Java JDBC Steps to Connect to DB we have seen how to get a connection using DriverManager class. That’s ok for sample code where you just need to test using a connection and close it. But in a real life application creating connection object every time DB interaction is needed will be very time consuming. What you need is a connection pool where a given number of connection objects are created in the beginning itself and are reused.

In this post we’ll see another way of connecting to DB from your Java application using a DataSource object which provides the connection pooling. There are other advantages of using DataSource in JDBC too.

Advantages of using DataSource

1. Layer of abstraction– In an enterprise application you can configure JDBC DataSource in the application server and register it with JNDI. That way user just need to know the bound logical name for the DataSource and the DB specific details are hidden.

By pooling the connection you can reuse the connection objects rather then creating it every time that improves performance for database-intensive applications because creating connection objects is costly both in terms of time and resources.

DataSource interface in Java

Both of these methods return a Connection object.

DataSource Implementations

A JDBC driver should include at least a basic DataSource implementation. For example MySQL DB JDBC driver includes the implementation com.mysql.jdbc.jdbc2.optional.MysqlDataSource and Oracle DB’s implementation is oracle.jdbc.pool.OracleDataSource.

JDBC DataSource Examples

Let’s see some examples of DataSource in use. First let us see one example with MySQL DataSource. Though people prefer to use one of the connection pooling library Apache DBCP or mchange c3p0 atleast with stand alone Java programs, we’ll see example of these too.

MySQL DataSource Example

Here we have a DataSource class that is a singleton class giving the instance of MysqlDataSource. Also the schema in MySQL is netjs and table is Employee. You should have the mysql-connector jar in your class path.

There is another class DSConnection where we get the instance of MysqlDataSource and use it to get the Connection object.

Then a PreparedStatement object is created with a Select SQL statement to get Employee data having the passed ID.

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; public class DataSource < private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; private static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/netjs"; private static final String DB_USER = "root"; private static final String DB_PWD = "admin"; private static DataSource ds; private MysqlDataSource mySqlDS = new MysqlDataSource(); //private constructor private DataSource()< //mySqlDS.setDriverClassName(DRIVER_CLASS); mySqlDS.setUrl(DB_CONNECTION_URL); mySqlDS.setUser(DB_USER); mySqlDS.setPassword(DB_PWD); >/** *static method for getting instance. */ public static DataSource getInstance() < if(ds == null)< ds = new DataSource(); >return ds; > public MysqlDataSource getMySqlDS() < return mySqlDS; >public void setMySqlDS(MysqlDataSource mySqlDS) < this.mySqlDS = mySqlDS; >>

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; public class DSConnection < public static void main(String[] args) < DSConnection dsCon = new DSConnection(); try < dsCon.displayEmployee(6); >catch (SQLException e) < // TODO Auto-generated catch block e.printStackTrace(); >> /** * @param connection * @param id * @throws SQLException */ private void displayEmployee(int id) throws SQLException < Connection connection = null; String selectSQL = "Select * from employee where PreparedStatement prepStmt = null; try < MysqlDataSource basicDS = DataSource.getInstance().getMySqlDS(); connection = basicDS.getConnection(); prepStmt = connection.prepareStatement(selectSQL); prepStmt.setInt(1, id); ResultSet rs = prepStmt.executeQuery(); while(rs.next())< System.out.println("id : " + rs.getInt("id") + " Name : " + rs.getString("name") + " Age : " + rs.getInt("age")); >>finally < if(prepStmt != null)< prepStmt.close(); >if(connection != null) < connection.close(); >> > >

DataSource example Using Apache DBCP

With stand alone Java programs where data source is needed it is more convenient to use connection pooling library like DBCP.

You need the following jars in your project’s classpath (Download path- https://commons.apache.org/proper/commons-dbcp/), check the versions as per your Java and DB versions.

commons-dbcp2-2.1.1.jar commons-pool2-2.5.0.jar commons-logging-1.2.jar

and the JDBC driver for the DB used. In this example MySQL is used so mysql-connector-java-5.1.39 jar is used.

Here we have a DataSource class that is a singleton class creating and returning the instance of dbcp2 BasicDataSource.

There is another class DSConnection where we get the instance of dbcp2 BasicDataSource and use it to get the Connection object.

Then a PreparedStatement object is created with a Select SQL statement to get Employee data having the passed ID.

DB schema in MySQL is netjs and table is Employee.

import org.apache.commons.dbcp2.BasicDataSource; public class DataSource < private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; private static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/netjs"; private static final String DB_USER = "root"; private static final String DB_PWD = "admin"; private static DataSource ds; private BasicDataSource basicDS = new BasicDataSource(); //private constructor private DataSource()< //BasicDataSource basicDS = new BasicDataSource(); basicDS.setDriverClassName(DRIVER_CLASS); basicDS.setUrl(DB_CONNECTION_URL); basicDS.setUsername(DB_USER); basicDS.setPassword(DB_PWD); // Parameters for connection pooling basicDS.setInitialSize(10); basicDS.setMaxTotal(10); >/** *static method for getting instance. */ public static DataSource getInstance() < if(ds == null)< ds = new DataSource(); >return ds; > public BasicDataSource getBasicDS() < return basicDS; >public void setBasicDS(BasicDataSource basicDS) < this.basicDS = basicDS; >>

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.commons.dbcp2.BasicDataSource; public class DSConnection < public static void main(String[] args) < DSConnection dsCon = new DSConnection(); try < dsCon.displayEmployee(6); >catch (SQLException e) < // TODO Auto-generated catch block e.printStackTrace(); >> /** * @param connection * @param id * @throws SQLException */ private void displayEmployee(int id) throws SQLException < Connection connection = null; String selectSQL = "Select * from employee where PreparedStatement prepStmt = null; try < BasicDataSource basicDS = DataSource.getInstance().getBasicDS(); connection = basicDS.getConnection(); prepStmt = connection.prepareStatement(selectSQL); prepStmt.setInt(1, id); ResultSet rs = prepStmt.executeQuery(); while(rs.next())< System.out.println("id : " + rs.getInt("id") + " Name : " + rs.getString("name") + " Age : " + rs.getInt("age")); >>finally < if(prepStmt != null)< prepStmt.close(); >if(connection != null) < connection.close(); >> > >

DataSource example Using c3p0

Another way of getting DataSource object is using c3p0 library. With stand alone Java program you can create an instance of ComboPooledDataSource.

c3p0-0.9.5.2.jar mchange-commons-java-0.2.11.jar

If we retain the same class structure as explained above. Now the classes would look like —

import java.beans.PropertyVetoException; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DataSource < private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; private static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/netjs"; private static final String DB_USER = "root"; private static final String DB_PWD = "admin"; private static DataSource ds; private ComboPooledDataSource cpds = new ComboPooledDataSource(); //private constructor private DataSource() throws PropertyVetoException< cpds.setDriverClass(DRIVER_CLASS); //loads the jdbc driver cpds.setJdbcUrl(DB_CONNECTION_URL); cpds.setUser(DB_USER); cpds.setPassword(DB_PWD); // the settings below are optional // c3p0 can work with defaults cpds.setMinPoolSize(5); cpds.setAcquireIncrement(5); cpds.setMaxPoolSize(20); >/** *Static method for getting instance. * @throws PropertyVetoException */ public static DataSource getInstance() throws PropertyVetoException < if(ds == null)< ds = new DataSource(); >return ds; > public ComboPooledDataSource getCpds() < return cpds; >public void setCpds(ComboPooledDataSource cpds) < this.cpds = cpds; >>

import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DSConnection < public static void main(String[] args) throws PropertyVetoException < DSConnection dsCon = new DSConnection(); try < dsCon.displayEmployee(6); >catch (SQLException e) < // TODO Auto-generated catch block e.printStackTrace(); >> /** * @param connection * @param id * @throws SQLException * @throws PropertyVetoException */ private void displayEmployee(int id) throws SQLException, PropertyVetoException < Connection connection = null; String selectSQL = "Select * from employee where PreparedStatement prepStmt = null; try < ComboPooledDataSource basicDS = DataSource.getInstance().getCpds(); connection = basicDS.getConnection(); prepStmt = connection.prepareStatement(selectSQL); prepStmt.setInt(1, id); ResultSet rs = prepStmt.executeQuery(); while(rs.next())< System.out.println("id : " + rs.getInt("id") + " Name : " + rs.getString("name") + " Age : " + rs.getInt("age")); >>finally < if(prepStmt != null)< prepStmt.close(); >if(connection != null) < connection.close(); >> > >

That’s all for this topic DataSource in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

Источник

Java Guides

In this article, we will show you how to create and use a DataSource object to get a connection to your data source without using JNDI services.

JAR dependencies

Download the MySQL JDBC driver from https://dev.mysql.com/downloads/connector/j/ and add to build path of your project.

Technologies used

JDBC DataSource + MySQL example

The following example demonstrates how to get a connection from MySQL DataSource. Also, we will create an employee table using Statement interface.

package com.javaguides.jdbc.statement.examples.packages; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; /** * JDBC MysqlDataSource Example * @author Ramesh Fadatare * */ public class DataSourceExample < private static final String createTableSQL = "create table employee (\r\n" + " id int(3) primary key,\r\n" + " name varchar(20),\r\n" + " email varchar(20),\r\n" + " country varchar(20),\r\n" + " password varchar(20)\r\n" + " );"; private static DataSource getMySQLDatasource() < MysqlDataSource dataSource = new MysqlDataSource(); // Set dataSource Properties dataSource.setServerName("localhost"); dataSource.setPortNumber(3306); dataSource.setDatabaseName("mysql_database"); dataSource.setUser("root"); dataSource.setPassword("root"); return dataSource; > public static void main(String[] argv) throws SQLException < DataSourceExample dataSourceExample = new DataSourceExample(); dataSourceExample.createTable(); > public void createTable() throws SQLException < System.out.println(createTableSQL); // Step 1: Establishing a Connection try (Connection connection = getMySQLDatasource().getConnection(); // Step 2:Create a statement using connection object Statement statement = connection.createStatement();) < // Step 3: Execute the query or update query statement.execute(createTableSQL); > catch (SQLException e) < // print SQL exception information printSQLException(e); > // Step 4: try-with-resource statement will auto close the connection. > public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < // e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

create table employee ( id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20) );

Источник