查询

⬇参考代码：demo2

查询单个实体类对象

1
Customer selectCustomerById(@Param("CustomerId") Integer customerId);
1
2
3
4
5
<select id="selectCustomerById" resultMap="CustomerMap">
    SELECT *
    FROM customers
    WHERE CustomerID = #{CustomerId}
</select>
1
2
3
4
5
6
7
@Test
public void selectCustomerById() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    Customer customer = customerMapper.selectCustomerById(1);
    System.out.println(customer);
}

Customer{customerId=1, customerName=‘Alfreds Futterkiste’, contactName=‘Maria Anders’, address=‘Obere Str. 57’, city=‘Berlin’, postalCode=‘12209’, country=‘Germany’}

查询多个实体类对象

1
List<Customer> selectAllCustomer();
1
2
3
4
<select id="selectAllCustomer" resultMap="CustomerMap">
    SELECT *
    FROM customers
</select>
1
2
3
4
5
6
7
@Test
public void testSelectAllCustomer() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> customers = customerMapper.selectAllCustomer();
    customers.forEach(System.out::println);
}

Customer{customerId=1, customerName=‘Alfreds Futterkiste’, contactName=‘Maria Anders’, address=‘Obere Str. 57’, city=‘Berlin’, postalCode=‘12209’, country=‘Germany’}
Customer{customerId=2, customerName=‘Ana Trujillo Emparedados y helados’, contactName=‘Ana Trujillo’, address=‘Avda. de la Constitución 2222’, city=‘México D.F.’, postalCode=‘5021’, country=‘Mexico’}
Customer{customerId=3, customerName=‘Antonio Moreno Taquería’, contactName=‘Antonio Moreno’, address=‘Mataderos 2312’, city=‘México D.F.’, postalCode=‘5023’, country=‘Mexico’}
Customer{customerId=4, customerName=‘Around the Horn’, contactName=‘Thomas Hardy’, address=‘120 Hanover Sq.’, city=‘London’, postalCode=‘WA1 1DP’, country=‘UK’}
Customer{customerId=5, customerName=‘Berglunds snabbköp’, contactName=‘Christina Berglund’, address=‘Berguvsvägen 8’, city=‘Luleå’, postalCode=‘S-958 22’, country=‘Sweden’}

…

查询单条数据转换为 Map\text {Map} 集合

1
Map<String, Objects> selectCustomerByIdToMap(@Param("CustomerId") Integer customerId);
1
2
3
4
5
<select id="selectCustomerByIdToMap" resultType="map">
    SELECT *
    FROM customers
    WHERE CustomerID = #{CustomerId}
</select>
1
2
3
4
5
6
7
@Test
public void testSelectCustomerToMap() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    Map<String, Objects> customer = customerMapper.selectCustomerByIdToMap(1);
    System.out.println(customer);
}

{Address=Obere Str. 57, PostalCode=12209, Country=Germany, CustomerID=1, CustomerName=Alfreds Futterkiste, City=Berlin, ContactName=Maria Anders}

查询多条数据转换为 Map\text {Map} 集合

1⃣

1
List<Map<String, Object>> selectAllCustomerToMap1();
1
2
3
4
<select id="selectAllCustomerToMap1" resultType="map">
    SELECT *
    FROM customers
</select>
1
2
3
4
5
6
7
@Test
public void testSelectAllCustomerToMap1() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Map<String, Object>> customersListMap = customerMapper.selectAllCustomerToMap1();
    customersListMap.forEach(System.out::println);
}

{Address=Obere Str. 57, PostalCode=12209, Country=Germany, CustomerID=1, CustomerName=Alfreds Futterkiste, City=Berlin, ContactName=Maria Anders}
{Address=Avda. de la Constitución 2222, PostalCode=5021, Country=Mexico, CustomerID=2, CustomerName=Ana Trujillo Emparedados y helados, City=México D.F., ContactName=Ana Trujillo}
{Address=Mataderos 2312, PostalCode=5023, Country=Mexico, CustomerID=3, CustomerName=Antonio Moreno Taquería, City=México D.F., ContactName=Antonio Moreno}
{Address=120 Hanover Sq., PostalCode=WA1 1DP, Country=UK, CustomerID=4, CustomerName=Around the Horn, City=London, ContactName=Thomas Hardy}
{Address=Berguvsvägen 8, PostalCode=S-958 22, Country=Sweden, CustomerID=5, CustomerName=Berglunds snabbköp, City=Luleå, ContactName=Christina Berglund}

…

2⃣添加CustomerID为键

1
2
@MapKey("CustomerID")
Map<String, Object> selectAllCustomerToMap2();
1
2
3
4
<select id="selectAllCustomerToMap2" resultType="map">
    SELECT *
    FROM customers
</select>
1
2
3
4
5
6
7
@Test
public void testSelectAllCustomerToMap2() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    Map<String, Object> customersMap = customerMapper.selectAllCustomerToMap2();
    System.out.println(customersMap);
}

{1={Address=Obere Str. 57, PostalCode=12209, Country=Germany, CustomerID=1, CustomerName=Alfreds Futterkiste, City=Berlin, ContactName=Maria Anders}, 2={Address=Avda. de la Constitución 2222, PostalCode=5021, Country=Mexico, CustomerID=2, CustomerName=Ana Trujillo Emparedados y helados, City=México D.F., ContactName=Ana Trujillo}, 3={Address=Mataderos 2312, PostalCode=5023, Country=Mexico, CustomerID=3, CustomerName=Antonio Moreno Taquería, City=México D.F., ContactName=Antonio Moreno}, 4={Address=120 Hanover Sq., PostalCode=WA1 1DP, Country=UK, CustomerID=4, CustomerName=Around the Horn, City=London, ContactName=Thomas Hardy},…

模糊查询

1
List<Customer> selectCustomerByName(@Param("CustomerName") String customerName);

1⃣占位符

1
2
3
4
5
<select id="selectCustomerByName" resultMap="CustomerMap">
    SELECT *
    FROM customers
    WHERE CustomerName LIKE '%${CustomerName}%'
</select>

2⃣字符串拼接

1
2
3
4
5
<select id="selectCustomerByName" resultMap="CustomerMap">
    SELECT *
    FROM customers
    WHERE CustomerName LIKE "%"#{CustomerName}"%"
</select>
1
2
3
4
5
<select id="selectCustomerByName" resultMap="CustomerMap">
    SELECT *
    FROM customers
    WHERE CustomerName LIKE CONCAT("%",#{CustomerName},"%")
</select>
1
2
3
4
5
6
7
@Test
public void testSelectCustomerByName() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> customers = customerMapper.selectCustomerByName("ab");
    customers.forEach(System.out::println);
}

Customer{customerId=5, customerName=‘Berglunds snabbköp’, contactName=‘Christina Berglund’, address=‘Berguvsvägen 8’, city=‘Luleå’, postalCode=‘S-958 22’, country=‘Sweden’}
Customer{customerId=22, customerName=‘FISSA Fabrica Inter. Salchichas S.A.’, contactName=‘Diego Roel’, address=‘C/ Moralzarzal, 86’, city=‘Madrid’, postalCode=‘28034’, country=‘Spain’}
Customer{customerId=35, customerName=‘HILARIÓN-Abastos’, contactName=‘Carlos Hernández’, address=‘Carrera 22 con Ave. Carlos Soublette #8-35’, city=‘San Cristóbal’, postalCode=‘5022’, country=‘Venezuela’}
Customer{customerId=40, customerName=‘La corne d’‘abondance’, contactName=‘Daniel Tonini’, address=‘67, avenue de l’‘Europe’, city=‘Versailles’, postalCode=‘78000’, country=‘France’}
Customer{customerId=60, customerName=‘Princesa Isabel Vinhoss’, contactName=‘Isabel de Castro’, address=‘Estrada da saúde n. 58’, city=‘Lisboa’, postalCode=‘1756’, country=‘Portugal’}

自定义 resultMap

⬇参考代码：demo3

当出现数据库字段名和 Java\text {Java} 类属性名不一致的情况，解决方式：

1⃣全局设置

1
2
<!-- 开启驼峰命名自动映射，例如，数据库字段名为 user_id，自动映射为 userId -->
<setting name="mapUnderscoreToCamelCase" value="true"/>

2⃣别名映射

1
2
3
4
5
<select id="getUserById" resultMap="userResultMap">
    SELECT user_id AS id, user_name AS userName, user_age AS userAge
    FROM users
    WHERE user_id = #{id}
</select>

3⃣<resultMap>

1
2
3
4
5
6
7
8
9
10
11
<resultMap id="userResultMap" type="User">
    <id property="id" column="userId" />
    <result property="userName" column="userName" />
    <result property="userAge" column="userAge" />
</resultMap>

<select id="getUserById" resultMap="userResultMap">
    SELECT *
    FROM users
    WHERE user_id = #{id}
</select>

一对一关系映射

一个订单（Orders\text {Orders}）对应着一个顾客（Customers\text {Customers}）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public class Order {
    private int orderId;
    private int customerId;
    private int employeeId;
    private Date orderDate;
    private int shipperId;
    // constructor , getters , setters and toString
}

public class Customer {
    private int customerId;
    private String customerName;
    private String contactName;
    private String address;
    private String city;
    private String postalCode;
    private String country;
    // constructor , getters , setters and toString
}

1⃣级联关系

1
Order selectOrderByIdWithCustomer1(@Param("OrderId") Integer orderId);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<resultMap id="OrderByIdWithCustomer1" type="com.liangjiajia.cs.pojo.one2one.Order">
    <id column="OrderID" property="orderId"/>
    <result column="CustomerID" property="customerId"/>
    <result column="EmployeeID" property="employeeId"/>
    <result column="OrderDate" property="orderDate"/>
    <result column="ShipperID" property="shipperId"/>
    <result column="CustomerID" property="customer.customerId"/>
    <result column="CustomerName" property="customer.customerName"/>
    <result column="ContactName" property="customer.contactName"/>
    <result column="Address" property="customer.address"/>
    <result column="City" property="customer.city"/>
    <result column="PostalCode" property="customer.postalCode"/>
    <result column="Country" property="customer.country"/>
</resultMap>
<select id="selectOrderByIdWithCustomer1" resultMap="OrderByIdWithCustomer1">
    SELECT *
    FROM orders o
             LEFT JOIN customers c ON o.CustomerID = c.CustomerID
    WHERE o.OrderID = #{OrderId}
</select>
1
2
3
4
5
6
7
@Test
public void testSelectOrderByIdWithCustomer1() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    One2OneMapper one2OneMapper = sqlSession.getMapper(One2OneMapper.class);
    Order order = one2OneMapper.selectOrderByIdWithCustomer1(10248);
    System.out.println(order);
}

Order{orderId=10248, customerId=90, employeeId=5, orderDate=Thu Jul 04 00:00:00 CST 1996, shipperId=3, customer=Customer{customerId=90, customerName=‘Wilman Kala’, contactName=‘Matti Karttunen’, address=‘Keskuskatu 45’, city=‘Helsinki’, postalCode=‘21240’, country=‘Finland’}}

2⃣<association>

1
Order selectOrderByIdWithCustomer2(@Param("OrderId") Integer orderId);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<resultMap id="OrderByIdWithCustomer2" type="com.liangjiajia.cs.pojo.one2one.Order">
    <id column="OrderID" property="orderId"/>
    <result column="CustomerID" property="customerId"/>
    <result column="EmployeeID" property="employeeId"/>
    <result column="OrderDate" property="orderDate"/>
    <result column="ShipperID" property="shipperId"/>
    <association property="customer" javaType="com.liangjiajia.cs.pojo.one2one.Customer">
        <id column="CustomerID" property="customerId"/>
        <result column="CustomerName" property="customerName"/>
        <result column="ContactName" property="contactName"/>
        <result column="Address" property="address"/>
        <result column="City" property="city"/>
        <result column="PostalCode" property="postalCode"/>
        <result column="Country" property="country"/>
    </association>
</resultMap>
<select id="selectOrderByIdWithCustomer2" resultMap="OrderByIdWithCustomer2">
    SELECT *
    FROM orders o
             LEFT JOIN customers c ON o.CustomerID = c.CustomerID
    WHERE o.OrderID = #{OrderId}
</select>
1
2
3
4
5
6
7
@Test
public void testSelectOrderByIdWithCustomer2() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    One2OneMapper one2OneMapper = sqlSession.getMapper(One2OneMapper.class);
    Order order = one2OneMapper.selectOrderByIdWithCustomer2(10249);
    System.out.println(order);
}

Order{orderId=10249, customerId=81, employeeId=6, orderDate=Fri Jul 05 00:00:00 CST 1996, shipperId=1, customer=Customer{customerId=81, customerName=‘Tradição Hipermercados’, contactName=‘Anabela Domingues’, address=‘Av. Inês de Castro, 414’, city=‘São Paulo’, postalCode=‘05634-030’, country=‘Brazil’}}

3⃣分步查询​

1
2
3
Order selectOrderByIdWithCustomer3(@Param("OrderId") Integer orderId);

Customer selectCustomerById(@Param("CustomerId") Integer customerId);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<resultMap id="OrderByIdWithCustomer3" type="com.liangjiajia.cs.pojo.one2one.Order">
    <id column="OrderID" property="orderId"/>
    <result column="CustomerID" property="customerId"/>
    <result column="EmployeeID" property="employeeId"/>
    <result column="OrderDate" property="orderDate"/>
    <result column="ShipperID" property="shipperId"/>
    <association property="customer" select="selectCustomerById" column="CustomerID"/>
</resultMap>
<select id="selectOrderByIdWithCustomer3" resultMap="OrderByIdWithCustomer3">
    SELECT * FROM orders
    WHERE OrderID = #{OrderId}
</select>
<resultMap id="CustomerById" type="com.liangjiajia.cs.pojo.one2one.Customer">
    <id column="CustomerID" property="customerId"/>
    <result column="CustomerName" property="customerName"/>
    <result column="ContactName" property="contactName"/>
    <result column="Address" property="address"/>
    <result column="City" property="city"/>
    <result column="PostalCode" property="postalCode"/>
    <result column="Country" property="country"/>
