Mybatis的命名空间
对于mybatis,可以调用其它命名空间(另外个XML文件)的的sql语句。
<?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="t8.drp.dao.ProductMapper" ><!--命名空间 -->
<!--结果集合 设置了这个mybatis会自动将查询到的列自动注入 实体中。 -->
<resultMap id="BaseResultMap" type="t8.drp.entity.Product" >
<id column="P_ID" property="pid" jdbcType="DECIMAL" />
<result column="PCA_ID" property="pcaid" jdbcType="DECIMAL" />
<result column="P_NAME" property="pname" jdbcType="VARCHAR" />
<result column="P_ISOPEN" property="pisopen" jdbcType="DECIMAL" />
<result column="P_STYLE" property="pstyle" jdbcType="DECIMAL" />
<result column="P_FILENAME" property="pfilename" jdbcType="VARCHAR" />
<result column="P_LEVEL" property="plevel" jdbcType="DECIMAL" />
<result column="P_DESCRIBE" property="pdescribe" jdbcType="VARCHAR" />
<result column="P_STOCK" property="pstock" jdbcType="DECIMAL" />
</resultMap>
<resultMap id="colorsMap" type="t8.drp.entity.ProductColor" >
<id column="PCO_ID" property="pcoid" jdbcType="DECIMAL" />
<result column="P_ID" property="pid" jdbcType="DECIMAL" />
<result column="PCO_STOCK" property="pcostock" jdbcType="DECIMAL" />
<result column="PCO_PRIMER_COST" property="pcoprimerCost" jdbcType="VARCHAR" />
<result column="PCO_SELLING_PRICE" property="pcosellingPrice" jdbcType="VARCHAR" />
<result column="PCO_NAME" property="pconame" jdbcType="VARCHAR" />
</resultMap>
<!--根据商品id,查找该商品,包括商品的颜色种类 -->
<resultMap type="t8.drp.entity.Product" id="productView">
<id column="P_ID" property="pid" />
<result column="PCA_ID" property="pcaid" jdbcType="DECIMAL" />
<result column="P_NAME" property="pname" jdbcType="VARCHAR" />
<result column="P_ISOPEN" property="pisopen" jdbcType="DECIMAL" />
<result column="P_STYLE" property="pstyle" jdbcType="DECIMAL" />
<result column="P_FILENAME" property="pfilename" jdbcType="VARCHAR" />
<result column="P_LEVEL" property="plevel" jdbcType="DECIMAL" />
<result column="P_DESCRIBE" property="pdescribe" jdbcType="VARCHAR" />
<result column="P_STOCK" property="pstock" jdbcType="DECIMAL" />
<!-- 一对多关系 -->
<!-- ofType指定students集合中的对象类型 -->
<collection property="productColors" ofType="t8.drp.entity.ProductColor" column="P_ID" select="getColors"></collection>
</resultMap>
<!--重用性,去除冗余。最好给表重命名 -->
<sql id="prams" >
<if test="product.pname != null" >AND p.P_NAME like '%'||#{product.pname,jdbcType=DECIMAL}||'%'</if>
<if test=" product.pcaid != null and product.pcaid != 0" >AND p.PCA_ID=#{product.pcaid,jdbcType=DECIMAL}</if>
<if test="product.pstyle != null and product.pstyle != 0" >AND p.P_STYLE=#{product.pstyle,jdbcType=DECIMAL}</if>
<if test="product.pisopen != null" >AND p.P_ISOPEN=#{product.pisopen,jdbcType=DECIMAL}</if>
</sql>
<sql id="P_Column_List" >
p.P_ID, p.PCA_ID, p.P_NAME, p.P_ISOPEN, p.P_STYLE, p.P_FILENAME, p.P_LEVEL, p.P_DESCRIBE,P_STOCK
</sql>
<sql id="PCO_Column_List" >
pco.PCO_ID, pco.PCO_STOCK, pco.PCO_PRIMER_COST, pco.PCO_SELLING_PRICE, pco.PCO_NAME
</sql>
<!-- 带oracle分页功能-->
<select id="query" resultMap="productView" parameterType="map" >
SELECT * FROM
(SELECT A.*, ROWNUM RN FROM (
SELECT <include refid="P_Column_List" /> from T8_PRODUCT p
WHERE 1=1 <include refid="prams" /> ) A
<!--大于号 >小于号 <-->
WHERE ROWNUM <= #{max})
WHERE RN >= #{min}
</select>
<select id="getColors" parameterType="int" resultMap="colorsMap">
SELECT <include refid="PCO_Column_List" /> FROM T8_PRODUCT_COLOR pco WHERE P_ID=#{pid}
</select>
<select id="queryRecord" resultType="java.lang.Integer" parameterType="Product" >
SELECT count(p.p_id) from T8_PRODUCT p
WHERE 1=1 <include refid="prams" />
</select>
<!--根据商品查找该商品 (包含该商品的颜色) -->
<select id="queryById" resultMap="productView" parameterType="java.lang.Integer" >
SELECT <include refid="P_Column_List" />
FROM T8_PRODUCT p WHERE P_ID=#{pid}
</select>
<delete id="delete" parameterType="java.lang.Integer" >
delete from T8_PRODUCT
where P_ID = #{pid,jdbcType=DECIMAL}
</delete>
<delete id="deleteColor" parameterType="java.lang.Integer" >
delete from T8_PRODUCT_COLOR
where PCO_ID = #{pcoid,jdbcType=DECIMAL}
</delete>
<insert id="insert" parameterType="t8.drp.entity.Product" >
insert into T8_PRODUCT (P_ID, PCA_ID, P_NAME,
P_ISOPEN, P_STYLE, P_FILENAME,
P_LEVEL, P_DESCRIBE)
values (#{pid,jdbcType=DECIMAL}, #{pcaid,jdbcType=DECIMAL}, #{pname,jdbcType=VARCHAR},
#{pisopen,jdbcType=DECIMAL}, #{pstyle,jdbcType=DECIMAL}, #{pfilename,jdbcType=VARCHAR},
#{plevel,jdbcType=DECIMAL}, #{pdescribe,jdbcType=VARCHAR})
</insert>
<update id="update" parameterType="t8.drp.entity.Product" >
update T8_PRODUCT
set PCA_ID = #{pcaid,jdbcType=DECIMAL},
P_NAME = #{pname,jdbcType=VARCHAR},
P_ISOPEN = #{pisopen,jdbcType=DECIMAL},
P_STYLE = #{pstyle,jdbcType=DECIMAL},
P_FILENAME = #{pfilename,jdbcType=VARCHAR},
P_LEVEL = #{plevel,jdbcType=DECIMAL},
P_DESCRIBE = #{pdescribe,jdbcType=VARCHAR}
where P_ID = #{pid,jdbcType=DECIMAL}
</update>
</mapper>