1. Directly execute SQL query:
1. Excerpt from mappers files
<resultMap id="AcModelResultMap" type="com.izumi.InstanceModel"><result column="instanceid" property="instanceID" jdbcType="VARCHAR" /><result column="instancename" property="instanceName" jdbcType="VARCHAR" /></resultMap><select id="getInstanceModel" resultType="com.izumi.InstanceModel">${paramSQL} </select>2. Excerpt from DAO
public interface SomeDAO{List<InstanceModel> getInstanceModel(@Param("paramSQL")String sql);}3. Things to note
3.1: The parameters of the passed method must follow the following specification "select XXX as instanceid, XXX as instancename .....", otherwise MyBatis cannot automatically turn the query result into a Java object.
3.2: The difference between #{} syntax and ${} syntax in mappers file:
By default, the #{} syntax causes MyBatis to generate the PreparedStatement property and use the PreparedStatement parameter (=?) to set the value. If you want to directly substitute unchanged strings into SQL, you can use ${}.
In other words, when MyBatis sees #{}, it will think that you are assigning values to variables in SQL, just like in JDBC programming to assign values to question marks (for example, MyBatis will judge its type and automatically add single quotes before and after). When MyBatis sees ${}, it will directly replace it with the value of the variable without doing any processing.
So when using ${}, you do not need to write attributes such as "jdbcType=VARCHAR" like #{}.
3.3: resultType and resultMap
According to the writing method in 1, the <resultMap> part can be deleted without using it, because in the next <select>, the defined resultMap is not used, but resultType is used.
So we can see that there are two ways to write the definition of the return value of <select>. One is to define a resultMap and then refer to this resultMap. The other is to directly use resultType to specify the path of a class.
2. Batch insertion of data
1. Experience tells us that using insert into XXX values(XX)(XXX)(XXX) is more efficient than using insert into XXX values(XX), insert into XXX values(XXX), insert into XXX values(XXX).
2. Usage in MyBatis
2.1. Excerpt from mappers file
<insert id="insertBatch" > insert into student ( <include refid="Base_Column_List" /> ) values <foreach collection="list" item="item" index="index" separator=","> (null,#{item.name},#{item.sex},#{item.address},#{item.telephone},#{item.tId}) </foreach></insert>2.2. Excerpt from DAO
public interface SomeDAO{public void insertBatch(@Param("list")List<Student> students); }Detailed explanation of mybatis batch insertion data
First look at the batch mapper.xml file
<insert id="insertbatch" parameterType="java.util.List"><selectKey keyProperty="fetchTime" order="BEFORE"resultType="java.lang.String">SELECT CURRENT_TIMESTAMP()</selectKey>insert into kangaiduoyaodian ( depart1, depart2, product_name,generic_name,img, product_specification, unit,approval_certificate, manufacturer, marketPrice, vipPrice,website,fetch_time, productdesc ) values<foreach collection="list" item="item" index="index"separator=",">( #{item.depart1}, #{item.depart2}, #{item.productName}, #{item.genericName}, #{item.img}, #{item.productSpecification}, #{item.unit}, #{item.approvalCertificate}, #{item.manufacturer}, #{item.marketprice}, #{item.vipprice}, #{item.website}, #{fetchTime}, #{item.productdesc} )</foreach></insert>In batch processing, I found that there are several issues that need attention.
1. Automatically obtain the primary key. Add the useGeneratedKeys=”true” keyProperty=”id” in insert. The two properties of the two properties are invalid and may interrupt the data insertion. If the id is the database self-increase, you can write nothing. Remove the primary key attribute in the inserted statement, and use it.
<selectKey keyProperty="id" order="BEFORE"resultType="java.lang.Integer">SELECT LAST_INSERT_ID()</selectKey>
Note: The <selectKey> tag can only exist in insert; it is not suitable to use <selectKey> during batch processing. It is best to increase the primary key itself, or specify it.
2. Getting the insertion time is shown above. I am using mysql. As long as it is a mysql function, it can be used. The insertion time and primary key are one of the mysql functions. . .