我们来看一个实例:
在数据库中创建两个表
一、创建表:
员工表:
DROP TABLE IF EXISTS `tbl_employee`;
CREATE TABLE `tbl_employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) DEFAULT NULL,
`gender` char(1) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`d_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept` (`d_id`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`d_id`) REFERENCES `tbl_dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
部门表:
CREATE TABLE tbl_dept(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(255)
)
二、mybatis动态代理创建相应的实体类、Mapper接口和Xml文件
实体类:
public class tbl_dept { private int id; private String deptName;
//自己添加get和set方法
}
public class tbl_employee { private int id; private String userName; private String gender; private String email; private int dId; private tbl_dept dept;//声明了一个tbl_dept 对象
//自己添加get和set方法 }
mapper接口:
public interface Tbl_employeeMapper { public tbl_employee findTblEmployeeById(int id); }
1.xml 文件:使用association标签
<resultMap type="com.neuedu.bean.tbl_employee" id="findTblEmployeeByIdMap"> <id column="id" property="id" /> <result column="email" property="email" /> <result column="gender" property="gender" /> <result column="user_name" property="userName" /> <result column="d_id" property="dId"/>
<!-- property 属性指定的POJO中关联的其它POJO在本POJO中的属性,JavaType指定的是关联的POJO的全类名-->
<!--id 用于指定主键,result用于指定普通的字段,column用于指定数据库字段,property用于指定POJO中的属性 --> <association property="dept" javaType="com.neuedu.bean.tbl_dept"> <id column="id" property="id"/> <result column="dept_name" property="deptName"/> </association> </resultMap> <select id="findTblEmployeeById" resultMap="findTblEmployeeByIdMap" parameterType="int"> select e.id,e.email,e.gender,e.user_name,e.d_id,d.id did,d.dept_name from tbl_employee e ,tbl_dept d <!-- 这里使用了别名--> where e.id=#{id} and d.id=e.id </select>
2.xml文件使用级联查询
<resultMap type="com.neuedu.bean.tbl_employee" id="findTblEmployeeByIdMap"> <id column="id" property="id" /> <result column="email" property="email" /> <result column="gender" property="gender" /> <result column="user_name" property="userName" /> <result column="d_id" property="dId"/> <result column="did" property="dept.id"/> <result column="dept_name" property="dept.deptName"/> </resultMap> <select id="findTblEmployeeById" resultMap="findTblEmployeeByIdMap" parameterType="int"> select e.id,e.email,e.gender,e.user_name,e.d_id,d.id did,d.dept_name from tbl_employee e ,tbl_dept d where e.id=#{id} and d.id=e.id </select>
3.使用两个查询语句:
<select id="selectDepartment" resultType="com.neuedu.bean.tbl_dept"> select * from tbl_dept where id=#{id} </select> <resultMap type="com.neuedu.bean.tbl_employee" id="findTblEmployeeByIdMap"> <id column="id" property="id" /> <result column="email" property="email" /> <result column="gender" property="gender" /> <result column="user_name" property="userName" /> <!-- select指定上面的statement语句的id column指定的是上面的查询语句的需要的参数 --> <association property="dept" select="selectDepartment" column="d_id"></association> </resultMap> <select id="findTblEmployeeById" resultMap="findTblEmployeeByIdMap" parameterType="int"> select e.id,e.email,e.gender,e.user_name,e.d_id from tbl_employee e where e.id=#{id} </select>