1、实体和数据库中对应
@Table
@TableId
@TableField
其中TableField
2、排除非表字段的三种
a)在字段前家transit
b) 设置字段为static,并添加get、set方法
c) @TableField(exist = false) 表示不是表中的字段
3、配置文件
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/mybatis_plus?useSSL=true&serverTimezone=GMT%2B8 username: root password: 123456 logging: level: root: warn com.smiletimli.mybatis_plus_demo.dao: trace pattern: console: '%p%m%n'
4、简单查询使用
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.smiletimli.mybatis_plus_demo.entity.User;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {
@Resource
private UserMapper userMapper;
/**
* 普通方法查询全部
*/
@Test
public void selectList() {
List<User> userList = userMapper.selectList(null);
Assert.assertEquals(5,userList.size());
userList.forEach(System.out::println);
}
/**
* selectByMap
*/
@Test
public void selectByMap() {
Map<String,Object> map = new HashMap<>();
map.put("age",25);
List<User> userList = userMapper.selectByMap(map);
userList.forEach(System.out::println);
}
@Before
public void test() {
System.out.println("考试");
}
/**
* 1、名字中包含雨并且年龄小于40
* name like '%雨%' and age<40
*/
@Test
public void selectLikeAndLt() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.likeRight("name","雨").lt("age", 40);
List<User> userList = userMapper.selectList(queryWrapper);
Assert.assertEquals(2,userList.size());
userList.forEach(System.out::println);
}
/**
* 2、名字中包含雨年并且龄大于等于20且小于等于40并且email不为空
* * name like '%雨%' and age between 20 and 40 and email is not null
*/
@Test
public void selectByListBetwweenAndNotNull() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.like("name","雨").between("age", 20 , 40).isNotNull("email");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* * 3、名字为王姓或者年龄大于等于25,按照年龄降序排列,年龄相同按照id升序排列
* name like '王%' or age>=25 order by age desc,id asc
*/
@Test
public void selectListByListOrAgeOrderBy(){
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.likeRight("name","王").or().ge("age", "25").orderByDesc("age").orderByAsc("id");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 4、创建日期为2019年2月14日并且直属上级为名字为王姓
* date_format(create_time,'%Y-%m-%d')='2019-02-14'
* and manager_id in (select id from user where name like '王%')
*
*/
@Test
public void selectApplyAndInSql() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2019-02-14")
.inSql("manager_id","select id from user where name like '王%'");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 5、名字为王姓并且(年龄小于40或邮箱不为空)
* name like '王%' and (age<40 or email is not null)
*/
@Test
public void selectByLikeAnd() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.likeRight("name","王").and(i -> i.lt("age",40).or().isNotNull("email"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 6、名字为王姓或者(年龄小于40并且年龄大于20并且邮箱不为空)
* name like '王%' or (age<40 and age>20 and email is not null)
*/
@Test
public void selectByLikeOr() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.likeRight("name","王")
.or().nested(i -> i.between("age",20,40).isNotNull("email"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 7、(年龄小于40或邮箱不为空)并且名字为王姓
* (age<40 or email is not null) and name like '王%'
*/
@Test
public void selectByOr(){
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.nested(i->i.lt("age",40).or().isNotNull("email"))
.likeRight("name", "王");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 8、年龄为30、31、34、35
* age in (30、31、34、35)
* 9、只返回满足条件的其中一条语句即可
* limit 1
*/
@Test
public void selectByInAndLimit() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.inSql("age","30,31,32,35").last("limit 1");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
}
5、只查询部分字段
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.smiletimli.mybatis_plus_demo.entity.User;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Predicate;
/**
* 普通测试的加强版
* select中字段不全部出现的查询
*
* 10、名字中包含雨并且年龄小于40(需求1加强版)
*
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperStrongTest {
@Resource
private UserMapper userMapper;
/**
* 名字中包含雨并且年龄小于40
select id,name
* from user
* where name like '%雨%' and age<40
*/
@Test
public void selectList() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.select("id,name").like("name", "王").lt("age", 40);
List<User> userList = userMapper.selectList(queryWrapper);
Assert.assertEquals(1,userList.size());
userList.forEach(System.out::println);
}
/**
* 名字中包含雨并且年龄小于40
select id,name,age,email
* from user
* where name like '%雨%' and age<40
*/
@Test
public void selectList1() {
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.like("name", "王").lt("age", 40)
.select(User.class, info -> !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id"));
List<User> userList = userMapper.selectList(queryWrapper);
Assert.assertEquals(1,userList.size());
userList.forEach(System.out::println);
}
}
6、根据字段是否为空进行查询
String name = "";
String age = "25";
QueryWrapper<User> queryWrapper = Wrappers.<User>query();
queryWrapper.like(StringUtils.isNotEmpty(name),name,"王")
.lt(StringUtils.isNotEmpty(age), age, 40);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
7、使用lambda进行查询
lambdaQuery
LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.<User>lambdaQuery();
lambdaQueryWrapper.like(User::getName,"王").lt(User::getAge, 40);
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);
LambdaQueryChainWrapper
List<User> userList = new LambdaQueryChainWrapper<User>(userMapper)
.like(User::getName, "雨").ge(User::getAge, 20).list();
userList.forEach(System.out::println);
其中list()方法使用的源码如下
public interface ChainQuery<T> extends ChainWrapper<T> {
default List<T> list() {
return this.getBaseMapper().selectList(this.getWrapper());
}
default T one() {
return this.getBaseMapper().selectOne(this.getWrapper());
}
default Integer count() {
return this.getBaseMapper().selectCount(this.getWrapper());
}
default IPage<T> page(IPage<T> page) {
return this.getBaseMapper().selectPage(page, this.getWrapper());
}
}
8、自定义sql
方式一:通过注解@Select
public interface UserMapper extends BaseMapper<User> {
@Select("select * from user ${ew.customSqlSegment}")
List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);
}
方式二:
通过xml文件
mybatis-plus: mapper-locations: classpath:/mapper/*Mapper.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.smiletimli.mybatis_plus_demo.dao.UserMapper"> <select id="selectAll" resultType="com.smiletimli.mybatis_plus_demo.entity.User"> select * from user ${ew.customSqlSegment} </select> </mapper>