The code is placed directly in the Github repository[ https://github.com/Damaer/Mybatis-Learning ], it can be run directly, so it doesn't take up space.

• 1. #{} placeholder
• 2. ${} splice
• 3. #{} and ${} difference

1. #{} placeholder

1. #{} placeholders can be used to set parameters. If the basic type is passed in, that is (string,long,double,int,boolean,float, etc.), the variable name in #{} can be written at will, such as abc,xxx, etc. this name can be inconsistent with the parameter name passed in.

2. If pojo type is passed in, the variable name in #{} must be the property name of pojo, which can be written as property name or property name Property name.

The parameter is int, so you don't need to set parameterType:

<delete id="deleteStudentById" >
    delete from student where id=#{XXXdoukeyi}
</delete>

parameterType is a pojo class. If a pojo type is used as a parameter, a get method must be provided. That is, when the framework runs, it needs to get the value through reflection according to the name in #{} and put it into the sql statement. If the name and attribute in the placeholder are inconsistent, it will report ReflectionException.

<insert id="insertStudent" parameterType="Student">
 insert into student(name,age,score) values(#{name},#{age},#{score})
</insert>

3. #{} placeholders cannot solve three types of problems:

Dynamic table name cannot be used #{}: Select * from #{table} dynamic column name cannot be used #{}: select #{column} from table dynamic sorting column cannot be used #{}: select * from table order by #{column}

Note: it cannot be written like this:

<insert id="insertStudent" parameterType="Student">
 insert into student(name,age,score) values(#{Student.name},#{Student.age},#{Student.score})
</insert>

Otherwise, an error will be reported (Student will be regarded as an attribute), so we can directly omit the class name without writing it:

### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'Student' in 'class bean.Student'

2. ${} splice

1. If the basic type is passed in, that is (string,long,double,int,boolean,float, etc.), the variable name in #{} must be written value.

<delete id="deleteStudentById" >
    delete from student where id=${value}
</delete>

2. If pojo type is passed in, the variable name in #{} must be the property name of pojo, which can be written as property name or property name Property name. However, due to the splicing method, we need to add quotation marks for the string.

<insert id="insertStudent" parameterType="Student">
 insert into student(name,age,score) values('${name}',${age},${score})
</insert>

As above, the class name cannot be written in:

<!--This is wrong-->
<insert id="insertStudent" parameterType="Student">
 insert into student(name,age,score) values('${Student.name}',${Student.age},${Student.score})
</insert>

3. ${} placeholder is a string connector, which can be used to dynamically set table name, column name and sorting name

• Dynamic table name: Select * from ${table}
• Dynamic column name: select ${column} from table
• Dynamic sorting: select * from table order by ${column}

4. ${} can be used as a connector, but this method is not safe and is prone to sql injection problems. For sql injection problems, please refer to:

JDBC [5] - Comparison between JDBC precompiled and spliced Sql

<select id="selectStudentsByName" resultType="Student">
    select id,name,age,score from student where name like '%${value}%'
</select>

3. #{} and ${} difference

• 1. When you can use #{}, try to use #{} instead of ${}.
• 2. #{} is equivalent to the preparedstatement (precompiled) in jdbc. ${} is directly spliced with the values inside. If precompiling and direct splicing are explained, I think precompiling can be understood as follows: for example, pass in a #{name}. Precompiling is to compile the sql statement into a template first, that is, I know what you want to do, Assuming that this sql is to query the student information named xxx, no matter what information is in xxx, I will only query according to the name column. No matter what is written in it, it will only be treated as a string. This type has been defined during precompiling.
• 3. ${} is different. The query condition / type is determined only after the statements are spliced. Then there will be the possibility of being injected. Some people deliberately set the name as the deletion condition. At this time, sql becomes a deletion operation.

We generally use #{} placeholders for similar fuzzy queries:

<select id="selectStudentsByName" resultType="Student">
    select id,name,age,score from student where name like '%' #{name} '%'
</select>

• But we can't use #{} the order by, because if we use it, it will be automatically converted into a string and quoted, so the statement won't take effect.

<select id="selectStudentsByName" resultType="Student">
    select id,name,age,score from student order by #{column}
</select>

<!--The compiled results are as follows:-->
select * from table order by 'column'

So what do we need to do? We can only use ${}, and MyBatis does not modify or escape strings. This is not safe and will lead to potential SQL injection attacks. We need to restrict ourselves and do not allow users to enter these fields, or usually escape and check them by ourselves. So this must filter the input.