I found MyBatis useful, so I wrote it.

Let's summarize what we learned in the field series (Introduction)

As it is, it is a series that summarizes the tools etc. learned (used) in the field. I write it with the feeling that it is also for myself and that it should be for someone. We look forward to your suggestions and comments regarding mistakes and description methods!

Edit history

What is MyBatis?

A framework for mapping SQL statements and objects using XML or annotations.

Not to mention normal CRUD operations You can issue SQL dynamically depending on the state of the parameters! If there is a description that can be reused many times, it can be shared and the amount of description can be reduced.

In this article, I will mainly explain the description of XML.

Basic description and usage

Anyway, it's faster to see the source, so please see below.

Sample.xml


<?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="src.mapper.MybatisMapper">

  <select id="select" resultType="src.mapper.entity.TableEntity">
    select
        *
    from 
        table_name
  </select>

  <insert id="insert">
    insert into table_name (
      id,
      name,
      age,
      department
    ) values (
      #{id},
      #{name},
      #{age},
      #{department}
    )
  </insert>

</mapper>

MyBatisMepper.java


package src.mapper

//import omitted

public interface MyBatisMapper {
  TableEntity select();

  void insert(TableEntity entity);
}

TableEntity.java


package src.mapper.entity

//import omitted

public class TableEntity {
  private String id;
  private String name;
  private Integer age;
  private String department;

  public void setId(String id) {
    this.id = id;
  }

  public String getId() {
    return this.id;
  }

  //Getters and setters for all fields
}

To briefly explain,

  1. Associate with /src/mapper/MybatisMapper.java with <mapper namespane =" src.mapper.MybatisMapper ">.
  2. Select is executed by executing MybatisMapper.select () with <select id =" select "resultType =" src.mapper.entity.TableEntity "> ~ </ select>, and the result is mapped to TableEntity. Will be.
  3. If you want to pass a parameter, you can receive it by writing # {id}. In the above, the value is passed as an object, but it can also be received as an argument of the Mapper class by using @Param.

It's very easy to link with java classes and methods, so it's intuitively easy to understand!

How to pass parameters

If you can prepare a data class as described above, you can map by field name. (I feel like it would have been useless without a getter) However, for various reasons, for those who want to pass it as an argument of the mapper class! Here are some ways to do it.

Set the XML parameter to # {param1}

SampleParam1.xml


  <select id="select" resultType="src.mapper.entity.TableEntity">
    select
        *
    from 
        table_name
    where
        id = #{param1}
        and name = #{param2}
  </select>

MyBatisMepper.java


package src.mapper

//import omitted

public interface MyBatisMapper {
  TableEntity select(String id, String name);
}

If you write like this, # {param1} will have the first argument ʻid, The second argument, name, is mapped to # {param2} `! It is difficult to understand that it is personally mapped in the order of arguments, so it is not recommended.

Add @Param to the argument of the mapper class

SampleParam2.xml


  <select id="select" resultType="src.mapper.entity.TableEntity">
    select
        *
    from 
        table_name
    where
        id = #{id}
        and name = #{name}
  </select>

MyBatisMepper.java


package src.mapper

//import omitted

public interface MyBatisMapper {
  TableEntity select(@Param("id") String id, @Param("name") String name);
}

By specifying an alias with a character string in the argument of @ Param You can map parameters in XML. Personally, it's the easiest to understand and recommended!

Pass by map

