多表连接
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN">
-<mapper namespace="com.gem.demo.mapper.ProductMapper">
-<resultMap id="ProductMap" type="Product">
<id property="id" column="id"/>
<!-- 主键字段 -->
<result property="pname" column="pname"/>
<!-- 普通字段 -->
<result property="pimg" column="pimg"/>
<result property="price" column="price"/>
<result property="info" column="info"/>
<result property="ptype" column="ptype"/>
<!-- 1端 -->
-<association property="type" javaType="ProductType">
<id property="id" column="typeid"/>
<result property="typename" column="typename"/>
</association>
</resultMap>
-<select id="findAll" resultMap="ProductMap">
<!-- 多表连接查询 -->
select t1.*,t2.id typeid,t2.typename from product t1 left join producttype t2 on t1.ptype=t2.id
</select>
</mapper>
多表连接，一对多
<?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.gem.demo.mapper.ProductTypeMapper">
<resultMap type="ProductType" id="ProductTypeMap">
<id column="typeid" property="id"/>
<result column="typename" property="typename"/>
<!-- 一对多 -->
<collection property="products" ofType="Product">
<id column="id" property="id"/>
<result column="pname" property="pname"/>
<result column="pimg" property="pimg"/>
<result column="price" property="price"/>
<result column="info" property="info"/>
<result column="ptype" property="ptype"/>
</collection>
</resultMap>
<select id="findAll" resultMap="ProductTypeMap">
<!--1对多查询 -->
select t1.id typeid,t1.typename,t2.* from producttype t1 left join product t2 on t1.id=t2.ptype
</select>
</mapper>
</mapper>
懒加载
<?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.gem.demo.mapper.ProductMapper">
<resultMap type="Product" id="ProductMap">
<id column="id" property="id"/><!-- 主键字段 -->
<!-- 普通字段 column :查询结果中的字段名 property：实体中的属性名-->
<result column="pname" property="pname"/>
<result column="pimg" property="pimg"/>
<result column="price" property="price"/>
<result column="info" property="info"/>
<result column="ptype" property="ptype"/>
<!-- 懒加载 -->
<association property="type" javaType="ProductType"
select="com.gem.demo.mapper.ProductTypeMapper.selectById" column="ptype"></association>
</resultMap>
<select id="findAll" resultMap="ProductMap">
select * from product
</select>
</mapper>
#{}、${}的用法，动态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">
<!-- namespace:Mapper（DAO）接口的全路径 -->
<mapper namespace="com.gem.demo.mapper.ProductMapper">
<!--id: 同一个namespace下id唯一 接口中的方法名
resultType：查询结果映射的实体类的全类名
在mybatis的核心配置文件中定义别名
映射为别名
-->
<select id="findAll" resultType="Product">
select * from product
</select>
<!--
parameterType:参数类型
sql语句中
#{}设置参数 底层 jdbc psmt 预编译 ? "%"?"%" ptype=?
${} 字符串值直接拼接 没有?
#{} ${}区别
-->
<select id="selectBytype" resultType="Product" parameterType="int">
select * from product where ptype=#{ptype}
</select>
<!-- 模糊查询
select * from product where pname like '%${value}%'
select * from product where pname like "%"#{name}"%"
select * from product where pname like "%"#{value}"%"
select * from product where pname like concat("%",#{name},"%")
select * from product where pname like concat("%","${value}","%")
concat:字符串拼接函数(mysql)
-->
<select id="selectByName" resultType="Product" >
select * from product where pname like concat("%",'${value}',"%")
</select>
<select id="getToal" resultType="int" >
select count(id) from product
</select>
<!-- 动态sql -->
<!-- <select id="selectByNameAndType" resultType="Product">
select * from product where 1=1
<if test="pname!=null and pname !=''" >
and pname like "%"#{pname}"%"
</if>
<if test="ptype!=null and ptype!=0">
and ptype=#{ptype}
</if>
</select> -->
<!-- sql片段 -->
<sql id="defaultQueryProduct">
select * from product
</sql>
<select id="selectByNameAndType" resultType="Product">
<!-- 引用sql片段 -->
<include refid="defaultQueryProduct"></include>
<where>
<if test="pname!=null and pname !=''" >
and pname like "%"#{pname}"%"
</if>
<if test="ptype!=null and ptype!=0">
and ptype=#{ptype}
</if>
</where>
</select>
<!-- 分类1或者分类3的数据 ptype in(1,3)
foreach:遍历 list set map array
select * from product
where
ptype in(?,?)
foreach: 用于in查询
属性：collection 集合类型 list...
item:集合元素
index：元素的索引
open：遍历集合之前的部分
close：遍历集合之后的部分
separator：元素之间的分割符
-->
<select id="selectBytypes" resultType="Product">
<include refid="defaultQueryProduct"></include>
<where>
<foreach collection="list" item="type" open="ptype in (" separator="," close=")">
#{type}
</foreach>
</where>
</select>
</mapper>
