MyBatis持久化层技术

MyBatis技术总结模板

分类:JavaEE框架技术之Mybatis
作者:LL

  • 什么是MyBatis?

    MyBatis 框架也被称之为 ORM(Object/Relational Mapping,即对象关系映射)框架。MyBatis是基于jdbc的持久化框架;它将数据库中的表与java中的实体类通过映射文件(xxxMapper.xml)与配置文件(config.xml)联系起来。支持普通 SQL查询,存储过程和高级映射的优秀持久层框架。

  • MyBatis中的主要组成部分

    MyBatis主要由配置文件(mybatisConfig.xml)、映射文件(xxxMapper.xml)、第三方jar包(核心jar包)、SqlSessionFactoryBuilder(类)、SqlSessionFactory(接口)、SqlSession(接口)、Resources(类)。

    SqlSession的实现类是DefaultSqlSession(可以通过控制台打印出来观察)
    SqlSessionFactory的实现类是:DefaultSqlSessionFactory

    SqlSessionFactory是用来创建SqlSession(会话的意思:连接+数据库操作+结果集+关闭)

    SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在,没有任何理由丢弃它或重新创建另一个实例。 使用 SqlSessionFactory 的最佳实践是在应用运行期间不要重复创建多次,多次重建 SqlSessionFactory 被视为一种代码“坏习惯”。因此 SqlSessionFactory 的最佳作用域是应用作用域。 有很多方法可以做到,最简单的就是使用单例模式或者静态单例模式。

    SqlSessionFactoryBuilder实例 随着SqlSessionFactory的创建而销毁,所以它的最佳作用域是方法作用域(也就是局部方法变量)。

    SqlSessionFactory创建代码示例:
    //1.读取配置文件
    InputStream in = Resources.getResourceAsStream("mybatis-config.xml");//注意配置文件放在类路径下(即src下面);
    SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(in);
    Sqlsession:每个线程都应该有它自己的 SqlSession 实例。SqlSession 的实例不是线程安全的,因此是不能被共享的,所以它的最佳的作用域是请求或方法作用域。
    SqlSession创建代码示例:
    SqlSession session=factory.openSession();
    
  • MyBatis配置

    • 设置(settings)

    主要作用:开启日志文件与MyBatis框架运行规则配置

    设置(settings)代码示例:
    <setting name="logImpl" value="LOG4J"/>value指定具体使用的日志 ,name是指定的(不能变的),这里的value可以是"STDOUT_LOGGING",若是log4j则需要写外部的properties文件
    
    • 类型别名(typeAliases)

    主要作用:类型别名是为 Java 类型设置一个短的名字。它只和 XML 配置有关,存在的意义仅在于用来减少类完全限定名的冗余。

    类型别名(typeAliases)代码示例:
    <!-- 给resultType起一个别名,注意这里是实体类所在的包名,而不是映射接口所在的包名千万注意 -->
    <typeAliases>
      <typeAlias type="com.apesource.entity.Employee" alias="Employee"/>
    </typeAliases>
    
    <typeAliases>
    		<!-- 设置实体类包,为该package中的每个实体类自动设置别名,这样的话就可以在      resultType这里直接写实体类的名字了-->
    		<package name="com.apesource.entity"/>
    </typeAliases>
    
    • 环境配置(environments)

    主要作用:MyBatis 可以配置适应多种环境,这种机制有助于将 SQL 映射应用于多种数据库之中,如:测试环境,生产环境(实际的使用环境),一个environments下有多个environment

    环境配置(environments)代码示例:
    <environments default="development">
    	 <!-- 环境 :默认是开发环境,也可也有多个环境(如:测试环境,生产环境(实际的使用环境))-->
    	    <environment id="development">
    	    <!-- 数据库的事务管理器:jdbc -->
    	      <transactionManager type="JDBC"/>
    	      <!-- 数据源(数据库连接池) -->
    	      <dataSource type="POOLED">
    	        <property name="driver" value="${jdbc_driver}"/>
    	        <!-- &amp;表示的转义字符,因为mybatis中&是关键字,所以要转义 -->
    	        <property name="url" value="${jdbc_url}"/>
    	        <property name="username" value="${jdbc_user}"/>
    	        <property name="password" value="${jdbc_password}"/>
    	      </dataSource>
    	    </environment>
    </environments>
    
    • 映射器(mappers)

    主要作用:将实体类与数据库中的表映射起来,每个实体类对应一个xxxMapper.xml映射文件(里面写SQL语句);

    映射器(mappers)代码示例:
    <?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 namespace="com.apesource.dao.mapper.AnswerRecordMapper">
       <insert id="insertAnswerRecord" parameterType="AnswerRecord" useGeneratedKeys="true" keyProperty="recordId">
            insert into answer_record (respondent,question,right_answer,submit_answer,submit_datetime)
            values(#{respondent},#{question},#{rightAnswer},#{submitAnswer},now())<!-- #{submitDatetime}可以用now()函数替换 -->
       </insert>
       <delete id="deleteAnswerRecord" parameterType="int">
            delete from answer_record where 
            record_id=#{recordId}
       </delete>
       
       <update id="updateAnswerRecord" parameterType="AnswerRecord">
           update answer_record 
        		              <set>
        		                 <if test="respondent!=null">respondent=#{respondent},</if>
        		                 <if test="question!=null">question=#{question},</if>
        		                 <if test="rightAnswer!=null">right_answer=#{rightAnswer},</if>
        		                 <if test="submitAnswer!=null">submit_answer=#{submitAnswer},</if>
        		                 submit_datetime=now()<!-- 由于时间是函数调用得到的所以不用修改就行 -->
       		              </set>
                        where record_id=#{recordId}
        </update>
        
    <select id="listAnswerRecordByRespondent" resultType="AnswerRecord">
        select record_id as recordId,
               respondent as respondent ,
               question    as question,
               right_answer  as rightAnswer,
               submit_answer  as submitAnswer,
                submit_datetime  as submitDatetime
        from   answer_record   
        where respondent=#{respondent}
    </select>
       <select id="countAnswerRecordDataByRespondent" parameterType="AnswerRecord" resultType="map">
            SELECT COUNT(record_id) AS totalAnswer,
      (SELECT COUNT(record_id)  FROM answer_record 
      WHERE right_answer=submit_answer AND respondent='洪七公')
      AS rightAnswer,
      (SELECT COUNT(record_id)   FROM answer_record
       WHERE right_answer!=submit_answer AND respondent='洪七公')
      AS wrongAnswer
    FROM answer_record    
        WHERE respondent='洪七公'
       </select>
       
       <!-- 进行批量的插入数据,这里的list是List的别名,因为传入的是集合,返回值(影响的行数)默认不用写(int的类型) -->
        <insert id="insertBatchData" parameterType="list">
            INSERT INTO answer_record(respondent,question,right_answer,submit_answer,submit_datetime)VALUES
               <foreach collection="list" item="record" separator=",">
                     (
                       #{record.respondent},
                       #{record.question},
                       #{record.rightAnswer},
                       #{record.submitAnswer},
                      NOW()
                      
                     )
               </foreach>
        </insert>
        
        <delete id="deleteBatchData" parameterType="list">
        
          DELETE FROM answer_record WHERE
                         record_id  IN
                     <foreach collection="list" item="rid" open="(" close=")" separator=",">
                        #{rid}<!-- 表示的是遍历出的集合中的id值 -->
                      </foreach>
       </delete>
       
        <!-- 根据多个并行条件进行查询 -->
        </mapper>
        ```
    
  • MyBatis XML 映射器

    1.常用节点作用总结

    • select :用于查询的SQL语句书写
    <select id="listAnswerRecordByRespondent" resultType="AnswerRecord">
              select record_id as recordId,
                     respondent as respondent ,
                     question    as question,
                     right_answer  as rightAnswer,
                     submit_answer  as submitAnswer,
                      submit_datetime  as submitDatetime
              from   answer_record   
              where respondent=#{respondent}
    </select>
    
    • update:用于更新的SQL语句的书写
    <update id="updateAnswerRecord" parameterType="AnswerRecord">
             update answer_record 
    		  <set>
    		    <if test="respondent!=null">respondent=#{respondent},</if>
    		    <if test="question!=null">question=#{question},</if>
    		    <if test="rightAnswer!=null">right_answer=#{rightAnswer}</if>
    		    <if test="submitAnswer!=null">submit_answer=#{submitAnswer}</if>
    		  submit_datetime=now()<!-- 由于时间是函数调用得到的所以不用修改就行 -->
    		  </set>
              where record_id=#{recordId}
    </update>
    
    • delete:用于删除的SQL语句书写
    <delete id="deleteBatchData" parameterType="list">
         
           DELETE FROM answer_record WHERE
                 record_id  IN
                 <foreach collection="list" item="rid" open="(" close=")" separator=",">
                          #{rid}<!-- 表示的是遍历出的集合中的id值 -->
                 </foreach>
    </delete>
    
    • insert:用于插入数据的sql语句书写
    <insert id="insertAnswerRecord" parameterType="AnswerRecord" useGeneratedKeys="true" keyProperty="recordId">
             insert into answer_record (respondent,question,right_answer,submit_answer,submit_datetime)
             values(#{respondent},#{question},#{rightAnswer},#{submitAnswer},now())<!-- #{submitDatetime}可以用now()函数替换 -->
    </insert>
    

    2.常用属性作用总结

    • id 属性:里面写一个接口(xxxMapper.java)中的方法的名字
    • resultType 属性:是返回结果的类型
    • parameterType 属性:传入参数的类型
    • useGeneratedKeys属性:设置是否主键回填
    • keyProperty属性用于接收返回的主键的属性(一般是实体类的id属性)

    3.常见SQL映射示例

    示例1:普通增加

    SQL映射配置
    <insert id="insertAnswerRecord" parameterType="AnswerRecord" useGeneratedKeys="true" keyProperty="recordId">
    insert into answer_record (respondent,question,right_answer,submit_answer,submit_datetime)
    values(#{respondent},#{question},#{rightAnswer},#{submitAnswer},now())<!-- #{submitDatetime}可以用now()函数替换 -->
    </insert>
    
    接口方法定义
    int insertAnswerRecord(AnswerRecord answerRecord);
    

    示例2:批量增加

    <insert id="insertBatchData" parameterType="list">
    INSERT INTO answer_record(respondent,question,right_answer,submit_answer,submit_datetime)VALUES
    <foreach collection="list" item="record" separator=",">
    (
    #{record.respondent},
    #{record.question},
    #{record.rightAnswer},
    #{record.submitAnswer},
    NOW()
    
    )
    </foreach>
    </insert>SQL映射配置
    
    接口方法定义
    int insertBatchData(List<AnswerRecord>listBat);
    

    示例3:普通删除

    SQL映射配置
    <delete id="deleteAnswerRecord" parameterType="list">
    DELETE FROM answer_record WHERE record_id = #{recordId}
    </foreach>
    </delete>
    
    接口方法定义
    int deleteAnswerRecord(int recordId);
    

    示例4:批量删除

    <delete id="deleteBatchData" parameterType="list">
    DELETE FROM answer_record WHERErecord_id  IN
    <foreach collection="list" item="rid" open="(" close=")" separator=","> #{rid}<!-- 表示的是遍历出的集合中的id值 -->
    </foreach>
    </delete>
    
    接口方法定义
    int deleteBatchData(List<Integer>listBatId);
    

    示例5:动态修改

    SQL映射配置
    <update id="updateAnswerRecord" parameterType="AnswerRecord">
    update answer_record 
    <set>
    <if test="respondent!=null">respondent=#{respondent},</if>
    <if test="question!=null">question=#{question},</if>
    <if test="rightAnswer!=null">right_answer=#{rightAnswer},</if>
    <if test="submitAnswer!=null">submit_answer=#{submitAnswer},</if>
    submit_datetime=now()<!-- 由于时间是函数调用得到的所以不用修改就行 -->
    </set>
    where record_id=#{recordId}
    </update>
    
    接口方法定义
    int updateAnswerRecord(AnswerRecord answerRecord);
    

    示例6:动态查询

    <select id="queryAllByCondition"  resultType="AnswerRecord" parameterType="AnswerRecord">
    SELECT  record_id AS recordId,
    respondent,<!-- 因为字段名字与实体类的一致所以不用起别名-->
    question,
    right_answer AS rightAnswer,
    submit_answer AS submitAnswer,
    submit_datetime AS submitDatetime
    FROM answer_record
    <!-- 动态sql会将第一个and忽略掉-->
    <where>
    	<if test="respondent != null">AND respondent = #{respondent}</if>
    	<if test="question != null">AND question LIKE concat('%',#{question},'%')</if>	  <if test="rightAnswer != null">AND right_answer = #{rightAnswer}</if>
    <if test="submitAnswer != null">AND submit_answer = #{submitAnswer}</if>
    </where>
    </select>
    
    接口方法定义
    List<AnswerRecord> queryAllByCondition(AnswerRecord condition);
    

    示例7:查询结果封装为Map

    SQL映射配置
    <select id="countAnswerRecordDataByRespondent" resultType="map">
    	SELECT count(record_id) as total,
    		 (SELECT count(record_id) FROM answer_record WHERE respondent = #{name} AND right_answer = submit_answer ) as right_count,
    		 (SELECT count(record_id) FROM answer_record WHERE respondent = #{name} AND right_answer != submit_answer ) as fail_count
    	FROM answer_record
    	WHERE respondent = #{name}
    
    </select>
    
    接口方法定义
    Map<String,Integer> countAnswerRecordDataByRespondent(String respondent);
    
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页