Mybatis注解查询

康弟弟2024-05-032025-09-03

单表查询

1、创建实体类

例如：存在一个实体Student

public class Student {
    private String sno;
    private String sname;
    private String ssex;
    private String snative;
    private int mno;
    // 省略了getter、setter和toString方法
}

2、创建持久层

则可以直接创建一个持久层对象（Dao）或者（Mapper），本文全部采用Dao文件

public interface StudentDao {
    @Select("select * from stu where sno=#{sno};")
    Student findStudentBySno(String sno);
    @Select("select * from stu;")
    List<Student> findStudentAll();
    @Select("select * from stu where sname like '%${sname}%';")
    List<Student> findStudentBySname(String sname);
    @Insert("insert into stu(sno,sname,ssex,snative,mno) values(#{sno},#{sname},#{ssex},#{snative},#{mno});")
    int insertStudent(Student student);
    @Update({"update stu set sno=#{sno},sname=#{sname},ssex=#{ssex},snative=#{snative},mno=#{mno}"," where sno=#{sno};"})
    int updateStudent(Student student);
    @Delete("delete from stu where " + "sno=#{sno};")
    int deleteStudentBySno(String sno);
}

3、对配置文件进行扫描配置

注意需要将包扫描路径添加或者使用@Mapper注解（如果是Idea在不取别名的情况下需要写完全包名（com.xxx.xxx））

1
2
3

<mappers>
    <mapper class="dao.StudentDao"/>
</mappers>

4、逻辑测试文件

以上完成所有的逻辑书写，下面创建测试文件

package test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import Dao.StudentDao;
import pojo.Student;
import utils.MybattisUtils;

public class StudentDaoTest {
    @Test
    public void findStudentBySnoTest(){
        System.out.println("findStudentBySnoTest:");
        SqlSession sqlSession = MybattisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
        Student student = studentDao.findStudentBySno("100000001");
        System.out.println(student);
        sqlSession.close();
    }

    @Test
    public void findStudentAllTest(){
        System.out.println("findStudentAllTest:");
        SqlSession sqlSession = MybattisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);

        List<Student> studentList = studentDao.findStudentAll();
        System.out.println(studentList);
        sqlSession.close();
    }

    @Test
    public void findStudentBySnameTest(){
        System.out.println("findStudentBySnameTest:");
        SqlSession sqlSession = MybattisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);

        List<Student> studentList = studentDao.findStudentBySname("小");
        System.out.println(studentList);
        sqlSession.close();
    }

    @Test
    public void insertStudentTest(){
        System.out.println("insertStudentTest:");
        SqlSession sqlSession = MybattisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);

        Student student = new Student();
        student.setSno("888888888");
        student.setSname("张思源");
        student.setSsex("男");
        student.setSnative("广东广州");
        student.setMno(1);
        int lines = studentDao.insertStudent(student);
        sqlSession.commit();
        sqlSession.close();
        System.out.println("成功插入了" + lines + "条记录");
    }

    @Test
    public void updateStudentTest(){
        System.out.println("updateStudentTest:");
        SqlSession sqlSession = MybattisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);

        Student student = new Student();
        student.setSno("888888888");
        student.setSname("张思霞");
        student.setSsex("女");
        student.setSnative("广东佛山");
        student.setMno(1);
        int lines = studentDao.insertStudent(student);
        sqlSession.commit();
        sqlSession.close();
        System.out.println("成功更新了" + lines + "条记录");
    }

    @Test
    public void deleteStudentBySnoTest(){
        System.out.println("deleteStudentBySnoTest:");
        SqlSession sqlSession = MybattisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);

        int lines = studentDao.deleteStudentBySno("888888888");
        sqlSession.commit();
        sqlSession.close();
        System.out.println("成功删除了" + lines + "条记录");
    }
}

5、测试结果

其中测试结果如下：

findStudentAllTest:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 215759534.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - ==>  Preparing: select * from stu; 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 4
[Student [sno=100000001, sname=尚小云, ssex=女, snative=广东广州, mno=1, cardid=1, idcard=null], Student [sno=100000002, sname=廖时飞, ssex=男, snative=广东梅州, mno=1, cardid=2, idcard=null], Student [sno=100000003, sname=宋凌枫, ssex=男, snative=湖南郴州, mno=2, cardid=0, idcard=null], Student [sno=100000004, sname=刘小纳, ssex=女, snative=广东佛山, mno=2, cardid=0, idcard=null]]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - Returned connection 215759534 to pool.
findStudentBySnoTest:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Checked out connection 215759534 from pool.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - ==>  Preparing: select * from stu where sno=?; 
DEBUG [main] - ==> Parameters: 100000001(String)
DEBUG [main] - <==      Total: 1
Student [sno=100000001, sname=尚小云, ssex=女, snative=广东广州, mno=1, cardid=1, idcard=null]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - Returned connection 215759534 to pool.
insertStudentTest:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Checked out connection 215759534 from pool.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - ==>  Preparing: insert into stu(sno,sname,ssex,snative,mno) values(?,?,?,?,?); 
DEBUG [main] - ==> Parameters: 888888888(String), 张思源(String), 男(String), 广东广州(String), 1(Integer)
DEBUG [main] - <==    Updates: 1
DEBUG [main] - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - Returned connection 215759534 to pool.
成功插入了1条记录
updateStudentTest:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Checked out connection 215759534 from pool.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cdc3aae]
DEBUG [main] - ==>  Preparing: insert into stu(sno,sname,ssex,snative,mno) values(?,?,?,?,?); 
DEBUG [main] - ==> Parameters: 888888888(String), 张思霞(String), 女(String), 广东佛山(String), 1(Integer)
deleteStudentBySnoTest:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 557725225.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@213e3629]
DEBUG [main] - ==>  Preparing: delete from stu where sno=?; 
DEBUG [main] - ==> Parameters: 888888888(String)

