Mybatis映射文件

简介

  1. MyBatis 的映射语句功能强大，相对来说XML 文件就显得相对简单。如果拿它跟具有相同功能的 JDBC 代码进行对比，你会立即发现省掉了将近 95% 的代码。
  2. 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());
	}
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/suit000001/p/13306342.html