JDBC Tutorial | Performing Database Operations in Java

In our last tutorial, we studied Socket Programming in Java. In this JDBC tutorial, we are going to learn about performing database operations in Java by utilizing the JDBC API (SQL CREATE, INSERT, UPDATE, DELETE, and SELECT) using appropriate examples of Java Performing Database Operation with some examples of JDBC.

So, let us start the JDBC Tutorial.

JDBC Tutorial | Performing Database Operations in Java

What is JDBC?

JDBC Tutorial will help you in figuring out how to do fundamental database activities utilizing the JDBC (Java Database Connectivity) API. These fundamental tasks are INSERT, SELECT, UPDATE, and DELETE statements in SQL dialect. Even though the objective database framework is Oracle Database.

JDBC is an application interface that acts as a mediator between the database and the applications. It allows the application to integrate with the database and perform all the database operations.

However, similar procedures can be connected to other database frameworks too as a result of the question grammar utilized is standard SQL is for the most part bolstered by all social database frameworks.

In 5 Simple Steps – Establish JDBC Connection in Java

Requirements for JDBC API:

• JDK
• Prophet Database
• JDBC driver for Oracle Database (Download Oracle Database 11g discharge 2 JDBC drivers). You need to add ojdbc6.jar to extend the library.

a. Creating a user in Oracle Database and granting required permissions

• Open Oracle utilizing cmd. For that, compose SQL Plus in cmd and press Enter.
• Make a client ID secured by a password. This client ID is called a youngster client.

Make the client recognized by;

• Give expected authorizations to the young client. For straightforwardness, we allow database manager privilege to the youngster client.

conn/as sysdba;
grant dba to;

Java JDBC Example –

CREATE TABLE userid(
id varchar2(30) NOT NULL PRIMARY KEY,
pwd varchar2(30) NOT NULL,
fullname varchar2(50),
email varchar2(50)
);

Let’s Explore Multithreading in Java

Principal JDBC Interfaces and Classes

We investigate the JDBC’s fundamental interfaces and classes, which we’ll use in this JDBC Tutorial. They are, on the whole, accessible under the java.sql bundle:

i. forName()

Here we stack the driver’s class document into memory at runtime. No need to utilize new or produce a question.

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

ii. DriverManager

This class is utilized to enlist a driver for a particular database to compose (e.g., Prophet Database in this tutorial) and to set up a database associated with the server by means of its getConnection() method.

iii. Association

This interface speaks to a built-up database association (session) from which we can make explanations to execute questions and recover results, get metadata about the database, close the association, and so forth.

Association con = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");

• Statement and PreparedStatement: These interfaces are utilized to execute static SQL queries and parameterized SQL queries, respectively. A Proclamation is the super interface of the PreparedStatement interface. Their usually utilized strategies are:
• Boolean execute(String sql): Executes a general SQL statement. It returns genuine if the question restores a ResultSet, false if the query restores a refresh tally or returns nothing. This strategy can be utilized with a Statement as it were.

Do you Know Island of Isolation in Java with Example

• Int executeUpdate(String sql): Executes an INSERT, UPDATE or DELETE statement and returns a refresh account demonstrating several lines influenced (e.g. 1 row embedded, or 2 columns refreshed, or 0 lines influenced).

Statement stmt = con.createStatement();
String q1 = "embed into userid values
('" +id+ "', '" +pwd+ "', '" +fullname+ "', '" +email+ "')";
int x = stmt.executeUpdate(q1);

ResultSet executeQuery(String sql): Executes a SELECT statement and returns a ResultSet object that contains comes about returned by the query.

Statement stmt = con.createStatement();
String q1 = "select * from userid WHERE id = '" + id + "'
Also, pwd = '" + pwd + "'";
ResultSet rs = stmt.executeQuery(q1);

iv. ResultSet

Contains table information returned by a SELECT statement. Utilize this protest to emphasize columns in the outcome set utilizing the straightaway() technique.

v. SQLException

The checked special case is announced to be thrown by all the above strategies, so we need to get this exemption expressly when calling the over classes’ techniques.

JDBC Tutorial – Principal JDBC Interfaces and Classes

Connecting to the Database

