[long article] spring learning notes (7): mybatis mapper + dynamic SQL

Time:2021-5-29

1 Overview

This article mainly describes how to useMyBatisMapper and dynamicSQLConfiguration of.

2 MyBatisProfile overview

MyBatisThe main properties of the configuration file are as follows:

  • <settings>: related settings, key value pair form
  • <typeAliases>: type alias
  • <typeHandlers>: type processor
  • <objectFactory>: object factory
  • <plugins>: plug in, including several<plugin>
  • <environments>: environment configuration, including several<environment>, in<environment>Transaction manager can be specified in<transactionManager>And data sources<dataSource>
  • <databaseIdProvider>: database vendor identification
  • <mappers>: mapper, including several<mapper>

Note that the order should not be reversed, otherwise an exception will occur during startup.

3 preparation steps

Because most of the code in this paper only gives the key statements without a complete project, so if you want to implement it once, pleasecloneCode here(KotlinpleasecloneHere), and:

  • utilizeresources/sqlCreate database and data table, and insert corresponding data
  • modifyMyBatisSpringdhcp2Etc. to the latest version and modifyMySQLThe driver is the corresponding version
  • modifyapplicationContext.xmlFile database user name, password and databaseURL, you may need to modify the driver
  • Start the database service, test and runMainTestNormally, the following results will appear:

[long article] spring learning notes (7): mybatis mapper + dynamic SQL

4 mapper overview

MyBatisThe mapper of is added by an interfaceXMLThe mapping file is the most complex component. The common elements of the mapping file are as follows:

  • <select>: query statement
  • <insert>/<update>/<delete>insert/to update/deleteStatement, which returns the number of rows affected by the operation. For example, if two rows are inserted and the operation is successful, the number of rows affected is two, and the integer 2 is returned
  • <sql>: CustomSQL
  • <resultMap>: provide mapping rules

Let’s take a look at the most commonly used<select>

4.1 <select>

