ibatis调用mysql存储过程示例

配置文件:

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 cacheModelsEnabled="true"
			  enhancementEnabled="true"
			  lazyLoadingEnabled="true"
			  maxRequests="32"
			  maxSessions="10"
			  maxTransactions="5"
			  useStatementNamespaces="true"
			/>

  <!-- Configure a built-in transaction manager.  If you're using an 
       app server, you probably want to use its transaction manager 
       and a managed datasource -->
  <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="proxool.ProxoolDataSourceFactory">
      <property name="driver" value="com.mysql.jdbc.Driver"/>
      <property name="driverUrl" value="jdbc:mysql://localhost:3306/test"/>
      <property name="user" value="root"/>
      <property name="password" value="root"/>
      <property name="alias" value="default"/>
      <property name="maximum-connection-count" value="40"/>
      <property name="overload-without-refusal-lifetime" value="60"/>
      <property name="house-keeping-test-sql" value="select 1"/>  
    </dataSource>
  </transactionManager>


  <sqlMap resource="proxool/User.xml"/>

</sqlMapConfig>

 

User.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="user">

	<!-- 返回结果集 -->
	<parameterMap id="ParameterMap" class="map">
	    <parameter property="userNo" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
  	</parameterMap>

	<procedure id="call_SearchUser" parameterMap="ParameterMap" resultClass="proxool.User">
		{call selectuser(?)} 
	</procedure>

</sqlMap>

 

数据库脚本:

create procedure selectuser(in userno int) 
begin   
 select * from user where uid = userno;   
end 

 

 pojo类:

package proxool;

public class User {

	private String uid;

	private String usercd;

	private String password;

	private String addymd;

	public String getAddymd() {
		return addymd;
	}

	public void setAddymd(String addymd) {
		this.addymd = addymd;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getUid() {
		return uid;
	}

	public void setUid(String uid) {
		this.uid = uid;
	}

	public String getUsercd() {
		return usercd;
	}

	public void setUsercd(String usercd) {
		this.usercd = usercd;
	}

}

 

测试代码: 

List list = null;
		String resource = "proxool/sqlMapConfig.xml";
		Reader reader;
		SqlMapClient sqlMap = null;
		try {
			reader = Resources.getResourceAsReader(resource);
			sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
			sqlMap.startTransaction();
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put("userNo", 1);
			list = sqlMap.queryForList("user.call_SearchUser", map);
			for (int i = 0; i < list.size(); i++) {
				User user = (User) list.get(i);
				System.out.print(user.getUid());
				System.out.print('\t');
				System.out.print(user.getUsercd());
				System.out.print('\t');
				System.out.println(user.getPassword());
			}
			sqlMap.commitTransaction();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlMap.endTransaction();
		}