Can CallableStatement be used in place of PreparedStatement?

Question

Need some help with a JDBC query. I would have tried it out myself, but right now I do not have access to a database.

My question is: Since CallableStatement extends PreparedStatement, does this mean we can use CallableStatement to execute any query prepared for prepared statement?

More specifically, any downsides to using Callable like this:

CallableStatement stmt = null;
String sql = "UPDATE Employees set age=30 WHERE id=";
      stmt = conn.prepareCall(sql);
      int empID = 102;
      stmt.setInt(1, empID); );
      stmt.execute();
stmt.close();
conn.close();

You might be able to, but you shouldn't. A CallableStatement is _"The interface used to execute SQL stored procedures."_. You are not calling a stored procedure, so you shouldn't use it. Now with most JDBC drivers it will probably work, but it could have behavior that is specifically intended for stored procedures, and might result in weird and hard to diagnose problems. — Mark Rotteveel, Mar 08 '15 at 11:58

score 1 · Accepted Answer · answered Mar 07 '15 at 13:59

Yes, you can. Difference of prepareCall and prepareStatement methods described in documentation. As you can see they a just optimized for different tasks.

package com.company;

import java.sql.*;

public class Main {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/mysql", "user", "password");

        CallableStatement callableStatement = connection.prepareCall("SELECT * FROM db WHERE user = ?");
        callableStatement.setString(1, "deployer");
        ResultSet resultSet = callableStatement.executeQuery();

        while(resultSet.next()) {
            System.out.println(resultSet.getString("Db"));
        }

        connection.close();
    }
}

score -1 · Answer 2 · answered Mar 07 '15 at 13:56

We use CallableStatement to call predefined procedures from database rather than to execute prepared statements.

PreparedStatement:

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
    pstmt.close();
}

CallableStatement:

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

I suggest you to read this article:

http://www.tutorialspoint.com/jdbc/jdbc-statements.htm

Can CallableStatement be used in place of PreparedStatement?

2 Answers2