JDBC DataSource example–oracle, MySQL and Apache DBCP Tutorial

We have already seen that JDBC DriverManager can is used to get relational database connections. But if it comes to actual programming, we want more than just connections.

Most of the times we is looking for loose coupling for connectivity so, we can switch databases easily, connection PO Oling for transaction management and distributed systems support. JDBC DataSource is the preferred approach if you be looking for all of these features in your application. JDBC DataSource interface is present in package and it's only declare, overloaded javax.sql methods and getConnection() getConnection(String str1,String str2) .

It is the responsibility of different Database vendors to provide different kinds of implementation of DataSource INTERFAC E. For example MySQL JDBC Driver provides basic implementation of DataSource interface with com.mysql.jdbc.jdbc2.optional.MysqlDataSource class and Oracle Databas E driver implements it with oracle.jdbc.pool.OracleDataSource class.

These implementation classes provide methods through which we can provide database server details with user credentials. Some of the other common features provided by these DataSource implementation classes is;

• Caching of PreparedStatement for faster processing
• Connection Timeout settings
• Logging Features
• ResultSet Maximum size threshold

Let's create a simple JDBC project and learn. Use MySQL and Oracle DataSource Basic implementation classes to get th e database connection. Our final project would look like below image.

Database Setup

Before we get into our example programs, we need some the database setup with table and sample data. Installation of MySQL or Oracle database is out of the scope of this tutorial, so I'll just go ahead and setup table with SA Mple data.

Mysqlsetup.sql--create Employee tablecreate TABLE ' employee ' (  ' empId ' int (ten) unsigned not NULL,  ' name ' varchar (ten) Default NULL,  PRIMARY KEY (' empId ')) engine=innodb default Charset=utf8; --Insert some sample datainsert into ' Employee ' (' empId ', ' name ') VALUES    (1, ' Pankaj '),    (2, ' David '), commit;

　　

Oraclesetup.sqlcreate TABLE "EMPLOYEE"  (    "EMPID" number not   NULL ENABLE,    "NAME"    VARCHAR2 (ten bytes ) DEFAULT NULL,    PRIMARY KEY ("EMPID")  ); INSERT into employee (Empid,name) VALUES ("Pankaj"), insert into employee (Empid,name) VALUES (5, ' Kumar '); INSERT INTO EM Ployee (Empid,name) VALUES (1, ' Pankaj '); commit;

　　

Now let's move on to our Java programs. For have database configuration loosely coupled, I'll read them from property file.

Db.properties#mysql DB Propertiesmysql_db_driver_class=com.mysql.jdbc.drivermysql_db_url=jdbc:mysql://localhost : 3306/userdbmysql_db_username=pankajmysql_db_password=pankaj123 #Oracle DB propertiesoracle_db_driver_class= Oracle.jdbc.driver.oracledriveroracle_db_url=jdbc:oracle:thin: @localhost: 1521:orcloracle_db_username=hroracle_ Db_password=oracle

　　

Make sure this above configurations match with your local setup. Also Make sure are MySQL and Oracle DB JDBC jars included in the build path of the project.

JDBC MySQL and Oracle DataSource Example

Let's write a factory class, we can use the to get MySQL or Oracle DataSource.

Mydatasourcefactory.javapackage Com.journaldev.jdbc.datasource; Import Java.io.fileinputstream;import java.io.ioexception;import Java.sql.sqlexception;import java.util.Properties ; Import Javax.sql.DataSource; Import Oracle.jdbc.pool.OracleDataSource; Import Com.mysql.jdbc.jdbc2.optional.MysqlDataSource; public class Mydatasourcefactory {public static DataSource Getmysqldatasource () {Properties props = new Prope        Rties ();        FileInputStream FIS = null;        Mysqldatasource mysqlds = null;            try {fis = new FileInputStream ("Db.properties");            Props.load (FIS);            Mysqlds = new Mysqldatasource ();            Mysqlds.seturl (Props.getproperty ("Mysql_db_url"));            Mysqlds.setuser (Props.getproperty ("Mysql_db_username"));        Mysqlds.setpassword (Props.getproperty ("Mysql_db_password"));        } catch (IOException e) {e.printstacktrace ();    } return mysqlds; } public static DataSource GetoracledataSource () {Properties props = new Properties ();        FileInputStream FIS = null;        Oracledatasource oracleds = null;            try {fis = new FileInputStream ("Db.properties");            Props.load (FIS);            Oracleds = new Oracledatasource ();            Oracleds.seturl (Props.getproperty ("Oracle_db_url"));            Oracleds.setuser (Props.getproperty ("Oracle_db_username"));        Oracleds.setpassword (Props.getproperty ("Oracle_db_password"));        } catch (IOException e) {e.printstacktrace ();        } catch (SQLException e) {e.printstacktrace ();    } return oracleds; }         }

　　

Notice that both Oracle and MySQL DataSource implementation classes is very similar, let's write a simple test program to Use these methods and run some test.

