动态SQL
if和where的使用
<select id="findStudentList" parameterType="studentQuery" resultType="student">
select * from student
<where>
<if test="student != null">
<if test="student.name != null and student.name!=''">
name = #{student.name}
</if>
<if test="student.address != null and student.address!=''">
And address like '%${student.address}'
</if>
</if>
</where>
</select>
声明一个SQL片段,可以提高SQL的重用性
<sql id = 'select_student'>
<if test="student != null">
<if test="student.name != null and student.name!=''">
name = #{student.name}
</if>
<if test="student.address != null and student.address!=''">
And address like '%${student.address}'
</if>
</if>
</sql>
<select id="findStudentList" parameterType="studentQuery" resultType="student">
select * from student
<where>
<include refid="select_student"></include>
</where>
</select>
foreach遍历
<select id="findStudentList" parameterType="studentQuery" resultType="student">
select * from student
<where>
<if test="ids != null and ids.size > 0 ">
<foreach collection="ids" item="id" open="AND id in (" close=")" separator=",">
${id}
</foreach>
</if>
</where>
</select>
或者
<select id="findStudentList" parameterType="java.util.List" resultType="student">
select * from student
<where>
<if test="ids != null and ids.size > 0 ">
<foreach collection="list" item="id" open="AND id in (" close=")" separator=",">
${id}
</foreach>
</if>
</where>
</select>
关联查询
一对一:
public interface OrdersDao {
public OrdersExt queryOrders(int id);
}
public class Orders {
private Integer id;
private Integer u_id;
private String number;
private Date createtime;
private String note;
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", u_id=" + u_id +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
'}';
}
public Orders() {
}
public Orders(Integer id, Integer u_id, String number, Date createtime, String note) {
this.id = id;
this.u_id = u_id;
this.number = number;
this.createtime = createtime;
this.note = note;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getU_id() {
return u_id;
}
public void setU_id(Integer u_id) {
this.u_id = u_id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
}
package com.hetl.day03.model;
public class OrdersExt extends Orders{
private String username;
private String address;
@Override
public String toString() {
return "OrdersExt{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}'+super.toString();
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD MAPPER 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hetl.day03.dao.OrdersDao">
<sql id="orders">
orders.id,
orders.number,
orders.note,
orders.createtime,
</sql>
<sql id="user">
user.username,
user.address
</sql>
<select id="queryOrders" parameterType="Integer" resultType="ordersExt">
select
<include refid="orders"/>
<include refid="user"/>
from
orders,user
where
orders.u_id = user.id and orders.id = #{?};
</select>
</mapper>