In advanced Java, managing database interactions is a crucial task. The JDBC (Java Database Connectivity) API provides various classes that simplify interacting with databases. Three important classes in JDBC are PreparedStatement, CallableStatement, and ResultSet, which allow developers to execute SQL queries, call stored procedures, and handle results efficiently. In this article, we will explore these classes and demonstrate their use through examples.
The PreparedStatement is an extension of the Statement interface in JDBC. It is used to execute precompiled SQL queries with or without input parameters. Prepared statements help prevent SQL injection attacks and improve performance when executing the same query multiple times with different parameters.
import java.sql.*;
public class PreparedStatementExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/yourdatabase";
String user = "root";
String password = "password";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// Establish the connection
conn = DriverManager.getConnection(url, user, password);
// Create the SQL query
String sql = "SELECT * FROM employees WHERE salary > ?";
pstmt = conn.prepareStatement(sql);
// Set the parameter for salary
pstmt.setDouble(1, 50000.0);
// Execute the query
rs = pstmt.executeQuery();
// Process the result set
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
double salary = rs.getDouble("salary");
System.out.println(id + ", " + name + ", " + salary);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
In this example, we use PreparedStatement to execute a query that selects employees with a salary greater than a specified value. The query is precompiled, and the parameter is set using setDouble() to insert the salary value safely into the SQL query.
The CallableStatement interface is used to execute SQL stored procedures. It allows you to call stored procedures that may have input and output parameters. This is especially useful for executing complex SQL queries or database logic encapsulated in stored procedures.
import java.sql.*;
public class CallableStatementExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/yourdatabase";
String user = "root";
String password = "password";
Connection conn = null;
CallableStatement cstmt = null;
try {
// Establish the connection
conn = DriverManager.getConnection(url, user, password);
// Create the SQL query to call a stored procedure
String sql = "{CALL getEmployeeById(?)}";
cstmt = conn.prepareCall(sql);
// Set the input parameter (employee ID)
cstmt.setInt(1, 1);
// Execute the stored procedure
ResultSet rs = cstmt.executeQuery();
// Process the result set
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
double salary = rs.getDouble("salary");
System.out.println(id + ", " + name + ", " + salary);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (cstmt != null) cstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
In this example, we use CallableStatement to call a stored procedure named getEmployeeById which retrieves an employee's details based on their ID. The input parameter (employee ID) is set using setInt() and passed to the stored procedure.
The ResultSet object is used to store the results of a query. It provides methods for retrieving data from the result set, such as getString(), getInt(), getDouble(), etc. The result set is a cursor that points to the current row of the results, and you can iterate through the rows using methods like next().
import java.sql.*;
public class ResultSetExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/yourdatabase";
String user = "root";
String password = "password";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// Establish the connection
conn = DriverManager.getConnection(url, user, password);
// Create the SQL statement
String sql = "SELECT * FROM employees";
stmt = conn.createStatement();
// Execute the query
rs = stmt.executeQuery(sql);
// Process the result set
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
double salary = rs.getDouble("salary");
System.out.println(id + ", " + name + ", " + salary);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
In this example, we use ResultSet to retrieve data from the employees table. We execute a simple SQL query, and then iterate over the result set using next() to print each employee's details.
Here’s a quick summary of the differences between PreparedStatement, CallableStatement, and ResultSet:
PreparedStatement is used for executing precompiled SQL queries with or without input parameters. It helps prevent SQL injection and is more efficient for repeated executions of the same query with different parameters.CallableStatement is used to execute stored procedures. It can handle both input and output parameters and is ideal for executing complex SQL logic that is stored within the database.ResultSet is used to store and navigate the results of a query. It allows retrieving data from the result set row by row and provides methods to access different types of data.In this article, we have explored the usage of PreparedStatement, CallableStatement, and ResultSet in advanced Java for database interaction. These JDBC components play a key role in executing SQL queries, calling stored procedures, and managing the result sets. Understanding how to use these classes effectively will allow you to write efficient, secure, and maintainable database code in Java applications.