Demonstrate the use of the JDBC to call stored procedures from a Microsoft SQL Server database and return data to a Java-based console application.

Update 07/10/2015: All source code now on GitHub
Update 09/08/2020: Major source code update on GitHub: MS SQL 2017, JDBC 8.4, Java 11, Gradle

Introduction

Enterprise software solutions often combine multiple technology platforms. Accessing an Oracle database via a Microsoft .NET application, or accessing Microsoft’s SQL Server from a Java-based application is common practice. In an earlier post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, I discussed the use of the Microsoft JDBC Driver 4.0 for SQL Server to connect a Java-based RESTful web service application to a Microsoft SQL Server database. In this post, I will demonstrate the use of JDBC (Java Database Connectivity) to call stored procedures from a Microsoft SQL Server 2008 R2 database and return data to a Java-based console application. The objectives of this post include:

• Demonstrate the differences between using static SQL statements and stored procedures to return data
• Demonstrate the use of three types of JDBC statements to return data: Statement, PreparedStatement, and CallableStatement
• Demonstrate how to call stored procedures with input and output parameters
• Demonstrate how to return single values and a result set from a database using stored procedures

Why Stored Procedure?

To access data, many enterprise software organizations require their developers to call stored procedures within their code as opposed to executing static SQL statements against the database. There are several common reasons stored procedures are preferred:

• Optimization – Stored procedures are often written by DBAs or developers who specialize in database development. They understand the best way to construct queries for optimal performance and minimal load on the database server. Think of it as a developer using an API to interact with the database.
• Safety and Security – Stored procedures are considered safer and more secure than static SQL statements. The stored procedure provides tight control over the content of the queries, preventing malicious or unintentionally destructive code from being executed against the database.
• Error Handling – Stored procedures can contain logic for handling errors before they bubble up to the application layer and possibly to the end-user.

Adventure Works 2008 Database

For brevity, I will use an existing SQL Server instance and database I’ve already created for recent series of blog posts. The first post in that series, Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3, covers the creation of a SQL Server 2008 R2 instance and installation of Microsoft’s Adventure Works database. The database comes prepopulated with plenty of data for demonstration purposes. In addition to the SQL Server instance and database, I will also use the ‘aw_dev’ user account created in that post. However, for this post, we will need to add additional permission to call or ‘Execute’ the database queries.

Lastly, I have added four stored procedures to the Adventure Works database to use in this post. In order to follow along, you will need to install these stored procedures. The SQL install scripts are included in the downloadable code. See the SQL statements below.

Data Sources, Connections, and Properties

After adding the Microsoft JDBC Driver 4.0 for SQL Server to the project, we create a SQL Server data source (com.microsoft.sqlserver.jdbc.SQLServerDataSource) and database connection (java.sql.Connection). There are several patterns for creating and working with data sources and connections. This post does not necessarily focus on the best practices for creating or using either. In this example, the application instantiates a connection class (SqlConnection.java), which in turn instantiates the java.sql.Connection and com.microsoft.sqlserver.jdbc.SQLServerDataSource objects. The data source’s properties are supplied from an instance of a singleton class (ProjectProperties.java). This properties class instantiates the java.util.Properties class and reads values from an XML property file (properties.xml). On startup, the application creates the database connection, calls each of the example methods, and then closes the connection.

Examples

For each example, I will show the stored procedure, if applicable, followed by the Java method that calls the procedure or executes the static SQL statement. I have left out all the data source and connection code in the article. Again, a complete copy of all the code for this article is available on GitHub. The GitHub project contains the complete NetBeans project, including some basic JUnit tests, in a zipped package. A second zipped package contains the SQL scripts necessary to create the four stored procedures used in the examples.

Example 1: A Simple Statement

