Executing Queries (Insert, Update, Delete, Select) in Java
Introduction
Java provides JDBC (Java Database Connectivity) to interact with relational databases like MySQL and PostgreSQL. With JDBC, you can execute SQL queries like INSERT, UPDATE, DELETE, and SELECT to manipulate and retrieve data from a database.
In this tutorial, we will demonstrate how to execute these four basic SQL queries:
- INSERT: To insert data into a database table.
- UPDATE: To update existing data in a database table.
- DELETE: To delete data from a database table.
- SELECT: To retrieve data from a database table.
Step 1: Set Up the Database
Before executing queries, ensure that you have a database set up. For this tutorial, we will use a sample database called testdb and a table called employees.
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50)
);
INSERT INTO employees VALUES (1, 'John Doe', 'HR');
INSERT INTO employees VALUES (2, 'Jane Smith', 'Finance');
Step 2: Add JDBC Driver
Ensure that the JDBC driver for your database (e.g., MySQL or PostgreSQL) is included in your project’s classpath. You can download the JDBC drivers from:
- MySQL Connector/J for MySQL
- PostgreSQL JDBC Driver for PostgreSQL
Step 3: Establish a Connection to the Database
To perform database operations, you first need to establish a connection using JDBC.
Example 1: MySQL Connection
import java.sql.*;
public class MySQLConnection {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a connection to the MySQL database
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
System.out.println("Connected to MySQL database!");
// Close the connection
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
Example 2: PostgreSQL Connection
import java.sql.*;
public class PostgreSQLConnection {
public static void main(String[] args) {
try {
// Load the PostgreSQL JDBC driver
Class.forName("org.postgresql.Driver");
// Establish a connection to the PostgreSQL database
Connection connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/testdb", "postgres", "password");
System.out.println("Connected to PostgreSQL database!");
// Close the connection
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
Step 4: Executing SQL Queries
Now that you have established a connection to the database, you can execute SQL queries such as INSERT, UPDATE, DELETE, and SELECT using JDBC.
Insert Query
The INSERT query is used to add new data to a table.
import java.sql.*;
public class InsertQueryExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create the INSERT query
String insertQuery = "INSERT INTO employees (id, name, department) VALUES (?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
// Set the values for the parameters
preparedStatement.setInt(1, 3);
preparedStatement.setString(2, "Mark Johnson");
preparedStatement.setString(3, "Engineering");
// Execute the query
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows inserted: " + rowsAffected);
// Close the statement and connection
preparedStatement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use a
PreparedStatementto safely insert data into theemployeestable. - The
setXXX()methods are used to set the values for the parameters in the query. - The
executeUpdate()method executes the INSERT query and returns the number of rows affected.
Update Query
The UPDATE query is used to modify existing data in a table.
import java.sql.*;
public class UpdateQueryExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create the UPDATE query
String updateQuery = "UPDATE employees SET department = ? WHERE id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
// Set the values for the parameters
preparedStatement.setString(1, "Sales");
preparedStatement.setInt(2, 2);
// Execute the query
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows updated: " + rowsAffected);
// Close the statement and connection
preparedStatement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use a
PreparedStatementto update the department of the employee with ID 2. - The
executeUpdate()method executes the UPDATE query and returns the number of rows affected.
Delete Query
The DELETE query is used to remove data from a table.
import java.sql.*;
public class DeleteQueryExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create the DELETE query
String deleteQuery = "DELETE FROM employees WHERE id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery);
// Set the value for the parameter
preparedStatement.setInt(1, 1);
// Execute the query
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows deleted: " + rowsAffected);
// Close the statement and connection
preparedStatement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use a
PreparedStatementto delete the employee with ID 1. - The
executeUpdate()method executes the DELETE query and returns the number of rows affected.
Select Query
The SELECT query is used to retrieve data from a table.
import java.sql.*;
public class SelectQueryExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create the SELECT query
String selectQuery = "SELECT * FROM employees";
Statement statement = connection.createStatement();
// Execute the query and get the result
ResultSet resultSet = statement.executeQuery(selectQuery);
// Process the result
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String department = resultSet.getString("department");
System.out.println("ID: " + id + ", Name: " + name + ", Department: " + department);
}
// Close the result set, statement, and connection
resultSet.close();
statement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use a
Statementto execute the SELECT query. - The
ResultSetis used to retrieve and process the result set returned by the SELECT query.
Conclusion
In this tutorial, we covered how to execute the basic SQL queries (INSERT, UPDATE, DELETE, and SELECT) in Java using JDBC. These operations are fundamental for interacting with relational databases and are widely used in database-driven applications.
Always use PreparedStatement for executing queries with user input to avoid SQL injection vulnerabilities and improve performance.