通用ORM模板

像太阳那样耀眼

已于 2023-06-12 20:10:53 修改

阅读量149

点赞数

文章标签： mysql

于 2023-06-12 19:54:33 首次发布

本文链接：https://blog.csdn.net/m0_58259152/article/details/131175696

版权

数据库数据

/*
 Navicat Premium Data Transfer

 Source Server         : zzj
 Source Server Type    : MySQL
 Source Server Version : 50733
 Source Host           : localhost:3306
 Source Schema         : goods

 Target Server Type    : MySQL
 Target Server Version : 50733
 File Encoding         : 65001

 Date: 12/06/2023 20:02:14
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books`  (
  `book_id` int(6) NOT NULL,
  `type_id` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `book_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `author` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `publisher` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` int(3) NULL DEFAULT NULL,
  PRIMARY KEY (`book_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES (109101, 'BAS', '高等数学', '李方健', '清华大学出版社', 20);
INSERT INTO `books` VALUES (109104, 'BAS', '离散数学', '孙德风', '天津大学出版社', 22);
INSERT INTO `books` VALUES (109107, 'ELC', '数字电路', '刘国庆', '高等教育出版社', 13);
INSERT INTO `books` VALUES (109109, 'CMP', 'C语言程序设计', '谭浩强', '清华大学出版社', 15);
INSERT INTO `books` VALUES (109110, 'CMP', '数据结构', '王志国', '高等教育出版社', 32);
INSERT INTO `books` VALUES (109111, 'CMP', '操作系统', '王志国', '高等教育出版社', 25);
INSERT INTO `books` VALUES (109112, 'CMP', '计算机组成原理', '张小敏', '南大在学出版社', 27);
INSERT INTO `books` VALUES (109113, 'CMP', '微机原理与接口技术', '刘国庆', '人民大学出版社', 34);
INSERT INTO `books` VALUES (109114, 'CMP', '数据库原理', '彭来德', '高等教育出版社', 16);
INSERT INTO `books` VALUES (109115, 'CMP', '计算机网络', '马国露', '人民大学出版社', 11);
INSERT INTO `books` VALUES (109116, 'CMP', '计算机网络', '谭浩强', '南大在学出版社', 13);
INSERT INTO `books` VALUES (109117, 'CMP', '编译原理', '方刚', '清华大学出版社', 38);
INSERT INTO `books` VALUES (209101, 'CMP', 'VB与WINDOWS程序设计', '谭浩强', '清华大学出版社', 30);
INSERT INTO `books` VALUES (209102, 'CMP', 'C++与面向对象技术', '谭浩强', '人民大学出版社', 19);
INSERT INTO `books` VALUES (209103, 'CMP', 'Java与网络程序设计', '付勇', '高等教育出版社', 20);
INSERT INTO `books` VALUES (209106, 'CMP', '单片机原理及应用', '刘国庆', '人民大学出版社', 22);
INSERT INTO `books` VALUES (209107, 'CMP', 'PLC原理及其应用开发', '刘国庆', '南大在学出版社', 16);
INSERT INTO `books` VALUES (209111, 'CMP', '人工智能导论', '丁宝康', '高等教育出版社', 18);
INSERT INTO `books` VALUES (209116, 'CMP', '信息论与编码学概论', '丁宝康', '高等教育出版社', 21);
INSERT INTO `books` VALUES (209117, 'CMP', '密码学基础', '张顺志', '人民大学出版社', 25);
INSERT INTO `books` VALUES (209123, 'CMP', 'Internet应用及网页设计', '李朋', '高等教育出版社', 16);
INSERT INTO `books` VALUES (209124, 'CMP', '多媒体技术及应用', '谭浩强', '高等教育出版社', 15);
INSERT INTO `books` VALUES (209130, 'CMP', 'PB与数据库应用开发', '张华强', '清华大学出版社', 42);
INSERT INTO `books` VALUES (209132, 'CMP', '计算机图形学', '徐志超', '人民大学出版社', 27);
INSERT INTO `books` VALUES (309102, 'CMP', '计算机学科教学论', '丁宝康', '高等教育出版社', 12);
INSERT INTO `books` VALUES (309103, 'CMP', '中学计算机教材研究与分析', '徐志超', '人民大学出版社', 14);
INSERT INTO `books` VALUES (309104, 'CMP', '信息技术概论', '付勇', '天津大学出版社', 24);
INSERT INTO `books` VALUES (309105, 'CMP', '数据库分析与设计', '丁宝康', '人民大学出版社', 30);
INSERT INTO `books` VALUES (309106, 'CMP', '数据库导论', '付勇', '清华大学出版社', 29);

-- ----------------------------
-- Table structure for borrow_info
-- ----------------------------
DROP TABLE IF EXISTS `borrow_info`;
CREATE TABLE `borrow_info`  (
  `reader_id` int(7) NOT NULL,
  `book_id` int(6) NOT NULL,
  `borrow_time` date NULL DEFAULT NULL,
  `notes` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`reader_id`, `book_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of borrow_info
-- ----------------------------
INSERT INTO `borrow_info` VALUES (34103, 109101, '2005-06-02', 'NULL');
INSERT INTO `borrow_info` VALUES (34301, 209130, '2005-06-14', 'NULL');
INSERT INTO `borrow_info` VALUES (34301, 209132, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (34301, 309102, '2005-06-09', 'NULL');
INSERT INTO `borrow_info` VALUES (34301, 309103, '2005-06-21', 'NULL');
INSERT INTO `borrow_info` VALUES (34301, 309104, '2005-06-14', 'NULL');
INSERT INTO `borrow_info` VALUES (34429, 109107, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134101, 109101, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134101, 109109, '2005-06-03', 'NULL');
INSERT INTO `borrow_info` VALUES (134102, 109104, '2005-06-08', 'NULL');
INSERT INTO `borrow_info` VALUES (134102, 109110, '2005-06-30', 'NULL');
INSERT INTO `borrow_info` VALUES (134103, 109111, '2005-06-13', 'NULL');
INSERT INTO `borrow_info` VALUES (134104, 109107, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134104, 109112, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134105, 109109, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134105, 109113, '2005-06-14', 'NULL');
INSERT INTO `borrow_info` VALUES (134106, 109114, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134107, 109115, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134108, 109116, '2005-06-12', 'NULL');
INSERT INTO `borrow_info` VALUES (134109, 109117, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134110, 209101, '2005-06-22', 'NULL');
INSERT INTO `borrow_info` VALUES (134111, 209102, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134112, 209103, '2005-06-16', 'NULL');
INSERT INTO `borrow_info` VALUES (134113, 209106, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134114, 209107, '2005-06-11', 'NULL');
INSERT INTO `borrow_info` VALUES (134115, 209111, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134116, 209106, '2005-06-14', 'NULL');
INSERT INTO `borrow_info` VALUES (134426, 109110, '2005-06-15', 'NULL');
INSERT INTO `borrow_info` VALUES (134427, 109111, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134428, 109112, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134429, 109113, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134430, 109114, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134430, 209103, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134430, 209106, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134430, 209107, '2005-06-21', 'NULL');
INSERT INTO `borrow_info` VALUES (134430, 209111, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134430, 209116, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134430, 209124, '2005-06-05', 'NULL');
INSERT INTO `borrow_info` VALUES (134431, 109115, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134432, 109116, '2005-06-14', 'NULL');
INSERT INTO `borrow_info` VALUES (134433, 109117, '2005-06-01', 'NULL');
INSERT INTO `borrow_info` VALUES (134434, 209101, '2005-06-02', 'NULL');
INSERT INTO `borrow_info` VALUES (134435, 209102, '2005-06-01', 'NULL');

-- ----------------------------
-- Table structure for goods_info
-- ----------------------------
DROP TABLE IF EXISTS `goods_info`;
CREATE TABLE `goods_info`  (
  `goodsid` int(11) NOT NULL AUTO_INCREMENT,
  `goodsname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `goodstype` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `goodsstore` int(11) NOT NULL,
  `goodsprice` decimal(8, 2) NOT NULL,
  PRIMARY KEY (`goodsid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods_info
-- ----------------------------
INSERT INTO `goods_info` VALUES (1, '佳洁士', '牙膏', 66, 300.00);
INSERT INTO `goods_info` VALUES (2, '舒肤佳', '香皂', 4, 500.00);
INSERT INTO `goods_info` VALUES (3, 'Dell', '电脑', 900, 98.00);
INSERT INTO `goods_info` VALUES (4, '小天鹅', '洗衣机', 800, 96.00);

-- ----------------------------
-- Table structure for readers
-- ----------------------------
DROP TABLE IF EXISTS `readers`;
CREATE TABLE `readers`  (
  `reader_id` int(7) NOT NULL,
  `name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sex` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '男',
  `dept` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `status` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `address` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`reader_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of readers
-- ----------------------------
INSERT INTO `readers` VALUES (34103, '范元帅', '男', '艺术学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (34301, '杨凡', '男', '电信学院', '教师', '八里台禄小区');
INSERT INTO `readers` VALUES (34429, '许丹丹', '女', '艺术学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134101, '陈超', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134102, '范金良', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134103, '国皓', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134104, '贺云龙', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134105, '刘德文', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134106, '彭俊', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134107, '彭志成', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134108, '沈举', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134109, '田强', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134110, '王旭', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134111, '吴卫', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134112, '熊敏', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134113, '颜东', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134114, '殷建鹏', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134115, '周春林', '男', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134116, '陈洁', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134117, '陈琪', '女', '计算机学院', '研究生', '师大南院');
INSERT INTO `readers` VALUES (134118, '丁璐', '女', '计算机学院', '研究生', '师大南院');
INSERT INTO `readers` VALUES (134119, '丁岩', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134120, '董蕾', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134121, '何芳', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134122, '蒋莎', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134123, '李颖', '女', '计算机学院', '研究生', '师大南院');
INSERT INTO `readers` VALUES (134124, '李芬', '女', '计算机学院', '研究生', '师大南院');
INSERT INTO `readers` VALUES (134125, '李锦萍', '女', '计算机学院', '教授', '师大南院');
INSERT INTO `readers` VALUES (134126, '李小汐', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134127, '李玥玖', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134128, '郦莎', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134129, '廖英', '女', '计算机学院', '本科生', '师大南院');
INSERT INTO `readers` VALUES (134130, '林琳', '女', '计算机学院', '教师', '师大南院');
INSERT INTO `readers` VALUES (134131, '林敏', '女', '计算机学院', '本科生', '师大南院');

-- ----------------------------
-- Table structure for tbl_class
-- ----------------------------
DROP TABLE IF EXISTS `tbl_class`;
CREATE TABLE `tbl_class`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tbl_class
-- ----------------------------
INSERT INTO `tbl_class` VALUES (1, '小班');
INSERT INTO `tbl_class` VALUES (2, '中班');
INSERT INTO `tbl_class` VALUES (3, '大班');

-- ----------------------------
-- Table structure for tbl_dept
-- ----------------------------
DROP TABLE IF EXISTS `tbl_dept`;
CREATE TABLE `tbl_dept`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `loc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of tbl_dept
-- ----------------------------
INSERT INTO `tbl_dept` VALUES (1, '研发部', '郑州');
INSERT INTO `tbl_dept` VALUES (2, '市场部', '北京');
INSERT INTO `tbl_dept` VALUES (3, '财务部', '成都');
INSERT INTO `tbl_dept` VALUES (4, '人事部', '杭州');
INSERT INTO `tbl_dept` VALUES (6, '商务部', '重庆');
INSERT INTO `tbl_dept` VALUES (12, '商务部', '青岛');
INSERT INTO `tbl_dept` VALUES (13, '商务部', '青岛');
INSERT INTO `tbl_dept` VALUES (14, '商务部', '青岛');

-- ----------------------------
-- Table structure for tbl_emp
-- ----------------------------
DROP TABLE IF EXISTS `tbl_emp`;
CREATE TABLE `tbl_emp`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `salary` double(10, 2) NULL DEFAULT NULL COMMENT '薪水',
  `birthday` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '出生日期',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '部门id 关联部门表',
  `headImg` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 24 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of tbl_emp
-- ----------------------------
INSERT INTO `tbl_emp` VALUES (1, '刘德华', 2500.00, '2000-10-11', 2, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (2, '张学友', 3200.00, '2001-11-15', 2, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/05.jpg');
INSERT INTO `tbl_emp` VALUES (3, '黎明', 2800.00, '2002-12-12', 4, '\r\n\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/09.jpg');
INSERT INTO `tbl_emp` VALUES (4, '郭富城', 5000.00, '2008-11-12', 3, '\r\n\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/09.jpg');
INSERT INTO `tbl_emp` VALUES (5, '张振嘉', 50000.00, '2010-12-12', 1, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (6, '谷歌', 122.00, '2023-05-01', 2, '\r\n\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/09.jpg');
INSERT INTO `tbl_emp` VALUES (7, '冻干粉', 12345.00, '2023-05-15', 2, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (8, '是多少', 1230.22, '2023-05-15', 3, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (9, '艾尔登', 123.00, '2023-05-08', 1, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (10, '第三方', 145.02, '2023-05-04', 3, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (11, '东方闪电', 1548.23, '2023-05-15', 2, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (12, '挖地方', 145.20, '2023-05-15', 4, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (13, '山东人', 165.23, '2023-05-11', 2, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (14, '色彩上午', 15.65, '2023-05-10', 2, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (15, '课骨干', 156.23, '2023-05-15', 2, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (16, '问题吧', 153.20, '2023-05-02', 3, '\r\nhttps://aaamain.oss-cn-beijing.aliyuncs.com/01.jpg');
INSERT INTO `tbl_emp` VALUES (19, '发发发', 888.80, '2023-05-15', 3, 'https://aaamain.oss-cn-beijing.aliyuncs.com/33f9dab9-e5d8-4171-b0c2-dcadcb43323104.jpg');
INSERT INTO `tbl_emp` VALUES (20, 'AAA', 150.56, '2023-05-08', 2, 'https://aaamain.oss-cn-beijing.aliyuncs.com/d0e5d586-9903-4fed-9652-0ac90b6add6f00.jpg');
INSERT INTO `tbl_emp` VALUES (21, '儿童', 12345.00, '2023-05-08', 3, 'https://aaamain.oss-cn-beijing.aliyuncs.com/75ef686b-7df1-4b8d-b6b9-99cd126c53b101.jpg');
INSERT INTO `tbl_emp` VALUES (22, '规划局', 12312.00, '2023-05-15', 2, 'https://aaamain.oss-cn-beijing.aliyuncs.com/35.jpg');
INSERT INTO `tbl_emp` VALUES (23, 'aaaa', 12345.00, '2023-05-15', 3, 'https://aaamain.oss-cn-beijing.aliyuncs.com/953d21ed-82e5-474e-9142-8486d743544210.jpg');

-- ----------------------------
-- Table structure for tbl_student
-- ----------------------------
DROP TABLE IF EXISTS `tbl_student`;
CREATE TABLE `tbl_student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `class_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `class_id`(`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tbl_student
-- ----------------------------
INSERT INTO `tbl_student` VALUES (1, '输入', 18, '男', 2);
INSERT INTO `tbl_student` VALUES (2, '二手', 20, '女', 3);
INSERT INTO `tbl_student` VALUES (3, '泰国', 18, '男', 3);
INSERT INTO `tbl_student` VALUES (4, '示范', 18, '男', 2);
INSERT INTO `tbl_student` VALUES (5, '覆盖', 19, '女', 1);
INSERT INTO `tbl_student` VALUES (6, '幅度', 20, '女', 1);
INSERT INTO `tbl_student` VALUES (14, '干饭', 18, '女', 2);
INSERT INTO `tbl_student` VALUES (15, '干饭', 20, '女', 1);

SET FOREIGN_KEY_CHECKS = 1;

引入依赖

<?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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>day01</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>


</project>

引入db.properties

jdbc.driverName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/goods?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
jdbc.username=root
jdbc.password=123456

定义实体类

package com.aaa.pojo;

import com.aaa.annotation.TableField;
import com.aaa.annotation.TableId;
import com.aaa.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/**
 * @ClassName: Dept
 * @Description: TODO
 * @date: 2023/6/12 17:32
 * @author: zzj
 * @Version: 1.0
 */

