Variable # and $ in MyBatis

An error occurred using the SELECT top #num # * from TableName in Ibatis. Since the initial use of Ibatis does not know the spelling of the SQL statements in it some of the rules, resulting in some self-considered very common SQL statements, in it overturned the ship.

Select Top #number # This is not the correct wording, the reason unknown origin. The correct way to write is select top $.

The following paragraph is found in the network it does not give a specific explanation, just say what SQL dynamic writing, unknown so.

In Ibatis, for the top parameter, only dynamic SQL is used. Such as
<select id= "getclasslatest" parameterclass= "map" resultmap= "Musicitemmap" >
SELECT Top $ * from Tbl_music_item where class_id= #classId # ORDER BY add_time Desc
</select>

The difference between #与 $

Yesterday in a project in the Write Ibatis SQL statement, ORDER by #field #, the runtime always error, and later on the Internet check, just know here should not use #, and should use $, then check the next # and the difference between $.
Summarized as follows:
1. #是把传入的数据当作字符串, such as #field# incoming ID, then SQL statement generation is so, order by "id", which of course will error.

2.$ incoming data is generated directly in SQL, such as $field$ incoming ID, then SQL statement generation is so, order by ID, that's right.

3. #方式能够很大程度防止sql注入.

The 4.$ method does not prevent SQL injection.

The 5.$ method is typically used to pass in database objects. For example, incoming table names.

6. Do not use the $ in general.

MyBatis Incoming parameters and ParameterType

The SELECT, INSERT, update, and delete elements in the MyBatis mapper file have a ParameterType property that is used for the parameter types that the corresponding Mapper interface method accepts.

The types of parameters that can be accepted are primitive types and complex types.

The Mapper interface method generally takes a parameter, and you can bind multiple parameters to a map as input parameters by using the @param annotation.

1. Simple data types

Mapper Interface Method:

User Selectbyprimarykey (Integer ID);

SQL mapping:

 <  select  id  = "Selectbyprimarykey"   Resultmap  = "Baseresultmap"   ParameterType  = "Java.lang.Integer"  >   select  <  include  refid  = "Base_column_list"  />   from base.tb_user where id = #{id,jdbctype=integer}  </ select  >  

For a simple data type, the SQL mapping statement directly #{the variable name} in this way, the "variable name" can be arbitrary. The Mapper interface method passes over the value, as to what its name is actually not test and need not know.
and Java reflection can only get the type of the method parameter, it is impossible to know the name of the method parameter.

For example above, using #{id} to refer to is just more intuitive, using other names to refer to the same. So when you pass a parameter in the IF element, you have to use _parameter to refer to the parameter. Like this:

<SelectID= "Selectbyprimarykey"Resultmap= "Baseresultmap"ParameterType= "Java.lang.Integer" >Select<includerefID= "Base_column_list" />From Tb_user<ifTest= "_parameter! = 0">WHERE id = #{id,jdbctype=integer}</if></Select>

If you use the ID in test conditions, you will be prompted with an error because the parameter does not actually have a name, only a value or reference, and can only be referenced using _parameter.

1. Object type

In the case of a Java complex object type, the attribute name of the object can be directly referenced in the SQL mapping statement, where the property name is the actual real name and is not arbitrarily specified.
Mapper Interface Method:

int Insert (user user);

SQL mapping:

<id= "Insert"  parametertype= "User"  usegeneratedkeys = "true" Keyproperty = "id" >   INSERT INTO tb_user (name, sex)  values (#{name,jdbctype=char}, #{sex,jdbctype=char})

Although you can explicitly refer to the property name of an object, if you want to test the incoming user parameter in the IF element, you still use _parameter to refer to the actual argument passed in, because the name of the user object passed in is not testable. If you test the properties of an object, you can simply refer to the property name.

To test the user object:

<test= "_parameter! = null">

To test the properties of the user object:

<test= "Name! = NULL">

1. Map type

Incoming map type, directly through #{keyname} can refer to the value corresponding to the key. Multiple parameter values that use the @param annotation are also assembled into a map data structure, and there is no difference in passing the map directly.

Mapper Interface:

int Updatebyexample (@Param ("user") user user, @Param ("example") userexample example);

SQL mapping:

<UpdateID= "Updatebyexample"ParameterType= "Map" >update tb_user Set id = #{user.id,jdbctype=integer}, ...<offTest= "_parameter! = null" >    <includerefID= "Update_by_example_where_clause" />  </if>

Note here that the test passed in the map is empty, still using _parameter

1. Collection type

You can pass a List of instance or an Array to MyBatis as a parameter object. When you do, MyBatis'll automatically wrap it in a Map, and key it by name. List instances would be keyed to the name "list" and array instances would be keyed to the name "array".

You can pass an object of list or array type as a parameter, MyBatis automatically wraps the list or array object into a Map object, and the list type Object uses list as the key name, and the array object uses array as the key name.

A collection type is typically used to construct an in condition that uses a foreach element in a SQL map file to traverse a list or array element.

Mapper Interface:

User selectuserinlist (list<interger> idlist);

SQL Dynamic Statement Mapping:

<SelectID= "Selectuserinlist"Resulttype= "User">SELECT * from USER WHERE ID in<foreachItem= "Item"Index= "Index"Collection= "List"Open="("Separator=","Close=")">#{item}</foreach></Select>

1. Collection properties in the object type

For a single pass-through list or array, when mapped in a SQL mapping file, it can only be referenced by a list or array. However, if the object type has a property of type list or array, then in the foreach element of the SQL mapping file, you can use the property name directly to refer to it.
Mapper Interface:

List<user> Selectbyexample (userexample example);

SQL mapping File:

<where >  <foreach collection= "Oredcriteria" item= "Criteria" separator= "or" >    <if test= "Criteria.valid" >

Here, Userexample has a property called Oredcriteria, whose type is list, so you can refer to the list directly using the property name Oredcriteria in the foreach element.

Item= "Criteria" means using the criteria name to refer to each list or array element in each collection

Variable # and $ in MyBatis