In this tutorial we will learn about java.sql.PreparedStatement in java JDBC.
12 points about java.sql.PreparedStatement in java jdbc?
- java.sql.PreparedStatement is used for executing a precompiled SQL statement in java jdbc.
- java.sql.PreparedStatement can be executed repeatedly, it can accept different parameters at runtime in java jdbc.
- PreparedStatement is faster as compared to java.sql.Statement because it is used for executing precompiled SQL statement in java.
- java.sql.Prepared statements are executed through a non sql binary protocol.
In binary protocol communications to the server is faster because less data packets are transferred.
- PreparedStatement can be used for
- java.sql.PreparedStatement can be used for setting java.sql.Array using setArray method in java.
While sending it to database the driver converts this java.sql.Array to an SQL ARRAY
- java.sql.PreparedStatement prevents SQL injection, because text for all the parameter values is escaped in java.
Example in java jdbc>
prepStmt = con.prepareStatement("select * from EMPLOYEE where ID=? ");
prepStmt.setInt(1, 8);
QUESTION. Here comes one very important question, are PreparedStatement vulnerable to SQL injections in java jdbc?
ANSWER. YES, when we use concatenated SQL strings rather than using input as a parameter for preparedStatement in java.
- java.sql.PreparedStatement extends Statement and inherits all methods from Statement and additionally adds addBatch() method in java jdbc.
addBatch() method - adds a set of parameters to the PreparedStatement object's batch of commands.
Hence, same SQL query can be executed repeatedly in PreparedStatement.
- java.sql.PreparedStatement provides methods like getMetadata() and getParameterMetadata() in java.
- getMetadata() - Method retrieves ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when PreparedStatement object is executed.
- getParameterMetadata() - method retrieves the number, types and properties of PreparedStatement object's parameters.
- java.sql.PreparedStatement is an interface in java jdbc.
- java.sql.Statement Important methods -
- prepStmt.setInt(1, 8); //substitute first occurrence of ? with 8
- prepStmt.setString(2, “javaMadeSoEasy”); //substitute second occurrence of ? with “javaMadeSoEasy”
Storing File and Image in database methods -
Programs where java.sql.PreparedStatement is used in java jdbc >
java.sql.PreparedStatement - using executeUpdate and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE in java>
JDBC- PreparedStatement example - Execute CREATE table query(DDL command) using PreparedStatement's executeUpdate method in java
JDBC- PreparedStatement example - Execute SELECT query(DML command) using PreparedStatement's executeQuery method and ResultSet in java
JDBC- PreparedStatement example - Execute INSERT query(DML command) using PreparedStatement's executeUpdate method in java
JDBC- PreparedStatement example - Execute UPDATE query(DML command) using PreparedStatement's executeUpdate method in java
JDBC- PreparedStatement example - Execute DELETE query(DML command) using PreparedStatement's executeUpdate method in java
JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java
JDBC- Batch PreparedStatement example- Execute UPDATE query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java
JDBC- Batch PreparedStatement example- Execute DELETE query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java
JDBC Transactions - commit and rollback(TCL command) - using PreparedStatement's executeUpdate method, setting connection.setAutoCommit(false) in java
CLOB and BLOB data type - IMAGE and FILE - Storing in and retrieving out from database in java >
Difference between CLOB and CLOB data type in Oracle
JDBC- Insert/Store/save IMAGE in database by using PreparedStatement's setBinaryStream and executeUpdate methods, using BLOB data type - in java
JDBC- Retrieve IMAGE from database by using PreparedStatement's executeQuery, ResultSet's getBlob method - using BLOB data type - in java
JDBC- Insert/Store/save FILE in database by using PreparedStatement's executeUpdate and setCharacterStream methods, using CLOB data type - in java
JDBC- Retrieve FILE from database by using PreparedStatement's executeQuery and ResultSet's getClob method, using CLOB data type - in java
JDBC Transactions - commit and rollback(TCL command) - using PreparedStatement's executeUpdate method, setting connection.setAutoCommit(false) in java
JDBC Transactions - commit and rollback(TCL command) - using Statement's executeUpdate method, setting connection.setAutoCommit(false) in java
Differences in java jdbc >
12 Differences between Statement, PreparedStatement and CallableStatement in java
In this tutorial we learned about java.sql.PreparedStatement in java JDBC.
RELATED LINKS>
Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java