JDBC- PreparedStatement example - Execute CREATE table query(DDL command) using PreparedStatement's executeUpdate method in java

In this tutorial we will learn how to Execute CREATE table query(DDL command) using PreparedStatement's executeUpdate method in java JDBC.

JDBC tutorial - What is java.sql.CallableStatement in java

What is PreparedStatement in JDBC (java database connectivity)?
  1. PreparedStatement is used for executing a precompiled SQL statement in java.

  1. PreparedStatement can be executed repeatedly, it can accept different parameters at runtime in java.

  1. PreparedStatement is faster as compared to java.sql.Statement because it is used for executing precompiled SQL statement in java jdbc.

  1. 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. 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. PreparedStatement can be used for setting java.sql.Array using setArray method.
While sending it to database the driver converts this java.sql.Array to an SQL ARRAY
  1. PreparedStatement prevents SQL injection, because text for all the parameter values is escaped.
Example in java jdbc >
prepStmt = con.prepareStatement("DELETE from EMPLOYEE where ID=? ");
prepStmt.setInt(1, 8);

QUESTION. Here comes one very important question, are PreparedStatement vulnerable to SQL injections?
ANSWER. YES, when we use concatenated SQL strings rather than using input as a parameter for preparedStatement

  1. 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. PreparedStatement provides methods like getMetadata() and getParameterMetadata()  in java jdbc.
    • 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.

  1. java.sql.Statement Important methods -

java.sql.PreparedStatement's executeUpdate method can be used for executing CREATE table queries

--Before executing java program execute these database scripts  >

--If table already exists then execute the DROP command >
drop table EMPLOYEE;

If table already exist and below program is executed then you will face error in creating table (ORA-00955: name is already used by an existing object)

Example/ Full Programs JDBC- Execute CREATE table query using PreparedStatement's executeUpdate method in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementCreateExample {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          try {
                 // registering Oracle driver class
                 // getting connection
                 con = DriverManager.getConnection(
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");            
                 prepStmt = con.prepareStatement("create table EMPLOYEE"
                                                     + "(id number(4), name varchar2(22))");
                 // execute CREATE table query
                 System.out.println("EMPLOYEE Table created");
          } catch (ClassNotFoundException e) {
          } catch (SQLException e) {
                 try {
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
Connection established successfully!
EMPLOYEE Table created

Must read : java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - SELECT, INSERT, UPDATE and DELETE in java

NOTE : PreparedStatement is suitable for executing DML commands - select, insert, update and delete. This article demonstrates that PreparedStatement can also be used to serve the purpose.
But, we must prefer Statement, as it is suitable for executing DDL commands - CREATE, drop, alter and truncate.

So, in this tutorial we learned how to Execute CREATE table query(DDL command) using PreparedStatement's executeUpdate method in java JDBC.


Oracle 11g and SQL Developer installation and setup on 32/64bit windows - explained step by step with screenshots

Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java

java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE

JDBC tutorial- Insert/Store/save FILE in database

JDBC program- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java

Difference between CLOB and CLOB data type in Oracle

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...
//start paginationJs //end paginationJs