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!
How to write TipsA 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.
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,
<mapper namespane =" src.mapper.MybatisMapper ">.<select id =" select "resultType =" src.mapper.entity.TableEntity "> ~ </ select>, and the result is mapped to TableEntity. Will be.# {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!
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.
# {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.
@Param to the argument of the mapper classSampleParam2.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!
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 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>
select * from table_name where id =?select * from table_name where name =?select * from table_name where age =?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>
or ʻor at the end of the elements in <if> ～ </ if>, they will not be removed!
Also, although it is very detailed, ʻand (half-width space) and ʻor (half-width space) are targeted for removal, so using tabs may not work properly.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>
where, theandat the end ** are removed!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!
<where> ～ </ where> is replaced with ** trim **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>
<set> ～ </ set> is replaced with ** trim **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>
I will take notes from a more practical point of view!
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>
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!
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!
MyBatis Official-Dynamic SQL MyBatis Official --XML File
Recommended Posts