×
☰ See All Chapters

JDBC Example

In general, to process any SQL statement with JDBC, we follow below steps:

  1. Register the driver class 

  2. Establishing a connection 

  3. Create a statement 

  4. Execute the query 

  5. Process the ResultSet object 

  6. Close the connection 

 

jdbc-example-0
 

Example to connect to the Oracle database

DB script

MySQL DB

 

   CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6));

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES (103,'Manu',3000);

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(104,'Tyagraj',4000);

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(105,'Likitha',5000);

   INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(106,'Advith',6000);

   COMMIT;

 

Oracle DB

   CREATE TABLE EMP(EMP_ID NUMBER(5),NAME VARCHAR2(30),SALARY NUMBER(6));

   INSERT INTO EMP(EMP_ID,NAME,SALARY) VALUES(103,'MANU',3000);

   INSERT INTO EMP(EMP_ID,NAME,SALARY) VALUES(104,'LIKITHA',4000);

   INSERT INTO EMP(EMP_ID,NAME,SALARY) VALUES(105,'THYAGRAJ',5000);

   COMMIT;

 

OracleCon.java

import java.sql.Connection;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import oracle.jdbc.driver.*;

 

public class OracleCon {

        public static void main(String[] args) {

                try {

                        // step1 load the driver class

                        Class.forName("oracle.jdbc.driver.OracleDriver");

 

                        // step2 create the connection object

                        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "Manu_M", "mm");

 

                        // step3 create the statement object

                        Statement stmt = con.createStatement();

 

                        // step4 execute query

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

 

                        // step 5: process the results

                        while (rs.next()) {

                                int id = rs.getInt(1);

                                String name = rs.getString(2);

                                int sal = rs.getInt("SALARY");

                                System.out.println(id + "\t" + name + "\t" + sal);

                        }

                }

 

                catch (ClassNotFoundException e) {

                        //Debugging Tips: Always print stack trace to identify programming mistakes

                        System.out.println("Exception caught...." + e);

                        e.printStackTrace();

                } catch (SQLException e) {

                        System.out.println("Exception caught...." + e);

                        e.printStackTrace();

                }

// step 6 : close the connection object

                finally {

                        try {

                                rs.close();

                                stmt.close();

                                con.close();

                        } catch (SQLException e) {

                                e.printStackTrace();

                        }

                }

 

        }

}

Example to connect to the MySQL database

import java.sql.Connection;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class MysqlCon {

 

        public static void main(String[] args) {

                Connection con = null;

                Statement stmt = null;

                ResultSet rs = null;

                try {

                        // step1 load the driver class

 

                    //Class.forName("com.mysql.jdbc.Driver"); //for mysql versions lesser than 8.0

                Class.forName("com.mysql.cj.jdbc.Driver");//for mysql version 8.0

                        // step2 create the connection object

                        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "welcome");

                        // step3 create the statement object

                        stmt = con.createStatement();

 

                        // step4 execute query

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

 

                        // step 5: process the results

                        while (rs.next()) {

                                int id = rs.getInt(1);

                                String name = rs.getString(2);

                                int sal = rs.getInt("SALARY");

                                System.out.println(id + "\t" + name + "\t" + sal);

                        }

 

                } catch (ClassNotFoundException e) {

                        System.out.println("Exception caught...." + e);

                        e.printStackTrace();

                } catch (SQLException e) {

                        System.out.println("Exception caught...." + e);

                        e.printStackTrace();

                }

                // step 6 : close the connection object

                               //Always close the connections in finally block. To do this variables rs, stmt, con must be declared outside the try block.

                finally {

                        try {

                                rs.close();

                                stmt.close();

                                con.close();

                        } catch (SQLException e) {

                                e.printStackTrace();

                        }

                }

        }

}

Output for above programs

103

Manu

3000

104

Tyagraj

4000

105

Likitha

5000

106

Advith

6000

 

Register the driver class

JDBC API is mostly with interfaces and with very few classes. Database vendor provides implementations for all these interfaces. These implementations are referred as Driver.  Before using this driver (implementations) we need to register this driver, just like we register our SIM with network provider when mobile phone is switched on.

In order to register the driver (implementations), each database should supply a class that implements the Driver interface present in java.sql package.

Class provided by Oracle for implementing  java.sql.Driver iterface is oracle.jdbc.driver.OracleDriver

Class provided by MySQL for implementing  java.sql.Driver iterface is com.mysql.jdbc.Driver

Code to register mysql driver

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

DriverManager.registerDriver(drive);

Or

For mysql versions lesser than 8.0 - Class.forName("com.mysql.jdbc.Driver");

For mysql version 8.0 - Class.forName("com.mysql.cj.jdbc.Driver");

--------------------------------------------------

Code to register oracle driver

Driver drive=new OracleDriver();

DriverManager.registerDriver(drive);

Or

Class.forName("oracle.jdbc.driver.OracleDriver");

 

Establishing a connection

The getConnection() method of java.sql.DriverManager class is used to establish connection with the database.

Syntax of getConnection() method

public static Connection getConnection(String url, String user name/schema name, String password)

Parameters:

url - a database url of the form jdbc:subprotocol:subname

user - the database user on whose behalf the connection is being made

password - the user's password

Example to establish connection with the Oracle database

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","Manu_M","mm");  

Example to establish connection with the MySQL database

Connection con=DriverManager.getConnection( "jdbc:mysql://localhost:3306/study","root","welcome");

Connection is an interface present in java.sql package

1521 is the DB port number for Oracle database

3306 is the DB port number for MySql database

These are the default port numbers, if you have configured different port numbers you have to use that.

Important methods of ResultSet interface

Method

Description

public boolean absolute(int rows)

Moves the cursor to the given row number in this ResultSet object.

public boolean first()

Moves the cursor to the first row in this ResultSet object.

public boolean last()

Moves the cursor to the last row in this ResultSet object.

public boolean next()

Moves the cursor froward one row from its current position.

public boolean previous()

Moves the cursor to the previous row in this ResultSet object.

public boolean relative(int rows)

Moves the cursor a relative number of rows, either positive or negative.

public int getInt(int columnIndex)

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

public int getInt(String columnLabel)

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

public String getString(int columnIndex)

Retrieves the value of the designated column in the current row of this  ResultSet object as a String in the Java programming language.

public String getString(String columnLabel)

Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.

 

All Chapters
Author