/**
* 分页查询
* @return
*/
@RequestMapping("/get0")
@ResponseBody
public String test0(){
LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
// 查询姓名中包含a的
userLambdaQueryWrapper.like(User::getUsername , "a");
// 查看第1页，每页3条
Page<User> userPage = new Page<>(1 , 3);
IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
System.out.println("总页数： "+userIPage.getPages());
System.out.println("总记录数： "+userIPage.getTotal());
// 打印出第1页数据
userIPage.getRecords().forEach(System.out::println);
return "aaa";
}
/**
* 不记录总记录数、不记录总页数
* @return
*/
@RequestMapping("/get1")
@ResponseBody
public String test1(){
LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
userLambdaQueryWrapper.like(User::getUsername , "a");
Page<User> userPage = new Page<>(1 , 2 , false);
IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
System.out.println("总页数： "+userIPage.getPages());
System.out.println("总记录数： "+userIPage.getTotal());
userIPage.getRecords().forEach(System.out::println);
return "aaa";
}
/**
* selectMapsPage()的用法
* @return
*/
@RequestMapping("/get2")
@ResponseBody
public String test2(){
LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
// 查询用户名包含a的
userLambdaQueryWrapper.select(User::getUsername).like(User::getUsername , "a");
// 查询1页，每页2条
Page<Map<String , Object>> mapPage = new Page<>(1 , 2 , false);
IPage<Map<String , Object>> mapIPage = userMapper.selectMapsPage(mapPage , userLambdaQueryWrapper);
System.out.println("总页数： "+mapIPage.getPages());
System.out.println("总记录数： "+mapIPage.getTotal());
// 打印查询到的数据
mapIPage.getRecords().forEach(System.out::println);
return "aaa";
}
/**
* 自定义sql
* @return
*/
@RequestMapping("/get3")
@ResponseBody
public String test3(){
// 构建wrapper对象
LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
// 查询用户名包含a的
userLambdaQueryWrapper.like(User::getUsername , "a");
// 查询第1页，每页2条
Page<User> mapPage = new Page<>(1 , 2 , false);
// 调用mapper层方法
IPage<User> mapIPage = userMapper.selectByPage(mapPage , userLambdaQueryWrapper);
System.out.println("总页数： "+mapIPage.getPages());
System.out.println("总记录数： "+mapIPage.getTotal());
// 打印出查询结果
mapIPage.getRecords().forEach(System.out::println);
return "aaa";
}
@Component
public interface UserMapper extends BaseMapper<User> {
IPage<User> selectByPage(IPage<User> userPage, @Param(Constants.WRAPPER) Wrapper<User> userWrapper);
}
<select id="selectByPage" resultType="com.ychen.mybatis.demo01.model.User">
select * from user ${ew.customSqlSegment}
</select>
@GetMapping("/findUser")
@ResponseBody
public String testFind(){
List<UserBookPo> users = userMapper.findUser();
System.out.println(users);
return "aaa";
}
@GetMapping("/findUsers")
@ResponseBody
public String testLimit(){
List<User> users = userMapper.findUsers();
System.out.println(users);
return "aaa";
}
/**
* http://localhost:8080/test/listAll
* {
* "username": "chen1",
* "bookname": "book1"
* }
* @param req
* @return
*/
@RequestMapping(value = "/listAll", method = RequestMethod.POST)
@ResponseBody
public String testList(@RequestBody UserBookReqDTO req){
List<UserBookRespDTO> list = userMapper.getUserBook(req);
System.out.println(list);
return "aaa";
}
// 分页查询
@RequestMapping("/get0")
@ResponseBody
public String test0(){
// 构建wrapper对象
LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
// 查询用户名包含a的
userLambdaQueryWrapper.like(User::getUsername , "c");
// 查询第1页，每页2条
Page<User> mapPage = new Page<>(1 , 2 , false);
// 调用mapper层方法
IPage<User> mapIPage = userMapper.selectByPage(mapPage , userLambdaQueryWrapper);
System.out.println("总页数： "+mapIPage.getPages());
System.out.println("总记录数： "+mapIPage.getTotal());
// 打印出查询结果
mapIPage.getRecords().forEach(System.out::println);
return "aaa";
}
/**
* 分页连接
*
* http://localhost:8080/test/listAll
* * {
* * "username": "chen1",
* * "bookname": "book1"
* * }
* @param req
* @return
*/
@RequestMapping("/get1")
@ResponseBody
public String test1(@RequestBody UserBookReqDTO req){
int pageNo = 1;
int pageSize = 3;
String username = req.getUsername();
String sex = req.getSex();
String bookname = req.getBookname();
// 构建wrapper对象
QueryWrapper<UserBookRespDTO> wrapper = new QueryWrapper<>();
// 构建page对象
Page<UserBookRespDTO> page = new Page<UserBookRespDTO>(pageNo, pageSize);
// 调用mapper层方法
IPage<UserBookRespDTO> userPageList = userMapper.getUserBooks(username, sex, bookname, page, wrapper);
System.out.println(userPageList);
return "aaa";
}
// 连接查询
@SelectProvider(type = findUserProvider.class, method = "findUser")
List<UserBookPo> findUser();
class findUserProvider {
public String findUser() {
String sql = new SQL()
.SELECT("u.username", "u.password", "b.bookname", "b.author")
.FROM("user u")
.INNER_JOIN("book b")
.WHERE("u.username = b.author")
.toString();
return sql;
}
}
// 分页查询
@SelectProvider(type = findUsersProvider.class, method = "findUsers")
List<User> findUsers();
class findUsersProvider {
public String findUsers() {
String sql = new SQL()
.SELECT("id", "username", "password", "sex", "birthday")
.FROM("user")
.LIMIT("2, 3")
.toString();
return sql;
}
}
// 分页连接查询
@SelectProvider(type = getUserBookProvider.class, method = "getUserBook")
List<UserBookRespDTO> getUserBook(UserBookReqDTO user);
class getUserBookProvider {
public String getUserBook(UserBookReqDTO user) {
SQL sql = new SQL();
sql.SELECT("u.id", "u.username", "u.password", "u.sex", "u.birthday", "b.id", "b.bookname", "b.author", "b.price")
.FROM("user u").INNER_JOIN("book b").WHERE("u.username = b.author");
if(user.getUsername() != null) {
sql.SET("username=#{user.username}");
}
if(user.getSex() != null) {
sql.SET("sex = #{user.sex}");
}
if(user.getBookname() != null) {
sql.SET("bookname = #{user.bookname}");
}
if(user.getAuthor() != null) {
sql.SET("author = #{user.author}");
}
if(user.getPrice() != null) {
sql.SET("price = #{user.price}");
}
if(user.getPageSize() != null && user.getPageNum() != null) {
sql.LIMIT("#{user.pageNum}, #{user.pageSize}");
}
return sql.toString();
}
}
// 分页
IPage<User> selectByPage(IPage<User> userPage, @Param(Constants.WRAPPER) Wrapper<User> userWrapper);
// 分页连接
IPage<UserBookRespDTO> getUserBooks(@Param("username") String username, @Param("sex") String sex, @Param("bookname") String bookname, @Param("page") Page<UserBookRespDTO> page, @Param(Constants.WRAPPER) Wrapper<UserBookRespDTO> wrapper);
<select id="selectByPage" resultType="com.ychen.mybatis.demo02.model.User">
select * from user ${ew.customSqlSegment}
</select>
<select id="getUserBooks" resultType="com.ychen.mybatis.demo02.model.dto.UserBookRespDTO">
SELECT
u.id,
u.username,
u.password,
u.sex,
u.birthday,
b.bookname,
b.author,
b.price
FROM
user AS u
LEFT JOIN book AS b ON u.username = b.author
<where>
<if test="username !=null and username!=''">
or u.username like concat('%',#{username},'%')
</if>
<if test="sex !=null and sex!=''">
or u.sex like concat('%',#{sex},'%')
</if>
<if test="bookname != null and bookname!=''">
or b.bookname like concat('%',#{bookname}'%')
</if>
</where>
${ew.customSqlSegment}
</select>
public interface UserDao extends BaseMapper<User> {
}
/**
* 单表查询所有
* @return
*/
@GetMapping("/test1")
@ResponseBody
public String test1() {
List<User> users = userDao.selectList(null);
users.forEach(user-> System.out.println("user = " + user));
return "success";
}
/**
* 单表，根据id查询
* @return
*/
@GetMapping("/test2")
@ResponseBody
public String test2() {
User user = userDao.selectById("1");
System.out.println("user = " + user);
return "success";
}
/**
* 条件查询
* @return
*/
@GetMapping("/test3")
@ResponseBody
public String test3() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// 设置等值查询，即查询age=23的对象
queryWrapper.eq("age",23);
// 设置小于查询
//queryWrapper.lt("age",23);
// 小于等于查询
//queryWrapper.ge("age",23);
// gt 大于
// ge 大于等于
List<User> users = userDao.selectList(queryWrapper);
users.forEach(user-> System.out.println(user));
return "success";
}
/**
* 模糊查询
* like 表示包含某个字符
* likeLeft 表示以某个字符结尾
* likeRight 表示以某个字符开头的
* @return
*/
@GetMapping("/test4")
@ResponseBody
public String test4() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name","g");
List<User> users = userDao.selectList(queryWrapper);
users.forEach(user-> System.out.println("user = " + user));
return "success";
}
/**
* 添加
* @return
*/
@GetMapping("/test5")
@ResponseBody
public String test5() {
User entity = new User();
entity.setId("4").setAge(23).setName("小明明").setBir(new Date());
userDao.insert(entity);
return "success";
}
/**
* 删除
* @return
*/
@GetMapping("/test6")
@ResponseBody
public String test6() {
// 删除单个
//userDao.deleteById("4");
// 通过id批量删除
userDao.deleteBatchIds(Arrays.asList(5, 6));
return "success";
}
/**
* 修改
* @return
*/
@GetMapping("/test7")
@ResponseBody
public String test7() {
User user = userDao.selectById("1");
user.setAge(99);
userDao.updateById(user);
return "success";
}
/**
* 按条件修改
* 将age=99的 用户名改为狗蛋
* @return
*/
@GetMapping("/test8")
@ResponseBody
public String test8() {
User user = userDao.selectById("1");
user.setName("狗蛋");
QueryWrapper<User> updateWrapper = new QueryWrapper<>();
// 修改age=99的对象
updateWrapper.eq("age",99);
userDao.update(user, updateWrapper);
return "success";
}
/**
* 分页查询
* @return
*/
@GetMapping("/test9")
@ResponseBody
public String test9() {
// new一个page对象，传入2个参数，第一个参数表示页数，第二个参数表示每页多少条数据
IPage<User> page = new Page<>(1,2);
// 调用分页的方法
page = userDao.selectPage(page, null);
page.getRecords().forEach(user -> System.out.println("user = " + user));
return "success";
}
/**
* 带条件的分页查询
* 查询age=99的对象
* @return
*/
@GetMapping("/test10")
@ResponseBody
public String test10() {
// new一个条件对象，例如age=99
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("age",99);
// new一个page对象
IPage<User> page = new Page<>(1,2);
// 调用分页的方法，传入2个参数
page = userDao.selectPage(page, queryWrapper);
page.getRecords().forEach(user-> System.out.println("user = " + user));
return "success";
}
