Types of Statements in JDBC credit by https://i.ytimg.com/vi/3LIXxfHb1To/maxresdefault.jpg

Mastering 3 Types of Statements in JDBC: Unlocking Secure and Efficient Database Interactions

by

Types of Statements in JDBC

  • In JDBC (Java Database Connectivity), the types of statements in JDBC are utilized to execute SQL queries and commands on a database.
  • They permit developers to connected with the database to retrieve, update, or control information.
  • There are three types of statements in JDBC: Statement, PreparedStatement, and CallableStatement.
  • Each serves distinctive purposes and is suited for particular scenarios.
Types of Statements in JDBC
Types of Statements in JDBC

1.Statement

  • There is a Statement object in JDBC, which is the one that is used to execute the basic SQL queries like SELECT, INSERT, UPDATE, and DELETE.
  • It is appropriate to be used where the SQL query is not complicated and it does not repeat frequently.
Characteristics:
  • Suitable for dynamic SQL queries.
  • Ideal for executing one-off queries where the SQL doesn’t change.
  • Does not support parameterized queries (it executes the query as a raw string).
Example:

import java.sql.*;

public class StatementExample {
public static void main(String[] args) {
try {
// Establish a connection
Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/mydb”, “root”, “password”);

// Create a statement object
Statement stmt = conn.createStatement();

// Execute a SELECT query
String sql = “SELECT * FROM employees”;
ResultSet rs = stmt.executeQuery(sql);

// Process the result set
while (rs.next()) {
System.out.println(“ID: ” + rs.getInt(“id”) + “, Name: ” + rs.getString(“name”));
}

// Close resources
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Explanation: In this example, a Statement object is employed to run a SELECT query and retrieve the employee details from the database.

2.PreparedStatement

  • PreparedStatement is a Statement interface extension used to execute precompiled SQL queries.
  • Because it is precompiled on the database, it eliminates the overhead that would result from parsing the query more than once; thus, it is more effective than a Statement.
  • Additionally, this new method is the one using a parameterized query becomes possible, making it more secure (preventing SQL injection) and flexible.
Characteristics:
  • Supports parameterized queries (using placeholders like ?).
  • Precompile queries, which significantly enhance performance when they are executed several times.
  • Being more secure than Statement, it is efficient and the injection of the SQL is blocked.
Example:

import java.sql.*;

public class PreparedStatementExample {
public static void main(String[] args) {
try {
// Establish a connection
Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/mydb”, “root”, “password”);

// Create a prepared statement object with placeholders
String sql = “SELECT * FROM employees WHERE department = ?”;
PreparedStatement pstmt = conn.prepareStatement(sql);

// Set the parameter for the department
pstmt.setString(1, “HR”);

// Execute the query
ResultSet rs = pstmt.executeQuery();

// Process the result set
while (rs.next()) {
System.out.println(“ID: ” + rs.getInt(“id”) + “, Name: ” + rs.getString(“name”));
}

// Close resources
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Explanation:
  • This is a piece of code that displays the use of the PreparedStatement object in combination with a parameter (?) to find employees whose start date is the 1st of January, 2020, and who are working in the HR department.
  • The setString() method is used to set the value of the department.

3.CallableStatement

  • A CallableStatement is utilized to execute stored procedures within the database.
  • Stored methods are precompiled SQL explanations stored within the database, which can be executed numerous times with diverse parameters.
  • This type of statement is essential when working with complex logic stored within the database, such as triggers, functions, and stored procedures.
Characteristics:
  • Utilized to call stored procedures or functions.
  • Supports both input and output parameters.
  • More adaptable and effective for executing
Example:

import java.sql.*;

public class CallableStatementExample {
public static void main(String[] args) {
try {
// Establish a connection
Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/mydb”, “root”, “password”);

// Create a callable statement to execute a stored procedure
String sql = “{call getEmployeeDetails(?)}”; // Stored procedure with one parameter
CallableStatement cstmt = conn.prepareCall(sql);

// Set the input parameter
cstmt.setInt(1, 101);

// Execute the stored procedure
ResultSet rs = cstmt.executeQuery();

// Process the result set
while (rs.next()) {
System.out.println(“ID: ” + rs.getInt(“id”) + “, Name: ” + rs.getString(“name”));
}

// Close resources
rs.close();
cstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Explanation:

In this example, the CallableStatement is utilized to call a stored method getEmployeeDetails with an input parameter (id), and the result is displayed.

Leave a Comment