Course Module 4. Working with databases - Lecture: Calling SQL Server Functions

CallableStatement

JDBC has another interface for even more complex scenarios. It inherits from PreparedStatement and is called CallableStatement .

It is used to call (Call) stored procedures in the database. The peculiarity of such a call is that, in addition to the ResultSet result , parameters can also be passed to such a stored procedure.

What's new, you ask? PreparedStatement also has a ResultSet result and you can also pass parameters to it. Yes, that's right, but the peculiarity of stored procedures is that through parameters they can not only receive, but also return data.

The stored procedure is called with IN , OUT , and INOUT parameters . It returns one or more ResultSet objects . The Connection.prepareCall() method is used to create a CallableStatement object .

Here imagine that you have a stored procedure ADD which accepts parameters a, b and c. This procedure adds a and b and places the result of the addition in the variable c.

Let's write the code where we will try to call it:

// Connect to the server
Connection connection = DriverManager.getConnection("jdbc:as400://mySystem");

// Create a CallableStatement object. It does preprocessing
// calling a stored procedure. Question marks
// indicate where the input parameters should be substituted, and where the output ones
// The first two parameters are input,
// and the third one is a day off.
CallableStatement statement = connection.prepareCall("CALL MYLIBRARY.ADD (?, ?, ?)");

// Setting up input parameters. Passing 123 and 234 to the procedure
statement.setInt (1, 123);
statement.setInt (2, 234);

// Registering the output parameter type
statement.registerOutParameter (3, Types.INTEGER);

// Run stored procedure
statement.execute();

// Get the value of the output parameter
int sum = statement.getInt(3);

// Close CallableStatement and Connection
statement.close();
connection.close();

Work is almost like with PreparedStatement , only there is a nuance. Our ADD function returns the result of the addition in the third parameter. Only the CallableStatement object knows nothing about this. Therefore, we tell him this explicitly by calling the registerOutParameter() method :

registerOutParameter(parameter number, Parameter type)

After that, you can call the procedure through the execute() method and then read the data from the third parameter using the getInt() method .

Batching Requests

In real projects, a situation often arises when you need to make a lot of the same type of queries (the most common in this case is PreparedStatement ), for example, you need to insert several tens or hundreds of records.

If you execute each request separately, it will take a lot of time and reduce the performance of the application. To prevent this, you can use the batch insert mode. It lies in the fact that you accumulate some buffer with your requests, and then execute them immediately.

Here is a piece of code as an example:

PreparedStatement stmt = con.prepareStatement(
        	"INSERT INTO jc_contact (first_name, last_name, phone, email) VALUES (?, ?, ?, ?)");

for (int i = 0; i < 10; i++) {
	// Fill in the request parameters
	stmt.setString(1, "FirstName_" + i);
    stmt.setString(2, "LastNAme_" + i);
    stmt.setString(3, "phone_" + i);
    stmt.setString(4, "email_" + i);
	// The request is not executed, but fits into the buffer,
	// which is then executed immediately for all commands
	stmt.addBatch();
}
// Execute all requests at once
int[] results = stmt.executeBatch();

Instead of executing the query with the execute() method , we batch it with the addBatch() method .

And then, when there are several hundred requests, you can send them all at once to the server by calling the executeBatch() command .

Healthy. The executeBatch() method returns an array of integers — int[]. Each cell in this array contains a number that indicates the number of rows modified by the corresponding query. If request number 3 in batch changed 5 rows, then the 3rd cell of the array will contain the number 5.