19.6. Executing SQL Statements

1. Statement Interface Used to execute static SQL statements. Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM students");

The Statement interface is fundamental in JDBC for executing static SQL queries. When you create a Statement object through the connection object, you can issue a SQL command, such as selecting data from a database. In this example, con.createStatement() creates a Statement object, allowing you to execute a query using stmt.executeQuery(). The query fetched all records from the 'students' table, and the results are returned in a ResultSet object.

Think of the Statement interface as a waiter at a restaurant. When you want to place an order (execute a SQL statement) to the chef (database), you give your order to the waiter (Statement object), who then communicates with the kitchen and returns your meal (ResultSet) when it's ready.

2. PreparedStatement Interface Used for executing parameterized queries – safer and faster. PreparedStatement pstmt = con.prepareStatement("SELECT * FROM students WHERE id = ?"); pstmt.setInt(1, 101); ResultSet rs = pstmt.executeQuery();

The PreparedStatement interface is an enhancement over Statement, primarily used for executing parameterized queries. This method allows you to define an SQL query with parameters defined by placeholders (?). When using pstmt.setInt(1, 101);, you are setting the first placeholder in the SQL query to 101. This approach improves performance since the database can cache the query plan, and enhances security by reducing the risk of SQL injection attacks - something that can occur when user inputs are directly embedded in SQL queries.

Consider the PreparedStatement interface like a customizable meal kit. Instead of choosing a fixed meal from the menu (static SQL query), you select a base meal (the query) and add your choice of ingredients (parameters) to it. This allows you to tailor your meal to your taste, while also ensuring it integrates well with what the chef is accustomed to preparing.

3. CallableStatement Interface Used for calling stored procedures. CallableStatement cstmt = con.prepareCall("{call getStudent(?)}"); cstmt.setInt(1, 101); ResultSet rs = cstmt.executeQuery();

The CallableStatement interface is designed specifically for executing stored procedures in the database, which are precompiled SQL statements stored within the database. This is advantageous for executing complex operations and improving performance. In the provided example, prepareCall() is used to prepare a call to the stored procedure getStudent with a parameter set to 101. The results may be returned in a ResultSet just like with the previous interfaces.

Imagine the CallableStatement as a customer service line at a bank. When you call the service number (call a stored procedure), you're speaking to a representative capable of retrieving complex information or performing various tasks with just your account number (parameter). They efficiently execute tasks that may involve multiple steps, akin to a stored procedure, to resolve your request.

--