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 0 · 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 0 · Answer 2 · answered Jun 26 '23 at 06:55

For a scenario using Sybase for a some non-trivial SQL (~30 lines using temp tables and while loops) we were having an issue where the exact same prepared statement was being intermittently executed with dynamic prepare and failing, the other times dynamic prepare wasn't being used and then it would work successfully.

Checking the below page suggests to either set the Sybase connection property of DYNAMIC_PREPARE to false or use the prepareCall method to ensure dynamic prepare wasn't used.

Sybase jConnect Performance Tuning

score -1 · Answer 3 · 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?

3 Answers3