3 Different ways of Java Database Connection

3 ways of database connection

 183 total views

Today we will learn about the Java Database connection and several other ways to connect a Java application with DB.

At the start of our career, the first API we encounter for database connection is JDBC API aka Java DataBase Connectivity API.

In the beginning, we learn about Drivers, DriverManager, Connection and etc.

All this learning is mainly to let you understand the basics of JDBC API.

In the real-world, enterprise application does not use simple connectivity classes and hard-coded driver code.

So Let’s see the different ways that Java provides for database connection, starting with the most popular and known.

1. JDBC Connection using DriverManager

If you are working on Java 5, then you have to load the MySQL Driver class.

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

But from Java 6, this line is not mandatory.

In the below code, we are providing

1. Hard-coded MySQL Url.
2. Username
3. Password

And we are using the DriverManager Class to get the MySQL DB connection.

Note: Below Url is specific to MySQL, some other database will have different URL for connection For example

“jdbc:oracle:thin:@localhost:1521:xe” this is the Url for Oracle DB.

public static void main(String[] args) {
		String lURL =
				"jdbc:mysql://localhost:3306/testDB";
		String lUser =
				"root";
		String lPassword =
				"root";
		try (Connection dbConnection=
				DriverManager.getConnection(lURL, lUser, lPassword))
		{
			// Perform DB Operation using dbConnection
		} catch (SQLException ex) {
		    System.out.println("SQLException occured");
		    ex.printStackTrace();
		}
	}

2. Java Database connection using DataSource

The Datasource object is deployed on the Application server, It will be registered with a JNDI Naming Service.

It can be accessed using the same JNDI service.

In the below code, Firstly, we will create the InitialContext Object.

Secondly, use the InitialContext object to access the Datasource object using the JNDI service.

Thirdly, get the DB connection by using Datasource object.

public static void main(String[] args) {
		DataSource dataSource;
		Connection dbConnection = null;
		try {
			Context ctx = new InitialContext();
			dataSource = (DataSource)ctx.lookup("jdbc/testDB");
			dbConnection= dataSource.getConnection();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
		    System.out.println("SQLException occured");
		    ex.printStackTrace();
		} finally {
			if (dbConnection != null)
			{
				try {
					dbConnection.close();
				} catch (SQLException e) {
					System.out.println("SQLException occured On Close");
				}
			}
		}
	}

Benefits of Datasource in Java

1. Connection Pooling

Connection pooling features help you manage the connections dynamically without memory leaks whereas with DriverManager you have to write your own connection pooling mechanism which is a tedious task.

2. Quick Migration options

You can easily migrate your application from one database to another because all the DB configuration resides on the Application server and managed by the server admin.

3. Ease of maintenance

The application does not need to save the connection details like Driver name, DB Url, username and password anywhere which leads to cleaner code.

All you need to know is the JNDI name.

3. Java database connection using ORM framework

Many enterprises are now using one or the other Object Relational Mapping(ORM) frameworks like JPA, Hibernate and etc.

Hibernate framework is able to map Java objects to Database table and Java data types to SQL datatypes.

We will not go into details of Hibernate, we will only check – How hibernate manages database connection.

To save Database connection details Hibernate uses hibernate.cfg.xml.

Lets see how it looks

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">
          com.mysql.jdbc.Driver
        </property>
        <property name="hibernate.connection.url">
          jdbc:mysql://localhost:3306/Book
        </property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password"></property>
        <property name="hibernate.connection.pool_size">1</property>
        <property name="hibernate.current_session_context_class">
          thread
        </property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.dialect">
          org.hibernate.dialect.MySQLDialect
        </property>

        <mapping resource="techdora.hbm.xml" />
    </session-factory>
</hibernate-configuration>

The above XML file has all the connection details for a MySQL database server.

Additionally, In Hibernate you can connect with Datasource as well using below config

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="connection.datasource">
          jdbc/testDB
        </property>
        <property name="dialect">
          org.hibernate.dialect.MySQLDialect
        </property>
        <property name="show_sql">false</property>
        <property name="transaction.factory_class">
            org.hibernate.transaction.JTATransactionFactory
        </property>
        <property name="jta.UserTransaction">
          java:comp/UserTransaction
        </property>
        <mapping resource="techdora.hbm.xml" />
    </session-factory>
</hibernate-configuration>

Below is the Java code to connect with Hibernate DB

private static SessionFactory factoryObj=
			new Configuration().configure().buildSessionFactory();
	public static void main(String[] args) {
		Session lSession = factoryObj.openSession();
	    Transaction lTransaction = null;	      
	    try {
	         lTransaction = lSession.beginTransaction();
	         // read or write persistence object here
	         lTransaction.commit();
	    } catch (HibernateException e) {
	         if (lTransaction!=null)
	        	 lTransaction.rollback();
	         e.printStackTrace(); 
	      } finally {
	         lSession.close(); 
	      }
	}

In Hibernate, session object is used to get the DB connection therefore we need to close it in finally block.

By using a framework user does not have to write the code for driver loading and manual connection creation.

Conclusion

In conclusion, I would say for enterprise level application using DataSource is the most preferred way.

However, as a professional, you must learn and know about all the available methods because it will enable you to take critical business decisions when it comes to scaling and maintaining the database.

Leave a Reply

Your email address will not be published. Required fields are marked *