JDBC Statements & CRUD Operations

JDBC provides different types of statements to execute SQL queries and perform CRUD operations (Create, Read, Update, Delete). Understanding these is essential for building real-world Java backend applications.

What are JDBC Statements?

JDBC statements are used to send SQL queries from a Java application to the database.

In simple words: They are tools that allow Java programs to execute SQL queries and interact with databases.

Example

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

? Using JDBC statements, you can insert, fetch, update, and delete data from the database.

Types of JDBC Statements

Java provides three main types of statements that are used to execute SQL queries in a database. Each type is used for different situations depending on security, performance, and requirements.

1. Statement (Static Queries)

Statement is used for executing simple SQL queries without parameters.

? It is mainly used when the query is fixed and does not take input from the user.

Key Features

Used for static SQL queries (no dynamic values)
Simple and easy to use
Executes query directly without pre-compilation
Not safe when user input is involved

Example

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM users");

while (rs.next()) {
    System.out.println(rs.getInt("id") + " " + rs.getString("name"));
}

Explanation

createStatement() → Creates a Statement object
executeQuery() → Executes SELECT query
ResultSet → Stores data returned from database
rs.next() → Moves to next row

Limitations

Vulnerable to SQL Injection attacks
Not efficient for repeated queries (no caching/pre-compilation)
Not suitable for dynamic queries with user input

2. PreparedStatement (Parameterized Queries)

PreparedStatement is used for executing SQL queries with parameters.

? It is mainly used when the query contains dynamic values (like user input).

Key Features

Supports parameter binding using ? placeholders
Prevents SQL Injection attacks
Better performance due to precompiled queries
Suitable for dynamic and repeated queries

Example

PreparedStatement ps = con.prepareStatement(
    "SELECT * FROM users WHERE id = ?"
);

ps.setInt(1, 1);

ResultSet rs = ps.executeQuery();

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

Explanation

prepareStatement() → Precompiles SQL query
? → Placeholder for dynamic value
setInt(1, 1) → Binds value to first parameter
executeQuery() → Executes the query
ResultSet → Stores returned data

Advantages

Secure (prevents SQL Injection)
Faster for repeated execution (query compiled once)
Cleaner and more readable code

3. CallableStatement (Stored Procedures)

CallableStatement is used to call stored procedures in the database.

? It is mainly used when business logic is written inside the database as procedures or functions.

Key Features

Calls database stored procedures and functions
Supports IN, OUT, and INOUT parameters
Used in enterprise and large-scale applications
Helps move business logic to the database layer

Example

CallableStatement cs = con.prepareCall("{call getUser(?)}");

cs.setInt(1, 1);

ResultSet rs = cs.executeQuery();

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

Explanation

prepareCall() → Prepares a stored procedure call
{call getUser(?)} → Calls procedure with parameter
setInt(1, 1) → Passes value to procedure
executeQuery() → Executes procedure
ResultSet → Stores returned data

Executing SQL in JDBC

JDBC provides different methods to execute SQL queries depending on the type of operation (SELECT, INSERT, UPDATE, DELETE).

executeQuery()

Used for executing SELECT queries that return data from the database.

ResultSet rs = stmt.executeQuery("SELECT * FROM users");

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

? Returns a ResultSet object that contains the data retrieved from the database.

executeUpdate()

Used for executing INSERT, UPDATE, and DELETE queries.

int rows = stmt.executeUpdate(
    "UPDATE users SET name='Amit' WHERE id=1"
);

System.out.println("Rows affected: " + rows);

? Returns the number of rows affected by the query.

execute()

Used when you don’t know the type of SQL query (can be SELECT or UPDATE).

boolean result = stmt.execute("SELECT * FROM users");

if (result) {
    ResultSet rs = stmt.getResultSet();
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
} else {
    int count = stmt.getUpdateCount();
    System.out.println("Rows affected: " + count);
}

? Returns:

true → if result is ResultSet (SELECT query)
false → if result is update count (INSERT/UPDATE/DELETE)

CRUD Operations in JDBC

CRUD stands for Create, Read, Update, Delete.

These are the basic operations used to manage data in a database.

1. CREATE (INSERT)

Used to insert new data into the database.

PreparedStatement ps = con.prepareStatement(
    "INSERT INTO users(name, age) VALUES (?, ?)"
);

ps.setString(1, "Amit");
ps.setInt(2, 25);

ps.executeUpdate();

? executeUpdate() inserts data and returns number of affected rows.

2. READ (SELECT)

Used to retrieve data from the database.

PreparedStatement ps = con.prepareStatement(
    "SELECT * FROM users"
);

ResultSet rs = ps.executeQuery();

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

? executeQuery() returns a ResultSet containing the data.

3. UPDATE

Used to modify existing data.

PreparedStatement ps = con.prepareStatement(
    "UPDATE users SET age=? WHERE name=?"
);

ps.setInt(1, 30);
ps.setString(2, "Amit");

ps.executeUpdate();

? Updates existing records based on condition.

4. DELETE

Used to remove data from the database.

PreparedStatement ps = con.prepareStatement(
    "DELETE FROM users WHERE name=?"
);

ps.setString(1, "Amit");

ps.executeUpdate();

? Deletes records that match the given condition.

Parameter Binding

Parameter binding is used to pass values safely into SQL queries.

? It uses ? placeholders in the query, and actual values are set using methods like setInt(), setString(), etc.

Example

PreparedStatement ps = con.prepareStatement(
    "SELECT * FROM users WHERE id = ?"
);

ps.setInt(1, 10);

ResultSet rs = ps.executeQuery();

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

Explanation

? → Placeholder for dynamic value
setInt(1, 10) → Binds value 10 to first parameter
PreparedStatement → Ensures values are handled safely
executeQuery() → Executes the query

Benefits

Prevents SQL Injection attacks
Improves performance (query is precompiled)
Makes code cleaner and easier to read

Statement vs PreparedStatement vs CallableStatement

Feature	Statement	PreparedStatement	CallableStatement
Query Type	Executes simple static SQL queries	Executes dynamic SQL queries with parameters	Calls stored procedures and functions
Parameters	Does not support parameters	Supports input parameters using `?`	Supports IN, OUT, and INOUT parameters
Security	Vulnerable to SQL Injection	Prevents SQL Injection using parameter binding	Secure when used properly
Performance	Slower (query compiled every time)	Faster (query precompiled and reused)	Moderate (depends on procedure execution)
Use Case	Simple and fixed queries	Most real-world applications	Enterprise-level database logic

Common Mistakes to Avoid

Using Statement for dynamic input (can lead to SQL Injection)
Not using parameter binding (PreparedStatement) for user input
Not closing resources like Connection, Statement, and ResultSet
Ignoring SQL exceptions (no proper error handling)
Hardcoding values directly in SQL queries

Real-World Use Cases

JDBC statements are widely used in real-world applications where database operations are required.

Login and authentication systems (user validation and access control).
E-commerce order processing (products, orders, payments).
Banking transactions (account updates, transfers, records).
REST APIs using Spring Boot (backend database operations).
Data management systems (storing, updating, and retrieving data).

Conclusion

JDBC statements are the core of database operations in Java and are used to execute all types of SQL queries.

Statement → Used for simple and static queries.
PreparedStatement → Secure, efficient, and best for dynamic queries.
CallableStatement → Used for advanced database logic like stored procedures.