一个由库表到POJO的myBatis配置文件建立的例子

在Java web项目中，使用 myBatis 来作为DB持久化ORM框架时，其中免不了要进行从数据库表到Java 的POJO对象的映射XML文件的编写，本例假设以MS SQL SERVER 2008作为数据库，对应的DB表定义和XML映射文件分别如下：

1，DB表 productCategory  定义，

create table productCategory(
recId int identity(1,1) not null,
parentCategoryId int not null default 0,
productCategoryId int not null default 0, //value created by trigger
productCategoryName varchar(100) not null default '',
description varchar(200) not null default '',
showFlag varchar(1) not null default '1', //1=show,0=hide
showIndex int not null default 0,
addTime date not null default getDate(),
primary key(recId)
)

2，myBatis 对应库表到POJO的映射XML文件如下，（接口这里省去了，根据XML可自行定义对应的接口）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.service.ProductCategoryService">
	<resultMap id="productCategoryList" type="ProductCategory">
		<id column="recId" property="recId" />
		<result column="parentCategoryId" property="parentCategoryId" />
		<result column="productCategoryId" property="productCategoryId" />
		<result column="productCategoryName" property="productCategoryName" />
		<result column="description" property="description" />
		<result column="showFlag" property="showFlag" />
		<result column="showIndex" property="showIndex" />
		<result column="addTime" property="addTime" javaType="java.util.Date" jdbcType="TIMESTAMP" />
		<result column="remark" property="remark" />
	</resultMap>
	
	<select id="getByRecId" parameterType="Integer" resultType="ProductCategory">
        select * from [ProductCategory] where [recId] = #{recId}
    </select>

	<select id="getByCategoryId" parameterType="Integer" resultType="ProductCategory">
        select * from [productCategory] where [productCategoryId] = #{productCategoryId}
    </select>
	
	<select id="getByProductCategoryName" parameterType="String" resultType="ProductCategory">
        select top 1 * from [productCategory] 
        where productCategoryName = #{productCategoryName}
    </select>
    
	<!-- 
	方法一:大于或小于号可用用对应实体代替,大于号用 > 小于号用 < 
	方法二:用<![CDATA[]]>指令包含SQL语句，如：<![CDATA[用户自定义SQL语句]]>
	-->    
    <select id="queryByProductCategoryName" parameterType="String" resultMap="productCategoryList">
        select * from productCategory 
        where charIndex(#{productCategoryName},productCategoryName) > 0 
        order by showIndex asc 
    </select>
    
    
    <!-- queryByXXXCount是为查询帮 queryByXXX求出总记录数,是配对的,名称前者在后者基础上增加Count -->
    <!-- row_number() over(order by xx asc|desc)方式 ,support ms sql2005+ -->
    <select id="queryByProductCategory" parameterType="ProductCategoryParam" resultMap="ProductCategoryList">
	select top ${rows} 
		recId ,parentCategoryId ,productCategoryId ,productCategoryName 
		,description ,showFlag ,showIndex ,addTime ,remark
	from (
		select *,ROW_NUMBER() over(order by showIndex asc) rowNo 
		from productCategory 
		where 1=1
       		<if test="adParentCategoryId != null and adParentCategoryId >= 0">
       			and parentCategoryId = #{parentCategoryId}  
       		</if> 
       		<if test="productCategoryId != null and productCategoryId >= 0">
       			and productCategoryId = #{productCategoryId} 
       		</if> 
       		<if test="productCategoryName != null and productCategoryName != ''">
       			and charIndex(#{productCategoryName},productCategoryName) > 0 
       		</if>
       		<if test="showFlag != null and showFlag != ''">
  			and showFlag = #{showFlag}
  		</if>
	) tempProductCategory where rowNo >= ${offsetRecord} 
    </select>
 	
    
    <select id="queryByProductCategoryCount" parameterType="ProductCategoryParam" resultType="Long">
        select count(1) as totalRecord from productCategory 
        where 1=1 
	<if test="adParentCategoryId != null and adParentCategoryId >= 0">
		and parentCategoryId = #{parentCategoryId}  
	</if> 
	<if test="productCategoryId != null and productCategoryId >= 0">
		and productCategoryId = #{productCategoryId} 
	</if> 
	<if test="productCategoryName != null and productCategoryName != ''">
		and charIndex(#{productCategoryName},productCategoryName) > 0 
	</if>
	<if test="showFlag != null and showFlag != ''">
		and showFlag = #{showFlag}
	</if>
    </select>
    
	<!-- 
	useGeneratedKeys属性设置为true表明通知MyBatis获取由数据库自动生成的主键值;
	keyColumn="GENERATED_KEY" 表示从DB查询得到最新的主键值放到myBatis系统的字段GENERATED_KEY中;
	keyProperty="XXX"指定把查询获取到的主键值注入到对象的XXX属性中;
	
	最新的主键(此为recId)值的获取需要位于insert元素内部的selectKey元素人工去DB中查询返回得到最新值;
	因selectKey元素位于insert元素内部,故selectKey的参数可以引用insert元素的参数值;
	其中selectKey查询的顺序可以是插入语句的执行之前或之后,由order=BEFORE|AFTER指定,如本例;
	order = BEFORE(在插入语句前面)|AFTER(在后面); 参考http://www.iteye.com/problems/86864
	-->	
	<insert id="insert" useGeneratedKeys="true" keyProperty="recId" parameterType="ProductCategory">
		<!-- recId,productCategoryId 由数据库自动生成/created by db auto -->
		<![CDATA[
			insert into productCategory(
				parentCategoryId ,productCategoryName 
				,description ,showFlag ,showIndex ,remark, addTime
			) values (
				#{parentCategoryId},#{productCategoryName}
				,#{description} ,#{showFlag} ,#{showIndex} ,#{remark} 
				,#{addTime,jdbcType=TIMESTAMP}
			)
		]]>
		<selectKey resultType="java.lang.Integer" keyProperty="recId" order="AFTER"> 
			SELECT top 1 recId from [productCategory] 
			where productCategoryName = #{productCategoryName}  
			order by recId desc 
		</selectKey>			
	</insert>

	<update id="update" parameterType="productCategory">
		<!-- recId,productCategoryId can not update -->
		update productCategory set 
			parentCategoryId = #{parentCategoryId}
			,productCategoryName = #{productCategoryName}
			,description = #{description}
			,showFlag = #{showFlag}
			,showIndex = #{showIndex}
			,remark = #{remark}
			,addTime = #{addTime,jdbcType=TIMESTAMP}
		where recId = #{recId}
	</update>

	<delete id="delete" parameterType="productCategory">
		delete from productCategory where recId = #{recId}
	</delete>
	
	<delete id="deleteByRecId" parameterType="Integer">
		delete from productCategory where recId = #{recId}
	</delete>
	
	<delete id="deleteByProductCategoryId" parameterType="Integer">
		delete from productCategory where productCategoryId = #{productCategoryId}
	</delete>
	
	<delete id="deleteByParentCategoryId" parameterType="Integer">
		delete from productCategory where parentCategoryId = #{parentCategoryId}
	</delete>
</mapper>

这里基本设计到建立 mybatis 配置文件时，遇到的常见问题，参考这些，可以很方便写出自己需要的配置文件，简单吧？懒人计划 ...

欢迎拍砖讨论...