MyBatis supports the configuration of SQL statements to be run through XML or annotation. Finally, the framework maps Java objects and SQL statements to generate the final executed SQL. After execution, the results are mapped into Java objects and returned. Compared with Hibernate, Mybatis can write native SQL, that is, it can strictly control the SQL execution performance and has high flexibility. 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.

Let's talk about hibernate. It has strong object / relationship mapping ability and can be database independent. If you develop with hibernate, There is no need to write relational SQL (anyone who can't write SQL can operate the database), which can save a lot of code and improve efficiency. However, the disadvantage of Hibernate is that it has a high learning threshold and a higher threshold to master. Moreover, it requires strong experience and ability to design O/R mapping, how to balance performance and object model, and how to make good use of hibernate.

Finally, I think we should select the most suitable framework in combination with the company's business. Don't use technology for the sake of technology, otherwise we are all playing hooligans. For example, you are in a relatively small company with a small amount of data, and the technical stack of the company's developers is more inclined to Hibernate. It is recommended to use JPA and Hibernate, which do not need to write SQL manually, to improve development efficiency and version iteration speed. If you are an Internet company with a large number of users and strict requirements on the execution performance of relevant SQL, Mybatis is recommended. In short, according to the needs of users, in the limited resource environment, as long as the software architecture with good maintainability and scalability can be made, it is a good architecture, so the framework is the best only if it is suitable.

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

This is a basic and classic question, but it is basically a must in the interview

#{} is precompiled and ${} is string substitution.

When processing #{}, Mybatis 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.

Let's use an example to illustrate

Everyone knows Mybatis mapper There are two ways to pass parameters from parameterType to SQL statement in XML statement: #{} and ${}

We often use #{}. The general explanation is that this method can prevent SQL injection. In short, the SQL statement of #{} This method is precompiled, which translates the #{} intermediate parameters into strings. For example:

select * from student where student_name = #{name} 

After precompiling, it will be dynamically parsed into a parameter marker?

select * from student where student_name = #{name} 

When ${} is used for dynamic parsing, the parameter string will be passed in

​
select * from student where student_name = #{name} 

​

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

For example: one is to use resultMap in Mapper mapping file to define mapping rules

<!-- Custom advanced mapping -->
<!-- namespace Attribute: must be the full class name of the interface -->
<mapper namespace="com.tt.mybatis.mapper.EmployeeMapper">
	<!-- 
		id Property: must be the method name of the method in the interface
		resultType Property: must be the full class name of the return value of the method
	 -->
	<select id="getEmployeeById" resultMap="myMap">
		select * from employees where id = #{id}
	</select>
	
	<!-- Custom advanced mapping -->
    <resultMap type="com.tt.mybatis.entities.Employee" id="myMap">
    	<!-- Map primary key -->
    	<id column="id" property="id"/>
    	<!-- Map other columns -->
    	<result column="last_name" property="lastName"/>
    	<result column="email" property="email"/>
    	<result column="salary" property="salary"/>
    	<result column="dept_id" property="deptId"/>
    </resultMap>
</mapper>

There are also aliases when using sql statements

<configuration>
 	<settings>
		 <!-- Turn on the hump naming rule to map the underscores in the database to hump naming rules
		 	For example: last_name Can be mapped to lastName
		 -->
 		<setting name="mapUnderscoreToCamelCase" value="true"/>
 	</settings>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
	<!-- Register mapping file -->
	<mappers>
		<mapper resource="EmployeeMapper.xml" />
	</mappers>

How to write a fuzzy query like statement?    

Example: add sql wildcards in Java code.

string wildcardname = "%smi%";

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

<select id="selectlike">

select * from foo where bar like #{value}

</select>

Another way is to splice wildcards in sql statements, but it may cause sql injection

 string wildcardname = "smi";

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

<select id="selectlike">

select * from foo where bar like "%"#{value}"%"

</select>

How does the Mybatis Dao} interface work? Can the method be overloaded when the parameters of the method in Dao interface are different?

For example: Dao interface is Mapper interface, --- you can create an interface based on annotation and define abstract methods in the interface;

public interface UserMapper {

	    @Select("select * from users where id=#{id}")
        public User getUserById(int id);
	    
}

The fully qualified name of the interface is the value of the namespace in the mapping file:

<mapper namespace="com.mybatis.test3.orderMapper">
<select id="selectUser" parameterType="int" resultType="Order">
select * from users where id=#{id}
</select>
</mapper>

The full name of the interface is the value of the namespace in the mapping file:

<mapper namespace="com.mybatis.test3.orderMapper">
<select id="selectUser" parameterType="int" resultType="Order">
select * from users where id=#{id}
</select>
</mapper>

