基于注解的时候没有xxxMapper.xml文件
要把配置文件中要改:
<mappers>
<mapper class="dao.IPerson"></mapper>
</mappers>
基于注解的 一对一查询
package dao;
import domain.Idcard;
import domain.Person;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import java.util.List;
public interface IPerson {
/*基于注解的方式*/
//根据人的id查询人的信息+身份证号 自定义规则查询
@Select("select * from person where pid=#{pid}")
@Results( /*<resultMap id="selectPerson" type="domain.Person">*/
id = "selectOne",
value ={
/*字段名,id为true时表示是主键*/
@Result(property="pid",column ="pid",id = true),
@Result(property="pname",column ="pname"),
// <association property="idcard" javaType="domain.Idcard"
// column="cardid" select="selectIdcardId"></association>
//javaType可以省略
@Result(property ="idCard",javaType = Idcard.class,column = "cardid",
one =@One(select = "selectCard",
fetchType = FetchType.LAZY//fetchType相当于setting设置第二个对象为懒加载机制
))
}
)
public Person selectOne(Integer pid);
//第二次查询的方法
@Select("select * from idcard where cardid=#{cardid}")
public Idcard selectCard(int cardid);
//查询多条记录
@Select("select * from person")
@ResultMap("selectOne")//返回值类型,在返回多条记录使用,和results中的id对应
public List<Person> selectAll();
}
基于注解的一对多查询
package dao;
import domain.Dept;
import domain.Emp;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import java.util.List;
public interface IDept {
//查询部门下的员工信息
@Select("select * from dept where deptno=#{deptno}")
@Results(
id = "selectDept",
value = {
@Result(property = "deptno",column = "deptno",id = true),
@Result(property = "dname",column = "dname"),
@Result(property = "loc",column = "loc"),
@Result(property = "empList",column = "deptno",
many=@Many(select = "selectEmp"))
}
)
public List<Dept> selectOne(int deptno);
//辅助方法
@Select("select * from emp where deptno=#{deptno}")
@ResultMap("selectDept")
public Emp selectEmp(int deptno);
}
基于注解的多对多查询
注:tea_stu:存学生编号和老师编号,是一个中间表
package dao;
import domain.Student;
import domain.Teacher;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface IStudent {
//通过id查询一个学生的信息+学生所上课程的所有老师信息
@Results(
id = "selectStudent",
value = {
@Result(property = "sid",column = "sid",id = true),
@Result(property = "sname",column = "sname"),
@Result(property = "ssex",column = "ssex"),
@Result(property = "sage",column = "sage"),
@Result(property = "teacherList",column = "sid",
javaType = List.class,many=@Many(select = "selectTeacher")),
}
)
@Select("select * from student where sid=#{sid}")
public Student selectOne(int sid);
//辅助方法,负责通过学生的id找到teacher
@Select("select t.* from tea_stu ts inner join teacher t on ts.tis=t.tid where ts.sid=#{sid}")
public Teacher selectTeacher(int sid);
//负责查询所有学生+学过课程的所有老师
@Select("select * from student")
@ResultMap("selectStudent")
public List<Student> selectAll();
}
基于xxxMapper.xml文件的一对一查询
<?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="dao.PersonDao">
<!--方法一:采用两次查询的方法-->
<!-- <resultMap id="selectPerson" type="domain.Person">
<id property="pid" column="pid"></id>
<result property="pname" column="pname"></result>
<association property="idcard" javaType="domain.Idcard" column="cardid" select="selectIdcardId"></association>
</resultMap>
<select id="selectIdcardId" resultType="domain.Idcard">
select * from idcard where cardid=#{idcard}
</select>
<select id="selectOne" resultMap="selectPerson">
select pid,pname,cardid from person where pid=#{pid};
</select>
<select id="selectAll" resultMap="selectPerson">
select pid,pname,cardid from person;
</select>
-->
<!--方法二:采用多表联合查询的语句查询,如:等值连接 内连接 外连接-->
<resultMap id="selectPerson" type="domain.Person">
<id property="pid" column="pid"></id>
<result property="pname" column="pname"></result>
<association property="idcard" javaType="domain.Idcard">
<id property="cardid" column="cardid"></id>
<result property="address" column="address"></result>
</association>
</resultMap>
<select id="selectOne" resultMap="selectPerson">
select p.pid,p.pname,i.cardid from person p inner join idcard i on p.cardid=i.cardid where p.pid=#{pid}
</select>
<select id="selectAll" resultMap="selectPerson">
select p.pid,p.pname,i.cardid,i.address from person p inner join idcard i on p.cardid=i.cardid
</select>
</mapper>
基于xxxMapper.xml文件的一对多查询
<?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="dao.DeptDao">
<!--<resultMap id="selectDept" type="domain.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<!–如果属性为集合则用collection–>
<collection property="empList" javaType="list" ofType="domain.Emp"
select="selectEmp" column="deptno"></collection>
</resultMap>
<select id="selectEmp" resultType="domain.Emp">
select empno,ename,sal,deptno from emp where deptno=#{deptno}
</select>
<select id="selectOne" resultMap="selectDept">
select deptno,dname,loc from dept where deptno=#{deptno}
</select>
<select id="selectAll" resultMap="selectDept">
select deptno,dname,loc from dept
</select>-->
<!--方式二,联合查询-->
<resultMap id="selectDept" type="domain.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<!--collection相当于一个对象,然后给对象进行赋值-->
<collection property="empList" javaType="list" ofType="domain.Emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="sal" column="sal"></result>
</collection>
</resultMap>
<select id="selectOne" resultMap="selectDept">
select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from dept d left join emp e on e.deptno=d.deptno where d.deptno=#{deptno}
</select>
<select id="selectAll" resultMap="selectDept">
select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from dept d left join emp e on e.deptno=d.deptno
</select>
</mapper>
基于xxxMapper.xml文件的多对多查询
<?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="dao.TeacherDao">
<!--方式一 先查老师再查学生 但是第二次查询学生信息的时候需要两张表联合-->
<!-- <resultMap id="selectTeacher" type="domain.Teacher">
<id property="tid" column="tid"></id>
<result property="tname" column="tname"></result>
<result property="tsex" column="tsex"></result>
<result property="tage" column="tage"></result>
<collection property="studentList" javaType="list" ofType="domain.Student"
select="selectTeacherForStudent" column="tid"></collection>
</resultMap>
<select id="selectTeacherForStudent" resultType="domain.Student">
select s.* from tea_stu ts inner join student s on ts.sid=s.sid where ts.tid=#{tid}
</select>
<select id="selectOne" resultMap="selectTeacher">
select tid,tname,tsex,tage from teacher where tid=#{tid}
</select>-->
<!--方式二 一次三表联合查询 将老师和学生信息都获取到了 可以直接赋值,不需要再查询-->
<resultMap id="selectTeacher" type="domain.Teacher">
<id property="tid" column="tid"></id>
<result property="tname" column="tname"></result>
<result property="tsex" column="tsex"></result>
<result property="tage" column="tage"></result
<collection property="studentList" javaType="list" ofType="domain.Student">
<id property="sid" column="sid"></id>
<result property="sname" column="sname"></result>
<result property="ssex" column="ssex"></result>
<result property="sage" column="sage"></result>
</collection>
</resultMap>
<select id="selectOne" resultMap="selectTeacher">
select t.*,s.* from teacher t inner join tea_stu ts on t.tid=ts.tid
inner join student s on ts.sid=s.sid where t.tid=#{tid}
</select>
</mapper>