金山老师的笔记

Appearance

13. Spring Boot对jdbc的支持 ​

13.1. 准备数据库 ​

sql
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.18 : Database - ssm-java1
*********************************************************************
*/


/*!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*/`ssm-java1` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `ssm-java1`;

/*Table structure for table `check_apply` */

DROP TABLE IF EXISTS `check_apply`;

CREATE TABLE `check_apply` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `register_id` int(11) DEFAULT NULL COMMENT '病历号',
  `item_id` int(11) DEFAULT NULL COMMENT '项目id',
  `item_name` varchar(100) DEFAULT NULL COMMENT '项目名称',
  `fee` decimal(8,2) DEFAULT NULL COMMENT '检查费用',
  `status` int(11) DEFAULT NULL COMMENT '状态',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COMMENT='检查申请';

/*Data for the table `check_apply` */

insert  into `check_apply`(`id`,`register_id`,`item_id`,`item_name`,`fee`,`status`,`active`,`createtime`) values (4,4,1,'甲状腺超声（小器官）','110.00',1,1,'2021-01-06 10:05:26'),(5,4,2,'腹部超声','140.00',1,1,'2021-01-06 10:05:26'),(6,7,1,'甲状腺超声（小器官）','110.00',1,1,'2021-01-06 10:09:47'),(7,7,2,'腹部超声','140.00',1,1,'2021-01-06 10:09:47'),(14,8,1,'甲状腺超声（小器官）','110.00',1,1,'2021-01-06 10:13:45'),(15,8,2,'腹部超声','140.00',1,1,'2021-01-06 10:13:45'),(16,8,3,'腋窝超声','50.00',1,1,'2021-01-06 10:20:50'),(17,2,1,'甲状腺超声（小器官）','110.00',4,1,'2021-01-06 11:01:06'),(18,2,2,'腹部超声','140.00',4,1,'2021-01-06 11:01:06'),(19,21,1,'甲状腺超声（小器官）','110.00',2,1,'2021-01-07 15:02:16'),(20,21,2,'腹部超声','140.00',4,1,'2021-01-07 15:02:16');

/*Table structure for table `check_item` */

DROP TABLE IF EXISTS `check_item`;

CREATE TABLE `check_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(100) DEFAULT NULL COMMENT '检查名称',
  `fee` decimal(8,2) DEFAULT NULL COMMENT '检查费用',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='检查项目';

/*Data for the table `check_item` */

insert  into `check_item`(`id`,`name`,`fee`,`active`,`createtime`) values (1,'甲状腺超声（小器官）','110.00',1,'2021-01-05 11:04:44'),(2,'腹部超声','140.00',1,'2021-01-05 13:40:09'),(3,'腋窝超声','50.00',1,'2021-01-05 13:40:27'),(4,'测试待删除','120.00',0,'2021-01-05 13:41:07');

/*Table structure for table `constant_item` */

DROP TABLE IF EXISTS `constant_item`;

CREATE TABLE `constant_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `type_id` int(11) DEFAULT NULL COMMENT '类别id',
  `code` varchar(100) DEFAULT NULL COMMENT '常数项代码',
  `name` varchar(100) DEFAULT NULL COMMENT '常数项名称',
  `sort` int(11) DEFAULT NULL COMMENT '排序',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='常数项表';

/*Data for the table `constant_item` */

insert  into `constant_item`(`id`,`type_id`,`code`,`name`,`sort`,`active`,`createtime`) values (1,1,'1','男',1,1,'2020-12-29 13:40:20'),(2,1,'2','女士',1,1,'2020-12-29 14:05:58'),(3,1,'3','33',333,0,'2020-12-29 14:07:05'),(4,5,'SHOW','展示菜单',0,1,'2021-01-04 11:28:02'),(5,5,'BTN','按钮',0,1,'2021-01-04 11:28:14'),(6,6,'1','是',0,1,'2021-01-04 14:02:59'),(7,6,'2','否',2,1,'2021-01-04 14:03:10'),(8,7,'1','已挂号',0,1,'2021-01-04 14:05:48'),(9,7,'2','已接诊',2,1,'2021-01-04 14:06:05'),(10,7,'3','已退号',3,1,'2021-01-04 14:06:16'),(11,8,'1','待缴费',1,1,'2021-01-06 14:03:27'),(12,8,'2','待检查',2,1,'2021-01-06 14:03:44'),(13,8,'3','已检查',3,1,'2021-01-06 14:03:55'),(14,8,'4','已退费',4,1,'2021-01-06 14:04:04');

