JDBC CallableStatement

So far, every example has sent raw SQL from Java to the database. Sometimes the SQL logic itself lives inside the database, as a stored procedure. JDBC's CallableStatement is how you call one of those from Java.

What Is a Stored Procedure?

A stored procedure is a block of SQL logic that is written once, compiled, and stored inside the database itself, under a name. Instead of sending the full SQL text every time, your application just calls the procedure by name and passes it parameters — the database already knows what to do.

Calling a Procedure with CallableStatement

You get a CallableStatement from the Connection, using JDBC's escape syntax {call procedure_name(?)}. Input parameters are set exactly the same way as with PreparedStatement — setInt, setString, and so on.

try (
  CallableStatement cs = conn.prepareCall("{call get_user_by_id(?)}")
) {
  cs.setInt(1, 101);

  try (ResultSet rs = cs.executeQuery()) {
    if (rs.next()) {
      System.out.println(rs.getString("name"));
    }
  }
} catch (SQLException e) {
  System.err.println("Call failed: " + e.getMessage());
}

In this example, get_user_by_id returns its result the same way a normal query would — through a ResultSet. Some procedures instead return values through dedicated OUT parameters.

Reading OUT Parameters

When a stored procedure returns a value through an OUT parameter instead of a result set, you have to tell JDBC in advance what type to expect, with registerOutParameter(index, type). After calling execute(), you read the value back with the matching getter, such as getInt(index).

try (
  CallableStatement cs = conn.prepareCall("{call get_user_count(?)}")
) {
  // 1) Tell JDBC that parameter 1 is an OUT parameter, of type INTEGER
  cs.registerOutParameter(1, Types.INTEGER);

  // 2) Run the procedure
  cs.execute();

  // 3) Read the OUT parameter back
  int count = cs.getInt(1);
  System.out.println("Total users: " + count);
} catch (SQLException e) {
  System.err.println("Call failed: " + e.getMessage());
}

Stored Procedure or Plain SQL?

Most of the time, writing your SQL directly in Java (with PreparedStatement) is simpler to read, version, and debug — the query lives right next to the code that uses it. Stored procedures are worth reaching for when business logic genuinely needs to live in the database layer (shared across multiple applications, for example), or for performance-critical operations where keeping the logic close to the data avoids shipping large amounts of data back and forth. Neither approach is universally "correct" — it depends on the team and the system.