注解版多表自定义填充查询

1. 方法一(pojo)

1.1 Domain

定义pojo方法

@Data//Get Set 方法
@NoArgsConstructor//无参
@AllArgsConstructor//有参
@ToString // toString
public class Pojo {
    private Integer id;// NOT NULL
    private String loginName;//用户名
    private String password;//密码
    private String status;//状态
    private Integer roleId;
    private String name;
}

1.2 dao(mapper)

@Select("SELECT * FROM tb_admin a , tb_admin_role ar , tb_role r WHERE a.id = ar.`admin_id` AND ar.`role_id` = r.`id`")
//SQL语句 , 数据用pojo类接受 
@Results({
        @Result(property = "loginName",column = "login_name"),
})
List<Pojo> userList();

2. 方法二(填充中间表)

2.1 有中间表

2.1.1 保存数据

中间表接受数据

中间表加两表的对象属性,自动填充

2.1.2 返回数据集合(查询所有)

自定义填充

2.2 无中间表

2.2.1 保存数据

在主表加从表的字段,以主表接受数据

2.2.2 返回数据集合(查询所有)

返回中间表集合,自定义填充从表中的属性

3. 条件查询

3.1 Mapper(接口)

@Select("<script>select * from t_employee " +
        "where 0 = 0" +
        "<if test=\"employeeVo.depId != null and employeeVo.depId != '' \"> and dep_id = #{employeeVo.depId} </if>"+
        "<if test=\"employeeVo.empName != null and employeeVo.empName != '' \"> and emp_name = #{employeeVo.empName} </if>"+
        "<if test=\"employeeVo.ageStart != null and employeeVo.ageStart != '' \"> and age >= #{employeeVo.ageStart} </if>"+
        "<if test=\"employeeVo.ageEnd != null and employeeVo.ageEnd != '' \"> and age &lt;= #{employeeVo.ageEnd} </if>"+
        "</script>")
@Results({
        @Result(property = "depId",column = "dep_id"),
        @Result(property = "department",column = "dep_id" , one = @One(select = "com.czxy.mapper.DepartmentMapper.selectByPrimaryKey")),
        @Result(property = "empId",column = "emp_id"),
        @Result(property = "empName",column = "emp_name"),
})
List<Employee> selectByVo(@Param("employeeVo") EmployeeVo vo);

3.1.1 条件查询模板

@Select("<script>select * from t_employee " +
        "where 0 = 0" + 
        "<if test=\"employeeVo.depId != null and employeeVo.depId != '' \"[于2] > and dep_id  = #{employeeVo.depId[于4] }  </if>"+
        "<if test=\"employeeVo.empName != null and employeeVo.empName != '' \"> and emp_name = #{employeeVo.empName} </if>"+
        "<if test=\"employeeVo.ageStart != null and employeeVo.ageStart != '' \"> and age >= #{employeeVo.ageStart} </if>"+
        "<if test=\"employeeVo.ageEnd != null and employeeVo.ageEnd != '' \"> and age &lt;[于6] = #{employeeVo.ageEnd} </if>"+
        "</script>")
List<Employee> selectByVo(@Param("employeeVo")EmployeeVo vo);
 

<![CDATA[>]]>

大于小号

尖括号 尖括号里边两个中括号嵌套 第一个中括号里边写CDATA(大写) 嵌套的中括号里边写大于号或者小于号