For a small application it is OK to use DriveManager to get connection as we saw in the example JDBC Database Connection Steps but any enterprise application will most definitely use DataSource as means of connecting to a data source.
With JDBC DataSource objects you get advantage of connection pooling and distributed transactions along with other advantages like configuring time DS waits for the connection, loose coupling so that when the DB is moved to a different server, the property for the server can be changed in the DataSource object.
DataSource interface in Java
javax.sql.DataSource interface is a factory for connections to the physical data source. The DataSource interface is implemented by a driver vendor and there are three types of implementations:
- Basic implementation- This implementation produces a standard Connection object. With a basic implementation, the connection obtained through a DataSource object is identical to a connection obtained through the DriverManager class.
- Connection pooling implementation- In this implementation there is a cache of database connection objects called connection pool. At run time, the application requests a connection from the pool rather than creating new connection every time.
- Distributed transaction implementation- Produces a Connection object that may be used for distributed transactions (a transaction that accesses two or more DBMS servers).
A JDBC driver should include at least a basic DataSource implementation. For example Derby DB implementation of DataSource is org.apache.derby.jdbc.BasicClientDataSource40 class, MySQL provided implementation is com.mysql.jdbc.jdbc2.optional.MysqlDataSource class and for Oracle it is oracle.jdbc.pool.OracleDataSource.
Methods in JDBC DataSource interface
- getConnection()- Attempts to establish a connection with the data source that this DataSource object represents.
- getConnection(String username, String password)- Attempts to establish a connection with the data source that this DataSource object represents by passing credentials like user name and password.
- getLoginTimeout()- Gets the maximum time in seconds that this data source can wait while attempting to connect to a database.
- setLoginTimeout(int seconds)- Sets the maximum time in seconds that this data source will wait while attempting to connect to a database.
JDBC DataSource example
Let’s see an example using MySQL DataSource. Schema used is knpcode and table is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT. You should have the mysql-connector jar in your class path.
Properties are read from a properties file db.properties which is in the classpath.
MYSQL.DRIVER_CLASS=com.mysql.cj.jdbc.Driver MYSQL.DB_URL=jdbc:mysql://localhost:3306/knpcode MYSQL.DB_USER=root MYSQL.DB_PASSWORD=admin
Class used for creating MysqlDataSource instance.
class MyDataSource {
public static DataSource getMySQLDS() {
MysqlDataSource mySqlDS = new MysqlDataSource();
MyDataSource myDS = new MyDataSource();
Properties properties = myDS.loadProperties();
mySqlDS.setUrl(properties.getProperty("MYSQL.DB_URL"));
mySqlDS.setUser(properties.getProperty("MYSQL.DB_USER"));
mySqlDS.setPassword(properties.getProperty("MYSQL.DB_PASSWORD"));
return mySqlDS;
}
// Method to load the properties file
private Properties loadProperties(){
Properties properties = new Properties();
InputStream inputStream = null;
try {
// Loading properties file from the classpath
inputStream = this.getClass()
.getClassLoader()
.getResourceAsStream("db.properties");
if(inputStream == null){
throw new IOException("File not found");
}
properties.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if(inputStream != null){
inputStream.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return properties;
}
}
Class used to create connection by getting MySQLDataSource instance and using that connection object to get a PreparedStatement instance.
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.mysql.cj.jdbc.MysqlDataSource;
public class DSDemo {
public static void main(String[] args) {
DSDemo dsDemo = new DSDemo();
dsDemo.displayEmployeeById(16);
}
private void displayEmployeeById(int id){
Connection connection = null;
String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
PreparedStatement prepStmt = null;
try {
DataSource ds = MyDataSource.getMySQLDS();
connection = ds.getConnection();
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id: " + rs.getInt("id"));
System.out.println("First Name: " + rs.getString("FIRST_NAME"));
System.out.println("Last Name: " + rs.getString("LAST_NAME"));
System.out.println("Department: " + rs.getString("DEPARTMENT"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
These basic DataSource implementation provided by DB vendors like MySQL, Oracle have the disadvantage of tightly coupling your code with the specific DB vendor. There are third party libraries like Apache DBCP, C3P0 that can be used with any DB vendor and provide a pooled data source that increases the application efficiency.
That's all for the topic JDBC DataSource Interface. If something is missing or you have something to share about the topic please write a comment.
You may also like
No comments:
Post a Comment