May 192016

ADM

How to delete a record using JDBC PreparedStatement

  • May 19 2016
  • ADM

 

 

How to delete a record using JDBC PreparedStatement - jdbc

 

Following the previous tutorials on JDBC is time to see how to delete a record using PreparedStatement. For more details check the references section.

Example

package com.admfactory.db;

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

public class JDBCDeleteRecord {

    private static Connection connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/admfactory.com", "root", "");
        } catch (Exception e) {
            System.out.println("Failed to connect to DB.");
            e.printStackTrace();
            return null;
        }
    }

    private static void deleteData(long id) throws Exception {
        PreparedStatement statement = null;
        try {
            String query = "DELETE FROM `user` WHERE `id`=?;";
            Connection connection = connect();
            statement = connection.prepareStatement(query);
            statement.setLong(1, id);
            int r = statement.executeUpdate();
            /** Check if the user was successfully deleted. */
            if (r > 0) {
                System.out.println("User [" + id + "] was successfully deleted.");
            } else {
                System.out.println("Failed to delete user id " + id);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    public static void main(String[] args) {
        try {
            System.out.println("Delete one record.");
            /** Get the connection. */
            Connection connection = connect();

            if (connection != null) {
                /** delete some data. */
                deleteData(1);
                System.out.println("Record deleted successfully!");
                /** Close the connection. */
                connection.close();
            }
        } catch (Exception e) {
            System.out.println("Something went wrong.");
        }

    }
}
Note that the index in statement object starts from 1.

Output

Delete one record.
User [1] was successfully deleted.
Record deleted successfully!
You can also check if the row was successfully deleted using a database GUI tool like, MySQL Workbench.

References