Scott 数据映射 MySQL代码实现分享

吾爱主题阅读：142 2024-04-01 23:23:10 评论：0

1、SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 DROP TABLE IF EXISTS `tb_dept`; CREATE TABLE `tb_dept` ( `deptno` tinyint(2) UNSIGNED NOT NULL COMMENT '部门编号' , `dname` varchar (14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称' , `loc` varchar (13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门地址' , PRIMARY KEY (`deptno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic ; INSERT INTO `tb_dept` VALUES (10, 'ACCOUNTING' , 'NEW YORK' ); INSERT INTO `tb_dept` VALUES (20, 'RESEARCH' , 'DALLAS' ); INSERT INTO `tb_dept` VALUES (30, 'SALES' , 'CHICAGO' ); INSERT INTO `tb_dept` VALUES (40, 'OPERATIONS' , 'BOSTON' ); ------------------------------------------------------------------- DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` ( `empno` int (4) UNSIGNED NOT NULL , `ename` varchar (10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `job` varchar (9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `mgr` int (4) UNSIGNED NULL DEFAULT NULL , `hiredate` date NULL DEFAULT NULL , `sal` decimal (7, 2) NULL DEFAULT NULL , `comm` decimal (7, 2) NULL DEFAULT NULL , `deptno` tinyint(2) UNSIGNED NULL DEFAULT NULL , PRIMARY KEY (`empno`) USING BTREE, INDEX `deptno`(`deptno`) USING BTREE, CONSTRAINT `tb_emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `tb_dept` (`deptno`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic ; INSERT INTO `tb_emp` VALUES (7369, 'SMITH' , 'CLERK' , 7902, '1980-12-17' , 800.00, NULL , 20); INSERT INTO `tb_emp` VALUES (7499, 'ALLEN' , 'SALESMAN' , 7698, '1981-02-20' , 1600.00, 300.00, 30); INSERT INTO `tb_emp` VALUES (7521, 'WARD' , 'SALESMAN' , 7698, '1981-02-22' , 1250.00, 500.00, 30); INSERT INTO `tb_emp` VALUES (7566, 'JONES' , 'MANAGER' , 7839, '1981-04-02' , 2975.00, NULL , 20); INSERT INTO `tb_emp` VALUES (7654, 'MARTIN' , 'SALESMAN' , 7698, '1981-09-28' , 1250.00, 1400.00, 30); INSERT INTO `tb_emp` VALUES (7698, 'BLAKE' , 'MANAGER' , 7839, '1981-05-01' , 2850.00, NULL , 30); INSERT INTO `tb_emp` VALUES (7782, 'CLARK' , 'MANAGER' , 7839, '1981-06-09' , 2450.00, NULL , 10); INSERT INTO `tb_emp` VALUES (7788, 'SCOTT' , 'ANALYST' , 7566, '1987-04-19' , 3000.00, NULL , 20); INSERT INTO `tb_emp` VALUES (7839, 'KING' , 'PRESIDENT' , NULL , '1981-11-17' , 5000.00, NULL , 10); INSERT INTO `tb_emp` VALUES (7844, 'TURNER' , 'SALESMAN' , 7698, '1981-09-08' , 1500.00, 0.00, 30); INSERT INTO `tb_emp` VALUES (7876, 'ADAMS' , 'CLERK' , 7788, '1987-05-23' , 1100.00, NULL , 20); INSERT INTO `tb_emp` VALUES (7900, 'JAMES' , 'CLERK' , 7698, '1981-12-03' , 950.00, NULL , 30); INSERT INTO `tb_emp` VALUES (7902, 'FORD' , 'ANALYST' , 7566, '1981-12-03' , 3000.00, NULL , 20); INSERT INTO `tb_emp` VALUES (7934, 'MILLER' , 'CLERK' , 7782, '1982-01-23' , 1300.00, NULL , 10);

2、实体类

2.1、Dept.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 /** * 部门 * @author HC * */ public class Dept { /** * 部门编号 */ private Integer deptno; /** * 部门名称 */ private String dname; /** * 部门地址 */ private String loc; public Dept() { } public Dept(Integer deptno, String dname, String loc) { this .deptno = deptno; this .dname = dname; this .loc = loc; } public Integer getDeptno() { return deptno; } public void setDeptno(Integer deptno) { this .deptno = deptno; } public String getDname() { return dname; } public void setDname(String dname) { this .dname = dname; } public String getLoc() { return loc; } public void setLoc(String loc) { this .loc = loc; } @Override public String toString() { return "Dept{" + "deptno=" + deptno + ", dname='" + dname + '\ '' + ", loc='" + loc + '\ '' + '}' ; } }

2.2、Emp.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 /** * 员工 * @author HC */ public class Emp { /** * 员工编号 */ private Integer empno; /** * 员工姓名 */ private String ename; /** * 工作 */ private String job; /** * 上级领导编号 */ private Integer mgr; /** * 受雇日期 */ private LocalDate hiredate; /** * 薪资 */ private Double sal; /** * 奖金 */ private Double comm; /** * 部门编号 */ private Integer deptno; public Emp() { } public Emp(Integer empno, String ename, String job, Integer mgr, LocalDate hiredate, Double sal, Double comm, Integer deptno) { this .empno = empno; this .ename = ename; this .job = job; this .mgr = mgr; this .hiredate = hiredate; this .sal = sal; this .comm = comm; this .deptno = deptno; } public Integer getEmpno() { return empno; } public void setEmpno(Integer empno) { this .empno = empno; } public String getEname() { return ename; } public void setEname(String ename) { this .ename = ename; } public String getJob() { return job; } public void setJob(String job) { this .job = job; } public Integer getMgr() { return mgr; } public void setMgr(Integer mgr) { this .mgr = mgr; } public LocalDate getHiredate() { return hiredate; } public void setHiredate(LocalDate hiredate) { this .hiredate = hiredate; } public Double getSal() { return sal; } public void setSal(Double sal) { this .sal = sal; } public Double getComm() { return comm; } public void setComm(Double comm) { this .comm = comm; } public Integer getDeptno() { return deptno; } public void setDeptno(Integer deptno) { this .deptno = deptno; } @Override public String toString() { return "Emp{" + "empno=" + empno + ", ename='" + ename + '\ '' + ", job='" + job + '\ '' + ", mgr=" + mgr + ", hiredate=" + hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + '}' ; } }

3、数据库模拟代码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 public class DB { private static List<Emp> emps = new ArrayList<>(); private static List<Dept> depts = new ArrayList<>(); static { depts.add( new Dept( 10 , "ACCOUNTING" , "NEWYORK" )); depts.add( new Dept( 20 , "RESEARCH" , "DALLAS" )); depts.add( new Dept( 30 , "SALES" , "CHICAGO" )); depts.add( new Dept( 40 , "OPERATIONS" , "BOSTON" )); emps.add( new Emp( 7369 , "SMITH" , "CLERK" , 7902 ,LocalDate.of( 1980 , 12 , 17 ), 800D, null , 20 )); emps.add( new Emp( 7499 , "ALLEN" , "SALESMAN" , 7698 , LocalDate.of( 1981 , 2 , 20 ), 1600D, 300D, 30 )); emps.add( new Emp( 7521 , "WARD" , "SALESMAN" , 7698 , LocalDate.of( 1981 , 2 , 22 ), 1250D, 500D, 30 )); emps.add( new Emp( 7566 , "JONES" , "MANAGER" , 7893 , LocalDate.of( 1981 , 4 , 2 ), 2975D, null , 20 )); emps.add( new Emp( 7654 , "MARTIN" , "SALESMAN" , 7698 , LocalDate.of( 1981 , 9 , 28 ), 1250D, 1400D, 30 )); emps.add( new Emp( 7698 , "BLAKE" , "MANAGER" , 7839 , LocalDate.of( 1981 , 5 , 1 ), 2850D, null , 30 )); emps.add( new Emp( 7782 , "CLARK" , "MANAGER" , 7839 , LocalDate.of( 1981 , 6 , 9 ), 2450D, 600D, 10 )); emps.add( new Emp( 7788 , "SCOTT" , "ANALYST" , 7566 , LocalDate.of( 1987 , 4 , 19 ), 3000D, null , 20 )); emps.add( new Emp( 7839 , "KING" , "PRESIDENT" , null , LocalDate.of( 1981 , 11 , 17 ), 5000D, null , 10 )); emps.add( new Emp( 7844 , "TURNER" , "SALESMAN" , 7698 , LocalDate.of( 1981 , 9 , 8 ), 1500D, null , 30 )); emps.add( new Emp( 7876 , "ADAMS" , "CLERK" , 7788 , LocalDate.of( 1987 , 5 , 23 ), 1100D, 350D, 20 )); emps.add( new Emp( 7900 , "JAMES" , "CLERK" , 7698 , LocalDate.of( 1981 , 12 , 3 ), 950D, null , 30 )); emps.add( new Emp( 7902 , "FORD" , "ANALYST" , 7566 , LocalDate.of( 1981 , 12 , 3 ), 3000D, null , 20 )); emps.add( new Emp( 7934 , "MILLER" , "CLERK" , 7782 , LocalDate.of( 1982 , 1 , 23 ), 1300D, 400D, 10 )); } public static List<Emp> getEmps() { return emps; } public static List<Dept> getDepts() { return depts; } }