Mybatis 一对多关联注解

文章目录

- - 两表关联(Student、Classes)
  - 三表关联查询(User、Role、User_Role)
  - - 过程问题记录

最近学习mybatis，记录下一对多关联注解。

两表关联(Student、Classes)

这里用班级(Classes)和学生(Student)一对多关联，配置如下：

public class Classes {
	public int id;
	public String name;
	public List<Student> studentList;
	//getter/setter
	}

public class Student {
	private int id;
	private String name;
	private Classes classes;
	//getter/setter
	}

public interface ClassesMapper {
	
	@Insert("insert into classes (name) values (#{name})")
	@Options(useGeneratedKeys=true,keyProperty="id")
	public void insertClasses(Classes classes);
	
	@Select("select * from classes where id=#{id}")
	@Results({
		@Result(id=true,property="id",column="id"),
		@Result(property="name",column="name"),
		@Result(property="studentList",column="id",javaType=,
		many=@Many(select=""))
	})
	public Classes selectClasses(int id);
	
	@Delete("delete from classes where id=#{id}")
	public void deleteClasses(int id);
	
}

public interface StudentMapper {
	
	@Insert("insert into student (name,classes_id) values(#{name},#{})")
	@Options(useGeneratedKeys=true,keyProperty="id")
	public void insertStudent(Student student);
	
	@Select("select * from student where id=#{id}")
	@Results({
		@Result(id=true,property="id",column="id"),
		@Result(property="name",column="name"),
		@Result(property="classes",column="classes_id",javaType=,
		one=@One(select=""))
	})
	public Student selectStudent(int id);
	
	@Select("select * from student where classes_id=#{classId}")
	@Results({
		@Result(id=true,property="id",column="id"),
		@Result(property="name",column="name"),
		@Result(property="classes",column="classes_id",javaType=,
		one=@One(select=""))
	})
	public List<Student> selectStudentByClass(int classId);
	
	@Delete("delete from student where id=#{id}")
	public void deleteStudent(int id);
}

这里StudentMapper中，Student中包含classes对象，是多端，通过

		@Result(property="classes",column="classes_id",javaType=,
		one=@One(select="")

把student与classes联系起来，这样查询student时，根据classes_id就可以加上classes查询并加入到student中

在ClassesMapper中查询studentList时，引用了StudentMapper中通过班级Id查询Student的select语句

@Result(property="studentList",column="id",javaType=,
		many=@Many(select="")

其中many语句需要在StudentMapper定义一个根据班级id查学生的select语句，上面的Result的many中column必须是班级表的id，把班级id传给select语句作为参数。

三表关联查询(User、Role、User_Role)

使用MybatisPlus直接生成三个表的bean跟Mapper，用户表、角色表跟用户角色关联表：

@Getter
@Setter
@TableName("t_user")
public class TUser implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "user_id", type = IdType.AUTO)
    private Integer userId;

    private String userName;

    private String password;

    private LocalDateTime createTime;

    private Short status;
}

@Getter
@Setter
@TableName("t_role")
public class TRole implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "role_id", type = IdType.AUTO)
    private Integer roleId;

    private String roleName;

    private String roleDesc;

    private Short status;
}

@Getter
@Setter
@TableName("t_user_role")
public class TUserRole implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "user_role_id", type = IdType.AUTO)
    private Integer userRoleId;

    private Integer userId;

    private Integer roleId;
}

修改过程如下：

给User对象增加List属性，需要添加注解@TableField(exist = false)，mybatiPlus自动生成的Mapper类，在使用其方法进行查询时会动态拼接bean属性到sql中，导致查询时表找不到roles字段报错

    @TableField(exist = false)
    private List<TRole> roles;

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'roles' in 'field list'
### The error may exist in com/user/dao/TUserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT user_id,user_name,password,create_time,status,roles FROM t_user WHERE user_id=?
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'roles' in 'field list'
; bad SQL grammar []] with root cause

java.sql.SQLSyntaxErrorException: Unknown column 'roles' in 'field list'

Mapper修改

