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>