Just like creating a data class How to create a map with key = parameter name and value = value. [Click here for details](https://qiita.com/opengl-8080/items/a9b5d4038f19d4813ee6#map-%E3%81%A7%E3%83%91%E3%83%A9%E3%83%A1%E3 Please see% 83% BC% E3% 82% BF% E3% 82% 92% E6% B8% A1% E3% 81% 99). (If you do this, I think it's easier to understand if you create a data class.)

Next, I will introduce how to write dynamic SQL!

Dynamic SQL

Dynamic SQL means that you can dynamically change the SQL issued depending on the state of the parameter, as mentioned above! Specifically, it is as follows!

if/choose/where,set,trim For example, you have to write multiple similar SELECT statements because the referenced columns are the same but the conditions (WHERE clause) are different. .. That's a shame, isn't it? ** if / choose / where, set, trim **, which will be introduced below, is a convenient description method when you want to change the conditions of the WHERE clause or when you want to unify the UPDATE statement.

if

SampleIf.xml


  <update id="update">
    update table_name 
    set
      <if test="name != null">
        name = #{name},
      </if>
      age = #{age}
    where
      id = #{id}
  </update>

Depending on the situation, I would like to choose from more conditions than two choices! I think that there will be cases like that. In that case, please use ** choose (when, otherwise) **. (I haven't seen it so much) choose(when,otherwise)

SampleChoose.xml


  <select id="select">
    select
      *
    from
      table_name
    where
      <choose>
        <when test="id != null">
          id = #{id}
        </when>
        <when test="name != null">
          name = #{name}
        </when>
        <otherwise>
          age = #{age}
        </otherwise>
      </choose>
  </select>

where,set,trim ** where, set, trim ** are useful when you want to work with a bit of awkward SQL. For example, take the following SQL.

Sample2.xml


  <select id="select2" resultType="src.mapper.entity.TableEntity">
    select
      *
    from
      table_name
    where
      <if test="id != null">
        id = #{id}
      </if>
      <if test="name != null">
        and name = #{name}
      </if>
      <if test="age != null">
        and age = #{age}
      </if>
  </update>

If all ifs are not met, the SQL select * from table_name where will be issued and a syntax error will occur. Also, if the parameter .name is not null and the parameters .id and age are null, then select * from table_name where and name =? Will occur, which will also result in a syntax error.

where If you use <where> ~ </ where>, it will insert where only when one of the included tags returns a result. Also, if the result of the contained tag starts with ʻand or ʻor, it will remove them! How convenient it is! .. ..

SampleWhere.xml


  <select id="select2" resultType="src.mapper.entity.TableEntity">
    select
      *
    from
      table_name
    <where>
      <if test="id != null">
        id = #{id}
      </if>
      <if test="name != null">
        and name = #{name}
      </if>
      <if test="age != null">
        and age = #{age}
      </if>
    </where>
  </select>

set This is the set clause version of where. If you use <set> ~ </ set>, the extra , will be deleted.

SampleSet.xml


  <update id="update">
    update table_name 
    <set>
      <if test="name != null">
        name = #{name},
      </if>
      <if test="age != null">
        age = #{age},
      </if>
      <if test="department != null">
        department = #{department}
      </if>
    </set>
    where
      id = #{id}
  </update>

trim Customization is possible by defining where and set explained above in <trim> ~ </ trim>. It may seem subtle considering readability, but I think it's worth knowing!

SampleTrim1.xml


  <select id="select2" resultType="src.mapper.entity.TableEntity">
    select
      *
    from
      table_name
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <if test="id != null">
        id = #{id}
      </if>
      <if test="name != null">
        and name = #{name}
      </if>
      <if test="age != null">
        and age = #{age}
      </if>
    </trim>
  </select>

SampleTrim2.xml


  <update id="update">
    update table_name 
    <trim prefix="SET" suffixOverrides=",">
      <if test="name != null">
        name = #{name},
      </if>
      <if test="age != null">
        age = #{age},
      </if>
      <if test="department != null">
        department = #{department}
      </if>
    </trim>
    where
      id = #{id}
  </update>

sql,include For example, if you need a SELECT statement for acquisition and a SELECT statement for update, Isn't it like that when I wrote it, there was only a difference with or without for update at the end? In such a case, using <sql> ~ <sql> and <include> ~ </ include> will make it cleaner!

SampleSql.xml


  <sql id="selectBase">
    select
      *
    from
      table_name
    where
      id = #{id}
  </sql>

  <select id="select" resultType="src.mapper.entity.TableEntity">
    <include refid="selectBase" />
  </select>

  <select id="selectForUpdate" resultType="src.mapper.entity.TableEntity">
    <include refid="selectBase" />
    for update
  </select>

How to write tips

I will take notes from a more practical point of view!

I want to write a LIKE phrase

In MyBatis, there are two ways to write variables.

--# {param}… Escape and enclose in single quotes. --$ {param}… Do not escape.

The following is a common way to write. (If you want to match the prefix with id)

SampleSql.xml


  <select id="select3" resultType="src.mapper.entity.TableEntity">
    select
      *
    from
      table_name
    where
      id like '${id}%'
  </select>

This way of writing will still issue the correct query, As mentioned above ** There is a risk of SQL injection because it does not escape. ** **

Make sure to use the ** string concatenation provided by each SQL. ** ** The following is for Oracle.

SampleSql.xml


  <select id="select3" resultType="src.mapper.entity.TableEntity">
    select
      *
    from
      table_name
    where
      id like #{id} || '%'
  </select>

I want to use the inequality sign

Since MyBatis is described in XML, the inequality sign <> is treated as a metacharacter, The query cannot be read normally. In such a case, you can use it by enclosing it in <![CDATA [...]]>!

SampleSql.xml


  <select id="select4" resultType="src.mapper.entity.TableEntity">
    <![CDATA[ 
    select
      *
    from
      table_name
    where
      create_at < #{date}
    ]]>
  </select>

I think it is more readable to enclose the entire SQL than to enclose it with pinpoints. I personally recommend the above writing style!

Finally

When I try to output like this, I understand how I ** just use it and don't understand it **. I will continue to post articles on a regular basis, so I would appreciate it if you have another chance. Until the end Thank you for reading!

reference

MyBatis Official-Dynamic SQL MyBatis Official --XML File

Recommended Posts

I found MyBatis useful, so I wrote it.
I found Lombok useful so I wrote it
Methods that I found useful in Ruby
I passed Ruby Silver (June 2020), so I will summarize it.
firewalld went wrong, so I managed to fix it.
I wrote a CRUD test with SpringBoot + MyBatis + DBUnit (Part 1)
parquet-tools gives java.lang.ExceptionInInitializerError, so I made it work with java8
Java SE 13 (JSR388) has been released so I tried it