Java JDBC CRUD Operations in Eclipse - SQL Insert, Select, Update, and Delete Examples

Author:
In this tutorial, we will learn how to write a Java program to connect to the MySQL database and perform basic database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API.

These CRUD operations are equivalent to the INSERT, SELECT, UPDATE and DELETE statements in SQL language.

Before dive into coding CRUD operations, let's understand what is JDBC and what is JDBC API?.

1. What is JDBC?


Java Database Connectivity or JDBC API provides industry-standard and database-independent connectivity between the Java applications and relational database servers (relational databases, spreadsheets, and flat files).


To keep it simple, JDBC allows a Java application to connect to a relational database. The major databases are supported such as Oracle, Microsoft SQL Server, DB2 and many others.

Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:
  1. Import JDBC packages 
  2. Load and register the JDBC driver // This step is not required in Java 6 and in JDBC 4.0
  3. Open a connection to the database.
  4. Create a statement object to perform a query.
  5. Execute the statement object and return a query resultset.
  6. Process the resultset.
  7. Close the resultset and statement objects. // This step is not required because we use a try-with-resource statement to auto-close the resources
  8. Close the connection. // This step is not required because we use a try-with-resource statement to auto-close the resources
From the above steps, we actually require below five steps to connect a Java application to the database (example: MySQL):
  1. Import JDBC packages 
  2. Open a connection to the database.
  3. Create a statement object to perform a query.
  4. Execute the statement object and return a query resultset.
  5. Process the resultset.
We are going to use the above steps in further Java JDBC programs.

Key points

  1. From JDBC 4.0, we don't need to include 'Class.forName()' in our code to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
  2. We have used try-with-resources statements to automatically close JDBC resources.

