官网地址
一对一查询
模型介绍
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对⼀查询的需求 ：查询⼀个订单，与此同时查询出该订单所属的⽤户
对应的sql语句：
select * from orders o,user u where o.uid=u.id;
查询结果
创建实体类
public class Order {
private Integer id;
private Date orderTime;
private Double total;
//每个订单对应唯一一个用户
private User user;
}
public class User {
private Integer id;
private String username;
//每个用户可以有多个订单
private List<Order> orders;
}
创建Dao接口
public interface IOrderMapper {
List<Order> findAll();
}
编写SqlMapConfig配置文件
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<package name="com.elvis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://192.168.137.144:3306/self_mybatis?useSSL=false&characterEncoding=UTF-8"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.elvis.mapper"/>
</mappers>
</configuration>
mapper映射文件
<?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="com.elvis.mapper.IOrderMapper">
<!--<cache type=""></cache>-->
<cache type="org.mybatis.caches.redis.RedisCache"></cache>
<resultMap id="orderMap" type="com.elvis.pojo.Order">
<result column="id" property="id"></result>
<result column="ordertime" property="orderTime"></result>
<result column="total" property="total"></result>
<association property="user" javaType="com.elvis.pojo.User">
<result column="uid" property="id"></result>
<result column="username" property="username"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT * FROM orders o, user_t u WHERE o.`uid`=u.`id`;
</select>
</mapper>
也可以使用下面这种配置方式
<mapper namespace="com.lagou.mapper.OrderMapper">
<resultMap id="orderMap" type="com.lagou.domain.Order">
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * from orders o,user u where o.uid=u.id
</select>
</mapper>
测试方法
@Test
public void findAllTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(resourceAsStream);
SqlSession session = sqlSessionFactory.openSession();
IOrderMapper mapper = session.getMapper(IOrderMapper.class);
List<Order> all = mapper.findAll();
for (Order order : all) {
System.out.println(order);
}
}
测试结果
一对多查询
模型介绍
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对多查询的需求 ：查询⼀个⽤户，与此同时查询出该⽤户具有的订单
对应的sql语句：
SELECT u.*, o.`id` oid, o.`ordertime`,o.`total` FROM user_t u LEFT JOIN orders o ON o.`uid`=u.`id`;
查询结果：
创建IUserDao接口
public interface IUserMapper {
List<User> findAll();
}
编写Mapper映射文件
<?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="com.elvis.mapper.IUserMapper">
<resultMap id="userMap" type="com.elvis.pojo.User">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<collection property="orders" ofType="com.elvis.pojo.Order">
<result column="oid" property="id"></result>
<result column="ordertime" property="orderTime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT u.*, o.`id` oid, o.`ordertime`,o.`total` FROM user_t u LEFT JOIN orders o ON o.`uid`=u.`id`;
</select>
</mapper>
测试方法
@Test
public void findAllUserTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(resourceAsStream);
SqlSession session = sqlSessionFactory.openSession();
IUserMapper mapper = session.getMapper(IUserMapper.class);
List<User> users = mapper.findAll();
for (User user : users) {
System.out.println(user);
}
}
测试结果
多对多查询
模型介绍
⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求 ：查询⽤户同时查询出该⽤户的所有⻆⾊
查询语句：
SELECT u.*, su.`id` rid,su.`rolename`,su.`roleDesc` FROM user_t u LEFT JOIN sys_user_role sur ON u.`id` = sur.`userid` INNER JOIN sys_role su ON sur.`roleid`=su.`id`
查询结果：
创建实体类
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
}
//修改User实体类
public class User {
private Integer id;
private String username;
private List<Order> orders;
private List<Role> roles;
}
创建Dao接口
public interface IUserMapper {
List<User> findAll();
List<User> findAllUserRole();
}
修改Mapper映射文件，添加对应标签
<resultMap id="userRoleMap" type="com.elvis.pojo.User">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<collection property="roles" ofType="com.elvis.pojo.Role">
<result column="rid" property="id"></result>
<result column="rolename" property="roleName"></result>
<result column="roledesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findAllUserRole" resultMap="userRoleMap">
SELECT u.*, su.`id` rid,su.`rolename`,su.`roleDesc` FROM user_t u LEFT JOIN sys_user_role sur ON u.`id` = sur.`userid` INNER JOIN sys_role su ON sur.`roleid`=su.`id`
</select>
测试方法
@Test
public void findAllUserRoleTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(resourceAsStream);
SqlSession session = sqlSessionFactory.openSession();
IUserMapper mapper = session.getMapper(IUserMapper.class);
List<User> users = mapper.findAllUserRole();
for (User user : users) {
System.out.println(user);
}
}
原文链接: https://blog.csdn.net/Kiven_ch/article/details/117838648