</resultMap>
<select id="selectCustomerById" resultMap="CustomerById">
    SELECT * FROM customers
    WHERE CustomerID = #{CustomerId}
</select>
1
2
3
4
5
6
7
@Test
public void testSelectOrderByIdWithCustomer3() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    One2OneMapper one2OneMapper = sqlSession.getMapper(One2OneMapper.class);
    Order order = one2OneMapper.selectOrderByIdWithCustomer3(10250);
    System.out.println(order);
}

Order{orderId=10250, customerId=34, employeeId=4, orderDate=Mon Jul 08 00:00:00 CST 1996, shipperId=2, customer=Customer{customerId=34, customerName=‘Hanari Carnes’, contactName=‘Mario Pontes’, address=‘Rua do Paço, 67’, city=‘Rio de Janeiro’, postalCode=‘05454-876’, country=‘Brazil’}}

延迟加载

1
2
3
<settings>
    <setting name="LazyLoadingEnabled" value="true"/>
</settings>
1
<association property="customer" select="selectCustomerById" column="CustomerID" fetchType="lazy"/>
1
2
3
4
5
6
7
8
9
@Test
public void testSelectCustomerByIdWithOrders3_LazyLoading() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    One2OneMapper one2OneMapper = sqlSession.getMapper(One2OneMapper.class);
    Order order1 = one2OneMapper.selectOrderByIdWithCustomer3(10250);
    System.out.println(order1.getOrderId());
    Order order2 = one2OneMapper.selectOrderByIdWithCustomer3(10250);
    System.out.println(order2.getCustomer());
}
1
<association property="customer" select="selectCustomerById" column="CustomerID" fetchType="eager"/>
## 一对多关系映射

一个顾客（Customers\text {Customers}）可能对应着多个订单（Orders\text {Orders}）

1⃣<collection>