The interface method name (getUserById) is the id value (selectUser) in the MappedStatement in the mapping file, and the parameters in the interface method are the parameters passed to SQL (#{id} > > #{id}).

Mapper interface does not implement class. When calling the interface method, connect the interface name + method name with the string as the key value to uniquely locate the MappedStatement. For example: com. Mybatis. Test2. usermap. Only com namespaces can be found. Mybatis. Test2. UserMapper id = insertUser MappedStatement is as follows.

In Mybatis, each < Select >, < Insert >, < update >, and < delete > tag is resolved to a MappedStatement object.

Explanation of heavy load and working principle

The two methods in Dao interface cannot be overwritten because it is a saving and searching strategy of full name + method name.

The Dao interface works as a JDK dynamic proxy. When Mybatis runs, JDK dynamic proxy will be used to generate proxy objects for Dao interface.

How does Mybatis perform paging? What is the principle of paging plug-in?

Mybatis uses the RowBounds object for paging, which is a memory paging for the ResultSet result set, not a physical paging. 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. Let's take a look at how to page in mybatis

  private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler resultHandler, RowBounds rowBounds, ResultMapping parentMapping)

throws SQLException {
    DefaultResultContext resultContext = new DefaultResultContext();
    // Skip to the offset position and prepare for reading
    skipRows(rsw.getResultSet(), rowBounds);
    // Read limit data
    while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
      ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
      Object rowValue = getRowValue(rsw, discriminatedResultMap);
      storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
    }
  }
    private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
    if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
      if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) {
        // direct location
        rs.absolute(rowBounds.getOffset());
      }
    } else {
      // You can only scroll to the specified position one by one
      for (int i = 0; i < rowBounds.getOffset(); i++) {
        rs.next();
      }
    }
  }

Principle, please refer to this article How the PageHelper of MyBatis works

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

The first method is to use the < resultmap > tag to define the mapping between database column names and object attribute names one by one.

The second method is to use the alias function of the SQL column to write the alias of the column as the object property name.

Using the mapping between column names and attribute names, Mybatis creates objects through reflection and uses the attributes of the reflection object to assign values one by one and return them. If the mapping relationship cannot be found, the assignment cannot be completed.

How does Mybatis perform batch inserts?

• First, create a simple INSERT statement:

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

Then perform a batch insert operation in Java code:

list < string > names = new arraylist();
names.add("fred");
names.add("barney");
names.add("betty");
names.add("wilma");
 
// Note executortype batch
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();
}

How does Mybatis get automatically generated (Master) key values?

Mapper file insert statement settings

useGeneratedKeys="true" keyProperty="id"

How does Mybatis pass multiple parameters in mapper?

The first scheme is the function method of DAO layer

Public User selectUser(String name,String area); 

Corresponding mapper XML configuration file

<select id="selectUser" resultMap="BaseResultMap" parameterType="java.lang.String">  
    select  *  from user_user_t   where user_name = #{0} and user_area=#{1}  
</select>

Where, #{0} represents the first parameter received in dao layer, #{1} represents the second parameter in dao layer, and more parameters can be added later.

The second function method of Dao layer

Public User selectUser(@param("userName")Stringname,@param("userArea")String area);  

Corresponding mapper XML configuration file

<select id=" selectUser" resultMap="BaseResultMap">  
   select  *  from user_user_t   where user_name = #{userName，jdbcType=VARCHAR} and user_area=#{userArea,jdbcType=VARCHAR}  
</select> 

Personally, I think this method is better. It allows developers to see the dao layer method and know what parameters to pass. I personally recommend this scheme.

What is the use of Mybatis dynamic sql? How does it work? What are the dynamic sql?

The dynamic sql of Mybatis can be written in Xml Mapping Files in the form of tags,

The execution principle is to complete logical judgment according to the value of the expression and dynamically splice the function of sql.

There are nine kinds of dynamic sql, specifically: trim | where | set | foreach | if | choose| when | otherwise | bind.

Nine specific dynamic SQL examples:

if tag

<!-- Query students list，like full name -->  

<select id=" getStudentListLikeName " parameterType="StudentEntity" resultMap="studentResultMap">  

    SELECT * from STUDENT_TBL ST   

    <if test="studentName!=null and studentName!='' ">  

        WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')   

    </if>  

</select>  

where tag

<!-- Query students list，like full name,=Gender -->  
<select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">  
    SELECT * from STUDENT_TBL ST   
    <where>  
        <if test="studentName!=null and studentName!='' ">  
            ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')   
        </if>  
        <if test="studentSex!= null and studentSex!= '' ">  
            AND ST.STUDENT_SEX = #{studentSex}   
        </if>  
    </where>  
</select>  

set tag

