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 a users 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 the users database table that is created in the above section.
PreparedStatement interface provides the executeUpdate() 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 the users 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();
}
}
}
}
}
JDBC PreparedStatement - Select a Record Example
In the previous example, we have inserted or updated a record into the users 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
Comments
Post a Comment
Leave Comment