/*Table structure for table `constant_type` */

DROP TABLE IF EXISTS `constant_type`;

CREATE TABLE `constant_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `code` varchar(100) DEFAULT NULL COMMENT '代码',
  `name` varchar(100) DEFAULT NULL COMMENT '名称',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='常数类别';

/*Data for the table `constant_type` */

insert  into `constant_type`(`id`,`code`,`name`,`active`,`createtime`) values (1,'XB','性别',1,'2020-12-29 11:32:19'),(2,'GHZT','挂号状态',1,'2020-12-29 11:51:25'),(3,'JCZT','检查状态',1,'2020-12-29 11:52:26'),(4,'JYZT','检验状态',1,'2020-12-29 14:04:48'),(5,'MENU_TYPE','菜单类型',1,'2021-01-04 11:27:35'),(6,'IS_BOOK','是否需要病历本',1,'2021-01-04 14:02:38'),(7,'REGISTER_STATUS','挂号状态',1,'2021-01-04 14:05:36'),(8,'APPLY_STATUS','申请状态（检查、检验）',1,'2021-01-06 14:02:19');

/*Table structure for table `department` */

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(100) DEFAULT NULL COMMENT '名称',
  `address` varchar(200) DEFAULT NULL COMMENT '办公地址',
  `leader` varchar(100) DEFAULT NULL COMMENT '负责人',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='科室';

/*Data for the table `department` */

insert  into `department`(`id`,`name`,`address`,`leader`,`active`,`createtime`) values (1,'神经内科','1111','1',1,'2020-12-23 09:06:58'),(2,'消化科','内科楼101','扁鹊主任',1,'2021-01-04 15:24:31'),(3,'普外科','外科大楼2层','孙医生',1,'2021-01-04 15:41:49');

/*Table structure for table `inspect_apply` */

DROP TABLE IF EXISTS `inspect_apply`;

CREATE TABLE `inspect_apply` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `item_id` int(11) DEFAULT NULL COMMENT '项目id',
  `item_name` varchar(100) DEFAULT NULL COMMENT '项目名称',
  `fee` decimal(8,2) DEFAULT NULL COMMENT '检查费用',
  `status` int(11) DEFAULT NULL COMMENT '状态',
  `register_id` int(11) DEFAULT NULL COMMENT '病历号',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='检验申请';

/*Data for the table `inspect_apply` */

insert  into `inspect_apply`(`id`,`item_id`,`item_name`,`fee`,`status`,`register_id`,`active`,`createtime`) values (1,1,'乙肝抗原','200.00',4,2,1,'2021-01-06 11:12:06'),(2,2,'血常规','40.00',4,2,1,'2021-01-06 11:12:06'),(3,1,'乙肝抗原','200.00',2,21,1,'2021-01-07 15:02:25'),(4,2,'血常规','40.00',2,21,1,'2021-01-07 15:02:26');

/*Table structure for table `inspect_item` */

DROP TABLE IF EXISTS `inspect_item`;

CREATE TABLE `inspect_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(100) DEFAULT NULL COMMENT '检查名称',
  `fee` decimal(8,2) DEFAULT NULL COMMENT '检查费用',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='检验项目';

/*Data for the table `inspect_item` */

insert  into `inspect_item`(`id`,`name`,`fee`,`active`,`createtime`) values (1,'乙肝抗原','200.00',1,'2021-01-05 10:57:47'),(2,'血常规','40.00',1,'2021-01-05 11:09:10'),(3,'肝功5项','60.00',1,'2021-01-05 11:10:12'),(4,'尿常规','60.00',1,'2021-01-05 13:51:19'),(5,'测试删除项目','10.00',0,'2021-01-05 13:51:46');

/*Table structure for table `menu` */

DROP TABLE IF EXISTS `menu`;

CREATE TABLE `menu` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `menu_name` varchar(100) DEFAULT NULL COMMENT '菜单名称',
  `url` varchar(100) DEFAULT NULL COMMENT '菜单url',
  `parent_id` int(11) DEFAULT NULL COMMENT '上级菜单id',
  `menu_type` int(10) DEFAULT NULL COMMENT '菜单类型1 展示、2按钮',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='菜单';