一对一的内连接查询

1、创建实体类

这里使用学生表和学生信息卡两张表作为演示（一个学生只能拥有一张学生卡）

其中需要注意创建一个另一张表的对象idcard用来存放另一张表中的对应关系

学生实体：

public class Student {
    private String sno;
    private String sname;
    private String ssex;
    private String snative;
    private int mno;
    private int cardid;
    private IdCard idcard;
}
//省略get、set等方法

学生信息卡实体：

public class IdCard {
	private int id;
	private String cardno;
	private String cardname;
}
//省略get、set等方法

2、创建持久层

同上可以创建Dao或者Mapper，以下不在赘述。

这里主要采用join on内连接查询

学生信息卡层（IdCardDao）：

public interface IdCardDao {
	@Select("select * from idcard where id=#{id}")
	IdCard selectIdCardByid(int id);
}

学生关联查询层（StudentCardDao）：

public interface StudentCardDao {
	@Select("SELECT * FROM stu JOIN idcard ON stu.cardid = idcard.id WHERE sno =#{sno}")
	@Results({@Result(column = "id", property = "idcard", one = @One(select = "Dao.IdCardDao.selectIdCardByid"))})
	Student finStudentidcardbysno(String sno);
}

3、对配置文件进行扫描配置

这里为了简化扫描类的书写，采用包扫描的方式，在mybatis.xml中添加添加以下代码：

1
2
3

<mappers>
      <package name="mapper"></package>
  </mappers>

或者使用Mapper创建持久层时：

1
2
3

<mappers>
      <package name="Dao"></package>
  </mappers>

4、逻辑测试文件

测试单表查询idcard查询

public class StudentCardTest {
	@Test
	public void findcord() {
		System.out.println("findStudentCardBySno:");
		SqlSession sqlSession = MybattisUtils.getSession();
		IdCardDao idCardDao = sqlSession.getMapper(IdCardDao.class);
		IdCard idCard = idCardDao.selectIdCardByid(1);
		System.out.println(idCard);
		sqlSession.close();
	}
}

测试join in内查询（一对一查询）

public class StudentCardTest {
	@Test
	public void findStudentCardBySno() {
		System.out.println("findStudentCardBySno:");
		SqlSession sqlSession = MybattisUtils.getSession();
		StudentCardDao studentCardDao = sqlSession.getMapper(StudentCardDao.class);
		Student stu = studentCardDao.finStudentidcardbysno("100000001");
		System.out.println(stu);
		sqlSession.close();
	}
}

5、测试结果

DEBUG [main] - ==>  Preparing: select * from idcard where id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
idcard [id=1, cardno=120154199908162247, cardname=idcard]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@31024624]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@31024624]
DEBUG [main] - Returned connection 822232612 to pool.
findStudentCardBySno:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Checked out connection 822232612 from pool.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@31024624]
DEBUG [main] - ==>  Preparing: SELECT * FROM stu JOIN idcard ON stu.cardid = idcard.id WHERE sno =? 
DEBUG [main] - ==> Parameters: 100000001(String)
DEBUG [main] - ====>  Preparing: select * from idcard where id=? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 1
Student [sno=100000001, sname=尚小云, ssex=女, snative=广东广州, mno=1, cardid=1, idcard=idcard [id=1, cardno=120154199908162247, cardname=idcard]]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@31024624]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@31024624]
DEBUG [main] - Returned connection 822232612 to pool.

6、思考：

使用注解式@One进行一对一的查询如何实现。

一对多注解型查询

1、实体类

这里举例查询某一门课程的所有成绩信息

课程实体（Course）

public class Course {
	private int cno;
	private String cname;
	private int period;
	private int bookid;
	private List<Sc> ScList;
}
//省略get、set等方法

选课结果表，其中包含成绩字段（Sc）

public class Sc {
    private String sno;
    private int cno;
    private int tno;
    private float participation;
    private float sfinal;
    private float total;
}
//省略get、set等方法

2、持久层

选课表持久层(ScDao):

public interface ScDao {
	@Select("select * from sc where cno=#{cno}")
    List<Sc> findScByCno(String cno);
}

通过课程号查询所有选课信息持久层（CourseScDao）：

public interface CourseScDao {
	@Select("select * from course where cno=#{cno};")
    @Results(
    { @Result(property = "cno",column = "cno"),
    	@Result(column = "cno", property = "ScList", 
            many = @Many(select = "dao.ScDao.findScByCno"))})
    Course findCourseScBycno(int cno);
}

