Java Sql Delete Prepared Statement Example
JDBC PreparedStatement Tutorial - Select, Insert, Update and Delete Example
In this tutorial, we will learn how to use JDBC PreparedStatement to insert, select, update and delete records with MySQL database.
What is Prepared Statement
A Prepared Statement is simply a precompiled SQL statement.
Prepared Statements provide the following benefits:
- They make it easier to set SQL parameters.
- They prevent SQL dependency injection attacks, and they also may improve the application.
- It provides better performance since the SQL statement is precompiled.
Key points
- 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.
- We use try-with-resources statements to automatically close JDBC resources
Technologies used
- JDK - 1.8 or later
- MySQL - 8+
- IDE - Eclipse Neon
- JDBC API - 4.2
Prep work
- You need to have MySQL database installed - https://dev.mysql.com/downloads/installer/
- Download MySQL JDBC Driver - https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.18
- Use the following SQL statement to create a Database in MySQL Workbench:
create database jdbc_demo; - Create ausers table using the following DDL Script:
CREATE TABLE `users` ( `id` int(3) NOT NULL, `name` varchar(20) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `country` varchar(20) DEFAULT NULL, `password` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET =utf8mb4 COLLATE =utf8mb4_0900_ai_ci;
JDBC PreparedStatement - Insert a Record Example
In this example, we will use theusers database table that is created in the above section.
PreparedStatement interface provides theexecuteUpdate() method - executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
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, "tony@gmail.com" ); 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(); } } } } } Output
JDBC PreparedStatement - Update a Record Example
In the previous example, we have inserted a record into theusers table and now we will update the same record with below Java program:
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(); } } } } } Output
JDBC PreparedStatement - Select a Record Example
In the previous example, we have inserted or updated a record into theusers table and now we will query a list of users 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/jdbc_demo?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,tony@gmail.com,US,secret
JDBC PreparedStatement - Delete a Record Example
Let's write a Java program to delete a record from MySQL database using Java JDBC:
package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Delete Statement JDBC Example * * @author Ramesh Fadatare * */ public class DeletePreparedStatementExample { private static final String DELETE_USERS_SQL = "delete from users where id = ?;" ; public static void main(String[] argv) throws SQLException { DeletePreparedStatementExample deleteStatementExample = new DeletePreparedStatementExample(); deleteStatementExample.deleteRecord(); } public void deleteRecord() throws SQLException { System .out.println(DELETE_USERS_SQL); // no need to register driver manually // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection( "jdbc:mysql://localhost:3306/jdbc_demo?useSSL=false" , "root" , "root" ); // Step 2:Create a statement using connection object PreparedStatement statement = connection.prepareStatement(DELETE_USERS_SQL);) { statement.setInt(1, 1); // Step 3: Execute the query or update query int result = statement.executeUpdate(); 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 = ?; Number of records affected :: 1
Conclusion
In this tutorial, we have seen how to use JDBC PreparedStatement to insert, select, update and delete records with MySQL database.
Learn more about JDBC at https://www.javaguides.net/p/jdbc-tutorial.html
Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours
Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course
Source: https://www.javaguides.net/2019/10/jdbc-preparedstatement-tutorial-select-insert-update-and-delete-example.html
0 Response to "Java Sql Delete Prepared Statement Example"
Post a Comment