This is the first step of the JDBC Tutorial or JDBC API.

Read About Java Garbage Collection Algorithm – Mark and Sweep Algorithm

import java.sql.*;
public class connect
{
    public static void main(String args[])
    {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(
             "jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
            if (con != null)            
                System.out.println("Connected");           
            else          
                System.out.println("Not Connected");
            
            con.close();
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
    }
}

Output- 
Connected

Implementing Insert Statement

Applying the insert statement to input the data into the database.

import java.sql.*;
public class insert1
{
    public static void main(String args[])
    {
        String id = "id1";
        String pwd = "pwd1";
        String fullname = "geeks for geeks";
        String email = "[email protected]";        
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("
             jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
            Statement stmt = con.createStatement();
            String q1 = "insert into userid values('" +id+ "', '" +pwd+
                                  "', '" +fullname+ "', '" +email+ "')";
            int x = stmt.executeUpdate(q1);
            if (x > 0)           
                System.out.println("Successfully Inserted");           
            else          
                System.out.println("Insert Failed");            
            con.close();
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
    }
}

Output- 

Successfully Registered

Let’s read about Working & Types of Java Packages With Examples

Implementing Update Statement

Performing the updating operation on the data.

import java.sql.*;
public class update1
{
    public static void main(String args[])
    {
        String id = "id1";
        String pwd = "pwd1";
        String newPwd = "newpwd";
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("
             jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
            Statement stmt = con.createStatement();        
            // Updating database
            String q1 = "UPDATE userid set pwd = '" + newPwd +
                    "' WHERE id = '" +id+ "' AND pwd = '" + pwd + "'";
            int x = stmt.executeUpdate(q1);           
            if (x > 0)            
                System.out.println("Password Successfully Updated");           
            else          
                System.out.println("ERROR OCCURED :(");            
            con.close();
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
    }
}

Output- 
Password Successfully Updated

Implementing Delete Statement

Deleting the element from the database.

import java.sql.*;
public class delete
{
    public static void main(String args[])
      {
          String id = "id2";
          String pwd = "pwd2";
          try
           {
              Class.forName("oracle.jdbc.driver.OracleDriver");
              Connection con = DriverManager.getConnection("
              jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
              Statement stmt = con.createStatement();                 
              // Deleting from database
              String q1 = "DELETE from userid WHERE id = '" + id +
                    "' AND pwd = '" + pwd + "'";                  
              int x = stmt.executeUpdate(q1);            
              if (x > 0)           
                System.out.println("One User Successfully Deleted");           
            else
                System.out.println("ERROR OCCURED :(");           
            con.close();
           }
         catch(Exception e)
        {
            System.out.println(e);
        }
    }
}

Output- 

One User Successfully Deleted

What is Java Character Class Methods with Syntax and Examples

Implementing Select Statement

Selecting data from the table for display.

import java.sql.*;
public class select
{
   public static void main(String args[])
      {
         String id = "id1";
         String pwd = "pwd1";
         try
           {
              Class.forName("oracle.jdbc.driver.OracleDriver");
              Connection con = DriverManager.getConnection("
                    jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
               Statement stmt = con.createStatement();
               String q1 = "select * from userid WHERE id = '" + id +
                                    "' AND pwd = '" + pwd + "'";
                ResultSet rs = stmt.executeQuery(q1);
                 if (rs.next())
                   {
                       System.out.println("User-Id : " + rs.getString(1));
                       System.out.println("Full Name :" + rs.getString(3));
                       System.out.println("E-mail :" + rs.getString(4));
                    }
             else
               {
                 System.out.println("No such user id is already registered");
               }
            con.close();
         }
        catch(Exception e)
        {
            System.out.println(e);
        }
    }
}

Output- 
User-Id: id1
Full Name: Dataflair

Conclusion

In this JDBC tutorial, we learned about how to perform the various database functions (SQL) in Java. We looked into various programs and steps: Connecting to a database, executing an INSERT statement, executing a SELECT Statement, executing an UPDATE statement, AND executing a DELETE statement with an example program. Furthermore, if you have any queries regarding the JDBC Tutorial, feel free to ask in the comments section.

Related Topic: Access Modifiers in Java 

For reference