创建数据库,数据表
学生表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `age` int(11) DEFAULT NULL, `t_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; INSERT INTO `student` VALUES (1, '张三', '男', 18, 1); INSERT INTO `student` VALUES (2, '李四', '女', 18, 1); INSERT INTO `student` VALUES (3, '王五', '男', 18, 1); INSERT INTO `student` VALUES (4, '小白', '女', 18, 1); INSERT INTO `student` VALUES (5, '小黑', '男', 18, 1); INSERT INTO `student` VALUES (6, '小红', '女', 20, 2); INSERT INTO `student` VALUES (7, '小李', '男', 20, 2); INSERT INTO `student` VALUES (8, '小张', '女', 20, 2); INSERT INTO `student` VALUES (9, '小赵', '男', 20, 2); INSERT INTO `student` VALUES (10, '小王', '女', 20, 2); SET FOREIGN_KEY_CHECKS = 1;
老师表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; INSERT INTO `teacher` VALUES (1, '张老师'); INSERT INTO `teacher` VALUES (2, '李老师'); SET FOREIGN_KEY_CHECKS = 1;
数据关联
表关系:一对一,一对多多对一,多对多,无关系
- 一对一,多对一
查询每个学生的对应的老师
1.按照查询嵌套处理
SELECT s.id,s.Sname,t.Tname FROM student s,teacher t where s.t_id = t.id
编写实体类Student,Teacher以及其对应的get,set,toString方法。
public class Student { private Integer id; private String Sname; private String sex; private Integer age; private Integer t_id; //这个是重点,因为要查询每个学生对应的老师,所以这里要加上老师对象 private Teacher teacher; }
public class Teacher { private Integer id; private String Tname; }
编写StudentDao接口,是因为我们的操作对象是Student类。
public interface StudentDao { }
编写StudentMapper.xml文件,实现sql查询。
<!-- 多对一查询:查询每个学生所对应的老师--> <!--第一种形式:按照查询嵌套处理--> <!-- 1.查询所有的学生信息 2.根据查询出来的t_id,寻找对应的老师 --> <!-- 注意学生实体类当中要有老师类的对象才能实现关联查询--> <!-- resultMap:返回关联的结果映射--> <select id = "getStudent" resultMap="StudentTeacher"> select * from student; </select> <!--结果映射集--> <resultMap id="StudentTeacher" type="com.qcby.entity.Student"> <result property="id" column="id"/> <result property="Sname" column="Sname"/> <result property="sex" column="sex"/> <result property="age" column="age"/> <result property="t_id" column="t_id"/> <!-- 复杂的属性我们需要单独去处理 对象:association 集合:collection --> <!-- property="teacher" student类当中的关联字段 --> <!-- column="t_id" 两个表的关联字段--> <!-- javaType="com.javen.model.Teacher" 为复杂属性设置类类型--> <!-- select="getTeacher" :调用下一个查询语句 --> <association property="teacher" column="t_id" javaType="com.qcby.entity.Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="com.qcby.entity.Teacher"> select * from teacher where id = #{t_id}; <!-- #{t_id}; 可以写任何东西,因为会自动匹配 t_id --> </select>
在StudentDao接口中创建新方法
public interface StudentDao { public List<Student> getStudent(); }
编写StudentTest测试类
public class StudentTest { private InputStream in = null; private SqlSession session = null; private StudentDao mapper = null; @Before //前置通知, 在方法执行之前执行 public void init() throws IOException { //加载主配置文件,目的是为了构建SqlSessionFactory对象 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //创建SqlSessionFactory对象 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //通过SqlSessionFactory工厂对象创建SqlSesssion对象 session = factory.openSession(); //通过Session创建UserDao接口代理对象 mapper = session.getMapper(StudentDao.class); } @After //@After: 后置通知, 在方法执行之后执行 。 public void destory() throws IOException { //释放资源 session.close(); in.close(); } @Test public void getStudent(){ List<Student> students=mapper.getStudent(); for (Student student:students) { System.out.println(student.toString()); } } }
最后需要配置数据库连接文件,并把下段代码放进去
<mapper resource="mapper/StudentMapper.xml"></mapper>
2.按照结果嵌套处理
SELECT * FROM student LEFT JOIN teacher on student.t_id = teacher.id
编写实体类,这里可以不变动,接着使用Student,Teacher类。
编写接口,不变动接着使用StudentDao接口。
编写StudentMapper.xml文件,实现sql查询。
<!-- 按照结果嵌套处理--> <select id="getStudent1" resultMap="StudentTeacher1"> SELECT * FROM student LEFT JOIN teacher on student.t_id = teacher.id </select> <resultMap id="StudentTeacher1" type="com.qcby.entity.Student"> <result property="id" column="id"/> <result property="Sname" column="Sname"/> <result property="sex" column="sex"/> <result property="age" column="age"/> <result property="t_id" column="t_id"/> <association property="teacher" javaType="com.qcby.entity.Teacher"> <result property="id" column="id"/> <result property="Tname" column="Tname"/> </association> </resultMap>
在StudentDao接口中创建新方法
public interface StudentDao { public List<Student> getStudent1(); }
编写StudentTest测试类,把下段代码放到StudentTest里面
@Test public void getStudent1(){ List<Student> students=mapper.getStudent(); for (Student student:students) { System.out.println(student.toString()); } }
- 一对多
查询每个老师有多少学生
1.按照查询嵌套处理
SELECT s.id,s.Sname,t.Tname FROM student s,teacher t where s.t_id = t.id
编写实体类Student,Teacher以及其对应的get,set,toString方法。
public class Student { private Integer id; private String Sname; private String sex; private Integer age; private Integer t_id; }
public class Teacher { private Integer id; private String Tname; //这个一定要有,因为一个老师对多名学生,所以学生信息用List来接收 private List<Student> students; }
编写TeacherDao接口,是因为我们的操作对象是Teacher类。
public interface TeacherDao { }
编写StudentMapper.xml文件,实现sql查询。
<select id="getTeacher" resultType="com.qcby.entity.Teacher"> select * from teacher where id = #{t_id}; <!-- #{t_id}; 可以写任何东西,因为会自动匹配 t_id --> </select> <!--按照查询嵌套处理--> <select id="getTeacher" resultMap="TeacherStudent2"> select * from teacher </select> <resultMap id="TeacherStudent2" type="com.qcby.entity.Teacher"> <collection property="students" column="id" javaType="ArrayList" ofType="com.qcby.entity.Student" select="getStudentByTeacherId" /> </resultMap> <select id="getStudentByTeacherId" resultType="com.qcby.entity.Student"> select * from student where t_id = #{t_id} </select>
在TeacherDao接口中创建新方法
public interface TeacherDao { public List<Teacher> getTeacher(); }
编写TeacherTest测试类
public class TeacherTest { private InputStream in = null; private SqlSession session = null; private TeacherDao mapper = null; @Before //前置通知, 在方法执行之前执行 public void init() throws IOException { //加载主配置文件,目的是为了构建SqlSessionFactory对象 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //创建SqlSessionFactory对象 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //通过SqlSessionFactory工厂对象创建SqlSesssion对象 session = factory.openSession(); //通过Session创建UserDao接口代理对象 mapper = session.getMapper(TeacherDao.class); } @After //@After: 后置通知, 在方法执行之后执行 。 public void destory() throws IOException { //释放资源 session.close(); in.close(); } @Test public void getTeacher(){ List<Teacher> teachers=mapper.getTeacher(); for (Teacher teacher:teachers) { System.out.println(teacher.toString()); } } }
最后需要配置数据库连接文件,并把下段代码放进去
<mapper resource="mapper/TeacherMapper.xml"></mapper>
2.按照结果嵌套处理
SELECT teacher.id,teacher.name,student.name FROM teacher LEFT JOIN student on student.t_id = teacher.id
编写实体类,这里可以不变动,接着使用Student,Teacher类。
编写接口,不变动接着使用TeacherDao接口。
编写TeacherMapper.xml文件,实现sql查询。
<!--按照结果进行查询--> <select id="getTeacher1" resultMap="TeacherStudent"> SELECT teacher.id,teacher.Tname,student.Sname FROM teacher LEFT JOIN student on student.t_id = teacher.id </select> <resultMap id="TeacherStudent" type="com.qcby.entity.Teacher"> <result property="id" column="id"/> <result property="Tname" column="Tname"/> <!-- 复杂的属性我么需要单独去处理 对象:association 集合:collection 在集合中的泛型信息,我们使用ofType获取 --> <collection property="students" ofType="com.qcby.entity.Student"> <result property="Sname" column="Sname"/> </collection> </resultMap>
在TeacherDao接口中创建新方法
public interface TeacherDao { public List<Teacher> getTeacher1(); }
编写TeacherTest测试类,把下段代码放到TeacherTest里面
@Test public void getTeacher1(){ List<Teacher> students=mapper.getTeacher1(); for (Teacher teacher:teachers) { System.out.println(teacher.toString()); } }