Datasourcetest.javapackage Com.journaldev.jdbc.datasource; Import Java.sql.connection;import java.sql.resultset;import java.sql.sqlexception;import java.sql.Statement; Import Javax.sql.DataSource;        public class Datasourcetest {public static void main (string[] args) {testdatasource ("MySQL");        System.out.println ("**********");     Testdatasource ("Oracle");        } private static void Testdatasource (String dbType) {DataSource ds = null;        if ("MySQL". Equals (DbType)) {ds = Mydatasourcefactory.getmysqldatasource ();        }else if ("Oracle". Equals (DbType)) {ds = Mydatasourcefactory.getoracledatasource ();            }else{System.out.println ("Invalid db type");        Return        } Connection con = null;        Statement stmt = null;        ResultSet rs = null;            try {con = ds.getconnection ();            stmt = Con.createstatement (); rs = Stmt.executequery ("Select Empid, Name from Employee ");            while (Rs.next ()) {System.out.println ("Employee id=" +rs.getint ("Empid") + ", name=" +rs.getstring ("Name"));        }} catch (SQLException e) {e.printstacktrace ();                    }finally{try {if (rs! = null) rs.close ();                    if (stmt! = null) stmt.close ();                if (con! = null) con.close ();                } catch (SQLException e) {e.printstacktrace (); }        }    } }

　　

Notice the client class is totally independent of any Database specific classes. This helps us in hiding the underlying implementation details from client program and achieve loose coupling and abstracti On benefits.

When we run the above test program, we'll get below output.

Employee id=1, Name=pankajemployee id=2, Name=david**********employee id=10, Name=pankajemployee ID=5, Name= Kumaremployee id=1, Name=pankaj

　　

Apache Commons DBCP Example

If you look at above DataSource factory class, there is the major issues with it.

1. The factory class methods to create the MySQL and Oracle DataSource is tightly coupled with respective driver API. If we want to remove support for Oracle database in the future or want to add some other database support, it'll require cod E change.
2. Most of the code to get the MySQL and Oracle DataSource are similar, the only different are the implementation class that we is using.

Apache Commons DBCP API helps us in getting rids of these issues by providing DataSource implementation so works as an AB Straction layer between our program and different JDBC drivers.

Apache DBCP Library depends on Commons Pool library, so make sure they both is in the build path as shown in the image.

Here's the DataSource factory class using Basicdatasource that's the simple implementation of DataSource.

Dbcpdatasourcefactory.javapackage Com.journaldev.jdbc.datasource; Import Java.io.fileinputstream;import Java.io.ioexception;import java.util.Properties; Import Javax.sql.DataSource; Import Org.apache.commons.dbcp.BasicDataSource; public class Dbcpdatasourcefactory {public static DataSource Getdatasource (String dbType) {Properties props =        New Properties ();        FileInputStream FIS = null;                 Basicdatasource ds = new Basicdatasource ();            try {fis = new FileInputStream ("Db.properties");        Props.load (FIS);            }catch (IOException e) {e.printstacktrace ();        return null;            } if ("MySQL". Equals (DbType)) {Ds.setdriverclassname (Props.getproperty ("Mysql_db_driver_class"));            Ds.seturl (Props.getproperty ("Mysql_db_url"));            Ds.setusername (Props.getproperty ("Mysql_db_username"));        Ds.setpassword (Props.getproperty ("Mysql_db_password")); }else if ("Oracle". Equals (DbType)){Ds.setdriverclassname (Props.getproperty ("Oracle_db_driver_class"));            Ds.seturl (Props.getproperty ("Oracle_db_url"));            Ds.setusername (Props.getproperty ("Oracle_db_username"));        Ds.setpassword (Props.getproperty ("Oracle_db_password"));        }else{return null;    } return DS; }}

　　

As you can see, the depending on user input, either MySQL or the Oracle datasource is created. If you're supporting only one database in the application then you don ' t even need these logic. Just change the properties and your can switch from one database server to another. The key point through which Apache DBCP provide abstraction is setdriverclassname () method.

Here's the client program using above factory method to get different types of connection.

Apachecommonsdbcptest.javapackage Com.journaldev.jdbc.datasource; Import Java.sql.connection;import java.sql.resultset;import java.sql.sqlexception;import java.sql.Statement; Import Javax.sql.DataSource;        public class Apachecommonsdbcptest {public static void main (string[] args) {testdbcpdatasource ("MySQL");        System.out.println ("**********");    Testdbcpdatasource ("Oracle"); } private static void Testdbcpdatasource (String dbType) {DataSource ds = Dbcpdatasourcefactory.getdatasource (d                 Btype);        Connection con = null;        Statement stmt = null;        ResultSet rs = null;            try {con = ds.getconnection ();            stmt = Con.createstatement ();            rs = Stmt.executequery ("Select Empid, name from Employee");            while (Rs.next ()) {System.out.println ("Employee id=" +rs.getint ("Empid") + ", name=" +rs.getstring ("Name")); }} catch (SQLException e) {e.printstacktrace ();        }finally{try {if (rs! = null) rs.close ();                    if (stmt! = null) stmt.close ();                if (con! = null) con.close ();                } catch (SQLException e) {e.printstacktrace (); }        }    } }

　　

When you run above program, the output would be same as earlier program.

If you are on the DataSource and above usage, it can be do with the normal drivermanager too. The major benefit of DataSource is when it's used within a Context and with JNDI.

With simple configurations we can create a Database Connection Pool, that's maintained by the Container itself. Most of the servlet containers such as Tomcat and JBoss provide it's own DataSource implementation and all we need are to C Onfigure it through simple XML based configurations and then use JNDI context lookup to get the DataSource and work with I T. helps us by taking care of connection pooling and management from our application side to server side and thus giv ing us more time-to-write business logic for the application.

In next tutorial, we'll learn how we can configure DataSource in Tomcat Container and use it in WEB application.

JDBC DataSource example–oracle, MySQL and Apache DBCP Tutorial