<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "//mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UsersMapper">
<!--
//根據給定的查詢條件進行多條件查詢
List<User> selectOnCondition(User user);
private Integer id;
private String userName;
private Date birthday;
private String sex;
private String address;
-->
<select id="selectOnCondition" parameterType="user" resultType="user">
select
<include refid="allColumns"/>
from
users
<where>
<if test="userName != null and userName != ''">
and username like concat('%', #{userName},'%')
</if>
<if test="birthday != null">
and birthday=#{birthday}
</if>
<if test="sex != null and sex != ''">
and sex=#{sex}
</if>
<if test="address != null and address != ''">
and address=#{address}
</if>
</where>
</select>
</mapper>
< where >標籤相當於在原sql語句後補加了一個：where關鍵字，從底層輸出的結果可以證實
==> Preparing: select id, username, birthday, sex, address from users WHERE username like concat('%', ?,'%')
注意：至少有一個< if >標籤通過，where關鍵字才會被追加，不然不會追加
@Test
public void testSelectOnCondition(){
User u = new User();
List<User> users = usersMapper.selectOnCondition(u);
users.forEach(System.out::println);
}
Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]
Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]
Opening JDBC Connection
Created connection 1340848245.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4febb875]
==> Preparing: select id, username, birthday, sex, address from users //這時sql語句後面沒有追加where
==> Parameters:
<== Columns: id, username, birthday, sex, address
<== Row: 1, 荷包蛋, 2002-08-23, 女, 黑河
<== Row: 2, 小王, 2001-07-12, 1, 蕪湖市
<== Row: 3, 小張, 1999-02-22, 1, 長沙
<== Row: 5, 段, 2001-03-10, 1, 太原
<== Row: 6, 范成群, 2002-01-19, 1, 鮁魚圈
<== Row: 7, 學委, 2001-05-13, 2, 平頂山市
<== Row: 28, 邏輯, 2010-05-18, 男, 上海
<== Row: 29, 小昕, 2001-03-14, 女, 忻州
<== Row: 30, 小青, 1996-11-22, 女, 瀋陽市
<== Total: 9
Users{id=1, userName='荷包蛋', birthday=Fri Aug 23 00:00:00 CST 2002, sex='女', address='黑河'}
Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='蕪湖市'}
Users{id=3, userName='小張', birthday=Mon Feb 22 00:00:00 CST 1999, sex='1', address='長沙'}
Users{id=5, userName='段', birthday=Sat Mar 10 00:00:00 CST 2001, sex='1', address='太原'}
Users{id=6, userName='范成群', birthday=Sat Jan 19 00:00:00 CST 2002, sex='1', address='鮁魚圈'}
Users{id=7, userName='學委', birthday=Sun May 13 00:00:00 CST 2001, sex='2', address='平頂山市'}
Users{id=28, userName='邏輯', birthday=Tue May 18 00:00:00 CST 2010, sex='男', address='上海'}
Users{id=29, userName='小昕', birthday=Wed Mar 14 00:00:00 CST 2001, sex='女', address='忻州'}
Users{id=30, userName='小青', birthday=Fri Nov 22 00:00:00 CST 1996, sex='女', address='瀋陽市'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4febb875]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4febb875]
Returned connection 1340848245 to pool.
Process finished with exit code 0
package com.example.mapper;
import com.example.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestUsersMapper {
//SqlSession對象
SqlSession sqlSession;
//mybatis動態代理對象
UsersMapper usersMapper;
//獲取SqlSession
@Before
public void getSqlSession() throws IOException {
//讀取核心配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//創建SqlSessionFactory對象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//獲取SqlSession
sqlSession = factory.openSession();
//獲取mybatis動態代理對象
usersMapper = sqlSession.getMapper(UsersMapper.class);
}
//歸還SqlSession
@After
public void closeSession(){
sqlSession.close();
}
@Test
public void testUpdateBetter(){
User u = new User();
u.setId(1);
u.setAddress("哈爾濱");
int num = usersMapper.updateBetter(u);
if(num == 1){
System.out.println("更新成功!");
}else{
System.out.println("更新失敗!");
}
sqlSession.commit();
}
}
輸出結果
Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]
Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]
Opening JDBC Connection
Created connection 544966217.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]
==> Preparing: update users SET address=? where id=?
==> Parameters: 哈爾濱(String), 1(Integer)
<== Updates: 1
更新成功!
Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]
Returned connection 544966217 to pool.
Process finished with exit code 0
