MyBatis configuration file

The MyBatis core configuration file, MyBatis -config. XML, configures a lot of information that affects the behavior of MyBatis. This information is usually only configured in a file and cannot be changed easily. After integration with the Spring framework, MyBatis core profile information will be configured into the Spring profile. Therefore, there are few cases in which the core configuration file of MyBatis needs to be written or modified during actual development.

The element nodes of this configuration file are sequential and immutable

You can configure Settings in the Java properties configuration file to modify the behavior of MyBatis at runtime. TypeAliases Give Java types an alias (typeHandlers for short) Type processors objectFactory objectFactory plugins environments transactionManager transactionManager dataSource mappers mapperCopy the code

The sample


      
<! DOCTYPEconfiguration
PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<! -- When using MyBatis nested query mode for associated query, using MyBatis lazy loading can improve query efficiency to a certain extent.
	<settings>
		<! -- Turn on the lazy loading switch -->
		<setting name="lazyLoadingEnabled" value="true" />
		<! -- Change active load to on-demand load -->
		<setting name="aggressiveLazyLoading" value="false" />
	</settings>

	<mappers><! Mapper, tell MyBatis where to find the mapping file.
		<mapper resource="com/mybatis/UserMapper.xml" />
		<mapper resource="com/mybatis/PersonMapper.xml" />
		<mapper resource="com/mybatis/IdCardMapper.xml" />
		<mapper resource="com/mybatis/OrdersMapper.xml" />
	</mappers>
</configuration>
Copy the code

mapper

The mapper specifies the mapping rules for SQL statements and can be implemented in two ways: based on interface and XML, or (less commonly) based on interface and annotations. Common elements in an XML mapper are as follows

The element name	describe	note
select	The query	Customize parameters and result sets
insert	insert	Returns an integer representing the number of inserted rows
update	update	Returns an integer representing the number of updated rows
delete	delete	Returns an integer representing the number of deleted rows
sql	Define SQL phrases that can be referenced elsewhere	This is equivalent to aliasing the table name
resultMap	Mapping the result set, more on that later	Provide mapping rules

2.1 select

The example is to receive a parameter of type Integer, return an object of type MyUser, and the result set is automatically mapped to the MyUser attribute.

<! SQL > select * from user where uid = 1;
<select id="selectUserById" parameterType="Integer" resultType="com.po.MyUser">
    select * from user where uid = #{uid}
</select>
Copy the code

For cases where multiple parameters are passed in, there are two implementations: Map key-value pairs, and creating new Javabeans for the result set

2.1.1 Transferring Parameters using Map

The interface is defined as follows:

public List<MyUser> selectAllUser(Map<String, Object> param);
Copy the code

Mapper.xml is defined as follows

