本文所用的例子是基于ibatis自带的demo（Account），数据库是oracle10g，该例子不只有ibatis调用存储过程并取得cursor的值，还有模糊查询，返回普通的string类型，批量查询等。
首先创建一个表：
create table account（
acc_id number(4) primary key,
acc_first_name varchar2(20),
acc_last_name varchar2(20),
acc_email varchar2(50)
）
account表对应的实体类：
package com.mydomain.domain;public class Account { private int id; private String firstName; private String lastName; private String emailAddress; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmailAddress() { return emailAddress; } public void setEmailAddress(String emailAddress) { this.emailAddress = emailAddress; }}
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> <transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@172.17.40.227:1521:orcl"/> <property name="JDBC.Username" value="scott"/> <property name="JDBC.Password" value="tiger"/> </dataSource> </transactionManager></sqlMapConfig>
Account.xml 如下：
<?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="Account"> <!-- Use type aliases to avoid typing the full classname every time. --> <typeAlias alias="Account" type="com.mydomain.domain.Account"/> <!-- Result maps describe the mapping between the columns returned from a query, and the class properties. A result map isn't necessary if the columns (or aliases) match to the properties exactly. --> <resultMap id="AccountResult" class="Account"> <result property="id" column="ACC_ID"/> <result property="firstName" column="ACC_FIRST_NAME"/> <result property="lastName" column="ACC_LAST_NAME"/> <result property="emailAddress" column="ACC_EMAIL"/> </resultMap> <!-- Select with no parameters using the result map for Account class. --> <select id="selectAllAccounts" resultMap="AccountResult"> select * from ACCOUNT </select> <!-- A simpler select example without the result map. Note the aliases to match the properties of the target result class. --> <select id="selectAccountById" parameterClass="int" resultClass="Account"> select ACC_ID as id, ACC_FIRST_NAME as firstName, ACC_LAST_NAME as lastName, ACC_EMAIL as emailAddress from ACCOUNT where ACC_ID = #id# </select> <!-- Insert example, using the Account parameter class --> <insert id="insertAccount" parameterClass="Account"> insert into ACCOUNT ( ACC_ID, ACC_FIRST_NAME, ACC_LAST_NAME, ACC_EMAIL values ( #id#, #firstName#, #lastName#, #emailAddress# ) </insert> <!-- Update example, using the Account parameter class --> <update id="updateAccount" parameterClass="Account"> update ACCOUNT set ACC_FIRST_NAME = #firstName#, ACC_LAST_NAME = #lastName#, ACC_EMAIL = #emailAddress# where ACC_ID = #id# </update> <!-- Delete example, using an integer as the parameter class --> <delete id="deleteAccountById" parameterClass="int"> delete from ACCOUNT where ACC_ID = #id# </delete> <!-- 调用存储过程，获得name --> <parameterMap id="swapParam" class="java.util.HashMap"> <parameter property="name" javaType="java.lang.String" jdbcType="VARCHAR" mode="OUT"/> <parameter property="id" javaType="java.lang.Integer" jdbcType="INT" mode="IN"/> </parameterMap> <procedure id="selectNamePro" parameterMap="swapParam"> {call get_account_name(?,?)} </procedure> <!-- 调用存储过程，获得cursor --> <parameterMap id="swapParamCursor" class="java.util.HashMap"> <parameter property="cursor" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/> <parameter property="id" javaType="java.lang.Integer" jdbcType="INT" mode="IN"/> </parameterMap> <procedure id="selectCursorPro" parameterMap="swapParamCursor" resultClass="java.util.HashMap"> {call get_account_cursor(?,?)} </procedure> <!-- 调用存储过程，获得cursor，返回account对象 --> <resultMap id="myaccount" class="com.mydomain.domain.Account"> <result property="id" column="ACC_ID" jdbcType="Int"/> <result property="firstName" column="ACC_FIRST_NAME" jdbcType="VARCHAR"/> <result property="lastName" column="ACC_LAST_NAME" jdbcType="VARCHAR"/> <result property="emailAddress" column="ACC_EMAIL" jdbcType="VARCHAR"/> </resultMap> <procedure id="selectCursorProAccount" parameterMap="swapParamCursor" resultMap="myaccount"> {call get_account_cursor(?,?)} </procedure> <!-- 通过name实现模糊查询 --> <select id="selectAccountsByName" parameterClass="String" resultClass="Account"> select acc_id as id, acc_first_name as firstName, acc_last_name as lastName, acc_email as emailAddress from account where acc_last_name like '$name$%' </select> <!-- 批量查询 --> <select id="selectAccountsByIds" parameterClass="String" resultClass="Account"> select acc_id as id, acc_first_name as firstName, acc_last_name as lastName, acc_email as emailAddress from account where acc_id in ($ids$) </select></sqlMap>
具体的调用的代码实现类-SimpleExample.java代码如下：
package com.mydomain.data;import java.io.IOException;import java.io.Reader;import java.sql.ResultSet;import java.sql.SQLException;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import com.ibatis.common.resources.Resources;import com.ibatis.sqlmap.client.SqlMapClient;import com.ibatis.sqlmap.client.SqlMapClientBuilder;import com.mydomain.domain.Account;/** * This is not a best practices class. It's just an example * to give you an idea of how iBATIS works. For a more complete * example, see JPetStore 5.0 at http://www.ibatis.com. */@SuppressWarnings("unchecked")public class SimpleExample { /** * SqlMapClient instances are thread safe, so you only need one. * In this case, we'll use a static singleton. So sue me. ;-) */ private static SqlMapClient sqlMapper; /** * It's not a good idea to put code that can fail in a class initializer, * but for sake of argument, here's how you configure an SQL Map. */ static { try { Reader reader = Resources.getResourceAsReader("com/mydomain/data/SqlMapConfig.xml"); sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader); reader.close(); } catch (IOException e) { // Fail fast. throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e); } } public static List selectAllAccounts () throws SQLException { return sqlMapper.queryForList("selectAllAccounts"); } public static Account selectAccountById (int id) throws SQLException { return (Account) sqlMapper.queryForObject("selectAccountById", id); } public static void insertAccount (Account account) throws SQLException { sqlMapper.insert("insertAccount", account); } public static void updateAccount (Account account) throws SQLException { sqlMapper.update("updateAccount", account); } public static void deleteAccount (int id) throws SQLException { sqlMapper.delete("deleteAccount", id); } //通过id，获得名字 public static void selectNamePro(int id) throws SQLException{ Map map = new HashMap(); map.put("id", id);// map.put("name", ""); sqlMapper.queryForObject("selectNamePro",map); System.out.println("调用存储过程的结果："+map.get("name")); }//通过id，获得列表-map public static void selectCursorPro(int id) throws SQLException{ Map map = new HashMap(); map.put("id", id);// map.put("cursor", null); List<Map> list = sqlMapper.queryForList("selectCursorPro",map); for (Iterator iterator = list.iterator(); iterator.hasNext();) {Map temp = (Map) iterator.next();System.out.println(temp.get("ACC_ID")+" "+temp.get("ACC_FIRST_NAME")+temp.get("ACC_LAST_NAME")); } System.out.println(); /*ResultSet rs = (ResultSet)map.get("cursor"); while(rs.next()){System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getFloat(3)); }*/ } @SuppressWarnings("unchecked") public static void selectCursorProAccount(int id) throws SQLException{ Map map = new HashMap(); map.put("id", id);// map.put("cursor", null); List<Account> list = sqlMapper.queryForList("selectCursorProAccount",map); for(Iterator iterator = list.iterator();iterator.hasNext();){ Account temp = (Account)iterator.next(); System.out.println(temp.getId()+":"+temp.getFirstName()+temp.getLastName()); } System.out.println(); } //lastName模糊查询 public static List<Account> selectAccountsByName (String name) throws SQLException { List<Account> list = sqlMapper.queryForList("selectAccountsByName",name); return list; } public static List<Account> selectAccountsByIds(String ids) throws SQLException{ return sqlMapper.queryForList("selectAccountsByIds",ids); }}
程序的入口所在的类：ibatisProTest.JAVA 代码如下：
package com.jac.ibatis;import java.sql.SQLException;import java.util.Iterator;import java.util.List;import com.mydomain.data.SimpleExample;import com.mydomain.domain.Account;@SuppressWarnings("unchecked")public class ibatisProTest {/** * @param args * @throws SQLException */public static void main(String[] args) throws SQLException {// TODO Auto-generated method stub/*List list = SimpleExample.selectAllAccounts();for (Iterator iterator = list.iterator(); iterator.hasNext();) {Account object = (Account) iterator.next();System.out.println(object.getId()+":"+object.getFirstName()+object.getLastName());}*///SimpleExample.selectNamePro(2);//SimpleExample.selectCursorPro(1);//SimpleExample.selectCursorProAccount(1);List<Account> list = SimpleExample.selectAccountsByName("江");for (Iterator iterator = list.iterator(); iterator.hasNext();) {Account account = (Account) iterator.next();System.out.println("aaaa："+account.getLastName()+"");//批量查询List<Account> list = SimpleExample.selectAccountsByIds("1,2");for (Iterator iterator = list.iterator(); iterator.hasNext();) {Account account = (Account) iterator.next();System.out.println("aaaa："+account.getFirstName()+account.getLastName()+"");}}}
总结：其实ibatis是属于比较简单的sqlmap技术，只要稍加练习就很容易掌握了。