Addition deletion revision and review of mybatis course _ Power node of Java College

2020-10-31 21:45:33
OfStack

select

1 select element is very simple. Such as:


<!--  Query the students according to id --> 
<select id="getStudent" parameterType="String" resultMap="studentResultMap"> 
  SELECT ST.STUDENT_ID, 
        ST.STUDENT_NAME, 
        ST.STUDENT_SEX, 
        ST.STUDENT_BIRTHDAY, 
        ST.CLASS_ID 
     FROM STUDENT_TBL ST 
     WHERE ST.STUDENT_ID = #{studentID} 
</select>

This statement, called 'getStudent, takes one String argument and returns one object of type StudentEntity.

Note that the identification of the parameter is #{studentID}.

select statement property configuration details:

属性	描述	取值	默认
id	在这个模式下唯1的标识符，可被其它语句引用
parameterType	传给此语句的参数的完整类名或别名
resultType	语句返回值类型的整类名或别名。注意，如果是集合，那么这里填写的是集合的项的整类名或别名，而不是集合本身的类名。（resultType 与resultMap 不能并用）
resultMap	引用的外部resultMap 名。结果集映射是MyBatis 中最强大的特性。许多复杂的映射都可以轻松解决。（resultType 与resultMap 不能并用）
flushCache	如果设为true，则会在每次语句调用的时候就会清空缓存。select 语句默认设为false	true\|false	false
useCache	如果设为true，则语句的结果集将被缓存。select 语句默认设为false true\|false false timeout 设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定	true\|false	false
timeout	设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定	正整数	未设置
fetchSize	设置1个值后，驱动器会在结果集数目达到此数值后，激发返回，默认为不设值，由驱动器自己决定	正整数	驱动器决定
statementType	statement，preparedstatement，callablestatement。预准备语句、可调用语句	STATEMENT PREPARED CALLABLE	PREPARED
resultSetType	forward_only，scroll_sensitive，scroll_insensitive 只转发，滚动敏感，不区分大小写的滚动	FORWARD_ONLY SCROLL_SENSITIVE SCROLL_INSENSITIVE	驱动器决定

insert

1 simple insert statement:


