多对多关系数据库表 java描述
JAVA 描述 关系数据库
2023-09-11 14:18:04 时间
多对多关系 需要建立一张新表存放它们的对应数据
sql语句
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table teacher( 2 id int primary key, 3 name varchar(100), 4 money float(8,2) 5 ); 6 create table student( 7 id int primary key, 8 name varchar(100), 9 grade varchar(10) 10 ); 11 create table teacher_student( 12 t_id int, 13 s_id int, 14 primary key(t_id,s_id), 15 constraint t_id_fk foreign key(t_id) references teacher(id), 16 constraint s_id_fk foreign key(s_id) references student(id) 17 );
domain
Teacher.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package cn.itcast.domain; 2 3 import java.io.Serializable; 4 import java.util.ArrayList; 5 import java.util.List; 6 /** 7 create table teacher( 8 id int primary key, 9 name varchar(100), 10 money float(8,2) 11 ); 12 create table student( 13 id int primary key, 14 name varchar(100), 15 grade varchar(10) 16 ); 17 create table teacher_student( 18 t_id int, 19 s_id int, 20 primary key(t_id,s_id), 21 constraint t_id_fk foreign key(t_id) references teacher(id), 22 constraint s_id_fk foreign key(s_id) references student(id) 23 ); 24 * @author wzhting 25 * 26 */ 27 public class Teacher implements Serializable { 28 private Integer id; 29 private String name; 30 private float money; 31 private List<Student> stus = new ArrayList<Student>(); 32 public Integer getId() { 33 return id; 34 } 35 public void setId(Integer id) { 36 this.id = id; 37 } 38 public String getName() { 39 return name; 40 } 41 public void setName(String name) { 42 this.name = name; 43 } 44 public float getMoney() { 45 return money; 46 } 47 public void setMoney(float money) { 48 this.money = money; 49 } 50 public List<Student> getStus() { 51 return stus; 52 } 53 public void setStus(List<Student> stus) { 54 this.stus = stus; 55 } 56 57 }
Student.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package cn.itcast.domain; 2 3 import java.io.Serializable; 4 import java.util.ArrayList; 5 import java.util.List; 6 7 public class Student implements Serializable { 8 private Integer id; 9 private String name; 10 private String grade; 11 private List<Teacher> ts = new ArrayList<Teacher>(); 12 public Integer getId() { 13 return id; 14 } 15 public void setId(Integer 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 public String getGrade() { 25 return grade; 26 } 27 public void setGrade(String grade) { 28 this.grade = grade; 29 } 30 public List<Teacher> getTs() { 31 return ts; 32 } 33 public void setTs(List<Teacher> ts) { 34 this.ts = ts; 35 } 36 37 }
daoImpl.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package cn.itcast.dao.impl; 2 3 import java.sql.SQLException; 4 import java.util.List; 5 6 import org.apache.commons.dbutils.QueryRunner; 7 import org.apache.commons.dbutils.handlers.BeanHandler; 8 import org.apache.commons.dbutils.handlers.BeanListHandler; 9 import org.apache.commons.dbutils.handlers.ScalarHandler; 10 11 import cn.itcast.domain.Student; 12 import cn.itcast.domain.Teacher; 13 import cn.itcast.util.DBCPUtil; 14 15 public class TeacherDaoImpl { 16 private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource()); 17 public void addTeacher(Teacher t) throws SQLException{ 18 //保存老师的基本信息 19 qr.update("insert into teacher(id,name,money) values(?,?,?)", t.getId(),t.getName(),t.getMoney()); 20 //判断t中是否有学生 21 List<Student> stus = t.getStus(); 22 //有学生:判断该学生是否在student表中;在,不添加了;不在,添加进去 23 if(stus!=null&&stus.size()>0){ 24 for(Student s:stus){ 25 Object num = qr.query("select 1 from student where id=?", new ScalarHandler(1), s.getId());//判断学生是否已在数据库中 26 if(num==null){ 27 //学生信息不存在 28 qr.update("insert into student (id,name,grade) values(?,?,?)", s.getId(),s.getName(),s.getGrade()); 29 } 30 //在第三方表中建立老师和学生的关系 31 qr.update("insert into teacher_student (t_id,s_id) values(?,?)", t.getId(),s.getId()); 32 } 33 } 34 35 } 36 37 public Teacher findTeacher(Integer id) throws SQLException{ 38 //查询老师的基本信息 39 Teacher t = qr.query("select * from teacher where id=?", new BeanHandler<Teacher>(Teacher.class), id); 40 if(t!=null){ 41 //根据老师的id查学生的基本信息:方式三种 42 // String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";//子查询 43 // String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";//隐式内连接 44 String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";;//显式内连接 45 List<Student> stus = qr.query(sql, new BeanListHandler<Student>(Student.class), id); 46 t.setStus(stus); 47 } 48 return t; 49 } 50 }
test
测试
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package cn.itcast.test; 2 3 import java.sql.SQLException; 4 import java.util.List; 5 6 import org.junit.Test; 7 8 import cn.itcast.dao.impl.TeacherDaoImpl; 9 import cn.itcast.domain.Student; 10 import cn.itcast.domain.Teacher; 11 12 public class TeacherDaoImplTest { 13 private TeacherDaoImpl dao = new TeacherDaoImpl(); 14 @Test 15 public void testAddTeacher() throws SQLException { 16 Teacher t1 = new Teacher(); 17 t1.setId(1); 18 t1.setName("bxd"); 19 t1.setMoney(20000); 20 21 Teacher t2 = new Teacher(); 22 t2.setId(2); 23 t2.setName("wzt"); 24 t2.setMoney(15000); 25 26 Student s1 = new Student(); 27 s1.setId(1); 28 s1.setName("gfy"); 29 s1.setGrade("A"); 30 31 Student s2 = new Student(); 32 s2.setId(2); 33 s2.setName("wxy"); 34 s2.setGrade("A"); 35 36 t1.getStus().add(s1); 37 t1.getStus().add(s2); 38 39 t2.getStus().add(s1); 40 t2.getStus().add(s2); 41 42 dao.addTeacher(t1); 43 dao.addTeacher(t2); 44 45 } 46 47 @Test 48 public void testFindTeacher() throws SQLException { 49 Teacher t = dao.findTeacher(2); 50 System.out.println(t.getName()); 51 List<Student> stus = t.getStus(); 52 for(Student s:stus) 53 System.out.println(s.getName()); 54 } 55 56 }
相关文章
- 深入java面向对象四:Java 内部类种类及使用解析(转)
- java -version 问题 : C:ProgramDataOracleJavajavapath;
- Spring Boot Java应用代码混淆介绍
- java-Timestamp
- Java实现 蓝桥杯 算法提高 特等奖学金(暴力)
- Java实现 LeetCode 429 N叉树的层序遍历
- Java实现 LeetCode 416 分割等和子集
- Java实现蓝桥杯模拟递增的数
- Java实现 洛谷 P1914 小书童——密码
- java实现第六届蓝桥杯饮料换购
- Java实现 蓝桥杯VIP 算法提高 计算时间
- Java实现 蓝桥杯 算法提高 三进制数位和
- Java 蓝桥杯 算法训练 字符串的展开 (JAVA语言实现)
- 【JAVA】java中char类型数组用数组名打印结果不是地址值而是数组内容
- 【JAVA】Java循环语句中的continue跳转进入下一次循环是否判断循环条件
- 【JAVA】 03-Java中的异常和包的使用
- 【JAVA】Java循环语句中的continue跳转进入下一次循环是否判断循环条件
- 【JAVA】java中的length和length()
- 【JAVA】毕向东Java基础视频教程-笔记
- 【Java】java使用反射访问对象方法和成员变量
- java JUC线程池:Executors.newFixedThreadPool代码示例
- Spring 基于 Java 的配置 - 如何不用Beans.xml照样描述bean之间的依赖关系
- Atitit 实体的概念与理解 目录 1. 使用不同的语言来描述实体1 1.1. H5 table dom模型 json1 1.2. Sql table1 1.3. Java class m
- paip.文件读写api php java python总结.txt
- 已解决:module java.base does not “opens java.lang“ to module
- Java 关于java.util.LinkedHashMap cannot be cast to 实体类问题答案
- 报错 java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp 原因
- Java学习路线-24:类库使用案例StringBuffer、Rondom、ResourceBundle、regex、Comparable
- 一文详解java线程池 详解Java线程池的七个参数 详解池化技术 java如何选择核心线程数 详解Java线程池的拒绝策略
- java中的transient 详细总结Java中transient关键字 对象写出流ObjectOutputStream 对象写入流ObjectInputStream
- Java Logger(java日志)
- 左右 Java 于 finally 深度分析语句块
- Java开发技术之成为高级java工程师必须学习的三个技术
- JAVA开发讲义(二)-Java程序设计之数据之谜四