Summary of MyBatis common interview questions

1. What is MyBatis?

1. MyBatis is a semi ORM (object relational mapping) framework. It encapsulates JDBC internally. During development, you only need to pay attention to the SQL statement itself, and you don't need to spend energy dealing with the complicated processes such as loading driver, creating connection and creating statement. Programmers directly write the original SQL, which can strictly control the SQL execution performance and has high flexibility.
2. MyBatis can use XML or annotations to configure and map native information, map POJO s to records in the database, and avoid almost all JDBC code, manually setting parameters and obtaining result sets.
3. Various statements to be executed are configured through xml files or annotations, and the final executed sql statements are generated through the mapping between java objects and sql dynamic parameters in the statement. Finally, the MySQL framework executes sql and maps the results into java objects and returns them( From executing sql to returning result).

2. Advantages of MyBatis:

1. Programming based on SQL statement is quite flexible and will not have any impact on the existing design of application program or database. SQL is written in XML to decouple SQL and program code for unified management; Provides XML tags, supports writing dynamic SQL statements, and can be reused.
2. Compared with JDBC, it reduces the amount of code by more than 50%, eliminates a large number of redundant codes in JDBC, and does not require manual switch connection;
3. It is well compatible with various databases (since MyBatis uses JDBC to connect to the database, MyBatis supports all databases supported by JDBC).
4. Good integration with Spring;
5. Provide mapping labels to support the mapping between objects and ORM fields in the database; Provide object relationship mapping labels to support object relationship component maintenance.

3. Disadvantages of MyBatis framework:

1. The workload of writing SQL statements is large, especially when there are many fields and associated tables, which has certain requirements for developers to write SQL statements.
2. SQL statements depend on the database, resulting in poor database portability, and the database cannot be replaced at will.

4. MyBatis framework is applicable to:

1. MyBatis focuses on SQL itself and is a sufficiently flexible DAO layer solution.
2. MyBatis will be a good choice for projects with high performance requirements or more demand changes, such as Internet projects.

5. What are the differences between MyBatis and Hibernate?

1. Unlike hibernate, mybatis is not entirely an ORM framework, because mybatis requires programmers to write Sql statements themselves.
2. Mybatis directly writes the original sql, which can strictly control the sql execution performance and has high flexibility. It is very suitable for software development with low requirements for relational data model, because the requirements of this kind of software change frequently, but once the requirements change, it requires rapid output of results. However, the premise of flexibility is that mybatis cannot be database independent. If you need to implement software that supports multiple databases, you need to customize multiple sets of sql mapping files, which is a heavy workload.
3. Hibernate has strong object / relational mapping ability and good database independence. For software with high requirements for relational model, if developed with hibernate, it can save a lot of code and improve efficiency.

6. What is the difference between #{} and ${}?

#{} is precompiled and ${} is string substitution.
When MyBatis processes #{}, it will replace #{} in sql with? Number, call the set method of PreparedStatement to assign value;
When MyBatis handles ${}, it replaces ${} with the value of the variable.
Using #{} can effectively prevent SQL injection and improve system security.

7. What happens when the attribute name in the entity class is different from the field name in the table?

• The first method: define the alias of the field name in the sql statement of the query to make the alias of the field name consistent with the attribute name of the entity class.

<select id="selectorder" parametertype="int" resultetype="me.gacl.domain.order">       
    select order_id id, order_no orderno ,order_price price form orders where order_id=#{id};</select>

• Type 2: map the one-to-one correspondence between field names and entity class attribute names through < resultmap >.

<select id="getOrder" parameterType="int" resultMap="orderresultmap">
    select order_no as orderno from orders where order_id=#{id}
</select>
<resultMap type="me.gacl.domain.order" id="orderresultmap">    
    <!–use id Property to map primary key fields–>    
    <id property="id" column="order_id">
    <!–use result Property to map non primary key fields, property Is the attribute name of the entity class, column Attributes in the data table–>    
   	<result property = "orderno" column ="order_no"/>    
    <result property="price" column="order_price" />
</reslutMap>

8. How to write a fuzzy query like statement?

• Type 1: add sql wildcards in Java code.

string wildcardname = "%smi%";
list<name> names = mapper.selectlike(wildcardname);

Mapping file

<select id="selectlike">select * from foo where bar like #{value}</select>

• Type 2: splicing wildcards in sql statements will cause sql injection

string wildcardname = "smi";
list<name> names = mapper.selectlike(wildcardname);

Mapping file

<select id="selectlike">select * from foo where bar like "%"#{value}"%"</select>

9. Usually, an Xml Mapping file will write a Dao interface corresponding to it. What is the working principle of this Dao interface? Can the method be overloaded when the parameters of the method in Dao interface are different?

