环境搭建
导入依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.18</version>
</dependency>
mybatis-config.xml配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"/>
<typeAliases>
<package name="com.young.model"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${db.driverClassName}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.young.mapper.GoodMapper"/>
</mappers>
</configuration>
db.properties配置文件
db.url=jdbc:mysql://localhost:3306/mybatis2_db?serverTimezone=UTC
db.username=root
db.password=123
db.driverClassName=com.mysql.jdbc.Driver
Good实体类
public class Good {
private Integer gid;
private String gname;
private Float gprice;
private String gdecs;
//以下省略了所有成员变量的get和set方法......
}
传统的Mapper映射文件
GoodMapper接口
public interface GoodMapper {
Good selectByGid(Integer gid);
}
GoodMapper.xml映射文件
注意：xml配置文件的第一行必须为该文件的版本号等信息，即：<?xml version="1.0" encoding="UTF-8" ?>，否则会报错
<?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.young.mapper.GoodMapper">
<select id="selectByGid" resultType="Good">
SELECT
gid,
gname,
gprice,
gdecs
FROM
goods
WHERE
gid=#{gid};
</select>
</mapper>
问题：
在之前的开发中，我们会在<select>标签中书写Sql语句，比如上述根据gid查询商品信息。假设我们还需要根据gname或者gprice等其他列查询商品信息，就需要再写更多的<select>标签来实现这些功能，而且查询的信息都是相同的，也会造成代码冗余。
动态Sql
<if>标签、<sql>标签、<where>标签
GoodMapper.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.young.mapper.GoodMapper">
<sql id="baseColumn">
gid,gname,gprice,gdecs
</sql>
<!--根据传入的参数Good对象来判断，查询的条件-->
<select id="query" parameterType="Good" resultType="Good">
SELECT
<include refid="baseColumn"/>
FROM
goods
WHERE
<if test="gid!=null">
gid=#{gid}
</if>
<if test="gname!=null">
and gname=#{gname}
</if>
</select>
</mapper>
GoodMapper接口
public interface GoodMapper {
Good query(Good good);
}
使用<sql>标签将需要查询的列包裹起来，然后使用<include>标签在Sql语句中引用。
使用<if>标签进行条件的判断，如果传入的Good类对象的gid属性不为空，那么将gid=#{gid}拼接在WHERE后；同理，如果如果传入的Good类对象的gname属性不为空，将and gname=#{gname}继续拼接。
细节： 在设计Good实体类时，gid的类型为Integer，此时gid的默认值为null，如果类型为int，那么默认值为0，<if>标签在进行判断时会将sql语句拼接
测试方法
@Test
public void testMapper() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sessionFactory.openSession();
GoodMapper goodMapper = session.getMapper(GoodMapper.class);
Good good = new Good();
good.setGid(1);
good.setGname("补兵的艺术");
Good result = goodMapper.query(good);
System.out.println(JSON.toJSONString(result,true));
session.close();
}
运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods WHERE gid=? and gname=?
问题：
如果Good类对象的gid属性为null，即将good.setGid(1);这一行注释，那么执行的sql语句为：SELECT gid,gname,gprice,gdecs FROM goods WHERE and gname=?
所以可以使用<where>标签，如下所示：
<select id="query" parameterType="Good" resultType="Good">
SELECT
<include refid="baseColumn"/>
FROM
goods
<where>
<if test="gid!=null">
and gid=#{gid}
</if>
<!--优化功能，如果用户输入空字符串或者空格不会查询-->
<if test="gname!=null and gname.trim!='' ">
and gname=#{gname}
</if>
</where>
</select>
<where>标签会自动删除where关键字后的and关键字
测试方法
GoodMapper goodMapper = session.getMapper(GoodMapper.class);
Good good = new Good();
good.setGname("补兵的艺术");
Good result = goodMapper.query(good);
System.out.println(JSON.toJSONString(result,true));
运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods WHERE gname=?
<trim>标签
可以使用<trim>标签代替 <where>标签
<select id="query" parameterType="Good" resultType="Good">
SELECT
<include refid="baseColumn"/>
FROM
goods
<!--prefix:增加的前缀
prefixOverrides:删除的前缀
suffix:增加的后缀
suffixOverrides:删除的后缀
在动态生成的语句中，删除前缀"and"，然后增加前缀"where"-->
<trim prefix="where" prefixOverrides="and">
<if test="gid!=null">
and gid=#{gid}
</if>
<if test="gname!=null and gname.trim!=''">
and gname=#{gname}:
</if>
</trim>
</select>
测试方法
GoodMapper goodMapper = session.getMapper(GoodMapper.class);
Good good = new Good();
good.setGname("补兵的艺术");
Good result = goodMapper.query(good);
System.out.println(JSON.toJSONString(result,true));
运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods where gname=?
<trim>标签通常用于插入和更新
GoodMapper.xml映射文件中<update>标签内容
<update id="update" parameterType="Good">
UPDATE
goods
SET
<trim prefixOverrides="," >
<if test="gname!=null and gname.trim!=''">
gname=#{gname}
</if>
<if test="gprice!=null">
,gprice=#{gprice}
</if>
<if test="gdecs!=null and gdecs.trim!=''">
,gdecs=#{gdecs}
</if>
</trim>
where gid=#{gid}
</update>
GoodMapper接口
public interface GoodMapper {
Good query(Good good);
void update(Good good);
}
测试方法
GoodMapper goodMapper = session.getMapper(GoodMapper.class);
Good good = new Good();
good.setGid(4);
good.setGname("英雄图鉴");
good.setGdecs("英雄百科全书");
goodMapper.update(good);
session.commit();
运行方法通过日志可以得知执行的sql语句：UPDATE goods SET gname=? ,gdecs=? where gid=?
<foreach>标签
<foreach>标签通常用于构建IN条件语句时
GoodMapper.xml映射文件
<select id="queryIn" parameterType="list" resultType="Good">
SELECT
<include refid="baseColumn"/>
FROM
goods
where gid in
<!--collection:该属性的值为"list"，代表传入的参数List集合
item:表示集合中的元素
open:表示前缀
close:表示后缀
separator:表示每个元素的分割符
index:如果参数时List集合或者数组表示下标，如果参数是Map集合，则表示key-->
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
GoodMapper接口
public interface GoodMapper {
Good query(Good good);
void update(Good good);
List<Good> queryIn(List<Integer> ids);
}
测试方法
GoodMapper goodMapper = session.getMapper(GoodMapper.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(3);
List<Good> goods = goodMapper.queryIn(ids);
System.out.println(JSON.toJSONString(goods,true));
运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods where gid in ( ? , ? )
扫一扫
1361
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
