tkMapper整合

一.简介

tkMapper就是一个MyBatis插件，提高开发效率。

• 提供了针对单表的数据库操作方法
• 逆向工程(根据数据表生成实体类、dao接口、映射文件)

二.tkMapper整合

2.1 基于SpringBoot完成MyBatis的整合

1.新建SpringBoot项目

2.添加需要的依赖

3.配置properties

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/fmmall2?useSSL=false&userUnicode=true&characterEncoding=utf-8&serverTimeZone=GMT
spring.datasource.username=root
spring.datasource.password=####

mybatis.type-aliases-package=com.mordle.tkmapperdemo.beans
mybatis.mapper-locations=classpath:mappers/*Mapper.xml

4.在启动类添加@MapperScan(“com.mordle.tkmapperdemo.dao”)

2.2整合tkMapper

1.添加tkMapper的依赖

<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>4.2.1</version>
</dependency>

2.修改启动类@MapperScan(“com.mordle.tkmapperdemo.dao”)注解的包路径

import tk.mybatis.spring.annotation.MapperScan;

三.tkMapper使用

1.创建数据表

2.创建实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "category")
public class Category {
    @Id
    private Integer categoryId;
    private String  categoryName;
    private Integer  categoryLevel;
    private Integer  parentId;
    private String   categoryIcon;
    private String   categorySlogan;
    private String   categoryPic;
    private String   categoryBgColor;
}

3.创建GeneralDao包不被@MapperScan扫描到，定义模板，并不是具体的操作。

public interface GeneralDao<T> extends Mapper<T>, MySqlMapper<T> {
}

4.创建DAO接口

@Mapper
public interface CategoryDao extends GeneralDao<Category> {
//tkMapper已经完成了对单表的通用操作的封装,自定义Dao接口继承即可
}

四.TkMapper提供的方法

4.1添加

   @Test
    public void testInsert(){
        Category category = new Category(0,"测试2",2,0,"2.png","02.png","heh","black");
        //方法一：int i = categoryDao.insert(category);
        //方法二：回显id，必须在实体类添加@Id
        int i = categoryDao.insertUseGeneratedKeys(category);
        System.out.println(category.getCategoryId());
        assertEquals(1,i);//返回值和期望值是否相等
    }

4.2更新

  @Test
    public void testUpdate(){
        Category category = new Category(48,"测试3",3,0,"3.png","02.png","heh","black");
         //方法一：实体类必须要有@Id
        int i = categoryDao.updateByPrimaryKey(category);
         //方法二：根据自定义条件修改，Example 就是封装条件的
        //int i1 = categoryDao.updateByExample(Example example);
        assertEquals(1,i);
    }

4.3删除

 @Test
    public void testDelete(){
    	//方法一：实体类必须要有@Id
        int i = categoryDao.deleteByPrimaryKey(48);
         //方法二：根据自定义条件删除，Example 就是封装条件的
        //int i1 = categoryDao.deleteByExample(Example example);
        assertEquals(1,i);
    }

4.4查询

方法一：查询所有

 @Test
    public void testSelect1(){
        List<Category> categories = categoryDao.selectAll();
        for (Category category : categories) {
            System.out.println(category);
        }

方法二：根据主键查询实体类要有@Id

   @Test
    public void testSelect2(){
        Category category1 = categoryDao.selectByPrimaryKey(49);
        System.out.println(category1);
    }

方法三：按条件查询

@Test
    public void testSelect3(){
        //1.创建一个Example封装类别Category查询条件
        Example example = new Example(Category.class);
        Example.Criteria criteria = example.createCriteria();
   	   //2.按条件查询等级为1或2的商品,不能是and，有逻辑关联  	//不等于1 andNotEqualTo
        criteria.andEqualTo("categoryLevel",1);
        criteria.orEqualTo("categoryLevel",2);
       //3.模糊查询
        criteria.andLike("categoryName","%干%");
        List<Category> categories = categoryDao.selectByExample(example);
        for (Category category : categories) {
            System.out.println(category);
        }
    }

方法四：分页查询

//第1页：1 ~10，第2页：11~20
  @Test
    public void testSelect4(){
        int pageNum=2;
        int pageSize=10;
        int start=(pageNum-1)*pageSize;
        RowBounds rowBounds = new RowBounds(start,pageSize);
        List<Category> categories = categoryDao.selectByRowBounds(new Category(), rowBounds);
        for (Category category : categories) {
            System.out.println(category);
        }
           //查询总记录数
        int i = categoryDao.selectCount(new Category());
           System.out.println(i);
    }

方法五：条件分页

@Test
    public void testSelect5(){
        Example example = new Example(Category.class);
        Example.Criteria criteria = example.createCriteria();
        //2.按条件对一级类别分页
        criteria.andEqualTo("categoryLevel",1);

        int pageNum=1;
        int pageSize=3;
        int start=(pageNum-1)*pageSize;
        RowBounds rowBounds = new RowBounds(start,pageSize);

        List<Category> categories = categoryDao.selectByExampleAndRowBounds(example, rowBounds);
        for (Category category : categories) {
            System.out.println(category);
        }
          //查询满足条件的记录数
        int i = categoryDao.selectCountByExample(example);
        System.out.println(i);
    }

4.5连表查询

根据用户查询订单

//用户表
@Data
@NoArgsConstructor
@AllArgsConstructor
 @Table(name = "users")
public class User {
    @Id
    private Integer  userId;
    private String  username;
    private String  password;
    private String  nickname;
    private String  realname;
    private String  userImg;
    private String  userMobile;
    private String  userEmail;
    private String  userSex;
    private Date userBirth;
    private Date  userRegtime;
    private Date  userModtime;
    //订单
    private List<Order> orderList;
}

//订单表
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "orders")
public class Order {
    @Id
    private String orderId;
     //用户Id
    private Integer userId;
    private String receiverName;
    private String receiverMobile;
    private String receiverAddress;

}

方法一：多次单表查询

 @Test
    public void testSelect(){
        //查询用户的同时查询订单
        Example example = new Example(User.class );
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("username","zhangsan");

        //根据用户名查询用户
        //1.先根据用户名查询用户信息
        List<User> users = userDao.selectByExample(example);
        User user = users.get(0);
        //2.再根据用户id到订单表查询
        Example example1 = new Example(Order.class);
        Example.Criteria criteria1 = example1.createCriteria();
        criteria.andEqualTo("userId",user.getUserId());
        List<Order> orderList = orderDao.selectByExample(example1);
         //3.将查询到订单集合设置到user
        user.setOrderList(orderList);
        System.out.println(user);
    }

方法二：自定义方法，通过mapper.xml

@Mapper
public interface UserDao extends GeneralDao<User> {
    public User selectByUsername(String username);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mordle.tkmapperdemo.dao.UserDao">

    <resultMap id="userMap" type="User">
        <id column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="nickname" property="nickname"/>
        <result column="realname" property="realname"/>
        <result column="user_img" property="userImg"/>
        <result column="user_mobil" property="userMobil"/>
        <result column="user_email" property="userEmail"/>
        <result column="user_sex" property="userSex"/>
        <result column="user_birth" property="userBirth"/>
        <result column="user_regtime" property="userRegtime"/>
        <result column="user_modtime" property="userModtime"/>
        <collection property="orderList" ofType="order">
            <result column="order_id" property="orderId"/>
            <result column="receiver_name" property="receiverName"/>
            <result column="receiver_mobile" property="receiverMobile"/>
            <result column="receiver_address" property="receiverAddress"/>
        </collection>
    </resultMap>
<select id="selectByUsername" resultMap="userMap">
    select u.*,o.user_id,o.receiver_name,o.receiver_address,o.receiver_mobile
    from fmmall2.users u inner join fmmall2.orders o
    where u.user_id=o.user_id
</select>

</mapper>