/*Data for the table `menu` */

insert  into `menu`(`menu_id`,`menu_name`,`url`,`parent_id`,`menu_type`,`active`,`createtime`) values (1,'系统管理',NULL,NULL,1,1,'2020-12-28 14:13:19'),(2,'用户管理','/user',1,1,1,'2020-12-28 14:54:43'),(4,'角色管理','/role',1,1,1,'2020-12-28 14:55:59'),(5,'挂号收费',NULL,NULL,1,1,'2020-12-28 15:51:32'),(6,'挂号','/regist',5,1,1,'2020-12-28 15:51:47'),(7,'收费','/fee',5,1,1,'2020-12-28 15:52:06'),(8,'退号','unregist',5,1,1,'2020-12-28 15:52:16'),(9,'退费','refund',5,1,1,'2020-12-28 15:52:24'),(10,'用户查询','/user?type=query',2,2,1,'2021-01-04 11:21:55'),(11,'用户添加','/user?type=add',2,2,1,'2021-01-04 11:24:46'),(12,'用户所有操作','/user',2,2,1,'2021-01-04 11:25:14'),(13,'菜单管理','/menu',1,1,1,'2021-01-07 11:38:49'),(14,'门诊医生',NULL,NULL,1,1,'2021-01-07 11:39:57'),(15,'门诊病历','doctor',14,1,1,'2021-01-07 11:41:40'),(16,'基础数据管理',NULL,NULL,1,1,'2021-01-07 11:41:53'),(17,'部门管理','dept',16,1,1,'2021-01-07 11:42:01'),(18,'挂号级别管理','regist_level',16,1,1,'2021-01-07 11:42:11'),(19,'检查项目维护','check_item',16,1,1,'2021-01-07 11:42:15'),(20,'检验项目维护','inspect_item',16,1,1,'2021-01-07 11:42:23'),(21,'常数类别','constant_type',16,1,1,'2021-01-07 11:42:31'),(22,'常数项目管理','constant_item',16,1,1,'2021-01-07 11:42:40'),(23,'角色的处理','/role',4,2,1,'2021-01-07 14:16:38'),(24,'患者的信息','/register',15,2,1,'2021-01-07 14:18:21'),(25,'医生相关的操作','/doctor',24,2,1,'2021-01-07 14:19:24'),(26,'部门的操作','/dept',2,2,1,'2021-01-07 14:51:05'),(27,'挂号级别查询','/regist_level',2,2,1,'2021-01-07 14:51:25');

/*Table structure for table `regist_level` */

DROP TABLE IF EXISTS `regist_level`;

