MyBatis directly execute SQL queries and BULK INSERT data

MyBatis directly execute SQL queries and BULK INSERT data

First, execute SQL query directly:

1. mappers file Excerpt

<ResultmapId= "Acmodelresultmap"Type= "Com.izumi.InstanceModel">
<ResultColumn="InstanceidProperty= "InstanceID"Jdbctype= "VARCHAR"/>
<result column= " instancename " property = "instancename"  jdbctype = "VARCHAR"  /
</resultmap >

id= "Getinstancemodel"  resulttype= "Com.izumi.InstanceModel" >
  ${paramsql} 

</Select>

2. DAO class Excerpt

Public Interface somedao{
List<instancemodel> Getinstancemodel (@Param ("Paramsql") String sql);

}

3. Precautions

3.1: The parameter of the incoming method SQL must follow the following specifications "Select XXX as Instanceid, XXX as instancename ...", otherwise mybatis cannot automatically turn the query result into a Java object.

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

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

That is, mybatis see #{} will think you are assigning a value to a variable in SQL, just like a question mark in JDBC programming (for example, MyBatis will determine its type and automatically add single quotes around it). When MyBatis sees ${}, it is replaced directly with the value of the variable without any processing.

So when using ${}, you do not need to write "Jdbctype=varchar" Properties like #{}.

3.3:Resulttype and Resultmap

Follow the 1 notation,  < resultmap  The >  section can be removed without, because in the next <select>

So we can see that There are two ways to define the definition of < SELECT > return value, one is to define a resultmap and then reference this resultmap, Another way is to specify the path of a class directly using Resulttype.

Second, BULK INSERT data

1. Experience tells us that using INSERT into XXX values (xx) (XXX) (XXX), than using INSERT INTO XXX values (xx), insert to XXX values (XXX), insert into XXX VALUES (XXX) are highly efficient.

2. Usage in MyBatis

2.1. mappers file Excerpt

<InsertId= "Insertbatch">
INSERT INTO student (<IncluderefID= "Base_column_list"/>)
Values
<ForeachCollection= "List"Item= "Item"Index= "Index"Separator= ","
( Null,#{item.name},#{item.sex},#{item.address},#{item.telephone},#{item.tid})
</foreach>

</Insert>

2.2. DAO class Excerpt

public Interface somedao{

public void Insertbatch (@Param ("list") list<student> students);

}

Reference:

1, "MyBatis User Guide Chinese version" Translator: has made a wish

2, http://winhack.iteye.com/blog/1522181

MyBatis directly execute SQL queries and BULK INSERT data