Different steps required to Connect Java Applications with Database Servers using JDBC Driver

Different steps are required to Connect Java Applications with Database Servers using JDBC Driver

 In this java learning tutorial, we shall learn how to use JDBC (Java Database Connectivity) Technology to connect java applications with different database DBMS like MS Access, MS SQL Server, MySQL Server, Oracle SQL Server, Cloud database, etc. There are many ways to use the JDBC driver for connection and access to the database. We shall discuss only the basic steps which are required to accomplish database connection. There are eight basic steps that are necessary to connect an application with a database. These steps are as under 

Step 1:- Import Required Package
The first step is to import the required package "java.SQL.*". This package has useful classes and interfaces to communicate with the database.

import java.sql.*;

Step 2:- Load Driver
In this step load a suitable driver for the underlying database DBMS. As we learn, in my previous post about JDBC there are different JDBC Driver types for different DBMSs.
For example,

• For MS Access, JDBC -ODBC Driver is used which is available with j2se.
       Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

• For Oracle SQL Database Server, load the following driver. This driver is available as free on Oracle Website for downloading.

        Class.forName(“oracle.jdbc.driver.OracleDriver”);

Step 3:- Define Connection URL
For creating a connection with the Database, we need to specify the URL / address of the database.
 
For Microsoft Access use DSN of Database with JDBC - odbc driver.

String conURL = “jdbc:odbc:personDSN”;

For Oracle Database Server:
String  conURL ="jdbc:oracle:thin:@myhost:1521:orcl";


Step 4:- Establish Connection With the Database
Use Driver Manager() method to get the connection object. The URL of the database is passed to the get Connection method.

Connection con = DriverManager.getConnection(conURL);

If the Database requires a username & password, you can use the overloaded version of the getting Connection method as shown below:

con = DriverManager.getConnection(con URL, user, PWD);


For example to connect the Oracle database Scott with the password tiger use below 

Connection conn = DriverManager.getConnection
                 ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");


Step 5:- Create a Statement
As a connection is established, a Statement object is obtained from the Connection object. Once you get a statement object, you can use it for various kinds of SQL queries like SELECT, INSERT, DELETE, UPDATE, etc...

Statement stmt = con.createStatement( );

Step 6:- Execute a Query
After getting the statement object, the next step is to pass the SQL statements and execute them. Generally below mentioned two methods are used for executing SQL queries

o executeQuery(SQL) method:  This method is used for SQL SELECT queries. It returns the Result SET object that contains the results of the query which will be further processed as per requirement.

String sql = “SELECT * from tablename”;
ResultSet rs = stmt.executeQuery(sql);

o executeUpdate(SQL) method: This method is used for executing an update statement like INSERT,
UPDATE or DELETE. It returns an Integer value that represents the number of rows updated

String SQL = “INSERT INTO table-name ” + “(columnNames) Values (values)” ;
int count = stmt.executeUpdate(SQL);

Step 7:- Process Results of the Query
The obtained ResultSet provides various get methods which take column name or column index to access data. The ResultSet maintains the data in the form of tables (rows & columns)
Below are some requirements/features of ResultSet

• Table first row has an index of 1, not 0.
• ResultSet next method returns true or false depending upon whether the next row is available or not available and if the next row is available then it moves next
• Always remember to call the next() method at-least once
• Various getter methods of  ResultSet are used to get data. For example, below mentioned code will iterate over the whole ResultSet.

         while ( rs.next() ){
         String name = rs.getString(“columnName”);            //Retrieved data by using column name
         String name = rs.getString(1); // or Retrieved data by using column index
                                   }

Step 8:- Close the Connection
After retrieving data, close the database connection because an opening connection is expensive which may cause issues for other database requests, postpone this step if additional database operations are required.

con.close();

Example Codes with Steps:
Below is the example code with steps, it will show clearly the steps and codes required to connect the java applications with the required Database DBMS and how to use Getter methods to retrieve the data from the database.

// File JdbcEx.java
//Step 1: Import the required package which is java.sql.

import java.sql.*;

public class JdbcEx {
public static void main (String args[ ]) {
try {

//Step 2: load driver in the try clause

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

//Step 3: define the database connection URL

String url = “jdbc:odbc:personDSN”;

//Step 4: establish the connection using DriverManager.getConnection() method.

Connection con = DriverManager.getConnection(url);

//Step 5: Create a Statement

Statement st = con.createStatement();

//Step 6: preapare & execute the query

String sql = “SELECT * FROM Person”;    // Retrieve data from Person table in database
ResultSet rs = st.executeQuery(sql);

//Step 7: process the results
while(rs.next()){

// For example, the database table Column name are “name, address, and phone”, specified these columns in the getString() method.

String name = rs.getString(“name”);
String add = rs.getString(“address”);
String pNum = rs.getString(“phoneNum”);
System.out.println(name + “ ” + add + ” ” + pNum);    // show output result on console.
}

//Step 8: After the activity is done, close the database connection

con.close();
}catch(Exception sqlEx){
System.out.println(sqlEx);        // this code will show any exception or error if any occurs.
}
} // end main
} // end class

Related Topic:

What is JDBC and different JDBC Driver Types and JDBC Benefits