CREATE TABLE `regist_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(100) DEFAULT NULL COMMENT '名称',
  `fee` decimal(8,2) DEFAULT NULL COMMENT '费用',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='挂号级别';

/*Data for the table `regist_level` */

insert  into `regist_level`(`id`,`name`,`fee`,`active`,`createtime`) values (1,'专家','500.00',1,'2021-01-04 13:58:22'),(2,'教授','400.00',1,'2021-01-04 13:58:40'),(3,'主治','30.00',1,'2021-01-04 13:58:44'),(4,'住院医师','10.00',1,'2021-01-04 13:59:09'),(5,'测试级别','30.50',1,'2021-01-04 14:24:36'),(6,'实习大夫','2.00',1,'2021-01-04 14:55:20');

/*Table structure for table `register` */

DROP TABLE IF EXISTS `register`;

CREATE TABLE `register` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id病历号',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `gender` int(11) DEFAULT NULL COMMENT '性别',
  `idno` varchar(100) DEFAULT NULL COMMENT '身份证号',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `address` varchar(200) DEFAULT NULL COMMENT '家庭住址',
  `regsit_level_id` int(11) DEFAULT NULL COMMENT '挂号级别',
  `dept_id` int(11) DEFAULT NULL COMMENT '挂号科室',
  `doctor_id` int(11) DEFAULT NULL COMMENT '看诊医生',
  `book` int(11) DEFAULT NULL COMMENT '是否要病历本',
  `visittime` date DEFAULT NULL COMMENT '看诊时间',
  `fee` decimal(8,2) DEFAULT NULL COMMENT '挂号费用',
  `readme` varchar(500) DEFAULT NULL COMMENT '主诉',
  `present` varchar(500) DEFAULT NULL COMMENT '现病史',
  `present_treat` varchar(500) DEFAULT NULL COMMENT '现病史治疗情况',
  `history` varchar(500) DEFAULT NULL COMMENT '既往史',
  `allergy` varchar(500) DEFAULT NULL COMMENT '过敏史',
  `disease` varchar(500) DEFAULT NULL COMMENT '确诊疾病',
  `suit` varchar(500) DEFAULT NULL COMMENT '处置方案',
  `drug` varchar(500) DEFAULT NULL COMMENT '药品清单',
  `status` int(11) DEFAULT NULL COMMENT '状态',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COMMENT='诊疗信息';

/*Data for the table `register` */

insert  into `register`(`id`,`name`,`gender`,`idno`,`birthday`,`age`,`address`,`regsit_level_id`,`dept_id`,`doctor_id`,`book`,`visittime`,`fee`,`readme`,`present`,`present_treat`,`history`,`allergy`,`disease`,`suit`,`drug`,`status`,`active`,`createtime`) values (1,'张飞',1,'232323232136546456',NULL,40,NULL,1,25,NULL,1,'2021-01-04','501.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,'2021-01-04 14:07:05'),(2,'张三',1,'1212121212',NULL,1212,'12121212112',1,1,NULL,1,NULL,'500.00','主诉1','现病史2','现病史治疗情况2','既往史2','过敏史2',NULL,NULL,NULL,2,1,'2021-01-05 09:22:56'),(3,'关羽',1,'1212121221121211','2010-01-04',50,'黑龙江齐齐哈尔',1,1,27,1,NULL,'500.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,0,'2021-01-05 09:26:50'),(4,'刘备',1,'1212121221121211',NULL,50,'黑龙江齐齐哈尔',2,1,27,1,NULL,'500.00','主诉12111','现病1211','现病史治疗情221','既往史321','过敏史421',NULL,NULL,NULL,2,1,'2021-01-05 09:27:19'),(5,'孙悟空',1,'1212121221121211','2010-01-04',50,'黑龙江齐齐哈尔',1,1,27,1,NULL,'500.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,'2021-01-05 09:28:56'),(6,'刘备',1,'1212121221121211','2010-01-04',50,'黑龙江齐齐哈尔',2,1,27,1,'2021-01-07','500.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,'2021-01-05 09:30:34'),(7,'李逵',1,'1212121221121211',NULL,50,'黑龙江齐齐哈尔',1,1,27,1,'2021-01-07','500.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,1,'2021-01-05 09:36:54'),(8,'诸葛亮',1,'1212121221121211',NULL,50,'黑龙江齐齐哈尔',2,1,27,1,'2021-01-07','501.00','122','112','12','12','121212',NULL,NULL,NULL,1,1,'2021-01-05 09:41:52'),(9,'关羽',1,'54654654165464564','2021-01-12',50,'12121212121212',1,1,27,1,'2021-01-06','501.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,'2021-01-05 09:46:40'),(12,'诸葛亮',1,'1212121','2010-01-04',80,'东吴',2,2,25,1,'2021-01-06','401.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,'2021-01-05 10:53:19'),(13,'孙尚香',2,'5464854654654','2017-01-03',18,'东吴',2,2,25,1,'2021-01-08','401.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,'2021-01-05 10:54:23'),(15,'邹兆龙',1,'45651654684651651','2011-01-03',48,'32132132',1,1,27,1,'2021-01-07','501.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,'2021-01-06 10:29:41'),(16,'邹毅',1,'121212112212','2015-12-28',55,'1212121212',1,2,23,0,'2021-01-07','500.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,'2021-01-06 10:32:38'),(17,'徐玮辰',1,'12121212121212',NULL,50,'大撒旦法师法',2,1,28,0,'2021-01-13','400.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,1,'2021-01-06 10:35:53'),(18,'徐罗成',1,'12121212',NULL,12,'是大是大非',1,1,27,0,'2021-01-06','500.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,1,'2021-01-06 10:44:08'),(19,'杨旭',1,'231321321',NULL,50,'1212121212',1,2,23,0,'2021-01-08','500.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,1,'2021-01-07 14:55:43'),(20,'露露',2,'121212121','2016-01-04',50,'1211212',1,2,29,1,'2021-01-08','501.00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,'2021-01-07 14:56:42'),(21,'韦吉亮',1,'121212',NULL,50,'121212',1,2,23,1,'2021-01-07','501.00','主诉','现病史','现病史治疗情况','既往史','过敏史\n',NULL,'尊医嘱，多喝水，多喝热水',NULL,2,1,'2021-01-07 15:01:45');

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色id',
  `role_name` varchar(100) DEFAULT NULL COMMENT '角色名称',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='角色';

/*Data for the table `role` */

insert  into `role`(`role_id`,`role_name`,`active`,`createtime`) values (1,'门诊医生',1,'2020-12-28 13:38:52'),(2,'挂号收费员',1,'2020-12-28 13:39:21'),(3,'系统管理员',1,'2020-12-28 13:39:41'),(4,'测试角色',0,'2020-12-28 13:59:02'),(5,'测试校验',1,'2021-01-07 10:49:23');

/*Table structure for table `role_menu` */

DROP TABLE IF EXISTS `role_menu`;

CREATE TABLE `role_menu` (
  `menu_id` int(11) NOT NULL COMMENT '菜单id',
  `role_id` int(11) NOT NULL COMMENT '角色id',
  PRIMARY KEY (`menu_id`,`role_id`),
  KEY `FK_Reference_2` (`role_id`),
  CONSTRAINT `FK_Reference_1` FOREIGN KEY (`menu_id`) REFERENCES `menu` (`menu_id`),
  CONSTRAINT `FK_Reference_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色菜单信息';

