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
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