Dao interface is Mapper interface. The fully qualified name of the interface is the value of the namespace in the mapping file; The method name of the interface is the id value of Mapper's Statement in the mapping file; The parameters in the interface method are the parameters passed to sql.
Mapper interface has no implementation class. When calling interface methods, the interface fully qualified name + method name splicing string is used as the key value to uniquely locate a MapperStatement. In Mybatis, each < Select >, < Insert >, < update >, < delete > tag will be resolved into a MapperStatement object.
For example: com.mybatis3.mappers.StudentDao.findStudentById, MapperStatement with id findStudentById under namespace com.mybatis3.mappers.StudentDao can be found uniquely.
The method in Mapper interface cannot be overloaded because it uses the saving and searching strategy of fully qualified name + method name. The working principle of Mapper interface is JDK dynamic proxy. Mybatis runtime will use JDK dynamic proxy to generate proxy object proxy for Mapper interface. The proxy object will intercept interface methods, execute the sql represented by MapperStatement, and then return the sql execution results.

10. How does MyBatis page? What is the principle of paging plug-in?

MyBatis uses the RowBounds object for paging, which is a memory page for the ResultSet result set, not a physical page. You can directly write parameters with physical paging in sql to complete the physical paging function, or you can use the paging plug-in to complete the physical paging.
The basic principle of the paging plug-in is to use the plug-in interface provided by Mybatis to implement a custom plug-in, intercept the sql to be executed in the plug-in interception method, then rewrite the sql, and add the corresponding physical paging statements and physical paging parameters according to the dialect of dialect.

11. How does MyBatis encapsulate the sql execution result as a target object and return it? What are the mapping forms?

• The first is to use the < resultmap > tag to define the mapping relationship between database column names and object attribute names one by one.
• The second is to use the alias function of sql column to write the alias of column as object attribute name.

After having the mapping relationship between column name and attribute name, Mybatis creates objects through reflection, and uses the attributes reflected to the object to assign values one by one and return them. Those attributes that cannot find the mapping relationship cannot be assigned.

12. How do I perform a batch insert?

First, create a simple insert statement:

<insert id="insertname">insert into names (name) values (#{value})</insert>

Then perform a batch insert in the java code as follows:

list < string > names = new arraylist();
names.add("fred");
names.add("barney");
names.add("betty");
names.add("wilma");
// Note here executortype.batchsqlsession 
SqlSession sqlsession = sqlsessionfactory.opensession(executortype.batch);
try {    
    namemapper mapper = sqlsession.getmapper(namemapper.class);    
    for (string name: names) {        
        mapper.insertname(name);    
    }    
    sqlsession.commit();
} catch (Exception e) {    
    e.printStackTrace();    
    sqlSession.rollback();    
    throw e;
}finally {    
    sqlsession.close();
}

13. How to get automatically generated (Master) key values?

The insert method always returns an int value, which represents the number of rows inserted.

If the self growth strategy is adopted, the automatically generated key value can be set to the passed in parameter object after the insert method is executed.

Example:

<insert id="insertname" usegeneratedkeys="true" keyproperty="id">     
    insert into names (name) values (#{name})
</insert>

name name = new name();
name.setname("fred");
int rows = mapper.insertname(name);// After completion, the id has been set in the object
system.out.println("rows inserted = " + rows);
system.out.println("generated key value = " + name.getid());

14. How to pass multiple parameters in mapper?

1. The first is the function of DAO layer

public UserselectUser(String name,String area);
// Corresponding xml,#{0} indicates that the first parameter in dao layer is received,
// #{1} Represents the second parameter in dao layer, and more parameters can be added later.

<select id="selectUser"resultMap="BaseResultMap">      
    select *  fromuser_user_t   whereuser_name = #{0} anduser_area=#{1}
</select>

2. The second method: use @ param annotation:

public interface usermapper { 
    user selectuser(
        @param("username") string username,
        @param("hashedpassword") string hashedpassword
    );
}

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):

<select id="selectuser" resulttype="user">         
    select id, username, hashedpassword    from some_table  where username = #{username}         and hashedpassword = #{hashedpassword}
</select>

3. The third method is to encapsulate multiple parameters into a map

try {    
    //The namespace of the mapping file and the ID of the SQL fragment can call the SQL in the corresponding mapping file    
    //Since we have more than two parameters and only one Object parameter is collected in the method, we use the Map collection to load our parameters    
    Map < String, Object > map = new HashMap();    
    map.put("start", start);    
    map.put("end", end);    
    return sqlSession.selectList("StudentID.pagination", map);
} catch (Exception e) {    
    e.printStackTrace();    
    sqlSession.rollback();    
    throw e;
} finally {    
    MybatisUtil.closeSqlSession();
}

15. What is the use of MyBatis dynamic sql? How does it work? What are the dynamic sql?

MyBatis dynamic sql can write dynamic sql in the form of tags in the Xml Mapping file. The execution principle is to complete logical judgment and dynamically splice sql according to the value of the expression.

MyBatis provides nine dynamic sql Tags: trim|where|set|foreach|if|choose|when|otherwise|bind.

16. In addition to the common select, insert, updae and delete tags in the xml Mapping file, what other tags are there?

Answer: < resultmap >, < parametermap >, < sql >, < include >, < selectkey >, plus 9 labels of dynamic sql. Among them, < sql > is the label of sql fragment. sql fragment is introduced through the < include > label, and < selectkey > is the policy label generated for the primary key that does not support self increment.

17. In the Xml Mapping file of Mybatis, can the IDs of different Xml mapping files be repeated?

For different Xml mapping files, if namespace is configured, the id can be repeated; If the namespace is not configured, the id cannot be repeated;

The reason is that namespace+id is used as the key of map < string, mapperstatement >. If there is no namespace, there is only id, and the repeated id will cause the data to overlap each other. With a namespace, the natural id can be repeated. If the namespace is different, the namespace+id will naturally be different.

18. Why is MyBatis a semi-automatic ORM mapping tool? What is the difference between it and automatic?

Hibernate is a fully automatic ORM mapping tool. When using hibernate to query associated objects or associated collection objects, it can be obtained directly according to the object relationship model, so it is fully automatic. When MyBatis queries Association objects or association collection objects, it needs to write sql manually. Therefore, it is called a semi-automatic ORM mapping tool.

19. One to one and one to many association queries?

<mapper namespace="com.lcb.mapping.userMapper">      
    <!--association  One to one association query -->      
	<select id="getClass" parameterType="int" resultMap="ClassesResultMap">          
        select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}  
    </select>  
    
    <resultMap type="com.lcb.user.Classes" id="ClassesResultMap">          
        <!-- The field name of the entity class is mapped to the field name of the data table -->          
        <id property="id" column="c_id"/>          
        <result property="name" column="c_name"/>          
        <association property="teacher" javaType="com.lcb.user.Teacher">              
            <id property="id" column="t_id"/>              
            <result property="name" column="t_name"/>          
        </association>      
    </resultMap>  

    <!--collection  One to many association query -->      
    <select id="getClass2" parameterType="int" resultMap="ClassesResultMap2">          
        select * from class c,teacher t,student s where c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=#{id}    
    </select>  
    
    <resultMap type="com.lcb.user.Classes" id="ClassesResultMap2">          
        <id property="id" column="c_id"/>          
        <result property="name" column="c_name"/>          
        <association property="teacher" javaType="com.lcb.user.Teacher">              
            <id property="id" column="t_id"/>              
            <result property="name" column="t_name"/>          
        </association>  
        <collection property="student" ofType="com.lcb.user.Student">              
            <id property="id" column="s_id"/>              
            <result property="name" column="s_name"/>          
        </collection>      
    </resultMap>  
</mapper> 

20. How many ways can MyBatis implement one-to-one? How did you do it?

There are joint query and nested query. Joint query is the joint query of several tables. It can be completed only once by configuring the association node in resultMap and configuring one-to-one classes;

Nested query is to query a table first and then query data in another table according to the foreign key id of the result in this table. It is also configured through association, but the query of another table is configured through the select attribute.

21. There are several ways for MyBatis to realize one to many. How do you operate it?

There are federated queries and nested queries. Joint query is a joint query of several tables. It can be completed only once by configuring one to many classes in the collection node in resultMap; Nested query is to query a table first and then query data in another table according to the foreign key id of the result in this table. It is also through configuring collection, but the query of another table is configured through the select node.

22. Does MyBatis support delayed loading? If yes, what is its implementation principle?

A: mybatis only supports delayed loading of association objects and Collection Association collection objects. Association refers to one-to-one and Collection refers to one to many queries. In the mybatis configuration file, you can configure whether to enable deferred loading. Lazyloading enabled = true|false.

Its principle is to create object objects of proxy using CGLIB. When calling the target method, it will enter the interceptor method, such as calling a.getB().getName(). The interceptor invoke() method finds a.getB () is null value, then it sends the saved query query sql of the B object separately, and then queries the B and then calls a.setB(b), so the object attribute of the B is worth. Then complete the call to the a.getB().getName() method. This is the basic principle of delayed loading.

Of course, not only Mybatis, but almost all, including Hibernate, support delayed loading on the same principle.

