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

Leave your comment:
Name:
Email:
URL:
Title:
Comments:
Enter Code:
	Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted. No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.