一、mybatis单表查询
如果实体类属性名称和数据库字段的名称不一致，会出现<在执行查询时，有的字段会出现null值，但是不会报错! >
1、xml配置
1.1通过resultMap 手动映射
<resultMap type="role">
<!--id:主键字段
property:实体中的属性名称
column:数据库字段的名称-->
<id property="id" column="id"></id>
<!--
result:普通字段手动赋值
property:实体中的属性名称
column:数据库字段的名称-->
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
</resultMap>
<select resultMap="roleMap">
select * from role
</select>1.2如果是因为驼峰式命名导致映射值为空，那么只需要开启驼峰式命名即可
<settings>
<!--开启驼峰式命名-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>2、@注解配置
2.1 一对一
@Data
public class Orders {
private Integer id;
private Integer uid;
private String ordertime;
private double money;
/*
* 查询订单的时候 关联出该订单的所属人员信息
* */
private User user1;
}
/*
* 根据订单号查询订单信息
*
* */
@Select("select * from orders where id =#{id}")
@Results({
@Result(id = true, property = "id", column = "id"),
@Result(property = "user1", javaType = ,
one = @One(select = ""), column = "uid")
})
Orders findOrdersById(Integer id);2.2 一对多
@Data
public class Dept {
private Integer deptno;
private String dname;
private String loc;
private List<Emp> empList;// 该部门下的员工数据
}
public interface DeptMapper {
/*
* 根据部门编号查询部门信息
* select * from emp where deptno = deptno
* */
@Results({
@Result(id = true,property = "deptno",column = "deptno"),
@Result(property = "empList",javaType = ,many = @Many(select = "",fetchType = ),column = "deptno")
})
@Select("select * from dept where deptno=#{deptno}")
Dept findDeptByDeptno(Integer deptno);
}
public interface EmpMapper {
/*select * from emp where deptno =1*/
/*
* 根据部门编号查询该部门下的所属员工信息
* */
@Select("select * from emp where deptno =#{deptno}")
List<Emp> findEmpByDeptno(Integer deptno);
}2.3 多对多
@Data
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
}
@Data
public class User {
private Integer id;
private String username;
private String birthday;
private String sex;
private String address;
/*
* 用户有多种角色
* */
List<Role> roleList;
}
public interface RoleMapper {
/*
* 根据用户的编号查询用户的角色
* */
@Select("select * from role r,user_role ur where r.`id` =ur.`rid` and ur.`uid` =#{id}")
@Results({
@Result(property = "roleName",column = "role_name"),
@Result(property = "roleDesc",column = "role_desc")
})
List<Role> findRoleByUid(Integer id);
}public interface UserMapper {
/*
* 根据用户的Id查询用户的信息 同时关联出用户拥有的角色
* */
@Select("select * from user where id =#{id}")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "roleList",javaType = ,many = @Many(select = ""),column = "id")
})
}
二、多表查询
1.查询一个订单，与此同时查询出该订单所属的用户<一对一>
Order实体类
@Data
public class Orders {
private Integer id;
private Integer uid;
private String ordertime;
private double money;
/*来表示 1对1的关系*/
private User user;
}
<mapper namespace="">
<resultMap type="orders">
<!--id标签 封装主键字段
property表示 java类中的属性名称 column 表示 表中的字段名称
result:普通字段赋值-->
<id property="id" column="id"></id>
<result property="uid" column="uid"></result>
<result property="ordertime" column="ordertime"></result>
<result property="money" column="money"></result>
<!--
来描述1对1关系
association:给1的一方做映射
property:表示 Java类中一的一方的属性名称
javaType:该属性的java的类型
-->
<association property="user" javaType="">
<id property="id" column="uid"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
<select resultMap="ordersMap">
select
*, uid
from
orders o,user u
where o.`uid` =u.`id`
and o.`id`=#{id};
</select>
</mapper>2.<一对多>
@Data
public class Dept {
private Integer deptno;
private String dname;
private String loc;
/*
* 在一个部门下有多个员工
* */
private List<Emp> empList;
}public interface DeptMapper {
/*
* 根据部门名称查询部门信息
* */
Dept findDept(String dname);
}<mapper namespace="">
<!--
autoMapping:自定映射
只要数据表中的字段名称和实体中的属性的名称一致就可以自动进行映射
-->
<resultMap type="dept" autoMapping="true">
<!--主表dept表的赋值-->
<id property="deptno" column="deptno"></id>
<!-- <result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>-->
<!--
property:java类中属性的名称
ofType:集合中泛型的数据类型
-->
<collection property="empList" ofType="emp" autoMapping="true">
<!--从表 多的一方的emp的赋值-->
<id property="empno" column="empno"></id>
<!-- <result property="ename" column="ename"></result>
<result property="salary" column="salary"></result>
<result property="hiredate" column="hiredate"></result>-->
</collection>
</resultMap>
<select resultMap="DeptMap">
select
*
from
emp e,dept d
where = d.`deptno`
and d.`dname` = #{dname}
</select>
</mapper>