QA From shallow to Deep Persistence Layer Framework (4) – SQL Mapping XML

“This is the 22nd day of my participation in the First Challenge 2022. For details: First Challenge 2022”

A, select

Query statements are one of the most commonly used elements in MyBatis – just storing data in the database is not valuable, but it can be retrieved, and most applications are more frequently query than modify. One of the basic principles of MyBatis is that it is common to perform multiple queries between each insert, update, or delete operation. Therefore, MyBatis has made quite a few improvements in query and result mapping. The SELECT element of a simple query is very simple. The SELECT element allows you to configure a number of properties to configure the behavior details of each statement.

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.

Parameter passing

One or more parameters may be passed in a query statement according to different query conditions. How to obtain these parameters?

There are four cases of parameter passing

• A single parameter: Accepts values of primitive types, object types, and collection types. If there is only one parameter, MyBatis can use this parameter directly without any processing.
• Multiple parameters: Any multiple parameters will be repackaged by MyBatis into a Map. The Map key is param1, param2, 0, and 1. The Map value is the parameter value, which can be obtained by key
• Named parametersMyBatis will encapsulate the parameters into the Map using the specified name using the @param annotation
• POJO: If the parameter belongs to a service POJO, the POJO is directly passed
• Map: You can also encapsulate multiple parameters as a Map and pass the Map directly

Passing multiple parameters

Add a query method to the EmployeeDao interface

Employee getEmpByIdAndEmpName(Integer id, String empName);
Copy the code

Add SQL mappings to employee.xml

<select id="getEmpByIdAndEmpName" resultType="com.citi.entity.Employee">
    select * from t_employee where id = #{id} and empname = #{empName}
</select>
Copy the code

Execute test method

Modifying SQL statements

<select id="getEmpByIdAndEmpName" resultType="com.citi.entity.Employee">
    select * from t_employee where id = #{param1} and empname = #{param2}
</select>
Copy the code

Run the test again

When only one argument is passed, as in the getEmpById method, the name of the passed argument can be arbitrary

<select id="getEmpById" resultType="com.citi.entity.Employee">
    select * from t_employee where id = #{mybatis}
</select>
Copy the code

Perform the test

I’ll just pass in an argument, write whatever name you want

#{parameter name} is invalid. You can use 0, 1, param1, param2 instead, because MyBatis automatically encapsulates multiple parameters in the Map. The Map used for encapsulation can be the index of the parameter

You can use the @param annotation to specify keys that are encapsulated in a Map. Modify the method definition to specify the name of the key encapsulated in MyBatis

Employee getEmpByIdAndEmpName(@Param("id") Integer id, @Param("name") String empName);
Copy the code

Use #{parameter name} values in SQL mapping files to perform tests

Introduced to the Map

Add a method to the EmoloyeeDao interface

Employee getEmployeeByIdAndName(Map<String,Object> paramMap);
Copy the code

Add SQL mapping statements

<select id="getEmployeeByIdAndName" resultType="com.citi.entity.Employee">
    select * from t_employee where id = #{id} and empname = #{name}
</select>
Copy the code

Add test methods

@Test
public void getEmployeeByIdAndName(a){

    EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("id".1);
    paramMap.put("name"."stark");
    Employee employee = employeeDao.getEmployeeByIdAndName(paramMap);

    System.out.println(employee);
}
Copy the code

Perform the test

When a parameter is passed into a Map, you can use #{Key} to get the Value of the parameter.

When you have all of these cases in a method, that is, multiple arguments

