MyBatis中的SQL映射文件

MyBatis中的SQL映射文件

MyBatis 真正强大之处在于SQL语句的映射，也是它的魅力所在。SQL映射文件实际内部封装了JDBC操作。

SQL映射文件的好处
1、相比较于JDBC，减少50%以上的代码量
2、MyBatis专注于SQL，将SQL语句从硬编码中分离出来
3、便于维护，可以极大限度的调优

<?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="org.mybatis.example.BlogMapper">
	
</mapper>

注意： mapper标签中的namespace属性所填写的内容是所对应接口的 包名+接口名

mapper标签中元素

示例：
select:

<select id="getUSers" resultType="User">
		SELECT *
		FROM book_info
	</select>

insert:

<insert id="addUser" parameterType="User">
		INSERT INTO
		<include refid="userCole" />
		User VALUES(NULL,#{name},#{age},#{sex})
	</insert>

delete:

<delete id="deleteUser" parameterType="User">
		DELETE FROM User WHERE id
		= #{id}
	</delete>

update

<update id="updateUSer" parameterType="int">
		UPDATE SET name=#{name} WHERE id=#{id}
	</update>

resultMap:

<resultMap type="User" id="UserMap">
		<id property="id" column="r_id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
		<result property="sex" column="sex"/>
	</resultMap>

<select id="getUSers" resultType="User" resultMap="UserMap">
		SELECT id as r_id,name,age,sex 
		FROM book_info
	</select>

当我们查询的字段与java实体类不一致的时候，这是MyBatis不能自动映射，需要我们手动映射

include:

<insert id="addUser" parameterType="User">
		INSERT INTO
		<include refid="userCole" />
		User VALUES(NULL,#{name},#{age},#{sex})
	</insert>

sql：

	<sql id="userCole">
		id,
		name,
		age,
		sex,
	</sql>

注意： 增删改查标签中的id属性在此文件中必须是唯一的，对应接口中的名字，resultType中时类型的别名或者是全限定名（包+Xxx.java）。

对于普通的java类型，常用内建的类型别名：

别名	映射的类型	别名	映射的类型
string	String	doule	Double
byte	Byte	float	Float
long	Long	boolean	Boolean
short	Short	date	Date
int	Integer	map	Map
interger	Integer	hashmap	HashMap
arrarlist	ArrayList	list	List

resultType和resultMap概述
MyBatis中在查询进行baiselect映射的时候du，返回类型可以用resultType，也可以用resultMap，resultType是直接zhi表示返回类型的，而resultMap则是dao对外部ResultMap的引用，但是resultType跟resultMap不能同时存在。在MyBatis进行查询映射时，其实查询出来的每一个属性都是放在一个对应的Map里面的，其中键是属性名，值则是其对应的值。①当提供的返回类型属性是resultType时，MyBatis会将Map里面的键值对取出赋给resultType所指定的对象对应的属性。所以其实MyBatis的每一个查询映射的返回类型都是ResultMap，只是当提供的返回类型属性是resultType的时候，MyBatis对自动的给把对应的值赋给resultType所指定对象的属性。②当提供的返回类型是resultMap时，因为Map不能很好表示领域模型，就需要自己再进一步的把它转化为对应的对象，这常常在复杂查询中很有作用。

注意： resultType和resultMap不能同时使用，使用resultMap的时候id标签在其中扮演重要的角色，
如果没有该标签，MyBatis也会工作，但是这样会大大影响MyBatis的执行的效率，建议还是加上。

动态SQL

if

<select id="getBooksByConditions" resultType="BookInfo">
		SELECT * FROM
		book_info WHERE 1=1
		
			<if test="book_name != null and book_name != ''">
				AND book_name LIKE CONCAT('%',#{book_name},'%')
			</if>
			<if test="book_type != null and book_type != 0">
				AND book_type = #{book_type}
			</if>
			<if test="is_borrow != null and is_borrow != 0">
				AND is_borrow = #{is_borrow}
			</if>
	</select>

test:表示判断的条件

where

<select id="getBooksByConditions" resultType="BookInfo">
		SELECT * FROM
		book_info
		<where>
			<if test="book_name != null and book_name != ''">
				AND book_name LIKE CONCAT('%',#{book_name},'%')
			</if>
		</where>
	</select>

where：当where作用域中的条件不满足的时候会自动的剔除where关键字，当满足的时候会自动补上where关键字。

if+where

<select id="getBooksByConditions" resultType="BookInfo">
		SELECT * FROM
		book_info
		<where>
			<if test="book_name != null and book_name != ''">
				AND book_name LIKE CONCAT('%',#{book_name},'%')
			</if>
			<if test="book_type != null and book_type != 0">
				AND book_type = #{book_type}
			</if>
			<if test="is_borrow != null and is_borrow != 0">
				AND is_borrow = #{is_borrow}
			</if>
		</where>

set

<update id="updateUSer" parameterType="int">
		UPDATE
		<set>
			<if test="name != null and name != ''">
				name=#{name}
			</if>
			WHERE id=#{id}
		</set>
	</update>

set：当set作用域中的条件不满足的时候会自动的剔除set关键字，当满足的时候会自动补上set关键字,并且会提出最后一个字段后的逗号。

if+set

<update id="updateUSer" parameterType="int">
		UPDATE
		<set>
			<if test="name != null and name != ''">
				name=#{name}
			</if>
		</set>
		<where>
			<if test="id != null and id != 0">
				id=#{id}
			</if>
		</where>
	</update>

trim

<update id="updateUSer" parameterType="int">
		UPDATE
		<trim prefix="SET" suffixOverrides="," suffix="WHERE id=#{id}">
			<if test="name != null and name != ''">
				name=#{name},
			</if>
			<if test="age != null and age != ''">
				age=#{age},
			</if>
		</trim>
	</update>

trim：功能比较强大
prefix:条件满足时自动补全SQL语句前指定的内容，否则提出
suffix:条件满足时自动补全SQL语句后指定的内容，否则提出
prexOverridesL:覆盖SQL语句前指定的内容
suffixOverrides:覆盖SQL语句后指定的内容
foreach

 <select id="getProvider">
        SELECT * FROM smbms_provider WHERE
        <foreach collection="list" item="t" open="in id(" close=")" separator=",">
          #{t}
        </foreach>
    </select>

foreach主要构建在in条件中
collection：
当参数是单参取参数类型是一个List的时候，值为list
当参数是单参取参数类型是一个数组的时候，值为array
当参数是单参取参数类型是一个Map的时候，值为map的key指
item:表示迭代时的别名
open：该语句以什么开始
close：该语句以什么结束
separator：每次迭代的时候以什么分割

<select id="getProvider">
        SELECT * FROM smbms_provider WHERE
        <foreach collection="array" item="t" open="in id(" close=")" separator=",">
          #{t}
        </foreach>
    </select>

choose

<select id="getBooksByConditions" resultType="BookInfo">
		SELECT * FROM
		book_info
		<where>
			<choose>
				<when test="book_name != null and book_name != ''">
					AND book_name LIKE CONCAT('%',#{book_name},'%')
				</when>
				<when test="book_type != null and book_type != 0">
					AND book_type = #{book_type}
				</when>
				<otherwise>
					AND is_borrow = #{is_borrow}
				</otherwise>
			</choose>
		</where>
	</select>

choose:相当于java中的switch，当条件都不满足时，会执行otherwise作用域中的内容。

association(一对一)、collection（一对多）

两个元素都是定义在resultMap标签中

association： 映射JavaBean中某个“复杂类型”属性。此标签仅处理一对一的映射关系
collection: 映射JavaBean中某个“复杂类型”属性，只不过这个属性是一个集合列表。 处理一对多的映射关系。

一对一、一对多在数据库中的体现
总结：对于用户，一般情况下在公司都扮演一个角色，这就是所谓的一对一，对于公司来说每个角色的员工都不止一个，这就形成了一对多，角度不同，所对应的映射关系不同。
对应的实体类

package cn.com.pojo;

import java.util.List;

public class User {
	private Integer id;
	private String userCode;
	private String userName;
	private String userPassword;
	private Integer gender;
	private String birthday;
	private String phone;
	private String address;
	private Integer userRole;
	private Integer createdBy;
	private String creationDate;
	private Integer modifyBy;
	private String modifyDate;
	private String userRoleName;
	
	private Role role;
	private List<Address> addresses;

	public User() {
		super();
	}

	public User(String userCode, String userName, String userPassword, Integer gender, String birthday, String phone,
			String address, Integer userRole, Integer createdBy, String creationDate, Integer modifyBy,
			String modifyDate) {
		super();
		this.userCode = userCode;
		this.userName = userName;
		this.userPassword = userPassword;
		this.gender = gender;
		this.birthday = birthday;
		this.phone = phone;
		this.address = address;
		this.userRole = userRole;
		this.createdBy = createdBy;
		this.creationDate = creationDate;
		this.modifyBy = modifyBy;
		this.modifyDate = modifyDate;
	}

	public Role getRole() {
		return role;
	}

	public void setRole(Role role) {
		this.role = role;
	}

	public String getUserRoleName() {
		return userRoleName;
	}

	public void setUserRoleName(String userRoleName) {
		this.userRoleName = userRoleName;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUserCode() {
		return userCode;
	}

	public void setUserCode(String userCode) {
		this.userCode = userCode;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getUserPassword() {
		return userPassword;
	}

	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}

	public Integer getGender() {
		return gender;
	}

	public void setGender(Integer gender) {
		this.gender = gender;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public Integer getUserRole() {
		return userRole;
	}

	public void setUserRole(Integer userRole) {
		this.userRole = userRole;
	}

	public Integer getCreatedBy() {
		return createdBy;
	}

	public void setCreatedBy(Integer createdBy) {
		this.createdBy = createdBy;
	}

	public String getCreationDate() {
		return creationDate;
	}

	public void setCreationDate(String creationDate) {
		this.creationDate = creationDate;
	}

	public Integer getModifyBy() {
		return modifyBy;
	}

	public void setModifyBy(Integer modifyBy) {
		this.modifyBy = modifyBy;
	}

	public String getModifyDate() {
		return modifyDate;
	}

	public void setModifyDate(String modifyDate) {
		this.modifyDate = modifyDate;
	}

	public List<Address> getAddresses() {
		return addresses;
	}

	public void setAddresses(List<Address> addresses) {
		this.addresses = addresses;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", userName=" + userName + ", role=" + role + ", addresses=" + addresses + "]";
	}

	

}

对应的接口

package cn.com.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;



import cn.com.pojo.User;

public interface UserMapper {
	/**
	 * 一对一的映射关系
	 * @return
	 */
	public List<User> getAssociation();
	/**
	 * 一对多的映射关系
	 * @param id
	 * @return
	 */
	public List<User> getCollection(Integer id);

}

userMapper.xml 映射文件

<?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="cn.com.mapper.UserMapper">

	<sql id="userCol">
		id,
		userCode,
		userName,
		userPassword,
		gender,
		birthday,
		phone,
		address,
		userRole,
		createdBy,
		creationDate
	</sql>
	<!-- Sql映射 -->
	<resultMap type="User" id="UserMap">
		<id property="id" column="id" />
		<result property="userName" column="userName" />
		<!-- 映射一对一 --> 
		 <association property="role" javaType="Role">
			<id property="id" column="userRole" />
			<result property="roleName" column="roleName" />
		</association> 
		<!-- 一对多 -->
		<collection property="addresses" ofType="Address">
			<id property="id" column="a_id" />
			<result property="addressDesc" column="addressDesc" />
		</collection>
	</resultMap>
	
	<!-- 一对一 -->
	<select id="getAssociation" resultMap="UserMap">
		SELECT
		u.*,r.id,r.roleName
		FROM smbms_user u,smbms_role r
		WHERE u.userRole =
		r.id
	</select>
	
	<!-- 一对多 -->
	<select id="getCollection" resultMap="UserMap">
		SELECT u.*,a.id AS
		a_id,a.addressDesc FROM smbms_user u, smbms_address a
		WHERE u.id=a.userId AND u.id=#{id}
	</select>
</mapper>

结果：
一对一：

User [id=1, userName=系统管理员, role=Role [id=1, roleName=系统管理员], addresses=[Address [addressDesc=null, id=1]]]
User [id=2, userName=李明, role=Role [id=2, roleName=经理], addresses=[Address [addressDesc=null, id=2]]]
User [id=5, userName=韩路彪, role=Role [id=2, roleName=经理], addresses=[Address [addressDesc=null, id=5]]]
User [id=6, userName=张华, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=6]]]
User [id=7, userName=王洋, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=7]]]
User [id=8, userName=赵燕, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=8]]]
User [id=10, userName=孙磊, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=10]]]
User [id=11, userName=孙兴, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=11]]]
User [id=12, userName=张晨, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=12]]]
User [id=13, userName=邓超, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=13]]]
User [id=14, userName=杨过, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=14]]]
User [id=15, userName=赵敏, role=Role [id=2, roleName=经理], addresses=[Address [addressDesc=null, id=15]]]
User [id=17, userName=曹操, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=17]]]
User [id=18, userName=王7, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=18]]]
User [id=19, userName=王7, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=19]]]
User [id=20, userName=曹操, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=20]]]
User [id=22, userName=布鲁斯。李, role=Role [id=2, roleName=经理], addresses=[Address [addressDesc=null, id=22]]]
User [id=23, userName=布鲁斯。李, role=Role [id=2, roleName=经理], addresses=[Address [addressDesc=null, id=23]]]
User [id=25, userName=张三, role=Role [id=3, roleName=普通员工], addresses=[Address [addressDesc=null, id=25]]]

一对多：

User [id=1, userName=系统管理员, role=Role [id=1, roleName=null], addresses=[Address [addressDesc=北京市东城区东交民巷44号, id=1], Address [addressDesc=北京市海淀区丹棱街3号, id=2], Address [addressDesc=北京市东城区美术馆后街23号, id=3]]]

用心去创造，尊敬每一位创造者！