mybatis 的一对多一对一 还有动态sql
一对多查询
老师和学生：一个老师对应多个学生
package com.example.tkmappertest.vo; import com.example.tkmappertest.entity.Student; import com.example.tkmappertest.entity.Teacher; import javax.persistence.Column; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import java.util.ArrayList; import java.util.Date; import java.util.List; public class TeacherAndStudentVo { private int id; private String tname; private int tage; private Date createTime; //StudentVo这里面的id和外面的id不能相同要不就只能查询出来一条记录 private List<StudentVo> listStu=new ArrayList<>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public int getTage() { return tage; } public void setTage(int tage) { this.tage = tage; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public List<StudentVo> getListStu() { return listStu; } public void setListStu(List<StudentVo> listStu) { this.listStu = listStu; } }
跟据老师的id查出多个学生直接封装成复杂的实体类
<resultMap id="resultMapName" type="com.example.tkmappertest.vo.TeacherAndStudentVo"> <id property="id" column="id"/> <result column="tname" property="tname"/> <result column="tage" property="tage"/> <result column="create_time" property="createTime"/> <collection property="listStu" ofType="com.example.tkmappertest.vo.StudentVo"> <id property="sid" column="sid"/> <result column="name" property="name"/> <result column="age" property="age"/> <result column="create_time" property="createTime"/> <result column="t_id" property="tId"/> </collection> </resultMap> <select id="selectAllByTid" parameterType="int" resultMap="resultMapName"> select t.id, t.tname, t.tage, t.create_time, s.id as sid, s.name, s.age, s.create_time, s.t_id from teacher t left join student s on t.id=s.t_id where t.id=#{tid} </select>
嵌套查询：
package com.example.tkmappertest.vo; import com.example.tkmappertest.entity.Student; import com.example.tkmappertest.entity.Teacher; import javax.persistence.Column; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import java.util.ArrayList; import java.util.Date; import java.util.List; public class TeacherAndStudentVo { private int id; private String tname; private int tage; private Date createTime; //StudentVo这里面的id和外面的id不能相同要不就只能查询出来一条记录 private List<StudentVo> listStu=new ArrayList<>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public int getTage() { return tage; } public void setTage(int tage) { this.tage = tage; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public List<StudentVo> getListStu() { return listStu; } public void setListStu(List<StudentVo> listStu) { this.listStu = listStu; } }
<resultMap id="resultMapName2" type="com.example.tkmappertest.vo.TeacherAndStudentVo"> <id property="id" column="id"/> <result column="tname" property="tname"/> <result column="tage" property="tage"/> <result column="create_time" property="createTime"/> <!--column="id" 说明把上个id的值传到下个sql里面当参数 column="{id=id,name=tname,age=tage}" 这个是可以把上个sql的多个值传入下个sql当参数相当于map select="selectStuById" 这个是指定下个sql的查询语句 --> <collection property="listStu" column="id" select="selectStuById" ofType="com.example.tkmappertest.vo.StudentVo"> <result column="sid" property="sid"/> <result column="name" property="name"/> <result column="age" property="age"/> <result column="create_time" property="createTime"/> <result column="t_id" property="tId"/> </collection> </resultMap> <!--这个是第一个sql查询老师的信息--> <select id="selectAllByTid2" parameterType="int" resultMap="resultMapName2"> select t.id, t.tname, t.tage, t.create_time from teacher t where t.id=#{tid} </select> <!--这个是第二个查询的信息 查询这个老师下面所有的学生--> <select id="selectAllByTid2" parameterType="int" resultMap="resultMapName2"> select t.id, t.tname, t.tage, t.create_time from teacher t where t.id=#{tid} </select>
动态sql
<insert id="insertIntoSelect" parameterType="com.example.tkmappertest.entity.Student"> insert into student <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id!='' and id!=null"> id, </if> <if test="name!='' and name!=null"> name, </if> <if test="age!='' and age!=null"> age, </if> <if test="createTime!=null"> create_time, </if> <if test="tId!=null and tId!=''"> t_id, </if> </trim> values <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id!='' and id!=null"> #{id}, </if> <if test="name!='' and name!=null"> #{name}, </if> <if test="age!='' and age!=null"> #{age}, </if> <if test="createTime!=null"> #{createTime}, </if> <if test="tId!=null and tId!=''"> #{tId}, </if> </trim> </insert>
trim的用法：
<trim prefix="(" suffix=")" suffixOverrides=",">
prefix就前缀 suffix 是后缀
suffixOverrides就是用逗号进行分隔
foreach的用法
<insert id="insertAll" parameterType="com.example.tkmappertest.entity.Student"> insert into student(id,name,age,create_time,t_id) values <foreach collection="list" item="stu" index="index" separator="," > (#{stu.id},#{stu.name},#{stu.age},#{stu.createTime},#{stu.tId}) </foreach> </insert>
<foreach collection="list" item="stu" index="index" separator="," >
collection 说明是集合 如果只传个list这里面就写list 如果传过来的是个map那这就写map的健 如果加了@Param("") 这个注解那么这里面是什么collection就是什么
item 是从集合中拿出来的单个的对象 如果是字符串就可以直接#{item的名} 直接取如果是对象的话就得#{item的名.属性名}取
separator是用什么分隔两次循环
解决重复插入的问题：
<insert id="insertByName" parameterType="com.example.tkmappertest.entity.Student"> insert into student(id,name,age,create_time) select #{id}, #{name}, #{age}, #{createTime} from dual where not exists(select 1 from student where name=#{name}) </insert>
