Java JDBC - Steps for connecting to database

What are the steps for making connection between Java Application and Database?

Above is the question, we will be answering for in this post. In previous post we have learnt about Type of JDBC Drivers for making connection with database. In this post we will look into the programming aspects of making connection with database.

Below are the steps for making connection with database in Java application :

1. Loading or Registering JDBC Driver
2. Create Connection
3. Create Statement
4. Execute Query
5. Close the Connection

1. Loading or Registering JDBC Driver

There are two approaches through which JDBC drivers are registered or loaded in Java Application. 

1. Class.forName() : This is the most common approach for registering or loading a JDBC driver. Through this method we load driver classes dynamically at runtime into memory. There is no need of creating object. Following is the example of using Class.forName to load MySQL driver class. 

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

2. DriverManager.registerDriver() : It's a Java inbuilt class with a static member, this method is used if we are using a non-JDK compliant JVM. Following is the example for using DriverManager.registerDriver() for registering a driver :

Driver driverObj = new com.mysql.jdbc.Driver();

DriverManager.registerDriver(driverObj);

Note : Don't forget to handle ClassNotFoundException in both cases.

2. Creating Connections

After loading driver class, we need to establish connection with database. For establishing connection we will use getConnection() of DriverManager class, below is the syntax for it's use :

Connection con = DriverManager.getConnection(url, user, password); 

con : it is a reference to Connection interface.

user : username of the database from which sql command prompt can be accessed.

password : password of the user provided.

url : it is the path through which database can be accessed. 

For our example url will be in below form :

jdbc:mysql://localhost:3306/student

jdbc:mysql:  - This specifies the type of database to which we are making connection.

localhost:3306 - This specifies the machine on which your database is installed and the port number on which your database service is listening. In our example our database is installed on local machine therefore we have used localhost if our database would have installed on remote machine then we would have used IP address of that machine instead. Default port number on which MySQL service listens is 3306.

student - This is the name of schema to which we want to make connection. 

Below is the list of some commonly used url for making connection with different databases.

RDBMS	JDBC driver name	URL format
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname/databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:portNumber:databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:portNumber/databaseName
Sybase	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname:portNumber/databaseName

3. Creating Statement

After creating connection with database, we can now create Statement object for executing SQL queries on database which we have make connection to.
createStatement(), method of Connection interface is used to create Statement object.

Below is an example for creating Statement object :

Statement stmt = con.createStatement();

4. Executing SQL Query

Next step is to execute SQL query and getting the result of query execution in ResultSet object. When we execute SQL query using Statement object created in above statement, an object of ResultSet class is returned which holds the data returned by executing query.

Below is an example of executing SQL query :

ResultSet rs = stmt.executeQuery("select * from student");


5. Printing result on console

We can loop through ResultSet object to access each row returned by query execution.

Below is an example for looping through the ResultSet object :

while(rs.next()){
     String name = rs.getString("name");
     String rollNo = rs.getString("rool_no");
     System.out.println(name+" : "+rollNo); 
}


6. Close the connection

This is the most important step, always remember to close the connection which you opens otherwise it will increase overhead on database and your application may crash. Believe me, this is one of the bad things I have seen till date which causes application server crash in production.
You can call close() method of connection class to close the connection.

Below is an example to close the connection :

con.close();

Below is a complete program :

 
 1 package TestConnection;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 import java.util.logging.Level;
 9 import java.util.logging.Logger;
10 
11 /**
12  *
13  * @author NPSingh
14  */
15 public class TestConnection {
16 
17     public static void main(String[] args){
18         try {
19             Class.forName("com.mysql.jdbc.Driver");
20             Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
21             Statement stmt = con.createStatement();
22             ResultSet rs = stmt.executeQuery("select * from student");
23             while(rs.next()){
24                 String name = rs.getString("name");
25                 String rollNo = rs.getString("rool_no");
26                 System.out.println(name+" : "+rollNo); 
27             }
28         } catch (ClassNotFoundException | SQLException ex) {
29             Logger.getLogger(TestConnection.class.getName()).log(Level.SEVERE, null, ex);
30         }
31     }
32             
33 }

In subsequent posts on this topic, we will discuss ResultSet, Statement etc. more deeply.

Share: