规范

Mapper 类

写在在shoptnt-core工程中

包名

cn.shoptnt.mapper
	system	系统相关
	member	会员相关
	goods	商品相关
	trade	交易相关
	promotion	促销相关
	payment	支付相关
	statistics	统计相关
	distribution	分销相关

类名

Model+Mapper.java

如：GoodsMapper.java、TradeMapper.java

Mappper Xml

shoptnt-core/src/main/resources/mapper
	system	系统相关
	member	会员相关
	goods	商品相关
	trade	校验相关
	promotion	促销相关
	payment	支付相关
	statistics	统计相关
	distribution	分销相关

文件名

Model+Mapper.xml

如：GoodsMapper.xml、TradeMapper.xml

Mybatis Plus升级指南

Mybatis Plus基础

领域模型

public class User {
    @TableId(type= )
    private Long id;
    private String name;
    private Integer age;
    private String email;
    //getter setter略
}

Mapper

public interface UserMapper extends BaseMapper<User> {
}

示例

增加

@Autowired
private UserMapper userMapper;
@Test
public void testAdd() {

    //构造用户模型
    User user = new User();
    user.setAge(10);
    user.setEmail("kingapex@163.com");
    user.setName("kingapex");

    //调用mapper的insert方法插入数据
    userMapper.insert(user);
}

删除

@Test
public void testDelete() {
    userMapper.deleteById(1L);

    //构造查询条件，按条件删除
    QueryWrapper wrapper = buildWrapper();
    userMapper.delete(wrapper);

}

更新

@Test
public void testUpdate() {
    //构造要更新的用户模型
    User user = new User();
    user.setId(2L);
    user.setEmail("kingapex@shoptnt.cn");
    user.setName("111");
    userMapper.updateById(user);
}

列表查询

@Test
public void testSelect() {

    //构造查询条件
    QueryWrapper wrapper = buildWrapper();

    //通过mapper查询
    List<User> userList = userMapper.selectList(wrapper);
    userList.forEach(System.out::println);
}

分页查询（返回map)

@Test
public void testPageForMap() {

    //构造查询条件
    QueryWrapper wrapper = buildWrapper();

    //构造分页条件：第一页，页大小为2
    Page page = new Page<>(1, 2);

    //调用mapper进行分页的Map式查询
    IPage<Map<String,Object>> userIPage = 
    userMapper.selectMapsPage(page, wrapper);

    Long total = userIPage.getTotal();
    System.out.println(total);
    List  userList = userIPage.getRecords();
    System.out.println(userList);
}

分页查询（返回Model)

@Test
public void testPageForModel() {

    //构造查询条件
    QueryWrapper wrapper = buildWrapper();

    //构造分页条件：第一页，页大小为2
    Page page = new Page<>(1, 2);

    //调用mapper进行分页的Map式查询
    IPage<User> userIPage = 
    userMapper.selectPage(page, wrapper);

    Long total = userIPage.getTotal();
    System.out.println(total);
    List  userList = userIPage.getRecords();
    System.out.println(userList);

}

构造查询条件

常见条件

where age=? and name like ? order by id

嵌套条件

WHERE (name LIKE ? AND (age = ? OR age = ?)) ORDER BY id ASC

  QueryWrapper buildWrapper() {
        //构造查询条件
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.orderByAsc("id");
        //name like %wf%
        wrapper.like("name", "wf");
        wrapper.and(wq -> wq.eq("age", 19).or().eq("age", 18));
        return wrapper;
    }

嵌套+动态条件

QueryWrapper buildWrapper() {
    //构造查询条件
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.orderByAsc("id");
    //name like %wf%
    wrapper.like("name", "wf");   
    wrapper.and(wq -> {
        wq.eq("age", 19);
        if (some) {
            wq.or().eq("age", 18);
        }
    });
    return wrapper;
}

自定义sql

传递wapper

src/main/resources/mapper/UserMapper.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="cn.shoptnt.mapper.UserMapper">
    <select id="customerSqlSegment" resultType="cn.shoptnt.mapper.User">
        select u.*
        from USER u
        ${ew.sqlSegment}
    </select>
</mapper>

UserMapper

