MyBatis is an open source project of apache. In 2010, this project was moved from apache software foundation to Google code and was renamed MyBatis. The following is to introduce Mybatis advanced mapping, dynamic SQL and obtain auto-increment primary key content. For details, please refer to this article.
1. Dynamic SQL
I believe that everyone will always encounter a problem when using mybatis to operate the database. If we now have a list authorList about the author, we need to query the blog information of the corresponding author in the database based on the existing author information in the authorList. Then the easiest way to think of is to traverse the authorList and obtain the corresponding information to query the database.
for(int i=0;I < authorList.size();i++) {…//Query database code//select * from blog where author=#{author,jdbcType=VARCHAR}}Think about it, if the length of authorList is N, then we need to query the database N times. If we use this method, the overhead of the program is not only about querying, but also about taking out the connection instance from the database connection pool, establishing a database connection, and returning the database instance to the database connection pool. Assume that these three actions take a total of 0.001 seconds. Then, if you use traversal methods to query, it will take 0.001N seconds more. If you need to query 1,000 times, it will be 1 second more. This is unbearable for programmers, because this is just a circular query and not counting as other business code.
So, is there a better way? The answer is yes, one of which is dynamic SQL:
First upload the code:
<select id="dynamicForeachTest" resultType="com.blog.Blog" parameterType="java.util.List">select * from blog where author in<foreach collection="list" index="index" item="item" open="(" separator="," close=")">#{item}</foreach></select>tem represents the alias when each element in the set is iterated.
index specifies a name to represent the position to which each iteration is reached during the iteration process.
open means where the statement starts,
separator indicates what symbol is used as the separator between each iteration.
close indicates what ends with so that the return value can be accepted using List<Bolg>.
However, the foreach statement in dynamic SQL uses the most real thing in insert statements and is usually used in in clauses.
2. Advanced Mapping
When using mybatis, the resultType = com.blog.author entity class is generally used to accept query results.
Or use resultType = java.util.map to return the database column name as key and the record value as value.
But this time, resultMap is needed, which allows free combination of return values to handle more complex queries.
Or just upload the code first:
SQL:
<select id="getBlogs" resultMap="blogs " parameterType="map">Select a.authorID,a.uthorName,b.blogID,b.blogNamefrom author a left join blog b on a. authorID=b. authorID where a. authorID = #{authorID,jdbcType=INTEGER}</select>Mybatis configuration:
<resultMap id="blogs" type="com.bloh.Blog"><id property="authorID" column="authorID"><result property="authorName" column="authorName"><collection property="postsList" ofType="com.bolg.Post"><id property="blogID" column="blogID"/><result property="blogName" column="blogName"/><result property="blogName"/></resultMap>
Blog Entity Class
Public class Bolg {private Integer authorID;private String authorName;private List<Post> postsList;//setter getter}Post entity class
Public class Post {private Integer blogID;private String blogName;//setter getter}This allows you to accept a complex query with an entity.
The following is the function of each attribute:
I won't go into details about other properties and configurations of ordinary mybatis queries.
resultMap is used instead of resultType, indicating the format returned by the query result
The id in resultMap has two main functions:
Similar indexes to improve query performance
Distinguish different results
Therefore, it is best not to omit id. If there is no primary key, use a field that can uniquely distinguish records instead.
result is the variable name defined in the entity class, and column is the column name of the database
collection is a collection of lists, maps, etc.
postsList is the list variable name defined in the Blog entity class
ofType is the entity class of the object in the object list.
3. Obtain the self-increasing ID:
If there is the following situation, after inserting the database record, you want to get the primary key of the insert record, and use the subsequent business code
Then mybatis also provides corresponding support for this situation (batch insertion is not supported):
MySQL is the original sound self-increasing ID; assuming the field name of the self-increasing primary key is ID
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="User">insert into <include refid="TABLE_NAME" /> ( NAME, AGE )values ( #{name}, #{age} )</insert>There are two more attributes than ordinary insertion useGeneratedKeys="true" which means that it returns the self-increment ID when enabled.
keyProperty="id" means the name of the return primary key.
Then you can use the following statements to receive in the business code:
Assume that the entity class is User
User userNew = userMapper.insert(user);
userNew.getID // is the auto-incremented ID after insertion
In fact, the auto-increment primary key of mysql can be obtained by select LAST_INSERT_ID();
So, there is another way to write it:
<insert id="insert" parameterType="User"><selectKey resultType="int" order="AFTER" keyProperty="id">SELECT LAST_INSERT_ID() AS id</selectKey>insert into name,agevalues ( #{name}, #{age} )</insert>It is exactly the opposite of mysql's method of obtaining primary keys. Mysql is the table that allocates the self-growth value after insert execution, while Oracle obtains the self-growth value before performing insert sql. Before executing insert sql, a primary key value must be specified to the record to be inserted. Therefore, you need to get the self-growth sequence at "BEFORE", and then inject it into the parameter map by selectKey. Assume self-growth or id
<insert id=" insert " useGeneratedKeys="true" keyProperty="id" parameterType="xxxx" ><selectKey resultType="int" order="BEFORE" keyProperty="id">SELECT SEQ_TABLE.NEXTVAL FROM dual</selectKey>INSERT INTO id,name,ageVALUES(#{id} #{name}, #{age} )</insert>The id here is the auto-increment id obtained by selectKey.
The reception method is the same as mysql. When obtaining the auto-increment primary key, it is best to use entity reception.
The above is the Mybatis advanced mapping, dynamic SQL and obtaining self-added primary keys introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!