@TableName(value = "tbl_dept")
public class Dept {
    @TableId
    private int id;
    @TableField(value = "name")
    private String name;
    private String loc;

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", loc='" + loc + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }
}

package com.aaa.pojo;

import com.aaa.annotation.TableField;
import com.aaa.annotation.TableId;
import com.aaa.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/**
 * @ClassName: Emp
 * @Description: TODO
 * @date: 2023/6/12 17:32
 * @author: zzj
 * @Version: 1.0
 */
@TableName(value = "tbl_emp")
public class Emp {
    @TableId
    private int id;
    private String name;
    private double salary;
    private String birthday;
    @TableField(value = "dept_id")
    private int deptId;
    private String headImg;

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", birthday='" + birthday + '\'' +
                ", deptId=" + deptId +
                ", headImg='" + headImg + '\'' +
                '}';
    }

    public Emp() {
    }

    public Emp(int id, String name, double salary, String birthday, int deptId, String headImg) {
        this.id = id;
        this.name = name;
        this.salary = salary;
        this.birthday = birthday;
        this.deptId = deptId;
        this.headImg = headImg;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public int getDeptId() {
        return deptId;
    }

    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }

    public String getHeadImg() {
        return headImg;
    }

    public void setHeadImg(String headImg) {
        this.headImg = headImg;
    }
}

