Standard Steps to Be Followed For Developing JDBC Application

JDBC or Java Database Connectivity is a Java API to connect and execute the query with the database. It is a specification from Sun microsystems that provides a standard abstraction(API or Protocol) for java applications to communicate with various databases. It provides the language with java database connectivity standards. It is used to write programs required to access databases. JDBC, along with the database driver, can access databases and spreadsheets. The enterprise data stored in a relational database(RDB) can be accessed with the help of JDBC APIs.

Standard Steps followed for developing JDBC(JDBC4.X) Application

1. Load and register the Driver
2. Establish the Connection b/w java application and database
3. Create a Statement Object
4. Send and execute the Query
5. Process the result from ResultSet
6. Close the Connection

Step1: Load and register the Driver

A third-party DB vendor class that implements java.sql.Driver(I) is called a “Driver”. This class Object we need to create and register it with JRE to set up the JDBC environment to run JDBC applications.

public class com.mysql.cj.jdbc.Driver extends com.mysql.cj.jdbc.NonRegisteringDriver implements java.sql.Driver {
    public com.mysql.cj.jdbc.Driver() throws java.sql.SQLException;
    static {};
}

In MySQL Jar, the Driver class is implementing java.sql.Driver, so Driver class Object should be created and it should be registered to set up the JDBC environment inside JRE.

Step 2: Establish the Connection b/w java application and database

• public static Connection getConnection(String url, String username,String password) throws SQLException;
• public static Connection getConnection(String url, Properties) throws SQLException;
• public static Connection getConnection(String url) throws SQLException;

The below creates the Object of Connection interface.

Connection connection = DriverManager.getConnection(url,username,password);

getConnection(url,username,password) created an object of a class which implements Connection(I) that class object is collected by Connection(I). This feature in java refers to

• Abstraction(hiding internal services)
• Polymorphism(making code run in 1:M forms)

1. Can we create an Object for the Interface?
Answer: no

2. Can we create an Object for a class that implements an interface?
Answer: yes

Step 3: Create a Statement Object

• public abstract Statement createStatement() throws SQLException;
• public abstract Statement createStatement(int,int) throws SQLException;
• public abstract Statement createStatement(int,int,int) throws SQLException;

// create statement object
Statement statement = connection.createStatement();

Step 4: Send and execute the Query

From the DB administrator’s perspective queries are classified into 5 types

1. DDL (Create table, alter table, drop table,..)
2. DML(Insert, update, delete)
3. DQL(select)
4. DCL(alter password,grant access)
5. TCL(commit,rollback,savepoint)

Read in detail here: DDL, DQL, DML, DCL and TCL Commands

According to the java developer perspective, we categorize queries into 2 types

• Select Query
• NonSelect Query

Methods for executing the Query are

• executeQuery() => for the select query we use this method.
• executeUpdate() => for insert, update and delete queries we use this method.
• execute() => for both select and non-select queries we use this method

public abstract ResultSet executeQuery(String sqlSelectQuery) throws SQLException;
String sqlSelectQuery ="select sid,sname,sage,saddr from Student";
ResultSet resultSet = statement.executeQuery(sqlSelectQuery);

Step 5: Process the result from ResultSet

public abstract boolean next() throws java.sql.SQLException; => To check whether the next Record is available or not returns true if available otherwise returns false.

System.out.println("SID\tSNAME\tSAGE\tSADDR");

while(resultSet.next()){
    Integer id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    Integer age = resultSet.getInt(3);
    String team = resultSet.getString(4);
    System.out.println(id+"\t"+name+"\t"+age+"\t"+team);
}

Step 6: Close the Connection

// Close the Connection
connection.close();

Example:

Java

/*Java code to communicate with database and execute select
 * query*/

import com.mysql.cj.jdbc.Driver;
import java.io.*;
import java.sql.*;

class GFG {
    public static void main(String[] args)
        throws SQLException
    {
        // Step1. Load and register the Driver
        Driver driver = new Driver(); // Creating driver
                                      // object for MySQLDB
        DriverManager.registerDriver(driver);
        System.out.println("Driver registered successfully");

        // Step2: Establish the connection b/w java and
        // Database
        // JDBC URL SYNTAX::
        // <mainprotocol>:<subprotocol>:<subname>
        String url
            = "jdbc:mysql://localhost:3306/enterprisejavabatch";
        String username = "root";
        String password = "root123";

        Connection connection = DriverManager.getConnection(
            url, username, password);
        System.out.println("Connection object is created:: "
                           + connection);

        // Create a Statement Object
        Statement statement = connection.createStatement();
        System.out.println("Statement object is created:: "
                           + statement);

        // Sending and execute the Query
        String sqlSelectQuery
            = "select sid,sname,sage,saddr from Student";
        ResultSet resultSet
            = statement.executeQuery(sqlSelectQuery);
        System.out.println("ResultSet object is created:: "
                           + resultSet);

        // Process the result from ResultSet
        System.out.println("SID\tSNAME\tSAGE\tSADDR");
        while (resultSet.next()) {
            Integer id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            Integer age = resultSet.getInt(3);
            String team = resultSet.getString(4);
            System.out.println(id + "\t" + name + "\t" + age
                               + "\t" + team);
        }

        // Close the Connection
        connection.close();
        System.out.println("Closing the connection...");
    }
}

Output: