mybatis映射文件与动态sql

最新推荐文章于 2020-09-23 11:07:09 发布

桃桃乌龙

最新推荐文章于 2020-09-23 11:07:09 发布

阅读量313

点赞数

分类专栏： Mybatis 文章标签： MyBatis

本文链接：https://blog.csdn.net/hawkDai5/article/details/79172673

版权

Mybatis 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文主要总结mybatis映射文件编写sql语句的一些简单例子

resultMap

<resultMap type="User" id="UserMap">
	<id property="id" column="id"/>
	<result property="userName" column="user_name"/>
	<result property="userPwd" column="user_pwd"/>
</resultMap>

type：类的全限定名, 或者一个类型别名

外部id：当前命名空间中的一个唯一标识，用于标识一个result map

内部id ：一个 ID 结果;标记结果作为 ID 可以帮助提高整体效能

property对应的值为java代码实体类对应的字段名称，column对应的值为数据库中相应的字段名称

如上例：

对应的java实体类为

public class User {

	private Integer id;
	private String userName;
	private String userPwd;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPwd() {
		return userPwd;
	}
	public void setUserPwd(String userPwd) {
		this.userPwd = userPwd;
	}
}

对应的数据库表结构为

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `user_pwd` varchar(20) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select

<select id="login" parameterType="User" resultMap="UserMap">
	select * from t_user where user_name = #{userName} and user_pwd = #{userPwd}
</select>

<select id="getUserList" parameterType="String" resultMap="UserMap">
	select * from t_user where user_name = #{UserName}
</select>

id:在命名空间中唯一的标识符，可以被用来引用这条语句，通俗可以理解为和dao层的接口函数保持一致

parameterType：将要传入语句的参数的完全限定类名或别名。这个属性是可选的，因为 MyBatis 可以通过 TypeHandler 推断出具体传入语句的参数，默认值为 unset

resultMap：结果集，对应上面定义的resultMap的id值

insert

