JDBC Statement Types Comparison:
In JDBC (Java Database Connectivity) there are 3-types of statements that we can use to interact with the DBMS (Database Management System). Go over very simple steps to connect to Oracle DB and Execute Query.
These statements are:
- Statement
- PreparedStatement
- CallableStatement
• Statement
Represents the base statements interface. In terms of efficiency, it is suitable to use Statement only when we know that we will not need to execute the SQL query multiple times. In contrast to PreparedStatement the Statement doesn’t offer support for the parameterized SQL queries, which is an important protection from SQL injection attacks.
With that said, Statement would be suitable for the execution of the DDL (Data Definition Language) statements, such as CREATE, ALTER, DROP.
- It is a basic statement that can be used to execute a static SQL query.
- The query is sent to the database each time it is executed, making it less efficient than PreparedStatement.
- No parameterization is possible in this statement.
Other must read
: MySQL DB connection and execute query tutorial
For example:
Statement stmt = con.createStatement(); stmt.executeUpdate("DROP TABLE PRODUCTS IF EXISTS");
• PreparedStatment
Extends the Statement interface. In most cases it is more efficient (in the context of multiple executions) to use the PreparedStatement because the SQL statement that is sent gets pre-compiled (i.e. a query plan is prepared) in the DBMS. Furthermore, we can use PreparedStatement to safely provide values to the SQL parameters, through a range of setter methods (i.e. setInt(int,int), setString(int,String), etc.).
- It is precompiled SQL statement that can be executed multiple times with different parameters.
- The query is parsed and optimized by the database when it is created, leading to faster execution times.
- Supports parameterization, allowing dynamic values to be passed to the query.
With that said, here’s an example of a PreparedStatement:
PreparedStatement pstmt = con.prepareStatement("UPDATE PRODUCTS SET PRICE = ? WHERE ID = ?"); pstmt.setFloat(1, 546.00f); pstmt.setInt(2, 7889);
• CallableStatement
extends the PreparedStatement interface. This interface is used for executing the SQL stored procedures. One particular advantage of using CallableStatement is that it adds a level of abstraction, so the execution of stored procedures does not have to be DBMS-specific.
However, it should be noted that the output parameters need to be explicitly defined through the corresponding registerOutParameter() methods; whereas the input parameters are provided in the same manner as with the PreparedStatement.
- It is used to call stored procedures in a database.
- Can also return values from a stored procedure, unlike a standard Statement.
- Similar to a PreparedStatement, it is precompiled and optimized for faster execution.
With that said, here’s the example of how the CallableStatement interacts with a MySQL Stored Procedure that does basic multiplication of integers:
CREATE PROCEDURE MULTIPLY(OUT RESULT INT, IN a INT, IN b INT) SET RESULT = a * b; CallableStatement cstmt = con.prepareCall("{call MULTIPLY(?, ?, ?)}"); cstmt.registerOutParameter(1, Types.INTEGER); cstmt.setInt(2, 4); cstmt.setInt(3, 8); cstmt.execute(); int result = cstmt.getInt(1);
In general, Statement is simple and basic but not very efficient, PreparedStatement is optimized for performance and supports parameterization, and CallableStatement is used to call stored procedures.
Let me know if you have any question regarding this.