定义util下DBUtil

package com.aaa.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @ClassName: DBUtil
 * @Description: TODO
 * @date: 2023/6/12 9:00
 * @author: zzj
 * @Version: 1.0
 */
public class DBUtil {

    private static String driverName="";
    private static String url="";
    private static String username="";
    private static String password="";

    static {
        try {
            /**
             * ClassLoader.getSystemResourceAsStream
             * 该方法通过读取,从而从 加载类搜索路径中指定名称的资源
             * 简单来说 读取db.properties 文件,并加载出来
             */

            InputStream inputStream = ClassLoader.getSystemResourceAsStream("db.properties");
            /**
             * 创建 Properties 读取java配置文件
             */
            Properties properties = new Properties();
            // properties.load 读取文件的属性
            properties.load(inputStream);
            // properties.getProperty 方法将搜索此属性列表中指定键的属性值.
            driverName = properties.getProperty("jdbc.driverName");
            url = properties.getProperty("jdbc.url");
            username = properties.getProperty("jdbc.username");
            password = properties.getProperty("jdbc.password");
        } catch(Exception e){
            System.out.println("文件不存在");
            e.printStackTrace();
        }
    }


    /**
     * Connection主要用于执行对象的获取从而进一步执行SQL，这是应用程序与数据库交互的主要途径
     * @return Connection
     * @throws Exception
     * 此方法是 获取连接对象
     */
    public static Connection getConnection() throws Exception{
        // Class.forName 方法的作用，就是初始化给定的类。
        //而我们给定的 MySQL 的 Driver 类中，它在静态代码块中通过 JDBC 的 DriverManager 注册了一下驱动。
        Class.forName(driverName);
        // DriverManager.getConnection() 该方法用于获取建立到指定数据库url的连接 从已注册的JDBC驱动程序集中选择一个适合的驱动程序
        Connection connection = DriverManager.getConnection(url,username,password);
        return connection;
    }

