一对一联表查询

一：

1.需求说明

　　根据班级id查询班级信息（带教师信息）

　　一个老师带一个班级。

　　

2.新建数据库

　　老师表

　　班级表

 1 CREATE TABLE TEACHER(
 2     t_id INT PRIMARY KEY AUTO_INCREMENT,
 3     t_name VARCHAR(20)
 4 );
 5 CREATE TABLE CLASS(
 6     c_id INT PRIMARY KEY AUTO_INCREMENT,
 7     c_name VARCHAR(20),
 8     teacher_id INT
 9 );
10 ALTER TABLE CLASS ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES TEACHER(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 package com.jun.bean;
 2 
 3 public class Teacher {
 4     //
 5     private int id;
 6     private String name;
 7     //
 8     public Teacher() {}
 9     public Teacher(int id,String name) {
10         this.id=id;
11     }
12     public int getId() {
13         return id;
14     }
15     public void setId(int id) {
16         this.id = id;
17     }
18     public String getName() {
19         return name;
20     }
21     public void setName(String name) {
22         this.name = name;
23     }
24     @Override
25     public String toString() {
26         return "Teacher [id=" + id + ", name=" + name + "]";
27     }
28     
29 }

 

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

 1 package com.jun.bean;
 2 
 3 public class Classes {
 4     //
 5     private int id;
 6     private String name;
 7     private Teacher teacher;
 8     //
 9     public Classes() {}
10     public Classes(int id,String name,Teacher teacher) {
11         this.id=id;
12         this.name=name;
13         this.teacher=teacher;
14     }
15     public int getId() {
16         return id;
17     }
18     public void setId(int id) {
19         this.id = id;
20     }
21     public String getName() {
22         return name;
23     }
24     public void setName(String name) {
25         this.name = name;
26     }
27     public Teacher getTeacher() {
28         return teacher;
29     }
30     public void setTeacher(Teacher teacher) {
31         this.teacher = teacher;
32     }
33     @Override
34     public String 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 <!DOCTYPE mapper
 3     PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="one2one">
 6     <!-- 
 7         方式一：
 8         select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=1 
 9      -->
10      <select id="selectClass" parameterType="int" resultMap="getClass">
11          select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
12      </select>
13      <resultMap type="com.jun.bean.Classes" id="getClass">
14          <id property="id" column="c_id"/>
15          <result property="name" column="c_name"/>
16          <association property="teacher" javaType="com.jun.bean.Teacher">
17              <id property="id" column="t_id"/>
18              <result property="name" column="t_name"/>
19          </association>
20      </resultMap>
21 </mapper>

 

3.配置文件Configuration.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 3 
 4 <configuration>
 5   <environments default="development">
 6     <environment id="development">
 7       <transactionManager type="JDBC">
 8         <property name="" value=""/>
 9       </transactionManager>
10       <dataSource type="UNPOOLED">
11         <property name="driver" value="com.mysql.jdbc.Driver"/>
12         <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
13         <property name="username" value="root"/>
14         <property name="password" value="123456"/>
15       </dataSource>
16     </environment>
17   </environments>
18 
19    <mappers>
20       <mapper resource="com/jun/sql/config/class.xml"/>
21    </mappers>
22 
23 </configuration>

 

4.测试类

 1 package com.jun.main;
 2 
 3 import java.io.IOException;
 4 import java.io.Reader;
 5 
 6 import org.apache.ibatis.io.Resources;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.apache.ibatis.session.SqlSessionFactory;
 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 import org.junit.Test;
11 import com.jun.bean.Classes;
12 public class MainTest {
13     @Test
14     public void test1() throws Exception {
15         Reader reader=Resources.getResourceAsReader("com/jun/config/Configuration.xml");
16         SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().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 <!DOCTYPE mapper
 3     PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="one2one">
 6     <!-- 
 7         方式一：
 8         select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=1 
 9      -->
10      <select id="selectClass" parameterType="int" resultMap="getClass">
11          select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
12      </select>
13      <resultMap type="com.jun.bean.Classes" id="getClass">
14          <id property="id" column="c_id"/>
15          <result property="name" column="c_name"/>
16          <association property="teacher" javaType="com.jun.bean.Teacher">
17              <id property="id" column="t_id"/>
18              <result property="name" column="t_name"/>
19          </association>
20      </resultMap>
21      
22      <!-- 
23          方式二：
24          select * from class where c_id=1;
25          select * from teacher where t_id=1; //这个1是上面的语句查询出来的结果
26       -->
27       <select id="selectClass2" parameterType="int" resultMap="getClass2">
28           select * from class where c_id=#{id}
29       </select>
30       <select id="getTeacher" parameterType="int" resultType="com.jun.bean.Teacher">
31           select t_id id,t_name name from teacher where t_id=#{id}
32       </select>
33       <resultMap type="com.jun.bean.Classes" id="getClass2">
34           <id property="id" column="c_id"/>
35          <result property="name" column="c_name"/>
36          <association property="teacher" column="teacher_id" select="getTeacher"></association>
37       </resultMap>
38 </mapper>

 

3.配置文件--Configuration.xml

　　这个不用改变

 

4.测试类

 1 package com.jun.main;
 2 
 3 import java.io.IOException;
 4 import java.io.Reader;
 5 
 6 import org.apache.ibatis.io.Resources;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.apache.ibatis.session.SqlSessionFactory;
 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 import org.junit.Test;
11 import com.jun.bean.Classes;
12 public class MainTest {
13     /**
14      * 方式一
15      * @throws Exception
16      */
17     @Test
18     public void test1() throws Exception {
19         Reader reader=Resources.getResourceAsReader("com/jun/config/Configuration.xml");
20         SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().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     @Test
31     public void test2() throws Exception {
32         Reader reader=Resources.getResourceAsReader("com/jun/config/Configuration.xml");
33         SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().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.测试结果

　　

 

转载于:https://www.cnblogs.com/juncaoit/p/8228163.html