1
Customer selectCustomerByIdWithOrders1(@Param("CustomerId") Integer customerId);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<resultMap id="CustomerByIdWithOrders1" type="com.liangjiajia.cs.pojo.one2many.Customer">
    <result column="CustomerID" property="customerId"/>
    <result column="CustomerName" property="customerName"/>
    <result column="ContactName" property="contactName"/>
    <result column="Address" property="address"/>
    <result column="City" property="city"/>
    <result column="PostalCode" property="postalCode"/>
    <result column="Country" property="country"/>
    <collection property="orders" ofType="com.liangjiajia.cs.pojo.one2many.Order">
        <id column="OrderID" property="orderId"/>
        <result column="CustomerID" property="customerId"/>
        <result column="EmployeeID" property="employeeId"/>
        <result column="OrderDate" property="orderDate"/>
        <result column="ShipperID" property="shipperId"/>
    </collection>
</resultMap>
<select id="selectCustomerByIdWithOrders1" resultMap="CustomerByIdWithOrders1">
    SELECT *
    FROM customers c
             LEFT JOIN orders o ON c.CustomerID = o.CustomerID
    WHERE c.CustomerID = #{CustomerId}
</select>
1
2
3
4
5
6
7
@Test
public void testSelectCustomerByIdWithOrders1() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    One2ManyMapper one2ManyMapper = sqlSession.getMapper(One2ManyMapper.class);
    Customer customer = one2ManyMapper.selectCustomerByIdWithOrders1(20);
    System.out.println(customer);
}

Customer{customerId=20, customerName=‘Ernst Handel’, contactName=‘Roland Mendel’, address=‘Kirchgasse 6’, city=‘Graz’, postalCode=‘8010’, country=‘Austria’, orders=[Order{orderId=10258, customerId=20, employeeId=1, orderDate=Wed Jul 17 00:00:00 CST 1996, shipperId=1}, Order{orderId=10263, customerId=20, employeeId=9, orderDate=Tue Jul 23 00:00:00 CST 1996, shipperId=3}, Order{orderId=10351, customerId=20, employeeId=1, orderDate=Mon Nov 11 00:00:00 CST 1996, shipperId=1}, Order{orderId=10368, customerId=20, employeeId=2, orderDate=Fri Nov 29 00:00:00 CST 1996, shipperId=2}, Order{orderId=10382, customerId=20, employeeId=4, orderDate=Fri Dec 13 00:00:00 CST 1996, shipperId=1}, Order{orderId=10390, customerId=20, employeeId=6, orderDate=Mon Dec 23 00:00:00 CST 1996, shipperId=1}, Order{orderId=10402, customerId=20, employeeId=8, orderDate=Thu Jan 02 00:00:00 CST 1997, shipperId=2}, Order{orderId=10403, customerId=20, employeeId=4, orderDate=Fri Jan 03 00:00:00 CST 1997, shipperId=3}, Order{orderId=10430, customerId=20, employeeId=4, orderDate=Thu Jan 30 00:00:00 CST 1997, shipperId=1}, Order{orderId=10442, customerId=20, employeeId=3, orderDate=Tue Feb 11 00:00:00 CST 1997, shipperId=2}]}

2⃣分步查询

1
2
3
Customer selectCustomerByIdWithOrders2(@Param("CustomerId") Integer customerId);

Order selectOrdersById(@Param("CustomerId") Integer customerId);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<resultMap id="CustomerByIdWithOrders2" type="com.liangjiajia.cs.pojo.one2many.Customer">
    <id column="CustomerID" property="customerId"/>
    <result column="CustomerName" property="customerName"/>
    <result column="ContactName" property="contactName"/>
    <result column="Address" property="address"/>
    <result column="City" property="city"/>
    <result column="PostalCode" property="postalCode"/>
    <result column="Country" property="country"/>
    <collection property="orders" select="selectOrdersById" column="CustomerID"/>
</resultMap>
<select id="selectCustomerByIdWithOrders2" resultMap="CustomerByIdWithOrders2">
    SELECT * FROM customers
    WHERE CustomerID = #{CustomerId}
</select>
<resultMap id="OrdersById" type="com.liangjiajia.cs.pojo.one2many.Order">
    <id column="OrderID" property="orderId"/>
    <result column="CustomerID" property="customerId"/>
    <result column="EmployeeID" property="employeeId"/>
    <result column="OrderDate" property="orderDate"/>
    <result column="ShipperID" property="shipperId"/>
