什么是动态SQL?
在不同条件下拼接不同的sql,语句中包含if等判断语句的sql叫动态SQL。在我们以前用servlet的时候,我们就是用jdbc手动拼接的sql语句,我们在拼写的时候经常会出现sql拼写错误,比如入参写的不对,或者没有加单引号等等原因,那么动态sql语句就是帮我们去写这样的sql.
动态sql的使用案例-查询
- 一般的sql查询语句
编写.xml
<select id="some" resultType="com.qcby.entity.User"> select * from user where sex=#{sex} and address=#{address} <!-- 查询sex与address等于给定条件的所有信息--> </select>
编写UserDao
public interface UserDao { public List<User> some(User user); }
编写UserTest
@Test public void some(){ User user=new User(); //user.setSex("男"); user.setAddress("保定"); List<User> users=mapper.some(user); for(User usr:users){ System.out.println(usr); } }
如果sex或address中的一个为null,那么sql就无法运行,会报异常。这时我们可以使用if语句来解决。
- <if test="">
编写.xml
<select id="some" resultType="com.qcby.entity.User"> select * from user where <if test="sex!=null"> sex=#{sex} </if> <if test="address!=null and sex!=null" > and address=#{address} </if> <if test="address!=null and sex==null" > address=#{address} </if> </select>
编写UserDao
public interface UserDao { //if 测试 public List<User> some(User user); }
编写UserTest
@Test public void some(){ User user=new User(); //user.setSex("男"); user.setAddress("保定"); List<User> users=mapper.some(user); for(User usr:users){ System.out.println(usr); } }
如果sex和address两者null,那么sql无法运行,还会报异常。这时我们可以使用where if语句来解决。
- <where ><if test=""></if></where >
编写.xml
<select id="some" resultType="com.qcby.entity.User"> select * from user <where > <if test="sex!=null"> sex=#{sex} </if> <if test="address!=null"> and address=#{address} </if> </where > </select>
编写UserDao
public interface UserDao { //where if 测试 public List<User> some(User user); }
编写UserTest
@Test public void some(){ User user=new User(); //user.setSex("男"); user.setAddress("保定"); List<User> users=mapper.some(user); for(User usr:users){ System.out.println(usr); } }
where if完美解决了参数为null造成的sql无法运行的问题。当sex为null并且address不为null时,执行select * from user where address =${address}语句,会自动去掉and语句;address为null时,执行select * from user where sex = ${sex}语句;两者都null时,执行select * from user语句。
动态sql的使用案例-修改
- 一般sql修改语句
编写.xml
<update id="update" parameterType="com.qcby.entity.User"> update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id} </update>
编写UserDao
public interface UserDao { //修改数据 public int update(User user); }
编写UserTest
//修改 @Test public void update(){ User user=new User(); //user.setUsername("AGi");//这个参数为null,就会报错 user.setBirthday(new Date()); user.setSex("男"); user.setAddress("保定"); user.setId(1); int flag= mapper.update(user); session.commit(); System.out.println(flag); }
当不该为null的字段为null时,这段修改语句会报异常。因此可以使用set if语句来解决问题。
- <set><if test=""></if></set>
编写.xml
<update id="update" parameterType="com.qcby.entity.User"> update user <set> <if test="username!=null and id!=null"> username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id} </if> </set> </update>
编写UserDao
public interface UserDao { //修改数据 public int update(User user); }
编写UserTest
//修改 @Test public void update(){ User user=new User(); //user.setUsername("AGi");//这个参数为null,就会报错 user.setBirthday(new Date()); user.setSex("男"); user.setAddress("保定"); user.setId(1); int flag= mapper.update(user); session.commit(); System.out.println(flag); }
根据指定的条件来执行sql语句。
动态sql的使用案例-其他
- <choose>、<when>和<otherwise>元素:在查询中如果不想使用所有的条件,而只是想从多个选项中选择一个,可以使用<chose>、<when>和otherwise>元素来实现。<choose>会按照顺序判断<when>元素中的条件是否成立,如果有一个成立,则不会判断后边<otherwise>的是否成立。如果元素不成立,则执行<otherwise>元素的sql语句
编写.xml
<select id="find" resultType="com.qcby.entity.User" > select * from user <where> <choose> <when test="id !='' and id != null"> id=#{id} </when> <when test="username !='' and username != null"> and username=#{username} </when> <otherwise> and sex=#{sex} </otherwise> </choose> </where> </select>
编写UserDao
public interface UserDao { //查询 public List<User> find(User user); }
编写UserTest
@Test public void find(){ User usr=new User(); //usr.setUsername("AGi"); usr.setSex("男"); //usr.setId(1); List<User> users=mapper.find(usr); for(User user:users){ System.out.println(user); } }
- <trim prefix="" prefixOverrides=""/suffixOverrides="">:trim标记是一个格式化的标记,可以完成set或者是where标记的功能。
编写.xml
//用 trim 改写上面的where choose,when,otherwise语句 <select id="find" resultType="com.qcby.entity.User" > select * from user <trim prefix="where" prefixOverrides="and|or"> <!-- prefix:前缀;prefixoverride:去掉第一个and或者是or --> <!-- 等价于<where>标签 --> <choose> <when test="id !='' and id != null"> id=#{id} </when> <when test="username !='' and username != null"> and username=#{username} </when> <otherwise> and sex=#{sex} </otherwise> </choose> </trim> </select> //用 trim 改写上面第三点的 if+set 语句 <update id="update" parameterType="com.qcby.entity.User"> update user <trim prefix="set" suffixOverrides=","> <!-- prefix:前缀;suffixOverrides:去掉最后一个,号 --> <!-- 等价于<set>标签 --> <if test="username != null and username != ''"> username = #{username}, </if> <if test="sex != null and sex != ''"> sex = #{sex}, </if> </trim> where id = #{id} </update>
编写UserDao
public interface UserDao { //查询 public List<User> find(User user); //修改 public List<User> update(User user); }
编写UserTest
@Test public void find(){ User usr=new User(); //usr.setUsername("AGi"); usr.setSex("男"); //usr.setId(1); List<User> users=mapper.find(usr); for(User user:users){ System.out.println(user); } } //修改 @Test public void update(){ User user=new User(); user.setUsername("AGi"); //user.setSex("男"); user.setId(1); int flag= mapper.update(user); session.commit(); System.out.println(flag); }