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.

iBatis Stored Procedure Example

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">
  <!--- Calling stored procedure --> 
  <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