<!-- Update student information -->  
<update id="updateStudent" parameterType="StudentEntity">  
    UPDATE STUDENT_TBL   
    <set>  
        <if test="studentName!=null and studentName!='' ">  
            STUDENT_TBL.STUDENT_NAME = #{studentName},   
        </if>  
        <if test="studentSex!=null and studentSex!='' ">  
            STUDENT_TBL.STUDENT_SEX = #{studentSex},   
        </if>  
        <if test="studentBirthday!=null ">  
            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},   
        </if>  
        <if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">  
            STUDENT_TBL.CLASS_ID = #{classEntity.classID}   
        </if>  
    </set>  
    WHERE STUDENT_TBL.STUDENT_ID = #{studentID};   
</update>  

trim tag

 <!-- Query students list，like full name,=Gender -->  
<select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">  
    SELECT * from STUDENT_TBL ST   
    <trim prefix="WHERE" prefixOverrides="AND|OR">  
        <if test="studentName!=null and studentName!='' ">  
            ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')   
        </if>  
        <if test="studentSex!= null and studentSex!= '' ">  
            AND ST.STUDENT_SEX = #{studentSex}   
        </if>  
    </trim>  
</select>  

choose when otherwise tab

<!-- Query students list，like Name,, or=Gender, or=Birthday,, or=Class, using choose -->  
<select id="getStudentListChooseEntity" parameterType="StudentEntity" resultMap="studentResultMap">  
    SELECT * from STUDENT_TBL ST   
    <where>  
        <choose>  
            <when test="studentName!=null and studentName!='' ">  
                    ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')   
            </when>  
            <when test="studentSex!= null and studentSex!= '' ">  
                    AND ST.STUDENT_SEX = #{studentSex}   
            </when>  
            <when test="studentBirthday!=null">  
                AND ST.STUDENT_BIRTHDAY = #{studentBirthday}   
            </when>  
            <when test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">  
                AND ST.CLASS_ID = #{classEntity.classID}   
            </when>  
            <otherwise>  
                   
            </otherwise>  
        </choose>  
    </where>  
</select> 

 foreach

<select id="getStudentListByClassIDs" resultMap="studentResultMap">  
    SELECT * FROM STUDENT_TBL ST   
     WHERE ST.CLASS_ID IN    
     <foreach collection="list" item="classList"  open="(" separator="," close=")">  
        #{classList}   
     </foreach>      
</select>  

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.

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

< resultmap >, < parametermap >, < sql >, < include >, < selectkey >, plus 9 labels of dynamic sql, of which < sql > is the label of sql fragment, sql fragment is introduced through the < include > label, and < selectkey > is the policy label generated for primary keys that do not support self increment.

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.

Mybatis's one-to-one and one to many association queries?

One to one association query

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

</mapper> 

One to many association query

<!--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>  

How many ways can MyBatis implement one-to-one? How did you do 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 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

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.

Does Mybatis support deferred loading? If yes, what is its implementation principle?

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 use CGLIB to create the proxy object of the target object. When calling the target method, enter the interceptor method, such as calling a.getB() GetName (), the interceptor invoke() method finds that a.getB () is null value, then it sends the sql of the saved B query object separately, B queries up, and then a.setB(b) is invoked, so the object B property of a has value, and then completes the B (). Call to 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.

Mybatis L1 and L2 cache

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.

What is MyBatis interface binding? 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.

What are the requirements when using the mapper interface of MyBatis?

Mapper interface method name and mapper The id of each sql defined in XML is the same;

Mapper interface method input parameter type and mapper The parameterType of each sql defined in XML is the same;

Mapper interface method output parameter type and mapper The resultType of each sql defined in XML is the same;

Mapper. The namespace in the XML file is the classpath of the mapper interface.

How to implement Mapper?

First, the interface implementation class inherits SqlSessionDaoSupport: to use this method, you need to write the mapper interface, which implements the class and mapper XML file.

1. In sqlmapconfig Configure mapper.xml Location of XML

<mappers>

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

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

</mappers>

2. Define the mapper interface

3. This. Can be used in the SqlSessionDaoSupportmapper method to implement class integration Getsqlsession() performs data addition, deletion, modification and query.

4. spring configuration

<bean id=" " class="mapper Implementation of interface">

 <property name="sqlSessionFactory" ref="sqlSessionFactory"></property>

</bean>

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

1. In sqlmapconfig Configure mapper.xml The location of the XML, if mapper The names of the XML and map interfaces are the same and in the same directory. There is no need to configure them here

<mappers>

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

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

</mappers>

2. Define mapper interface:

2.1,mapper. The namespace in XML is the address of the mapper interface

2.2. Method name and mapper in mapper interface The id of the statement defined in XML is consistent

2.3 definitions 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 writing: mapper The namespace in XML is the address of the mapper interface; Method name in mapper interface and mapper The id of the statement defined in XML is consistent; If mapper If the names of XML and mapper interfaces are consistent, they are not used in sqlmapconfig XML.

2. Define mapper interface: Note mapper The file name of XML is consistent with the interface name of mapper and placed in the same directory

3. To configure the 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

Briefly describe the operation principle of Mybatis plug-in and how to write a plug-in?