Home Tutorials Ibatis iBatis Stored Procedure Example
Questions:Ask|Latest

 
 

Share on Google+Share on Google+

iBatis Stored Procedure Example

Advertisement
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

     

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

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: September 9, 2008

Related Tutorials

Ask Questions?    Discuss: iBatis Stored Procedure Example   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
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>
DMCA.com