Java SQL Server: Call Stored Procedures

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

Summary: in this tutorial, you will learn how to call a stored procedure in SQL Server from a Java program using JDBC.

This tutorial begins where the Performing a Transaction in Java tutorial left off.

How to call a stored procedure from Java using JDBC #

Here are the steps for calling a stored procedure in SQL Server from Java using JDBC:

• First, connect to your SQL Server database using JDBC.
• Second, create a CallableStatement to execute a stored procedure.
• Third, bind the input parameters of the statement that calls the stored procedure.
• Fourth, execute the CallableStatement that calls the stored procedure.
• Fifth, process the result set returned by the stored procedure.
• Finally, close resources including ResultSet, CallableStatement, and Connection to free resources. If you use the try-with-resources statement, you don’t need to do this step manually.

Creating a new stored procedure #

Step 1. Launch Microsoft SQL Server Management Studio (SSMS) and connect to the SQL Server.

Step 2. Create a new stored procedure by executing the following statement:

CREATE PROCEDURE GetBooksByPublishedDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    -- Check for valid date range
    IF @StartDate > @EndDate
    BEGIN
        PRINT 'Error: Start date must be less than or equal to end date.'
        RETURN
    END

    -- Retrieve books within the date range
    SELECT BookID, Title, Publisher, ISBN, PublishedDate
    FROM Books
    WHERE PublishedDate BETWEEN @StartDate AND @EndDate
    ORDER BY PublishedDate;
ENDCopy

The GetBooksByPublishedDate stored procedure returns a list of books with published dates between start and end dates.

Calling a stored procedure #

Step 1. Define a Book class that represents each row in the Books table:

import java.time.LocalDate;

public class Book {
    private int bookId;
    private String title;
    private String publisher;
    private String isbn;
    private LocalDate publishedDate;


    public Book(int bookId, String title, String publisher, String isbn, LocalDate publishedDate) {
        this.bookId = bookId;
        this.title = title;
        this.publisher = publisher;
        this.isbn = isbn;
        this.publishedDate = publishedDate;
    }

    public Book(String title, String publisher, String isbn, LocalDate publishedDate) {
        this(0, title, publisher, isbn, publishedDate);
    }

    public int getBookId() {
        return bookId;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getPublisher() {
        return publisher;
    }

    public void setPublisher(String publisher) {
        this.publisher = publisher;
    }

    public String getIsbn() {
        return isbn;
    }

    public void setIsbn(String isbn) {
        this.isbn = isbn;
    }

    public LocalDate getPublishedDate() {
        return publishedDate;
    }

    public void setPublishedDate(LocalDate publishedDate) {
        this.publishedDate = publishedDate;
    }
}Copy

Step 2. Define a BookDB class to interact with the BookStore database:

import java.sql.Date;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.time.LocalDate;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

public class BookDB {
    private final Connection connection;

    public BookDB(Connection connection) {
        this.connection = connection;
    }

    private Book createBookFrom(ResultSet rs) throws SQLException {
        return new Book(
                rs.getInt("BookID"),
                rs.getString("Title"),
                rs.getString("Publisher"),
                rs.getString("ISBN"),
                rs.getDate("PublishedDate").toLocalDate()
        );
    }

    public List<Book> findByPublishedDate(LocalDate fromDate, LocalDate toDate) throws DBException {
        var books = new ArrayList<Book>();
        String sql = "{CALL GetBooksByPublishedDate(?, ?)}";

        try (var stmt = connection.prepareCall(sql)) {
            stmt.setDate(1, Date.valueOf(fromDate));
            stmt.setDate(2, Date.valueOf(toDate));

            try (var rs = stmt.executeQuery()) {
                while (rs.next()) {
                    books.add(createBookFrom(rs));
                }
                return books;
            }

        } catch (SQLException e) {
            throw new DBException(e.getMessage());
        }

    }
}Copy

How it works.

First, define a connection private field:

private final Connection connection;Copy

Second, initialize the connection object in the constructor:

public BookDB(Connection connection) {
  this.connection = connection;
}Copy

Third, define the createBookFrom() method to create a new Book object from the ResultSet object:

private Book createBookFrom(ResultSet rs) throws SQLException {
    return new Book(
            rs.getInt("BookID"),
            rs.getString("Title"),
            rs.getString("Publisher"),
            rs.getString("ISBN"),
            rs.getDate("PublishedDate").toLocalDate()
    );
}Copy

Fourth, define the findByPublishedDate method that returns a list of Book objects with the published date between the fromDate and toDate:

public List<Book> findByPublishedDate(LocalDate fromDate, LocalDate toDate) throws DBException {Copy

Fifth, initialize the books list object:

var books = new ArrayList<Book>();Copy

Sixth, construct a T-SQL statement that calls the stored procedure:

String sql = "{CALL GetBooksByPublishedDate(?, ?)}";Copy

The question marks (?) are placeholders for parameters of the GetBooksByPublishedDate stored procedure. When executing the stored procedure, you need to set actual values to these parameters.

Seventh, create a CallableStatemen object:

try (var stmt = connection.prepareCall(sql)) {Copy

Eighth, bind the dates to the parameters of the query:

stmt.setDate(1, Date.valueOf(fromDate));
stmt.setDate(2, Date.valueOf(toDate));Copy

Ninth, execute the call to the stored procedure:

try (var rs = stmt.executeQuery()) {Copy

Tenth, iterate over the rows in the result set, convert each row into a Book object, and add it to the books list:

while (rs.next()) {
  books.add(createBookFrom(rs));
}Copy

Eleventh, return the books list:

return books;Copy

Finally, throw a DBException if any error occurs during the call:

} catch(SQLException e) {
  throw new DBException(e.getMessage());
}Copy

Step 3. Modify the main() method of the Main class to use the findByPublishedDate method of the BookDB class:

import java.sql.SQLException;
import java.time.LocalDate;

public class Main {
    public static void main(String[] args) {
        try (var connection = SQLServerConnection.connect()) {
            //
            var bookDB = new BookDB(connection);

            var fromDate = LocalDate.of(2021,1,1);
            var toDate = LocalDate.of(2022,12,31);

            var books = bookDB.findByPublishedDate(fromDate, toDate );

            for (var book: books) {
                System.out.println(book.getTitle()  + '\t' + book.getPublishedDate());
            }
        } catch (SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}Copy

How it works.

First, connect to the SQL Server:

try (var connection = SQLServerConnection.connect()) {Copy

Second, create a BookDB object:

var bookDB = new BookDB(connection);Copy

Third, initialize variables that store the dates for passing to the findByPublishedDate method:

var fromDate = LocalDate.of(2021, 1, 1);
var toDate = LocalDate.of(2022, 12, 31);Copy

Fourth, execute the findByPublishedDate method of the bookDB object to call the stored procedure:

var books = bookDB.findByPublishedDate(fromDate, toDate );Copy

Fifth, iterate over the books list and display the book title and published date:

for (var book: books) {
  System.out.println(book.getTitle() + '\t' + book.getPublishedDate());
}Copy

Finally, display the error message:

} catch(SQLException | DBException e) {
  System.err.println(e.getMessage());
}Copy

Step 4. Run the program.

It’ll issue the following output:

Inside SQL Server	2021-07-20
SQL Server Performance Tuning	2022-04-15
SQL Server Internals: A Deep Dive	2022-12-15Copy

Download the project source code #

Download the project source code

Summary #

• Use the CallableStatement object to call a stored procedure.