Java JDBC CRUD Application in IntelliJ and Maven

This JDBC tutorial is going to help you learning how to do basic database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API using maven as a build tool.

We will learn how to do insert, query, update and delete database records by writing code to manage records of a table Employee in a MySQL database called crud.

Understand the main JDBC interfaces and classes

DriverManager: this class is used to register driver for a specific database type (e.g. MySQL in this tutorial) and to establish a database connection with the server via its getConnection() method.

Connection: this interface represents an established database connection from which we can create statements to execute queries and retrieve results, get metadata about the database, close connection, etc.

Statement and PreparedStatement: these interfaces are used to execute static SQL query and parameterized SQL query, respectively. Statement is the super interface of the PreparedStatement interface.

• boolean execute(String sql): executes a general SQL statement. It returns true if the query returns a ResultSet, false if the query returns an update count or returns nothing. This method can be used with a Statement only.
• int executeUpdate(String sql): executes an INSERT, UPDATE or DELETE statement and returns an update account indicating number of rows affected (e.g. 1 row inserted, or 2 rows updated, or 0 rows affected).
• ResultSet executeQuery(String sql): executes a SELECT statement and returns a ResultSet object which contains results returned by the query.
• ResultSet: contains table data returned by a SELECT query. Use this object to iterate over rows in the result set using next() method, and get value of a column in the current row using getXXX() methods (e.g. getString(), getInt(), getFloat() and so on). The column value can be retrieved either by index number (1-based) or by column name.

Adding JDBC Dependecy in Maven POM.xml file:

The following code snippet will add the dependency in Maven:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Connecting to a Database

Supposing the MySQL database server is listening on the default port 3306 at localhost. The following code snippet connects to the database name crud by the user root and password empty by default:

  /*connect method will create connection with database using Connection Interface and

  Driver manager for registering driver for specific database*/   

 public void connect(String dbUrl,String userName,String password){
        try {

            connection= DriverManager.getConnection(dbUrl,userName,password);
            if(connection!=null){
                logger.debug("Database Connection Successful");
                logger.info("Entering application.");

            }
        }catch (SQLException ex){
            //showing exception in log            

            logger.error("Exception in connection: "+ ex.toString());

        }
    }

Inserting Record in Database

Let’s write code to insert a new record into the table employee with following details:

Here’s the code snippet:

public void insertRecord(){

    //sql statement for inserting record    

    String sql = "INSERT INTO employee (first_name, last_name, salary) VALUES (?, ?,?)";
    //getting input from user    Scanner input=new Scanner(System.in);
    System.out.println("Enter First name");
    String fName=input.nextLine();
    System.out.println("Enter Last name");
    String lName=input.nextLine();
    System.out.println("Enter salary");
    String salary=input.nextLine();

    try {
        PreparedStatement statement = connection.prepareStatement(sql);
        //setting parameter values        statement.setString(1, fName);
        statement.setString(2, lName);
        statement.setString(3, salary);
        //executing query which will return an integer value

        int rowsInserted = statement.executeUpdate();
        //if rowInserted is greater then 0 mean rows are inserted        

        if (rowsInserted > 0) {
            logger.debug("A new user was inserted successfully!");
        }
    }catch (Exception e){
        logger.error("Exception in connection: "+ e.toString());

    }
}

In this code, we create a parameterized SQL INSERT statement and create  PreparedStatement from the Connection object. To set values for the parameters in the INSERT statement, we use the PreparedStatement‘s setString() methods

Displaying Record in Database

The following code snippet queries all records from the Employee table and print out details for each record:

public void getAllRecords(){
    //sql statement for inserting record    String sql = "SELECT * FROM employee";
    //Creating a collection form employee list for storing all employee record    

    ArrayList<Employee> employeeList=new ArrayList<Employee>();

    try {

        //creating and executing our statement        

        Statement statement = connection.createStatement();
        ResultSet result = statement.executeQuery(sql);
        //iterating over the rows in the result        while (result.next()) {

            //storing single result in employee object            

            Employee employee=new Employee();
            employee.setId(Integer.parseInt(result.getString(1)));
            employee.setFirstName(result.getString(2));
            employee.setLastName(result.getString(3));
            employee.setSalary(Integer.parseInt(result.getString(4)));

            //adding employee in employee list            

            employeeList.add(employee);
        }
        //caalling function to display all record        

        displayRecord(employeeList);
    }catch (Exception e){
        logger.error("Exception in connection: "+ e.toString());

    }
}

public void displayRecord(ArrayList<Employee> employees){
    //iterating over employee list and displaying all employees data    

        for(int i=0;i<employees.size();i++){
        System.out.println("Id: "+employees.get(i).getId());
        System.out.println("First Name: "+employees.get(i).getFirstName());
        System.out.println("Last Name: "+employees.get(i).getLastName());
        System.out.println("Salary: "+employees.get(i).getSalary());

    }
}

Here i have created an Arraylist of Employee model to store all the records fetched from the

database.  The value is casted to a String because we know that the ifrstname and lastname

are varchar so casted to Integer based on the database schema mentioned previously. 

Updating Record in Database

The following code snippet will update the record based on the employee id taken from user input:

public void updateRecord(){
    //sql statement for inserting record    

    String sql = "UPDATE employee SET first_name=?, last_name=?, salary=? WHERE id=?";
    //getting input from user    Scanner input=new Scanner(System.in);
    System.out.println("Enter id of employee to update");
    String id=input.nextLine();
    System.out.println("Enter First name");
    String fName=input.nextLine();
    System.out.println("Enter Last name");
    String lName=input.nextLine();
    System.out.println("Enter salary");
    String salary=input.nextLine();

    try {
        //creating and executing our statement       

        PreparedStatement statement = connection.prepareStatement(sql);
        //setting parameter values        statement.setString(1, fName);
        statement.setString(2, lName);
        statement.setString(3, salary);
        statement.setString(4, id);

        int rowsUpdated = statement.executeUpdate();
        //if rowInserted is greater then 0 mean rows are inserted        if (rowsUpdated > 0) {
            logger.debug("An existing user was updated successfully!");
        }
    }catch (Exception e){
        logger.error("Exception in connection: "+ e.toString());

    }
}

Deleting Record in Database

The following code snippet will delete a record based on employee id taken from user input:




public void deleteRecord(){
    //sql statement for inserting record    String sql = "DELETE FROM employee WHERE id=?";

    //getting input from user    Scanner input=new Scanner(System.in);
    System.out.println("Enter id of employee to delete");
    String id=input.nextLine();

    try {
        //creating and executing our statement        
        PreparedStatement statement = connection.prepareStatement(sql);
        //setting parameter values        statement.setString(1, id);

        int rowsDeleted = statement.executeUpdate();
        //if rowInserted is greater then 0 mean rows are inserted        
        if (rowsDeleted > 0) {
            logger.debug("Employee was deleted successfully!");
        }else {
            logger.debug("Employee not found");

        }
    }catch (Exception e){
        e.printStackTrace();
    }
}



Closing Database Connection after all operations

To close the database connection simply call the connection object method close() if it is not null:




 public void closeConnection(){
        try {
            if(connection!=null) {
                connection.close();
            }
        }catch (Exception e){
            logger.error("Exception in connection: "+ e.toString());

        }
    }


Github Link:

Press here to get the github link.