Mybatis实战-各种干货分享

工作以来有感于现在企业的持久层框架基本上会选择mybatis来进行数据库操作，因此在这里总结下mybatis的常用的方法，下面的这篇文章将会就开发中常用的mybaits操作数据库的相关技巧性知识进行总结，不说总结的百分之百吧，基本上开发过程涉及到mybaits操作数据库方面的知识应该都会总结到，对于简单的基本上不会讲解过多，希望能帮助到有需要的人。

Mybatis的常用的配置

通常呢，绝大部分的其他框架集成Mybatis都会涉及到一下的两个配置文件，一个配置文件通常命名为sqlMapConfig.xml,一个命名为mybatis-config.xml

sqlMapConfig.xml的内容如下

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<settings>
		<setting name="cacheEnabled" value="true" />
		<setting name="lazyLoadingEnabled" value="true" />
		<setting name="multipleResultSetsEnabled" value="true" />
		<setting name="useColumnLabel" value="true" />
		<setting name="useGeneratedKeys" value="false" />
		<setting name="autoMappingBehavior" value="PARTIAL" />
		<setting name="defaultExecutorType" value="SIMPLE" />
		<setting name="defaultStatementTimeout" value="25000" />
		<setting name="safeRowBoundsEnabled" value="false" />
		<setting name="mapUnderscoreToCamelCase" value="false" />
		<setting name="localCacheScope" value="SESSION" />
		<setting name="jdbcTypeForNull" value="NULL" />
		<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
	</settings>

	<typeHandlers>
		<typeHandler
			handler="xxx.orm.mybatis.SerializableTypeHandler" />
	</typeHandlers>

	<plugins>
		<plugin
			interceptor="xxx.interceptor.mybatis.StatementHandlerInterceptor">
			<property name="prop1" value="prop1" />
			<property name="prop2" value="prop2" />
		</plugin>
		<plugin
			interceptor="xxx.interceptor.mybatis.ResultSetHandlerInterceptor">
			<property name="prop1" value="prop1" />
			<property name="prop2" value="prop2" />
		</plugin>
	</plugins>
</configuration>

mybatis-config.xml的内容如下

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	   xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd"
	   default-lazy-init="true">

	<description>Spring jdbc 配置</description>

	<!-- MyBatis配置 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
		<property name="typeAliasesPackage" value="xxx.domain" />
		<property name="configLocation" value="classpath:/spring-mybatis/sqlMapConfig.xml" />
		<!-- 显式指定Mapper文件位置 -->
		<property name="mapperLocations" value="classpath:/sql-mapper/*Mapper.xml" />
	</bean>

	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.**.dao" />
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
	</bean>
</beans>

• mybatis准备知识

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--mapper标签的作用是将xml文件中操作数据库中的方法同namespace中定义的接口的方法对应起来-->
<mapper namespace="xxx.dao.yyyDAO" >

<!--这里resultMap的作用是将实体勒种定义的属性同数据库定义的字段一一对应起来，column为数据库中定义的字段，property为实体类型的属性-->
 <resultMap id="BaseResultMap" type="xxx.domain.yyyPOJO" >
    <result column="groupId" property="groupid" jdbcType="INTEGER" />
    <result column="groupName" property="groupname" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
  </resultMap>

<!--定义的sql片段，可以用来进行复用-->
 <sql id="Base_Column_List">	
     res_id,basic_information,self_assessment,professional_skill
 </sql>  

查询相关的操作总结

输入参数为普通类型的
 ResStandardResume selectByPrimaryKey(Integer resId);

<!--resultMap指定查询结果对应类型，parameterType指定输入参数类型，一般为实体类型-->
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from res_standard_resume
        where res_id = #{resId,jdbcType=INTEGER}
</select>

输入参数为实体类型的
ResResume selectByResumeNetID(ResResume resResume);

<select id="selectByResume"resultMap="BaseResultMap"parameterType="xxx.domain.ResResume">
        SELECT
         xxx
        FROM
        res_resume 
        WHERE 1=1
        <if test="resumenetid != null">
            AND resumenetid = #{resumenetid}
        </if>
        <if test="resumeType != null">
            AND resume_type = #{resumeType}
        </if>
 </select>

模糊查询
 <if test="entity.workbranch != null">
      and a.workbranch LIKE CONCAT(CONCAT('%', #{entity.workbranch}), '%')
 </if>

时间类型
<if test="entity.workyears != null">
            <![CDATA[ and a.workyears >= #{entity.workyears} ]]>
</if>

<if test="entity.beginTime != null">
    <![CDATA[ and a.arrive_time >= #{entity.beginTime} ]]>
</if>
<if test="entity.endTime != null">
    <![CDATA[ and a.arrive_time <= #{entity.endTime} ]]>
</if>

集合的遍历
<if test="entity.groupIds != null">
    and (
    a.u_group_id IN
    <foreach item="item" index="index" collection="entity.groupIds" open="(" close=")" separator=",">
        #{item}
    </foreach>
    )
</if>

删除相关

int deleteByPrimaryKey(Integer resId);
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from res_resume
    where res_id = #{resId,jdbcType=INTEGER}
</delete>

插入相关

 int insert(ResResume record);
<insert id="insert" parameterType="xxx.domain.ResResume" useGeneratedKeys="true" keyProperty="resId">
    insert into res_resume (res_id, name, 
    identity_documents, gander
    values (#{resId,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, 
    #{identityDocuments,jdbcType=VARCHAR}, #{gander,jdbcType=VARCHAR}
</insert>

int insertSelective(ResResume record);
<insert id="insertSelective" parameterType="xxx.domain.ResResume" useGeneratedKeys="true" keyProperty="resId">
    insert into res_resume
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="resId != null">
            res_id,
        </if>
        <if test="name != null">
            name,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="resId != null">
            #{resId,jdbcType=INTEGER},
        </if>
        <if test="name != null">
            #{name,jdbcType=VARCHAR},
        </if>
    </trim>
</insert>

更新相关

int releaseResume(@Param("resId")Integer resId);
<update id="releaseResume" parameterType="java.lang.Integer">
    update res_resume
    <set>
        is_lock = 0,
        u_group_id = null,
        u_group_area = null
    </set>
    where res_id = #{resId,jdbcType=INTEGER}
</update>

int updateByPrimaryKeySelective(ResResume record);
<update id="updateByPrimaryKeySelective" parameterType=xxx.domain.ResResume">
    update res_resume
        <set>
            <if test="name != null and name != ''">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="identityDocuments != null and identityDocuments != ''">
                identity_documents = #{identityDocuments,jdbcType=VARCHAR},
            </if>
        </set>
    where res_id = #{resId,jdbcType=INTEGER}
</update>

int updateByPrimaryKey(ResResume record);
<update id="updateByPrimaryKey" parameterType="xxx.domain.ResResume">
    update res_resume
    set name = #{name,jdbcType=VARCHAR},
    identity_documents = #{identityDocuments,jdbcType=VARCHAR},
    gander = #{gander,jdbcType=VARCHAR},
    where res_id = #{resId,jdbcType=INTEGER}
</update>

未完待续…