Advanced Java
ResultSetMetaData Interface
1. The metadata means data about data i.e. we can get further information from
the data.
2. If you have to get metadata of a table like total number of column, column
name, column type etc. , ResultSetMetaData interface is useful because it
provides methods to get metadata from the ResultSet object.
Commonly used methods of ResultSetMetaData interface
Method Description
public int getColumnCount()throws It returns the total number of columns
SQLException in the ResultSet object.
public String getColumnName(int index)throws It returns the column name of the
SQLException specified column index.
public String getColumnTypeName(int It returns the column type name for the
index)throws SQLException specified index.
public String getTableName(int index)throws It returns the table name for the
SQLException specified column index.
CallableStatement INTERFACE
1. To call the stored procedures and functions, CallableStatement interface is
used.
2. We can have business logic on the database by the use of stored procedures
and functions that will make the performance better because these are
precompiled.
3. Suppose you need the get the age of the employee based on the date of birth,
you may create a function that receives date as the input and returns age of the
employee as the output.
Some of the commonly used methods of this interface are
Method Description
public void setInt(int paramIndex, int value) sets the integer value to the given
parameter index.
public void setString(int paramIndex, String sets the String value to the given parameter
value) index.
public void setFloat(int paramIndex, float sets the float value to the given parameter
value) index.
public void setDouble(int paramIndex, double sets the double value to the given
value) parameter index.
Contact: enquiry@vertexitservices.com Call:9763 9763 33| 1
www.vertexitservices.com
Compiled By Dr. Geeta Mete
Advanced Java
public int execute() executes the query.
Difference between Statement and PreparedStatement
Statement PreparedStatement
1 Statement is used for executing a PreparedStatement is used for executing a
static SQL statement. precompiled SQL statement.
2 Statement cannot accept parameters PreparedStatement can be executed
at runtime. repeatedly, it can accept different parameters
at runtime.
3 Statement is slower as compared to PreparedStatement is faster because it is used
PreparedStatement. for executing precompiled SQL statement
4 No such protocol. 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.
5 Statement is suitable for executing PreparedStatement is suitable for executing
DDL commands - create, drop, alter DML commands - select, insert, update and
and truncate. delete.
6 Statement can’t be used for storing PreparedStatement can be used for storing
image and file in database (i.e. using image and file in database (i.e. using BLOB,
BLOB, CLOB datatypes) CLOB datatypes)
7 Statement does not have setArray PreparedStatement can be used for setting
method. java.sql.Array using setArray method.
8 Statement enforces SQL injection, PreparedStatement prevents SQL injection,
because we end up using query because text for all the parameter values is
formed using concatenated SQL escaped.
strings.
Example >
Example >
prepStmt = con.prepareStatement("DELETE
stmt = con.createStatement(); from EMPLOYEE where ID=? ");
stmt.executeUpdate("DELETE from prepStmt.setInt(1, 8);
EMPLOYEE where ID=2 ");
Contact: enquiry@vertexitservices.com Call:9763 9763 33| 2
www.vertexitservices.com
Compiled By Dr. Geeta Mete
Advanced Java
Here comes one very important question, are
PreparedStatement vulnerable to SQL
injections?
YES, when we use concatenated SQL strings
rather than using input as a parameter for
preparedStatement
9 Statement does not provide PreparedStatement extends Statement and
addBatch() method, it provides only inherits all methods from Statement and
addBatch( String sql ) method. additionally adds addBatch() method.
Hence, same SQL query can’t be addBatch() method - adds a set of parameters
executed repeatedly in Statement . to the PreparedStatement object's batch of
commands.
Hence, same SQL query can be executed
repeatedly in PreparedStatement.
10 Statement makes code less readable PreparedStatement makes code more readable
and understandable - We may need and understandable - We need not to write
to write concatenated SQL strings concatenated SQL strings, we can use queries
and pass different parameters at runtime using
setter methods.
11 Statement does not provide such PreparedStatement provides methods like
methods. getMetadata() and getParameterMetadata()
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.
What is the difference between execute, executeQuery, executeUpdate?
1. Statement execute(String query) is used to execute any SQL query and it returns
TRUE if the result is an ResultSet such as running Select queries. The output is FALSE
when there is no ResultSet object such as running Insert or Update queries. We can
Contact: enquiry@vertexitservices.com Call:9763 9763 33| 3
www.vertexitservices.com
Compiled By Dr. Geeta Mete
Advanced Java
use getResultSet() to get the ResultSet and getUpdateCount()method to retrieve the
update count.
2. Statement executeQuery(String query) is used to execute Select queries and returns
the ResultSet. ResultSet returned is never null even if there are no records matching
the query. When executing select queries we should use executeQuery method so
that if someone tries to execute insert/update statement it will throw
java.sql.SQLException with message “executeQuery method can not be used for
update”.
3. Statement executeUpdate(String query) is used to execute Insert/Update/Delete
(DML) statements or DDL statements that returns nothing. The output is int and
equals to the row count for SQL Data Manipulation Language (DML) statements. For
DDL statements, the output is 0.
4. You should use execute() method only when you are not sure about the type of
statement else use executeQuery or executeUpdate method.
Difference between PreparedStatement and CallableStatement
S.No PreparedStatement CallableStatement
1. It extends the Statement Interface It extends the
PreparedStatement interface
2. It is used for executing dynamic SQL queries It is used for executing stored
procedures and functions
3. PrepraredStatement Objects are precompiled CallableStatement objects are
permanently precompiled
4. It gives high performance than Statement It gives higher performance
than PreparedStatement
5. It uses only one mode of parameter i.e. IN It uses three modes of
parameters like IN , OUT ,
INOUT
Contact: enquiry@vertexitservices.com Call:9763 9763 33| 4
www.vertexitservices.com
Compiled By Dr. Geeta Mete
