mybatis小案例笔记

Mybatis小案例笔记

常用依赖

org.mybatis
mybatis
3.3.0



mysql
mysql-connector-java
5.1.6

注解开发条件******





注解开发条件结束******

​
****使用批量注册映射文件（同时需要将映射文件和接口类设置同名且放在同一个包下面）




****使用批量注册映射文件（同时需要将映射文件和接口类设置同名且放在同一个包下面）结束

​
​
插入数据的时候获取自增字段*********

insert into users(username) values (#{username})

useGeneratedKeys="true" keyColumn="i_id" keyProperty="id"

插入数据的时候获取自增字段结束*********

​
参数处理***********
单个参数的情况时在#{}里面写什么都可以
多个参数的时候在#{}里面只能写#{param1}， #{param2}或者#{1}， #{2}之类的
或者使用命名参数的方式，即在接口的方法参数上加上@Param(“username”)， 之后便可以在映射文件中使用#{username}了
当然如果参数可以封装为POJO那就更好，没有POJO封装的话，可以将参数封装为Map，这样也可以使用#{username}这样的方式获取参数
如果接口参数是集合或数组的，若是集合List的话则得使用#{list[0]}， #{collecttion[0]}这种形式的，若是数组则用#{array[0]}这样的
除了使用#{}这种形式外，还有$，但是${}不安全， 所以一般使用#{}， 但是${}也有它自己的用处，虽然用得少，但是在分库分表中可以使用来拼接sql语句，同时也可以用于设置排序等动态sql语句拼接，这是#{}所无法做到的
参数处理结束***********

**使用map封装从数据库查到的数据

select *from users where username = #{username}

@Test
public void test5() {
UserDao dao = session.getMapper(UserDao.class);
Map<String, Object> userMap = dao.getUserMap(“111”);
System.out.println(userMap);
}
**使用map封装从数据库查到的数据结束

mybatis配置文件的设置*






mybatis配置文件的设置结束*

resultMap写法******

​

<resultMap type="Users" id="myUsers">
<id column="id"/>
	<result column="phone" property="phone_str"/>
</resultMap>
<select id="selectUsers"  resultMap="myUsers">
	select * from users where username = #{username}
</select>

<resultMap type="Users" id="myUserShop1">
	<id column="id" property="id"/>
	<result column="phone" property="phone_str"/>
	<association property="shop" javaType="Shop">
		<id column="shopid" property="shopid"/>
		<result column="shopname" property="shopname"/>
	</association>
</resultMap>

<resultMap type="Users" id="myUserShop">
	<id column="id" property="id"/>
	<result column="phone" property="phone_str"/>
	<result column="shopname" property="shop.shopname"/>
</resultMap>

<resultMap type="Users" id="myUserShop2">
	<id column="id" property="id"/>
	<result column="phone" property="phone_str"/>
	<association property="shop" javaType="Shop" 
		column="shopid"
		select="dao.ShopDao.getShopByID">
	</association>
</resultMap>

<resultMap id="word" type="com.hbt.en_rem_hou.entitys.Word" >
	<id column="id" property="id"/>
	<result column="cmd" property="cmd"/>
	<collection property="features" column="id" javaType="java.util.List" ofType="com.hbt.en_rem_hou.entitys.Feature"
		select="com.hbt.en_rem_hou.dao.FeatureDao.getFeaturesByWordId"/>
</resultMap>
<select id="getWordByWordId" parameterType="Integer" resultMap="word">
	select *from word where id = #{wordid}
</select>

resultMap写法结束******

动态SQL

双引号："
&&为：&
id != null
lastname!=''
and相当于：&&
""相当于""
email.trim()!=""
gender==0 or gender==1


标签有：trim， where， if， choose， foreach， 
where标签只会去掉第一个and 或第一个or
set标签在update的sql语句可以去掉多余的逗号
choose(when otherwise)


  <insert id="insertSelective" parameterType="com.cg.cn.dao.domain.Cart" >
    insert into cart
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="cmdyid != null" >
        cmdyid,
      </if>
      <if test="userid != null" >
        userid,
      </if>
      <if test="cartid != null" >
        cartid,
      </if>
      <if test="buynum != null" >
        buynum,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="cmdyid != null" >
        #{cmdyid,jdbcType=INTEGER},
      </if>
      <if test="userid != null" >
        #{userid,jdbcType=VARCHAR},
      </if>
      <if test="cartid != null" >
        #{cartid,jdbcType=INTEGER},
      </if>
      <if test="buynum != null" >
        #{buynum,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>


<select id="getUsersByDynamic" resultType="Users">
	select * from users 
	<where>
		<if test="username!=null and username!=''">
			username = #{username}
		</if>
		<if test="password!=null and password!=''">
			and password=#{password}
		</if>
		<if test="age!=null and age!=0">
			and age = #{age}
		</if>
		<if test="gender!=null and gender!=''">
			and gender =  #{gender}
		</if>
	</where>
</select>


<select id="getUsersByDynamic1" resultType="Users">
	select * from users 
	<where>
		<choose>
			<when test="username!=null and username!=''">
				username = #{username}
			</when>
			<when test="password!=null and password!=''">
				password=#{password}
			</when>
			<when test="age!=null and age!=0">
				age = #{age}
			</when>
			<when test="gender!=null and gender!=''">
				gender =  #{gender}
			</when>
			<otherwise>
				gender = '0'
			</otherwise>
		</choose>
	</where>
</select>


<update id="updateUsers">
	update users 
	<set>
		<if test="username!=null and username != ''">
			username = #{username},
		</if>
		<if test="gender != null and gender!=''">
			gender = #{gender},
		</if>
	</set>
	where username = '111'
</update>

<select id="getUsersByCollection" resultType="Users">
	select *from users where username in 
	<foreach collection="usernames" item="username" separator="," open="(" close=")">
		#{username}
	</foreach>
</select>

<if test="features != null and features.size > 0">
	and feature.descript in
	<foreach collection="features" item="feature" separator="," open="(" close=")">
			#{feature}
	</foreach>
</if>

<insert id="insertUsers">
	insert into users(username, password, age, gender, phone) values
	<foreach collection="users" item="user" separator=",">
		(#{user.username}, #{user.password}, #{user.age}, #{user.gender}, #{user.phone_str})
	</foreach>
</insert>



//bind标签的用法
<select id="getUsersByCollection" resultType="Users">
	<bind name = "_lastname" value = "'%'+lastname+'%'"/>
	select *from users where username in like #{_lastname}
</select>


//sql标签的用法
<sql id = "mycolumn">
		username, password, age, gender, phone
</sql>
<insert id="insertUsers">
	insert into users(<include id = "mycolumn"> </include>) values (#{user.username}, #{user.password}, #{user.age}, #{user.gender}, #{user.phone_str})
</insert>

//优秀例子
<select resultType="com.hbt.en_rem_hou.entitys.Word" id="getWordsByCondition" parameterType="com.hbt.en_rem_hou.entitys.WordsCondition">
		<bind name = "_word" value = "'%'+word+'%'"/>
		select distinct word.* from word left join w_f on word.id = w_f.wordid left join feature on w_f.featureid = feature.id
		<where>
			<if test="word != null and word != ''">
				 word.src like #{_word}
			</if>
			<if test="cmd != null and cmd != '全部'">
				and word.cmd = #{cmd}
			</if>
			<if test="imp != null and imp != '全部'">
				and word.imp = #{imp}
			</if>

			<if test="features != null and features.size > 0">
				and feature.descript in
				<foreach collection="features" item="feature" separator="," open="(" close=")">
						#{feature.descript}
				</foreach>
			</if>
		</where>
	</select>