Before jumping into stored procedures, we’ll start with a simple static SQL statement. This example’s method uses the java.sql.Statement class. According to the Oracle documentation, the Statement object is used for executing a static SQL statement and returning the results it produces. This SQL statement calculates the average weight of all products in the Adventure Works database that contain a weight. It returns a solitary double value. This example demonstrates one of the simplest methods for returning data from SQL Server.

    /**
     * Statement example, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightST() {
        double averageWeight = 0;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT ROUND(AVG([Weight]), 2)";
            sql += " FROM Production.Product";
            sql += " WHERE ([Weight] > 0)";
            sql += " AND (WeightUnitMeasureCode = 'LB')";
            stmt = connection.getConnection().createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                averageWeight = rs.getDouble(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 2: The Prepared Statement

Next, we’ll execute almost the same static SQL statement as Example 1. The only change is the addition of  ‘AS averageWeight’. This allows us to parse the results by column name, making the code easier to understand as opposed to using the numeric index of the column as in Example 1. Instead of using the java.sql.Statement class, we use the java.sql.PreparedStatement class. According to Oracle’s documentation, a SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

    /**
     * PreparedStatement example, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightPS() {
        double averageWeight = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT ROUND(AVG([Weight]), 2) AS averageWeight FROM";
            sql += " Production.Product";
            sql += " WHERE ([Weight] > 0)";
            sql += " AND (WeightUnitMeasureCode = 'LB')";
            pstmt = connection.getConnection().prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                averageWeight = rs.getDouble("averageWeight");
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 3: The Callable Statement

In this example, the average product weight query has been moved into a stored procedure. The procedure is identical in functionality to the static statement in the first two examples. To call the stored procedure, we use the java.sql.CallableStatement class. Again according to Oracle’s documentation, the CallableStatement extends PreparedStatement. It is the interface used to execute SQL stored procedures. The CallableStatement accepts both input and output parameters, however, this simple example does not use either. Like the previous two examples, the procedure returns a single double number value.

CREATE PROCEDURE [dbo].[uspGetAverageProductWeight]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ROUND(AVG([Weight]), 2)
    FROM Production.Product
    WHERE ([Weight] > 0) AND (WeightUnitMeasureCode = 'LB')
END
GO

    /**
     * CallableStatement, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightCS() {
        CallableStatement cstmt = null;
        double averageWeight = 0;
        ResultSet rs = null;
        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call uspGetAverageProductWeight}");
            cstmt.execute();
            rs = cstmt.getResultSet();
            if (rs.next()) {
                averageWeight = rs.getDouble(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 4: Calling a Stored Procedure with an Output Parameter

In this example, we use almost the same stored procedure as in Example 3. The only difference is the inclusion of an output parameter. This time, instead of returning a result set with a value in a single unnamed column, the column has a name – ‘averageWeight’. We can now call that column by name when retrieving the value.

The stored procedure patterns found in Examples 3 and 4 are both commonly used. One procedure uses an output parameter and one not, both return the same value(s). You can use the CallableStatement to for either type.

CREATE PROCEDURE [dbo].[uspGetAverageProductWeightOUT]
    @averageWeight DECIMAL(8,2) OUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @averageWeight = ROUND(AVG([Weight]), 2)
    FROM Production.Product
    WHERE ([Weight] > 0) AND (WeightUnitMeasureCode = 'LB')
END
GO

    /**
     * CallableStatement example, (1) output parameter, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightOutCS() {
        CallableStatement cstmt = null;
        double averageWeight = 0;

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call dbo.uspGetAverageProductWeightOUT(?)}");
            cstmt.registerOutParameter("averageWeight", java.sql.Types.DECIMAL);
            cstmt.execute();
            averageWeight = cstmt.getDouble("averageWeight");
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 5: Calling a Stored Procedure with an Input Parameter

Enough calculating the average weight of products. Let’s move onto another stored procedure. In this new example, the procedure returns a result set (java.sql.ResultSet) of employees whose last name starts with a particular sequence of character (e.g., ‘M’ or ‘Sta’).  The sequence of characters is passed as an input parameter to the stored procedure using the CallableStatement, again.

The method making the call iterates through the rows of the result set returned by the stored procedure, concatenating multiple columns to form the employee’s full name as a string. Each full name string is then added to an ordered collection of strings, a List<String> object. The List instance is returned by the method. You will notice this procedure takes a little longer to run because of the use of the ‘LIKE’ operator. The database server has to perform pattern matching on each last name value in the table to determine the result set.

CREATE PROCEDURE [HumanResources].[uspGetEmployeesByLastName]
    @lastNameStartsWith VARCHAR(20) = 'A'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Title, FirstName, MiddleName, LastName, Suffix
    FROM Person.Person
    WHERE (PersonType = 'EM') AND (LastName LIKE @lastNameStartsWith + '%')
END
GO

    /**
     * CallableStatement example, (1) input parameter, db returns ResultSet
     *
     * @param lastNameStartsWith
     * @return List<String> of employee names
     */
    public List<String> getEmployeesByLastNameCS(String lastNameStartsWith) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List<String> employeeFullName = new ArrayList<>();

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call HumanResources.uspGetEmployeesByLastName(?)}",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            cstmt.setString("lastNameStartsWith", lastNameStartsWith);
            boolean results = cstmt.execute();
            int rowsAffected = 0;

            // Protects against lack of SET NOCOUNT in stored prodedure
            while (results || rowsAffected != -1) {
                if (results) {
                    rs = cstmt.getResultSet();
                    break;
                } else {
                    rowsAffected = cstmt.getUpdateCount();
                }
                results = cstmt.getMoreResults();
            }
            while (rs.next()) {
                employeeFullName.add(
                        rs.getString("LastName") + ", "
                        + rs.getString("FirstName") + " "
                        + rs.getString("MiddleName"));
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return employeeFullName;
    }

