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)?
- PreparedStatement is used for executing a precompiled SQL statement in java.
- PreparedStatement can be executed repeatedly, it can accept different parameters at runtime in java.
- PreparedStatement is faster as compared to java.sql.Statement because it is used for executing precompiled SQL statement in java jdbc.
- 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
- 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
- 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
- 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.
- 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.
- java.sql.PreparedStatement is an interface.
- java.sql.Statement Important methods -
executeUpdate
executeQuery
addBatch()
executeBatch()
Storing File and Image in database related methods -
java.sql.PreparedStatement's executeUpdate method can be used for executing CREATE table queries
--Before executing java program execute these database scripts >
None
--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
Class.forName("oracle.jdbc.driver.OracleDriver");
// getting connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"ankit", "Oracle123");
System.out.println("Connection established successfully!");
prepStmt = con.prepareStatement("create table EMPLOYEE"
+ "(id number(4), name varchar2(22))");
// execute CREATE table query
prepStmt.executeUpdate();
System.out.println("EMPLOYEE Table created");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
if(con!=null) con.close(); // close connection
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/*OUTPUT
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.
RELATED LINKS>
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