[置顶] ibatis调用存储过程并且取得cursor的值

时间:2021-10-20 22:17:59

本文所用的例子是基于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技术,只要稍加练习就很容易掌握了。