Blog

Implement CRUD operations for student table using prepared statements in Java

CRUD operations using prepared Statements

Implement CRUD operations for student table using prepared statements. Ask for user input where applicable. [Tu question ]

Before building the project you need to follow the below step, CRUD operations using prepared statements

1. Start your 'Xampp'
2. At first create the database using sql query which is inside 'src' folder in 'script.sql' file.
   or simply You can also import 'JavaJdbc.sql' and 'bank.sql' database.
3. Note:
	a) Inside each source code:
	   If windows:
		String url = "jdbc:mariadb://localhost:3306/onlinelab";
        	String username = "root";
       		String password= ""; 
 
	    If Linux:
		String url = "jdbc:MySQL://localhost:3306/onlinelab";
        	String username = "db_username"; --> your localhost username.
       		String password= "db_password";  --> your localhost password

4) Extra(Not needed here in my project):
	- I have also attached mariadb connector jar file. If you are creating your own project
	  and want to connect database with your project then you must implement the connection 
	  using mariadb connector jar file. 
	- To implement it follow given link:-
		
		For Netbeans:
		https://www.youtube.com/watch?v=9BqHWF_-Fk0
		
		For Intellij IDEA
		https://www.youtube.com/watch?v=T5Hey0e2Y_g

Create Database using the following script or you can manually create student table with database name onlinelab.

create database onlinelab;
use onlinelab;

CREATE TABLE student (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(25) NOT NULL,
    district varchar(25),
    age int,
    PRIMARY KEY (id)
);

Prepared Statements Code in java for CRUD Operations

Create Prepare Statement :


package database;

import java.sql.*;
import java.util.Scanner;

public class CreatePreparedStatement {
    public static void main(String[] args) throws SQLException {
//********************************************* Establishing Connection to the Database ************************************************

        String url = "jdbc:mariadb://localhost:3306/onlinelab";
        String username = "root";
        String password = "";
        Connection connection = DriverManager.getConnection(url, username, password);

//************************************  Insert into students table according to user input ****************************************************************************
        Scanner in = new Scanner(System.in);

        System.out.println("Enter Name:");
        String name = in.next();

        System.out.println("Enter District:");
        String district = in.next();

        System.out.println("Enter Age:");
        int age = in.nextInt();

        //************************** Sql prepared statement *****************************************
        String sql = String.format("Insert into student (name,district,age) Values (?,?,?)");

        PreparedStatement statement = connection.prepareStatement(sql);

        //****************** Binding Parameter ******************************************************
        statement.setString(1,name);
        statement.setString(2,district);
        statement.setInt(3,age);
        int rowInserted = statement.executeUpdate();

        //******************** message for user if rows are  successfully updated or not **********
        if(rowInserted>0)
            System.out.println("Row Inserted Successfully!");
        else
            System.out.println("Row Insertion Failed!!");
        statement.close();
        connection.close();
    }
}

Read Prepared Statement


package database;

import java.sql.*;
import java.util.Scanner;

public class ReadPreparedStatement
{
        public static void main(String[] args) throws SQLException
        {
//********************************************* Establishing Connection to the Database ************************************************
            String url = "jdbc:mariadb://localhost:3306/onlinelab";
            String username = "root";
            String password = "";
            Connection connection = DriverManager.getConnection(url, username, password);

// **************** If user want to select but he/she don't know what values are  inside database table ***********************************

            System.out.println("Do You want to see database table before making Selection Query??(Y/N):");
            Scanner in = new Scanner(System.in);
            char userChoice = in.next().charAt(0);

            if (userChoice == 'Y')
            {
                String sql = "select * from student";
                PreparedStatement statement = connection.prepareStatement(sql);
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next())
                {
                    System.out.printf("%d, %s, %s, %s, \n",
                            resultSet.getInt("id"),
                            resultSet.getString("name"),
                            resultSet.getString("district"),
                            resultSet.getString("age")
                    );
                }
                statement.close();
            }
// **************** If user want to select but he/she don't know what values are  inside database table ***********************************
            if(userChoice== 'N')
            {
                //************************** Taking user input *******************************
                System.out.println("Enter the Id to select from database:");
                int id = in.nextInt();

                //********************** Sql Prepared Statement************************************

                String sql = "select * from student where id = ?";
                PreparedStatement statement = connection.prepareStatement(sql);
                statement.setInt(1,id);

                ResultSet resultSet = statement.executeQuery();

                while (resultSet.next())
                {
                    System.out.printf("%d, %s, %s, %s \n",
                            resultSet.getInt("id"),
                            resultSet.getString("name"),
                            resultSet.getString("district"),
                            resultSet.getString("age")
                    );
                }
                statement.close();

            }

