JDBC - What is java.sql.PreparedStatement in java
In this tutorial we will learn about java.sql.PreparedStatement  in java JDBC.
12 points about java.sql.PreparedStatement  in java jdbc?


  1. java.sql.PreparedStatement is used for executing a precompiled SQL statement in java jdbc.


  1. java.sql.PreparedStatement can be executed repeatedly, it can accept different parameters at runtime in java jdbc.
  1. PreparedStatement is faster as compared to java.sql.Statement because it is used for executing precompiled SQL statement in java.
  1. 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.
  1. java.sql.PreparedStatement is suitable for executing DML commands -  SELECT, INSERT, UPDATE and DELETE
  1. PreparedStatement can be used for  
storing/retrieving image and
Storing /retrieving file in database
(i.e. by using BLOB, CLOB datatypes)

  1. 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
  1. 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.


  1. 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.
  1. 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.
  2. java.sql.PreparedStatement is an interface in java jdbc.

  1. 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”


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





PreparedStatement BATCH - using executeUpdate methods - INSERT, UPDATE and DELETE  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)  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




IMAGE - Storing in and retrieving out from database



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





FILE - Storing in and retrieving out from database


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)  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

java.sql.Statement - using executeUpdate  and executeQuery methods CREATE, SELECT,INSERT, UPDATE, DELETE
Labels: Core Java JDBC
eEdit
Must read for you :