Hi
All,
Welcome
to Java-recent
In
this post we will discuss about different connection
statements used in JDBC.
There are primarily three types of connection statements:-
- Statement
- PreparedStatement
- CallableStatement
Lets discuss each one of them in detail
Statement
interface :- is used to query database from Java,it provides
different methods like executeQuery(), executeUpdate() etc.
- Resultset executeQuery(String) :- is used to execute SQL SELECT queries,returns ResultSet.
- Int executeUpdate(String) :- is used to execute SQL(INSERT,UPDATE,DELETE) queries,returns int value indicating number of rows affected
- boolean execute(String) :- returns true if a resultSet is retrieved successfully, generally used to execute DML statements
- Statement query gets compiled and executed every time when it is called
- It is used in case where there is no repetition of a query
Syntax
:-
statement.executeQuery("select
mail from COMPANY where name="+"'"+uName+"'");
PreparedStatement interface :-
- Prepared statement queries are precompiled and the fetch plan will be stored in cache,so for subsequent requests only execution will happen
- They are faster than Statement queries because Statement queries will get compiled every time
- Used in case there is repetition of a query
- Prepared statements can be parameterized,parameterization of query values is done by using '?' - place holder in setXXX() method
- Prepared Statement prevents SQL injectionFor detailed explanation on SQL injection with example refer SQL injection
Syntax
:-There are two ways of using prepared statement
1.PreparedStatement
preparedStatement = connection.prepareStatement("select
* from COMPANY where sid="+"'"+emailId+"'");
2.
PreparedStatement preparedStatement =
connection.prepareStatement("select
* from COMPANY where sid=? AND name=?");
preparedStatement.setInt(1,
101);
preparedStatement.setString(2,
"Google");
setXXX()
-- takes two parameters first one tells position of the value to be
placed,
this
starts from 1 .
Second
parameter is the respective value to be passed for ?-place holder
The
first type of declaration will not prevent SQL injections because we
are hard coding the where clause with a variable.
Second
type of declaration will prevent SQL injection because all the
parameters passed will be escaped by JDBC
CallableStatement :-
- is an interface used to execute stored procedures
- There are three types of parameters for a stored procedure
- IN :- used to provide input to stored procedure,this is set by using setXXX()
- OUT :- is used to hold values from a procedure,this is retrieved using getXXX()
- INOUT :- acts as both input and output parameters
Code
snippets :-
Procedure
create
or
replace
procedure "P_COMPANY"
(pname
IN
VARCHAR2, pemailid
OUT
VARCHAR2)
is
begin
select
mail into pemailid from COMPANY where
name=pname;
end;
Procedure
P_COMPANY takes two parameters and select query put the mail Id into
pemailid(OUT parameter)
//CallableStatement
usage
1
CallableStatement cs = connection.prepareCall("{call
P_COMPANY(?,?)}");
2 cs.setString(1,
name);
3 cs.registerOutParameter(2,
java.sql.Types.VARCHAR);
4 cs.execute();
5 String
mailId = cs.getString(2);
- Line 1 we are calling the the procedure P_COMPANY
- setting the IN parameter using setString(1, name);
- registering OUT parameter ,we have to mention sql data type also
- execute() or executeQuery() or executeUpdate() can be used to execute the procedure
- Line 5 getting the output from pemailid OUT variable
Complete
code
JDBCConnection.java
public
class JDBCConnection {
public
void
connectDB(String name) throws
ClassNotFoundException,
SQLException
{
Connection
connection = null;
;
CallableStatement
cs = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
//
Creating connection object
connection
= DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe",
"system",
"admin");
cs
= connection.prepareCall("{call
P_COMPANY(?,?)}");
cs.setString(1,
name);
cs.registerOutParameter(2,
java.sql.Types.VARCHAR);
cs.execute();
String
mailId = cs.getString(2);
System.out.println("Required
Mail id" + mailId);
}
finally
{
try
{
connection.close();
cs.close();
}
catch
(SQLException e) {
//
TODO
Auto-generated catch block
e.printStackTrace();
}
}
}
public
static
void
main(String[] args) {
JDBCConnection
jdbc = new
JDBCConnection();
try
{
jdbc.connectDB("Yahoo");
}
catch
(ClassNotFoundException e) {
//
TODO
Auto-generated catch block
e.printStackTrace();
}
catch
(SQLException e) {
//
TODO
Auto-generated catch block
e.printStackTrace();
}
}
}
Key
Points :-
- Statement is used for non-repeating queries
- Prepared Statement will be pre-compiled,on subsequent requests the query will get executes
- Action/Execution plan is stored in cache which helps in faster execution
- Prepared Statements will prevent SQL injections
- Prepared Statements allows parameterization of values by using place holder [?]
- Procedure can be executed using callable interface
- IN parameter is set using setXXX()
- OUT parameter is retrived by first registering the parameter and using getXXX()
Happy
Learning
Please
provide your valuable comments on this article and share it across
your network.
