java5表联查_一对一联表查询

一：

1.需求说明

根据班级id查询班级信息(带教师信息)

一个老师带一个班级。

2.新建数据库

老师表

班级表

1 CREATE TABLETEACHER(2 t_id INT PRIMARY KEYAUTO_INCREMENT,3 t_name VARCHAR(20)4 );5 CREATE TABLECLASS(6 c_id INT PRIMARY KEYAUTO_INCREMENT,7 c_name VARCHAR(20),8 teacher_id INT

9 );10 ALTER TABLE CLASS ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCESTEACHER(t_id);11 INSERT INTO teacher(T_NAME) VALUES('LS1');12 INSERT INTO teacher(T_NAME) VALUES('LS1');13 INSERT INTO CLASS(c_name,teacher_id) VALUES('bj1',1);14 INSERT INTO CLASS(c_name,teacher_id) VALUES('bj2',2);

3.新建实体类--Teacher.java

1 packagecom.jun.bean;2

3 public classTeacher {4 //5 private intid;6 privateString name;7 //8 publicTeacher() {}9 public Teacher(intid,String name) {10 this.id=id;11 }12 public intgetId() {13 returnid;14 }15 public void setId(intid) {16 this.id =id;17 }18 publicString getName() {19 returnname;20 }21 public voidsetName(String name) {22 this.name =name;23 }24 @Override25 publicString toString() {26 return "Teacher [id=" + id + ", name=" + name + "]";27 }28

29 }

4.新建实体类--Classes.java(使用Class会出错)

1 packagecom.jun.bean;2

3 public classClasses {4 //5 private intid;6 privateString name;7 privateTeacher teacher;8 //9 publicClasses() {}10 public Classes(intid,String name,Teacher teacher) {11 this.id=id;12 this.name=name;13 this.teacher=teacher;14 }15 public intgetId() {16 returnid;17 }18 public void setId(intid) {19 this.id =id;20 }21 publicString getName() {22 returnname;23 }24 public voidsetName(String name) {25 this.name =name;26 }27 publicTeacher getTeacher() {28 returnteacher;29 }30 public voidsetTeacher(Teacher teacher) {31 this.teacher =teacher;32 }33 @Override34 publicString toString() {35 return "Class [id=" + id + ", name=" + name + ", teacher=" + teacher + "]";36 }37

38 }

二：联表查询

1.目录

2.新建映射文件class.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

5

6

10

11 select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}12

13

14

15

16

17

18

19

20

21

3.配置文件Configuration.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

4.测试类

1 packagecom.jun.main;2

3 importjava.io.IOException;4 importjava.io.Reader;5

6 importorg.apache.ibatis.io.Resources;7 importorg.apache.ibatis.session.SqlSession;8 importorg.apache.ibatis.session.SqlSessionFactory;9 importorg.apache.ibatis.session.SqlSessionFactoryBuilder;10 importorg.junit.Test;11 importcom.jun.bean.Classes;12 public classMainTest {13 @Test14 public void test1() throwsException {15 Reader reader=Resources.getResourceAsReader("com/jun/config/Configuration.xml");16 SqlSessionFactory sqlSessionFactory=newSqlSessionFactoryBuilder().build(reader);17 SqlSession sqlSession=sqlSessionFactory.openSession(true); //true后是自动提交

18 String statement="one2one.selectClass";19 Classes classes=sqlSession.selectOne(statement, 1);20 System.out.println(classes);21 sqlSession.close();22 }23 }

5.结果

三：两次查询

1.目录结构

在原有的基础上继续增加方法

2.新建映射文件class.xml

在方式一的基础上增加了方式二。

方式二中需要的注意点是字段名与属性名不一致的解决方式，使用别名的处理方式。

1 <?xml version="1.0" encoding="UTF-8"?>

2 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

5

6

10

11 select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}12

13

14

15

16

17

18

19

20

21

22

27

28 select * from class where c_id=#{id}29

30

31 select t_id id,t_name name from teacher where t_id=#{id}32

33

34

35

36

37

38

3.配置文件--Configuration.xml

这个不用改变

4.测试类

1 packagecom.jun.main;2

3 importjava.io.IOException;4 importjava.io.Reader;5

6 importorg.apache.ibatis.io.Resources;7 importorg.apache.ibatis.session.SqlSession;8 importorg.apache.ibatis.session.SqlSessionFactory;9 importorg.apache.ibatis.session.SqlSessionFactoryBuilder;10 importorg.junit.Test;11 importcom.jun.bean.Classes;12 public classMainTest {13 /**

14 * 方式一15 *@throwsException16 */

17 @Test18 public void test1() throwsException {19 Reader reader=Resources.getResourceAsReader("com/jun/config/Configuration.xml");20 SqlSessionFactory sqlSessionFactory=newSqlSessionFactoryBuilder().build(reader);21 SqlSession sqlSession=sqlSessionFactory.openSession(true); //true后是自动提交

22 String statement="one2one.selectClass";23 Classes classes=sqlSession.selectOne(statement, 1);24 System.out.println(classes);25 sqlSession.close();26 }27 /**

28 * 方式二29 */

30 @Test31 public void test2() throwsException {32 Reader reader=Resources.getResourceAsReader("com/jun/config/Configuration.xml");33 SqlSessionFactory sqlSessionFactory=newSqlSessionFactoryBuilder().build(reader);34 SqlSession sqlSession=sqlSessionFactory.openSession(true); //true后是自动提交

35 String statement="one2one.selectClass2";36 Classes classes=sqlSession.selectOne(statement, 2);37 System.out.println(classes);38 sqlSession.close();39 }40 }

5.测试结果