<!--  Insert the students  --> 
<insert id="insertStudent" parameterType="StudentEntity"> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                     STUDENT_NAME, 
                     STUDENT_SEX, 
                     STUDENT_BIRTHDAY, 
                     CLASS_ID) 
       VALUES  (#{studentID}, 
             #{studentName}, 
             #{studentSex}, 
             #{studentBirthday}, 
             #{classEntityclassID}) 
</insert>

insert can use the database-backed auto-generate primary key policy, set useGeneratedKeys= "true", and set keyProperty to the corresponding column, and you're done. For example, StudentEntity above USES ES40en-ES41en to generate the primary key for the id column.

You can also use the selectKey element. The following example USES the mysql database nextval('student') as a custom function to generate 1 key.


<!--  Insert the students   Automatic primary key --> 
<insert id="insertStudentAutoKey" parameterType="StudentEntity"> 
  <selectKey keyProperty="studentID" resultType="String" order="BEFORE"> 
      select nextval('student') 
  </selectKey> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                 STUDENT_NAME, 
                 STUDENT_SEX, 
                 STUDENT_BIRTHDAY, 
                 CLASS_ID) 
       VALUES  (#{studentID}, 
            #{studentName}, 
            #{studentSex}, 
            #{studentBirthday}, 
            #{classEntityclassID})   
</insert>

insert statement property configuration details:

属性	描述	取值	默认
id	在这个模式下唯1的标识符，可被其它语句引用
parameterType	传给此语句的参数的完整类名或别名
flushCache	如果设为true，则会在每次语句调用的时候就会清空缓存。select 语句默认设为false	true\|false	false
useCache	如果设为true，则语句的结果集将被缓存。select 语句默认设为false true\|false false timeout 设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定	true\|false	false
timeout	设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定	正整数	未设置
fetchSize	设置1个值后，驱动器会在结果集数目达到此数值后，激发返回，默认为不设值，由驱动器自己决定	正整数	驱动器决定
statementType	statement，preparedstatement，callablestatement。预准备语句、可调用语句	STATEMENT PREPARED CALLABLE	PREPARED
useGeneratedKeys	告诉MyBatis 使用JDBC 的getGeneratedKeys 方法来获取数据库自己生成的主键（MySQL、SQLSERVER 等关系型数据库会有自动生成的字段）。默认：false	true\|false	false
keyProperty	标识1个将要被MyBatis 设置进getGeneratedKeys 的key 所返回的值，或者为insert 语句使用1个selectKey 子元素。

selectKey statement property configuration details:

属性	描述	取值
keyProperty	selectKey 语句生成结果需要设置的属性。
resultType	生成结果类型，MyBatis 允许使用基本的数据类型，包括String 、int类型。
order	可以设成BEFORE 或者AFTER，如果设为BEFORE，那它会先选择主键，然后设置keyProperty，再执行insert语句；如果设为AFTER，它就先运行insert 语句再运行selectKey 语句，通常是insert 语句中内部调用数据库（像Oracle）内嵌的序列机制。	BEFORE AFTER
statementType	像上面的那样， MyBatis 支持STATEMENT，PREPARED和CALLABLE 的语句形式，对应Statement ，PreparedStatement 和CallableStatement 响应	STATEMENT PREPARED CALLABLE

update, delete

1 Simple update:


<!--  Update student information  --> 
<update id="updateStudent" parameterType="StudentEntity"> 
    UPDATE STUDENT_TBL 
      SET STUDENT_TBL.STUDENT_NAME = #{studentName},  
        STUDENT_TBL.STUDENT_SEX = #{studentSex}, 
        STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday}, 
        STUDENT_TBL.CLASS_ID = #{classEntity.classID} 
     WHERE STUDENT_TBL.STUDENT_ID = #{studentID};   
</update>

1 Simple delete:


<!--  Delete the student  --> 
<delete id="deleteStudent" parameterType="StudentEntity"> 
    DELETE FROM STUDENT_TBL WHERE STUDENT_ID = #{studentID} 
</delete>

update, delete statement property configuration details:

属性	描述	取值	默认
id	在这个模式下唯1的标识符，可被其它语句引用
parameterType	传给此语句的参数的完整类名或别名
flushCache	如果设为true，则会在每次语句调用的时候就会清空缓存。select 语句默认设为false	true\|false	false
useCache	如果设为true，则语句的结果集将被缓存。select 语句默认设为false true\|false false timeout 设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定	true\|false	false
timeout	设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定	正整数	未设置
fetchSize	设置1个值后，驱动器会在结果集数目达到此数值后，激发返回，默认为不设值，由驱动器自己决定	正整数	驱动器决定
statementType	statement，preparedstatement，callablestatement。预准备语句、可调用语句	STATEMENT PREPARED CALLABLE	PREPARED

sql

The Sql element is used to define a reusable SQL statement segment for other statements to call. Such as:


<!--  reuse sql statements   The query student Table all fields  --> 
<sql id="selectStudentAll"> 
    SELECT ST.STUDENT_ID, 
          ST.STUDENT_NAME, 
          ST.STUDENT_SEX, 
          ST.STUDENT_BIRTHDAY, 
          ST.CLASS_ID 
       FROM STUDENT_TBL ST 
</sql>

In this way, the select statement can be used by direct reference. The select statement above is changed to:


<!--  Query the students according to id --> 
<select id="getStudent" parameterType="String" resultMap="studentResultMap"> 
  <include refid="selectStudentAll"/> 
      WHERE ST.STUDENT_ID = #{studentID}  
</select>

parameters

Parameters such as query, modify, delete conditions, insert, modified data, etc., basic data types that MyBatis can use and complex data types of Java are used in many places above.