2. JDBC API

    The JDBC API is comprised of two packages:
    We automatically get both packages when you download the Java Platform Standard Edition (Java SE) 8.
    JDBC API consists of two parts – the first part is the JDBC API to be used by the application programmers. The second part is the low-level API to connect to a database server(JDBC Driver).
    1. The first part of JDBC API is part of standard java packages in java.sql package. We use java.sql package API for accessing and processing data stored in a data source (usually a relational database) using the Java programming language.
    2. For the second part is the JDBC driver(there are four different types of JDBC drivers) A JDBC driver is a set of Java classes that implement the JDBC interfaces, targeting a specific database. The JDBC interfaces come with standard Java, but the implementation of these interfaces is specific to the database you need to connect to. Such an implementation is called a JDBC driver.

    3. Java JDBC CRUD Operations in Eclipse - SQL Insert, Select, Update, and Delete Examples 

    Prerequisites

    To begin, make sure you have the following pieces of software installed on your computer:
    1. JDK (download JDK 7).
    2. MySQL (download MySQL Community Server 5.6.12). You may also want to download MySQL Workbench - a graphical tool for working with MySQL databases.
    3. JDBC Driver for MySQL (download MySQL Connector/J 5.1.25). Extract the zip archive and put the mysql-connector-java-VERSION-bin.jar file into classpath (in the same folder as your Java source files).
    Create a Java project in Eclipse IDE and add the mysql-connector-java-VERSION-bin.jar file into the classpath of the project.

    Let's start with creating a users table and then we will perform  INSERT, UPDATE, SELECT, and DELETE operations.

    JDBC execute to create a table example

    Let's use the Statement interface to create a table - Statement object used for executing a static SQL statement and returning the results it produces. 
    1. Create a database with name - mysql_database
    2. Create a table with a name - users
     The DDL Script for users table: 
       create table users(
        id  int(3) primary key,
        name varchar(20),
        email varchar(20),
        country varchar(20),
        password varchar(20)
  );
    Here is a complete Java JDBC program to create a users table in a database:
    package com.javaguides.jdbc.statement.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Create Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class CreateStatementExample {

    private static final String createTableSQL = "create table users (\r\n" + "  id  int(3) primary key,\r\n" +
        "  name varchar(20),\r\n" + "  email varchar(20),\r\n" + "  country varchar(20),\r\n" +
        "  password varchar(20)\r\n" + "  );";

    public static void main(String[] argv) throws SQLException {
        CreateStatementExample createTableExample = new CreateStatementExample();
        createTableExample.createTable();
    }

    public void createTable() throws SQLException {

        System.out.println(createTableSQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            statement.execute(createTableSQL);
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

    JDBC Execute SQL Insert Statement - Insert a Record Example

    In this example, we use a PreparedStatement interface to insert a record into users table. As we know PreparedStatement interface improves performance like SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
    package com.javaguides.jdbc.preparestatement.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Insert PrepareStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class InsertPStatementExample {
    private static final String INSERT_USERS_SQL = "INSERT INTO users" +
        "  (id, name, email, country, password) VALUES " +
        " (?, ?, ?, ?, ?);";

    public static void main(String[] argv) throws SQLException {
        InsertPStatementExample createTableExample = new InsertPStatementExample();
        createTableExample.insertRecord();
    }

    public void insertRecord() throws SQLException {
        System.out.println(INSERT_USERS_SQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "Tony");
            preparedStatement.setString(3, "[email protected]");
            preparedStatement.setString(4, "US");
            preparedStatement.setString(5, "secret");

            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

    JDBC Execute SQL SELECT Statement - Select Records Example

    So far we have created a users table and inserted a few records into it. So now we will query and retrieve a unique user from the database table by id.
    package com.javaguides.jdbc.preparestatement.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Select PreparedStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class SelectPStatementExample {
    private static final String QUERY = "select id,name,email,country,password from users where id =?";

    public static void main(String[] args) {

        // using try-with-resources to avoid closing resources (boiler plate code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
            preparedStatement.setInt(1, 1);
            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            ResultSet rs = preparedStatement.executeQuery();

            // Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
    Output:
    1,Ram,[email protected],US,secret

    JDBC Execute SQL UPDATE statement - Update a Record Example

    We should use the PreparedStatement interface to perform SQL Update operation. In this example, we are updating the name by id.

    package com.javaguides.jdbc.preparestatement.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Update PreparedStatement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class UpdatePStatementExample {

    private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";

    public static void main(String[] argv) throws SQLException {
        UpdatePStatementExample updateStatementExample = new UpdatePStatementExample();
        updateStatementExample.updateRecord();
    }

    public void updateRecord() throws SQLException {
        System.out.println(UPDATE_USERS_SQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {
            preparedStatement.setString(1, "Ram");
            preparedStatement.setInt(2, 1);

            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
    Read more JDBC SQL update examples on JDBC PreparedStatement - Update a Record Example.

    JDBC Execute SQL DELETE Statement - Delete a Record Example

    Here we have a users table in a database and we will delete a record with following JDBC program.
    package com.javaguides.jdbc.statement.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Delete Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class DeleteStatementExample {

    private static final String DELETE_USERS_SQL = "delete from users where id = 3;";

    public static void main(String[] argv) throws SQLException {
        DeleteStatementExample deleteStatementExample = new DeleteStatementExample();
        deleteStatementExample.deleteRecord();
    }

    public void deleteRecord() throws SQLException {
        System.out.println(DELETE_USERS_SQL);

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            int result = statement.executeUpdate(DELETE_USERS_SQL);
            System.out.println("Number of records affected :: " + result);
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
    Output:
    delete from users where id = 3;
Number of records affected :: 1
    Read more JDBC SQL Delete examples on JDBC Statement - Delete a Record Example.

    4. Conclusion

    In this tutorial, we have learned how to to write a Java program to connect to the MySQL database and perform basic database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API. 
    Check out complete JDBC tutorial at https://www.javaguides.net/p/jdbc-tutorial.html
    Get the source code of this tutorial on my GitHub Repository. 

    Comments

    Post a Comment

    Leave Comment