3、配置扫描

参考以上

4、逻辑测试

public class CourseScTest {
	@Test
	public void findCourseScByCon() {
		System.out.println("findCourseScByCon:");
		SqlSession sqlSession = MybattisUtils.getSession();
		CourseScDao courseScDaio = sqlSession.getMapper(CourseScDao.class);
		Course course = courseScDaio.findCourseScBycno(1);
		System.out.println(course);
		sqlSession.close();
	}
}

5、测试结果

DEBUG [main] - ==>  Preparing: select * from course where cno=?; 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - ====>  Preparing: select * from sc where cno=? 
DEBUG [main] - ====> Parameters: 1(String)
DEBUG [main] - <====      Total: 2
DEBUG [main] - <==      Total: 1
Course [cno=1, cname=高等数学, period=80, bookid=1, ScList=[Sc [sno=100000001, cno=1, tno=1, participation=80.0, sfinal=0.0, total=85.0], Sc [sno=100000002, cno=1, tno=1, participation=78.0, sfinal=0.0, total=80.0]]]

6、总结分析

在测试结果中查询出Sc表的中的sfinal元素无法自动映射到数据库表字段final中，由于final作为关键字无法在实体类时创建，使用@Column(name=”final”)对实体中的sfinal进行手动映射时编译器无法识别，在手动导入org.apache.ibatis.annotations.Column;包后无法解析到包中文件，这里思考通过@Param注解尝试。

多对多注解查询

1、实体类

这里使用某一门课程的所有学生信息,粗看认为这是一对多查询，课程表中只包括课程号和课程信息，学生表中只包含学生信息，两张表中存在多个数据，唯一的关联关系位于选课表sc中，其中包含cno和sno两个字段将两张表链接，可以认为是作为多对多查询中需要提供的另一张表。

课程信息实体（Course）：

public class Course {
	private int cno;
	private String cname;
	private int period;
	private int bookid;
	private List<Student> stuList;
}
//省略get、set等方法

学生实体类（Student）:

public class Student {
    private String sno;
    private String sname;
    private String ssex;
    private String snative;
    private int mno;
    private List<Course> courseList;
    }
//省略get、set等方法

选课信息表（Sc）:

public class Sc {
    private String sno;
    private int cno;
    private int tno;
    private float participation;
    private float sfinal;
    private float total;
    }
//省略get、set等方法

2、持久层

这里持久层的创建尤其为关键

public interface StudentDao {
	@Select("select * from stu where sno in (select sno from sc where cno=#{cno});")
    List<Student> findStudentByCno(String cno);
}

public interface CourseStudentDao {
	@Select("select * from course where cno=#{cno};")
    @Results(
    		{@Result(column = "cno", property = "cno"),
    	@Result(column = "cno", property = "stuList", many = @Many(select = "dao.StudentDao.findStudentByCno"))})
    Course findStudentCourseByCno(String cno);
}

先对关联查询进行分析（CourseStudentDao）：

需要查询某一门课程的所有学生信息，首先执行select语句，将传入的cno查询到相关的Course信息

1	@Select("select * from course where cno=#{cno};")

在通过关联查询其中cno传入dao.StudentDao.findStudentByCno，并且将结果封装到stuLis中。

1
2
3

@Results(
    		{@Result(column = "cno", property = "cno"),
    	@Result(column = "cno", property = "stuList", many = @Many(select = "dao.StudentDao.findStudentByCno"))}

然后执行dao.StudentDao.findStudentByCno中的方法，其中cno在上述中传递进来，首先执行where语句中的查询，通过查询出的sno作为where条件查询出所有的stu中的信息传回给stuList，而在实体中stuList泛型被定义为了Student类型进行了自动封装。

1	@Select("select * from stu where sno in (select sno from sc where cno=#{cno});")

3、配置扫描

同上

4、逻辑测试

public class CourseStudentTest {
	   @Test
	    public void CourseStudentTest(){
	        System.out.println("CourseStudentTest:");
	        SqlSession sqlSession = MybattisUtils.getSession();
	        CourseStudentDao coursestudentdao = sqlSession.getMapper(CourseStudentDao.class);
	        Course course = coursestudentdao.findStudentCourseByCno("1");
	        System.out.println(course);
	        sqlSession.close();
	    }
}

5、测试结果

DEBUG [main] - ==>  Preparing: select * from course where cno=?; 
DEBUG [main] - ==> Parameters: 1(String)
DEBUG [main] - ====>  Preparing: select * from stu where sno in (select sno from sc where cno=?); 
DEBUG [main] - ====> Parameters: 1(String)
DEBUG [main] - <====      Total: 2
DEBUG [main] - <==      Total: 1
Course [cno=1, cname=高等数学, period=80, bookid=1, stuList=[Student [sno=100000001, sname=尚小云, ssex=女, snative=广东广州, mno=1, courseList=null], Student [sno=100000002, sname=廖时飞, ssex=男, snative=广东梅州, mno=1, courseList=null]]]

6、总结

多对多查询在类型上也可以看作一对多的查询