So far we have been testing stored procedures directly in the SQL client. But in real projects, your Java test automation framework calls stored procedures through JDBC. You need to know how to use CallableStatement to call procedures, pass parameters, and read results — because this is how your Selenium or TestNG test setup scripts interact with the database.
import java.sql.*;
public class DatabaseHelper {
private static final String URL = "jdbc:mysql://localhost:3306/testdb";
private static final String USER = "root";
private static final String PASSWORD = "password";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
}import java.sql.*;
import org.testng.Assert;
import org.testng.annotations.Test;
public class TestStoredProcedure {
@Test
public void testGetCustomerById() throws SQLException {
try (Connection conn = DatabaseHelper.getConnection();
CallableStatement stmt = conn.prepareCall("{CALL get_customer_by_id(?)}")) {
stmt.setInt(1, 101); // IN parameter
ResultSet rs = stmt.executeQuery();
Assert.assertTrue(rs.next(), "Customer should exist");
Assert.assertEquals(rs.getString("full_name"), "Rajesh Verma");
Assert.assertEquals(rs.getString("email"), "rajesh@example.com");
}
}
}import java.sql.*;
import org.testng.Assert;
import org.testng.annotations.Test;
public class TestOutParams {
@Test
public void testGetAccountBalance() throws SQLException {
try (Connection conn = DatabaseHelper.getConnection();
CallableStatement stmt = conn.prepareCall(
"{CALL get_account_balance(?, ?, ?)}")) {
// Set IN parameter
stmt.setString(1, "ACC001");
// Register OUT parameters
stmt.registerOutParameter(2, Types.DECIMAL); // balance
stmt.registerOutParameter(3, Types.VARCHAR); // status
stmt.execute();
// Read OUT values
double balance = stmt.getDouble(2);
String status = stmt.getString(3);
Assert.assertEquals(balance, 50000.00, 0.01);
Assert.assertEquals(status, "ACTIVE");
}
}
}Always use registerOutParameter() before calling execute(). If you forget this step, you will get a confusing "Parameter index out of range" or silent NULL values. It is the most common JDBC mistake with stored procedures.
import java.sql.*;
import org.testng.Assert;
import org.testng.annotations.Test;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.AfterMethod;
public class TestFundTransfer {
private Connection conn;
@BeforeMethod
public void setup() throws SQLException {
conn = DatabaseHelper.getConnection();
conn.setAutoCommit(false); // Will rollback after each test
// Setup known test data
try (Statement stmt = conn.createStatement()) {
stmt.execute("DELETE FROM transaction_log WHERE from_account LIKE 'TEST%'");
stmt.execute("DELETE FROM accounts WHERE account_id LIKE 'TEST%'");
stmt.execute(
"INSERT INTO accounts VALUES " +
"('TEST001', 'Amit', 10000.00, 'ACTIVE', NOW()), " +
"('TEST002', 'Priya', 5000.00, 'ACTIVE', NOW())");
}
conn.commit();
}
@Test
public void testValidTransfer() throws SQLException {
try (CallableStatement stmt = conn.prepareCall(
"{CALL transfer_funds(?, ?, ?, ?, ?)}")) {
stmt.setString(1, "TEST001");
stmt.setString(2, "TEST002");
stmt.setBigDecimal(3, new java.math.BigDecimal("2000.00"));
stmt.registerOutParameter(4, Types.INTEGER); // txn_id
stmt.registerOutParameter(5, Types.VARCHAR); // result
stmt.execute();
Assert.assertEquals(stmt.getString(5), "SUCCESS");
Assert.assertTrue(stmt.getInt(4) > 0, "Should have a transaction ID");
}
// Verify balances in DB
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT balance FROM accounts WHERE account_id = 'TEST001'")) {
rs.next();
Assert.assertEquals(rs.getDouble("balance"), 8000.00, 0.01);
}
}
@Test(expectedExceptions = SQLException.class)
public void testInsufficientFunds() throws SQLException {
try (CallableStatement stmt = conn.prepareCall(
"{CALL transfer_funds(?, ?, ?, ?, ?)}")) {
stmt.setString(1, "TEST001");
stmt.setString(2, "TEST002");
stmt.setBigDecimal(3, new java.math.BigDecimal("999999.00"));
stmt.registerOutParameter(4, Types.INTEGER);
stmt.registerOutParameter(5, Types.VARCHAR);
stmt.execute(); // Should throw SQLException
}
}
@AfterMethod
public void teardown() throws SQLException {
if (conn != null) {
conn.rollback();
conn.close();
}
}
}Use conn.setAutoCommit(false) and conn.rollback() in your teardown. This way, every test starts with clean data. No test pollution. No leftover test records. This is the standard pattern for database test automation.
| Scenario | JDBC Syntax |
|---|---|
| No parameters | {CALL procedure_name()} |
| IN parameters only | {CALL procedure_name(?, ?, ?)} |
| OUT parameters | stmt.registerOutParameter(index, Types.TYPE) |
| Read result set | ResultSet rs = stmt.executeQuery() |
| Read OUT value | stmt.getString(index) or stmt.getInt(index) |
| Function call | {? = CALL function_name(?)} |
Q: How do you call a stored procedure from Java using JDBC?
A: I use CallableStatement with the syntax conn.prepareCall("{CALL procedure_name(?, ?)}"). For IN parameters, I use setString(), setInt(), or setBigDecimal(). For OUT parameters, I first call registerOutParameter(index, Types.TYPE), then execute(), and read values with getString(index) or getInt(index). For procedures that return a result set, I use executeQuery() and iterate the ResultSet. In my test framework, I wrap this in a TestNG setup with setAutoCommit(false) and rollback() in teardown, so every test gets clean data. I also use try-with-resources to prevent connection leaks.
Key Point: Use CallableStatement to call stored procedures from Java. Register OUT parameters before execute(). Use setAutoCommit(false) and rollback() in test teardown for clean test data.