# Mybatis **Repository Path**: hrbu-2022/mybatis ## Basic Information - **Project Name**: Mybatis - **Description**: Mybatis学习 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2025-03-04 - **Last Updated**: 2025-03-07 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # SSH Struts1.x 2.x ： MVC Spring： 解耦的框架 Hibnernate : 持久层框架 封装jdbc的 重量级的框架 不需要写sql ​ PO对象 save(PO )-- insert ​ upodate ​ list select ​ ORM 对象 关系映射 # SSM Mybatis: 半自动化的ORM框架 需要手动维护 sql http://mybatis.p2hp.com/ ## 准备数据 ```sql /* SQLyog Ultimate v12.08 (64 bit) MySQL - 8.0.30 : Database - his ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`his` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */; USE `his`; /*Table structure for table `check_apply` */ DROP TABLE IF EXISTS `check_apply`; CREATE TABLE `check_apply` ( `item_id` int NOT NULL AUTO_INCREMENT COMMENT '检查项目主键', `regist_id` int DEFAULT NULL COMMENT '病例号', `check_id` int DEFAULT NULL COMMENT '检查ID', `name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '检查项目名称', `price` decimal(6,2) DEFAULT NULL COMMENT '价格', `status` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '状态', `result` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '检查结果', PRIMARY KEY (`item_id`), KEY `FK_Reference_10` (`regist_id`), KEY `FK_Reference_11` (`check_id`), CONSTRAINT `FK_Reference_10` FOREIGN KEY (`regist_id`) REFERENCES `register` (`regist_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_Reference_11` FOREIGN KEY (`check_id`) REFERENCES `checkitem` (`check_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='开立检查申请'; /*Data for the table `check_apply` */ insert into `check_apply`(`item_id`,`regist_id`,`check_id`,`name`,`price`,`status`,`result`) values (3,6,4,'心电图','80.00','1',NULL),(4,6,1,'腹部CT','200.00','1',NULL),(5,19,1,'腹部CT','200.00','1',NULL),(6,19,2,'胸部x光片','50.00','1',NULL),(7,19,3,'颈部超声波扫描','10.00','1',NULL),(8,18,1,'腹部CT','200.00','2',NULL),(9,18,2,'胸部x光片','50.00','2',NULL),(11,37,1,'腹部CT','200.00','3','董卓检查结果.....'),(12,37,2,'胸部x光片','50.00','1',NULL),(13,69,1,'腹部CT','200.00','3','ok 回去多吃饭，'),(14,69,2,'胸部x光片','50.00','3','肺结节'),(15,69,3,'颈部超声波扫描','10.00','1',NULL),(16,69,4,'心电图','80.00','3','窦性心律'); /*Table structure for table `checkitem` */ DROP TABLE IF EXISTS `checkitem`; CREATE TABLE `checkitem` ( `check_id` int NOT NULL AUTO_INCREMENT COMMENT '检查ID', `name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称', `price` decimal(6,2) DEFAULT NULL COMMENT '价格', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`check_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='检查'; /*Data for the table `checkitem` */ insert into `checkitem`(`check_id`,`name`,`price`,`del`,`createtime`) values (1,'腹部CT','200.00','1','2024-11-21 12:24:38'),(2,'胸部x光片','50.00','1','2024-11-21 12:24:39'),(3,'颈部超声波扫描','10.00','1','2024-11-21 12:25:31'),(4,'心电图','80.00','1','2024-12-02 13:47:20'); /*Table structure for table `constant_type` */ DROP TABLE IF EXISTS `constant_type`; CREATE TABLE `constant_type` ( `type_id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类别', `caption` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '说明', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`type_id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='常数类别'; /*Data for the table `constant_type` */ insert into `constant_type`(`type_id`,`type`,`caption`,`del`,`createtime`) values (1,'Gender','性别','1','2024-11-21 12:20:04'),(2,'RegisterStatus','患者状态','1','2024-11-21 16:57:54'),(5,'Docter','医生状态','1','2024-11-25 09:14:43'),(6,'YesOrNO','是否','1','2024-11-29 08:44:09'),(7,'CheckApplyStatus','检查项目申请状态','1','2024-12-02 16:30:03'),(8,'InspectApplyStatus','检验项目申请状态','1','2024-12-03 09:25:35'); /*Table structure for table `constants_item` */ DROP TABLE IF EXISTS `constants_item`; CREATE TABLE `constants_item` ( `item_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键', `code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '代码', `caption` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称', `type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型', `type_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型名称', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`item_id`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='常数项目'; /*Data for the table `constants_item` */ insert into `constants_item`(`item_id`,`code`,`caption`,`type`,`type_name`,`del`,`createtime`) values (1,'1','男','Gender','性别','1','2024-11-21 12:20:29'),(2,'2','女','Gender','性别','1','2024-11-21 12:20:31'),(4,'1','已挂号','RegisterStatus','患者状态','1','2024-11-22 11:15:48'),(7,'Y','是','YesOrNO','是否','1','2024-11-29 08:46:20'),(8,'N','否','YesOrNO','是否','1','2024-11-29 08:46:33'),(9,'1','待缴费','CheckApplyStatus','检查项目申请状态','1','2024-12-02 16:30:20'),(10,'2','已缴费','CheckApplyStatus','检查项目申请状态','1','2024-12-02 16:30:29'),(11,'3','已检查','CheckApplyStatus','检查项目申请状态','1','2024-12-02 16:31:01'),(12,'4','已退费','CheckApplyStatus','检查项目申请状态','1','2024-12-02 16:31:12'),(13,'1','待缴费','InspectApplyStatus','检验项目申请状态','1','2024-12-03 09:25:48'),(14,'2','已缴费','InspectApplyStatus','检验项目申请状态','1','2024-12-03 09:25:59'),(15,'3','已检查','InspectApplyStatus','检验项目申请状态','1','2024-12-03 09:26:03'),(16,'4','已退费','InspectApplyStatus','检验项目申请状态','1','2024-12-03 09:26:05'); /*Table structure for table `dept` */ DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `dept_id` bigint NOT NULL AUTO_INCREMENT COMMENT '部门ID', `dept_name` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '部门名称', `loc` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '办公地址', `leader` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '科室主任', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='部门'; /*Data for the table `dept` */ insert into `dept`(`dept_id`,`dept_name`,`loc`,`leader`,`del`,`createtime`) values (1,'心内科','207','孙思邈','1','2024-11-21 13:56:49'),(5,'皮肤科','202','李时珍','1','2024-11-22 08:39:22'); /*Table structure for table `inspect_apply` */ DROP TABLE IF EXISTS `inspect_apply`; CREATE TABLE `inspect_apply` ( `item_id` int NOT NULL AUTO_INCREMENT COMMENT '建议项目主键', `inspect_id` int DEFAULT NULL COMMENT '检验ID', `name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '检验项目名称', `regist_id` int DEFAULT NULL COMMENT '病例号', `price` decimal(6,2) DEFAULT NULL COMMENT '价格', `status` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '状态', `result` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '检查结果', PRIMARY KEY (`item_id`), KEY `FK_Reference_13` (`inspect_id`), KEY `FK_Reference_14` (`regist_id`), CONSTRAINT `FK_Reference_13` FOREIGN KEY (`inspect_id`) REFERENCES `inspectitem` (`inspect_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_Reference_14` FOREIGN KEY (`regist_id`) REFERENCES `register` (`regist_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='开立检验请'; /*Data for the table `inspect_apply` */ insert into `inspect_apply`(`item_id`,`inspect_id`,`name`,`regist_id`,`price`,`status`,`result`) values (7,1,'血常规',18,'10.00','2',NULL),(8,4,'大生化',18,'40.00','2',NULL),(9,2,'肝肾功能',37,'20.00','1',NULL),(10,1,'血常规',37,'10.00','2',NULL),(11,3,'鸟产规',37,'30.00','2',NULL),(13,2,'肝肾功能',69,'20.00','2',NULL),(14,3,'鸟产规',69,'30.00','2',NULL),(15,4,'大生化',69,'40.00','1',NULL),(16,5,'肝肾功能',69,'500.00','1',NULL); /*Table structure for table `inspectitem` */ DROP TABLE IF EXISTS `inspectitem`; CREATE TABLE `inspectitem` ( `inspect_id` int NOT NULL AUTO_INCREMENT COMMENT '检验ID', `name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称', `price` decimal(6,2) DEFAULT NULL COMMENT '价格', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`inspect_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='检验'; /*Data for the table `inspectitem` */ insert into `inspectitem`(`inspect_id`,`name`,`price`,`del`,`createtime`) values (1,'血常规','10.00','1','2024-11-21 12:26:07'),(2,'肝肾功能','20.00','1','2024-11-21 12:26:28'),(3,'鸟产规','30.00','1','2024-11-21 12:26:35'),(4,'大生化','40.00','1','2024-11-21 12:26:41'),(5,'肝肾功能','500.00','1','2024-12-03 09:13:11'); /*Table structure for table `item_upload` */ DROP TABLE IF EXISTS `item_upload`; CREATE TABLE `item_upload` ( `item_id` bigint NOT NULL COMMENT '项目Id', `upload_id` bigint NOT NULL COMMENT '上传文件Id', `type` int NOT NULL COMMENT '1 检查项目上传 2 检验上传的文件', PRIMARY KEY (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='上传文件和检验、检查项目的对应关系'; /*Data for the table `item_upload` */ /*Table structure for table `regist_level` */ DROP TABLE IF EXISTS `regist_level`; CREATE TABLE `regist_level` ( `level_id` bigint NOT NULL AUTO_INCREMENT COMMENT '级别ID', `caption` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '级别名称', `fee` decimal(6,2) DEFAULT NULL COMMENT '费用', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否删除0 删除1，正常的', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据创建时间', PRIMARY KEY (`level_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='挂号级别'; /*Data for the table `regist_level` */ insert into `regist_level`(`level_id`,`caption`,`fee`,`del`,`createtime`) values (1,'住院医','10.00','1','2024-11-22 11:59:02'),(2,'主治','20.00','1','2024-11-22 11:59:26'),(3,'主任','50.00','1','2024-11-22 11:59:34'),(4,'老专家','100.00','1','2024-11-22 11:59:40'),(5,'名老专家','2000.00','1','2024-11-22 12:19:06'); /*Table structure for table `register` */ DROP TABLE IF EXISTS `register`; CREATE TABLE `register` ( `regist_id` int NOT NULL AUTO_INCREMENT COMMENT '病例号', `name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称', `gender` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性别', `age` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '年龄', `birthday` date DEFAULT NULL COMMENT '出生日期', `visit_time` date DEFAULT NULL COMMENT '看诊时间', `dept_id` bigint DEFAULT NULL COMMENT '部门ID', `regist_level` bigint DEFAULT NULL COMMENT '级别ID', `readme` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '主诉', `present` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '现病史', `present_treat` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '现病史治疗情况', `history` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '既往史', `allergy` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '过敏史', `disease` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '确诊疾病', `suit` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '处置方案', `drug` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '药品清单', `userid` bigint DEFAULT NULL COMMENT '用户ID', `book` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Y N ', `fee` decimal(6,2) DEFAULT NULL COMMENT '挂号费用', `status` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '1 代接诊，2 一看诊，3 已退', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`regist_id`), KEY `FK_Reference_7` (`dept_id`), KEY `FK_Reference_8` (`regist_level`), KEY `FK_Reference_9` (`userid`), CONSTRAINT `FK_Reference_7` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_Reference_8` FOREIGN KEY (`regist_level`) REFERENCES `regist_level` (`level_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_Reference_9` FOREIGN KEY (`userid`) REFERENCES `ums_user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='患者表'; /*Data for the table `register` */ insert into `register`(`regist_id`,`name`,`gender`,`age`,`birthday`,`visit_time`,`dept_id`,`regist_level`,`readme`,`present`,`present_treat`,`history`,`allergy`,`disease`,`suit`,`drug`,`userid`,`book`,`fee`,`status`,`del`,`createtime`) values (1,'张飞','2','58',NULL,'2024-12-02',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(2,'刘备','2','58',NULL,'2024-12-02',1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,NULL,'2','1','2024-11-28 13:48:22'),(3,'关羽','2','58',NULL,'2024-12-02',1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,NULL,'1','1','2024-11-28 13:48:35'),(4,'赵云','1','50',NULL,'2024-12-02',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2','1','2024-11-28 14:01:48'),(5,'诸葛','1','50','2019-01-01','2024-12-02',1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,'1',NULL,'2','1','2024-11-28 14:08:41'),(6,'司马懿','1','12',NULL,'2024-12-02',1,1,'1','12','13','14','15',NULL,NULL,NULL,7,'1','0.00','3','1','2024-11-28 14:36:11'),(7,'孙权','1','32',NULL,'2024-12-02',1,1,'aa','bb','cc','dd','ee',NULL,NULL,NULL,7,'1','0.00','2','1','2024-11-28 14:53:27'),(8,'测试1','1','12',NULL,'2024-12-02',1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,'1','0.00','2','1','2024-11-28 15:01:41'),(9,'1','1','222',NULL,'2024-12-02',1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,'2','0.00','1','1','2024-11-28 15:02:09'),(10,'1','1','22','2024-11-28','2024-12-02',1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,'1','0.00','2','1','2024-11-28 15:03:24'),(11,'12112',NULL,NULL,NULL,'2024-12-02',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0.00','1','1','2024-11-29 09:23:12'),(12,NULL,NULL,NULL,NULL,'2024-12-02',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0.00','1','1','2024-11-29 09:28:50'),(13,'曹操1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(14,'周瑜1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(15,'赵云1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(16,'孙权1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(17,'吕布1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(18,'马超1203','2','58',NULL,'2024-12-03',1,1,'121212','121','12','12','1122121212',NULL,NULL,NULL,7,NULL,'0.00','3','1','2024-11-28 13:43:59'),(19,'刘禅1203','2','58',NULL,'2024-12-03',1,1,'sadfsa','sadfasfsa','asdfsaf','asdfsa','fdasfaffsf',NULL,NULL,NULL,7,NULL,'0.00','3','1','2024-11-28 13:43:59'),(20,'姜维1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(21,'典韦1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(22,'庞统1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(23,'董卓1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(24,'荀彧1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(25,'黄忠1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(26,'鲁肃1203','2','58',NULL,'2024-12-03',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(27,'曹操1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(28,'周瑜1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(29,'赵云1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(30,'孙权1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(31,'吕布1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(32,'马超1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(33,'刘禅1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(34,'姜维1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(35,'典韦1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(36,'庞统1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(37,'董卓1205','2','58',NULL,'2024-12-05',1,1,'1','1','2','3','4',NULL,NULL,NULL,7,NULL,'0.00','3','1','2024-11-28 13:43:59'),(38,'荀彧1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(39,'黄忠1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(40,'鲁肃1205','2','58',NULL,'2024-12-05',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(41,'曹操1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(42,'周瑜1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(43,'赵云1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(44,'孙权1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(45,'吕布1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(46,'马超1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(47,'刘禅1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(48,'姜维1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(49,'典韦1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(50,'庞统1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(51,'董卓1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(52,'荀彧1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(53,'黄忠1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(54,'鲁肃1206','2','58',NULL,'2024-12-06',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(55,'曹操1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(56,'周瑜1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(57,'赵云1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(58,'孙权1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(59,'吕布1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,'0.00','1','1','2024-11-28 13:43:59'),(60,'马超1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(61,'刘禅1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(62,'姜维1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(63,'典韦1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(64,'庞统1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(65,'董卓1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(66,'荀彧1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(67,'黄忠1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(68,'鲁肃1207','2','58',NULL,'2024-12-07',1,1,'我啥毛病都没有',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,'0.00','1','1','2024-11-28 13:43:59'),(69,'孙权1206','1','50','2024-12-05','2024-12-06',1,1,'121','1244','1233','1222','1212121212','肠胃感冒','多喝热水','五',3,'Y','11.00','4','1','2024-12-06 11:32:58'); /*Table structure for table `role_menu` */ DROP TABLE IF EXISTS `role_menu`; CREATE TABLE `role_menu` ( `menu_id` bigint DEFAULT NULL COMMENT '菜单ID', `role_id` bigint DEFAULT NULL COMMENT '角色ID', KEY `FK_Reference_1` (`menu_id`), KEY `FK_Reference_2` (`role_id`), CONSTRAINT `FK_Reference_1` FOREIGN KEY (`menu_id`) REFERENCES `ums_menu` (`menu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_Reference_2` FOREIGN KEY (`role_id`) REFERENCES `ums_role` (`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='角色配置菜单'; /*Data for the table `role_menu` */ insert into `role_menu`(`menu_id`,`role_id`) values (10,2),(11,2),(12,2),(13,2),(14,2),(15,3),(16,3),(17,5),(18,5),(19,5),(30,5),(1,1),(3,1),(8,1),(9,1),(2,1),(4,1),(6,1),(7,1),(25,1),(28,1),(29,1),(10,1),(11,1),(12,1),(13,1),(14,1),(15,1),(16,1),(17,1),(18,1),(19,1),(30,1); /*Table structure for table `ums_menu` */ DROP TABLE IF EXISTS `ums_menu`; CREATE TABLE `ums_menu` ( `menu_id` bigint NOT NULL AUTO_INCREMENT COMMENT '菜单ID', `menu_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '菜单名称', `url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '前端菜单路径', `path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '前端vue-router路径', `level` int DEFAULT NULL COMMENT '菜单的级别', `parent_id` bigint DEFAULT '0' COMMENT '上级ID', `show` int NOT NULL DEFAULT '0' COMMENT '是否显示，0 显示，1 不显示', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`menu_id`) ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='菜单表（权限）'; /*Data for the table `ums_menu` */ insert into `ums_menu`(`menu_id`,`menu_name`,`url`,`path`,`level`,`parent_id`,`show`,`del`,`createtime`) values (1,'系统管理','xtgl',NULL,1,0,0,'1','2024-11-25 15:13:36'),(2,'基础信息管理','jcxxgl',NULL,NULL,0,0,'1','2024-11-25 15:16:56'),(3,'用户管理','/home/user','ums/user/index.vue',2,1,0,'1','2024-11-25 15:20:17'),(4,'部门管理','/home/dept','base/dept/index.vue',4,2,0,'1','2024-11-25 15:26:11'),(6,'挂号级别','/home/regist_level','base/regist_level/index.vue',2,2,0,'1','2024-11-25 16:08:16'),(7,'常数项类型管理','/home/constants_type','base/constants_type/index.vue',2,2,0,'1','2024-11-25 16:09:45'),(8,'角色管理','/home/role','ums/role/index.vue',2,1,0,'1','2024-11-25 16:10:11'),(9,'菜单管理','/home/menu','ums/menu/index.vue',1,1,0,'1','2024-11-25 16:10:23'),(10,'挂号收费员','ghsfy',NULL,1,0,0,'1','2024-11-28 08:46:54'),(11,'挂号','/home/guahao','guahao/guahao/index.vue',2,10,0,'1','2024-11-28 08:47:14'),(12,'收费','/home/fee','guahao/fee/index.vue',2,10,0,'1','2024-11-28 08:47:28'),(13,'退号','/home/refund_regist','guahao/refund-regist/index.vue',2,10,0,'1','2024-11-28 08:47:49'),(14,'退费','/home/refund','guahao/refund/index.vue',2,10,0,'1','2024-11-28 08:48:01'),(15,'门诊医生','mzys',NULL,1,0,0,'1','2024-11-28 08:48:21'),(16,'门诊病历','/home/doctor','doctor/index.vue',2,15,0,'1','2024-11-28 08:48:33'),(17,'医技医生','yjys','12121212121112',1,0,0,'1','2024-11-28 08:48:59'),(18,'检查处理','/home/checkapply','skill/checkapply/index.vue',2,17,0,'1','2024-11-28 08:49:12'),(19,'检验处理','/home/inspectapply','skill/inspectapply/index.vue',2,17,0,'1','2024-11-28 08:49:30'),(25,'常数项目','/home/constants_item','base/constants_item/index.vue',2,2,1,'1','2024-11-29 08:35:55'),(28,'检查项目管理','/home/checkitem','base/checkitem/index.vue',2,2,0,'1','2024-12-02 13:38:00'),(29,'检验项目管理','/home/inspect','base/inspectitem/index.vue',2,2,0,'1','2024-12-02 13:38:56'),(30,'检查项目列表','/home/checkapplylist','skill/checkapply/check_apply_list.vue',2,17,1,'1','2024-12-05 09:01:02'); /*Table structure for table `ums_role` */ DROP TABLE IF EXISTS `ums_role`; CREATE TABLE `ums_role` ( `role_id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色ID', `role_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '角色名称', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`role_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='角色'; /*Data for the table `ums_role` */ insert into `ums_role`(`role_id`,`role_name`,`del`,`createtime`) values (1,'系统管理员','1','2024-11-25 14:43:37'),(2,'挂号收费员','1','2024-11-25 14:45:05'),(3,'门诊医生','1','2024-11-25 14:45:17'),(5,'医技医生','1','2024-11-25 14:46:23'),(10,'121122','1','2024-11-26 12:25:52'); /*Table structure for table `ums_user` */ DROP TABLE IF EXISTS `ums_user`; CREATE TABLE `ums_user` ( `userid` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID', `username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名', `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码', `nickname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称', `expire` date DEFAULT NULL COMMENT '过期时间', `lastlogin` datetime DEFAULT NULL COMMENT '最后一次登录时间', `dept_id` bigint DEFAULT NULL COMMENT '部门ID', `dept_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '部门名称', `regist_level` bigint DEFAULT NULL COMMENT '级别ID', `del` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '1' COMMENT '是否有效', `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`userid`), KEY `FK_Reference_5` (`dept_id`), KEY `FK_Reference_6` (`regist_level`), CONSTRAINT `FK_Reference_5` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_Reference_6` FOREIGN KEY (`regist_level`) REFERENCES `regist_level` (`level_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='医生、护士、医技等用户'; /*Data for the table `ums_user` */ insert into `ums_user`(`userid`,`username`,`password`,`nickname`,`expire`,`lastlogin`,`dept_id`,`dept_name`,`regist_level`,`del`,`createtime`) values (1,'admin','123456','系统管理员',NULL,'2024-12-06 15:06:20',1,'心内科',1,NULL,'2024-11-21 14:21:01'),(3,'ssm','123456','孙思邈','2025-04-26','2024-12-06 13:56:59',1,'心内科',1,NULL,'2024-11-22 13:51:33'),(7,'ht','123456','华佗',NULL,'2024-12-03 12:24:56',1,'心内科',1,NULL,'2024-11-25 13:43:59'),(8,'guahao','123456','挂号员','2024-11-29','2024-12-06 11:34:18',5,NULL,1,'1','2024-11-28 08:53:37'),(9,'yiji','123456','霍华德',NULL,'2024-12-06 11:53:09',5,NULL,1,'1','2024-11-28 08:54:20'),(10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,'1','2024-11-28 09:26:02'); /*Table structure for table `upload` */ DROP TABLE IF EXISTS `upload`; CREATE TABLE `upload` ( `upload_id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传文件的路径', `origin_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '原始文件名', `ext` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传文件的扩展名', `up_time` datetime DEFAULT NULL COMMENT '上传时间', `up_user` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传的人员', `up_type` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'check inspect', PRIMARY KEY (`upload_id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='检查或者检查结果'; /*Data for the table `upload` */ insert into `upload`(`upload_id`,`path`,`origin_name`,`ext`,`up_time`,`up_user`,`up_type`) values (1,'3bf84b63fa134b6cb9193692f9469d1d.png','abc','png','2024-12-05 08:53:46','admin','check'),(2,'3bf7d62817af48c1a2c80c503700ee15.png','微信图片_20241017140004.png','.png','2024-12-06 09:07:23','admin',NULL),(3,'86b7f7b1daac48cd84f03e21a895ccbd.png','QQ20241205-140636.png','.png','2024-12-06 09:26:51','admin',NULL),(4,'b14987dace144af890231f51af4cc06d.png','QQ20241205-140636.png','.png','2024-12-06 09:35:57','admin',NULL),(5,'e24f84a934fb4c88bffb63e890c57f45.png','微信图片_20241017140004.png','.png','2024-12-06 09:39:58','admin',NULL),(6,'7c22606b2be2495b9667d3d00c37647a.png','微信图片_20241017140004.png','.png','2024-12-06 09:40:21','admin',NULL),(7,'02614ddfd5454b2593c8b64f3aee2b32.png','QQ20241205-140636.png','.png','2024-12-06 09:40:30','admin',NULL),(8,'3fb36886eda142fb8219f9ef932b6fc1.png','QQ20241205-140636.png','.png','2024-12-06 11:10:27','admin',NULL),(9,'52a90e074d6e4ee8994b5b0dd91941b5.png','微信图片_20241017140004.png','.png','2024-12-06 11:10:32','admin',NULL),(10,'e7a9a25928b5449aa2160cf07d74d48e.png','QQ20241205-140636.png','.png','2024-12-06 11:35:49','yiji',NULL),(11,'8622916fca6e4b73b188e781fa611102.png','微信图片_20241017140004.png','.png','2024-12-06 11:35:52','yiji',NULL),(12,'af95a9d232754335a12545b4b55791fe.png','QQ20241205-140636.png','.png','2024-12-06 11:53:28','yiji',NULL),(13,'6af958a0cfea41afad6b4b513db30570.png','微信图片_20241017140004.png','.png','2024-12-06 11:53:31','yiji',NULL),(14,'51cdd3a8841b4a39bf921e0cd337faa5.png','QQ20241205-140636.png','.png','2024-12-06 11:53:47','yiji',NULL),(15,'56d0d566d07543a7b7a1418cdabedcf9.png','微信图片_20241017140004.png','.png','2024-12-06 11:53:51','yiji',NULL); /*Table structure for table `user_role` */ DROP TABLE IF EXISTS `user_role`; CREATE TABLE `user_role` ( `userid` bigint DEFAULT NULL COMMENT '用户ID', `role_id` bigint DEFAULT NULL COMMENT '角色ID', KEY `FK_Reference_3` (`userid`), KEY `FK_Reference_4` (`role_id`), CONSTRAINT `FK_Reference_3` FOREIGN KEY (`userid`) REFERENCES `ums_user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_Reference_4` FOREIGN KEY (`role_id`) REFERENCES `ums_role` (`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户角色'; /*Data for the table `user_role` */ insert into `user_role`(`userid`,`role_id`) values (1,1),(3,3),(7,3),(8,2),(9,5); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; ``` # 入门项目 ## 添加依赖 父项目 ```xml 4.0.0 com.neuedu.mybatis mybatis-learn pom ${revision} mybatis-01-helloworld mybatis-commons 17 17 1.0 8.0.33 3.5.16 mysql mysql-connector-java ${mysql.version} org.mybatis mybatis ${mybatis.version} ``` 子项目 ```xml com.neuedu.mybatis mybatis-learn ${revision} 4.0.0 mybatis-01-helloworld 17 17 com.neuedu.mybatis mybatis-commons 1.0 mysql mysql-connector-java org.mybatis mybatis ``` ## 创建主配置文件 ```xml ``` ## PO（dept对应的实体） ```java package com.neuedu.mybatis.po; import java.util.Date; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/410:50 */ public class Dept { private Integer dept_id; private String dept_name; private String loc; private String leader; private String del; private Date createtime; public Integer getDept_id() { return dept_id; } public void setDept_id(Integer dept_id) { this.dept_id = dept_id; } public String getDept_name() { return dept_name; } public void setDept_name(String dept_name) { this.dept_name = dept_name; } public String getLoc() { return loc; } public void setLoc(String loc) { this.loc = loc; } public String getLeader() { return leader; } public void setLeader(String leader) { this.leader = leader; } public String getDel() { return del; } public void setDel(String del) { this.del = del; } public Date getCreatetime() { return createtime; } public void setCreatetime(Date createtime) { this.createtime = createtime; } @Override public String toString() { return "Dept{" + "dept_id=" + dept_id + ", dept_name='" + dept_name + '\'' + ", loc='" + loc + '\'' + ", leader='" + leader + '\'' + ", del='" + del + '\'' + ", createtime=" + createtime + '}'; } } ``` ## 创建sql的映射文件(Mapper.xml) ![image-20250304105325856](imgs/image-20250304105325856.png) ```xml ``` ## 编码 SqlSessionFactory ```java import com.neuedu.mybatis.po.Dept; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/410:54 */ public class HelloWorld { public static void main(String[] args) throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //创建session对象 SqlSession session = sqlSessionFactory.openSession(); //使用 session 执行sql语句 List deptList = session.selectList("test.selectDept"); deptList.forEach(System.out::println); } } ``` ![image-20250304105933051](imgs/image-20250304105933051.png) # Lombok 引入插件 ````xml org.projectlombok lombok ```` 配置IDEA ![image-20250304112951913](imgs/image-20250304112951913.png) ## 修改实体类 ```java package com.neuedu.mybatis.po; import lombok.Data; import java.util.Date; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/410:50 * * JavaBean * * */ @Data public class Dept { private Integer dept_id; private String dept_name; private String loc; private String leader; private String del; private Date createtime; } ``` # 练习CRUD ## 查询单条结果 修改test.xml ```xml ``` 执行sql语句 ```java package com.neuedu.mybatis; import com.neuedu.mybatis.po.Dept; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.InputStream; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/411:49 */ public class CRUDTest { @Test public void testSelectById(){ try { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //创建session对象 SqlSession session = sqlSessionFactory.openSession(); int deptId = 5; Dept dept = session.selectOne("test.selectById",deptId); System.out.println("dept = " + dept); }catch (Exception e){ e.printStackTrace(); } } } ``` ## 添加一条 - 编写sql ```xml INSERT INTO `his`.`dept` ( `dept_name`, `loc`, `leader` ) VALUES ( #{dept_name}, #{loc}, #{leader} ) ``` - 调用sql ![image-20250304140029795](imgs/image-20250304140029795.png) ## 根据主键更新 sql ```xml UPDATE `his`.`dept` SET `dept_id` = #{deptId}, `dept_name` =#{deptName}, `loc` = #{loc}, `leader` = #{leader} WHERE `dept_id` = #{deptId} ``` 测试调用 ```java @Test public void testUpdate(){ SqlSession session = null; try { //创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); int deptId = 5; Dept dept = session.selectOne("test.selectDeptResultMap",deptId); //更新数据 dept.setLeader("刘备"); //影响函数 int count = session.update("test.updateById",dept); System.out.println(count); session.commit(); }catch (Exception e){ session.rollback(); e.printStackTrace(); }finally { if(session != null){ session.close(); } } } ``` ## 根据主键删除 sql ```xml delete from dept where dept_id = #{deptId} ``` 单元测试 ```java @Test public void testDelete(){ SqlSession session = null; try { //创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); //影响函数 int id = 10; int count = session.insert("test.deleteById",id); System.out.println(count); session.commit(); }catch (Exception e){ session.rollback(); e.printStackTrace(); }finally { if(session != null){ session.close(); } } } ``` ## 查询单值 ```xml ``` ```java @Test public void testcount(){ SqlSession session = null; try { //创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); int count = session.selectOne("test.count"); System.out.println("count = " + count); }catch (Exception e){ e.printStackTrace(); }finally { if(session != null){ session.close(); } } } ``` ## 使用转义字符 ```xml ``` ```java @Test public void testuseTransferredMeaning(){ SqlSession session = null; try { //创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); int deptId = 5; List deptList = session.selectList("test.useTransferredMeaning"); System.out.println("deptList = " + deptList); }catch (Exception e){ e.printStackTrace(); }finally { if(session != null){ session.close(); } } } ``` ## 多条件查询 ```xml ``` ```java @Test public void testSelectMutil(){ SqlSession session = null; try { //创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); Dept deptQueryParam = new Dept(); deptQueryParam.setDeptName("xxx"); deptQueryParam.setLoc("202"); List deptList = session.selectList("test.selectMutil",deptQueryParam); deptList.forEach(System.out::println); }catch (Exception e){ e.printStackTrace(); }finally { if(session != null){ session.close(); } } } ``` # 封装Dao（DeptDao） - selectList() 根据条件查询列表 - selectById(int id ) 根据主键查询 - count() 查询总条数 - updateById（Dept dept） 根据主键更新 可以有 updateByCondition - insert(Dept dept) 插入一条 - deleteById(int id) ```xml package com.neuedu.mybatis.dao; import com.neuedu.mybatis.po.Dept; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/510:26 */ public class DeptDao { /** * 根据条件查询列表 */ public List selectList(){ return null; } /** * 根据主键查询 * @param id */ public Dept selectById(int id ){ return null; } /** * 查询总条数 */ public long count(){ return -1; } /** * 根据主键更新 可以有 updateByCondition */ public int updateById(Dept dept){ return -1; } /** * public int * @param dept * @return */ public int insert(Dept dept){ return -1; } /** * 删除一条 * @param id * @return */ public int deleteById(int id){ return -1; } } ``` - 创建一个新项目 用于演示 mybatis-02-dao - ![image-20250305100120064](imgs/image-20250305100120064.png) ![image-20250305095728965](imgs/image-20250305095728965.png) ## 添加依赖 - junit - mybatis-commons ```xml junit junit com.neuedu.mybatis mybatis-commons ${revision} ``` ## 编写配置文件 mybatis-config.xml ```xml ``` ## DeptDao.xml ```xml UPDATE `his`.`dept` SET `dept_id` = #{deptId}, `dept_name` =#{deptName}, `loc` = #{loc}, `leader` = #{leader} WHERE `dept_id` = #{deptId} select LAST_INSERT_ID() INSERT INTO `his`.`dept` ( `dept_name`, `loc`, `leader` ) VALUES ( #{deptName}, #{loc}, #{leader} ) delete from dept where dept_id = #{deptId} ``` ## DeptDao ```java package com.neuedu.mybatis.dao; import com.neuedu.mybatis.po.Dept; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import java.util.ArrayList; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/510:26 */ public class DeptDao { private SqlSessionFactory sqlSessionFactory; public DeptDao(SqlSessionFactory sqlSessionFactory) { this.sqlSessionFactory = sqlSessionFactory; } /** * 根据条件查询列表 */ public List selectList() { List deptList = new ArrayList<>(); SqlSession session = null; try { // 创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); deptList = session.selectList("DeptDao.selectList"); } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); } } return deptList; } /** * 根据主键查询 * * @param id */ public Dept selectById(int id) { Dept dept = null; SqlSession session = null; try { // 创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); dept = session.selectOne("DeptDao.selectById", id); } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); } } return dept; } /** * 查询总条数 */ public long count() { long count = 0; SqlSession session = null; try { // 创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); count = session.selectOne("DeptDao.count"); } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); } } return count; } /** * 根据主键更新 可以有 updateByCondition */ public int updateById(Dept dept) { SqlSession session = null; int count = 0; try { // 创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); // 影响函数 count = session.update("DeptDao.updateById", dept); session.commit(); } catch (Exception e) { session.rollback(); e.printStackTrace(); } finally { if (session != null) { session.close(); } } return count; } /** * public int * * @param dept * @return */ public int insert(Dept dept) { SqlSession session = null; int count = 0; try { // 创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); // 影响函数 count = session.insert("DeptDao.insert", dept); session.commit(); } catch (Exception e) { session.rollback(); e.printStackTrace(); } finally { if (session != null) { session.close(); } } return count; } /** * 删除一条 * * @param id * @return */ public int deleteById(int id) { SqlSession session = null; int count = 0; try { // 创建session对象 SqlSession 用户接口 session = sqlSessionFactory.openSession(); // 影响函数 count = session.insert("DeptDao.deleteById", id); session.commit(); } catch (Exception e) { session.rollback(); e.printStackTrace(); } finally { if (session != null) { session.close(); } } return count; } } ``` ## 单元测试 ```java package com.neuedu.mybatis.dao; import com.neuedu.mybatis.po.Dept; import com.neuedu.mybatis.utils.MyBatisUtils; import org.apache.ibatis.session.SqlSessionFactory; import org.junit.Before; import org.junit.Test; import java.util.List; import static org.junit.Assert.*; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/510:33 */ public class DeptDaoTest { DeptDao deptDao; @Before public void setUp(){ String resource = "mybatis-config.xml"; SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory(resource); deptDao = new DeptDao(sqlSessionFactory); } @Test public void selectList() { List depts = deptDao.selectList(); System.out.println("depts = " + depts); } @Test public void selectById() { int id = 1; Dept dept = deptDao.selectById(id); System.out.println("dept = " + dept); } @Test public void count() { long count = deptDao.count(); System.out.println("count = " + count); } @Test public void updateById() { int id = 1; Dept dept = deptDao.selectById(id); dept.setDeptName(dept.getDeptName()+"-dao"); int count = deptDao.updateById(dept); System.out.println("count = " + count); } @Test public void insert() { Dept dept = new Dept(); dept.setDeptName("封装Dao"); int count = deptDao.insert(dept); System.out.println("count = " + count); } @Test public void deleteById() { int id = 17; int count = deptDao.deleteById(id); System.out.println("count = " + count); } } ``` # 封装Mapper * 1 Mapper.java 需要跟Mapper.xml 同名 * com.neuedu.mybatis.dao.DeptMapper.java 接口 * com.neuedu.mybatis.dao.DeptMapper.xml * 2 Mapper 和 Mapper.xlm 同源（同一个包） * 3 namespace 是 Mapper的全路径 com.neuedu.mybatis.dao.DeptMapper * * 4 方法 与 Statement ID 同名 > 说明: * 返回类型 决定了 底层调用的是 * selectList * selectOne--->底层调用SelectList 项目 ## mybatis-03-mapper ## ![image-20250305111354409](imgs/image-20250305111354409.png) ## pom ``` mybatis-learn com.neuedu.mybatis 1.0 4.0.0 mybatis-03-mapper 17 17 junit junit com.neuedu.mybatis mybatis-commons ${revision} ``` ## 主配置文件 ``` ``` ## Deptmapper.java ``` package com.neuedu.mybatis.dao; import com.neuedu.mybatis.po.Dept; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import java.util.ArrayList; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/510:26 * * Mapper 映射的规则 * * 1 Mapper.java 需要跟Mapper.xml 同名 * com.neuedu.mybatis.dao.DeptMapper.java 接口 * com.neuedu.mybatis.dao.DeptMapper.xml * 2 Mapper 和 Mapper.xlm 同源（同一个包） * 3 namespace 是 Mapper的全路径 com.neuedu.mybatis.dao.DeptMapper * * 4 方法 与 Statement ID 同名 * 5 返回类型 决定了 底层调用的是 * selectList * selectOne--->底层调用SelectList * * * */ public interface DeptMapper { public List selectList(); public Dept selectById(int id); public long count(); public int updateById(Dept dept); public int insert(Dept dept); public int deleteById(int id); } ``` ## DeptMapper.xml ```xml UPDATE `his`.`dept` SET `dept_id` = #{deptId}, `dept_name` =#{deptName}, `loc` = #{loc}, `leader` = #{leader} WHERE `dept_id` = #{deptId} select LAST_INSERT_ID() INSERT INTO `his`.`dept` ( `dept_name`, `loc`, `leader` ) VALUES ( #{deptName}, #{loc}, #{leader} ) delete from dept where dept_id = #{deptId} ``` ## 单元测试 ```java package com.neuedu.mybatis.dao; import com.neuedu.mybatis.po.Dept; import com.neuedu.mybatis.utils.MyBatisUtils; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.junit.Before; import org.junit.Test; import java.util.List; import static org.junit.Assert.*; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/511:09 */ public class DeptMapperTest { SqlSessionFactory sqlSessionFactory ; @Before public void setUp(){ String resource = "mybatis-config.xml"; sqlSessionFactory = MyBatisUtils.getSqlSessionFactory(resource); } @Test public void selectList() { SqlSession session = sqlSessionFactory.openSession(); //生成接口的代理对象 实现 DeptMapper deptMapper =session.getMapper(DeptMapper.class); List depts = deptMapper.selectList(); System.out.println("depts = " + depts); } @Test public void selectById() { } @Test public void count() { } @Test public void updateById() { } @Test public void insert() { } @Test public void deleteById() { } } ``` # 练习checkitem - 实体Checkitem - CheckitemMapper.java - CheckitemMapper.xml - 配置mybaits-config.xml - 测试CheckitemMapperTest ## 实体Checkitem ```java package com.neuedu.mybatis.po; import lombok.Data; import java.util.Date; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/410:50 * * JavaBean * * */ @Data public class Checkitem { private Integer check_id; private String name; private String price; private String del; private Date createtime; ``` ## ## CheckitemMapper.java ``` package com.neuedu.mybatis.dao; import com.neuedu.mybatis.po.Checkitem; import com.neuedu.mybatis.po.Dept; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/510:26 * * Mapper 映射的规则 * * 1 Mapper.java 需要跟Mapper.xml 同名 * com.neuedu.mybatis.dao.DeptMapper.java 接口 * com.neuedu.mybatis.dao.DeptMapper.xml * 2 Mapper 和 Mapper.xlm 同源（同一个包） * 3 namespace 是 Mapper的全路径 com.neuedu.mybatis.dao.DeptMapper * * 4 方法 与 Statement ID 同名 * 5 返回类型 决定了 底层调用的是 * selectList * selectOne--->底层调用SelectList * * * */ public interface CheckItemMapper { public List selectList(); public Checkitem selectById(int id); public long count(); public int updateById(Checkitem checkitem); public int insert(Checkitem checkitem); public int deleteById(int id); } ``` ## CheckitemMapper.xml - 配置mybaits-config.xml - 测试CheckitemMapperTest # 动态SQL Mybatis-04-dynamic-sql 复制了 Mybatis-03-mapper **动态SQL**主要用于解决查询条件不确定的情况。也就是说：在实际开发中，经常需要根据用户是否输入了某个值，来确定是否需要这个条件。 MyBatis中用于动态sql的元素主要有：if、where、trim、set、foreach、choose等 > > > if、where、trim、set、foreach、choose等 ## IF： if+where会实现以下功能： 1. 自动添加where 2. 不需要考虑where后是否加and，mybatis会自动处理 3. 不需要考虑是否加空格，mybatis会自动处理 4. 没有 else 标签，也没有 else if 标签。 注意： job!='' 此处只可以判断是否为空，不能判断是否为某个值。也就是说：job!='经理' 是不好使的。 ## choose when other wise ```xml ``` ```java @Test public void selectListWithChoose() { Dept deptQueryParam = new Dept(); // deptQueryParam.setDeptName("部"); // deptQueryParam.setLoc("201"); deptQueryParam.setLeader("张"); List depts = deptMapper.selectListWithChoose(deptQueryParam); System.out.println("depts = " + depts); } ``` ## trim标签 > trim标签可以在自己包含的内容中加上某些前缀或后缀，与之对应的属性是：prefix、suffix。 还可以把包含内容的开始内容覆盖，即忽略。也可以把结束的某些内容覆盖，对应的属性是：prefixOverrides、suffixOverrides ```xml select LAST_INSERT_ID() INSERT INTO `his`.`dept` `dept_name`, `loc`, `leader`, #{deptName}, #{loc}, #{leader}, ``` ```java @Test public void insertSelective() { try{ Dept dept = new Dept(); dept.setDeptName("封装Dao"); dept.setLeader("选择的地址"); int count = deptMapper.insertSelective(dept); System.out.println("count = " + count); session.commit(); }catch (Exception e){ session.rollback(); e.printStackTrace(); }finally { if(session != null){ session.close(); } } } ``` 根据选择项目更新 ```xml UPDATE `his`.`dept` `dept_id` = #{deptId}, `dept_name` =#{deptName}, `loc` = #{loc}, `leader` = #{leader}, WHERE `dept_id` = #{deptId} ``` ```java @Test public void updateSelectiveById() { // int id = 1; // Dept dept = deptMapper.selectById(id); Dept dept = new Dept(); dept.setDeptId(1); // dept.setLoc(null); try{ dept.setLeader("selective"); int count = deptMapper.updateSelectiveById(dept); System.out.println("count = " + count); session.commit(); }catch (Exception e){ session.rollback(); e.printStackTrace(); }finally { if(session != null){ session.close(); } } } ``` # 安装 git 拉取仓库 git clone https://gitee.com/hrbu-2022/mybatis.git 更新 git pull ![image-20250306104148121](imgs/image-20250306104148121.png) # 使用普通项目学习 ## 添加依赖 ``` junit junit 4.13.1 test mysql mysql-connector-java 8.0.33 org.mybatis mybatis 3.5.19 ``` ## mybatis-config.xml ``` ``` ## DeptMapper.xml ```xml UPDATE `his`.`dept` SET `dept_id` = #{deptId}, `dept_name` =#{deptName}, `loc` = #{loc}, `leader` = #{leader} WHERE `dept_id` = #{deptId} select LAST_INSERT_ID() INSERT INTO `his`.`dept` ( `dept_name`, `loc`, `leader` ) VALUES ( #{deptName}, #{loc}, #{leader} ) delete from dept where dept_id = #{deptId} ``` ## com.neuedu.mybatis.po.Dept ```java package com.neuedu.mybatis.po; import java.util.Date; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/410:50 * * JavaBean * * */ public class Dept { private Integer deptId; private String deptName; private String loc; private String leader; private String del; private Date createtime; public Integer getDeptId() { return deptId; } public void setDeptId(Integer deptId) { this.deptId = deptId; } public String getDeptName() { return deptName; } public void setDeptName(String deptName) { this.deptName = deptName; } public String getLoc() { return loc; } public void setLoc(String loc) { this.loc = loc; } public String getLeader() { return leader; } public void setLeader(String leader) { this.leader = leader; } public String getDel() { return del; } public void setDel(String del) { this.del = del; } public Date getCreatetime() { return createtime; } public void setCreatetime(Date createtime) { this.createtime = createtime; } @Override public String toString() { return "Dept{" + "deptId=" + deptId + ", deptName='" + deptName + '\'' + ", loc='" + loc + '\'' + ", leader='" + leader + '\'' + ", del='" + del + '\'' + ", createtime=" + createtime + '}'; } } ``` DeptMapper.java （相当于Dao Controller 、 Service、Dao） ```java package com.neuedu.mybatis.dao; import com.neuedu.mybatis.po.Dept; import java.util.List; /** * @author 金山 * 项目：mybatis-review * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/611:32 */ public interface DeptMapper { public List selectList(); } ``` 加载配置文件，调用Mapper 执行select方法 ```java package com.neuedu.mybatis; import com.neuedu.mybatis.dao.DeptMapper; import com.neuedu.mybatis.po.Dept; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.List; /** * @author 金山 * 项目：mybatis-review * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/611:34 */ public class Runner { public static void main(String[] args) throws IOException { String resource = "mybatis-config.xml"; InputStream resourceAsStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); //数据库的会话 SqlSession session = sqlSessionFactory.openSession(); //获取代理 DeptMapper mapper = session.getMapper(DeptMapper.class); List depts = mapper.selectList(); for (Dept dept : depts) { System.out.println("dept = " + dept); } } } ``` # 关联查询 > 一个员工 emp ， 一个部门 > > > > 一个部门 多个员工 > 多对多 学生 选课信息 课程 ## 数据库 ```sql /* SQLyog Ultimate v12.08 (64 bit) MySQL - 8.0.30 : Database - test ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; USE `test`; /*Table structure for table `dept` */ DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `deptno` int NOT NULL, `dname` varchar(14) DEFAULT NULL, `loc` varchar(13) DEFAULT NULL, PRIMARY KEY (`deptno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*Data for the table `dept` */ insert into `dept`(`deptno`,`dname`,`loc`) values (10,'测试Dao','NEW YORK'),(18,'dept1','loc1'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON'); /*Table structure for table `emp` */ DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `empno` int NOT NULL, `ename` varchar(10) DEFAULT NULL, `job` varchar(9) DEFAULT NULL, `mgr` int DEFAULT NULL, `hiredate` date DEFAULT NULL, `sal` decimal(7,2) DEFAULT NULL, `comm` decimal(7,2) DEFAULT NULL, `deptno` int DEFAULT NULL, PRIMARY KEY (`empno`), KEY `fk_deptno` (`deptno`), CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*Data for the table `emp` */ insert into `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) values (1234,'测试插入','SALESMAN',7698,'1981-09-28','99999.00','99999.99',10),(1235,'测试插入2','SALESMAN',NULL,NULL,NULL,NULL,10),(1236,'测试插入','SALESMAN',7698,'1981-09-28','99999.00','99999.99',10),(1238,'测试插入8','SALESMAN',7698,'1981-09-28','99999.00','99999.99',10),(1239,'测试插入9','SALESMAN',7698,'1981-09-28','99999.00','99999.99',10),(2002,'测试注入','job',7369,'2024-11-07','9999.00',NULL,10),(7369,'SMITH','CLERK',7902,'1980-12-17','800.00',NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20','1600.00','300.00',30),(7521,'WARD','SALESMAN',7698,'1981-02-22','1250.00','500.00',30),(7566,'JONES','MANAGER',7839,'1981-04-02','2975.00',NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28','1250.00','1400.00',30),(7698,'BLAKE','MANAGER',7839,'1981-05-01','2850.00',NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09','2450.00',NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19','3000.00',NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08','1500.00','0.00',30),(7876,'ADAMS','CLERK',7788,'1987-05-23','1100.00',NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03','950.00',NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03','3000.00',NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23','1300.00',NULL,10),(9876,'测试外连接',NULL,NULL,NULL,NULL,NULL,NULL); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; ``` ## 单表查询编写代码 ### 一对一 ```xml junit junit 4.13.1 test mysql mysql-connector-java 8.0.33 org.mybatis mybatis 3.5.19 ``` ### 编写实体类 ```java package com.neuedu.mybaits.po; import java.util.Date; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/614:36 */ public class Emp { private Integer empno; private String ename; private String job; private Integer mgr; private Date hiredate; private Double sal; private String comm; private Integer 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 Date getHiredate() { return hiredate; } public void setHiredate(Date hiredate) { this.hiredate = hiredate; } public Double getSal() { return sal; } public void setSal(Double sal) { this.sal = sal; } public String getComm() { return comm; } public void setComm(String 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 + '}'; } } ``` ``` package com.neuedu.mybatis.po; import java.util.Date; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/614:36 */ public class Dept { private Integer deptno; private String deptname; private String loc; public Integer getDeptno() { return deptno; } public void setDeptno(Integer deptno) { this.deptno = deptno; } public String getDeptname() { return deptname; } public void setDeptname(String deptname) { this.deptname = deptname; } public String getLoc() { return loc; } public void setLoc(String loc) { this.loc = loc; } @Override public String toString() { return "Dept{" + "deptno=" + deptno + ", deptname='" + deptname + '\'' + ", loc='" + loc + '\'' + '}'; } } ``` ### Mapper。xml ```xml ``` ```xml ``` ### Mapper。java ```java package com.neuedu.mybatis.mapper; import com.neuedu.mybatis.po.Emp; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/614:41 */ public interface EmpMapper { public List selectList(); } ``` ``` package com.neuedu.mybatis.mapper; import com.neuedu.mybatis.po.Dept; import com.neuedu.mybatis.po.Emp; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/614:41 */ public interface DeptMapper { public List selectList(); } ``` ### 主配置文件 mybaits-config.xml ```xml ``` ### jdbc.propertoies ```properties jdbc.username=root jdbc.password=root jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false jdbc.driverClass=com.mysql.cj.jdbc.Driver ``` ## 一对一 ### Sql语句 ```sql SELECT * FROM emp JOIN dept ON emp.`deptno` = dept.`deptno` ``` ### EmpMapper.xml ```xml ``` ## ## 一对多 一个部门有 多个 员工，维护 关系， 在Dept实体中添加 List（集合类型） empList属性（） Dept实体类改造 ```java package com.neuedu.mybatis.po; import java.util.Date; import java.util.List; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/614:36 */ public class Dept { private Integer deptno; private String dname; private String loc; private List empList; 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; } public List getEmpList() { return empList; } public void setEmpList(List empList) { this.empList = empList; } @Override public String toString() { return "Dept{" + "deptno=" + deptno + ", dname='" + dname + '\'' + ", loc='" + loc + '\'' + ", empList=" + empList + '}'; } } ``` ### sql语句 ```xml SELECT * FROM dept JOIN emp ON emp.`deptno` = dept.`deptno` ``` ### DeptMapper.xml 使用ResultMap 映射结果集到时候 使用 Collection标签 映射 从表的属性 ```xml ``` ![image-20250307091147767](imgs/image-20250307091147767.png) ## 延迟加载 全局配置，在mybatis-config.xml中添加延迟加载的开关 ```xml ``` ### 一对一 ```xml ``` ### EmpMapper.java ```java public List selectListLazy(); ``` ### 单元测试 ```java @Test public void selectListLazy() throws IOException { //SqlSessionFactory String resoueces = "mybatis-config.xml"; InputStream resourceAsStream = Resources.getResourceAsStream(resoueces); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); // SQLSession SqlSession session = sqlSessionFactory.openSession(); // 获取Mapper 代理对象 EmpMapper empMapper = session.getMapper(EmpMapper.class); // 多个sql 查询，可以 触发 延迟记载 List emps = empMapper.selectListLazy(); for (Emp emp : emps) { if(emp.getDept()!= null){ System.out.println("emp.getDept() = " + emp.getDept()); } } } ``` ## 一对多的延迟加载 - 开启 延迟加载开关 DeptMapper.xlm ```xml ``` ![image-20250307103736473](imgs/image-20250307103736473.png) 使用Debug单步执行 测试 getEmpList 之后才会查询从表数据 执行sql语句 ```java @org.junit.Test public void selectListCollectionEmpListLazy() throws IOException { //SqlSessionFactory String resoueces = "mybatis-config.xml"; InputStream resourceAsStream = Resources.getResourceAsStream(resoueces); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); // SQLSession SqlSession session = sqlSessionFactory.openSession(); // 获取Mapper 代理对象 DeptMapper empMapper = session.getMapper(DeptMapper.class); // 调用 selectList\ 延迟加载 先执行 select * from dept List depts = empMapper.selectListCollectionEmpListLazy(); for (Dept dept : depts) { System.out.println(dept.getEmpList()); System.out.println("dept = " + dept); } } ``` # 使用注解实现MyBatis映射 MyBatis也支持使用注解来配置映射语句。 主要有四种注解来实现增删改查：@Select、@Insert、@Update、@Delete # 使用工具 逆向生成 先创建表 生成java代码、 使用MybatisX ![image-20250307112643860](imgs/image-20250307112643860.png) ## 创建一个数据源 ![image-20250307112728224](imgs/image-20250307112728224.png) ![image-20250307112858460](imgs/image-20250307112858460.png) ![image-20250307112908097](imgs/image-20250307112908097.png) ## 生成代码 ![image-20250307112934861](imgs/image-20250307112934861.png) 设置基础包名（com.neuedu.mybatis），实体类的包名（po） ![image-20250307113124560](imgs/image-20250307113124560.png) ![image-20250307113340132](imgs/image-20250307113340132.png) ### 使用生成的代码 mybatis-07-mybatisx ![image-20250307114457035](imgs/image-20250307114457035.png) ![image-20250307114522593](imgs/image-20250307114522593.png) ![image-20250307114601428](imgs/image-20250307114601428.png) 修改项目依赖 ```xml mybatis-learn com.neuedu.mybatis 1.0 4.0.0 mybatis-07-mybatisx 17 17 org.projectlombok lombok 1.18.36 provided junit junit 4.13.1 test mysql mysql-connector-java 8.0.33 org.mybatis mybatis 3.5.19 ``` ### 主配置文件 ```xml ``` ### jdbc.properties ```properties jdbc.username=root jdbc.password=root jdbc.url=jdbc:mysql://127.0.0.1:3306/his?useSSL=false jdbc.driverClass=com.mysql.cj.jdbc.Driver ``` ### 测试 ```java package com.neuedu.mybatis.mapper; import com.neuedu.mybatis.po.Dept; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; import static org.junit.Assert.*; /** * @author 金山 * 项目：mybatis-learn * site: https://blog.fulfill.com.cn * 描述 * @data 2025/3/711:48 */ public class DeptMapperTest { @Test public void deleteByPrimaryKey() { } @Test public void insert() { } @Test public void insertSelective() { } @Test public void selectByPrimaryKey() throws IOException { //SqlSessionFactory String resoueces = "mybatis-config.xml"; InputStream resourceAsStream = Resources.getResourceAsStream(resoueces); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); // SQLSession SqlSession session = sqlSessionFactory.openSession(); // 获取Mapper 代理对象 DeptMapper deptMapper = session.getMapper(DeptMapper.class); long id = 1; Dept dept = deptMapper.selectByPrimaryKey(id); System.out.println("dept = " + dept); } @Test public void updateByPrimaryKeySelective() { } @Test public void updateByPrimaryKey() { } } ```