    // 关闭资源

    /**
     *
     * @param connection
     * Connection 主要用于执行对象的获取 从而进一步执行sql语句 这是应用程序与数据库交互的主要途径
     * @param ps
     * PreparedStatement 主要用于预编译处理,对于批量处理可以大大提高效率 也叫jdbc存储过程
     * @param rs
     * ResultSet 是数据库中查询的结果返回的一种对象 是存储查询结果的对象
     * 结果集不仅仅具有存储的功能,还能完成对数据的更新
     */
    public static void closeAll(Connection connection, PreparedStatement ps, ResultSet rs){
        try{
            if (rs != null){
                rs.close();
            }

            if (ps != null){
                ps.close();
            }

            if (connection !=null){
                connection.close();
            }
        } catch (Exception e){
            e.printStackTrace();
        }
    }
}

定义dao层 BaseDao

package com.aaa.dao;

import com.aaa.annotation.TableField;
import com.aaa.annotation.TableId;
import com.aaa.annotation.TableName;
import com.aaa.util.DbUtil;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: qy165-orm
 * @description:
 * @author: zzj
 * @create: 2023-06-12 09:22
 **/
public class BaseDao<T> {

    Class<T> clazz;
    public BaseDao(){
        //获取BaseDao子类的反射类对象
        Class<? extends BaseDao> aClass = this.getClass();
        //获取当前Dao子类的父类的反射类
        ParameterizedType parameterizedType= (ParameterizedType) aClass.getGenericSuperclass();
        //得到泛型的反射类
        Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
        clazz= (Class<T>) actualTypeArguments[0];
    }