Basic data types, String, int, date, etc.

However, with the basic data type, you can only provide one argument, so you need to use the Java entity class or the Map type for the argument type. Its properties can be obtained directly through #{}.

Basic type parameter

Retrieve the list of students according to the time of admission:


<!--  Query students list According to the time of admission  --> 
<select id="getStudentListByDate" parameterType="Date" resultMap="studentResultMap"> 
  SELECT * 
   FROM STUDENT_TBL ST LEFT JOIN CLASS_TBL CT ON ST.CLASS_ID = CT.CLASS_ID 
   WHERE CT.CLASS_YEAR = #{classYear};   
</select>


List<StudentEntity> studentList = studentMapper.getStudentListByClassYear(StringUtil.parse("2007-9-1")); 
for (StudentEntity entityTemp : studentList) { 
  System.out.println(entityTemp.toString()); 
}

Java entity type parameter

Retrieve a list of students by name and gender. Use entity classes as parameters:


<!--  Query students list . like The name, = Gender, parameters entity type  --> 
<select id="getStudentListWhereEntity" parameterType="StudentEntity" resultMap="studentResultMap"> 
  SELECT * from STUDENT_TBL ST 
    WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%') 
     AND ST.STUDENT_SEX = #{studentSex} 
</select>


<!--  Insert the students  --> 
<insert id="insertStudent" parameterType="StudentEntity"> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                     STUDENT_NAME, 
                     STUDENT_SEX, 
                     STUDENT_BIRTHDAY, 
                     CLASS_ID) 
       VALUES  (#{studentID}, 
             #{studentName}, 
             #{studentSex}, 
             #{studentBirthday}, 
             #{classEntityclassID}) 
</insert>

Map parameters

Retrieve a list of students by name and gender. Use Map as the parameter:


<!--  Insert the students  --> 
<insert id="insertStudent" parameterType="StudentEntity"> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                     STUDENT_NAME, 
                     STUDENT_SEX, 
                     STUDENT_BIRTHDAY, 
                     CLASS_ID) 
       VALUES  (#{studentID}, 
             #{studentName}, 
             #{studentSex}, 
             #{studentBirthday}, 
             #{classEntityclassID}) 
</insert>


<!--  Insert the students  --> 
<insert id="insertStudent" parameterType="StudentEntity"> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                     STUDENT_NAME, 
                     STUDENT_SEX, 
                     STUDENT_BIRTHDAY, 
                     CLASS_ID) 
       VALUES  (#{studentID}, 
             #{studentName}, 
             #{studentSex}, 
             #{studentBirthday}, 
             #{classEntityclassID}) 
</insert>

Multi-parameter implementation

If you want to pass in more than one parameter, you need to add the @Param annotation to the parameters of the interface. An example is given:

Interface writing:

  

public List<StudentEntity> getStudentListWhereParam(@Param(value = "name") String name, @Param(value = "sex") String sex, @Param(value = "birthday") Date birthdar, @Param(value = "classEntity") ClassEntity classEntity);

SQL writing:


<!--  Query students list . like The name, = Gender, = Birthday, = Class, multi-parameter mode  --> 
<select id="getStudentListWhereParam" resultMap="studentResultMap"> 
  SELECT * from STUDENT_TBL ST 
  <where> 
    <if test="name!=null and name!='' "> 
      ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{name}),'%') 
    </if> 
    <if test="sex!= null and sex!= '' "> 
      AND ST.STUDENT_SEX = #{sex} 
    </if> 
    <if test="birthday!=null"> 
      AND ST.STUDENT_BIRTHDAY = #{birthday} 
    </if> 
    <if test="classEntity!=null and classEntityclassID !=null and classEntityclassID!='' "> 
      AND ST.CLASS_ID = #{classEntity.classID} 
    </if> 
  </where> 
</select>

To inquire:


<!--  Insert the students  --> 
<insert id="insertStudent" parameterType="StudentEntity"> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                     STUDENT_NAME, 
                     STUDENT_SEX, 
                     STUDENT_BIRTHDAY, 
                     CLASS_ID) 
       VALUES  (#{studentID}, 
             #{studentName}, 
             #{studentSex}, 
             #{studentBirthday}, 
             #{classEntityclassID}) 
</insert>

String substitution

By default, using the #{} syntax causes MyBatis to generate PreparedStatement attributes and to use PreparedStatement parameters (=?). To set the value safely. Try to keep them fast and safe and use them often. But sometimes you might want to just plug in an unchanged string into an SQL statement. For example, for ORDER BY, you might use ORDER BY ${columnName} but MyBatis does not modify or circumvent the string.

Note: It is very unsafe to receive and apply 1 user input into an unchanged statement. This allows the user to plant corrupt code, so either ask the field not to allow the customer input, or you check its legitimacy directly.

cache cache

MyBatis includes a strong, configurable, and customizable caching mechanism. The cache implementation of MyBatis 3 has many improvements that are both powerful and easier to configure. By default, caching is not enabled. In addition to session caching, it can improve performance and resolve global dependencies. To enable level 2 caching, you only need to add a simple line 1 to the SQL mapping file: < cache/ >

This simple sentence does the following:

1. All select statements in the mapping file will be cached.
2. All insert,update and delete statements in the mapping file will clear the cache.
3. Cache is reclaimed using the "rarely used recently" algorithm
4. The cache will not be cleared by the set time.
5. Each cache can store 1024 references to lists or objects (regardless of the result of the query).
6. The cache will act as a "read/write" cache, meaning that the retrieved objects are not Shared and are safe for callers. There will be no other calls
7. Potential modification of a thread or thread.

For example, create an FIFO cache to empty once in 60 seconds, store 512 object results or list references, and return results that are read-only. Because two callers in a different thread modify them may cause a reference conflict.


<!--  Insert the students  --> 
<insert id="insertStudent" parameterType="StudentEntity"> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                     STUDENT_NAME, 
                     STUDENT_SEX, 
                     STUDENT_BIRTHDAY, 
                     CLASS_ID) 
       VALUES  (#{studentID}, 
             #{studentName}, 
             #{studentSex}, 
             #{studentBirthday}, 
             #{classEntityclassID}) 
</insert>

You can also share the same cache configuration or instance in a different namespace. In this case, you can use ES224en-ES225en to reference another cache.


<!--  Insert the students  --> 
<insert id="insertStudent" parameterType="StudentEntity"> 
    INSERT INTO STUDENT_TBL (STUDENT_ID, 
                     STUDENT_NAME, 
                     STUDENT_SEX, 
                     STUDENT_BIRTHDAY, 
                     CLASS_ID) 
       VALUES  (#{studentID}, 
             #{studentName}, 
             #{studentSex}, 
             #{studentBirthday}, 
             #{classEntityclassID}) 
</insert>

Cache statement property configuration details:

属性	说明	取值	默认值
eviction	缓存策略： LRU - 最近最少使用法：移出最近较长周期内都没有被使用的对象。 FIFI- 先进先出：移出队列里较早的对象 SOFT - 软引用：基于软引用规则，使用垃圾回收机制来移出对象 WEAK - 弱引用：基于弱引用规则，使用垃圾回收机制来强制性地移出对象	LRU FIFI SOFT WEAK	LRU
flushInterval	代表1个合理的毫秒总计时间。默认是不设置，因此使用无间隔清空即只能调用语句来清空。	正整数	不设置
size	缓存的对象的大小	正整数	1024
readOnly	只读缓存将对所有调用者返回同1个实例。因此都不能被修改，这可以极大的提高性能。可写的缓存将通过序列化来返回1个缓存对象的拷贝。这会比较慢，但是比较安全。所以默认值是false。	true\|false	false