iBatis Stored Procedure Example

As you have seen in the previous part of tutorials that we can use inline insert , delete, update SQL commands on our database table with iBatis. Here is the example where you will see how "Stored Procedures" are called in iBatis ?

As I have mentioned in previous example we are using MySQL database and we are using a Contact table same as in previous examples. We have created a stored procedure in "vin" database named as showData() which is showing all contact information of Contact table. For creating stored procedure first open MySQL and create procedure as defined below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `vin`.`showData`$$

CREATE PROCEDURE `vin`.`showData`()
BEGIN
select * from Contact;
END$$

DELIMITER ;

"Contact.java" and "SqlMapConfig.xml" files are same as in our previous examples.

Contact.java

public class Contact {

        private String firstName; 

        private String lastName; 

        private String email;  

        private int id;

      

        public Contact() {}

        

        public Contact(

        String firstName,

          String lastName,

          String email) {

        this.firstName = firstName;

        this.lastName = lastName;

        this.email = email;

        }

        

        public String getEmail() {

        return email;

        }

        public void setEmail(String email) {

        this.email = email;

        }

        public String getFirstName() {

        return firstName;

        }

        public void setFirstName(String firstName) {

        this.firstName = firstName;

        }

        public int getId() {

        return id;

        }

        public void setId(int id) {

        this.id = id;

        }

        public String getLastName() {

        return lastName;

        }

        public void setLastName(String lastName) {

        this.lastName = lastName;

        } 

      }

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
   <settings useStatementNamespaces="true"/>
   <transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL"
   value="jdbc:mysql://192.168.10.112:3306/vin"/>
<property name="JDBC.Username" value="root"/>
<property name="JDBC.Password" value="root"/>
</dataSource>
</transactionManager>
   <sqlMap resource="Contact.xml"/>
</sqlMapConfig>

We have only modified "Contact.xml" and using <procedure> tag for calling stored procedure.

<procedure id="storedInfo" resultClass="Contact">
{ call showData() }
</procedure>

Above lines of code is calling stored procedure and results contact lists. Full source code of Contact.xml is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Contact">

<procedure id="storedInfo" resultClass="Contact">
{ call showData()}
</procedure>
</sqlMap>

Now we can call this stored procedure as :

sqlMap.queryForList("Contact.storedInfo",null); where "sqlMap" is an object of SqlMapClient class. Full source code of IbatisStoredProcedure.java is as follows :

IbatisStoredProcedure.java


import com.ibatis.common.resources.Resources;

import com.ibatis.sqlmap.client.SqlMapClient;

import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.io.*;

import java.sql.SQLException;

import java.util.*;



public class IbatisStoredProcedure{

  public static void main(String[] args) 

       throws IOException,SQLException{  

         Reader reader = 

       Resources.getResourceAsReader("SqlMapConfig.xml");

  SqlMapClient sqlMap = 

       SqlMapClientBuilder.buildSqlMapClient(reader);

  System.out.println("All Contacts");

  List<Contact> contacts = (List<Contact>)

  sqlMap.queryForList("Contact.storedInfo",null);

  Contact contact = null;

  for (Contact c : contacts) {

  System.out.print("  " + c.getId());

  System.out.print("  " + c.getFirstName());

  System.out.print("  " + c.getLastName());

  System.out.print("  " + c.getEmail());

  contact = c; 

  System.out.println("");

  }  

  }

}

To run this example follow these steps:

Create and save Contact.xml and SqlMapConfig.xml
Create Contact.java and compile it.
make IbatisStoredProcedure.java and compile it .
Execute this IbatisStoredProcedure class file and all contact information will be displayed on your command prompt.

Output:

Download Source Code

iBatis Stored Procedure Example

iBatis Stored Procedure Example

iBatis Stored Procedure Example

Tutorials