http://www.roseindia.net/tutorials/ibatis/index.shtml
<?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">
<!--- Define specify resultmap -->
<resultMap id="result" class="com.sk.ibatis.model.Contact">
<result property="id" column="id" />
<result property="firstName" column="firstName" />
<result property="lastName" column="lastName" />
<result property="email" column="email" />
</resultMap>
<!--- Define specify resultmap -->
<resultMap id="labor" class="com.sk.ibatis.model.Labor">
<result property="id" column="id" />
<result property="firstName" column="firstName" />
<result property="band" column="band" />
<result property="email" column="email" />
</resultMap>
<!--- Showing contact data by ID -->
<select id="getById" resultMap="result">
select * from contact where
id=#id#
</select>
<!--- Showing labor data by ID -->
<select id="getLaborById" resultMap="labor">
select * from contact where
id=#id#
</select>
<!--- Showing all data of table -->
<select id="getAll" resultClass="com.sk.ibatis.model.Contact">
select * from contact
</select>
<!--- Delete data from com.sk.ibatis.model.Contact table -->
<delete id="deleteAll" parameterClass="com.sk.ibatis.model.Contact">
delete from contact
</delete>
<!--- Inserting data in table -->
<insert id="insert" parameterClass="com.sk.ibatis.model.Contact">
insert into contact (firstName,lastName,email)
values (#firstName#,
#lastName#, #email#)
<selectKey resultClass="int" keyProperty="id">
select
last_insert_id() as id
</selectKey>
</insert>
<!--- Update data of com.sk.ibatis.model.Contact table -->
<update id="updateById" parameterClass="long">
update contact
set
lastName = 'gf'
where
id=#id#
</update>
<!--- Calling stored procedure -->
<procedure id="storedInfo" resultClass="com.sk.ibatis.model.Contact">
{ call showData()}
</procedure>
<!--- Calling stored procedure -->
<procedure id="storedLaborInfo" resultMap="labor">
{ call showLabor()}
</procedure>
</sqlMap>
需要理解一下关键:
1 namespace="Contact" 在Spring的dao映射文件中,我们加载了多个域名空间的时候,就会出现重复的问题,需要在SqlMapConfig.xml 文件中配置 <settings useStatementNamespaces="true" />
如此我们在applicationContext.xml文件中做如下配置:
<bean id="laborDao" class="com.sk.spring.dao.LaborDaoSqlMap">
<property name="sqlMapClientTemplate">
<ref bean="sqlMapClientTemplate" />
</property>
</bean>
在dao的java 类上使用
public List<Labor> getLaborInfo() {
return (List<Labor>) sqlMapClientTemplate.queryForList(
"Contact.storedLaborInfo", null);
}
Contact就是Namespace的区分功能。
2.resultmap,resultClass,parameterClass,parameterMap 与调用存储过程
熟悉iBatis的人一定习惯使用resultMap了。resultMqp是包括resultClass的,同理,parameterMap是包含parameterClass的。定义的时候使用一个就行了。
普通的单一表的时候,我们定义resultMap为返回值如下就可以了:
我们需要在查询结果只有一条的时候以他作为返回值<resultMap id="result" class="com.sk.ibatis.model.Contact"> <result property="id" column="id" /> <result property="firstName" column="firstName" /> <result property="lastName" column="lastName" /> <result property="email" column="email" /> </resultMap>
IbatisResultMap.java<!--- Showing contact data by ID --> <select id="getById" resultMap="result"> select * from contact where id=#id# </select>
Contact contact = (Contact) sqlMap.queryForObject("Contact.getById", new Integer(1));
但是,使用多个表,查询多个值的时候,我们就需要做如下的方法:首先定义一个POJO,将我们需要的字段定义进去,比如demo中展示的,Labor类的字段是 id, firstname, lastname ,email和 band,其中,前四个字段来自contact,后一个来自labor。
定义result
<resultMap id="labor" class="com.sk.ibatis.model.Labor"> <result property="id" column="id" /> <result property="firstName" column="firstName" /> <result property="band" column="band" /> <result property="email" column="email" /> </resultMap>
调用存储过程<procedure id="storedLaborInfo" resultMap="labor"> { call showLabor()} </procedure>
存储过程本身DELIMITER $ DROP PROCEDURE IF EXISTS `vin`.`showLabor` $ CREATE DEFINER=`root`@`%` PROCEDURE `showLabor`() BEGIN select contact.id,contact.firstName,contact.email,labor.band from contact,labor where contact.id = labor.id; END $ DELIMITER ;
使用iBatis
Reader reader = Resources.getResourceAsReader("com/sk/ibatis/xml/SqlMapConfig.xml"); SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader); System.out.println("All Labors"); List<Labor> labors = (List<Labor>) sqlMap.queryForList( "Contact.storedLaborInfo", null); Labor labor = null; for (Labor c : labors) { System.out.print(" " + c.getId()); System.out.print(" " + c.getFirstName()); System.out.print(" " + c.getBand()); System.out.print(" " + c.getEmail()); labor = c; System.out.println(""); } }