/*Data for the table `role_menu` */

insert  into `role_menu`(`menu_id`,`role_id`) values (5,2),(6,2),(7,2),(8,2),(9,2),(1,3),(2,3),(4,3),(5,3),(6,3),(7,3),(8,3),(9,3),(10,3),(11,3),(12,3),(13,3),(14,3),(15,3),(16,3),(17,3),(18,3),(19,3),(20,3),(21,3),(22,3),(23,3),(24,3),(25,3),(26,3),(27,3);

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '医生id',
  `username` varchar(100) DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `realname` varchar(100) DEFAULT NULL COMMENT '真实姓名',
  `telephone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `dept_id` int(11) DEFAULT NULL COMMENT 'id',
  `user_type` int(11) DEFAULT NULL COMMENT '医生类型',
  `regist_level` int(10) DEFAULT NULL COMMENT '医生级别',
  `lastlogin` datetime DEFAULT NULL COMMENT '最后登录时间',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`),
  KEY `FK_Reference_5` (`dept_id`),
  CONSTRAINT `FK_Reference_5` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8 COMMENT='医生-用户信息';

/*Data for the table `user` */

insert  into `user`(`user_id`,`username`,`password`,`realname`,`telephone`,`dept_id`,`user_type`,`regist_level`,`lastlogin`,`active`,`createtime`) values (21,'admin','123456','管理员','13888888880',1,1,1,NULL,0,'2020-12-23 09:07:06'),(22,'jshand','123456','姓名1','13888888881',1,1,1,NULL,0,'2020-12-23 09:07:06'),(23,'huatuo','123456','华佗','13888888882',2,1,1,NULL,1,'2020-12-23 09:07:06'),(24,'admin3','1234563','姓名3','13888888883',2,1,2,NULL,1,'2020-12-23 09:07:06'),(25,'bianque','1234564','扁鹊','13888888884',2,1,2,NULL,1,'2020-12-23 09:07:06'),(26,'admin5','1234565','姓名5','13888888885',2,1,2,NULL,1,'2020-12-23 09:07:06'),(27,'admin6','1234566','姓名6','13888888886',1,1,1,NULL,1,'2020-12-23 09:07:06'),(28,'admin7','1234567','姓名7','13888888887',1,1,2,NULL,1,'2020-12-23 09:07:06'),(29,'admin8','1234568','姓名8','13888888888',2,1,1,NULL,1,'2020-12-23 09:07:06'),(30,'admin9','1234569','姓名dddddd','13888888889',2,1,2,NULL,1,'2020-12-23 09:07:06'),(31,'admin10','12345610','姓名10','138888888810',1,1,1,NULL,1,'2020-12-23 09:07:06'),(32,'admin11','12345611','姓名11','138888888811',2,1,2,NULL,1,'2020-12-23 09:07:06'),(33,'admin12','12345612','姓名12','138888888812',1,1,1,NULL,1,'2020-12-23 09:07:06'),(34,'admin13','12345613','姓名13','138888888813',1,1,2,NULL,1,'2020-12-23 09:07:06'),(35,'admin14','12345614','姓名14','138888888814',1,1,1,NULL,1,'2020-12-23 09:07:06'),(36,'admin15','12345615','姓名15','138888888815',1,1,2,NULL,1,'2020-12-23 09:07:06'),(37,'admin16','12345616','姓名16','138888888816',1,1,NULL,NULL,1,'2020-12-23 09:07:06'),(38,'admin17','12345617','姓名17','138888888817',1,1,NULL,NULL,1,'2020-12-23 09:07:06'),(39,'admin18','12345618','姓名18','138888888818',1,1,NULL,NULL,1,'2020-12-23 09:07:06'),(40,'admin19','12345619','姓名19','138888888819',1,1,NULL,NULL,1,'2020-12-23 09:07:06'),(51,'vuejs','123456','张医生','17745125669',2,1,NULL,NULL,1,'2020-12-25 09:19:19'),(52,'aaa','123456','135341','32131321',2,1,NULL,NULL,1,'2020-12-25 09:21:19'),(53,'abcbdb','123456','45646','121212',NULL,1,NULL,NULL,1,'2020-12-25 09:22:19'),(54,'aaaa','11212','121212','1212',2,1,NULL,NULL,1,'2020-12-25 09:23:31'),(55,'abcdef','123456','4564864','321321321',NULL,1,NULL,NULL,1,'2020-12-25 09:24:51'),(56,'lastuser','123456','789789','6541651',NULL,1,NULL,NULL,1,'2020-12-25 09:27:06'),(57,'l22222','12345864646','3213','21321',NULL,1,NULL,NULL,1,'2020-12-25 09:27:50'),(58,'qqq','qqq','qqq','18404075932',NULL,1,NULL,NULL,1,'2020-12-28 08:59:02'),(59,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,1,'2021-01-04 16:43:10');