            connection.close();
        }
}

Update Prepared Statement

package database;


    import java.sql.*;
    import java.util.Scanner;

    public class UpdatePreparedStatement
    {
        public static void main(String[] args) throws SQLException {
    //********************************************* Establishing Connection to the Database ************************************************

            String url = "jdbc:mariadb://localhost:3306/onlinelab";
            String username = "root";
            String password = "";

            Connection connection = DriverManager.getConnection(url, username, password);

    // **************** If user want to update but he/she don't know what values are  inside database table ***********************************

            System.out.println("Do You want to see database table before updating??(Y/N):");
            Scanner in = new Scanner(System.in);
            char userChoice = in.next().charAt(0);

            if (userChoice == 'Y')
            {
                String sql = "select * from student";
                PreparedStatement statement = connection.prepareStatement(sql);
                ResultSet resultSet = statement.executeQuery(sql);
                while (resultSet.next()) {
                    System.out.printf("%d, %s, %s, %s, \n",
                            resultSet.getInt("id"),
                            resultSet.getString("name"),
                            resultSet.getString("district"),
                            resultSet.getString("age")
                    );
                }
                statement.close();
            }
    //******************* If user knows what to update in database table ****************************************************************************

            if (userChoice == 'N')
            {
                //************************ Taking User Input ********************************
                System.out.println("Enter the Id to update into database:");
                int id = in.nextInt();

                System.out.println("Enter the Name to update into database:");
                String name = in.next();

                System.out.println("Enter the District to update into database:");
                String district = in.next();

                System.out.println("Enter the Age to update into database:");
                int age = in.nextInt();

                //************************ Executing Sql statement **************************
                String sql = "update student set name = ?,district= ?, age = ? where id= ?";
                PreparedStatement statement = connection.prepareStatement(sql);
                //****************** Binding Parameter ******************************************************
                statement.setString(1,name);
                statement.setString(2,district);
                statement.setInt(3,age);
                statement.setInt(4,id);

                int rowUpdated = statement.executeUpdate();

                //******************** message for user if rows are  successfully updated or not **********
                if (rowUpdated > 0)
                    System.out.println("Row updated Successfully!!");

                else
                    System.out.println("Row isn't Updated!!");

                statement.close(); //statement has to be close.
            }
            connection.close(); // connection is also closed.
        }

    }

Delete Prepared Statements


package database;


import java.sql.*;
import java.util.Scanner;

public class DeletePreparedStatement
{
    public static void main(String[] args) throws SQLException
    {
//********************************************* Establishing Connection to the Database ************************************************

        String url = "jdbc:mariadb://localhost:3306/onlinelab";
        String username = "root";    //For connection we need 'username' and 'password'
        String password = "";

        Connection connection = DriverManager.getConnection(url, username, password);


// **************** If user want to select but he/she don't know what values are  inside database table ***********************************

        System.out.println("Do You want to see database table before making Selection Query??(Y/N):");
        Scanner in = new Scanner(System.in);
        char userChoice = in.next().charAt(0);

        if (userChoice == 'Y') {
            String sql = "select * from student";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                System.out.printf("%d, %s, %s, %s, \n",
                        resultSet.getInt("id"),
                        resultSet.getString("name"),
                        resultSet.getString("district"),
                        resultSet.getString("age")
                );
            }

        }

// **************** If user want to select but he/she don't know what values are  inside database table ***********************************
        if (userChoice == 'N')
        {
            //********************* Taking input from user*********************
            System.out.println("Enter the Id to Delete from database:");
            int id = in.nextInt();

            // ************* Sql prepared statement *************************
            String sql = "delete from student where id= ?";
            PreparedStatement statement = connection.prepareStatement(sql);

            //************* Binding Parameters ***************************
            statement.setInt(1, id);

            int rowDeleted = statement.executeUpdate();

            //******************** message for user if rows are  successfully deleted or not ******
            if (rowDeleted > 0)
                System.out.println("Row Deleted Successfully!!");

            else
                System.out.println("Row isn't Deleted!!");

            statement.close();
            connection.close();
        }
    }
}