第1种：直接使用where
<sql id="columnSql">
<trim suffixOverrides=","> <!-- suffixOverrides此时的作用是去除最后一个逗号 -->
id,
username,
password,
sex,
</trim>
</sql>
<select id="findByUsernameAndPwd" resultMap="resultMap">
SELECT
<include refid="columnSql"></include>
FROM t_user
WHERE username = #{username} and password = #{password}
</select>
输入：username：Tom，password：123
2018-02-03 11:54:57,458 -[DEBUG] method:[ (10629ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username = ? and password = ?
2018-02-03 11:54:57,710 -[DEBUG] method:[ (10881ms)] - ==>
Parameters: Tom(String), 123(String)
2018-02-03 11:54:57,913 -[DEBUG] method:[ (11084ms)] - <==
Total: 1
输入：username：Tom，password：为空
2018-02-03 15:51:51,635 -[DEBUG] method:[ (15115ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username = ? and password = ?
2018-02-03 15:51:51,697 -[DEBUG] method:[ (15177ms)] - ==>
Parameters: Tom(String), (String)
2018-02-03 15:51:51,729 -[DEBUG] method:[ (15209ms)] - <==
Total: 0
第2种：使用<where>
<sql id="columnSql">
<trim suffixOverrides=","> <!-- suffixOverrides此时的作用是去除最后一个逗号 -->
id,
username,
password,
sex,
</trim>
</sql>
<select id="findByUsernameAndPwd" resultMap="resultMap">
SELECT
<include refid="columnSql"></include>
FROM t_user
<where>
<if test="username !=null and username != '' "> and username =#{username} </if>
<if test="password !=null and password != '' "> and password = #{password} </if>
</where>
</select>
输入：username：Tom，password：123
2018-02-03 15:41:23,540 -[DEBUG] method:[ (14556ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username =? and password = ?
2018-02-03 15:41:23,572 -[DEBUG] method:[ (14588ms)] - ==>
Parameters: Tom(String), 123(String)
2018-02-03 15:41:23,618 -[DEBUG] method:[ (14634ms)] - <==
Total: 1
输入：username：Tom
2018-02-03 15:41:40,974 -[DEBUG] method:[ (31990ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username =?
2018-02-03 15:41:40,975 -[DEBUG] method:[ (31991ms)] - ==>
Parameters: Tom(String)
2018-02-03 15:41:40,975 -[DEBUG] method:[ (31991ms)] - <==
Total: 2
输入：username：为空，password:为空
2018-02-03 15:50:12,162 -[DEBUG] method:[ (69422ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user
2018-02-03 15:50:12,162 -[DEBUG] method:[ (69422ms)] - ==>
Parameters:
2018-02-03 15:50:12,162 -[DEBUG] method:[ (69422ms)] - <==
Total: 3
第3种：使用<trim prefix="WHERE" suffixOverrides="AND">
<sql id="columnSql">
<trim suffixOverrides=","> <!-- suffixOverrides此时的作用是去除最后一个逗号 -->
id,
username,
password,
sex,
</trim>
</sql>
<select id="findByUsernameAndPwd" resultMap="resultMap">
SELECT
<include refid="columnSql"></include>
FROM t_user
<trim prefix="WHERE" suffixOverrides="AND"> <!-- prefix的作用是把WHERE放条件的前面，suffixOverrides此时的作用是去除最后一个AND -->
<if test="username !=null and username != '' "> username =#{username} AND </if>
<if test="password !=null and password != '' "> password =#{password} AND </if>
</trim>
</select>
2018-02-04 11:43:59,272 -[DEBUG] method:[ (11729ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username =? AND password =?
2018-02-04 11:43:59,537 -[DEBUG] method:[ (11994ms)] - ==>
Parameters: Tom(String), 123(String)
2018-02-04 11:43:59,725 -[DEBUG] method:[ (12182ms)] - <==
Total: 1
2018-02-04 11:51:03,690 -[DEBUG] method:[ (34742ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username =?
2018-02-04 11:51:03,690 -[DEBUG] method:[ (34742ms)] - ==>
Parameters: Tom(String)
2018-02-04 11:51:03,690 -[DEBUG] method:[ (34742ms)] - <==
Total: 2
2018-02-04 11:51:36,346 -[DEBUG] method:[ (67398ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE password =?
2018-02-04 11:51:36,346 -[DEBUG] method:[ (67398ms)] - ==>
Parameters: 123(String)
2018-02-04 11:51:36,362 -[DEBUG] method:[ (67414ms)] - <==
Total: 1
第4种：使用<trim prefix="WHERE" prefixOverrides="AND">
<sql id="columnSql">
<trim suffixOverrides=","> <!-- suffixOverrides此时的作用是去除最后一个逗号 -->
id,
username,
password,
sex,
</trim>
</sql>
<select id="findByUsernameAndPwd" resultMap="resultMap">
SELECT
<include refid="columnSql"></include>
FROM t_user
<trim prefix="WHERE" prefixOverrides="AND"> <!--prefix的作用是把WHERE放条件的前面，prefixOverrides此时的作用是去除最前面的一个AND -->
<if test="username !=null and username != '' "> AND username =#{username} </if>
<if test="password !=null and password != '' "> AND password =#{password} </if>
</trim>
</select>
2018-02-04 11:46:10,148 -[DEBUG] method:[ (12238ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username =? AND password =?
2018-02-04 11:46:10,413 -[DEBUG] method:[ (12503ms)] - ==>
Parameters: Tom(String), 123(String)
2018-02-04 11:46:10,600 -[DEBUG] method:[ (12690ms)] - <==
Total: 1
2018-02-04 11:47:33,979 -[DEBUG] method:[ (13198ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE username =?
2018-02-04 11:47:34,244 -[DEBUG] method:[ (13463ms)] - ==>
Parameters: Tom(String)
2018-02-04 11:47:34,432 -[DEBUG] method:[ (13651ms)] - <==
Total: 2
2018-02-04 11:49:00,977 -[DEBUG] method:[ (100196ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE password =?
2018-02-04 11:49:00,977 -[DEBUG] method:[ (100196ms)] - ==>
Parameters: 123(String)
2018-02-04 11:49:00,993 -[DEBUG] method:[ (100212ms)] - <==
Total: 1