MyBatis directly execute SQL query and data batch insert

I. direct execution of SQL query:

1. Extract from mappers

<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. Excerpts from DAO class

public interface SomeDAO{
List<InstanceModel> getInstanceModel(@Param("paramSQL")String sql);
} 

3. Matters needing attention

3.1: the parameter SQL of the incoming 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: differences between the #{} syntax and the ${} syntax in the mappers file:

By default, the #{} syntax causes MyBatis to generate the PreparedStatement property and use the PreparedStatement parameters (=?) To set the value. If you want to directly substitute unchanged strings into SQL, you can use ${}.

That is, when MyBatis sees #{}, it thinks you are assigning a value to a variable in SQL, just as you would to a question mark in JDBC programming (for example, MyBatis determines its type and automatically quotes it before and after). When MyBatis sees ${}, it will replace it with the value of the variable without any processing.

So when using ${}, you don't need to write attributes like "jdbcType=VARCHAR" like #{}.

3.3: resultType and resultMap

Write as in 1, < ResultMap > Part can be deleted no longer because in the following < Select > Instead of using a defined resultMap, a resultType is used.

So we can see that in terms of < Select > The definition of the return value can be written either by defining a resultMap and then referring to it, or by specifying the path of a class directly using the resultType.

Ii. Batch data insertion

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) (XXX).

2. Usage in MyBatis

2.1 extract from mappers

<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 excerpts from DAO class

public interface SomeDAO{
public void insertBatch(@Param("list")List<Student> students); 
}

Detail mybatis batch insert data

First, look at the mapper.xml file for the batch processing

<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 a few things to be aware of

1, the automatic primary key acquisition, in the insert to add the useGeneratedKeys= "true" keyProperty= "id" the two properties are invalid, and or interrupt the data insertion, if the id is the database itself, you can write nothing, in the inserted statement to remove the primary keyProperty, there is use

<selectKey keyProperty="id" order="BEFORE"
resultType="java.lang.Integer">
SELECT LAST_INSERT_ID()
</selectKey>

Note: < SelectKey > Only one tag can exist under insert; Not suitable for batch processing < SelectKey > , primary key self - increment is best, or specified
2, insert time as shown above, I use mysql, as long as the mysql function can be used, insert time and the primary key are one of the mysql function...