Example 6: Converting a Result Set to Ordered Collection of Objects

In this last example, we pass two input parameters, product color and product size, to a slightly more complex stored procedure. The stored procedure returns a result set containing several columns of product information. This time, the example’s method iterates through the result set returned by the procedure and constructs an ordered collection of products, a List<Product> object. The Product objects in the list are instances of the Product.java POJO class. The method converts each column/row value (key/value pair) into a Product property (i.e. Product.Size, Product.Model). This is a common pattern for persisting data from a result set in the application. The  Product.java class is part of the project code you can download.

CREATE PROCEDURE [Production].[uspGetProductsByColorAndSize]
    @productColor VARCHAR(20),
    @productSize INTEGER
AS
BEGIN
    SET NOCOUNT ON;

    SELECT p.Name AS [Product], p.ProductNumber, p.Color, p.Size, m.Name AS [Model]
    FROM Production.ProductModel AS m INNER JOIN
        Production.Product AS p ON m.ProductModelID = p.ProductModelID
    WHERE (p.Color = @productColor) AND (p.Size = @productSize)
    ORDER BY [Model], [Product]
END
GO

    /**
     * CallableStatement example, (2) input parameters, db returns ResultSet
     *
     * @param color
     * @param size
     * @return List<Product> of Product objects
     */
    public List<Product> getProductsByColorAndSizeCS(String color, String size) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List<Product> productList = new ArrayList<>();

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call Production.uspGetProductsByColorAndSize(?, ?)}",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            cstmt.setString("productColor", color);
            cstmt.setString("productSize", size);
            boolean results = cstmt.execute();
            int rowsAffected = 0;

            // Protects against lack of SET NOCOUNT in stored prodedure
            while (results || rowsAffected != -1) {
                if (results) {
                    rs = cstmt.getResultSet();
                    break;
                } else {
                    rowsAffected = cstmt.getUpdateCount();
                }
                results = cstmt.getMoreResults();
            }

            while (rs.next()) {
                Product product = new Product(
                        rs.getString("Product"),
                        rs.getString("ProductNumber"),
                        rs.getString("Color"),
                        rs.getString("Size"),
                        rs.getString("Model"));
                productList.add(product);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return productList;
    }

Schema Reference

You’ll notice in Example 4, I refer to the dbo schema ({call dbo.uspGetAverageProductWeightOUT(?)}). In Examples 3, I did not refer to the dbo schema. Since this stored procedure is part of the dbo schema and the dbo schema is also the default schema for this database, I did not have to reference it. However, according to Microsoft, it is always good practice to refer to database objects by a schema name and the object name, separated by a period. That includes the default schema. You must always refer to the schema if it is not the default schema.

Running the Examples

The application has a main method that runs all six examples. It will display the name of the method being called, the duration of time it took to retrieve the data, and the results returned by the method.

package com.articles.examples;

import java.util.List;

/**
 * Main class that calls all example methods
 *
 * @author Gary A. Stafford
 */
public class RunExamples {

    private static Examples examples = new Examples();
    private static ProcessTimer timer = new ProcessTimer();

