mybatis-2

mybatis原生用法

    sqlSession.selectOne(sqlId,参数);
    sqlSession.update(sqlId,参数);
    sqlSession.insert(sqlId,参数);
    sqlSession.delete(sqlId,参数);

mybatis接口用法

1 namespace与接口全限定名一致
2 id和抽象函数保持一致
3 参数类型与返回类型保持一致
4 java类名与xml文件名保持一致

    public interface GoodsMapper {
    Goods selectGoodsById();
}

    <select id="selectGoodsById" resultType="com.study.entity.Goods">
        select * from goods where gid=3
    </select>

    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    Goods goods = mapper.selectGoodsById();

mybatis-config.xml配置

properties

db.properties

jdbc.driverClass=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql:///taobao?useUnicode=true&characterEncoding=utf-8

jdbc.user=root

jdbc.password=123456

    <!-- 加载配置文件 -->
    <properties resource="db.properties"></properties>
    <!-- 通过${key}来使用 -->
    <property name="driver" value="${jdbc.driverClass}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.user}"/>
    <property name="password" value="${jdbc.password}"/>

settings配置

下面是配置log4j日志

    <settings>
        <!-- 日志的实现类，可以不写，会自动匹配 -->
        <setting name="logImpl" value="LOG4J"/>
        <!-- 将数据库字段的下划线自动转为驼峰命名 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 自动映射，FULL表示无论是否关联都进行自动映射 -->
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>

    # Global logging configuration
    log4j.rootLogger=ERROR, stdout
    # MyBatis logging configuration...
    log4j.logger.com.gxy.mapper=TRACE   //这里写你自己的包
    # Console output...
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

别名

    <!-- 指定包下面的所有实体类都会将类名作为别名 -->
    <typeAliases>
        <package name="com.study.entity"/>
    </typeAliases>
    <!-- 使用的使用直接使用别名Goods，不用写全限定名 -->
    <select id="selectGoodsById" resultType="Goods">
        select * from goods where gid=3
    </select>

关联

关联结果resultMap与resultMap嵌套

    public class Goods {
        private GoodType goodsType;//属性是pojo 普通的java对象
    }

一对一

    <select id="selectGoodsById" resultMap="goodsResultMap">
        select * from goods g inner join data_dictionary d on g.good_type=d.value_id where d.type_code='good_type' and gid=3
    </select>

    <resultMap id="goodsResultMap" type="Goods">
        <!-- 建议主键写上，可以提高mybatis的效率 -->
        <id property="gid" column="gid"/>
        <!--关联结果 association,只能关联一个bean javaType:返回的结果类型 -->
        <association property="goodsType" resultMap="goodTypeResultMap"/>
    </resultMap>
    <!-- 注意：这里要配置好自动映射autoMappingBehavior为FULL，配制后驼峰命名mapUnderscoreToCamelCase为true -->
    <resultMap id="goodTypeResultMap" type="GoodType"></resultMap>

关联resultMap相互嵌套

一对多

<select id="selectGorderByUid" resultMap="gorderResultMap">
    SELECT * from gorder g inner join detail d on g.oid=d.oid where uid=#{value}
</select>

<resultMap id="gorderResultMap" type="Gorder">
    <id property="oid" column="oid"/>
    <collection property="detailList" ofType="Detail" >
        <id property="did" column="did"/>
    </collection>
</resultMap>

关联查询resultMap与select嵌套

一对一

    <resultMap id="goodsResultMap" type="Goods">
            <id property="gid" column="gid"/>
            <association property="goodsType" select="selectGoodType" column="good_type" javaType="GoodsType"/>
        </resultMap>

        <select id="selectGoodsById" resultMap="goodsResultMap">
            select * from goods where gid=3
        </select>

        <select id="selectGoodType" resultType="GoodsType">
            select * from data_dictionary where type_code="good_type" and value_id=#{value}
        </select>

关联resultMap与select相互嵌套

一对多

    <select id="selectGorderByUid" resultMap="gorderResultMap">
        select * from gorder where uid=#{value}
    </select>

    <resultMap id="gorderResultMap" type="Gorder">
        <id property="oid" column="oid"/>
        <!-- select：调用另外一个查询语句  column：传递给另外一个查询语句使用的字段-->
        <collection property="detailList" select="com.study.mapper.DetailMapper.selectDetailByOid" column="oid"/>
    </resultMap>

动态sql

    <select id="searchGoods" resultMap="goodsResultMap" parameterType="Goods">
        select * from goods
        <!-- where会自动去掉第一个and -->
        <where>
            <if test="gname!=null">
                gname=#{gname}
            </if>
            <if test="goodsType.valueId!=null">
                and good_type=#{goodsType.valueId}
            </if>
        </where>
    </select>

set与if组合

    update goods
    <set>
        <if test="gname!=null and gname!=''">
            gname=#{gname}
        </if>
        <if test="goodsType.valueId!=null">
            , good_type=#{goodsType.valueId}
        </if>
    </set>
    where gid=#{gid}

trim与if的组合，可以代替where和set

    update goods
    <trim prefix="set" suffixOverrides=",">
        <if test="gname!=null and gname!=''">
            gname=#{gname},
        </if>
        <if test="goodsType.valueId!=null">
            good_type=#{goodsType.valueId},
        </if>
    </trim>

    select * from goods
    <trim prefix="where" prefixOverrides="and | or">
        <if test="gname!=null">
            and gname=#{gname}
        </if>
        <if test="goodsType.valueId!=null">
            and good_type=#{goodsType.valueId}
        </if>
    </trim>

foreach,一般用在in语句

    select * from goods where gid in
    <foreach collection="list" item="goods" open="(" close=")" separator=",">
        #{goods}
    </foreach>