1.多表查询
在多表查询中，mybatis和mybatis plus无区别
User:
package com.example.mpdemo.entity; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import java.util.List; @TableName("user") public class User{ @TableId(type= IdType.AUTO) private int id; private String username; private String password; private String birthday; //描述用户的所有订单 @TableField(exist = false) private List<Order> orders; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getBirthday() { return birthday; } public void setBirthday(String birthday) { this.birthday = birthday; } public List<Order> getOrders() { return orders; } public void setOrders(List<Order> orders) { this.orders = orders; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", birthday='" + birthday + '\'' + ", orders=" + orders + '}'; } }
UserController:
package com.example.mpdemo.controller; import com.alibaba.druid.pool.ha.selector.StickyDataSourceHolder; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.example.mpdemo.entity.User; import com.example.mpdemo.mapper.UserMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController public class UsrController { @Autowired UserMapper userMapper; @GetMapping("/user/findAll") public List<User> find(){ return userMapper.selectAllUserAndOrders(); } }
UserMapper:
package com.example.mpdemo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.mpdemo.entity.User; import org.apache.ibatis.annotations.*; import org.springframework.stereotype.Repository; import java.util.List; @Mapper @Repository public interface UserMapper extends BaseMapper<User> { //查询用户，根据用户id查询信息 select * from user where id= @Select("select * from user where id=#{id}") User selectById(int id); //查询用户及其所有订单 @Select("select * from user") @Results( { @Result(column = "id",property = "id"), @Result(column = "username",property = "username"), @Result(column = "password",property = "password"), @Result(column = "birthday",property = "birthday"), @Result(column = "id",property = "orders",javaType = List.class, //many：一个用户可以有多个订单 many=@Many(select = "com.example.mpdemo.mapper.OrderMapper.selectByUid" ) ) } ) List<User> selectAllUserAndOrders(); }
Order:
package com.example.mpdemo.entity; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import java.util.List; @TableName("orderss") public class Order { @TableId(type= IdType.AUTO) private int id; private String orderTime; private int total; private int uid; //描述用户的所有订单 select id,username,password,orderss from user @TableField(exist = false) private User user; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getOrderTime() { return orderTime; } public void setOrderTime(String orderTime) { this.orderTime = orderTime; } public int getTotal() { return total; } public void setTotal(int total) { this.total = total; } public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } @Override public String toString() { return "Order{" + "id=" + id + ", orderTime='" + orderTime + '\'' + ", total='" + total + '\'' + ", uid=" + uid + ", user=" + user + '}'; } }
OrderController:
package com.example.mpdemo.controller; import com.example.mpdemo.mapper.OrderMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import org.apache.ibatis.annotations.Mapper; import javax.annotation.Resource; import java.util.List; @RestController public class OrderController { @Autowired private OrderMapper orderMapper; @GetMapping("/order/findAll") public List findAll(){ List orders=orderMapper.selectAllUserAndOrders(); return orders; } }
OrderMapper:
package com.example.mpdemo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.mpdemo.entity.Order; import com.example.mpdemo.entity.User; import org.apache.ibatis.annotations.*; import java.util.List; @Mapper public interface OrderMapper extends BaseMapper<Order> { @Select("select * from orderss where uid=#{uid}") List<Order> selectByUid(int uid); //查询所有订单，同时查询订单的用户 @Select("select * from orderss") @Results( { @Result(column = "id",property = "id"), @Result(column = "orderTime",property = "orderTime"), @Result(column = "total",property = "total"), @Result(column = "uid",property = "user",javaType = User.class, //一个订单只能被一个用户拥有 one=@One(select = "com.example.mpdemo.mapper.UserMapper.selectById" ) ) } ) List<User> selectAllUserAndOrders(); }
注意！！！！一直查不出order可以把表名字改成orderss。（因为order是sql的关键字）
测试：
2.条件查询
UserConrtroller中加入：
//条件查询 @GetMapping("/user/find") public List<User> findByCond(){ QueryWrapper<User> queryWrapper=new QueryWrapper(); queryWrapper.eq("username","zhangsan"); return userMapper.selectList(queryWrapper); }
测试：
3.分页查询 （通过拦截器实现）
MyBatisPlusConfig：
package com.example.mpdemo.config; import com.baomidou.mybatisplus.annotation.DbType; import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration public class MyBatisPlusConfig { @Bean public MybatisPlusInterceptor paginationInterceptor(){ MybatisPlusInterceptor interceptor=new MybatisPlusInterceptor();//分页拦截器 PaginationInnerInterceptor paginationInterceptor=new PaginationInnerInterceptor(DbType.MYSQL);//分页拦截器 interceptor.addInnerInterceptor(paginationInterceptor); return interceptor; } }
测试：（从0开始，查两个记录）
