Make writing a habit together! This is the 14th day of my participation in the “Gold Digging Day New Plan · April More Text Challenge”. Click here for more details.

1. Method of obtaining parameters

When writing SQL statements in XML files, there are two ways to value values: #{} and ${}.

1-1, #{} pass the value

Using #{} to pass values is equivalent to JDBC

String sql=" SELECT id,user_name FROM EMP WHERE id=?"
Copy the code

In this way, the PreparedStatement in JDBC is precompiled and compiled into the data corresponding to the database based on different data types. Thus, SQL injection can be effectively prevented. Recommended use!!

Special usage of #{

Attributes that come with a lot of built-in parameters: usually not used. Understand javaType, jdbcType, mode, numericScale, resultMap, typeHandler. If you want to change the default NULL===>OTHER:#{id,javaType=NULL}

1-2, ${} pass value

Using #{} to pass values is equivalent to JDBC

String sql=" SELECT id,user_name FROM EMP WHERE id="+id
Copy the code

This will not precompile and will concatenate the incoming data directly into SQL. SQL injection risks exist. Not recommended.

1-2-1, special use of ${

1. It can be used temporarily during debugging. 2. To achieve some special functions: Data security must be guaranteed. For example: dynamic table, dynamic column. Dynamic SQL.

SQL > select * from empmapper. XML where #{} = ${}

Revised:

This makes it easier to debug complex statements without having to match parameter values one by one.

Select parameter pass

2-1. Single parameter transfer

Such as: SelectEmp (Integer id); Mybatis does not make any special requests.

As above: although we pass the ID, we can write the parameter as we like without special restrictions.

2-2. Multiple parameter transfer

2-2-1. Obtain parameters using arG or parAM

Emp SelectEmp(Integer id,String name); Mybatis will encapsulate the parameters passed in as a map: Each value corresponds to two map items: Id ===> {key:arg0,value:id value},{key:param1,value: ID value} Name ===> {key:arg1,value:id value},{key:param2,value: ID value}

Obtain parameters using arG as follows

Get parameters using param

Access: id = = = = = > # # {arg0} or {param1} name = = = = = > # # {arg1} or {param2} \

Summary: Start at 0 with arG and start at 1 with parAM

2-2-2. Set parameters using the @param annotation

Set the alias of the parameter: @param (“”) : SelectEmp(@param (“id”) Integer ID, @param (“name”) String name); \

When using the @ Param: id = = = = = > # # {id} or {param1} name = = = = = > # # {name} or {param2}

Get parameters by using an alias

You can also use Param to retrieve parameters if you use @param

In the actual project, it is recommended to use @param to set the alias of the parameter, so that when the parameter is obtained, it will be more clear and convenient for later maintenance.

Note that if you set the alias through @param, you can’t use arG to get the parameters.

2-3. JavaBean parameters

Use a javaBean to pass parameters

Single parameter: Emp SelectEmp(Emp Emp); Access: can directly use the property name emp id = = = = = > # {id} emp. The name = = = = = > # {name} \

As shown above, by passing a javaBean, only the property name can be entered to get parameter values.

2-3-2. Multiple parameters

Multiple parameters: Emp SelectEmp(Integer num,Emp Emp); \

Num ===> #{param1} or @param \

To obtain the parameters of a javaBean object, you can obtain emP ===> in the following way: Emp. Id = = = > # {param2. Id} or @ Param (” emp “) emp emp = = = = > # {emp. Id} emp. The name = = = > # {param2. Name} or @ Param emp emp (” emp “) ====>#{emp.name}

If @param is not set, only Param can be used to obtain parameters. JavaBean object property name after Param

If @param is set, you can get it from a different alias

Even if @param is set, you can still use Param to get the value of the parameter.

2-4. Collection or array parameters

2-4-1. Set pass parameters

Emp SelectEmp(List< String> name);

MyBatis will automatically encapsulate map: {key:”list”:value:name} without @param (“”)

name.get(0) =====> #{list[0]}

name.get(0) =====> #{agr0[0]}

There is @param (“names”) to get

names.get(0) =====> #{names[0]}

names.get(0) =====> #{param1[0]}

2-4-2, array passing parameters

MyBatis will automatically encapsulate map: {key:”array”:value:name}

No @ Param (” “) for the name. The get (0) = = = = = > # {array [0]} name. Get (0) = = = = = > # {agr0 [0]}

@ Param (” name “) to obtain: the name, the get (0) = = = = = > # {name [0]} name. Get (0) = = = = = > # {param1 [0]}

2-5. Parameter transfer in MAP mode

As with javaBean parameter passing, in general: If you’re asking for parameters that correspond to poJO, if you’re asking for parameters that don’t correspond to POJO, if you’re asking for parameters that don’t correspond to POJO, if you’re asking for parameters that correspond to POJO, if you’re asking for parameters that correspond to POJO, if you’re asking for parameters that correspond to POJO, if you’re asking for parameters that correspond to POJO, Use TO, DTO (that is, create a corresponding javaBean for these parameters, so that parameter passing is more standard, more reusable)

Third, the processing set returns the result

Return resultType: if you return one row of data, you can receive it using pojo, or if you return more than one row of data, you can use List< pojo> or List< map> and then specify the generic type in the List

In empmapper. XML, the resultType is Emp, although the data type is List< Emp>

If it is an underlying data type or a wrapper data type, you can specify the alias directly

4. Customize result sets

You can customize a result set using a ResultMap that maps database fields to poJO field attributes one by one, so that you can easily point the snake names of database fields to the hump names of attributes in the POJO, such as create_time–>createTime.

4-1. Why use custom result sets

Mysql > select * from poJO where id = create_time; mysql > select * from POJO where id = createTime; mysql > select * from POJO where id = createTime;

This is usually used on small systems, but if the mapper. XML file contains a large amount of SQL and create_time SQL is used, it is necessary to set each one. This not only increases the workflow, but also has a large impact on maintenance.

Therefore, we can use resultMap for mapping processing

4-2. Precautions for customizing result sets

Declare resultMap Only one resultType or resultMap can be used. Id uniquely identifies poJO objects that need to be mapped to resultMap on

When set to true, custom mapping can be performed even if the annotation’s field and attribute names are always mapped

Extends If multiple ResultMaps have duplicate mappings, you can declare a parent resultMap. To extract a common mapping, you can reduce the mapping redundancy of child ResultMaps

As you can see in the figure above, you comment out the returned ID map and set it not to be mapped automatically, but you use extends and inherit common_map so you own the mapping underneath it.

In addition, if database fields and POJO attributes can match, then mapper.xml mapping can be omitted, but it is not recommended, this will affect the readability of the following code.

Note that although we used the same mapping for primary key ids above, we should actually set the primary key as id (performance for underlying storage).

<id column="id" property="id"/>
Copy the code

Select * from ‘select’

attribute	describe
id	A unique identifier in the namespace that can be used to refer to this statement.
parameterType	The class-fully qualified name or alias that will be passed for the argument to this statement. This property is optional because MyBatis can infer the parameters of a specific incoming statement through the TypeHandler, which defaults to unset.
parameterMap	Deprecated property used to reference an external parameterMap. Use inline parameter mapping and the parameterType attribute.
resultType	The fully qualified name or alias of the class that is expected to return a result from this statement. Note that if a collection is returned, it should be set to the type contained in the collection, not the type of the collection itself. Only one resultType and resultMap can be used simultaneously.
resultMap	Named reference to an external resultMap. Result mapping is the most powerful feature of MyBatis. If you understand it well, many complex mapping problems can be solved easily. Only one resultType and resultMap can be used simultaneously.
flushCache	Setting this to true causes the local and secondary caches to be cleared whenever a statement is called. Default: false.
useCache	Setting it to true will cause the results of this statement to be cached by the second level cache. Default: true for select elements.
timeout	This setting is the number of seconds the driver waits for the database to return the result of the request before throwing an exception. The default is unset (database driven).
fetchSize	This is a recommended value for drivers to try to make the number of rows returned per batch equal to this value. The default value is unset (dependent driver).
statementType	The value can be STATEMENT, PREPARED or CALLABLE. This will make MyBatis use Statement, PreparedStatement, or CallableStatement, respectively. The default value is PREPARED.
resultSetType	FORWARD_ONLY, SCROLL_SENSITIVE, SCROLL_INSENSITIVE, or one of DEFAULT (equivalent to unset), which defaults to unset (database driver dependent).
databaseId	If databaseIdProvider is configured, MyBatis will load all statements that do not have a databaseId or match the current databaseId. If both tagged and untagged statements are present, untagged statements are ignored.
resultOrdered	This setting applies only to nested result SELECT statements: if true, nested result sets or groups are assumed to be included, and no references to previous result sets are generated when a primary result row is returned. This keeps memory from running out when retrieving nested result sets. Default value:false.
resultSets	This setting only works with multiple result sets. It lists the result sets returned after statement execution and gives each result set a name, separated by commas.

ParameterType, resultType, resultMap, flushCache, useCache, statementType (stored procedure)