JDBC条件查询的实现过程
String sql = "select * from userinfo where 1=1 "
if(userid !=null)
{
sql+= 'and userid = ?'
}
if(username != null)
{
sql+= 'and username = ?'
}
if(sex != null)
{
sql+= 'and sex = ?'
}
PreparedStatement statement = conn.prepareStatement(sql);
int i = 1;
替换?号
if(userid !=null)
{
statement.setint(i++, xxxx);
}
if(username !=null)
{
statement.setint(i++, xxxx);
}
if(sex !=null)
{
statement.setint(i++, xxxx);
}
statement.executeQuery();
1. 条件查询
~~~
<select id="getUsers" parameterType="Userinfo" resultType="Userinfo">
select * from userinfo
<where>
<if test="userid!=0">
and id = #{id}
</if>
<if test="name!=null and name!=''">
and name = #{name}
</if>
<if test="sex!=null and sex!=''">
and sex = #{sex}
</if>
</where>
</select>
~~~
JDBC条件更新的实现过程
String sql = "update userinfo set";
if(username!=null)
{
sql+=' username = ? ,';
}
if(sex!=null)
{
sql+=' sex = ?, ';
}
if(sal!=null)
{
sql+=' sal = ?,';
}
//删除最后的逗号
sql = sql.substring(0, sql.length-1);
sql += "where userid = ?"
PreparedStatement statement = conn.prepareStatement(sql);
int i = 1;
替换?号
if(username !=null)
{
statement.setint(i++, xxxx);
}
if(sex !=null)
{
statement.setint(i++, xxxx);
}
if(sal !=null)
{
statement.setint(i++, xxxx);
}
statement.setint(i++, userid);
statement.executeUpdate();
2. 条件更新
~~~
<update id="updateUser" parameterType="Userinfo">
update userinfo
<set>
<if test="name!=null and name!=''">
name = #{name},
</if>
<if test="sex!=null and sex!=''">
sex = #{sex},
</if>
<if test="birthday!=null and birthday!=''">
birthday = #{birthday},
</if>
<if test="sal!=0">
sal = #{sal},
</if>
</set>
where id = #{userid}
</update>
~~~
3. 数组操作
3.1 单参数List类型
~~~
<select id="getUsersByIds" resultType="Userinfo">
select * from userinfo where userid in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
~~~
~~~
<delete id="deleteUsersByIds">
delete from userinfo where userid in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
~~~
3.2 单参数数组类型
~~~
<select id="getUsersByIdsTwo" resultType="Userinfo">
select * from userinfo where userid in
<foreach collection="array" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
~~~
3.3 多参数map类型
~~~
<select id="getUsers" parameterType="map" resultType="Userinfo">
select * from userinfo where sex = #{sex} and userid in
<foreach collection="userids" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
~~~
~~~
<delete id="deleteUsers" parameterType="map">
delete from userinfo where name like "%"#{username}"%" or userid in
<foreach collection="userids" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
~~~