<! Select * from male user where user name = Chen -->
<select id="selectAllUser"  resultType="com.po.MyUser" parameterType="map">
    select * from user 
    where uname like concat('%',#{u_name},'%')
    and usex = #{u_sex}
</select>
Copy the code

The Map key is of String type, indicating the attribute name. Parameter names u_name and u_sex in the PRECEDING SQL file are Map keys. The Map value is of the Object type, representing the attribute value.

Using a Map does not limit the type of data it passes, so it is not very businesslike and readable.

2.1.2 Using JavaBean to Transmit Parameters

Create a special class for the result set whose attributes correspond to the column names of the result set. Create the beans as follows

package com.pojo;
public class SeletUserParam {
    private String u_name;
    private String u_sex;
    public String getU_name(a) {
        return u_name;
    }
    public void setU_name(String u_name) {
        this.u_name = u_name;
    }
    public String getU_sex(a) {
        return u_sex;
    }
    public void setU_sex(String u_sex) {
        this.u_sex = u_sex; }}Copy the code

The interface is defined as follows

public List<MyUser> selectAllUser(SeletUserParam param);
Copy the code

Mapper.xml is defined as follows

<select id="selectAllUser"  resultType="com.po.MyUser" parameterType="com.pojo.SeletUserParam">
    select * from user 
    where uname like concat('%',#{u_name},'%')
    and usex = #{u_sex}
</select>
Copy the code

2.2 insert

Its attributes are mostly the same as those of the

attribute	instructions
keyProperty	This property assigns the value returned by an insert or update operation to a property of the PO class, usually the property corresponding to the primary key. In the case of a federated primary key, multiple values can be separated by commas.
keyColumn	This property is used to set which column is the primary key, which is required if the primary key column is not the first column in the table. In the case of a federated primary key, multiple values can be separated by commas.
useGeneratedKeys	This property will enable MyBatis to use JDBC’s getGeneratedKeys() method to retrieve primary keys produced internally by the database, such as MySQL, SQL Server, and automatically incremented fields, with a default value of false.

Enable primary key backfill (primary key increment) :

<! Add a user and backfill the primary key with uid (Po attribute) -->
<insert id="addUser" parameterType="com.po.MyUser"  keyProperty="uid" useGeneratedKeys="true">
    insert into user (uname,usex) values(#{uname},#{usex})
</insert>
Copy the code

If primary key customization is not supported, use MyBatis

element to customize primary keys

<insert id="insertUser" parameterType="com.po.MyUser">

    <! Select primary key from selectKey; select primary key from selectKey;
    <selectKey keyProperty="uid" resultType="Integer" order="BEFORE">
       select if(max(uid) is null, 1 , max(uid)+1) as newUid from user
    </selectKey>
    
    insert into user (uid,uname,usex) values(#{uid},#{uname},#{usex}) 
</insert>
Copy the code

2.3 update and delete

<! -- Modify a user -->
<update id="updateUser" parameterType="com.po.MyUser">
    update user set uname = #{uname},usex = #{usex} where uid = #{uid}
</update>
<! -- Delete a user -->
<delete id="deleteUser" parameterType="Integer"> 
    delete from user where uid = #{uid}
</delete>
Copy the code

2.4 SQL

The element is used to define part of an SQL statement (a snippet of code) that can be referenced by subsequent SQL statements, such as column names that are used repeatedly.

<! Define a code snippet -->
<sql id="comColumns">uid,uname,usex</sql>

<! -- Change the code snippet -->
<select id="selectUser" resultType="com.po.MyUser">
    select 
    <include refid="comColumns"/> from user    
</select>
Copy the code

2.5 resultMap

The

element represents the resultMap set and is the most important and powerful element in MyBatis. It is mainly used to define mapping rules, cascading updates, and type converters.

<resultMap type="" id="">
    <constructor>    <! Class is used to inject results into the constructor when instantiated.
        <idArg/>     <! -- ID parameter, result is ID -->
        <arg/>       <! A common result injected into the constructor -->
    </constructor>
    <id/>      <! Which column is the primary key?
    <result/>  <! Pojos and datafile columns -->
    <association property=""/>     <! -- Used for one-to-one associations -->
    <collection property=""/>      <! -- For one-to-many, many-to-many associations -->
    <discriminator javaType="">    <! Use the result value to determine which result map to use -->
        <case value=""/>   <! Result mapping based on some values -->
    </discriminator>
</resultMap>
Copy the code

The TYPE attribute indicates the REQUIRED POJO, and the ID attribute is the unique identifier of the resultMap. The children

,
, and

are used in cascading cases.

There are two ways to store query results: Map and POJO

2.5.1 Using Map to Store Results

UserDao.java

package com.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Repository("userDao")
@Mapper
public interface UserDao {
	public List<Map<String,Object>> selectAll();
}
Copy the code

UserMapper.xml


      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.UserDao">
	<select id="selectAll" resultType="map">
		select * from user
	</select>
</mapper>
Copy the code

UserController.java

package com.controller;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import com.dao.UserDao;

@Controller("userController")
public class UserController {
	@Autowired
	private UserDao userDao;
	public void test(a) {
		List<Map<String,Object>> tmp= userDao.selectAll();
		for(Map<String,Object> myUser : tmp) { System.out.println(myUser); }}}Copy the code

2.5.2 Using POJOs to Store Results

With POJO results, automatic mapping can be done using resultType, but sometimes more complex mappings or cascades are required, where the collection of mappings needs to be configured using the resultMap attribute of the < SELECT > element.

The resultMap is used as follows

Create a result set for the POJO class MapUser

package com.pojo;
public class MapUser {
	private Integer m_uid;
	private String m_uname;
	private String m_usex;
	public Integer getM_uid(a) {
		return m_uid;
	} 
	public void setM_uid(Integer m_uid) {
		this.m_uid = m_uid;
	}
	public String getM_uname(a) {
		return m_uname;
	}
	public void setM_uname(String m_uname) {
		this.m_uname = m_uname;
	}
	public String getM_usex(a) {
		return m_usex;
	}
	public void setM_usex(String m_usex) {
		this.m_usex = m_usex;
	}
	@Override
	public String toString(a) {
		return "User [uid=" + m_uid +",uname=" + m_uname + ",usex=" + m_usex +"]"; }}Copy the code

Configure a resultMap in mapper.xml to map POJO classes to result set columns and use this resultMap when querying


      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.UserDao">


    <! -- Custom result set type -->
    <resultMap type="com.pojo.MapUser" id="myResult">
            <! -- Property = com.pojo.MapUser -->
            <! -- column is the name of the query result column, which can be from different tables -->
            <id property="m_uid" column="uid"/>
            <result property="m_uname" column="uname"/>
            <result property="m_usex" column="usex"/>
    </resultMap>
    
    
    <! Query all users with custom result set type -->
    <select id="selectResultMap" resultMap="myResult">
            select * from user
    </select>
</mapper>
Copy the code

Interface methods

public List<MapUser> selectResultMap(a);
Copy the code

2.6 Cascading Query

If A foreign key in table A references the primary key of table B, table A is the child table and table B is the parent table. When the data of table A is queried, the related records of table B are also returned via the foreign key of table A, which is A cascading query. For example, when a person’s information is queried, his ID information is also returned based on the foreign key (ID number).

2.6.1 A Pair of First-level connections

The person table has id,name,age,idcard_id. The IDcard table has id and code

A pair of cascaded queries can be implemented in three ways: nested queries, nested results, and poJOs

1. Set up the PO/POJO

The Idcard class corresponds to the Idcard table

package com.po;
public class Idcard {
	private Integer id;
	private String code;
	// Getter, setter, toString methods omitted
}
Copy the code

Person corresponds to the Person table and includes information about the IDcard cascaded

package com.po;
public class Person {
	private Integer id;
	private String name;
	private Integer age;
	private Idcard card;	// Personal id card association
	// Getter, setter, toString methods omitted
}
Copy the code

SelectPersonById, corresponding to the POJO class created by the result table, is used for the third cascading query method

package com.pojo;

public class SelectPersonById {
	private Integer id;
	private String name;
	private Integer age;
	private String code;
	// Getter, setter, toString methods omitted
}
Copy the code

2. Write Dao layer query interface

IdCardDao.java

@Repository("idCardDao")
@Mapper
public interface IdCardDao {
	public Idcard selectCodeById(Integer i);
}
Copy the code

Persondao.java, corresponding to three pairs of cascading query methods

@Repository("personDao")
@Mapper
public interface PersonDao {
	public Person selectPersonById1(Integer id);
	public Person selectPersonById2(Integer id);
	public SelectPersonById selectPersonById3(Integer id);
}
Copy the code

3. Compile IdcardMapper and PersonMapper mapping files

IdcardMapper.xml

<mapper namespace="com.dao.IdCardDao">
	<select id="selectCodeById" parameterType="Integer" resultType="com.po.Idcard">
		select * from idcard where id=#{id}
	</select>
</mapper>
Copy the code

PersonMapper.xml

<mapper namespace="com.dao.PersonDao">

	<! Query personal information by id one-to-one: first method (nested query)
	<resultMap type="com.po.Person" id="cardAndPerson1">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
		<! -- one-to-one associated query -->
		<association property="card" column="idcard_id" javaType="com.po.Idcard"
		select="com.dao.IdCardDao.selectCodeById"/>
	</resultMap>
	<select id="selectPersonById1" parameterType="Integer" resultMap="cardAndPerson1">
		select * from person where id=#{id}
	</select>
        
        
	<! Query personal information by id one-to-one: second method (nested results) -->
	<resultMap type="com.po.Person" id="cardAndPerson2">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
		<! -- one-to-one associated query -->
		<association property="card" javaType="com.po.Idcard">
			<id property="id" column="idcard_id"/>
			<result property="code" column="code"/>
		</association>
	</resultMap>
	<select id="selectPersonById2" parameterType="Integer" resultMap="cardAndPerson2">
		select p.*,ic.code 
		from person p, idcard ic 
		where p.idcard_id = ic.id and p.id=#{id}
	</select>
        
        
        
	<! -- One to one query personal information by ID: third method (use POJO to store results) -->
	<select id="selectPersonById3" parameterType="Integer" resultType="com.pojo.SelectPersonById">
		select p.*,ic.code 
		from person p, idcard ic 
		where p.idcard_id = ic.id and p.id=#{id}
	</select>
</mapper>
Copy the code

4. Configure mybatis-config. XML, tell him where to find the mapping file, and turn on the delay switch


      
<! DOCTYPEconfiguration
PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<! -- When using MyBatis nested query mode for associated query, using MyBatis lazy loading can improve query efficiency to a certain extent.
	<settings>
		<! -- Turn on the lazy loading switch -->
		<setting name="lazyLoadingEnabled" value="true" />
		<! -- Change active load to on-demand load -->
		<setting name="aggressiveLazyLoading" value="false" />
	</settings>

	<mappers><! Mapper, tell MyBatis where to find the mapping file.
		<mapper resource="com/mybatis/PersonMapper.xml" />
		<mapper resource="com/mybatis/IdCardMapper.xml" />
	</mappers>
</configuration>
Copy the code

5. Configure application. XML. Here configure MyBatis and enable scan


      
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx" 
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
                
    <context:component-scan base-package="com.dao"/>
    <context:component-scan base-package="com.controller"/> 

    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/sql_tmp? characterEncoding=utf8&amp;useSSL=false" />
        <property name="username" value="root" />
        <property name="password" value="20011017lh" />
        <property name="maxTotal" value="30"/>
        <property name="maxIdle" value="10"/>
        <property name="initialSize" value="5"/>
    </bean>
    
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="dataSource" ref="dataSource" />  
        <property name="configLocation" value="classpath:com/mybatis/mybatis-config.xml"/>
    </bean>  
    
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>
</beans>
Copy the code

6. Create Controller class and test class oneToonecontroller.java

package com.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import com.dao.PersonDao;
import com.po.Person;
import com.pojo.SelectPersonById;
@Controller("oneToOneController")
public class OneToOneController {
	@Autowired
	private PersonDao personDao;
	public void test(a) {
		Person p1 = personDao.selectPersonById1(1);
		System.out.println(p1);
		System.out.println("= = = = = = = = = = = = = = = = = = = = = = =");
		Person p2 = personDao.selectPersonById2(1);
		System.out.println(p2);
		System.out.println("= = = = = = = = = = = = = = = = = = = = = = =");
		SelectPersonById p3 = personDao.selectPersonById3(1); System.out.println(p3); }}Copy the code

TestOneToOne.java

package com.controller;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestOneToOne {
	public static void main(String[] args) {
		@SuppressWarnings("resource")
		ApplicationContext appCon = new ClassPathXmlApplicationContext("applicationContext.xml");
		OneToOneController oto = (OneToOneController)appCon.getBean("oneToOneController"); oto.test(); }}Copy the code

The final output

The Person [id = 1, name = Chen, age = 88, card = Idcard [id = 1, code = 123456789123456789]] = = = = = = = = = = = = = = = = = = = = = = = the Person Id = 1, name = Chen, age = 88, card = Idcard [id = 1, code = 123456789123456789]] = = = = = = = = = = = = = = = = = = = = = = = the Person [id = 1, name = Chen, age = 88, code = 123456789123456789]Copy the code

2.6.2 A pair of multi-level connections

A user can have multiple orders. The processing process of one-to-many query (to query information about users and their associated orders based on user IDS) is as follows.

1. Set up the PO/POJO

Product.java

package com.po;
public class Product {
	private Integer id;
	private String name;
	private Double price;
	private List<Orders> orders;
	Getters and setters and toString
}
Copy the code

MyUser.java

package com.po;
public class MyUser {
	private Integer uid;/ / the primary key
	private String uname;
	private String usex;
	// One-to-many associative query, user associated order
	private List<Orders> ordersList;
	Getters and setters and toString
}
Copy the code

Orders.java

package com.po;
public class Orders {
	private Integer id;
	private  String ordersn;
	private List<Product> products;
        Getters and setters and toString
}
Copy the code

SelectUserOrdersById.java

package com.pojo;
public class SelectUserOrdersById {
	private Integer uid;
	private String uname;
	private String usex;
	private Integer id;
	private String ordersn;
	Getters and setters and toString
}
Copy the code

2. Establish Dao layer interface to provide database operation methods

OrdersDao.java

package com.dao;
@Repository("ordersDao")
@Mapper
public interface OrdersDao {
	public List<Orders> selectOrdersById(Integer uid);
	public List<Orders> selectallOrdersAndProducts(a);
}
Copy the code

UserDao.java

package com.dao;
@Repository("userDao")
@Mapper

public interface UserDao {
	public MyUser selectUserOrdersById1(Integer uid);
	public MyUser selectUserOrdersById2(Integer uid);
	public List<SelectUserOrdersById> selectUserOrdersById3(Integer uid);
}
Copy the code

3. Compile a mapping file to provide SQL statement mapping rules and cascading rules

OrdersMapper.xml


      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.OrdersDao">
	<! -- query order information based on user uid -->
	<select id="selectOrdersById" parameterType="Integer" resultType="com.po.Orders">
		select * from orders where user_id=#{id}
	</select>
</mapper>
Copy the code

UserMapper.xml


      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.UserDao">


	<! Query a user and his/her order information based on uid:
	<resultMap type="com.po.MyUser" id="userAndOrders1">
		<id property="uid" column="uid"/>
		<result property="uname" column="uname"/>
		<result property="usex" column="usex"/>
		<! Select * from selectOrdersById; select * from selectOrdersById;
		<collection property="ordersList" ofType="com.po.Orders" column="uid" 
		 select="com.dao.OrdersDao.selectOrdersById"/>
	</resultMap>
	<select id="selectUserOrdersById1" parameterType="Integer" resultMap="userAndOrders1">
		select * from user where uid = #{id}
	</select>
        
        
        
	<! Query user and order information based on uid
	<resultMap type="com.po.MyUser" id="userAndOrders2">
		<id property="uid" column="uid"/>
		<result property="uname" column="uname"/>
		<result property="usex" column="usex"/>
		<! Set ofType = element type of set
		<collection property="ordersList" ofType="com.po.Orders" >
			<id property="id" column="id"/>
			<result property="ordersn" column="ordersn"/>
		</collection>
	</resultMap>
	<select id="selectUserOrdersById2" parameterType="Integer" resultMap="userAndOrders2">
		select u.*,o.id,o.ordersn from user u, orders o where u.uid = o.user_id and u.uid=#{id}
	</select>
        
        
        
	<! Query a user and its associated order information based on the UID.
	<select id="selectUserOrdersById3" parameterType="Integer" resultType="com.pojo.SelectUserOrdersById">
		select u.*,o.id,o.ordersn from user u, orders o where u.uid = o.user_id and u.uid=#{id}
	</select>
</mapper>
Copy the code

4. Configure mybatis-config. XML, specify the location of the mapping file, and enable loading on demand

<mappers><! Mapper, tell MyBatis where to find the mapping file.
   <mapper resource="com/mybatis/UserMapper.xml" />
   <mapper resource="com/mybatis/OrdersMapper.xml" />
</mappers>
Copy the code

5. Configure application. XML, including configuring MyBatis and enabling scan

6. Control layer

package com.controller;
@Controller("oneToMoreController")
public class OneToMoreController {
	@Autowired
	private UserDao userDao;
	public void test(a) {
		// Query a user and order information
		MyUser auser1 = userDao.selectUserOrdersById1(1);
		System.out.println(auser1);
		System.out.println("= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =");
		MyUser auser2 = userDao.selectUserOrdersById2(1);
		System.out.println(auser2);
		System.out.println("= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =");
		List<SelectUserOrdersById> auser3 = userDao.selectUserOrdersById3(1);
		System.out.println(auser3);
		System.out.println("= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = ="); }}Copy the code

7. Test

package com.controller;
public class TestOneToMore {
	public static void main(String[] args) {
		ApplicationContext appCon = new ClassPathXmlApplicationContext("applicationContext.xml");
		OneToMoreController otm = (OneToMoreController)appCon.getBean("oneToMoreController"); otm.test(); }}Copy the code

The output

User [uid=1,uname= 3,usex= female,ordersList=[Orders [id=1, orderSn =999999]null, The Orders [id = 2, ordersn = 88888] null]] = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = User [uid = 1, uname = zhang SAN, usex = female, ordersList = [the Orders [id=1,ordersn=999999]null, Orders [id=2,ordersn=88888]null]] =================================== [User [uid=1,uname= 3, USex = female, OID =1,ordersn=999999], User [uid = 1, uname = zhang SAN, usex = female and oid = 2, ordersn = 88888]] = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =Copy the code

2.6.3 Multi-pair multi-level connection

The basic process is similar to both of these

<! -- Many-to-many associative query all orders and the corresponding product information of each order (nested result) -->
<resultMap type="com.po.Orders" id="allOrdersAndProducts">
	<id property="id" column="id"/>
	<result property="ordersn" column="ordersn"/>
	<! -- Many-to-many association -->
	<collection property="products" ofType="com.po.Product">
		<id property="id" column="pid"/>
		<result property="name" column="name"/>
		<result property="price" column="price"/>
	</collection>
</resultMap>
<select id="selectallOrdersAndProducts" resultMap="allOrdersAndProducts">
	select o.*,p.id as pid,p.name,p.price
	from orders o,orders_detail od,product p
	where od.orders_id = o.id 
	and od.product_id = p.id
</select>
Copy the code

Dynamic SQL

3.1 `<if>`The element

Similar to the if statement in Java

Note the where 1 = 1

<! SQL > select * from user where user = >
<select id="selectUserByIf"  resultType="com.po.MyUser" parameterType="com.po.MyUser">
        select * from user where 1=1        
    <if test="uname ! =null and uname! = "">
            and uname like concat('%',#{uname},'%')
        </if>
    <if test="usex ! =null and usex! = "">
            and usex = #{usex}
        </if>
</select>
Copy the code

3.2 `<choose>`,`<when>`,`<otherwise>`The element

Like the Switch statement in Java

<! Select select, WHEN, otherwise;
<select id="selectUserByChoose"  resultType="com.po.MyUser" parameterType="com.po.MyUser">
        select * from user where 1=1        
    <choose>
        <when test="uname ! =null and uname! = "">
            and uname like concat('%',#{uname},'%')
        </when>
        <when test="usex ! =null and usex! = "">
            and usex = #{usex}
        </when>
        <otherwise>
            and uid > 10
        </otherwise>
    </choose>
</select>
Copy the code

3.3 `<trim>`,`<where>`,`<set>`The element

The
element prefixes its contents with prefix or suffix. PrefixOverrides and suffixOverrides override some content at the beginning or end of the content.

In the example, the prefix where is added. If the content is preceded by and or or, prefixOverrides will remove it

<! Use trim element to dynamically query user information based on conditions -->
<select id="selectUserByTrim"  resultType="com.po.MyUser" parameterType="com.po.MyUser">
        select * from user      
    <trim prefix="where" prefixOverrides="and |or">
        <if test="uname ! =null and uname! = "">  
                and uname like concat('%',#{uname},'%')
        </if>
        <if test="usex ! =null and usex! = "">  
                and usex = #{usex} 
        </if>
    </trim>
</select>
Copy the code

The < WHERE > element outputs a WHERE statement, regardless of what the conditional output is.

If all the criteria are not met, MyBatis will retrieve all the records.

MyBatis ignores output that starts with and or or;

Whitespace is not needed in the < WHERE > element.

<! Select * from 'where';
<select id="selectUserByWhere"  resultType="com.po.MyUser" parameterType="com.po.MyUser">
        select * from user      
    <where>
        <if test="uname ! =null and uname! = "">
                and uname like concat('%',#{uname},'%')
            </if>
        <if test="usex ! =null and usex! = "">
                and usex = #{usex}
            </if>
    </where>
</select>
Copy the code

In dynamic UPDATE statements, columns are dynamically updated with

elements. The

tag acts as a set keyword and automatically removes the last “, “from the concatenated string.

update user  set uname=#{uname} where uid = #{uid} 
Copy the code

<! Use the set element to dynamically modify a user.
<update id="updateUserBySet" parameterType="com.po.MyUser">
        update user     
    <set>
        <if test="uname ! = null">uname=#{uname},</if>
        <if test="usex ! = null">usex=#{usex}</if>
    </set>
        where uid = #{uid}  
</update>
Copy the code

3.4 `<foreach>`The element

The

element is used primarily to build in conditions, which allow you to iterate over a collection in an SQL statement.

The main attributes of foreach element are item, index, collection, open, separator, and close.

Item represents the alias of each element in the collection as it iterates,

Index specifies a name for the position reached during each iteration,

Open indicates what the statement starts with,

Separator indicates what symbol to use as the separator between each iteration.

Close means what to end with.

The collection attribute is mandatory and has three main values:

When a single argument is passed and the argument type is a List, the collection property value is List.
When a single argument is passed and the argument type is array, the collection property value is array.
When multiple parameters are passed in, they need to be encapsulated into a Map. Of course, a single parameter can also be encapsulated into a Map. The Map key is the parameter name, and the collection property value is the key of the List or array object in the encapsulated Map.

<! Select * from user where user = 'foreach';
<select id="selectUserByForeach" resultType="com.po.MyUser"  parameterType="List">
        select * from user where uid in     
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>
Copy the code

3.5 `<bind>`The element

In fuzzy queries, concatenating strings with “${}” does not prevent SQL injection problems.

Function or the joining together of different database connection symbol is different, such as concat function of MySQL, Oracle connection symbol “| |”. More troublesome, and not conducive to the migration of code.

MyBatis provides meta elements to solve this problem.

Previous fuzzy query methods:

% represents any character or characters. Can match characters of any type and length. Such as

select * from user where name like "%zhang%"
Copy the code

In practice, conditions are passed in as parameters. So we use the concatenation () function, concat(str1,str2,str3,str4…). ; Str1str2str3str4…

<select id="findByUsername" parameterType="String" resultType="user">
    SELECT * FROM user WHERE username LIKE concat('%',#{username},'%')
</select>
Copy the code

The introduction of the bind element

<! Fuzzy query using the bind element -->
<select id="selectUserByBind" resultType="com.po.MyUser"  parameterType="com.po.MyUser">
    <! Bind uname = com.po.myuser
    <bind name="paran_uname" value="'%' + uname + '%'"/>
        select * from user where uname like #{paran_uname}  
</select>
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Java EE SSM framework integration development (seven) MyBatis configuration file, mapper, dynamic SQL

MyBatis configuration file

mapper

2.1 select

2.1.1 Transferring Parameters using Map

2.1.2 Using JavaBean to Transmit Parameters

2.2 insert

2.3 update and delete

2.4 SQL

2.5 resultMap

2.5.1 Using Map to Store Results

2.5.2 Using POJOs to Store Results

2.6 Cascading Query

2.6.1 A Pair of First-level connections

2.6.2 A pair of multi-level connections

2.6.3 Multi-pair multi-level connection

Dynamic SQL

3.1 `<if>`The element

3.2 `<choose>`,`<when>`,`<otherwise>`The element

3.3 `<trim>`,`<where>`,`<set>`The element

3.4 `<foreach>`The element

3.5 `<bind>`The element

Java EE SSM framework integration development (seven) MyBatis configuration file, mapper, dynamic SQL

MyBatis configuration file

mapper

2.1 select

2.1.1 Transferring Parameters using Map

2.1.2 Using JavaBean to Transmit Parameters

2.2 insert

2.3 update and delete

2.4 SQL

2.5 resultMap

2.5.1 Using Map to Store Results

2.5.2 Using POJOs to Store Results

2.6 Cascading Query

2.6.1 A Pair of First-level connections

2.6.2 A pair of multi-level connections

2.6.3 Multi-pair multi-level connection

Dynamic SQL

3.1 <if>The element

3.2 <choose>,<when>,<otherwise>The element

3.3 <trim>,<where>,<set>The element

3.4 <foreach>The element

3.5 <bind>The element

Related Posts

This section describes how to submit changes to Git

Some guidelines for pit detour in CronJob controllers

Detailed! Matlab2016b Ubuntu16.04 LTS installed! (Reedited edition, 2018.4)

3.1 `<if>`The element

3.2 `<choose>`,`<when>`,`<otherwise>`The element

3.3 `<trim>`,`<where>`,`<set>`The element

3.4 `<foreach>`The element

3.5 `<bind>`The element