JDBC Tutorial | Performing Database Operations in Java

1. JDBC Tutorial – Objective

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

So, let us start the JDBC Tutorial.

JDBC Tutorial | Performing Database Operations in Java

2. What is JDBC?

JDBC Tutorial will help you in figuring out how to do fundamental database activities utilizing JDBC (Java Database Connectivity) API. These fundamental tasks are INSERT, SELECT, UPDATE and DELETE articulations in SQL dialect. In spite of the fact that the objective database framework is Oracle Database. 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 watchword. This client id is called youngster client.

Make the client recognized by;

• Give expected authorizations to youngster client. For straightforwardness, we allow database manager privilege to 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

3. 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 the runtime. No need of utilizing new or production of a question.

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

ii. DriverManager

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

iii. Association

This interface speaks to a built up database association (session) from which we can make explanations to execute questions and recover comes about, get metadata about the database, close 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 question and parameterized SQL inquiry, separately. A proclamation is the super interface of the PreparedStatement interface. Their usually utilized strategies are:
• Boolean execute(String sql): Executes a general SQL articulation. It returns genuine if the question restores a ResultSet, false if the inquiry 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 proclamation and returns a refresh account demonstrating a number of lines influenced (e.g. 1 push 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 proclamation and returns a ResultSet question which contains comes about returned by the inquiry.

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 question. Utilize this protest emphasize over columns in the outcome set utilizing 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

a. JDBC Tutorial – Connecting to the Database

This is the first step of 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

b. JDBC Tutorial – Implementing Insert Statement

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

c. JDBC Tutorial – Implementing Update Statement

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

d. JDBC Tutorial – Implementing Delete Statement

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

e. JDBC Tutorial – Implementing Select Statement

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

4. 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 INSERT statement, executing SELECT Statement, executing UPDATE statement, AND executing DELETE statement with an example program. Furthermore, if you have any query regarding JDBC Tutorial, feel free to ask in the comment section.

Related Topic- Access Modifiers in Java 

For reference