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

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.