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
CallableStatementto execute a stored procedure. - Third, bind the input parameters of the statement that calls the stored procedure.
- Fourth, execute the
CallableStatementthat calls the stored procedure. - Fifth, process the result set returned by the stored procedure.
- Finally, close resources including
ResultSet,CallableStatement, andConnectionto 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;
ENDCode language: SQL (Structured Query Language) (sql)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;
}
}Code language: Java (java)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());
}
}
}Code language: Java (java)How it works.
First, define a connection private field:
private final Connection connection;Code language: Java (java)Second, initialize the connection object in the constructor:
public BookDB(Connection connection) {
this.connection = connection;
}Code language: Java (java)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()
);
}Code language: Java (java)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 {Code language: Java (java)Fifth, initialize the books list object:
var books = new ArrayList<Book>();Code language: Java (java)Sixth, construct a T-SQL statement that calls the stored procedure:
String sql = "{CALL GetBooksByPublishedDate(?, ?)}";Code language: Java (java)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)) {Code language: Java (java)Eighth, bind the dates to the parameters of the query:
stmt.setDate(1, Date.valueOf(fromDate));
stmt.setDate(2, Date.valueOf(toDate));Code language: Java (java)Ninth, execute the call to the stored procedure:
try (var rs = stmt.executeQuery()) {Code language: Java (java)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));
}Code language: Java (java)Eleventh, return the books list:
return books;Code language: Java (java)Finally, throw a DBException if any error occurs during the call:
} catch(SQLException e) {
throw new DBException(e.getMessage());
}Code language: Java (java)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());
}
}
}Code language: Java (java)How it works.
First, connect to the SQL Server:
try (var connection = SQLServerConnection.connect()) {Code language: Java (java)Second, create a BookDB object:
var bookDB = new BookDB(connection);Code language: Java (java)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);Code language: Java (java)Fourth, execute the findByPublishedDate method of the bookDB object to call the stored procedure:
var books = bookDB.findByPublishedDate(fromDate, toDate );Code language: Java (java)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());
}Code language: Java (java)Finally, display the error message:
} catch(SQLException | DBException e) {
System.err.println(e.getMessage());
}Code language: Java (java)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-15Code language: Java (java)Download the project source code
Download the project source code
Summary
- Use the
CallableStatementobject to call a stored procedure.
