Today is Sunday, 10th March 2019

Archive for November, 2011


Calling Stored Procedures with Spring JDBC Templates

In this article, you learn how to call stored procedures using the Spring JDBC Templates API. Stored procedures are commonly used to encapsulate complex SQL queries. Application developers can leverage the functionality of stored procedures with the Spring JDBC Templates API.

In this example, we have a stored procedure named: getCars. This stored procedure will search for cars based on the given criteria. The procedure returns a result set and status information.

The parameters are listed below:

IN parameters
• Make – varchar
• Model – varchar
• Year – int
• Maximum Price – double

OUT parameters
• Result Set
• Status Code
• Status Message

Here’s an overview of the development process
Step 0: Set up database table, stored procedure and sample data
Step 1: Define an access class for the stored procedure
Step 2: Define a mapper to convert a row in result set to a domain object
Step 3: Call the stored procedure from your DAO implementation
Step 4: Spring configuration
Step 5: Simple test program

Now, let’s walk thru the process step-by-step

Set up database table, stored procedure and sample data
Execute the following SQL code to create the table, create the stored procedure and insert sample data. This is for MySQL. If you are using a different database, then adjust the code accordingly.

This file is included in the source code download. File location: sql/table-setup.sql.

Step 1: Define an access class for the stored procedure

Step 2: Define a mapper to convert a row in result set to a domain object

Step 3: Call the stored procedure from your DAO implementation

Supporting code for DAOConstants.java

Supporting code for Car.java

Supporting code for CarsDAO.java

Step 4: Spring Configuration

Here’s the code for the Spring configuration file:

Step 5: Simple test program

Let’s create a simple test program. We’ll get an instance of our CarsDAOImpl and then search for Nissan Altimas less than $50,000.

File: SpringDemoApp.java

Once you run the SpringDemoApp, then it will print out the following (assuming you used the same database setup script from Step 0).

You now have the steps to call a stored procedure with Spring JDBC Templates. Enjoy!

 

Download Java Source Code

 



Reading MS Excel files (.xslx) in Java

Last weekend, I was working on one of my personal projects. I needed to read a MS Excel spreadsheet. I know that the folks at Apache have a nice API for reading and writing MS Excels files, so I thought I’d download the latest version and give it a try.

The Apache project is named “Apache POI”. You can download it from http://poi.apache.org. Once downloaded, then extract the zip distribution and add the JAR files to your classpath.  You will also need the dependency JAR files. If you’re using Maven then this is automatic. You can also download the dependencies manually. See this page and at the bottom they provide a list of dependencies.

Here is a snippet of code to read a MS Excel 2010 file.

        import java.io.FileInputStream;
        import org.apache.poi.ss.usermodel.Cell;
        import org.apache.poi.ss.usermodel.Row;
        import org.apache.poi.xssf.usermodel.XSSFSheet;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;

        ...
        ...

        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("foo.xslx"));

        int sheetIndex = 0;
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

        for (Row tempRow : sheet) {

            // print out the first three columns
            for (int column = 0; column < 3; column++) {
                Cell tempCell = tempRow.getCell(column);

                if (tempCell.getCellType() == Cell.CELL_TYPE_STRING) {
                    System.out.print(tempCell.getStringCellValue() + "  ");
                }
            }
            System.out.println();
        }

The API also provides support for writing to a MS Excel in the native Microsoft format. If you want to get really fancy then you can even create formula fields etc. You can really go town with this one. By using this API it is much more powerful than shipping around CSV files.

Anyways, I hope you find this useful during your coding adventures.