数据库结构、实体类
public class Youth {private Integer id;private String username;private Date birthday;private Character sex;private String address;private Integer age;public Youth(Integer id, String username, Date birthday, Character sex, String address) {this.id = id;this.username = username;this.birthday = birthday;this.sex = sex;this.address = address;}public Youth() {}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public Character getSex() {return sex;}public void setSex(Character sex) {this.sex = sex;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}@Overridepublic String toString() {return "Youth{" +"id=" + id +", username='" + username + '\'' +", birthday=" + birthday +", sex=" + sex +", address='" + address + '\'' +", age=" + age +'}';}
}
where-if
where-if多用于查询含有某个特征语句上 如果 where标签内的条件成立 它会自动去掉条件语句开头多余的 and 或者 or
它会通过判断是否符合test中的属性来进行选择性执行 会将满足此条件的内置语句进行嵌入到sql语句中进行执行
在接口文件中写入
List<Youth> getYouthBywhereif(Youth youth);
在映射文件中写入
<select id="getYouthBywhereif" resultType="youth">select * from youth<where><if test=" username != null and username !='' ">and username = #{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><if test="age!=null">and age = #{age}</if></where></select>
测试类中写入
@Testpublic void getYouthBywhereifTest(){Youth youth = new Youth();youth.setSex('男');youth.setAddress("北京");List<Youth> youths = youthMapper.getYouthBywhereif(youth);for(Youth y:youths){System.out.println(y);}}
执行结果为
set-if
set-if多用于选择性修改操作上 它会自动去掉条件语句结尾多余的 ,
在映射文件中写入
<update id="updateYouthBysetif" parameterType="youth">update youth<set><if test="username!=null and username!=''">username = #{username},</if><if test="birthday!=null">birthday =#{birthday},</if><if test="sex!=null and sex!=''">sex = #{sex},</if><if test="address!=null and address!=''">address=#{address},</if><if test="age!=null">age = #{age}</if></set><where>id=#{id}</where></update>
在测试类中写入
@Testpublic void updateYouthBysetifTest(){Youth youth = new Youth(4,"张三", new Date(2019 - 1900,10 - 1,9),'男',"北京");int i = youthMapper.updateYouthBysetif(youth);if(i>0)System.out.println("修改成功");elseSystem.out.println("修改失败");}
执行结果为
特别须知
- 在test中非字符串类型 不可使用 属性 != ' '
- 建议将实体类的属性使用包装类进行修饰 因为非包装类不能使用 类似于age != null 的格式