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.


 
Ads

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

     

Advertisements

Ads
Share on Google+Share on Google+

iBatis Stored Procedure Example

Posted on: September 9, 2008 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: iBatis Stored Procedure Example   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:1
Bode
February 22, 2012
A simple Ibatis Program

I"m getting the following error while executing the Program: Eception Ocuuredjava.lang.RuntimeException: Error creating logger for class class com.ibatis.sqlmap.engine.impl.SqlMapClientImpl. Cause: java.lang.NullPointerException Eception Ocuured[Ljava.lang.StackTraceElement;@3ec43ec4 Instead of My sql I used Oracle DataBase: My SqlMapConfig file is <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "sql-map-config-2.dtd"> <sqlMapConfig> <settings useStatementNamespaces="true"/> <transactionManager type="JDBC"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@10.64.18.21:1521:DDDDEV40"/> <property name="JDBC.Username" value="admdv41"/> <property name="JDBC.Password" value="admin4dv41"/> </dataSource> </transactionManager> <sqlMap resource="Contact.xml"/> </sqlMapConfig>

Ads

 

Ads