好用的tkMapper：简单运用

最新推荐文章于 2024-01-29 10:05:16 发布

不明白(°ㅂ° ╬)

最新推荐文章于 2024-01-29 10:05:16 发布

阅读量550

点赞数

文章标签： java 数据库 mysql

本文链接：https://blog.csdn.net/l472620667/article/details/126310955

版权

1.tkMapper简介:

基于 MyBatis 提供了很多第三⽅插件，这些插件通常可以完成数据操作⽅法的封装（ GeneralDAO ）、数据库逆向⼯程⼯作( 根据数据表⽣成实体类、⽣成映射⽂件 )

···MyBatis-plus

···tkMapper

tkMapper 就是⼀个 MyBatis 插件，是在 MyBatis 的基础上提供了很多⼯具，让开发变得简单，提⾼开发效率。

···提供了针对单表通⽤的数据库操作⽅法

···逆向⼯程（根据数据表⽣成实体类、 dao 接⼝、映射⽂件）

2.tkMapper整合

2.1基于SpringBoot完成MyBatis的整合

2.2整合tkMapper

2.2.1添加tkMapper的依赖

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

2.2.2修改启动类的 @MapperScan注解的包

···为tk.mybatis.spring.annotation.MapperScan

package com.qfedu.tkmapperdemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;

@SpringBootApplication
@MapperScan("com.qfedu.tkmapperdemo.dao")
public class TkmapperDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(TkmapperDemoApplication.class, args);
    }

}

3.tkMapper使⽤

3.1创建数据表

