MyBatis 映射文件

映射文件

1、MyBatis 的真正强大在于它的映射语句，也是它的魔力所在。由于它的异常强大，映射器的 XML 文件就显得相对简单。如果拿它跟具有相同功能的 JDBC 代码进行对比，你会立即发现省掉了将近 95% 的代码。MyBatis 就是针对 SQL 构建的，并且比普通的方法做的更好2、SQL 映射文件有很少的几个顶级元素（按照它们应该被定义的顺序，不按上下顺序写或报错）： cache – 给定命名空间的缓存配置。cache-ref – 其他命名空间缓存配置的引用。resultMap – 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象。parameterMap – 已废弃！ 老式风格的参数映射。内联参数是首选,这个元素可能在将来被移除，这里不会记录。sql – 可被其他语句引用的可重用语句块。insert – 映射插入语句update – 映射更新语句delete – 映射删除语句select – 映射查询语

代码实例

这里我们先说最常见的insert 、update 、delete 、select

bean public class Employee { private Integer id; private String lastName; private String email; private String gender; private Department department; public Employee() { super(); // TODO Auto-generated constructor stub } public Employee(Integer id, String lastName, String email, String gender, Department department) { super(); this.id = id; this.lastName = lastName; this.email = email; this.gender = gender; this.department = department; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } @Override public String toString() { return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + ", department=" + department + "]"; } } Dao接口 在使用mybatis时我们不用为Dao层写实现类，通过配置文件来实现类中的方法。 public interface EmployeeMapper { /** * 通过id查询单个员工 * @param id */ public Employee queryEmpById(int id); /** * 查询所有员工 */ public List<Employee> queryEmpAll(); /** * 添加员工 * @param id */ public void addEmp(Employee emp); /** * 修改员工信息 * @param id */ public void updateEmp(Employee emp); /** * 通过id删除员工 * @param id */ public void delEmp(int id); } 配置文件 mybatis_config.xml 核心配置文件 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 引入外部文件 --> <properties resource="db.properties"></properties> <settings> <!-- 配置驼峰命名法（将数据库中的_命名方式改为驼峰命名法） --> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!-- 为该包下每一个类创建一个默认的别名，就是简单类名小写--> <typeAliases> <package name="com.atguigu.mybatis.bean"/> </typeAliases> <!-- 数据库连接环境的配置 --> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${db.driver}" /> <property name="url" value="${db.url}" /> <property name="username" value="${db.username}" /> <property name="password" value="${db.password}" /> </dataSource> </environment> </environments> <!-- 引入SQL映射文件,Mapper映射文件 --> <mappers> <!-- 这里我们使用class，前提是dao层的配置文件必须有dao层接口文件名相同，并在同一个包下--> <mapper class="com.atguigu.mybatis.dao.EmployeeMapper" /> </mappers> </configuration> EmployeeMapper.xml SQL映射文件 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- 命名空间（全类名） --> <mapper namespace="com.atguigu.mybatis.dao.EmployeeMapper"> <!-- id的值为dao层接口的方法名，resultType是返回值类型 --> <select id="queryEmpById" resultType="Employee"> select * from employee where id = #{id} </select> <!-- public List<Employee> queryEmpAll(); --> <select id="queryEmpAll" resultType="Employee"> select * from employee </select> <!-- public void addEmp(Employee emp); --> <insert id="addEmp"> insert into employee values(null,#{lastName},#{email},#{gender}) </insert> <!-- public void updateEmp(int id); --> <update id="updateEmp"> update employee set last_name=#{lastName},gender=#{gender} where id=#{id} </update> <!-- public void delEmp(int id); --> <delete id="delEmp"> delete from employee where id=#{id} </delete> </mapper> 测试类 增删改必须进行提交，使用session.commit();或factory.openSession(true); public class Main { static SqlSessionFactory factory; static { try { InputStream inputStream = Resources.getResourceAsStream("mybatis_config.xml"); SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder(); //读取配置文件 factory = factoryBuilder.build(inputStream); } catch (Exception e) { e.printStackTrace(); } } @Test public void select() { //创建session工厂 SqlSession session = factory.openSession(); //获取代理对象 EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class); Employee employee = employeeMapper.queryEmpById(1); System.out.println(employee); List<Employee> queryEmpAll = employeeMapper.queryEmpAll(); System.out.println(queryEmpAll); } @Test public void update() { //增删改必须进行提交，使用session.commit();或factory.openSession(true); SqlSession session = factory.openSession(); EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class); //增 /*Employee employee = new Employee(null,"王五","147@163.com","男"); employeeMapper.addEmp(employee); session.commit();*/ //删 employeeMapper.delEmp(1); session.commit(); //改 /*Employee employee = new Employee(1,"张三",null,"男"); employeeMapper.updateEmp(employee); session.commit();*/ } }

主键生成方式、获取主键值

在mysql中我们通常设置主键为自动递增，那么我们如何知道我们新添加进去的数据主键是多少呢？ 笨方法可以自己在查一遍，但显然不是我们想要的mybatis中为我们提供了获取新添加数据主键的方法 修改EmployeeMapper.xml SQL映射文件 若数据库支持自动生成主键的字段（比如 MySQL 和 SQL Server），则可以设置 useGeneratedKeys=”true”，然后再把 keyProperty 设置到目标属性上。<insert id="addEmp" useGeneratedKeys="true" keyProperty="id"> insert into employee values(null,#{lastName},#{email},#{gender}) </insert> 而对于不支持自增型主键的数据库（例如 Oracle），则可以使用 selectKey 子元素：selectKey 元素将会首先运行，id 会被设置，然后插入语句会被调用<insert id="insertEmployee" parameterType="com.atguigu.mybatis.beans.Employee" databaseId="oracle"> <selectKey order="BEFORE" keyProperty="id" resultType="integer"> select employee_seq.nextval from dual </selectKey> insert into orcl_employee(id,last_name,email,gender) values(#{id},#{lastName},#{email},#{gender}) </insert>

参数传递

1.单个普通类型参数 可以接受基本类型，包装类型，字符串类型等。这种情况MyBatis可直接使用这个参数，不需要经过任何处理。 public Employee queryEmpById(int id); <select id="queryEmpById" resultType="Employee"> select * from employee where id = #{id} </select> 2.多个参数 方法的形参多个{0,1}或{param1,param2} public Employee queryEmpByLastNnmeAndGenderNum(String lastName,String geder); <select id="queryEmpByLastNnmeAndGenderNum" resultType="Employee"> select * from employee where last_name=#{0} and gender=#{1} </select> <!-- 或者--> <select id="queryEmpByLastNnmeAndGenderNum" resultType="Employee"> select * from employee where last_name=#{param1} and gender=#{param2} </select> 3.命名参数 为参数使用@Param起一个名字，MyBatis就会将这些参数封装进map中，key就是我们自己指定的名字 public Employee queryEmpByLastNnmeAndGenderParam(@Param("name")String lastName,@Param("ge")String geder); <select id="queryEmpByLastNnmeAndGenderParam" resultType="Employee"> select * from employee where last_name=#{name} and gender=#{ge} </select> 4.POJO 当这些参数属于我们业务POJO时，我们直接传递POJO，通过对象中的属性名获取值 public Employee queryEmpByLastNnmeAndGender(Employee emp); <select id="queryEmpByLastNnmeAndGender" resultType="Employee"> select * from employee where last_name=#{lastName} and gender=#{gender} </select> 5.Map 我们也可以封装多个参数为map，直接传递，通过key来获取值 public Employee queryEmpByLastNnmeAndGenderMap(Map<String,Object> map); Map<String,Object> map =new HashMap<String, Object>(); map.put("lastName", "李四"); map.put("gender", "男"); <select id="queryEmpByLastNnmeAndGenderMap" resultType="Employee"> select * from employee where last_name=#{lastName} and gender=#{gender} </select> 6.Collection/Array（不常用） 会被MyBatis封装成一个map传入, Collection对应的key是collection,Array对应的key是array. 如果确定是List集合，key还可以是list.

select查询的几种情况

1、查询单行数据返回单个对象 public Employee getEmployeeById(Integer id ); 2、 查询多行数据返回对象的集合 public List<Employee> getAllEmps(); 3、查询单行数据返回Map集合 public Map<String,Object> getEmployeeByIdReturnMap(Integer id ); 4、 查询多行数据返回Map集合 @MapKey("id") // 指定使用对象的哪个属性来充当map的key,此时查询出来的id会作为map集合的key public Map<Integer,Employee> getAllEmpsReturnMap();

指定返回值类型resultType

结果会自动封装为 resultType中的类型 <select id="queryMaps" resultType="java.util.HashMap"> select * from employee </select>

resultMap自定义映射

1、自定义resultMap，实现高级结果集映射2、id ：用于完成主键值的映射3、result ：用于完成普通列的映射4、association ：一个复杂的类型关联;许多结果将包成这种类型5、collection ： 复杂类型的集

此时我们更改bean，使bean的结构边复杂

public class Employee { private Integer id; private String lastName; private String email; private String gender; private Department department; public Employee() { super(); // TODO Auto-generated constructor stub } public Employee(Integer id, String lastName, String email, String gender, Department department) { super(); this.id = id; this.lastName = lastName; this.email = email; this.gender = gender; this.department = department; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } @Override public String toString() { return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + ", department=" + department + "]"; } } public class Department { private Integer did; private String departmentName; public Department() { super(); // TODO Auto-generated constructor stub } public Department(Integer did, String departmentName) { super(); this.did = did; this.departmentName = departmentName; } public Integer getDid() { return did; } public void setDid(Integer did) { this.did = did; } public String getDepartmentName() { return departmentName; } public void setDepartmentName(String departmentName) { this.departmentName = departmentName; } @Override public String toString() { return "Department [did=" + did + ", departmentName=" + departmentName + "]"; } } 自定义映射 此时查询employee信息并查出employee对应的department信息，放入employee对象中 <resultMap type="Employee" id="resultMap"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="email" property="email"/> <result column="gender" property="gender"/> <association property="department" javaType="Department"> <id column="did" property="did"/> <result column="department_name" property="departmentName"/> </association> </resultMap> <select id="getEmpById" resultMap="resultMap"> select e.*,d.* from employee e,department d where e.did=d.did and e.id=#{id} </select>

虽然上述的employee类中包含了department类，但是有时候我们可能只需要employee信息，暂时不需要对应的department信息，我们想节约资源，等我们需要对应的department信息时在查询department

按照以前的思维，我们会将一条查询语句拆为两条查询语句，当我们需要department时，在通过employee的信息去查询department，但这样的话我们需要分开写两次调用查询的语句，mybatis中为我们提供了更好的方法：分布查询 select * from employee where id=#{id} select * from department where did=#{did}

分步查询

mybatis_config.xml，我们需要开启延迟加载，并且设置不加载全部数据<settings> <!-- 开启延迟加载 --> <setting name="lazyLoadingEnabled" value="true"/> <!-- 设置加载的数据是按需还是全部 --> <setting name="aggressiveLazyLoading" value="false"/> </settings> EmployeeMapper.xml association POJO中的属性可能会是一个对象,我们可以使用联合查询，并以级联属性的方式封装对象.使用association标签定义对象的封装规则property：当前对象的属性column：传递过去的列（理解为外键）select：调用的另一个查询方法 <resultMap type="Employee" id="getEmpByAssociationMap"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="email" property="email"/> <result column="gender" property="gender"/> <association property="department" column="did" select="com.atguigu.mybatis.dao.DepartmentMapper.getDepById"> </association> </resultMap> <!-- public Employee getEmpByAssociation(Integer id); --> <select id="getEmpByAssociation" resultMap="getEmpByAssociationMap" > select * from employee where id=#{id} </select> DepartmentMapper.xml <!-- 自定义结果集映射--> <resultMap type="Department" id="getDepByIdMap"> <id column="did" property="did"/> <result column="department_name" property="departmentName"/> </resultMap> <select id="getDepById" resultMap="getDepByIdMap"> select * from department where did=#{did} </select> java @Test public void t3() { SqlSession session = factory.openSession(); EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class); Employee employee = employeeMapper.getEmpByAssociation(2); //此时没有用到department类，此时只会查询employee，department的查询不会执行 System.out.println(employee.getEmail()); //此时用到了department类，有关department类的查询现在才回执行 System.out.println(employee.getDepartment().getDepartmentName()); }