Mybatis多表操作、注解开发
1、一对一查询
当查询一个表，需要把另一个表的数据也同时查询出来时
例如：查询订单表时，把用户的信息也显示出来
有两个实体类User、Order和对应的数据库表
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//省略set、get、tostring方法
}
public class Order {
private int id;
private Date ordertime;
private String total;
//代表当前订单数据哪个用户
private User user;
//省略set、get、tostring方法
}
mapper.xml文件：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.guixinchn.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<id property="id" column="oid"/>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
<!--<result property="user.id" column="uid"/>-->
<!--<result property="user.username" column="username"/>-->
<!--<result property="user.password" column="password"/>-->
<!--<result property="user.birthday" column="birthday"/>-->
<association property="user" javaType="user">
<id property="id" column="uid"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="birthday" column="birthday"/>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT * ,o.`id` AS oid FROM orders AS o,USER AS u WHERE o.`uid`=u.`id`
</select>
</mapper>
结果：
2、一对多查询
查询一个用户，与此同时查询出该用户具有的订单
有两个实体类User、Order和对应的数据库表
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
//省略set、get、tostring方法
}
public class Order {
private int id;
private Date ordertime;
private String total;
//省略set、get、tostring方法
}
mapper.xml文件：
<resultMap id="userMap" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="birthday" column="birthday"/>
<!--ofType指定的是 映射到list集合属性中pojo的类型-->
<collection property="orderList" ofType="order">
<id property="id" column="oid"/>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT *,o.id oid FROM USER u LEFT JOIN orders o ON u.id=o.uid
</select>
3、多对多查询
一个用户有多个角色，一个角色被多个用户使用
当需要查询用户同时查询出该用户的所有角色时
有两个实体类User、Role和对应的数据库表
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些角色
private List<Role> roleList;
//省略set、get、tostring方法
}
public class Role {
private int id;
private String roleName;
private String roleDesc;
//省略set、get、tostring方法
}
mapper.xml文件：
<resultMap id="userRole" type="user">
<!--user信息-->
<id property="id" column="userId"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="birthday" column="birthday"/>
<!--roleList信息-->
<collection property="roleList" ofType="cn.guixinchn.domain.Role">
<id property="id" column="roleId"/>
<result property="roleName" column="roleName"/>
<result property="roleDesc" column="roleDesc"/>
</collection>
</resultMap>
<select id="findUserRoleAll" resultMap="userRole">
SELECT * FROM USER u , sys_user_role ur , sys_role r WHERE u.`id`= ur.`userId` AND r.`id`= ur.`roleId`
</select>
4、Mybatis注解开发
-
@Insert：实现新增
-
@Update：实现更新
-
@Delete：实现删除
-
@Select：实现查询
-
@Result：实现结果集封装，代替，
<id>、<result>- column：数据库的列名
- property：需要装配的属性名
- one：需要使用的@One 注解（@Result（one=@One）（）））
- many：需要使用的@Many 注解（@Result（many=@many）（）））
-
@Results：可以与@Result 一起使用，封装多个结果集，代替
<resultMap> -
@One：实现一对一结果集封装，代替
<assocation>使用格式：@Result(column=" ",property="",one=@One(select=""))
-
@Many：实现一对多结果集封装，代替
<collection>使用格式：@Result(property="",column="",many=@Many(select=""))
首先修改MyBatis核心配置文件，加载使用了注解的Mapper接口
<mappers>
<!--扫描使用注解的类-->
<!--<mapper class="cn.guixinchn.mapper.UserMapper"/>-->
<!--或者 扫描使用注解的类所在的包-->
<package name="cn.guixinchn.mapper"/>
</mappers>
然后在Mapper接口的方法上，加上特定的方法实现对数据库表的操作
例如：
@Select("select * from user")
List<User> findAllTest();
@Select("select * from user where id = #{id}")
User findByIdTest(int id);
@Insert("insert into user values(#{id},#{username},#{password},#{birthday});")
void saveTest(User user);
@Delete("delete from user where id = #{id};")
void deleteTest(int id);
@Update("update user set username=#{username},password=#{password} where id = #{id};")
void updateTest(User user);
5、MyBatis的注解实现复杂映射开发
5.1、一对一查询
当查询一个表，需要把另一个表的数据也同时查询出来时
例如：查询订单表时，把用户的信息也显示出来
有两个实体类User、Order和对应的数据库表
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//省略set、get、tostring方法
}
public class Order {
private int id;
private Date ordertime;
private String total;
//代表当前订单数据哪个用户
private User user;
//省略set、get、tostring方法
}
在对应的Mapper接口上，用注解方式写数据库查询语句
public interface OrderMapper {
@Select("select * from orders")
@Results({
@Result(id=true,property = "id", column = "id"),
@Result(property = "ordertime", column = "ordertime"),
@Result(property = "total", column = "total"),
@Result(property = "user", column = "uid", javaType = User.class,
one = @One(select = "cn.guixinchn.mapper.UserMapper.findUserById"))
})
List<Order> findAllOrder();
}
5.2、一对多查询
查询一个用户，与此同时查询出该用户具有的订单
有两个实体类User、Order和对应的数据库表
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
//省略set、get、tostring方法
}
public class Order {
private int id;
private Date ordertime;
private String total;
//省略set、get、tostring方法
}
在对应的Mapper接口上，用注解方式写数据库查询语句
public interface OrderMapper {
@Select("select * from orders where uid = #{uid}")
List<Order> findOrderByUid(int uid);
}
public interface UserMapper {
@Select("select * from user")
@Results({
@Result(id=true, property = "id", column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "password",column = "password"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "orderList",column = "id", javaType = List.class,many = @Many(select = "cn.guixinchn.mapper.OrderMapper.findOrderByUid"))
})
List<User> findUserRoleAll();
}
5.3、多对多查询
一个用户有多个角色，一个角色被多个用户使用
当需要查询用户同时查询出该用户的所有角色时
有两个实体类User、Role和对应的数据库表
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些角色
private List<Role> roleList;
//省略set、get、tostring方法
}
public class Role {
private int id;
private String roleName;
private String roleDesc;
//省略set、get、tostring方法
}
在对应的Mapper接口上，用注解方式写数据库查询语句
public interface RoleMapper {
@Select("SELECT * FROM sys_role r,sys_user_role ur WHERE r.id=ur.roleId AND ur.userId=#{uid}")
List<Role> findRoleByUid(int uid);
}
public interface UserMapper {
@Select("select * from user")
@Results({
@Result(id=true, property = "id", column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "password",column = "password"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "roleList",column = "id",javaType = List.class,many = @Many(select = "cn.guixinchn.mapper.RoleMapper.findRoleByUid"))
})
List<User> findUserRoleAll();
}
