I just finished my interview with Spring yesterday. According to the feedback from HR, the interviewer was satisfied with my overall performance, and then informed me that I was free to talk about relevant technologies again today. On the way there, I was thinking, what are you going to ask today, JVM? Multithreading? Or distributed…… Really think more in the heart more bottomless.

Thinking about thinking about arrived, although still the familiar interviewer, but that young and promising face did not let me put down the nervous mood.

He spoke first: I felt very good about you in the interview yesterday. You said that you still used mybatis framework as database access in your project, so let’s talk about it today.

Interviewer: Please tell me your general understanding of Mybatis first.

Me: MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. It avoids almost all JDBC code and manual parameter setting and result set fetching. MyBatis can Map interfaces and Java POJOs to records in the database using simple XML or annotations for configuration and native maps.

Interviewer: Why do you choose Mybatis and Hibernate? What’s the difference between them?

Me: MyBatis focuses on the mapping between POJO and SQL, and then through the mapping configuration file, the parameters required by SQL, and the returned result fields are mapped to the specified POJO. Hibernate ORM implements the mapping between POJOs and database tables, as well as the automatic generation and execution of SQl, that is, Hibernate automatically generates the corresponding SQl according to the specified storage logic and calls the JDBC interface to execute it.

Here are four ways to compare the differences:

1. Development comparison:

Mybatis framework is relatively simple and easy to use, for advanced queries, MyBatis needs to manually write SQL statements.

Hibernate is more difficult to master than MyBatis, Hibernate has a good mapping mechanism, developers do not need to care about SQL generation and result mapping, can pay more attention to business process.

2. Tuning scheme:

Mybatis can carry out detailed SQL optimization design, using a reasonable session management mechanism.

Hibernate can specify a reasonable caching strategy;

Try to use the delay loading feature;

Adopt reasonable session management mechanism;

Adopt batch capture and set reasonable batch processing parameters.

3. Expansibility:

All SQL statements in the Mybatis project are dependent on the database used, so different database types are not supported well. The association between Hibernate and a specific database only needs to be configured in an XML file. All HQL statements are independent of the specific database, and portability is very good.

4. Cache mechanism:

Caching is disabled by default in MyBatis. To enable level 2 caching, add the SQL mapping file; All SELECT statements in the mapping file will be cached, and all INSERT/UPDATE/DELETE statements in the mapping file will be flushed.

The cache is recycled using the LRU(least recently used) algorithm;

The cache stores 1024 references to list collection objects;

The cache is treated as a read/write cache, meaning that object retrieval is not shared and can be safely modified by the caller without interfering with potential changes made by other callers or threads.

The level 1 cache of Hibernate is Session cache. To make good use of level 1 cache, we need to manage the life cycle of Session well. Level 2 caches are SessionFactory caches and are divided into built-in caches and external caches.

In addition, there is a saying that MyBatis is semi-automatic ORM mapping tool, Hibernate is automatic. This is mainly because when using Hibernate to query associative or collection objects, they can be obtained directly by calling the API interface based on the object relationship model. When Mybatis queries associated objects or collection objects, it needs to write SQL manually to complete, so it is called semi-automatic.

As for why our company chooses semi-automatic Mybatis, it is mainly because our business often needs to write complex SQL, such as dynamic SQL. It also makes it easier to use indexes to optimize SQL statements.

Interviewer: Why don’t you talk about the JDBC execution process first

I:

1. Load the JDBC driver

2. Establish and obtain database connections

3. Create the JDBC Statements object

4. Set the incoming parameters of the SQL statement

5. Execute SQL statements and obtain query results

6. Transform the query results and return the results

7. Release related resources (close Connection, Statement, ResultSet)

Interviewer: Can you tell me the process of executing SQL in Mybatis?

Me: Ok.

1. Load configuration and initialize:

Load the configuration file, load the SQl configuration information into one MappedStatement object (including the incoming parameter mapping configuration, the executed SQl statement, and the resulting mapping configuration), and store it in memory.

