Mybatis FAQ and mybatis FAQ

Mybatis FAQ and mybatis FAQ
1. Mybatis basics: # {...} and $ {...}
MyBatis will #{...} It is interpreted as a parameter mark of JDBC prepared statement. And $ {...} Interpreted as string replacement. It is useful to understand the differences between the two, because parameter markers cannot be used in some SQL statements ).


For example, we cannot use parameter tags in the position of table name.
Suppose there is the following code:


01. Map <String, Object> parms = new HashMap <String, Object> ();
02. parms. put ("table", "foo"); // table name
03. parms. put ("criteria", 37); // query filtering Conditions
04. List <Object> rows = mapper. generalSelect (parms );


01. <select id = "generalSelect" parameterType = "map">
02. select * from $ {table} where col1 =#{ criteria}
03. </select>


The SQL statement (prepared statement) generated by MyBatis is as follows:
01. select * from foo where col1 =?


Note: use $ {...} (String replacement) there may be risks of SQL injection attacks. In addition, string replacement may often cause problems when processing complex types, such as date types. Due to these factors, we recommend that you use # {…} whenever possible #{...} This method.
How to Write a LIKE statement?




2. There are two methods to use LIKE. (Recommended) the first method is to add SQL wildcards in Java code.
Example 1:
01. String wildcardName = "% Smi % ";
02. List <Name> names = mapper. selectLike (wildcardName );


01. <select id = "selectLike">
02. select * from foo where bar like # {value}
03. </select>


The second method is to splice wildcards in SQL statements. This method is relatively less secure because it may be vulnerable to SQL injection attacks.
Example 2:
01. String wildcardName = "Smi ";
02. List <Name> names = mapper. selectLike (wildcardName );


01. <select id = "selectLike">
02. select * from foo where bar like '%' | '$ {value}' | '%'
03. </select>


Important: Pay attention to the use of $ and # in the two methods!




3. How to Perform Batch insert?


First, create a simple insert statement:
01. <insert id = "insertName">
02. insert into names (name) values (# {value })
03. </insert>


Then execute batch insert in Java code as follows:
01. List <String> names = new ArrayList <String> ();
02. names. add ("Fred ");
03. names. add ("Barney ");
04. names. add ("Betty ");
05. names. add ("Wilma ");
06.
07. // note ExecutorType. BATCH
08. SqlSession sqlSession = sqlSessionFactory. openSession (ExecutorType. BATCH );
09. try {
10. NameMapper mapper = sqlSession. getMapper (NameMapper. class );
11. for (String name: names ){
12. mapper. insertName (name );
13 .}
14. sqlSession. commit ();
15.} finally {
16. sqlSession. close ();
17 .}




4. How do I obtain the automatically generated (primary) key value?


The insert method always returns an int value-this value represents the number of inserted rows. The automatically generated key value can be set to the input parameter object after the insert method is executed.
Example:
01. <insert id = "insertName" useGeneratedKeys = "true" keyProperty = "id">
02. insert into names (name) values (# {name })
03. </insert>


01. Name name = new Name ();
02. name. setName ("Fred ");
03.
04. int rows = mapper. insertName (name );
05. // After completion, the id has been set to the object
06. System. out. println ("rows inserted =" + rows );
07. System. out. println ("generated key value =" + name. getId ());




5. How do I pass multiple parameters in mapper?
The reflection mechanism of Java does not allow the framework to obtain the parameter name (the method signature only contains the parameter type, which can be called optimization or design, but the name is meaningless ), therefore, MyBatis is named param1, param2… by default ......
If you want to specify a name for them, you can use the @ param annotation:
01. import org. apache. ibatis. annotations. Param;
02. public interface UserMapper {
03. User selectUser (@ Param ("username") String username,
04. @ Param ("hashedPassword") String hashedPassword );
05 .}


Then, it can be used in xml as follows (it is recommended to encapsulate it as a Map and pass it to Mapper as a single parameter ):
01. <select id = "selectUser" resultType = "User">
02. select id, username, hashedPassword
03. from some_table
04. where username = # {username}
05. and hashedPassword = # {hashedPassword}
06. </select>

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.