How to use Prepared Statement In JDBC

Hello and welcome to yet another article. This article serves as a continuation and completion of part-1. If you have no knowledge of JDBC I do advise you read part-1 first then come back to learn to use prepared statement.

Prepared statement generally is an approach to querying a database while ensuring the security of the server from unauthorized access. Prepared statements help prevents SQL injection on a database.

It’s best practice to use prepared statements especially when you have sensitive data of user on your database. Using prepared statement serves as one of the front-line steps against security breaches.

Without much ado let's get right into it. Here we will learn how to perform CRUD i.e create/insert read, update, and delete data on the database.

Create/Insert to Database

// database connection fields
String url = "jdbc:mysql://localhost:3306/fk_server?useSSl=false";
String user = "root";
String pword = "";

// INSERT
public void insertDataPrepared() {

    String firstname = "Barack";
    String lastname = "Obama";
    String email = "barack@gmail.com";

    // open connection to database
    try(Connection connection = DriverManager.getConnection(url, user, pword);
        // prepared statement object to perform query
        PreparedStatement preparedstatement = connection.prepareStatement("INSERT INTO fk_example VALUES(?, ?, ?");)
    {   
        // assigning unknown value in query
        preparedstatement.setString(1, firstname);
        preparedstatement.setString(2, lastname);
        preparedstatement.setString(3, email);

        // execute prepared statement object to insert into database
        preparedstatement.executeUpdate();

        System.out.println("Data insert success");
    } catch (SQLException e) {

        e.printStackTrace();
    }
}

Read from Database

// database connection fields
String url = "jdbc:mysql://localhost:3306/fk_server?useSSl=false";
String user = "root";
String pword = "";

// READ
public void getDataPrepared() {

    int id = 1;

    // open connection to database
    try(Connection connection = DriverManager.getConnection(url, user, pword);
        // prepared statement object to perform query
        PreparedStatement preparedstatement = connection.prepareStatement("SELECT * FROM fk_example WHERE id = ?");) {

        // assigning the values from query
        preparedstatement.setInt(1, id);

        // execute the prepared statement object and return a result
        ResultSet result = preparedstatement.executeQuery();

        // process the result
        while (result.next()) {

            String firstname = result.getString("firstname");
            String lastname = result.getString("lastname");
            String email = result.getString("email");

            // output result
            System.out.println(id + " Name: " + firstname + " Surname: " + lastname + " Email: " + email);
        }

    } catch (SQLException e) {

        e.printStackTrace();
    }
}

Update to Database

// database connection fields
String url = "jdbc:mysql://localhost:3306/fk_server?useSSl=false";
String user = "root";
String pword = "";

// UPDATE
public void updateDataPrepared() {

    String firstname = "John";
    String email = "johndoe@gmail.com";
    int id = 1; 

    // open connection to database
    try(Connection connection = DriverManager.getConnection(url, user, pword);
        // prepared statement object to perform query
        PreparedStatement preparedstatement = connection.prepareStatement("UPDATE fk_example SET firstname = ?, " +
                "email = ? WHERE id = ?");) {

        // assigning unknown values from query
        preparedstatement.setString(1, firstname);
        preparedstatement.setString(2, email);
        preparedstatement.setInt(3, id);

        // execute query to update database
        preparedstatement.executeUpdate();

        System.out.println("Data update success");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Delete from Database

// database connection fields
String url = "jdbc:mysql://localhost:3306/fk_server?useSSl=false";
String user = "root";
String pword = "";

// DELETE
public void deleteDataPrepared() {

    int id = 1;

    // open connection to database
    try(Connection connection = DriverManager.getConnection(url, user, pword);
        // prepared statement object to perform query
        PreparedStatement preparedstatement = connection.prepareStatement("DELETE FROM fk_example WHERE id = ?");)   {

        // assigning unknown value from query
        preparedstatement.setInt(1, id);

        // execute query to delete from database
        preparedstatement.executeUpdate();

        System.out.println("Delete Success");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

You have come to the end of the article I hope you enjoy the read. Write to you soon. If you have got any questions please drop in the comment section You can connect with me on LinkedIn.