<insert id="add" parameterType="User">
	insert into t_user values(null,#{userName},#{userPwd})
</insert>

update

<update id="update" parameterType="User">
	update t_user set user_name = #{userName}, user_pwd = #{userPwd} where id = #{id}
</update>

delete

<delete id="delete" parameterType="Integer">
	delete from t_user where id = #{id}
</delete>

一对一

方法1：

<resultMap type="User" id="UserMap2">
	<id property="id" column="id"/>
	<result property="userName" column="user_name"/>
	<result property="userPwd" column="user_pwd"/>
	<association property="person" javaType="Person">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
	</association>
</resultMap>

<select id="findUserAndPerson" parameterType="int" resultMap="UserMap2">
	select a.user_name,a.user_pwd,b.name from t_user a,t_person b where a.person_id = b.id and a.id = #{id}
</select>

方法2：

<resultMap type="User" id="UserMap3">
	<id property="id" column="id"/>
	<result property="userName" column="user_name"/>
	<result property="userPwd" column="user_pwd"/>
	<association property="person" column="person_id" select="com.zby.dao.PersonDao.findById" />
</resultMap>

<select id="findUserAndPerson2" parameterType="int" resultMap="UserMap3">
	select * from t_user where id = #{id}
</select>

对应的com.zby.dao.PersonDao.findById为

<resultMap type="Person" id="PersonMap">
	<id property="id" column="id"/>
	<result property="name" column="name"/>
	<result property="age" column="age"/>
</resultMap>
	
<select id="findById" parameterType="int" resultType="Person">
	select * from t_person where id = #{id}
</select>

方法3：直接写sql

<select id="findUserAndPerson3" parameterType="int" resultType="User">
	select a.id as id,a.user_name as userName,a.user_pwd as userPwd,b.id as "person.id",b.name as "person.name",b.age as "person.age"
	from t_user a,t_person b where a.person_id = b.id and a.id = #{id}
</select>

一对多

方法1：

<resultMap type="User" id="UserMap4">
	<id property="id" column="id"/>
	<result property="userName" column="user_name"/>
	<result property="userPwd" column="user_pwd"/>
		
	<collection property="roleList" column="id" select="com.zby.dao.RoleDao.findById" />
</resultMap>

<select id="findUserAndRole" parameterType="int" resultMap="UserMap4">
	select * from t_user where id = #{id}
</select>

对应的com.zby.dao.RoleDao.findById为

<resultMap type="Role" id="RoleMap">
	<id property="id" column="id"/>
	<result property="roleName" column="role_name"/>
	<result property="userId" column="user_id"/>
</resultMap>
	
<select id="findById" parameterType="int" resultType="Role">
	select id,role_name as roleName,user_id as userId from t_role where user_id = #{id}
</select>

方法2：

<resultMap type="User" id="UserMap5">
	<id property="id" column="user_id"/>
	<result property="userName" column="user_name"/>
	<result property="userPwd" column="user_pwd"/>
		
	<collection property="roleList" ofType="Role">
		<id property="id" column="id"/>
		<result property="roleName" column="role_name"/>
		<result property="userId" column="user_id"/>
	</collection>
</resultMap>

<select id="findUserAndRole2" parameterType="int" resultMap="UserMap5">
	select a.id as user_id,a.user_name,a.user_pwd,b.* from t_user a,t_role b where a.id = b.user_id and a.id = #{id}
</select>

动态sql

if

<select id="findWithIf" parameterType="Map" resultMap="UserMap3">
	select * from t_user 
	where 1 = 1
	<if test="id != null and id != ''">
		and id = #{id}
	</if>
	<if test="user_name != null and user_name !=''">
		and user_name like concat('%',#{user_name},'%')
	</if>
	<if test="user_pwd != null and user_pwd != ''">
		and user_pwd = #{user_pwd}
	</if>
</select>

choose when otherwise

<select id="findWithIf" parameterType="Map" resultMap="UserMap3">
	select * from t_user 
	where 1 = 1
	<if test="id != null and id != ''">
		and id = #{id}
	</if>
	<if test="user_name != null and user_name !=''">
		and user_name like concat('%',#{user_name},'%')
	</if>
	<if test="user_pwd != null and user_pwd != ''">
		and user_pwd = #{user_pwd}
	</if>
	<choose>
		<when test="limit != null and limit != ''">
			limit 1
		</when>
		<otherwise>
			limit 2
		</otherwise>
	</choose>
</select>

where

<select id="findWithWhere" parameterType="Map" resultMap="UserMap3">
	select * from t_user
	<where>
		<if test="id != null and id != ''">
			and id = #{id}
		</if>
		<if test="user_name != null and user_name !=''">
			and user_name like concat('%',#{user_name},'%')
		</if>
		<if test="user_pwd != null and user_pwd != ''">
			and user_pwd = #{user_pwd}
		</if>
	</where>
</select>

where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。

trim

<select id="findWithTrim" parameterType="Map" resultMap="UserMap3">
	select * from t_user
	<trim prefix="where" prefixOverrides="and |or ">
		<if test="id != null and id != ''">
			and id = #{id}
		</if>
		<if test="user_name != null and user_name !=''">
			and user_name like concat('%',#{user_name},'%')
		</if>
		<if test="user_pwd != null and user_pwd != ''">
			and user_pwd = #{user_pwd}
		</if>
	</trim>
</select>

前缀为where，prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除，并且插入 prefix 属性中指定的内容

foreach

<select id="findWithForeach" parameterType="Map" resultMap="UserMap3">
	select * from t_user 
	<if test="userId != null and userId.size > 0">
		<where>
		id in 
			<foreach item="id" collection="userId" open="(" separator="," close=")">
				#{id}
			</foreach>
		</where>	
	</if>
</select>

foreach 元素的功能是非常强大的，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。