多表查询
Mybatis只是对单表查询进行增强,对多表查询没有增强。
在查询用户时,顺便把所有订单查询出来。
- 在User中添加一个不存在的字段,用来进行数据库的映射。
并添加对应的toString方法
@TableField(exist = false)
private List<Order> orders;
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 + '\\'' +
", orders=" + orders +
'}';
}
- 创建
OrderMapper
@Mapper
public interface OrderMapper extends BaseMapper<Order> {}
- 手动书写SQL,通过User.id查询对应的Order
@Mapper
public interface UserMapper extends BaseMapper<User> {
@Select("select * from t_user")
@Results( {
@Result(column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "id", property = "orders", javaType = List.class,
many = @Many(select = "com.example.quick_demo.mapper.OrderMapper.selectById")
),
})
List<User> SelectAllUserAndOrders();
}
- 最后调用方法即可。
代码:
package com.example.mpdemo.entity;
import com.baomidou.mybatisplus.annotation.TableName;
/**
* @Author Fxdll
* @Date 2024/5/7 23:25
* @PackageName:com.example.mpdemo.entity
* @ClassName: Order
* @Description: TODO
* @Version 1.0
*/
@TableName("T_order")
public class Order {
private int id;
private String order_time;
private Double total;
private int uid;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", order_time='" + order_time + '\'' +
", total=" + total +
", uid=" + uid +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOrder_time() {
return order_time;
}
public void setOrder_time(String order_time) {
this.order_time = order_time;
}
public Double getTotal() {
return total;
}
public void setTotal(Double total) {
this.total = total;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
}
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;
/**
* @Author Fxdll
* @Date 2024/5/7 22:53
* @PackageName:com.example.mpdemo.mapper
* @ClassName: OrderMapper
* @Description: TODO
* @Version 1.0
*/
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
//根据用户id查询所有订单
@Select("SELECT * FROM t_order WHERE id = #{uid}")
List<Order> selectByUid(int uid);
//查询所有订单及用户信息
@Select("select * from t_order")
@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<Order> SelectAllOrdersAndUser();
}
package com.example.mpdemo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mpdemo.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* @Author Fxdll
* @Date 2024/5/4 22:54
* @PackageName:com.example.mpdemo.mapper
* @ClassName: UserMapper
* @Description: TODO
* @Version 1.0
*/
//用于操作用户表,Mybatis会根据mapper注解,动态实现UserMapper接口(实现类),动态代理技术
//SPring会自动创建usermapper接口实现类对应的实例
@Mapper
public interface UserMapper{
//查询所有用户
@Select("select * from user")
public List<User> find();
//20240506
//查询用户,根据用户id查询信息
@Select("select * from t_user where id = #{id}")
User selectById(int id);
//查询用户及所有订单
@Select("select * from t_user")
//结果集映射,前面是数据库字段,后面是实体类属性 ,类里的字段
@Results( {
@Result(column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "id", property = "orders", javaType = List.class,
//通过OrderMapper的selectByUid方法查询订单 拷贝全路径
many = @Many(select = "com.example.mpdemo.mapper.OrderMapper.selectByUid")
),
})
List<User> SelectAllUserAndOrders();
//历史202405
// @Select("select * from user where id = #{id}")
@Update("update user set username = #{username},password = #{password},birthday = #{birthday} where id = #{id}")
public int update(User user);
@Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
public int insert(User user);
@Delete("delete from user where id = #{id}")
public int delete(Integer id);
@Select("select * from user where id = #{id}")
public User findById(Integer id);
}
package com.example.mpdemo.controller;
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.*;
import java.util.List;
/**
* @Author Fxdll
* @Date 2024/5/4 22:52
* @PackageName:com.example.mpdemo.controller
* @ClassName: UserController
* @Description: TODO
* @Version 1.0
*/
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
//0506更新--------------------------------------
@GetMapping("/user/findAll")
public List<User> query() {
// List<User> list = userMapper.find();
// System.out.println(list);
// return list;
return userMapper.SelectAllUserAndOrders();
// return userMapper.find();
}
//待更新------------------------------------------
//条件查询
//分页查询
// 0504更新-------------------------------------------
@PostMapping("/user")
public String save(User user) {
int i = userMapper.insert(user);
if (i > 0) {
return "插入成功";
} else {
return "插入失败";
}
}
@PutMapping("/user")
public String update(User user) {
int i = userMapper.update(user);
if (i > 0) {
return "更新成功";
} else {
return "更新失败";
}
}
@DeleteMapping("/user/{id}")
public String delete(@PathVariable("id") int id) {
int i = userMapper.delete(id);
if (i > 0) {
return "删除成功";
} else {
return "删除失败";
}
}
}
分页查询
创建Mybatis配置类
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor paginationInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor);
return mybatisPlusInterceptor;
}
}
直接在Controller中使用Mapper的selectPage方法即可
@GetMapping("/m2")
public IPage m2(){
Page<User> page = new Page<>(0, 2); // 0表示页数,2表示每页多少个
IPage ipage = userMapper.selectPage(page, null);
System.out.println(ipage);
return ipage;
}