Example (inmapper/UserDao.xmlYou can add it directly:

<select id="selectById" parameterType="Integer" resultType="pers.init.entity.User">
    select * from user where id = #{id}
</select>

amongidIs a unique identifier. Accept oneInteger, backcom.pojo.UserObject, the result set is automatically mapped to thecom.pojo.UserIn the middle.

The common attributes are as follows:

  • id<select>Globally unique identifier of the statement
  • paramterType: means incomingSQLThe fully qualified name or alias of the parameter type of the statement, which can be automatically inferred
  • resultType: ExecutionSQLType returned after
  • resultMap: andresultTypesimilar,resultTypeThe default one-to-one mapping, for example, the table field name isidIs mapped to theidIn the middle, andresultMapYou need to define the mapping relationship manually, so that theidMapping to an entity classid1, orid2, orid3resultTypeAndresultMapBoth need to specify one, can not exist at the same time
  • flushCache: set callSQLIs it required after operationMyBatisClear the local cache and secondary cache of the previous query. The default value is 0false
  • useCache: start L2 cache, defaulttrue
  • timeout: timeout parameter, in seconds
  • fetchSize: get the total number of records
  • statementType: which one to useJDBCOfStatement, the value can beSTATEMENT/PREPARED/CALLABLE, respectivelyStatement/PreparedStatement/CallableStatement
  • resultSetType: forJDBCOfResultSet, which can be set toFORWARD_ONLY/SCROLL_SENSITIVE/SCROLL_INSENSITIVE, respectivelyForward access only/Two way scrolling, not updated in time/Two way scrolling, timely update

And modify itUserDao, add oneselectByIdmethod:

User selectById(Integer id);

It can be tested directly

@Test
public void selectById()
{
    System.out.println(dao.selectById(1));
}

Let’s look at how to pass multiple parameters.

4.2 transfer parameters

With the most basicselectAfter that, pass it onidThis kind of single parameter is very easy, but in practice, many times you need to pass multiple parameters,MyBatisThere are two ways to pass multiple parameters in

  • adoptMaptransmit
  • adoptJavaBeantransmit

4.2.1 Map

have access toMapPassing multiple parameters, example<select>As follows:

<select id="selectByMap" resultType="pers.init.entity.User" parameterType="map">
    select * from user where name like concat('%', #{name}, '%') and age = #{age}
</select>

Parameter namenameas well asageyesMapThe key for.

And then in theUserDaoAdd below:

User selectByMap(Map<String,String> map);

Then use it in the main classMapAdd key value pair:

@Test
public void selectByMap()
{
    Map<String,String> map = new HashMap<>();
    map.put("name","111");
    map.put("age","33");
    System.out.println(dao.selectByMap(map));
}

In this way, multiple parameters can be passed to query.

4.1.2 useJavaBean

Another way to pass multiple parameters is to use theJavaBeanPass, create aPOJOClass:

@Getter
@Setter
@Builder
@ToString
public class UserPOJO {
    private String name;
    private Integer age;
}

modifyUserDaoInterface method:

public User selectByPOJO(UserPOJO user)

Next, modify the mapping file, which is actually modifiedparameterTypeThen:

<select id="selectByPOJO" resultType="pers.init.entity.User" parameterType="pers.init.pojo.UserPOJO">
    select * from user where name like concat('%', #{name}, '%') and age = #{age}
</select>

Note that when accessing the passed parameters, use thePOJOClass, without adding a similar nameUserPOJO.The prefix of.

Finally, the test is carried out

@Test
public void selectByPOJO()
{
    UserPOJO pojo = UserPOJO.builder().age(33).name("111").build();
    System.out.println(dao.selectByPOJO(pojo));
}

4.2 <insert>

<insert>For insertion, most properties are similar to<select>The following are some unique properties:

  • keyProperty: assign the return value of the insert operation to thePOJOClass
  • keyColumn: used to set the position of the primary key column. This parameter needs to be set when the first column in the table is not the primary key. The union primary key can be separated by commas
  • useGeneratedKeys: useJDBCOfgetGeneratedKeysGet the primary key generated inside the database. The default isfalse

For example, the typical primary key backfill<insert>As follows:

<insert id="insertUser1" parameterType="pers.init.entity.User" keyProperty="id" useGeneratedKeys="true">
    insert into user(name, age) values (#{name}, #{id})
</insert>

In this way, the self increasing primary key generated by the database will be used to backfill to the databaseUserOfidAttribute,UserDaoThe interface is as follows:

int insertUser1(User user);

Generally speaking, the insert operation returns an integer indicating the number of rows affected by the operation, so you can set the return value tointThe tests are as follows:

@Test
public void insertUser1()
{
    User user = User.builder().age((short) 88).name("test1").build();
    System.out.println(dao.insertUser1(user));
    System.out.println(user.getId());
}

In addition, if autoincrement primary key is not supported, you can useselectKeyGenerate primary key by customization, for example:

<insert id="insertUser2" parameterType="pers.init.entity.User">
    <selectKey keyProperty="id" resultType="integer" order="BEFORE">
        select if(max(id) is null,1,max(id)+1) as newId from user
    </selectKey>
    insert into user(id,name,age) values(#{id},#{name},#{age})
</insert>

<selectKey>InkeyPropertyNew primary key specifiednewIdReturn topers.pojo.UserOfidProperties,orderSet the execution order,BEFORE/AFTERIndicates execution<selectKey>Execute the insert statement after / before.

Test:

@Test
public void insertUser2()
{
    User user = User.builder().age((short) 10).name("test2").build();
    System.out.println(dao.insertUser2(user));
    System.out.println(user.getId());
}

4.3 <update>/<delete>

Returns an integer with the property and<insert>/<select>Similarly, a simple example is as follows:

<update id="updateUser" parameterType="pers.init.entity.User">
    update user set name=#{name}, age=#{age} where id = #{id}
</update>

<delete id="deleteUser" parameterType="Integer">
    delete from user where id = #{id}
</delete>

In the same wayupdate/deleteReturns an integer representing the number of rows affected by the operation, so setUserDaoThe interface is as follows:

int updateUser(User user);
int deleteUser(Integer id);

Test:

@Test
public void updateUser()
{
    User user = User.builder().id(3).name("3333333").age((short)11).build();
    selectAll();
    System.out.println(dao.updateUser(user));
    selectAll();
}

@Test
public void deleteUser()
{
    selectAll();
    System.out.println(dao.deleteUser(3));
    selectAll();
}

4.4 <sql>

Used to defineSQLPart of to facilitate the backSQLStatement references, such as:

<sql id="column">
    id,name,age
</sql>
<select id="selectBySqlColumn" resultType="pers.init.entity.User">
    select <include refid="column"/> from user
</select>

UserDaoInterface:

List<User> selectBySqlColumn();

Test:

@Test
public void selectBySqlColumn()
{
    System.out.println(dao.selectBySqlColumn());
}

5 <resultMap>

It’s been raised,<resultMap>than<resultType>To be powerful, but need to manually define the mapping relationship, a common one<resultMap>As follows:

<resultMap type="package1.package2.package3.POJO" id="resultMapId">
    <constrcutor>                       <!-- When instantiating, inject the result into the construction method -- >
        <idArg />                       <!-- ID parameter -- >
        <arg />                         <!-- Common parameter -- >
    </constrcutor>
    <id />                              <!-- Indicates which column is the primary key -- >
    <result />                          <!-- Common result injected into field / JavaBean property -- >
    <association property="">           <!-- One to one correlation -- >
    <collection property="">            <!-- One to many association -- >
    <discriminator javaType="">         <!-- Use the result value to determine which result mapping -- >
        <case value="">                 <!-- Result mapping based on some values -- >
    </discriminator>
</resultMap>

5.1 useMap

querySQLThe results can be usedMap/POJOStorage, useMapStorage does not need to be written manually<resultMap>, the default table property name is the key of the key value pair:

<select id="selectReturnMap" resultType="Map">
    select * from user
</select>

availableList<Map>To receive the returned result, a record is mapped to aMapObject,MapInkeyyesselectThe field name of the.

SampleUserDaoThe method is as follows

List<Map<String,Object>> selectReturnMap();

amongMapType isMap<String,Object>The test method is as follows:

 @Test
public void selectReturnMap()
{
    dao.selectReturnMap().forEach(System.out::println);
}

5.2 usePOJO

If usedPOJOWhen storing returned objects, you need to define aPOJOClass, you can use theUserPOJOAdd one to the baseidProperties:

@Getter
@Setter
@Builder
@ToString
public class UserPOJO {
    private Integer id;
    private String name;
    private Integer age;
}

Then write the mapping file

<resultMap id="testPOJO" type="pers.init.pojo.UserPOJO">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="age" column="age"/>
</resultMap>

amongpropertyappointPOJOThe properties of the,columnIs the name of the field in the table, and<select>Use, specifyresultMapCorresponding toid

<select id="selectReturnPOJO" resultMap="testPOJO">
    select * from user
</select>

The returned results can be usedList<UserPOJO>receive:

List<UserPOJO> selectReturnPOJO();

Test method:

@Test
public void selectReturnPOJO()
{
    dao.selectReturnPOJO().forEach(System.out::println);
}

6 cascade query

Cascading query is to use the relationship between primary key and foreign key to conduct combined query, such as tableAThe table is referenced by a foreign key ofBA primary key of, queryAThrough theAThe foreign key ofBThis is cascade query. There are three common cascading queries

  • one-on-one
  • One to many
  • Many to many

MyBatisIt supports one-to-one and one to multi-level connection, but it does not support multi to multi-level connection, but it can realize multi to multi-level connection with multiple one to multi-level connections. Let’s take a look at it.

6.1 one to one

One to one cascading query is the most common cascading query<resultMap>In<association>For configuration, the following properties are commonly used:

  • property: object properties mapped to the entity class
  • column: Specifies the corresponding field in the table
  • javaType: Specifies the type mapped to the entity object property
  • select: Specifies the child to introduce nested queriesSQLStatement for nested queries in association mapping

The following is an example, which is divided into five steps:

  • Create data table
  • Create entity class
  • Write mapping file
  • Modify persistence layer interface
  • Add test method

6.1.1 data sheet

For the convenience of adding new tables and data together:

use test;

drop table if exists idcard;
drop table if exists person;

create table idcard(
    id int(10) primary key auto_increment,
    code char(18) collate utf8mb4_unicode_ci default null
);

create table person(
    id int(10) primary key,
    name varchar(20) collate utf8mb4_unicode_ci default null,
    age smallint default null,
    idcard_id int(10) default null,
    key idcard_id(idcard_id),
    constraint idcard_id foreign key (idcard_id) references idcard(id)
);

insert into idcard(`code`) values('123456789123456789');

insert into person(`id`,`name`,`age`,`idcard_id`) values (1,'111',22,1);

6.1.2 entity class

@Data
public class IdCard {
    private Integer id;
    private String code;
}

@Data
public class Person {
    private Integer id;
    private String name;
    private Integer age;
    private IdCard card;
}

In addition, you need to create a mapping resultPOJOClass:

@Data
public class PersonPOJO {
    private Integer id;
    private String name;
    private Short age;
    private String code;
}

6.1.3 mapping file

The mapping file is divided into two parts

  • IdCardMapper.xml
  • PersonMapper.xml

First of allIdCardMapper.xml, plus one<select>That’s it. Pay attentionnamespaceFill in the correct position of, corresponding todaoThe location of the car.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pers.oneToOne.dao.IdCardDao">
    <select id="selectCodeById" parameterType="Integer" resultType="pers.oneToOne.entity.IdCard">
        select * from idcard where id = #{id}
    </select>
</mapper>

The second isPersonMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pers.oneToOne.dao.PersonDao">
    <resultMap id="personMap1" type="pers.oneToOne.entity.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <association property="card" column="idcard_id" javaType="pers.oneToOne.entity.IdCard"
                     select="pers.oneToOne.dao.IdCardDao.selectCodeById"/>
    </resultMap>
    <select id="selectPersonById1" parameterType="Integer" resultMap="personMap1">
        select *
        from person
        where id = #{id}
    </select>

    <resultMap id="personMap2" type="pers.oneToOne.entity.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <association property="card" javaType="pers.oneToOne.entity.IdCard">
            <id property="id" column="idcard_id"/>
            <result property="code" column="code"/>
        </association>
    </resultMap>

    <select id="selectPersonById2" parameterType="Integer" resultMap="personMap2">
        select p.*, ic.code
        from person p,
             idcard ic
        where p.idcard_id = ic.id
          and p.id = #{id}
    </select>

    <select id="selectPersonById3" parameterType="Integer" resultType="pers.oneToOne.pojo.PersonPOJO">
        select p.*, ic.code
        from person p,
             idcard ic
        where p.idcard_id = ic.id
          and p.id = #{id}
    </select>
</mapper>

First, first<resultMap>Specify firstidAnd so on<association>

  • propertyIs an entity class property. Note that the type isIdCard
  • columnIs a table field name of typeint(10)
  • javaTypeIt’s through the backselectThe return type can be interpreted as yespropertyThe type ofIdCard
  • selectSpecifies the method used by nested queriesSQL, corresponding toIdCardDao.xmlInselectCodeById

And then in a<select>InresultMapSpecify themapOfidThat’s it. This method is used twiceSQL

  • Once it wasselect * from person where id=?
  • Once it wasselect * from idcard where id=?

Finally, you can get the whole result and start debugging. You can find that you have actually executed two itemsSQL

[long article] spring learning notes (7): mybatis mapper + dynamic SQL

And the second one<resultMap>In, in<association>There’s no more in itselectProperty to map the result directly to theSelectPersonByIdThis is the implementation of oneSQLResult of statement:

select p.*,ic.code from person p,idcard ic where p.idcard_id = ic.id and p.id=#{id}

The actual query is as follows:

[long article] spring learning notes (7): mybatis mapper + dynamic SQL

If necessary, it can be configured as<resultMap>For example:

<association property="card" resultMap="resultMap" />
<resultMap id="resultMap">
    <id property="id" column="idcard_id"/>
    <result property="code" column="code"/>
</resultMap>

And the last one<select>Is a connection query, no additional<resultMap>The actual implementation is as follows:

[long article] spring learning notes (7): mybatis mapper + dynamic SQL

6.1.4 DaoInterface

This is simple:

public interface PersonDao {
    Person selectPersonById1(Integer id);
    Person selectPersonById2(Integer id);
    PersonPOJO selectPersonById3(Integer id);
}

6.1.5 testing

@Test
public void selectPersonById()
{
    System.out.println(dao.selectPersonById1(1));
    System.out.println(dao.selectPersonById2(1));
    System.out.println(dao.selectPersonById3(1));
}

Note that the configuration file needs to be modified before testingmybatis-config.xml

<configuration>
    <settings>
        <!-- Delayed loading -- >
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- Load on demand -- >
        <setting name="aggressiveLazyLoading" value="true"/>
        <!-- Debug the switch and print the executed SQL -- >
<!--        <setting name="logImpl" value="STDOUT_LOGGING"/>-->
    </settings>
    <mappers>
        <!--initMapper-->
        <mapper resource="mapper/init/UserMapper.xml" />
        <mapper resource="mapper/init/TestMapper.xml" />
        <!-- One to one cascade mapper -- >
        <mapper resource="mapper/oneToOne/PersonMapper.xml" />
        <mapper resource="mapper/oneToOne/IdCardMapper.xml" />
    </mappers>
</configuration>

The first two<setting>It means to turn on delay loading and on-demand loading. The next one is to set the debug switch, and the last one is in the following<mappers>add<mapper>CorrespondingxmlThe location of the car.

One thing to note is that<settings>Need to write in<mappers>It’s in front of me.

In addition, becauseDaoThe interface is not added@MapperAnnotation, so you need toapplicationContext.xmlIn the manual plusDaoLocation:

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="pers.oneToOne.dao"/>
    <property name="sqlSessionFactoryBeanName" value="sql.SessionFactory"/>
</bean>

Test results:

[long article] spring learning notes (7): mybatis mapper + dynamic SQL

6.2 one to many

One to many cascading query is similar to one-to-one processing, mainly in the mapping file<collection>Configuration, the example is the same as above, divided into five steps.

6.2.1 data sheet

Two tables are required:

  • user
  • order

userYou can use the previous oneuserWatch, andorderThe table is as follows:

use test;
drop table if exists orders;

create table orders(
    id int(10) primary key auto_increment,
    ordersn varchar(10) collate utf8mb4_unicode_ci default null,
    user_id int(10) default null,
    key user_id(user_id),
    constraint user_id foreign key (user_id) references user(id)
);

insert into orders(`ordersn`,`user_id`) values ('testorder1',1),('testorder2',1),('testorder3',1);

6.2.2 entity class

Add entity classOrders

@Data
public class Orders {
    private Integer id;
    private String ordersn;
}

Create a band at the same timeOrdersOfUser

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserWithOrders {
    private Integer id;
    private String name;
    private Short age;
    private List<Orders> ordersList;
}

6.2.3 mapping file

Two:

  • OrdersMapper.xml
  • UserWithOrdersMapper.xml

First of allOrdersMapper.xmlThere’s only one simple one<select>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pers.oneToMany.dao.OrdersDao">
    <select id="selectOrdersById" parameterType="Integer" resultType="pers.oneToMany.entity.Orders">
        select * from orders where user_id=#{id}
    </select>
</mapper>

And then there isUserWithOrdersMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pers.oneToMany.dao.UserWithOrdersDao">

    <resultMap id="userAndOrder1" type="pers.oneToMany.entity.UserWithOrders">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <collection property="ordersList" ofType="pers.oneToMany.entity.Orders" column="id" select="pers.oneToMany.dao.OrdersDao.selectOrdersById"/>
    </resultMap>
    <select id="selectUserOrders1" parameterType="Integer" resultMap="userAndOrder1">
        select * from user where id=#{id}
    </select>

    <resultMap id="userAndOrder2" type="pers.oneToMany.entity.UserWithOrders">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <collection property="ordersList" ofType="pers.oneToMany.entity.Orders">
            <id property="id" column="id" />
            <result property="ordersn" column="ordersn"/>
        </collection>
    </resultMap>
    <select id="selectUserOrders2" parameterType="Integer" resultMap="userAndOrder2">
        select u.*,o.id,o.ordersn from user u,orders o where u.id = o.user_id and u.id = #{id}
    </select>

    <select id="selectUserOrders3" parameterType="Integer" resultType="pers.oneToMany.pojo.UserOrdersPOJO">
        select u.*,o.id,o.ordersn from user u,orders o where u.id = o.user_id and u.id = #{id}
    </select>
</mapper>

Compared with one-on-one cascading, the key change is one of them<collection>The important attributes are as follows:

  • property: Specifies the property field of the entity class
  • ofType: Specifies the type in the collection
  • column: which values are passed toselectMethods in
  • select: nested query statements

the second<collection>Similarly, map the results of the query directly to theOrdersAbove. The last is to use join queries directly.

6.2.4 DaoInterface

public interface OrdersDao {
    List<Orders> selectOrdersById(Integer id);
}

public interface UserWithOrdersDao {
    UserWithOrders selectUserOrders1(Integer id);
    UserWithOrders selectUserOrders2(Integer id);
    List<UserOrdersPOJO> selectUserOrders3(Integer id);
}

6.2.5 testing

@Test
public void selectUserOrders()
{
    System.out.println(dao.selectUserOrders1(1));
    System.out.println(dao.selectUserOrders2(1));
    System.out.println(dao.selectUserOrders3(1));
}

6.3 many to many

MyBaitsIn fact, multi pair multi-level association is not supported, but it can be realized through multiple one pair multi-level associations. For example, an order corresponds to multiple goods, and a commodity corresponds to multiple orders. In this way, the two are multi pair multi-level association relations. In this way, using an intermediate table, it can be converted into two one to many relationships.

The following is also through five steps to achieve multi-level connection.

6.3.1 data sheet

We need order table, commodity table and an intermediate tableOrdersIt has been created before. Here you only need to create two tables:

use test;

create table product(
    id int(10) primary key auto_increment,
    name varchar(10) collate utf8mb4_unicode_ci default null,
    price double default null
);

create table orders_detail(
    id int(10) primary key auto_increment,
    orders_id int(10) default null,
    product_id int(10) default null,
    key orders_id(orders_id),
    key product_id(product_id),
    constraint orders_id foreign key (orders_id) references orders(id),
    constraint product_id foreign key (product_id) references product(id)
);

insert into product(`name`,`price`) values('product1',1.1),('product2',2.2),('product3',3.3);
insert into orders_detail(`orders_id`,`product_id`) values(1,1),(1,2),(1,3),(2,1),(2,3);

6.3.2 entity class

The order class can follow the previous one, only two entity classes are needed:

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Product {
    private Integer id;
    private String name;
    private Double price;
    private List<Orders> orders;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class OrdersWithProduct {
    private Integer id;
    private String ordersn;
    private List<Product> products;
}

6.3.3 mapping file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pers.manyToMany.dao.OrdersWithProductDao">
    <resultMap id="ordersAndProduct" type="pers.manyToMany.entity.OrdersWithProduct">
        <id property="id" column="id"/>
        <result property="ordersn" column="ordersn"/>
        <collection property="products" ofType="pers.manyToMany.entity.Product">
            <id property="id" column="pid"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>
    <select id="selectOrdersAndProduct" resultMap="ordersAndProduct">
        select o.*,p.id as pid ,p.name,p.price from orders o,orders_detail od, product p where o.id = od.orders_id and od.product_id = p.id
    </select>
</mapper>

In this case, many to many connections are made by specifying different connections each timeOrdersIdTo query the correspondingProductTo achieve, it is divided into multiple pairs of multi-level connection.

6.3.4 DaoInterface

public interface OrdersWithProductDao {
    List<OrdersWithProduct> selectOrdersAndProduct();
}

6.3.5 testing

@Test
public void test()
{
    ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
    OrdersWithProductDao dao = context.getBean(OrdersWithProductDao.class);
    System.out.println(dao.selectOrdersAndProduct());
}

7 newsSQL

Finally, let’s take a look at the newsSQL, dynamicSQLManual splicing can be avoidedSQLStatement, such as adding when certain conditions holdand xxx=xxxxAnd so on. Let’s take a look at the most commonly used<if>

7.1 <if>

<if>similarJavaInifThe simplest example is as follows:

<select id="selectByIf" parameterType="Integer" resultType="pers.init.entity.User">
    select * from user where 1=1
    <if test="name != null and name != ''">
        and name like concat('%',#{name},'%')
    </if>
    <if test="age != null and age>0">
        and age = #{age}
    </if>
</select>

That is to say, whentestWhen the condition in is true, it is addedand xxxsentence. be carefultestThis property is<if>It is necessary and cannot be omitted.

(note a key one is used here1=1For illustration only, do not use in actual development1=1(splicing)

DaoInterface:

List<User> selectByIf(User user);

Test:

@Test
public void testIf()
{
    System.out.println(dao.selectByIf(User.builder().age((short) 33).name("111").build()));
}

7.2 <choose>+<when>+<otherwise>

similarJavaInswitchsentence:

  • <choose>similarswitch
  • <when>similarcase
  • <otherwise>similardefault

When one of them<when>When it is established, the statement ends, similar to automatically adding"break"

Example:

<select id="selectByChoose" parameterType="Integer" resultType="pers.init.entity.User">
    select * from user where 1=1
    <choose>
        <when test="name != null and name != ''">
            and name like concat('%',#{name},'%')
        </when>
        <when test="age != null and age>0">
            and age=#{age}
        </when>
        <otherwise>
            and id != 3
        </otherwise>
    </choose>
</select>

DaoInterface:

List<User> selectByChoose(User user);

Test:

@Test
public void testChoose()
{
    System.out.println(dao.selectByChoose(User.builder().age((short)33).build()));
}

7.3 <trim>

Main functions:

  • Prefix
  • suffix
  • Replace some head / tail content

Here is an example to use<trim>To achieve<where>For example:

<select id="selectByTrim" parameterType="Integer" resultType="pers.init.entity.User">
    select * from user
    <trim prefix="where" prefixOverrides="and">
        <if test="name != null and name != ''">
            and name like concat('%',#{name},'%')
        </if>
    </trim>
</select>

DaoInterface:

List<User> selectByTrim(User user);

Test:

@Test
public void testTrim()
{
    System.out.println(dao.selectByTrim(User.builder().build()));
    System.out.println(dao.selectByTrim(User.builder().name("test2").build()));
}

7.4 <where>

<where>The most common way is to splice query conditions. For example, if there are multiple query conditions, only multiple query conditions are used<if>The first one will appear<if>There’s an extra oneandThe problem of using<where>Intelligent processing, of course, is also very importantorExamples are as follows:

<select id="selectByWhere" parameterType="Integer" resultType="pers.init.entity.User">
    select * from user
    <where>
        <if test="name != null and name != ''">
            and name like concat('%',#{name},'%')
        </if>
        <if test="age != null and age>0">
            and age=#{age}
        </if>
    </where>
</select>

DaoInterface:

List<User> selectByWhere(User user);

Test:

@Test
public void testWhere()
{
    System.out.println(dao.selectByWhere(User.builder().build()));
    System.out.println(dao.selectByWhere(User.builder().name("111").build()));
    System.out.println(dao.selectByWhere(User.builder().age((short)-3).build()));
}

7.5 <set>

<set>General coordinationupdateStatement use, such as:

<update id="updateBySet" parameterType="pers.init.entity.User">
    update user
    <set>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="age != null and age > 0">
            age = #{age}
        </if>
    </set>
    where id=#{id}
</update>

DaoInterface:

int updateBySet(User user);

Test:

@Test
public void testSet()
{
    System.out.println(dao.updateBySet(User.builder().name("999999").age((short)39).id(1).build()));
    System.out.println(dao.selectByWhere(User.builder().build()));
}

7.6 <foreach>

<foreach>Mainly used forinIt can be considered as a collection, and the typical use scenario isselect xxx from xxx where xxx in <foreach>

<foreach>The main properties of are as follows:

  • item: alias for each element
  • index: subscript of each element
  • collection<foreach>There are different typeslistarraymapWhen a single parameter is passed in and the parameter type isListWhen thelist, when a single parameter is passed in and the parameter type is array; otherwisearrayOtherwise, it should be packaged asMapAnd set the property value tomap
  • open: statement start flag
  • close: statement end flag

example:

<select id="selectByForeach" parameterType="Integer" resultType="pers.init.entity.User">
  select * from user where id in
    <foreach collection="list" item="item" index="index" open="(" separator="," close=")">#{item}</foreach>
</select>

DaoInterface:

List<User> selectByForeach(List<Integer> id);

Test:

@Test
public void testForeach()
{
    System.out.println(dao.selectByForeach(List.of(1,2,3)));
}

7.7 <bind>

<bind>It can be used to splice strings,MySQLUsingconcat, andOracleUsing||, andMyBatisProvided<bind>You can block thisDBMSThere is no need to modifyxmlThe following is an example:

<select id="selectByBind" parameterType="pers.init.entity.User" resultType="pers.init.entity.User">
    <bind name="new_name" value="'%'+name+'%'"/>
    select * from user where name like #{new_name}
</select>

DaoInterface:

List<User> selectByBind(User user);

Test:

@Test
public void testBind()
{
    System.out.println(dao.selectByBind(User.builder().name("test1").build()));
}

8 source code

The code after all the examples are given here for reference only, but it is not recommended to implement them directlycloneIt is suggested to start from the initial project.

JavaVersion:

  • Github
  • Code cloud
  • CODE.CHINA

KotlinVersion:

  • Github
  • Code cloud
  • CODE.CHINA