距离上次更新差不多有半年了,这半年比较忙。又要上班,还要准备毕业。所以一直没更新。非常抱歉!!!
上篇中大体描述了一下MyBatis的基本配置与应用,这篇就主要说一下MyBatis的查询。
多对一:
先在数据库中建立两个表t_user,t_dept。并自行插入几条数据。
create table t_dept(
id bigint(20) primary key not null auto_increment,
name varchar(500) default null
);
create table t_user(
id bigint(20) primary key not null auto_increment,
name varchar(50) default null,
password varchar(50) default null,
dept_id bigint(20) default null,
constraint fk1 foreign key (dept_id) references t_dept(id)
);
创建pojo类:
User类:
public class User {
private long id;
private String name;
private String password;
private Dept dept;
.........
}
Dept类:
public class Dept {
private long id;
private String name;
.......
}
UserMapper接口
public interface UserMapper {
User getUser(long id);
}
多对一有两种:
1.嵌套结果查询
<!-- 嵌套结果查询 -->
<!--
对象的属性的映射使用:assoiciation
property:对象的属性名称
javaTypy:对象的类型名称
association:所有的属性都要手动的映射
-->
<select id="getUser" parameterType="long" resultMap="userMap">
select u.*,d.name as deptName from t_user u join t_dept d
on u.dept_id=d.id where u.id=#{id}
</select>
<resultMap type="User" id="userMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="password" property="password"/>
<association property="dept" javaType="Dept">
<id column="dept_id" property="id"/>
<result column="deptname" property="name"/>
</association>
</resultMap>
2.嵌套查询
<!-- 嵌套查询 -->
<!--
对象的属性的映射使用:assoiciation
property:对象的属性名称
javaTypy:对象的类型名称
association:所有的属性都要手动的映射
select:嵌套的select语句 id
column:嵌套查询的参数
-->
<select id="getUser" parameterType="long" resultMap="userMap">
select * from t_user where id=#{id}
</select>
<select id="getUserDeppt" parameterType="long" resultType="Dept">
select * from t_dept where id=#{id}
</select>
<resultMap type="User" id="userMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="password" property="password"/>
<association property="dept" javaType="Dept"
select="getUserDeppt" column="dept_id">
<id column="id" property="id"/>
<result column="name" property="name"/>
</association>
</resultMap>
Test方法(Junit):
@Test
public void getUser() throws IOException{
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = factory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.getUser(5l);
System.out.println(user);
session.close();
}
一对多:
数据表不变
准备pojo类:与之前略有不同
User类:
public class User {
private long id;
private String name;
private String password;
.......
}
Dept类:
public class Dept {
private long id;
private String name;
private List<User> userlist;
......
}
同样是两种
1.嵌套结果查询
<!-- 嵌套结果查询 -->
<!--
对象的属性的映射使用:assoiciation
property:对象的属性名称
javaTypy:对象的类型名称
association:所有的属性都要手动的映射
-->
<select id="getAllDept" resultMap="allDept" >
select d.*,u.name as uname,password,
u.id as uid from t_dept d
join t_user u
on d.id=u.dept_id
</select>
<resultMap type="Dept" id="allDept">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="userlist" ofType="User">
<id column="uid" property="id"/>
<result column="uname" property="name"/>
<result column="password" property="password"/>
</collection>
</resultMap>
2.嵌套查询
<!-- 嵌套查询 -->
<!--
对象的属性的映射使用:assoiciation
property:对象的属性名称
javaTypy:对象的类型名称
association:所有的属性都要手动的映射
select:嵌套的select语句 id
column:嵌套查询的参数
-->
<select id="getAllDept" parameterType="long" resultMap="deptMap">
select * from t_dept
</select>
<select id="getDeptUser" parameterType="long" resultType="Dept">
select * from t_user where dept_id=#{id}
</select>
<resultMap type="Dept" id="deptMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="userlist" ofType="User"
select="getDeptUser" column="id">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="password" property="password"/>
</collection>
</resultMap>
一对多与多对一差别并不大,我个人更喜欢用一对多的嵌套结果查询。
谢谢!