mybatis 关联表查询
这段时间由于项目上的需求:需要将数据库中两表关联的数据查询出来展示到前端(包含一对一,一对多);
(1)一对一:
在实体类中维护了另一个类的对象:
这里我以用户(User)和产品(Product)为例:其中get和set的方法我就没有生成了,请自行生成;
实体类:
public class User {
private String id;
private String name;
private String password;
private Product product;
}
public class Product {
private String id;
private String name;
private String code;
private String userId;
private String userName;
}
查询方式:
联表查询:
sql语句:
<select id="selectAll" resultMap="allResultMap" parameterType="java.lang.String" >
select
u.*,p.id p_id,p.name p_name,p.*
from user u,product p
where p.user_id=u.id and u.id=#{id}
</select>
表和实体的映射关系
<resultMap id="allResultMap" type="bz.sunlight.entity.User" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Sat Aug 27 23:42:15 CST 2016.
-->
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<association property="product" javaType="bz.sunlight.entity.Product">
<id column="p_id" property="id" jdbcType="VARCHAR" />
<result column="p_name" property="name" jdbcType="VARCHAR" />
<result column="code" property="code" jdbcType="VARCHAR" />
<result column="user_id" property="userId" jdbcType="VARCHAR" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
</association>
</resultMap>
这里是将查询出来的结果嵌套到实体对象中:
需要说明一点:这里user表和product表都用了id,name作为column名称,所以需要在sql语句中指定别名;否则同名的字段会出现封装到实体类上出错;
还有一种方式是嵌套查询:需要执行两次查询达到我们预期的效果,这里就不做介绍了;
(2)一对多:
需要在实体类的“一”方中维护多方的数组:
需要将User类中维护product类更改为:
private List<Product> products;
sql:
<select id="selectAll" resultMap="allResultMap" parameterType="java.lang.String" >
select
u.*,p.id p_id,p.name p_name,p.*
from user u,product p
where p.user_id=u.id and u.id=#{id}
</select>
表和实体的映射关系
<resultMap id="allResultMap" type="bz.sunlight.entity.User" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Sat Aug 27 23:42:15 CST 2016.
-->
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<collection property="products" ofType="bz.sunlight.entity.Product">
<id column="p_id" property="id" jdbcType="VARCHAR" />
<result column="p_name" property="name" jdbcType="VARCHAR" />
<result column="code" property="code" jdbcType="VARCHAR" />
<result column="user_id" property="userId" jdbcType="VARCHAR" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
</collection>
</resultMap>
这里的collection标签来映射一对多的关系,ofType:指定封装类型;
同样的:如果你的两个表中的column名称有重复,则通过起别名来处理;
到这里:联表查询就Ok了;