Scott 数据 映射 MySQL

1、SQLSET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; -- ---------------------------- -- Table structure for tb_dept -- ---------------------------- DROP TABLE IF EXISTS tb_dept; CREATE TABLE tb_dept ( deptno tinyint UNSIGNED NOT NULL COMMENT 部门编号, dname varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 部门名称, loc varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 部门地址, PRIMARY KEY (deptno) USING BTREE ) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT 部门 ROW_FORMAT DYNAMIC; -- ---------------------------- -- Records of tb_dept -- ---------------------------- 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); -- ---------------------------- -- Table structure for tb_emp -- ---------------------------- DROP TABLE IF EXISTS tb_emp; CREATE TABLE tb_emp ( empno int UNSIGNED NOT NULL COMMENT 编号, ename varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 姓名, job varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 职位, mgr int UNSIGNED NULL DEFAULT NULL COMMENT 上级, hiredate date NULL DEFAULT NULL COMMENT 入职日期, sal decimal(7, 2) NULL DEFAULT NULL COMMENT 工作, comm decimal(7, 2) NULL DEFAULT NULL COMMENT 奖金, deptno tinyint UNSIGNED NULL DEFAULT NULL COMMENT 部门编号, PRIMARY KEY (empno) USING BTREE, INDEX deptno(deptno ASC) USING BTREE, CONSTRAINT tb_emp_ibfk_1 FOREIGN KEY (deptno) REFERENCES tb_dept (deptno) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT 员工 ROW_FORMAT DYNAMIC; -- ---------------------------- -- Records of tb_emp -- ---------------------------- 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); SET FOREIGN_KEY_CHECKS 1;2、实体类2.1、Dept.java/** * 部门 * author HC * */publicclassDept{/** * 部门编号 */privateIntegerdeptno;/** * 部门名称 */privateStringdname;/** * 部门地址 */privateStringloc;publicDept(){}publicDept(Integerdeptno,Stringdname,Stringloc){this.deptnodeptno;this.dnamedname;this.locloc;}publicIntegergetDeptno(){returndeptno;}publicvoidsetDeptno(Integerdeptno){this.deptnodeptno;}publicStringgetDname(){returndname;}publicvoidsetDname(Stringdname){this.dnamedname;}publicStringgetLoc(){returnloc;}publicvoidsetLoc(Stringloc){this.locloc;}OverridepublicStringtoString(){returnDept{deptnodeptno, dnamedname\, locloc\};}}2.2、Emp.java/** * 员工 * author HC */publicclassEmp{/** * 员工编号 */privateIntegerempno;/** * 员工姓名 */privateStringename;/** * 工作 */privateStringjob;/** * 上级领导编号 */privateIntegermgr;/** * 受雇日期 */privateLocalDatehiredate;/** * 薪资 */privateDoublesal;/** * 奖金 */privateDoublecomm;/** * 部门编号 */privateIntegerdeptno;publicEmp(){}publicEmp(Integerempno,Stringename,Stringjob,Integermgr,LocalDatehiredate,Doublesal,Doublecomm,Integerdeptno){this.empnoempno;this.enameename;this.jobjob;this.mgrmgr;this.hiredatehiredate;this.salsal;this.commcomm;this.deptnodeptno;}publicIntegergetEmpno(){returnempno;}publicvoidsetEmpno(Integerempno){this.empnoempno;}publicStringgetEname(){returnename;}publicvoidsetEname(Stringename){this.enameename;}publicStringgetJob(){returnjob;}publicvoidsetJob(Stringjob){this.jobjob;}publicIntegergetMgr(){returnmgr;}publicvoidsetMgr(Integermgr){this.mgrmgr;}publicLocalDategetHiredate(){returnhiredate;}publicvoidsetHiredate(LocalDatehiredate){this.hiredatehiredate;}publicDoublegetSal(){returnsal;}publicvoidsetSal(Doublesal){this.salsal;}publicDoublegetComm(){returncomm;}publicvoidsetComm(Doublecomm){this.commcomm;}publicIntegergetDeptno(){returndeptno;}publicvoidsetDeptno(Integerdeptno){this.deptnodeptno;}OverridepublicStringtoString(){returnEmp{empnoempno, enameename\, jobjob\, mgrmgr, hiredatehiredate, salsal, commcomm, deptnodeptno};}}3、数据库模拟代码publicclassDB{privatestaticListEmpempsnewArrayList();privatestaticListDeptdeptsnewArrayList();static{depts.add(newDept(10,ACCOUNTING,NEWYORK));depts.add(newDept(20,RESEARCH,DALLAS));depts.add(newDept(30,SALES,CHICAGO));depts.add(newDept(40,OPERATIONS,BOSTON));emps.add(newEmp(7369,SMITH,CLERK,7902,LocalDate.of(1980,12,17),800D,null,20));emps.add(newEmp(7499,ALLEN,SALESMAN,7698,LocalDate.of(1981,2,20),1600D,300D,30));emps.add(newEmp(7521,WARD,SALESMAN,7698,LocalDate.of(1981,2,22),1250D,500D,30));emps.add(newEmp(7566,JONES,MANAGER,7893,LocalDate.of(1981,4,2),2975D,null,20));emps.add(newEmp(7654,MARTIN,SALESMAN,7698,LocalDate.of(1981,9,28),1250D,1400D,30));emps.add(newEmp(7698,BLAKE,MANAGER,7839,LocalDate.of(1981,5,1),2850D,null,30));emps.add(newEmp(7782,CLARK,MANAGER,7839,LocalDate.of(1981,6,9),2450D,600D,10));emps.add(newEmp(7788,SCOTT,ANALYST,7566,LocalDate.of(1987,4,19),3000D,null,20));emps.add(newEmp(7839,KING,PRESIDENT,null,LocalDate.of(1981,11,17),5000D,null,10));emps.add(newEmp(7844,TURNER,SALESMAN,7698,LocalDate.of(1981,9,8),1500D,null,30));emps.add(newEmp(7876,ADAMS,CLERK,7788,LocalDate.of(1987,5,23),1100D,350D,20));emps.add(newEmp(7900,JAMES,CLERK,7698,LocalDate.of(1981,12,3),950D,null,30));emps.add(newEmp(7902,FORD,ANALYST,7566,LocalDate.of(1981,12,3),3000D,null,20));emps.add(newEmp(7934,MILLER,CLERK,7782,LocalDate.of(1982,1,23),1300D,400D,10));}publicstaticListEmpgetEmps(){returnemps;}publicstaticListDeptgetDepts(){returndepts;}}