This example show you how to use the CallableStatement.wasNull()
method call to see if the last OUT
parameter has a value of SQL NULL
.
package org.kodejava.jdbc;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Types;
import java.sql.SQLException;
public class WasNullExample {
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
public static void main(String[] args) {
try (Connection connection =
DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
// Prepares a call to the sored procedure
String query = "call Get_Product_By_Price2(?, ?)";
CallableStatement cb = connection.prepareCall(query);
// Sets the input parameter
cb.setBigDecimal(1, new BigDecimal("50"));
// Registers the OUT parameter
cb.registerOutParameter(2, Types.VARCHAR);
// Executes the query
cb.executeQuery();
// Gets the OUT parameter value
cb.getString(2);
// Checks if the last OUT parameter has value of SQL NULL.
// This method should be called only after calling a
// getter method; otherwise, there is no value to use in
// determining whether it is null or not.
if (cb.wasNull()) {
System.out.println("Product has an SQL NULL value");
} else {
System.out.println("Product: " + cb.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Here is the stored procedure script we called in the example above.
DELIMITER ;;
DROP PROCEDURE IF EXISTS Get_Product_By_Price2;;
CREATE PROCEDURE Get_Product_By_Price2(
IN product_price DECIMAL(10, 2),
OUT product_name VARCHAR(50))
BEGIN
SELECT NAME
INTO product_name
FROM products
WHERE price = product_price;
END;;
DELIMITER ;
Maven dependencies
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.33</version>
</dependency>