</resultMap>
<select id="selectOrdersById" resultMap="OrdersById">
    SELECT *
    FROM orders
    WHERE CustomerID = #{CustomerId}
</select>
1
2
3
4
5
6
7
@Test
public void testSelectCustomerByIdWithOrders2() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    One2ManyMapper one2ManyMapper = sqlSession.getMapper(One2ManyMapper.class);
    Customer customer = one2ManyMapper.selectCustomerByIdWithOrders2(20);
    System.out.println(customer);
}

延迟加载

1
2
3
<settings>
    <setting name="LazyLoadingEnabled" value="true"/>
</settings>
1
<collection property="orders" select="selectOrdersById" column="CustomerID" fetchType="lazy"/>
1
2
3
4
5
6
7
8
9
@Test
public void testSelectCustomerByIdWithOrders2_LazyLoading() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    One2ManyMapper one2ManyMapper = sqlSession.getMapper(One2ManyMapper.class);
    Customer customer1 = one2ManyMapper.selectCustomerByIdWithOrders2(20);
    System.out.println(customer1.getCustomerId());
    Customer customer2 = one2ManyMapper.selectCustomerByIdWithOrders2(20);
    System.out.println(customer2.getOrders());
}
1
<collection property="orders" select="selectOrdersById" column="CustomerID" fetchType="eager"/>

动态 SQL

  • <if>\text {<if>} 元素
1
2
3
4
5
6
7
8
9
10
11
<select id="selectUsers" resultType="User">
    SELECT * FROM Users 1 = 1
    <where>
        <if test="name != '' and name != null">
            AND name = #{name}
        </if>
        <if test="age != '' and age != null">
            AND age = #{age}
        </if>
    </where>
</select>
  • <where>\text {<where>} 元素
1
2
3
4
5
6
7
8
9
10
11
12
<select id="selectUsers" resultType="User">
    SELECT * FROM Users
    <where>
        <if test="name != '' and name != null">
            AND name = #{name}
        </if>
        <if test="age != '' and age != null">
            AND age = #{age}
        </if>
    </where>
</select>

  • <trim>\text {<trim>} 元素
1
2
3
4
5
6
7
8
9
10
11
<select id="selectUsers" resultType="User">
    SELECT * FROM Users
    <trim prefix="WHERE" prefixOverrides="AND | OR">
        <if test="name != '' and name != null">
            AND name = #{name}
        </if>
        <if test="age != '' and age != null">
            AND age = #{age}
        </if>
    </trim>
</select>

  • prefix：指定要添加到 SQL 片段前面的前缀。

  • prefixOverrides：指定要从 SQL 片段的前面删除的前缀。

  • suffix：指定要添加到 SQL 片段后面的后缀。

  • suffixOverrides：指定要从 SQL 片段的后面删除的后缀。

  • <choose>、<when>、<otherwise>\text {<choose>、<when>、<otherwise>} 元素

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<select id="selectUsers" resultType="User">
    SELECT * FROM Users
    <where>
        <choose>
            <when test="name != '' and name != null">
                AND name = #{name}
            </when>
            <when test="age != '' and age != null">
                AND age = #{age}
            </when>
            <otherwise>
                <!-- 添加默认条件或逻辑 -->
                AND 1 = 1
            </otherwise>
        </choose>
    </where>
</select>
  • <foreach>\text {<foreach>} 元素
1
2
3
4
5
// 批量插入用户
void batchInsertUsers(@Param("Users") List<User> users);

// 批量删除用户
void batchDeleteUsers(@Param("Ids") List<Integer> ids);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<insert id="batchInsertUsers">
    INSERT INTO Users (id, username, age) VALUES
    <foreach collection="Users" item="user" separator=",">
        (#{user.id}, #{user.username}, #{user.age})
    </foreach>
</insert>

<delete id="batchDeleteUsers">
    DELETE FROM Users
    WHERE id IN
    <foreach collection="Ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

SQL\text {SQL} 片段

1
2
3
4
5
6
7
8
9
<!-- 定义 SQL 片段 -->
<sql id="userColumns">
    id, username, age
</sql>

<!-- 引用 SQL 片段 -->
<select id="selectUsers" resultType="User">
    SELECT <include refid="userColumns"/> FROM Users
</select>