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
- 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!
Spring Boot DataSource Configuration
A DataSource is a factory for connections to a physical database. This tutorial will discuss what is a datasource and how to create and customize the DataSource bean in Spring boot applications.
The DataSource works as a factory for providing database connections. It is an alternative to the DriverManager facility. A datasource uses a URL along with username/password credentials to establish the database connection.
In Java, a datasource implements the javax.sql.DataSource interface. This datasource will typically be registered with the JNDI service and can be discovered using its JNDI name.
We may use a datasource to obtain the following:
- standard Connection object
- a connection that can be used in connection pooling
- a connection that can be used in distributed transactions and connection pooling
2. Configuring a DataSource
Spring boot allows defining datasource configuration in following ways:
During application startup, the DataSourceAutoConfiguration checks for DataSource.class (or EmbeddedDatabaseType.class) on the classpath and a few other things before configuring a DataSource bean for us.
If not already defined, include spring-boot-starter-data-jpa to the project that transitively brings all necessary dependencies including JDBC drivers for various databases e.g. mysql-connector-java for connecting to MySQL database.
org.springframework.boot spring-boot-starter-data-jpa If we plan to use an embedded database at some step (e.g., testing), we can import H2 DB separately.
2.2. Properties Configuration
DataSource configuration is provided by configuration properties entries ( spring.datasource.* ) in application.properties file. The properties configuration decouples the configuration from the application code. This way, we can import the datasource configurations from even external configuration provider systems.
Below given configuration shows sample properties for H2, MySQL, Oracle and SQL Server databases.
We often do not need to specify the driver-class-name , since Spring Boot can deduce it for the most databases from the connection url.
# H2 DB spring.datasource.url=jdbc:h2:file:C:/temp/test spring.datasource.username=sa spring.datasource.password= spring.datasource.driverClassName=org.h2.Driver spring.jpa.database-platform=org.hibernate.dialect.H2Dialect # MySQL spring.datasource.url=jdbc:mysql://localhost:3306/test spring.datasource.username=dbuser spring.datasource.password=dbpass spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect # Oracle spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl spring.datasource.username=dbuser spring.datasource.password=dbpass spring.datasource.driver-class-name=oracle.jdbc.OracleDriver spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect # SQL Server spring.datasource.url=jdbc:sqlserver://localhost;databaseName=springbootdb spring.datasource.username=dbuser spring.datasource.password=dbpass spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012DialectThe recommended way to create a DataSource bean is using DataSourceBuilder class within a class annotated with the @Configuration annotation.
Given is an example bean for H2 DB. Please configure other beans as necessary.
@Configuration public class JpaConfig < @Bean public DataSource dataSource() < DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(); dataSourceBuilder.driverClassName("org.h2.Driver"); dataSourceBuilder.url("jdbc:h2:file:C:/temp/test"); dataSourceBuilder.username("sa"); dataSourceBuilder.password(""); return dataSourceBuilder.build(); >>Suppose we deploy our Spring Boot application to an application server. In that case, we might want to configure and manage the DataSource using the Application Server’s built-in features and access it using JNDI.
We can do this using the spring.datasource.jndi-name property.
#JBoss defined datasource using JNDI spring.datasource.jndi-name = java:jboss/datasources/testDB3. Configuring Connection Pooling
3.1. HikariCP, Tomcat Pooling and Commons DBCP2
For a pooling datasource to be created, Spring boot verifies that a valid Driver class is available. If we set spring.datasource.driver-class-name property then the mentioned driver class must be found and loaded.
- The auto-configuration first tries to find and configure HikariCP. If HikariCP is available, it always chooses it.
- Otherwise, if the Tomcat Pooling is found, it is configured.
- If neither HikariCP nor the Tomcat Pooling datasource is available, then Commons DBCP2 is used if found on the classpath.
The spring-boot-starter-data-jpa starter automatically get a dependency to HikariCP .
3.2. Customizing Default Values
It is also possible to fine-tune implementation-specific settings by using their respective prefix ( spring.datasource.hikari.* , spring.datasource.tomcat.* , and spring.datasource.dbcp2.* ).
For example, we can use the below properties to customize a DBCP2 connection pool.
spring.datasource.dbcp2.initial-size = 50 spring.datasource.dbcp2.max-idle = 50 spring.datasource.dbcp2.default-query-timeout = 10000 spring.datasource.dbcp2.default-auto-commit = true4. Configuring Multiple DataSources with Spring Boot
To configure multiple data sources, create as many bean definitions as you want but mark one of the DataSource instances as @Primary.
Remember that if we create our own DataSource bean then auto-configuration backs off. In this case, we are responsible for providing configurations for all datasource beans.
@Configuration public class JpaConfig < @Bean(name = "h2DataSource") public DataSource h2DataSource() < DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(); dataSourceBuilder.driverClassName("org.h2.Driver"); dataSourceBuilder.url("jdbc:h2:file:C:/temp/test"); dataSourceBuilder.username("sa"); dataSourceBuilder.password(""); return dataSourceBuilder.build(); >@Bean(name = "mysqlDataSource") @Primary public DataSource mysqlDataSource() < DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(); dataSourceBuilder.url("jdbc:mysql://localhost/testdb"); dataSourceBuilder.username("dbuser"); dataSourceBuilder.password("dbpass"); return dataSourceBuilder.build(); >>While autowiring the datasource, spring boot will prefer the primary datasource i.e., “mysqlDataSource”. To autowire another non-primary datasource, use @Qualifier annotation.
@Autowired DataSource dataSource;@Autowired @Qualifier("h2DataSource") DataSource dataSource;Spring boot provides straightforward ways to create datasource beans – either using properties configuration or using java configuration. Spring boot offers ready-made auto configuration to use which can be further customized with advanced options in application.properties file.
Spring boot tries to find and configure connection pooling, first HikariCP, second Tomcat pooling, and finally Commons DBCP2. HikariCP comes inbuilt with spring-boot-starter-jdbc or spring-boot-starter-data-jpa starters.
We can configure multiple datasources, and we must mark as one of them @Primary . The primary datasource is autowired by default, and other datasources need to be autowired along with @Qualifier annotation.