2, pass the call request:

Call the API provided by Mybatis, pass in the SQL ID and parameter object, and pass the request to the lower request processing layer for processing.

3. Handling requests:

Find the MappedStatement object based on the SQL ID.

The MappedStatement object is parsed from the passed parameter object to obtain the SQL to be executed and the execution parameters.

Obtain the database connection, according to the SQL statement and execution parameters to the database execution, and get the execution results;

According to the result mapping configuration in the MappedStatement object, the execution result is transformed to obtain the final processing result.

Release connection resources;

Returns the final result.

In short, the process is:

Load configuration ->SQL parsing ->SQL execution -> Result mapping -> Release connection

Interviewer: Good. You were talking about initialization, do you know anything about mybatis initialization?

Me: So to speak, the process of Myabtis initialization is the process of creating a Configuration object.

The process is simple:

1. Load the configuration file mybatis-config. XML into mybatis.

2. Use the Configuration object as a container for all Configuration information. The organization of this object is almost exactly the same as that of the XML Configuration file, so that the Configuration file information can be stored in this object and easily accessed.

Interviewer: Then I ask further, have you read the source code of Mybatis?

Me: No, I haven’t. The key class does know a little bit.

Interviewer: Oh, what are the core classes of Mybatis that you know?

I: (thought: since the interview before the preparation, or to say, otherwise how to appear some nb)

The first is SqlSessionFactoryBuilder:

The main purpose of this class is to create an SqlSessionFactory.

You can reuse this class to create multiple instances of SqlSessionFactory, but it’s best not to leave it around forever to keep all the XML parsing resources open for more important things.

This class can be instantiated, used, and discarded, and is no longer needed once the SqlSessionFactory has been created.

The second is the SqlSessionFactory interface:

It acts as an SQL session factory and is used to create SQLSessions.

The SqlSessionFactory, once created, should persist for the duration of the application, and its best scope is the application scope.

The third and very important SqlSession interface is:

It is an important interface of Mybatis, which defines the increase, delete, change and check of database and common methods of transaction management.

SqlSession also provides methods for finding Mapper interfaces.

Each thread should have its own INSTANCE of SqlSession, and since this instance is not thread-safe, its best scope is the request or method scope.

Each time you receive an HTTP request, you can open a SqlSession and close it when the response is returned.

The fourth is the main Mapper interface we coded:

Mapper interface is a data manipulation interface defined by programmers, similar to the DAO interface.

Unlike DAO interface, Mapper interface only needs to be defined without implementation. Mybatis will automatically create dynamic proxy objects for Mapper interface.

The methods of the Mapper interface usually have a one-to-one correspondence with XML nodes such as SELECT, INSERT, UPDATE, and DELETE in the Mapper configuration file.

Interviewer: Can you tell me the main parts of mybatis source code?

Me: Ok, the main components are as follows:

1. SqlSession: as the main top-level API of Mybatis work, it represents the session of interaction with the database, and completes the necessary function of adding, deleting, changing and checking the database.

2. Executor: MyBatis Executor is the core of MyBatis scheduling. It is responsible for the generation of SQL statements and the maintenance of the query cache.

3. StatementHandler: encapsulates the JDBCStatement operation and is responsible for the operation of the JDBCStatement.

ParameterHandler: converts user-passed parameters to those required for a JDBC Statement.

5. ResultSetHandler: Converts the ResultSet returned by JDBC into a collection of type List.

TypeHandler: Maps and transforms between Java and JDBC data types.

Article 7, MappedStatement: maintains a select/update/delete/insert node encapsulation.

Sqlsource: dynamically generates SQL statements based on the parameterObject that the user delivers, encapsulates the information in BoundSql objects, and returns the SQL statement.

BoundSql: indicates the dynamically generated SQL statement and related parameter information.

10. Configuration: All the Configuration information of Mybatis is maintained in this object.

