简介
- MyBatis 的映射语句功能强大，相对来说XML 文件就显得相对简单。如果拿它跟具有相同功能的 JDBC 代码进行对比，你会立即发现省掉了将近 95% 的代码。
- SQL 映射文件的元素
cache – 给定命名空间的缓存配置。
cache-ref – 其他命名空间缓存配置的引用。
resultMap – 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象。
sql – 可被其他语句引用的可重用语句块。
insert – 映射插入语句
update – 映射更新语句
delete – 映射删除语句
select – 映射查询语
javaBean设置
package com.atguigu.bean;
import java.util.List;
public class Dept {
private Integer did;
private String dname;
private List<Emp> emps;
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
@Override
public String toString() {
return "Dept [did=" + did + ", dname=" + dname + ", emps=" + emps + "]";
}
}
package com.atguigu.bean;
public class Emp {
private Integer eid;
private String ename;
private Integer age;
private String sex;
private Dept dept;
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public Integer getEid() {
return eid;
}
public void setEid(Integer eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Emp [eid=" + eid + ", ename=" + ename +
", age=" + age + ", sex=" + sex + ", dept=" + dept + "]";
}
public Emp(Integer eid, String ename, Integer age,
String sex) {
super();
this.eid = eid;
this.ename = ename;
this.age = age;
this.sex = sex;
}
public Emp() {
super();
// TODO Auto-generated constructor stub
}
}
使用Mybatis完成简单的增删改查
引入映射文件
<!-- 引入映射文件 -->
<mappers>
<!-- <mapper resource="EmpMapper.xml"/>
<mapper resource="DeptMapper.xml" /> -->
<!-- 要求<u>mapper</u>接口和<u>mapper</u>文件在一个包下 -->
<package name="com.atguigu.mapper"/>
</mappers>
select、insert、update、delete
接口
public interface EmpMapper {
//查询eid查询一个员工信息
Emp getEmpByEid(String eid);
//获取所有员工信息
List<Emp> getAllEmp();
//添加员工信息
void addEmp(Emp emp);
//修改员工信息
void updateEmp(Emp emp);
//删除员工信息
void deleteEmp(String emp);
}
映射文件
<mapper namespace="com.atguigu.mapper.EmpMapper">
<!-- <u>Emp</u> getEmpByEid(String <u>eid</u>); -->
<select id="getEmpByEid" resultType="Emp">
select <u>eid</u>,<u>ename</u>,age,sex from <u>emp</u> where <u>eid</u> =
#{<u>eid</u>}
</select>
<!-- List<Emp> getAllEmp(); -->
<select id="getAllEmp" resultType="Emp">
select <u>eid</u>,<u>ename</u>,age,sex from <u>emp</u>
</select>
<!-- void addEmp(<u>Emp</u> <u>emp</u>); -->
<insert id="addEmp">
insert into <u>emp</u>
values(null,#{<u>ename</u>},#{age},#{sex})
</insert>
<!-- void updateEmp(<u>Emp</u> <u>emp</u>); -->
<update id="updateEmp">
update <u>emp</u> set <u>ename</u> = #{<u>ename</u>}, age = #{age},
sex = #{sex} where <u>eid</u> = #{<u>eid</u>}
</update>
<!-- void deleteEmp(String <u>eid</u>); -->
<delete id="deleteEmp">
delete from <u>emp</u> where <u>eid</u> = #{<u>eid</u>}
</delete>
</mapper>
测试
@Test
public void testCRUD() throws IOException {
InputStream is =
Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
//SqlSession sqlSession =
sqlSessionFactory.openSession();//需要手动处理事务
SqlSession sqlSession =
sqlSessionFactory.openSession(true);//自动处理事务
EmpMapper empMapper =
sqlSession.getMapper(EmpMapper.class);
//测试：根据<u>eid</u>获取员工信息
Emp emp = empMapper.getEmpByEid("3");
System.out.println(emp);
//测试：获取所有的员工信息
/*List<Emp> list = empMapper.getAllEmp();
System.out.println(list);*/
//测试：添加员工信息
/*empMapper.addEmp(new <u>Emp</u>(null, "<u>admin</u>", 23,
"女"));
sqlSession.commit();//提交事务*/
//测试：修改员工信息
/* empMapper.updateEmp(new <u>Emp</u>(6, "张二", 33,
"女")); */
//测试：删除员工信息
/*Boolean i = empMapper.deleteEmp("2");
System.out.println("result:"+i);*/
//select 字段名 from 表名 where 条件 group by 字段名 having 条件 order by 字段名 <u>desc</u>/<u>asc</u> limit
index,pageSize
}
参数传递
单个String或基本数据类型和包装类
${}:可以以任意名字获取参数值，#{}：只能以${value}或${_parameter}获取
传递参数为javaBean时
${}和#{}都可以通过属性名直接获取属性值，但要注意单引号问题
<mapper namespace="com.atguigu.mapper.EmpMapper">
<!-- <u>Emp</u> getEmpByEid(String <u>eid</u>); -->
<select id="getEmpByEid" resultType="Emp">
select <u>eid</u>,<u>ename</u>,age,sex from <u>emp</u> where <u>eid</u> =
#{<u>eid</u>}
</select>
<!-- List<Emp> getAllEmp(); -->
<select id="getAllEmp" resultType="Emp">
select <u>eid</u>,<u>ename</u>,age,sex from <u>emp</u>
</select>
<!-- void addEmp(<u>Emp</u> <u>emp</u>); -->
<insert id="addEmp">
insert into <u>emp</u>
values(null,#{<u>ename</u>},#{age},#{sex})
</insert>
<!-- void updateEmp(<u>Emp</u> <u>emp</u>); -->
<update id="updateEmp">
update <u>emp</u> set <u>ename</u> = #{<u>ename</u>}, age = #{age},
sex = #{sex} where <u>eid</u> = #{<u>eid</u>}
</update>
<!-- void deleteEmp(String <u>eid</u>); -->
<delete id="deleteEmp">
delete from <u>emp</u> where <u>eid</u> = #{<u>eid</u>}
</delete>
</mapper>
传输多个参数时，mybatis默认将参数放在map集合中
${}:以0、1、2、....、n或param1,param2,.....,paramN为键，参数为值，#{}：只能以param1,param2,.....,paramN为键，参数为值
<!-- <u>Emp</u> getEmpByEidAndEname(String <u>eid</u> ,String <u>Ename</u>);
-->
<select id="getEmpByEidAndEname" resultType="Emp">
select <u>eid</u>,<u>ename</u>,age,sex from <u>emp</u> where <u>eid</u> =
#{0} and <u>ename</u> = #{1}
</select>
<!-- Emp getEmpByEidAndEname(String eid, String ename); -->
<select id="getEmpByEidAndEname" resultType="Emp">
select eid,ename,age,sex from emp where eid = ${param1} and ename = '${param2}'
</select>
传输Map参数时
${}和#{}都可以通过属性名直接获取属性值，但要注意单引号问题
<!-- Map<String, Object> getEmpMapByEid(String <u>eid</u>); -->
<select id="getEmpMapByEid"
resultType="java.util.HashMap">
select <u>eid</u>,<u>ename</u>,age,sex from <u>emp</u> where <u>eid</u> =
#{<u>eid</u>}
</select>
命名参数
可以通过@Param("key")为map集合指定键的名字
//根据eid和ename查询员工信息
Emp getEmpByEidAndEnameByParam(@Param("eid")String eid, @Param("ename")String ename);
<!-- Emp getEmpByEidAndEnameByParam(@Param("eid")String eid, @Param("ename")String ename); -->
<select id="getEmpByEidAndEnameByParam" resultType="Emp">
select eid,ename,age,sex from emp where eid = #{eid} and ename = #{ename}
</select>
传输参数为List或Array
mybatis会将list或Array放在map中，list以list为键，Array以Array为键，暂且不写。。。。
主键生成与获取
获取主键值
若数据库支持自动生成主键的字段（比如 MySQL 和 SQL Server），则可以设置 useGeneratedKeys=”true”，然后再把 keyProperty 设置到目标属性上。
<insert id="insertEmployee" parameterType="com.atguigu.mybatis.beans.Employee"
databaseId="mysql"
useGeneratedKeys="true"
keyProperty="id">
insert into tbl_employee(last_name,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
select查询多对一
不分步
//获取所有的<u>Emp</u>信息
List<Emp> getAllEmp();
<resultMap type="Emp" id="empMap">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<association property="dept" javaType="Dept">
<id column="did" property="did"/>
<result column="dname" property="dname"/>
</association>
</resultMap>
<!-- List<Emp> getAllEmp(); -->
<select id="getAllEmp" resultMap="empMap">
select e.eid,e.ename,e.age,e.sex,e.did,d.dname
from <u>emp</u> e left join <u>dept</u> d on e.did = d.did
</select>
分步
//通过did查询dept
Dept getDeptByDid(String did);
//分步查询
Emp getEmpStep(String eid);
<!-- <u>Emp</u> getEmpStep(String <u>eid</u>); -->
<select id="getEmpStep" resultMap="empMapStep">
select <u>eid</u>,<u>ename</u>,age,sex,did from <u>emp</u> where <u>eid</u>
= #{<u>eid</u>}
</select>
<!--
<resultMap>：自定义映射，处理复杂的表关系
-->
<resultMap type="Emp" id="empMapStep">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<!--
select:分步查询的SQL的id，即接口的全限定名.方法名或namespace.SQL的id
column:分步查询的条件，注意：此条件必须是从数据库查询过得
-->
<association property="dept"
select="com.atguigu.mapper.DeptMapper.getDeptByDid"
column="did"/>
</resultMap>
测试
@Test
public void testParam() throws IOException {
InputStream is =
Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession =
sqlSessionFactory.openSession(true);
EmpDeptMapper mapper =
sqlSession.getMapper(EmpDeptMapper.class);
//查询两表连接
/*
* List<Emp> <u>emp</u> = mapper.getAllEmp();
System.out.println(<u>emp</u>);
*/
//分步查询
/*
* <u>Emp</u> <u>emp</u> = mapper.getEmpStep("3");
System.out.println(<u>emp</u>);
*/
//查询两表连接，1对多
/*
* <u>Dept</u> <u>dept</u> = mapper.getDeptEmpsByDid("3");
System.out.println(<u>dept</u>);
*/
//1对多,分步
Dept dept = mapper.getOnlyDeptByDid("1");
System.out.println(dept);
}
select查询1对多
不分步
//查询两表连接，1对多
Dept getDeptEmpsByDid(String did);
<resultMap type="Dept" id="deptMap">
<id column="did" property="did"/>
<result column="dname" property="dname"/>
<collection property="emps" ofType="Emp">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
</collection>
</resultMap>
<!-- <u>Dept</u> getDeptEmpsByDid(String did); -->
<select id="getDeptEmpsByDid" resultMap="deptMap">
select d.did,d.dname,e.eid,e.ename,e.age,e.sex
from <u>dept</u> d left join <u>emp</u> e on d.did = e.did where d.did
= #{did}
</select>
分步
//获取一个表的信息
Dept getOnlyDeptByDid(String did);
//获取员工信息
List<Emp> getEmpListByDid(String did);
<!-- <u>Dept</u> getOnlyDeptByDid(String did); -->
<select id="getOnlyDeptByDid"
resultMap="deptMapStep">
select did,<u>dname</u> from <u>dept</u> where did = #{did}
</select>
<!-- List<Emp> getEmpListByDid(String did); -->
<select id="getEmpListByDid" resultType="Emp">
select <u>eid</u>,<u>ename</u>,age,sex from <u>emp</u> where did =
#{did}
</select>
<resultMap type="Dept" id="deptMapStep">
<id column="did" property="did"/>
<result column="dname" property="dname"/>
<association property="Emp"
select="com.atguigu.mapper.EmpDeptMapper.getEmpListByDid"
column="did"/>
</resultMap>
测试
@Test
public void testParam() throws IOException {
InputStream is =
Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession =
sqlSessionFactory.openSession(true);
EmpDeptMapper mapper =
sqlSession.getMapper(EmpDeptMapper.class);
//查询两表连接
/*
* List<Emp> <u>emp</u> = mapper.getAllEmp();
System.out.println(<u>emp</u>);
*/
//分步查询
/*
* <u>Emp</u> <u>emp</u> = mapper.getEmpStep("3");
System.out.println(<u>emp</u>);
*/
//查询两表连接，1对多
/*
* <u>Dept</u> <u>dept</u> = mapper.getDeptEmpsByDid("3");
System.out.println(<u>dept</u>);
*/
//1对多,分步
Dept dept = mapper.getOnlyDeptByDid("1");
System.out.println(dept);
}
分步查询使用延迟加载
在分步查询的基础上，可以使用延迟加载来提升查询的效率，只需要在全局的Settings中进行如下的配置:
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 设置加载的数据是按需还是全部 -->
<setting name="aggressiveLazyLoading" value="false"/>
@Test
public void testSelect() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//SqlSession sqlSession = sqlSessionFactory.openSession();//需要手动处理事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动处理事务
EmpDeptMapper mapper = sqlSession.getMapper(EmpDeptMapper.class);
Dept dept = mapper.getOnlyDeptByDid("1");
System.out.println(dept.getDname());
System.out.println("=======================");
System.out.println(dept.getEmps());
}