CRUD Operations in Java using JDBC

CRUD Operations in Java using JDBC and Oracle Database

In this article, I am going to discuss CRUD Operations in Java using JDBC and Oracle Database with Examples. Please read our previous article where we discussed Steps to Design JDBC Applications in Java. At the end of this article, you will understand the following pointers in detail.

  1. CRUD Operations in Java
  2. Insert Operation using JDBC in Java
  3. Update Operations using JDBC
  4. Delete Operations using JDBC in Java
  5. How to retrieve the data from a Database in JDBC Application?
  6. Retrieve Operation in Java using JDBC
CRUD Operations in Java using JDBC:

CRUD is the acronym for the following four operations.

  1. C- INSERTION
  2. R- RETRIEVAL
  3. U- UPDATION
  4. D- DELETION

Let us understand each of the above operations with examples in java using JDBC.

The Accout (accno, name, balance ) table assumed to have already been created. Please use below SQL Script to create the table in the Oracle database.

CREATE TABLE ACCOUNT (accnonumber(8) primary key, name varchar2(12), balance number(8,2));

Insert Operation using JDBC in Java:

The following JDBC APPLICATION is used to store 2 accounts information (accno, name, and balance) into the Account table.

import java.sql.*;
class AccountStoringApplication
{
    public static void main(String[] args) throws ClassNotFoundException, SQLException
    {
    	Class.forName("oracle.jdbc.driver.OracleDriver");
    	Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","System", "pranaya");
    	Statement st = con.createStatement();
        int c = st.executeUpdate("insert into account values(1005, 'pranaya', 2345)");
        System.out.println(c + "account stored successfully");
        int c = st.executeUpdate("insert into account values(1006, 'kumar', 5345)");
        System.out.println(c + "" more account stored successfully");
        st.close();
        con.close();
    }
}
Update Operations using JDBC in Java:

The following JDBC APPLICATION is used to update the accounts by adding Rs 2000 to each account in the Account table. The SQL statement for updating is: update account set balance = balance+2000

import java.sql.*;
class UpdateAccountApplication 
{
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {
        Class.forName ("oracle.jdbc.driver.OracleDriver");  
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");  
        Statement st = con.createStatement ();
        int rows = st.executeUpdate ("update account set balance = balance+2000");
        System.out.println (rows + " rows modified");    
        st.close ();    
        con.close ();
    } 
}
Delete Operations using JDBC in Java:

The following JDBC APPLICATION is used to delete the account and if the account does not exist then show the same. Here, we need to enter the account number at runtime. The SQL statement is “delete from account where accno =” + ano

import java.sql.*;
import java.util.*;
class AccountCloseApplication 
{
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {  
        Scanner sc = new Scanner (System.in);  
        System.out.println ("ENTER ACCOUNT NUMBER");   
        int ano = sc.nextInt ();    
        Class.forName ("oracle.jdbc.driver.OracleDriver");    
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");    
        Statement st = con.createStatement (); 
        int c = st.executeUpdate ("delete from account where accno =" + ano);
        if (c == 0)    
            System.out.println ("account doesnot exist");  
        else
            System.out.println ("account closed successfully");
        st.close ();   
        con.close (); 
    } 
}

In the above three cases means for insertion, updation, and deletion, the DBMS returns an integer number. In the case of INSERT if the record is successfully inserted to the database then DBMS returns 1. In the case of UPDATE if the record successfully updated then it returns the no i.e. updated number of records. And in the case of DELETE, it returns 1 if successfully record is deleted else it returns 0. But in the case of retrieval, it is different.

How to retrieve the data from a Database in JDBC Application?

In order to understand this, please have a look at the following image.

How to retrieve the data from a Database in JDBC Application?

Note: We already discuss all steps except the Submitting the select statement step in our previous article. So, here we are only going to discuss this step.

Submitting the Select Statement:

In order to retrieve data from the database, the JDBC application has to submit SELECT SQL statements to the DBMS. The executeQuery() method of statement object is used to submit a select statement to the DBMS. For example

ResultSet rs = st.executeQuery(“select * from account where accno = 1001”);

Object-oriented representation of the records received from the DBMS is nothing but the ResultSet object. ResultSet object is logically divided into three partitions.

  1. ZERO RECORD AREA
  2. RECORD AREA
  3. NO RECORD AREA

When the ResultSet object is created a logical pointer points to ZERO RECORD AREA which is known as the cursor.

Submitting the Select Statement

Processing a record of the ResultSet is nothing but receiving its column values. We should not process the record when the cursor points to ZERO RECORD AREA or NO RECORD AREA of the ResultSet, otherwise, SQL Exception is raised i.e. we should read column values only when the cursor points to the RECORD AREA of the ResultSet.

CRUD Operations in Java using JDBC and Oracle Database

ResultSet object has 15 methods to deal with the cursor. Most frequently used method is “next()”. The next() method does two things.

  1. Moves the cursor by one record (area) in the forward direction.
  2. Returns boolean value after moving the cursor. It returns true if the record exists there, otherwise, it returns false.

ResultSet object has getXXX methods to read column values. This method takes column number of the record of the ResultSet as arguments and returns the column values.

Retrieve Operation in Java using JDBC

The following JDBC Application prompts the user to enter the account number and display the account details. If with that number no account exists then it should display the same.

import java.sql.*;
import java.util.*;
class AccountDetails 
{  
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {
        Scanner sc = new Scanner (System.in);  
        System.out.println ("ENTER ACCOUNT NUMBER");   
        int ano = sc.nextInt ();
        Class.forName ("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");
        Statement st = con.createStatement ();    
        ResultSet rs = st.executeQuery ("select * from account where accno =" + ano);
        if (rs.next ())
        {	
            System.out.println ("account no:     " + rs.getInt (1));
            System.out.println ("acc holder name:" + rs.getString (2));
            System.out.println ("balance :       " + rs.getFloat (3));	
            System.out.println ("address:        " + rs.getString (4));      
        }
        else
            System.out.println ("account doesnot exist");
    
        rs.close ();
        st.close ();
        con.close (); 
    }
}

In the next article, I am going to discuss JDBC ResultSet in Java Application with Examples. Here, in this article, I try to explain CRUD Operations in Java using JDBC and Oracle Database with Examples. I hope you enjoy this CRUD Operations in Java using JDBC and Oracle Database article.

Leave a Reply

Your email address will not be published. Required fields are marked *