Interviewer: After that, let’s talk about actual practice. How did you integrate Spring and Mybatis in your project?

Me: Let me talk about how XML is configured first.

Add mybatis-spring package:

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis-spring</artifactId>
  <version>x.x.x</version>
</dependency>Copy the code

Mysql > configure SqlSessionFactory

After integration, there is no need for a separate MyBatis configuration file, all configuration content can be in the context of Spring.

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <! The mapperLocations attribute is used to specify the path of the XML file when the mybatis XML file is not in the same package as the Mapper interface. <property name="mapperLocations" value="classpath:mapper/**/*.xml"/> <! - loading mybatis global configuration file - > < property name = "configLocation" value = "classpath: mybatis/mybatis - config. XML" / > < / bean >Copy the code

Datasource: datasource configuration. DBCP,C3P0, Druid, etc.

MapperLocations: indicates the configuration of interface XML files. If the mapping interface files (mapper interface) and mapping XML files (mapper.xml) are not configured, they need to be stored in the same package.

3. Configure data mapper class:

Using the automatic scanning mechanism provided by Mybatis – Spring:

<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd"> <! - automatic scanning - > < mybatis: scan base - package = "org. Mybatis. Spring. Sample. Mapper" / > < / beans >Copy the code

Me :(continuing) it seems that most of the way to configure mybatis and the data source is with annotations, using Java code and annotations provided by spring. (In fact, the steps are roughly the same, because the code is not disclosed because of security issues, want to learn can be found online.)

Interviewer: Can you write a SELECT SQL statement in a Mapper mapping file?

This statement, called selectPerson, takes an int and returns a HashMap object, where the key is the column name and the value is the corresponding value in the resulting row.

<select id="selectPerson" parameterType = "int" resultType="hashmap"
select * from person where id =#{id}
</select>Copy the code

I (continued) : Select has these attributes to choose from:

  1. Id: Mandatory, unique identifier in the namespace that can be used to reference this statement.

  2. ParameterType: Optional, the fully qualified name or alias of the parameter class to be passed in for this statement.

  3. ResultType: The fully qualified name or alias of the class of the expected type returned from this statement.

If it is a collection, it should be the type contained in the collection, not the collection itself.

  1. ResultMap: indicates the named reference of an external resultMap. Caution Use resultType or resultMap.

  2. FlushCache: default false. Setting this to true means that whenever a statement is called, the local cache and the secondary cache will be cleared.

  3. UseCache: True for the select element. Setting it to true causes the results of this statement to be cached at level 2.

  4. Timeout: Default value is unset (dependent driver). This setting is the number of seconds the driver waits for the database to return the result of the request before throwing an exception.

  5. FetchSize: The default is unset (dependent driver). This is an attempt to influence the number of rows returned by the driver per batch to be equal to this value.

  6. StatementType: The default value is PREPARED. This will cause MyBatis to use Statement, PreparedStatement, or CallableStatement respectively.

Interviewer: What should I do when the attribute names in the entity class are different from the field names in the table?

Me: There are two ways.

The first is straightforward: define an alias in the SQL statement to force the alias of the returned field name to be the same as the attribute name in the entity class.

<select id="getByOrderId" parameterType="java.lang.Long" resultType="com.demo.entity.OrderInfo">
select order_id OrderId, order_sn orderSn, total_fee totalFee, create_time createTime
from order_info where order_id=#{orderId}
</select>Copy the code

The second is more elegant:

ResultMap is used to map the mapping between field names of data tables and attribute names of entity classes.

(recommended)

<resultMap id = "BaseResultMap" type="com.demo.entity.OrderInfo">
    <id property="OrderId" column="order_id"/>
    <result property="orderSn" column="order_sn"/>
    <result property="totalFee" column="total_fee"/>
    <result property="createTime" column="create_time"/>
</resultMap>
<select id="getByOrderId" parameterType="java.lang.Long" resultMap="BaseResultMap">
select order_id, order_sn, total_fee, create_time
from order_info where order_id=#{orderId}
</select>Copy the code

