项目目录结构
数据库文件
CREATE TABLE `user` (
`id` int(24) primary key NOT NULL,
`name` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`age` int(3) ,
`tel` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `user`
--
INSERT INTO `user` (`id`, `name`, `password`, `age`, `tel`) VALUES
(1, 'Tom', 'tom', 3, '18866668888'),
(2, 'Jerry', 'jerry', 4, '16688886666'),
(3, 'Jock', 'jock', 41, '18812345678'),
(4, '传智播客', 'itcast', 15, '400618400'),
(1, 'Tom', 'tom', 3, '18866668888'),
(2, 'Jerry', 'jerry', 4, '16688886666'),
(3, 'Jock', 'jock', 41, '18812345678'),
(4, '传智播客', 'itcast', 15, '400618400');
COMMIT;
application.yml
在application.yml中配置log-impl，能在控制台查看mybatis-plus的SQL语句，方便Debug
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?serverTime=UTC
username: root
password: ""
#开启mp的日志(输出到控制台)
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
User类
package com.example.domain;
import lombok.*;
/**
* @auther CharlieLiang
* @date 2022/6/4-21:57
*/
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ToString
public class User {
private Long id;
private String name;
private String password;
private Integer age;
private String tel;
}UserDao类
package com.example.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.domain.User;
import org.apache.ibatis.annotations.Mapper;
/**
* @auther CharlieLiang
* @date 2022/6/4-22:17
*/
@Mapper
public interface UserDao extends BaseMapper<User> {
}Mybatisplus02DqlApplicationTests
package com.example;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.dao.UserDao;
import com.example.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
class Mybatisplus02DqlApplicationTests{
@Autowired
private UserDao userDao;
@Test
void testGetAll(){
//方式一：按条件查询
QueryWrapper qw=new QueryWrapper();
qw.lt("age",15);
List<User> userList=userDao.selectList(qw);
System.out.println(userList);
System.out.println("================================================================");
//方式二：lambda格式按条件查询
QueryWrapper<User> qw2 =new QueryWrapper<>();
qw2.lambda().lt(User::getAge,18);
List<User> userList2=userDao.selectList(qw2);
System.out.println(userList2);
System.out.println("================================================================");
//方式三：lambda格式按条件查询
LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
lqw.gt(User::getAge,18);
lqw.lt(User::getAge,60);
//也可以用链式编程：
// lqw.lt(User::getAge,60).gt(User::getAge,18)
//小于18岁 或者 大于30岁
// lqw.lt(User::getAge,18).or().gt(User::getAge,60)
List<User> userList3=userDao.selectList(lqw);
System.out.println(userList3);
}
}?运行结果：
条件查询，null值处理
新建UserQuery类，继承User类
package com.example.domain.query;
import com.example.domain.User;
import lombok.Data;
/**
* @auther CharlieLiang
* @date 2022/6/5-11:50
*/
@Data
public class UserQuery extends User {
private Integer age2;
}
Mybatisplus02DqlApplicationTests类的?测试方法
在测试方法中，的条件也可以使用链式编程
lqw.lt( null? != uq.getAge2(), User::getAge,uq.getAge2())
.gt( null != uq.getAge(), User::getAge, uq.getAge() );
@Test
void testGetAll2(){
//模拟页面传递过来的查询数据
UserQuery uq=new UserQuery();
uq.setAge(10);
uq.setAge2(30);
//null 判定
LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
//如果uq.getAge() 不为空才查询，
lqw.lt(null != uq.getAge2(),User::getAge,uq.getAge2());
lqw.gt(null != uq.getAge(),User::getAge,uq.getAge());
List<User> userList=userDao.selectList(lqw);
System.out.println(userList);
}运行结果：
?条件查询--条件投影
?测试类的条件查询的方法
//查询投影
@Test
void testGetAll3(){
//查询结果包含模型类中的部分属性
QueryWrapper<User> qw=new QueryWrapper<>();
qw.select("id","name","age","tel");
List<User> userList2=userDao.selectList(qw);
System.out.println(userList2);
System.out.println("------------------------------");
//查询结果包含模型类中未定义的属性
QueryWrapper<User> qw2=new QueryWrapper<>();
qw2.select("count(*) as count ,tel");
qw2.groupBy("tel");
List<Map<String, Object>> maps = userDao.selectMaps(qw2);
System.out.println(maps);
}运行结果：
?
?相等查询
@Test
void test4(){
//条件查询
LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
//等同于=
lqw.eq(User::getName,"Jerry").eq(User::getPassword,"jerry");
User loginUser=userDao.selectOne(lqw);
System.out.println(loginUser);
}
范围查询
@Test
void test5(){
//条件查询
LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
//范围查询 lt le gt ge eq between
lqw.between(User::getAge,10,30);
List<User> userList=userDao.selectList(lqw);
System.out.println(userList);
}模糊查询?
说明：likeLeft：表明%J， likeRight: J%
@Test
void test6(){
LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
//模糊匹配 like
lqw.like(User::getName,"J");
List<User> userList=userDao.selectList(lqw);
System.out.println(userList);
}更多查询使用
条件构造器 | MyBatis-PlusMyBatis-Plus 官方文档
映射匹配兼容性
?User类
package com.example.domain;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.*;
import java.net.PasswordAuthentication;
/**
* @auther CharlieLiang
* @date 2022/6/4-21:57
*/
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ToString
@TableName("user")
public class User {
private Long id;
private String name;
@TableField(value = "password", select = false)
private String password;
private Integer age;
private String tel;
@TableField(exist = false)
private Integer online;
}当类名和表明不一致时，用@TableName() 指明类名
当属性名和数据库的列名不一致时，用 @TableField() 指明列名