getByIdAndNameAndEmployee(@Param("id) id, String empName, Employee employee);
Copy the code

Id is set to #{id}. EmpName is set to #{param2}. Email is set to #{param3.email

Processing parameters

SQL statement parameter positions in SQL mapping files support properties such as javaType, jdbcType, Mode, numericScale, resultMap, typeHandler, and jdbcTypeName

jdbcType

The argument can also specify a special data type

#{property,javaType=int,jdbcType=NUMERIC}
#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}
Copy the code

• JavaType can usually be determined from the parameter object
• If NULL is passed as a value, the jdbcType needs to be set for all columns that may be empty
• For numeric types, you can set the number of digits reserved after the decimal point

<select id="getEmpByIdAndEmpName" resultType="com.citi.entity.Employee">
    select * from t_employee where id = #{id,jdbcType=INTEGER} and empname = #{name}
</select>
Copy the code

# {} and ${}

To get the value of a parameter, use #{} or ${}.

• #{key} : precompile to SQL, safer
• ${key} : concatenate SQL, there will be SQL injection vulnerability

Modify getEmployeeByIdAndEmpName method corresponding SQL statements, use ${}

<select id="getEmpByIdAndEmpName" resultType="com.citi.entity.Employee">
    select * from t_employee where id = ${id} and empname = #{name}
</select>
Copy the code

Id =1 is directly concatenated to SQL

Only parameter positions in SQL statements are supported for precompilation. ${} can be used to pass table names dynamically

Modify the getEmployeeByIdAndName test method, and add the table name information to the paramMap

@Test
public void getEmployeeByIdAndName(a){

    EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("id".1);
    paramMap.put("name"."stark");
    paramMap.put("tableName"."t_employee");
    Employee employee = employeeDao.getEmployeeByIdAndName(paramMap);

    System.out.println(employee);
}
Copy the code

Modify the mapping SQL for getEmployeeByIdAndName with ${tableName} to get the tableName

<select id="getEmployeeByIdAndName" resultType="com.citi.entity.Employee">
    select * from ${tableName} where id = #{id} and empname = #{name}
</select>
Copy the code

Perform the test

If you use #{} to pass the tableName, change the mapping SQL for getEmployeeByIdAndName, use #{tableName} to get the tableName, and run the test again

Error: Only parameter positions in SQL statement are supported for precompilation

Query returns List

The getAllEmployees method is added to the EmployeeDao interface to query all employees

List<Employee> getAllEmployees(a);
Copy the code

Employee.xml adds mapping SQL

<! ResultType specifies the type of the element in the collection.
<select id="getAllEmployees" resultType="com.citi.entity.Employee">
    select * from t_employee
</select>
Copy the code

Add test methods

@Test
public void getAllEmployees(a){
    EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
    List<Employee> employeeList = employeeDao.getAllEmployees();
    for(Employee employee : employeeList) { System.out.println(employee); }}Copy the code

Execute the test method

The query results are encapsulated into a Map

The query returns a single record with the following name as Key and the Value as Value

The new method

Map<String, Object> getEmployeeByIdWithMap(Integer id);
Copy the code

Add an SQL mapping statement. Because the data returned is of Map type, the resultType is Map

<select id="getEmployeeByIdWithMap" resultType="map">
    select * from t_employee where id = #{id}
</select>
Copy the code

Add test methods

@Test
public void getEmployeeByIdWithMap(a){
    EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
    Map<String, Object> employeeMap = employeeDao.getEmployeeByIdWithMap(1);
    System.out.println(employeeMap);
}
Copy the code

Perform the testBy default, a Map encapsulates the column name as the Key and the Value as the Value

If multiple records are returned, they are encapsulated into a Map

Define methods

Map<Integer, Employee> getAllEmployeesWithMap(a);
Copy the code

Add SQL mapping statements to employee.xml

<select id="getAllEmployeesWithMap" resultType="map">
    select * from t_employee
</select>
Copy the code

Add test methods

@Test
public void getAllEmployeesWithMap(a){
    EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
    Map<Integer, Employee> employeeMap = employeeDao.getAllEmployeesWithMap();
    System.out.println(employeeMap);
}
Copy the code

Perform the test

will

Add a primary key definition using @mapkey to specify the primary key of a Map

@MapKey("id")
Map<Integer, Employee> getAllEmployeesWithMap(a);
Copy the code

Run the test again

Print out the attributes of the element with key 1 in the test method and add code to the getAllEmployeesWithMap test method

@Test
public void getAllEmployeesWithMap(a){
    EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
    Map<Integer, Employee> employeeMap = employeeDao.getAllEmployeesWithMap();
    System.out.println(employeeMap);
    Employee employee = employeeMap.get(1);
    System.out.println(employee.getEmpName());
}
Copy the code

A cast exception has occurredThis error occurs because the resultType defined in the SQL mapping statement is Map, so the resultType type in the SQL mapping statement is changed to the type of the element in the collection

<select id="getAllEmployeesWithMap" resultType="com.citi.entity.Employee">
    select * from t_employee
</select>
Copy the code

The test again

Query multiple data returns a Map, resultType is the type of the element stored in the set Value