23. Level 1 and level 2 cache of MyBatis:

1) L1 Cache: the HashMap local Cache based on the PerpetualCache. Its storage scope is Session. When the Session is flushed or close d, all caches in the Session will be cleared. The L1 Cache is opened by default.
2) The second level cache has the same mechanism as the first level cache. By default, it is stored in perpetual cache and HashMap. The difference is that its storage scope is Mapper(Namespace) and the storage source can be customized, such as Ehcache. The L2 cache is not enabled by default. To enable L2 cache, the Serializable serialization interface (which can be used to save the state of the object) needs to be implemented by using L2 cache attribute class, and < cache / > can be configured in its mapping file;
3) For the cache data update mechanism, after the C/U/D operation of a scope (L1 cache Session / L2 cache Namespaces), by default, the caches in all select ions under the scope will be clear ed.

24. What is the interface binding of MyBatis? What are the implementation methods?

Interface binding is to define any interface in MyBatis, and then bind the methods in the interface with SQL statements. We can call the interface methods directly, so that we can have more flexible choices and settings than the methods provided by SqlSession.

There are two implementation methods for interface binding. One is binding through annotation, that is, add @ Select, @ Update and other annotations on the interface methods, which contain SQL statements for binding; The other is to bind by writing SQL in xml. In this case, the namespace in the xml Mapping file must be the full pathname of the interface. When the SQL statement is relatively simple, it is bound with annotations. When the SQL statement is relatively complex, it is bound with xml. Generally, it is more bound with xml.

25. What are the requirements when calling the mapper interface of MyBatis?

1. The Mapper interface method name is the same as the id of each sql defined in mapper.xml;
2. The input parameter type of Mapper interface method is the same as the parameterType of each sql defined in mapper.xml;
3. The output parameter type of Mapper interface method is the same as the resultType of each sql defined in mapper.xml;
4. The namespace in Mapper.xml file is the classpath of mapper interface.

26. What are the ways to write Mapper?

The first: interface implementation class inherits SqlSessionDaoSupport: using this method, you need to write mapper interface, mapper interface implementation class and mapper.xml file.

1. Configure the location of mapper.xml in sqlMapConfig.xml

<mappers>
    <mapper resource="mapper.xml Address of the file" />    
    <mapper resource="mapper.xml Address of the file" />
</mappers>

1. Define mapper interface 3. Implement class integration. In SqlSessionDaoSupportmapper method, you can add, delete, modify and query data in this.getSqlSession(). 4. spring configuration

<bean id=" " class="mapper Implementation of interface">    
    <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>

Second: use org.mybatis.spring.mapper.MapperFactoryBean:

1. Configure the location of mapper.xml in sqlMapConfig.xml. If the names of mapper.xml and map interface are the same and in the same directory, you can not configure it here

<mappers>    
    <mapper resource="mapper.xml Address of the file" />    
    <mapper resource="mapper.xml Address of the file" />
</mappers>

2. Define mapper interface:

1. The namespace in mapper.xml is the address of the mapper interface. 2. The method name in the mapper interface is consistent with the id of the statement defined in mapper.xml. 3. It is defined in Spring

<bean id="" class="org.mybatis.spring.mapper.MapperFactoryBean">    
	<property name="mapperInterface"   value="mapper Interface address" />     
	<property name="sqlSessionFactory" ref="sqlSessionFactory" /> 
</bean>

Third: use mapper scanner:

1. mapper.xml file preparation:

The namespace in mapper.xml is the address of mapper interface; The method name in the mapper interface is consistent with the id of the statement defined in mapper.xml; If the names of mapper.xml and mapper interface are consistent, you do not need to configure them in sqlMapConfig.xml.

2. Define mapper interface:

Note that the file name of mapper.xml is consistent with the interface name of mapper and placed in the same directory. 3. Configure mapper scanner:

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
	<property name="basePackage" value="mapper Interface package address"></property>    
	<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> 
</bean>

4. After using the scanner, get the mapper implementation object from the spring container.

27. Briefly describe the operation principle of MyBatis plug-in and how to write a plug-in.

A: MyBatis can only write plug-ins for ParameterHandler, ResultSetHandler, StatementHandler and Executor. MyBatis uses the dynamic agent of JDK to generate proxy objects for the interfaces to be intercepted to realize the interface method interception function. Whenever the methods of these four interface objects are executed, it will enter the interception method, Specifically, the invoke() method of InvocationHandler, of course, will only intercept those methods you specify to be intercepted.

Write a plug-in: implement the Interceptor interface of MyBatis and duplicate the intercept() method, then write comments for the plug-in and specify which methods of which interface to intercept. Remember, don't forget to configure the plug-in you write in the configuration file.