增加通过username查询User的语句：selectByUserName
增加使用userId作为参数，通过Role表跟UserRole表关联查询到Role的语句：selectRolesByUserId
增加ResultMap定义，其他Bean属性正常映射表字段，roles属性的column配置为user_id，many指向第2步定义的语句，代表使用user_id作为参数调用selectRolesByUserId

public interface TUserMapper extends BaseMapper<TUser> {
    @Select("")
    @Results(id = "UserResultMap",
            value = {
                    @Result(property = "userId", column = "user_id"),
                    @Result(property = "userName", column = "user_name"),
                    @Result(property = "password", column = "password"),
                    @Result(property = "createTime", column = "create_time"),
                    @Result(property = "status", column = "status"),
                    @Result(property = "roles", column = "user_id", many = @Many(select = "selectRolesByUserId"))
            }
    )
    TUser resultMap();

    @Select({"select u.* from t_user u",
            "where user_name = #{userName}"})
    @ResultMap("")
    TUser selectByUserName(String userName);

    @Select({"SELECT r.* FROM t_role r ",
            "RIGHT JOIN t_user_role ur ON r.role_id = ur.role_id",
            "WHERE ur.user_id = #{userId}"})
    @ResultMap("")
    List<TRole> selectRolesByUserId(Long userId);
}

过程问题记录

一开始想通过直接关联三个表查询出一个User对象，roles属性想通过role_id来查询对应的Role信息，但是这样查出来的数据会有多条，mybatis会报错说查出了多条数据，与预期的selectOne()不符合，经过反复折腾，后面脑子转过来了，user只查一个User表，再通过userId去关联查询Role信息，下面是一开始的错误修改：

public interface TUserMapper extends BaseMapper<TUser> {

    @Select({"select u.*,r.role_id from t_user u",
            "left join t_user_role ur on u.user_id = ur.user_id",
            "left join t_role r on r.role_id = ur.role_id",
            "where user_name = #{userName}"})
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "password", column = "password"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "roles", column = "role_id", javaType = List.class, many = @Many(select = "selectRoleById"))
    }
    )
    public TUser selectByUserName(String userName);

    @Select("select * from t_role where role_id = ${roleId}")
    @ResultMap("")
    public TRole selectRoleById(Integer roleId);
}

由于MybatisPlus是动态映射生成ResultMap，没有默认的ResultMap可以直接引用，所以在增加Role表相关查询时，需要能自己定义ResultMap，这个时候又想着复用它，于是修改RoleMapper类，在其中定义了一个@Results，给它加上id，@Results又必须定义在方法上，一开始随便定义了个方法 void resultMap()，测试查询时说找不到ResultObject的构造方法，debug后才发现方法的返回值不能定义为void，需要指定一个具体类，这里才修改为TRole这个类：

Caused by: org.apache.ibatis.executor.ExecutorException: No constructor found in void matching [java.lang.Integer, java.lang.String, java.lang.String, java.lang.Integer]

下面是一开始的错误修改，返回的是void，不是TRole类：

@Mapper
public interface TRoleMapper extends BaseMapper<TRole> {

    @Select("")
    @Results(id = "TRoleResultMap", value = {
            @Result(property = "roleId", column = "role_id"),
            @Result(property = "roleName", column = "role_name"),
            @Result(property = "roleDesc", column = "role_desc"),
            @Result(property = "status", column = "status")
    })
    void resultMap();
}

还是Results问题，一开始定义的@Results的方法上没有使用@Select(“”)，导致@Results没有被扫描解析，引用时找不到resultMap，下面是一开始的错误修改:

```java
@Mapper
public interface TRoleMapper extends BaseMapper<TRole> {

    @Results(id = "TRoleResultMap", value = {
            @Result(property = "roleId", column = "role_id"),
            @Result(property = "roleName", column = "role_name"),
            @Result(property = "roleDesc", column = "role_desc"),
            @Result(property = "status", column = "status")
    })
    TRole resultMap();
}

秒客网

Mybatis 一对多关联注解

文章目录

两表关联(Student、Classes)

三表关联查询(User、Role、User_Role)

过程问题记录

相关文章