    //根据主键查询一条记录
    //sql： select * from 表名 where 主键=值；
    public T selectById(Object id) throws Exception{
        StringBuffer sql=new StringBuffer("select * from ");
        TableName annotation = clazz.getAnnotation(TableName.class);
        String tableName=clazz.getSimpleName();
        if(annotation!=null){
             tableName=annotation.value();
        }
        sql.append(tableName+" where ");
        Field[] fields = clazz.getDeclaredFields();
        for(Field field:fields){
            TableId tableId = field.getAnnotation(TableId.class);
            if(tableId!=null){
                sql.append(tableId.value()+"='"+id+"'");
                continue;
            }
        }
        //执行sql语句
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql.toString());
        ResultSet rs = ps.executeQuery();
        T t =null;
        while (rs.next()){
            t= clazz.newInstance();
            Field[] declaredFields = clazz.getDeclaredFields();
            for(Field field:declaredFields){
                field.setAccessible(true);
                String name = field.getName();
                TableField tableField = field.getAnnotation(TableField.class);
                TableId tableId = field.getAnnotation(TableId.class);
                if(tableField!=null){
                    name=tableField.value();
                }
                if(tableId!=null){
                    name=tableId.value();
                }
                Object v = rs.getObject(name);
                field.set(t,v);
            }
        }
        return t;

    }

    //通用的删除
    // sql: delete from 表名 where 主键=值
    public int delete(Object id) throws  Exception{
        StringBuffer sql=new StringBuffer("delete from ");
        TableName annotation = clazz.getAnnotation(TableName.class);
        String tableName=clazz.getSimpleName();
        if(annotation!=null){
            tableName=annotation.value();
        }
        sql.append(tableName+" where ");
        Field[] fields = clazz.getDeclaredFields();
        for(Field field:fields){
            TableId tableId = field.getAnnotation(TableId.class);
            if(tableId!=null){
                 sql.append(tableId.value()+"='"+id+"'");
                 continue;
            }
        }

        //执行sql
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql.toString());
        int i = ps.executeUpdate();
        return i;

    }

    //通用的添加操作.
    //通用的添加sql语句: insert into 表名(列名,列名....) values(值,值.....)
    public int insert(T t) throws Exception{
        StringBuffer sql=new StringBuffer("insert into ");
        //根据对象获取Class反射类。
        Class<?> aClass = t.getClass();
        //获取反射类上的注解对象
        TableName annotation = aClass.getAnnotation(TableName.class);
        //表名
        String tableName=aClass.getSimpleName();
        if(annotation!=null){
             tableName=annotation.value();
        }
        sql.append(tableName);
        //获取列名
        Field[] declaredFields = aClass.getDeclaredFields();
        //列名
        List<String> columns=new ArrayList<String>();
        List<String> values=new ArrayList<String>();
        for(Field field:declaredFields){
            String name = field.getName();//属性名
            TableId annotation2 = field.getAnnotation(TableId.class);
            if(annotation2!=null||name.equals("id")){
                continue;
            }
            TableField annotation1 = field.getAnnotation(TableField.class);
            if(annotation1!=null){
                name=annotation1.value();
            }
            field.setAccessible(true);
            Object o = field.get(t);
            columns.add(name);
            values.add("'"+o+"'");//id name loc values(‘1’,'研发','背景')
        }
       String columnNames= columns.toString().replace("[","(").replace("]",")");
       String columnValues= values.toString().replace("[","(").replace("]",")");
       sql.append(columnNames);
       sql.append(" values ");
       sql.append(columnValues);
       //执行sql
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql.toString());
        int i = ps.executeUpdate();
        return i;
    }


    //修改方法
    // sql： update 表名  set 列名=值,列名=值.... where 主键名=值;
    public int update(T t) throws Exception{
        StringBuffer sql=new StringBuffer("update ");
        //获取实体类的反射类
        Class<?> aClass = t.getClass();
        //获取反射类上指定的注解对象
        TableName annotation = aClass.getAnnotation(TableName.class);
        String tableName=aClass.getSimpleName();
        if(annotation!=null){
             tableName=annotation.value();
        }
        sql.append(tableName+" set ");
        //获取所有的Field。
        Field[] declaredFields = aClass.getDeclaredFields();
        String where=" where ";
        for(Field field:declaredFields){
            field.setAccessible(true);
            String name = field.getName();
            TableId tableId = field.getAnnotation(TableId.class);
            if(name.equals("id")){
                where =where +" id='"+field.get(t)+"'";
                continue;
            }
            if(tableId!=null){
                where =where + tableId.value()+" '"+field.get(t)+"'";
                continue;
            }
            TableField tableField = field.getAnnotation(TableField.class);
            if(tableField!=null){
                name=tableField.value();
            }

            String value="'"+field.get(t)+"'";
            sql.append(name+"="+value+",");
        }
        String sql2=sql.toString().substring(0,sql.length()-1)+where;

        //执行sql
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql2);
        int i = ps.executeUpdate();
        return i;

    }
}

