jdbc statement Vs preparedstatement Vs callablestatement

A connection to the database can be established using below code.

Class.forName("driver");

Connection con = DriverManager.getConnection();

JDBC provides statement, preparedStatement and CallableStatement intefaces which define the methods and properties to execute the SQL queries in the database and receive the data from the database.

Statement:

Statement is used to execute static queries in the databases. It can not take the parameters at run time.

creating a statement is as below using the above connection object.

Statement stmt = con.createStatement();

The SQL query can be executed using the below three methods.

Boolean result = stmt.execute("select * from FIRST_TABLE"); // returns true if resultset can be retrieved

stmt.executeUpdate("update FIRST_TABLE set JOB_CODE=1"); // returns the number of rows affected by the update.

stmt.executeQuery("select * from FIRST_TABLE"); // it returns the result set object which can be iterated to collect the data.

PreparedStatement:

preparedstatement extends statement with added advantage of taking theĀ argumentsĀ at run time.

preparedStatement pstmt =con.prepareStatement("update FIRST_TABLE set job_code = ? where name = ? ");

The setXXX(); method can be used to bind input parameters to the query. Here XXX refer to the data type such as int, float, boolean etc.

The methods defined in statement would work for preparedStatement too.

pstmt.setInt(1,2); pstmt.setString(2,"JOHN");

CallableStatement:

Callable statements are used to execute stored procedures which were already compiled in the database.

CallableStatement cstmt = con.prepareCall("{call RT102(?,?)}");

cstmt.setInt(1,10); // passing the first param as 10

cstmt.registerOutParameter(2,java.sql.Types.VARCHAR); // to register the second parameter as output.

cstmt.execute();

String output = cstmt.getString(2);

===================================================

all the statements are to be closed after executing the query to save the database resources simialar to connection.

stmt.close();

JDBC statements are the best example for the usage of interfaces in java. These interfaces can be used without knowing the underlying implementation written for each driver.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working