CREATE TABLE `users` (
 `user_id` int(64) NOT NULL AUTO_INCREMENT COMMENT '主键id ⽤户id',
 `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '⽤户名 ⽤户
名',
 `password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码 密码',
 `nickname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'昵称 昵称',
 `realname` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'真实姓名 真实姓名',
 `user_img` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '头像 头 像',
 `user_mobile` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
COMMENT '⼿机号 ⼿机号',
 `user_email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'邮箱地址 邮箱地址',
 `user_sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别
M(男) or F(⼥)',
 `user_birth` date NULL DEFAULT NULL COMMENT '⽣⽇ ⽣⽇',
 `user_regtime` datetime(0) NOT NULL COMMENT '注册时间 创建时间',
 `user_modtime` datetime(0) NOT NULL COMMENT '更新时间 更新时间',
 PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '⽤户 ' ROW_FORMAT = Compact;

3.2.创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
 private int 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; 
}

3.3创建DAO接口

tkMapper 已经完成了对单表的通⽤操作的封装，封装在 Mapper 接⼝和 MySqlMapper 接⼝；因此如果我们要完成对单表的操作，只需⾃定义DAO 接⼝继承 Mapper 接⼝和 MySqlMapper 接⼝

package com.qfedu.tkmapperdemo.dao;

import com.qfedu.tkmapperdemo.beans.User;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface UserDAO extends Mapper<User>, MySqlMapper<User> {
}

3.4测试

package com.qfedu.tkmapperdemo.dao;

import com.qfedu.tkmapperdemo.TkmapperDemoApplication;
import com.qfedu.tkmapperdemo.beans.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Date;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class UserDAOTest {

    @Autowired
    private UserDAO userDAO;

    @Test
    public void test(){
        User user = new User();
        user.setUsername("aaa");
        user.setPassword("1111");
        user.setUserImg("img/default.png");
        user.setUserRegtime(new Date());
        user.setUserModtime(new Date());
        int i = userDAO.insert(user);
        System.out.println(i);
    }
}

3.4.1要注意实体类对应的表名

@Table(name = "users") 指定表名称或者是类名与表名一致（不用注意大小写）

但是，例如: UserAbc会指向user_abc表

package com.qfedu.tkmapperdemo.beans;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Table;
import java.util.Date;


@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "users")
public class User {
    private int 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;
}

数据库中出现，测试成功

4.tkMapper提供的⽅法

bug：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

解决方法：重新启动MySQL服务就好了

问题原因：不明

4.1增删改查

package com.qfedu.tkmapperdemo.dao;

import com.qfedu.tkmapperdemo.TkmapperDemoApplication;
import com.qfedu.tkmapperdemo.beans.Category;
import org.apache.ibatis.session.RowBounds;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import tk.mybatis.mapper.entity.Example;

import java.util.List;

import static org.junit.Assert.*;
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest {

    @Autowired
    private CategoryDAO categoryDAO;

    @Test
    public void testInsert(){
        Category category = new Category(0,"测试类别2",1,0,"02.png","hehe","aaa.jbg","black");
        //添加
        //int i = categoryDAO.insert(category);
        //添加 且主键回填
        int i = categoryDAO.insertUseGeneratedKeys(category);
        System.out.println(category.getCategoryId());
        assertEquals(1,i);
    }

    @Test
    public void testUpdate(){
        Category category = new Category(52,"测试类别4",1,0,"02.png","haxi","aaa.jbg","black");
        //根据主键修改
        int i = categoryDAO.updateByPrimaryKey(category);
        //根据自定义条件修改，Example example 就是封装条件
        //int i1 = categoryDAO.updateByExample(Example example);
        assertEquals(1,i);
    }
    @Test
    public void testDelect(){
        //根据主键删除
        int i = categoryDAO.deleteByPrimaryKey(52);
        // 根据条件查询
        //int i1 = categoryDAO.updateByExample(Example example);
        assertEquals(1,i);
    }
    @Test
    public void testSelect1(){
        //查询所有
        List<Category> categories = categoryDAO.selectAll();
        for (Category category:categories) {
            System.out.println(category);
        }
    }
    @Test
    public void testSelect2(){
        //根据主键查询
        Category category = categoryDAO.selectByPrimaryKey(53);
        System.out.println(category);
    }
    
    @Test
    public void testSelect3(){
        //条件查询
        //1.创建一个Example封装 类别Category查询条件
        Example example = new Example(Category.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("categoryLevel",1);
        criteria.orLike("categoryName","%干%");
        List<Category> categories = categoryDAO.selectByExample(example);
        for (Category category:categories) {
            System.out.println(category);
        }
    }

    @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();
        criteria.andEqualTo("categoryLevel",1);

        int pageNum = 2 ;//页码
        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);
    }
}

5.在使⽤tkMapper是如何进⾏关联查询

5.1所有的关联查询都可以通过多个单表操作实现

@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(Orders.class);
        Example.Criteria criteria1 = example1.createCriteria();
        criteria1.andEqualTo("userId",user.getUserId());
        List<Orders> ordersList = orderDAO.selectByExample(example1);
        //3.将查询到的定单集合设置到user
        user.setOrdersList(ordersList);

        System.out.println(user);

5.2⾃定义连接查询

···在使⽤ tkMapper,DAO 继承 Mapper 和 MySqlMapper 之后，还可以⾃定义查询

5.2.1在DAO接⼝⾃定义⽅法

package com.qfedu.tkmapperdemo.dao;

import com.qfedu.tkmapperdemo.beans.User;
import com.qfedu.tkmapperdemo.general.GeneralDAO;

import java.util.List;

public interface UserDAO extends GeneralDAO<User> {
    List<User> selectByUsername(String username);
}

5.2.2 创建 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.qfedu.tkmapperdemo.dao.UserDAO">

    <resultMap id="userMap" type="com.qfedu.tkmapperdemo.beans.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_mobile" property="userMobile"/>
        <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="ordersList" ofType="com.qfedu.tkmapperdemo.beans.Orders">
            <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.user_id,
               u.username,
               u.password,
               u.nickname,
               u.realname,
               u.user_img,
               u.user_mobile,
               u.user_email,
               u.user_sex,
               u.user_birth,
               u.user_regtime,
               u.user_modtime,
               o.order_id,
               o.receiver_name,
               o.receiver_mobile,
               o.receiver_address
        from users u inner join orders o
        on u.user_id = o.user_id
    </select>

</mapper>

5.2.3测试

@Test
    public void testSelect(){
        List<User> user = userDAO.selectByUsername("zhangsan");
        System.out.println(user);


    }

bug：nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

问题分析：当前查询方法定义了只能返回一条数据，但是查询结果却返回了多条数据，导致报错。

解决办法：将查询方法定义的返回数量改为可以返回多条。