Detailed mybatis directly execute SQL query and data BULK INSERT _java

First, execute SQL query directly:

1, mappers document extracts

<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. DAO class Excerpt

public interface somedao{
list<instancemodel> Getinstancemodel (@Param ("Paramsql") String SQL);

3. Attention Matters

3.1: Parameter sql of the incoming method must follow the following specification "Select XXX as Instanceid, xxx as instancename ..." or MyBatis cannot automatically turn the query result into a Java object.

The difference between the #{} syntax and the ${} syntax in the 3.2:mappers file:

By default, the #{} syntax prompts MyBatis to generate PreparedStatement properties and uses preparedstatement parameters (=?). To set the value. You can use ${} if you want to substitute an unchanged string into SQL directly.

In other words, MyBatis see #{} Think you're assigning a value to a variable in SQL, just as you would assign a question mark in JDBC programming (for example, MyBatis will judge its type and automatically put a single quote around it). When MyBatis sees ${}, it replaces it directly with the value of the variable without any processing.

So when using ${}, you don't need to write "Jdbctype=varchar" like #{}.

3.3:resulttype and Resultmap

According to the writing in 1, the < Resultmap > section can be deleted, because in the next <select > use of the defined Resultmap is not used, but Resulttype is used.

So we can see that there are two ways to define the <select > return value, one is to define a resultmap and then reference the Resultmap, and one is to specify the path of a class directly using Resulttype.

Second, BULK INSERT data

1, experience tells us that the use of INSERT INTO XXX values (xx) (XXX) (XXX), than using INSERT INTO XXX values (XX), inserts into XXX values (XXX), insert into XXX VALUES (XXX) efficiency is high.

2, the use in the MyBatis

2.1, Mappers document extracts

<insert id= "Insertbatch" > INSERT into Student (<include refid= "base_column_list"/>) values <foreach lection= "List" item= "item" index= "index" separator= "," > (null,#{item.name},#{item.sex},#{item.address},#{ Item.telephone},#{item.tid}) </foreach>

2.2. DAO class Excerpt

Public interface somedao{public
void Insertbatch (@Param ("list") list<student> students); 

Detailed MyBatis BULK INSERT data

First look at the batch of mapper.xml files

<insert id= "Insertbatch" parametertype= "java.util.List" > <selectkey keyproperty= "fetchtime"
Before "
resulttype=" java.lang.String ">
SELECT current_timestamp ()
</selectKey>
Insert Into the 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 issues to be aware of

1. Automatic acquisition of primary key, add usegeneratedkeys= "true" keyproperty= "id" in insert, and break data insert, if ID is database increment, you can write nothing. Remove the primary key attribute in the inserted statement, and use the

<selectkey keyproperty= "id" order= "before"
resulttype= "Java.lang.Integer" >
SELECT last_insert_id ()
</selectKey>

Note: <selectkey > tags can only exist under INSERT, batch processing is not suitable to use <selectkey, the primary key is the best, or specify
2, insert time to get as shown above, I use MySQL, as long as the MySQL function can be used, insert time and primary key is a MySQL function ...