Common SQL statements in MyBatis

1. Input of variables : #,$

In the xnm file,

1) by #{parameter name} to put parameters into the SQL statement, according to the data type input

Example: SELECT * FROM Table a where A.id=#{id}

2) to represent ordinary characters by ${parameter name}

For example: SELECT * from A_${id} A where A.id=#{id}, if string id=10,

Then equivalent to: SELECT * from A_10 A where a.id= ' 10 '

2. Conditional sentences

A. When you perform a conditional query, the statement is used:

SELECT * FROM Table a where 1=1

<if test="a.id!=null and a.id! =" "> and a.ID =#{id}</if>

<if test="a.name!=null and A.name! =" "> and A.name =#{name}</if>

......

B. When an INSERT UPDATE statement is executed,

Update a Table A set

<if test="a.id!=null and a.id! =" "> a.ID =#{id},</if>

<if test="a.name!=null and A.name! =" "> A.name =#{name},</if>

A.age=#{age}

......

C. Fuzzy query

SELECT * FROM Table a where 1=1

<if test="a.id!=null and a.id! =" "> and a.ID =#{id}</if>

<if test="a.name!=null and A.name! =" ""> and a.name like concat ('% ', #{name}, '% ') </if>

......

The core idea is: Splicing SQL statements. If necessary, you can also extract the IF condition clause. It is then referenced again, for example:

<select id="xxx" parametertype="com.xxx.xxxx" resulttype ="com.xxx.yyy">

SELECT * FROM Table a where 1=1

<include refid="yyy"><include>

</select>

<sql id="yyy">

<if test="a.id!=null and a.id! =" "> and a.ID =#{id}</if>

<if test="a.name!=null and A.name! =" "> and A.name =#{name}</if>

......

</sql>

Direct use in D.sql

SELECT IF (' status ' = 1, ' normal ', ' disable ') as ' status ' from area

Case Condition Statement: Change value to other information output

SELECT ' name ', Case ' status '

When 1 Then ' draft '

When 2 Then ' submit '

When 3 Then ' audit '

ELSE ' No ' END ' status '

From Table A

3. Greater than less than symbol

In MyBatis, the ">", "<" symbol in the SQL statement is not recognized, so you should use the "&gt;" Instead of ">", "&lt;" Instead of "<"

Common SQL statements in MyBatis