More Tutorials| Bioinformatics| Open Source| Photoshop| Questions?

 

 

 

 

 

 

 

 

 

 

 

 

 

Search Tutorials

Latest Questions
Comments
 
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

                         

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

                         

» View all related tutorials
Related Tags: c database query com ide ant data properties import object mapping map sed maps vi port bat id app tab

Leave your comment:

Name:

Email:

URL:

Title:

Comments:


Enter Code:

Audio Version
Reload Image
 

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.

Add This Tutorial To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Training Courses
Tell A Friend
Your Friend Name
Software Solutions
Least Viewed
Most Rated
Recently Viewed
Search Tutorials

 

 
 

Home | JSP | EJB | JDBC | Java Servlets | WAP  | Free JSP Hosting  | Search Engine | News Archive | Jboss 3.0 tutorial | Free Linux CD's | Forum | Blogs

About Us | Advertising On RoseIndia.net  | Site Map

India News

Indian Software Development Company | iPhone Development Company in India | Flex Development Company in India | Java Training Delhi | Java Training at Noida |

Send your comments, Suggestions or Queries regarding this site at roseindia_net@yahoo.com.

Copyright © 2008. All rights reserved.