mybatis基礎

<!-- parameterType为hashmap。此外resultType也可以为hashmap -->
<!-- parameterType为实体类，对象参数自动匹配，若是对象属性和列名不一致，可为sql语句中的列名起别名，别名和对象属性一直也可以 -->
<select id="login2" resultType="user" parameterType="user">
	select * from user where userName=#{userName} and password=#{password}
</select>

<select id="selectByParams" resultType="TableIp" parameterType="map">
	select * from table_ip
	order by findTime desc
	limit ${offset}, ${size}
</select>

<!-- resultType和resultMap两者只能有一个成立，resultMap可解决复杂的映射关系，如实体类中有关联查询。属性名和列名不一致时，property为属性名，column为列名 -->
<resultMap id="userMap" type="user">
	<id property="id" column="id" />
	<result property="userName" column="userName" />
	<result property="password" column="password" />
</resultMap>
<!-- resultMap指定resultMap的id -->
<select id="selectUsers" resultMap="userMap">
	select id, userName, password from user
</select>

<!-- 一对多关系的集合查询 -->
<!-- 此处id都为null，可使用别名解决 -->
<resultMap id="visitMap" type="user">
	<id property="id" column="user.id" />
	<result property="userName" column="userName" />
	<collection property="visitList" javaType="ArrayList"
		column="userID" ofType="com.dudu.entity.Visit">
		<result property="id" column="visit.id" />
		<result property="visitIP" column="visitIP" />
		<result property="visitDate" column="visitDate" />
	</collection>
</resultMap>
<select id="selectVisit" resultMap="visitMap">
	select * from user inner join visit on user.id=visit.userID
</select>

<!-- 一对一关系的联合查询 -->
<!-- 此处id都为null，可使用别名解决 -->
<resultMap id="AuthorMap" type="Author">
	<!-- id 为resultMap的主键，author.id中的author为表名 -->
	<id property="id" column="author.id" />
	<result property="realName" column="realName" />
	<result property="idCard" column="idCard" />
	<!-- association联合查询的对象信息 -->
	<association property="user" column="userId" javaType="user">
		<id property="id" column="user.id" />
		<result property="userName" column="userName" />
		<result property="password" column="password" />
	</association>
</resultMap>
<select id="selectAuthorJoin" resultMap="AuthorMap">
	select * from author inner join user
	on user.id=author.userId
</select>


<!-- 构造函数查询,查询部分字段时，使用构造查询方便 -->
<resultMap id="AuthorMapByCon" type="Author">
	<id property="id" column="author.id" />
	<result property="realName" column="realName" />
	<result property="idCard" column="idCard" />
	<association property="user" column="userId" javaType="user">
		<constructor>
			<arg column="user.id" javaType="Integer" />
			<arg column="userName" javaType="String" />
			<arg column="password" javaType="String" />
		</constructor>
	</association>
</resultMap>
<select id="selectAuthorJoinByCon" resultMap="AuthorMapByCon">
	select * from author inner join user
	on user.id=author.userId
</select>


<!-- 子查询，n+1条sql语句。先执行select * from author查询出所有authors，再遍历发出n条 select * from 
	user where id=? 查询user -->
<resultMap id="AuthorSubMap" type="Author">
	<id property="id" column="author.id" />
	<result property="realName" column="realName" />
	<result property="idCard" column="idCard" />
	<!-- select为user.xml中select标签的id，userId为传递给该方法的参数 -->
	<association property="user" column="userId" javaType="user"
		select="findById">
	</association>
</resultMap>
<select id="selectAuthor" resultMap="AuthorSubMap">
	select * from author
</select>

<!-- choose -->
<select id="selectUserChoose" resultType="user" parameterType="user">
	select * from user where 1=1
	<choose>
		<!-- 用户名不为空时，按用户名查，用户名为空，id不为空时，按id查，用户名和Id都为空，则查密码不为空的 -->
		<when test="userName!=null">
			and userName like #{userName}
		</when>
		<when test="id!=null">
			and id = #{id}
		</when>
		<otherwise>
			and password is not null and password != ''
		</otherwise>
	</choose>
</select>


<!--where，智能判断where中条件前，是否需要添加逻辑运算符(and,or) -->
<select id="selectUserWhere" resultType="user" parameterType="user">
	select * from user
	<where>
		<if test="userName!=null">
			and userName like #{userName}
		</if>
		<if test="id!=null">
			and id =#{id}
		</if>
	</where>
</select>


<!-- trim代替where,前缀判断是否需要prefixOverrides的内容 -->
<select id="selectUserTrimInsteadOfWhere" resultType="user"
	parameterType="user">
	select * from user
	<trim prefix="where" prefixOverrides="and|or">
		<if test="userName!=null">
			and userName like #{userName}
		</if>
		<if test="id!=null">
			and id = #{id}
		</if>
	</trim>
</select>

<!-- jdbcType mybatis进行的参数类型到数据库存储类型之间的转换，一般不用指定，若参数类型不一致，需指定jdbcType，为mybatis枚举值，必须大写 -->
<update id="updateUser" parameterType="user">
	update user set
	userName=#{userName,jdbcType=VARCHAR},
	password=#{password,jdbcType=VARCHAR}
	where id = #{id,jdbcType=INTEGER}
</update>

<!-- set，智能判断是否后面需要,分隔符 -->
<update id="updateUserSet" parameterType="user">
	update user
	<set>
		<if test="userName != null">userName=#{userName},</if>
		<if test="password != null">password=#{password},</if>
	</set>
	where id=#{id}
</update>


<!-- trim,代替set -->
<update id="updateUserTrim" parameterType="user">
	UPDATE user
	<!-- prefix表示在其中的sql语句前添加的内容，suffix表示在其中的sql语句后添加的内容，后缀判断是否需要suffixOverrides的内容 -->
	<trim prefix="SET" suffixOverrides="," suffix="WHERE id = #{id}">
		<if test="userName != null and userName != '' ">
			userName = #{userName},
		</if>
		<if test="password != null and password != '' ">
			password=#{password},
		</if>
	</trim>
</update>


<!-- foreach 循环查询,list为字符串id的集合 -->
<select id="selectUserForeach" resultType="user" parameterType="list">
	select * from user
	<where>
		id in
		<!-- item是list中的每一项，open循环开始时添加，close循环结束时添加 ，separator每一项的分隔符,index是从0开始的下标 -->
		<foreach item="item" index="index" collection="list" open="("
			separator="," close=")">
			#{item}
		</foreach>
	</where>
</select>


<!-- foreach 循环赋值，实现批量插入 -->
<!-- insert into user (userName, password) values (?, ?) , (?, ?) -->
<insert id="insertUserForeach">
	insert into user (userName, password) values
	<foreach item="item" index="index" collection="list" open=""
		separator="," close="">(#{item.userName}, #{item.password})
	</foreach>
</insert>

<!-- mybatis自动id机制，为insert的对象添加id。user.id中user为Author的属性名 -->
<insert id="insertAuthor" parameterType="Author" statementType="PREPARED">
	insert into Author(userId,realName,idCard)
	values(#{user.id},#{realName},#{idCard})
</insert>

1、首先定义一个sql标签，一定要定义唯一id
<sql id="Base_Column_List">name,age</sql>
2、然后通过id引用
<select id="selectAll">
	select
	<include refid="Base_Column_List" />
	from student
</select>
这个
<include refid="Base_Column_List" />
会自动把上面的代码贴过来。