public interface UserMapper extends BaseMapper<User> {
    List<User> customerSqlSegment(@Param("ew") Wrapper ew);
}

UserMapper注解方式（xml与注解方式可以二选一）

public interface UserMapper extends BaseMapper<User> {
    @Select("select u.* from USER u ${ew.customSqlSegment}")
    List<User> customerSqlSegment(@Param("ew") Wrapper ew);
}

注意：这里边${ew.customSqlSegment}不会有sql注入的问题，如果非wapper这种拼接的sql方式应该使用{}，否则会有sql注入的安全问题。

Test

@Test
public void testCustomSqlSegment() {
    QueryWrapper<User> ew = new QueryWrapper<>();
    ew.like("u.name", "Tom");
    List<User> list = userMapper.customerSqlSegment(ew);
    list.forEach(System.out::print);
 }

联合查询+ 传递参数

src/main/resources/mapper/ArticleMapper.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="cn.shoptnt.mapper.ArticleMapper">

    <select id="listDetail" resultType="cn.shoptnt.model.pagedata.vo.ArticleDetail">
        select a.*,ac.name category_name from es_article a left join es_article_category ac on a.category_id=ac.id
        where article_name  like {name}
    </select>

</mapper>

UserMapper

public interface ArticleMapper extends BaseMapper<Article>{
     @Select("  select a.*,ac.name category_name " +
    "  from es_article a left join es_article_category ac " +
    "  on a.category_id=ac.id " +
    "  where article_name  like CONCAT('%',{name},'%') ")
     List<ArticleDetail> listDetail(@Param("name") String name);
}

UserMapper（xml与注解方式可以二选一）

public interface ArticleMapper extends BaseMapper<Article>{
      @Select("  select a.*,ac.name category_name " +
    "  from es_article a left join es_article_category ac " +
    "  on a.category_id=ac.id " +
    "  where article_name  like CONCAT('%',{name},'%') ") 
    List<ArticleDetail>   listDetail(@Param("name") String name);
}

注意：可以使用CONCAT('%',{name},'%')这种方式进行个别符号的拼接

Test

@Test
public void articleTest() {
    List<ArticleDetail> articleDetailList = articleMapper.listDetail("%用户注册协议%");
    articleDetailList.forEach(System.out::print);
}

注意：这里使用了另外的model:ArticleDetail

动态sql

mapper

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.shoptnt.mapper.ArticleMapper">

    <select id="listDetail" resultType="cn.shoptnt.model.pagedata.vo.ArticleDetail">
        select a.*,ac.name category_name from es_article a left join es_article_category ac on a.category_id=ac.id
        <where>
            <if test="params.name != null and params.name != ''">
                article_name like {params.name}
            </if>
            <if test="params.list != null">
                and a.category_id in
                <foreach collection="params.list" index="index" item="item" open="(" separator="," close=")">
                    {item}
                </foreach>
            </if>
        </where>
    </select>

</mapper>

ArticleMapper

public interface ArticleMapper extends BaseMapper<Article>{
     List<ArticleDetail> listDetail
     (@Param("params") Map params);
}

ArticleMapper（xml与注解方式可以二选一）

public interface ArticleMapper extends BaseMapper<Article>{
    
    @Select("  select a.*,ac.name category_name from es_article a left join es_article_category ac on a.category_id=ac.id " +
"  <where> " +
"   <if test=\"params.name != null and params.name != ''\"> " +
    "  article_name like {params.name} " +
"   </if> " +
"   <if test=\"params.list != null\"> " +
"       and a.category_id in " +
"      <foreach collection=\"params.list\" index=\"index\"     item=\"item\" open=\"(\" separator=\",\" close=\")\"> " +
        " {item} " +
"        </foreach> " +
    " </if> " +
"   </where> ")
    List<ArticleDetail> listDetail   (@Param("params") Map params);
}

test

@Test
public void articleTest() {
    List list = new ArrayList();
    list.add(1);
    list.add(492);

    Map params = new HashMap();
    params.put("name","%用户注册协议%");
    params.put("list",list);

    List<ArticleDetail> articleDetailList = articleMapper.listDetail(params);
    articleDetailList.forEach(System.out::print);
}