数据库关系分析
一对一查询
1.需求
查询订单关联查询用户信息
- 使用resultType、
OrdersBean.java
package entity;
import java.util.Date;
public class OrdersBean {
private int id;
private int user_id;
private String number;
private Date createtime;
private String note;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "OrdersBean{" +
"id=" + id +
", user_id=" + user_id +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
'}';
}
}
OrdersCustom.java
package pojo;
import entity.OrdersBean;
//通过此类映射订单和用户查询的结果，让此类继承包括字段较多的pojo类
public class OrdersCustom extends OrdersBean {
//添加用户属性
private String username;
private String sex;
private String address;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return super.toString()+"OrdersCustom{" +
"username='" + username + '\'' +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
OrdersMapperCustom.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="dao.OrdersMapperCustom">
<select id="findOrdersUser" resultType="pojo.OrdersCustom">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
</mapper>
OrdersMapperCustom.java
package dao;
import java.util.List;
public interface OrdersMapperCustom {
public List<OrdersMapperCustom> findOrdersUser() throws Exception;
}
OrdersMapperTest.java
import dao.OrdersMapperCustom;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class OrdersMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws IOException {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findOrdersUser() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
List<OrdersMapperCustom> ordersCustom = ordersMapperCustom.findOrdersUser();
System.out.println(ordersCustom);
}
}
运行结果
- 使用resultMap
使用resultMap将查询结果中的订单信息映射到Orders对象中，在orders类中添加User属性，将关联查询出来的用户信息映射到orders对象中的user属性中。
OrdersBean.java
在OrdersBean中注入UserBean对象，并创建对应的get和set方法
OrdersMapperCustom.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="dao.OrdersMapperCustom">
<!--订单查询关联用户的resultMap-->
<resultMap id="OrdersUserResultMap" type="entity.OrdersBean">
<!--配置映射的订单信息-->
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<!--配置映射的关联的用户信息-->
<!--association:用于映射关联查询单个对象的信息
property:要将关联查询的用户信息映射到Orders中哪个属性-->
<association property="userBean" javaType="entity.UserBean">
<!--id:关联查询用户的唯一标识
column：指定唯一标识用户信息的列
javaType：映射到user的那个属性-->
<id column="user_id" property="id"></id>
<result column="username" property="username"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<!--查询订单关联查询用户信息，使用resultType-->
<select id="findOrdersUser" resultType="pojo.OrdersCustom">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
<!--查询订单关联查询用户信息，使用resultMap-->
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
</mapper>
OrdersMapperCustom.java
//查询订单关联查询用户使用resultMap
public List<OrdersBean> findOrdersUserResultMap() throws Exception;
OrdersMapperTest.java
@Test
public void findOrdersUserResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
List<OrdersBean> ordersBeans = ordersMapperCustom.findOrdersUserResultMap();
System.out.println(ordersBeans);
}
运行结果
2.总结
-
resultType
使用resultType实现较为简单，如果pojo中没有包括查询出来的列名，需要增加列名对应的属性，即可完成映射。
如果没有查询结果的特殊要求建议使用resultType。 -
resultMap
需要单独定义resultMap，实现有点麻烦，如果对查询结果有特殊的要求，使用resultMap可以完成将关联查询映射pojo的属性中。 -
resultMap可以实现延迟加载，resultType无法实现延迟加载。
网友评论