    /**
     * @param args the command line arguments
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        System.out.println("");
        System.out.println("SQL SERVER STATEMENT EXAMPLES");
        System.out.println("======================================");

        // Statement example, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        double averageWeight = examples.getAverageProductWeightST();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightST");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // PreparedStatement example, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightPS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightPS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightCS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement example, (1) output parameter, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightOutCS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightOutCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement example, (1) input parameter, db returns ResultSet
        timer.setStartTime(System.nanoTime());
        String lastNameStartsWith = "Sa";
        List<String> employeeFullName =
                examples.getEmployeesByLastNameCS(lastNameStartsWith);
        timer.setEndTime(System.nanoTime());
        System.out.println("GetEmployeesByLastNameCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Last names starting with '"
                + lastNameStartsWith + "': " + employeeFullName.size());
        if (employeeFullName.size() > 0) {
            System.out.println("Last employee found: "
                    + employeeFullName.get(employeeFullName.size() - 1));
        } else {
            System.out.println("No employees found with last name starting with '"
                    + lastNameStartsWith + "'");
        }
        System.out.println("");

        // CallableStatement example, (2) input parameters, db returns ResultSet
        timer.setStartTime(System.nanoTime());
        String color = "Red";
        String size = "44";
        List<Product> productList =
                examples.getProductsByColorAndSizeCS(color, size);
        timer.setEndTime(System.nanoTime());
        System.out.println("GetProductsByColorAndSizeCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        if (productList.size() > 0) {
            System.out.println("Products found (color: '" + color
                    + "', size: '" + size + "'): " + productList.size());
            System.out.println("First product: "
                    + productList.get(0).getProduct()
                    + " (" + productList.get(0).getProductNumber() + ")");
        } else {
            System.out.println("No products found with color '" + color
                    + "' and size '" + size + "'");
        }
        System.out.println("");

        examples.closeConnection();
    }
}

SQL Statement Performance

Although this post is not about SQL performance, I’ve added a timer feature (ProcessTimer.java class) to capture the duration of time each example takes to return data, measured in milliseconds. The  ProcessTimer.java class is part of the project code you can download. You will observe significant differences between the first four examples, even though they all basically perform the same function and return the same information. The time differences are a result of several factors, primarily pre-compilation of the SQL statements and SQL Server plan caching. The effects of these two factors are easily demonstrated by clearing the SQL Server plan cache (see SQL script below) and then running the application twice in a row. The second time, pre-compilation and plan caching should result in significantly faster times for the prepared statements and callable statements, in Examples 2–6. Differences of 2x, and up to 80x can be observed in the actual output from my machine, below.

DBCC FREEPROCCACHE;
GO

CHECKPOINT;
GO

DBCC DROPCLEANBUFFERS;
GO

C:\Users\gstaffor>java -jar
"C:\Users\gstaffor\Documents\NetBeansProjects\
JdbcStoredProcsExample\dist\JdbcStoredProcsExample.jar"

SQL SERVER STATEMENT EXAMPLES
======================================
GetAverageProductWeightST
Duration (ms): 402
Average product weight (lb): 13.48

GetAverageProductWeightPS
Duration (ms): 26
Average product weight (lb): 13.48

GetAverageProductWeightCS
Duration (ms): 35
Average product weight (lb): 13.48

GetAverageProductWeightOutCS
Duration (ms): 1620
Average product weight (lb): 13.48

GetEmployeesByLastNameCS
Duration (ms): 1108
Last names starting with 'Sa': 7
Last employee found: Sandberg, Mikael Q

GetProductsByColorAndSizeCS
Duration (ms): 56
Products found (color: 'Red', size: '44'): 7
First product: HL Road Frame - Red, 44 (FR-R92R-44)

C:\Users\gstaffor>java -jar
"C:\Users\gstaffor\Documents\NetBeansProjects\
JdbcStoredProcsExample\dist\JdbcStoredProcsExample.jar"

SQL SERVER STATEMENT EXAMPLES
======================================
GetAverageProductWeightST
Duration (ms): 57
Average product weight (lb): 13.48

GetAverageProductWeightPS
Duration (ms): 14
Average product weight (lb): 13.48

GetAverageProductWeightCS
Duration (ms): 8
Average product weight (lb): 13.48

GetAverageProductWeightOutCS
Duration (ms): 20
Average product weight (lb): 13.48

GetEmployeesByLastNameCS
Duration (ms): 42
Last names starting with 'Sa': 7
Last employee found: Sandberg, Mikael Q

GetProductsByColorAndSizeCS
Duration (ms): 8
Products found (color: 'Red', size: '44'): 7
First product: HL Road Frame - Red, 44 (FR-R92R-44)

Conclusion

This post has demonstrated several methods for querying and calling stored procedures from a SQL Server database using JDBC with the Microsoft JDBC Driver 4.0 for SQL Server. Although the examples are quite simple, the same patterns can be used with more complex stored procedures, with multiple input and output parameters, which not only select, but insert, update, and delete data.

As with any non-Oracle database driver, there are some limitations of the Microsoft JDBC Driver 4.0 for SQL Server you should be aware of by reading the documentation. However, for most tasks that require database interaction, the driver provides adequate functionality with SQL Server. The best place to learn more about using the driver is the help files that come with the driver. The path to the help files, relative to the root of the driver’s parent folder is [path to folder]\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\help\default.htm.