/*Table structure for table `user_role` */

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL COMMENT '医生id',
  `role_id` int(11) NOT NULL COMMENT '角色id',
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `FK_Reference_4` (`role_id`),
  CONSTRAINT `FK_Reference_3` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
  CONSTRAINT `FK_Reference_4` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户角色';

/*Data for the table `user_role` */

insert  into `user_role`(`user_id`,`role_id`) values (23,1),(25,1),(23,2),(21,3),(23,3),(25,3);

/*!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 */;

13.2. 准备项目 ​

img

img

img

img

img

13.2.1. 最终的pom文档 ​

xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springboot-learn-parent</artifactId>
        <groupId>com.neuedu.boot</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
    <packaging>jar</packaging>
    <artifactId>09-boot-jdbc-1</artifactId>


    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- 解析jsp类库  -->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
        </dependency>


        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>



        <!-- JDBC-启动器, 默认的数据源 HikariCP -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- JDBC-启动器, 默认的数据源 HikariCP -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>

    </dependencies>



</project>

11.3. 配置数据源 ​

https://www.cnblogs.com/panchanggui/p/10405244.html

img

img

yml
server:
  port: 80

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/ssm-java1
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: root

11.4. 编写数据操作语句 ​

11.4.1. UserDao对象 ​

java
package com.neuedu.his.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDao {

    @Autowired
    JdbcTemplate jt;

    public List list(){
        String sql ="select * from user ";
        return jt.queryForList(sql);
    }


}

11.5. 单元测试 ​

java
package com.neuedu.his.dao;

import com.neuedu.his.App09;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest(classes= App09.class)
class UserDaoTest {

    @Autowired
    UserDao userDao;


    @Test
    public void test(){

        List list = userDao.list();
        for (Object o : list) {
            System.out.println(o);
        }
    }

}

Released under the MIT License.

Copyright © 2024-present 金山老师