Jetbrains全系列IDE稳定放心使用

1、tkMapper介绍

tkMapper是MyBatis的插件，提供了对单表通用的操作数据库的方法

1.1 SpringBoot整合MyBatis以及tkMapper

 <!-- mybatis starter-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
 <!--1.tkMapper-->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>

1.2 在启动类上修改注解@MapperScan

import tk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
/** @MapperScan 换 tk.mybatis*/
@MapperScan("com.jia.fmmall.dao")
public class ApiApplication { 
   
	public static void main(String[] args) { 
   
		SpringApplication.run(ApiApplication.class, args);
	}
}

2、tkMapper的使用

2.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;

2.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;
}

2.3 创建Mapper接口

tkmapper 中有对单表通用操作的封装，封装到Mapper接口和MySqlMapper接口；对单表操作，只需要自定义DAO接口继承Mapper接口和MySqlMapper接口即可

import com.jia.beans.Users;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

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

2.4 测试

import com.jia.TkmapperDemoApplication;
import com.jia.beans.Users;
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;
/**生成测试类的快捷键 ctrl+shift +t */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class UsersDAOTest { 
   
    @Autowired
    private UserDAO userDAO;
    @Test
    public void test(){ 
   
        Users user = new Users();
        user.setUsername("aaaa");
        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、tkMapper的常用方法

首先定义类别的实体类以及接口

3.1 类别实体类

package com.blb.entity;

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

import javax.persistence.Column;
import javax.persistence.Id;

@Data
@NoArgsConstructor
@AllArgsConstructor
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.2 类别接口

import com.blb.entity.Category;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
public interface CategoryMapper extends Mapper<Category>, MySqlMapper<Category> { 
   
}

增加

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
	 @Test
	 public void testInsert(){ 
   
	 Category category = new Category(0,"测试类别
	3",1,0,"03.png","xixi","aaa.jpg","black");
	 //int i = categoryDAO.insert(category);
	 int i = categoryDAO.insertUseGeneratedKeys(category);
	 System.out.println(category.getCategoryId());
	 assertEquals(1,i);
	 }
}

主键回填一定要在对应的实体类的主键上添加@Id

public class Category { 
   
    /** * 主键 分类id主键 */
    @Id
    @Column(name = "category_id")
    private Integer categoryId;
}

修改

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
	@Test
	 public void testUpdate(){ 
   
	 Category category = new Category(48,"测试类别
	4",1,0,"04.png","heihei","aaa.jpg","black");
	 int i = categoryDAO.updateByPrimaryKey(category);
	 // 根据⾃定义条件修改，Example example就是封装条件的
	 // int i1 = categoryDAO.updateByExample( Example example);
	 assertEquals(1,i);
	 }
}

删除

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
@Test
 public void testDelete(){ 
   
 int i = categoryDAO.deleteByPrimaryKey(48);
 // 根据条件删除
 //int i1 = categoryDAO.deleteByExample(Example example);
 assertEquals(1,i);
 }
}

查询

查询所有

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
@Test
 public void testSelect1(){ 
   
 //查询所有
 List<Category> categories = categoryDAO.selectAll();
   for (Category category: categories) { 
   
   System.out.println(category);
   }
 }
}

根据主键查询

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
@Test
 public void testSelect2(){ 
   
 //根据主键查询
 Category category = categoryDAO.selectByPrimaryKey(47);
 System.out.println(category);
 }
}

条件查询

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
@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);
  }
 }
}

分页查询

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
@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);
 }
}

带条件的分页查询

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
@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);
  }
 }
}

关联查询

1、通过多个单表操作实现

例子：查询zhangsan的订单

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class CategoryDAOTest { 
   
 @Autowired
 private CategoryDAO categoryDAO;
@Test
 public void testSelect6(){ 
   
	//查询⽤户同时查询订单
	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);
  }
}

2、自定义连接查询

2.1、在DAO接口自定义方法

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

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.jia.fmmall.dao.UserDAO">
<insert id="insertUser">
insert into
users(username,password,user_img,user_regtime,user_modtime)
values(#{username},#{password},#{userImg},#{userRegtime},#
{userModtime})
</insert>
<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_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"/>
</resultMap>
<select id="queryUserByName" resultMap="userMap">
select
user_id,
username,
password,
nickname,
realname,
user_img,
user_mobile,
user_email,
user_sex,
user_birth,
user_regtime,
user_modtime
from users
where username=#{name}
</select>
</mapper>