Interviewer: How do I get the auto-generated primary key?

If you want to know what the primary key of the data you just inserted is, you can get it in the following way.

Use LAST_INSERT_ID () to obtain the value of the newly inserted primary key. After the insert statement, run select 1639308 to obtain the value of the newly inserted primary key.

<insert id='insert' parameterType="com.demo.entity.OrderInfo"    
<selectKey keyProperty="orderId" order="AFTER" resultType="java.lang.Long">        
select 1639308    
</selectKey>    
insert into order_info(order_sn,total_fee,create_time)    
values(#{orderSn},#{totalFee},#{createTime)</insert>Copy the code

Interviewer: What dynamic SQL do you know about Mybatis?

I:

If: conditional. If you do not use this tag, you must make certain judgments in your code, such as whether an element is empty, whether a string is an empty string, and certain enumeration values need to be checked for execution conditions.

Choose/WHEN /otherwise: This tag combination is similar to if/else if… /else: select one of several options, if none of them satisfy the criteria, then execute the content.

Such as:

<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap"> SELECT * from STUDENT WHERE 1=1 <where> <choose> <when test="Name! =null and student! ='' "> AND name LIKE CONCAT(CONCAT('%', #{student}),'%') </when> <when test="hobby! = null and hobby! = '' "> AND hobby = #{hobby} </when> <otherwise> AND AGE = 15 </otherwise> </choose> </where> </select>Copy the code

3. Foreach tag: used for loop. Such as:

<select id="listByOrderIds" resultMap="BaseResultMap">
    select * from order_info where order_id in 
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
</select>Copy the code

4. Other tags include set tags, WHERE tags, and trim tags.

Interviewer: What’s the difference between #{} and ${}?

I: #{} is to parse the parameters passed in, and the other is to concatenate the parameters into SQl. #{} is precompiled processing, while the other is string substitution. And #{} prevents SQL injection.

Select * from emp where name=#{empName} select * from emp where name=? . Select * from emp where name=${empName} from emp where name=’Smith’

Interviewer: How do you pass multiple parameters in Mapper?

Me: There are two ways:

1. The idea of using placeholders:

(1) Use #{0} in the mapping file, where #{1} represents the number of arguments passed in.

(2) Use the @param annotation to name parameters (recommended) for example:

//mapper interface Public OrderInfo getByOrderIdAndStatus(Long orderId, String status); //mapper. XML <select ID ="getByOrderIdAndStatus" resultMap="BaseResultMap"> select * from order_info where Order_id =#{0} and status=#{1} </select> public OrderInfo getByOrderIdAndStatus(@param("orderId"))  @param("status")String status); //mapper. XML <select ID ="getByOrderIdAndStatus" resultMap="BaseResultMap"> select * from order_info where order_id=#{orderId} and status=#{status} </select>Copy the code

2. Load with Map collection as parameters

Map<String, Object> map = new HashMap(); map.put("orderId", 1L); map.put("status", "NORMAL"); OrderInfo orderinfo = getByOrderIdAndStatus(map); // Mapper interface Public OrderInfo getByOrderIdAndStatus(Map<String, Object> Map); //mapper. XML <select ID ="getByOrderIdAndStatus" parameterType="map" resultMap="BaseResultMap"> select * from order_info where order_id=#{orderId} and status=#{status} </select>Copy the code

Interviewer: Yes, it seems that you are quite skilled in using Mybatis. That’s all for today’s interview. Go home and wait for the news.

Me: Ok.

I’m a little nervous. Do you think I can pass the interview?

Wenyuan network, only for the use of learning, such as infringement, contact deletion.

I have collected quality technical articles and experience summary in my public account “Java Circle”.

In order to facilitate your learning, I have compiled a set of learning materials, covering Java virtual machine, Spring framework, Java threads, data structures, design patterns and so on, free for students who love Java!