定义 DeptDao

package com.aaa.dao;

import com.aaa.pojo.Dept;

/**
 * @ClassName: DeptDao
 * @Description: TODO
 * @date: 2023/6/12 18:34
 * @author: zzj
 * @Version: 1.0
 */
public class DeptDao extends BaseDao< Dept >{
}

定义EmpDao

package com.aaa.dao;

import com.aaa.pojo.Emp;

/**
 * @ClassName: EmpDao
 * @Description: TODO
 * @date: 2023/6/12 18:35
 * @author: zzj
 * @Version: 1.0
 */
public class EmpDao extends BaseDao< Emp >{
}

自定义注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableField {
    String value();
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableId {
    String value() default "id";
}

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableName { //再实体类标记对应的表名
    String value();
}

实现类

package com.aaa;

import com.aaa.dao.DeptDao;
import com.aaa.pojo.Dept;

/**
 * @ClassName: Test
 * @Description: TODO
 * @date: 2023/6/12 18:23
 * @author: zzj
 * @Version: 1.0
 */
public class Test {

    public static void main(String[] args) throws Exception{
        // 查询
        /*DeptDao deptDao=new DeptDao();
        System.out.println(deptDao);
        System.out.println(deptDao.selectById(2));*/
        // 修改
        /*Dept dept = new Dept();
        DeptDao deptDao=new DeptDao();
        dept.setName("财务部");
        dept.setLoc("成都");
        dept.setId(3);

        deptDao.update(dept);*/

        // 添加
        /*Dept dept = new Dept();
        DeptDao deptDao=new DeptDao();
        dept.setId(7);
        dept.setName("商务部");
        dept.setLoc("青岛");
        deptDao.insert(dept);*/

        // 删除
        DeptDao deptDao = new DeptDao();
        deptDao.delete(15);
    }

}