java 執行緒池、多執行緒併發實戰(生產者消費者模型 1 vs 10) 附案例原始碼

導讀

　　前二天寫了一篇《Java 多執行緒併發程式設計》點我直達，放國慶，在家閒著沒事，繼續寫剩下的東西，開幹！

執行緒池

為什麼要使用執行緒池

　　例如web伺服器、資料庫伺服器、檔案伺服器或郵件伺服器之類的。請求的時候，單個任務時間很短，但是請求數量巨大。每一次請求，就會建立一個新執行緒，然後在新執行緒中請求服務，頻繁的建立執行緒，銷燬執行緒造成系統很大的開銷，資源的浪費。

　　執行緒池為執行緒生命週期開銷問題和資源不足問題提供瞭解決方案。通過對多個任務重用執行緒，執行緒車建立的開銷分攤到多個任務上。

建立與使用

Future

　　對具體的Runnable或者Callable任務的執行結果進行取消、查詢是否完成、獲取結果、設定結果。get方法會阻塞，直到任務返回結果。

Callable&FutureTask

　　Callable與Runnable功能相似，Callable有返回值；Runnable沒有返回值；一般情況下，Callable與FutureTask一起使用，或者與執行緒池一起使用

執行緒池核心組成部分

• corePoolSize：核心執行緒池大小
• maximumPoolSize：執行緒池最大容量
• KeepAliveTime：當執行緒數量大於核心時，多餘的空閒執行緒在終止之前等待新任務的最大時間
• unit：時間單位
• workQueue：工作佇列
• ThreadFactory：執行緒工廠
• handler：拒絕策略

Executor框架

實戰

需求分析

業務場景

　　一般系統，多數會與第三方系統的資料進行打交道，而第三方的生產庫，並不允許我們直接操作。在企業裡面，一般都是通過中間表進行同步，即第三方系統將生產資料放入一張與其生產環境隔離的另一個獨立資料庫中的獨立表，在根據介面協議，增加相應的欄位。而我方需要讀取該中間表中的資料，並對資料進行同步操作。此時就需要編寫相應的程式進行資料同步。

同步方式

1. 全量同步：每天定時將當天的生產資料全部同步過來(優點：實現檢點；缺點：資料同步不及時)
2. 增量同步：每新增一條，便將該資料同步過來(優點：資料接近實時同步；缺點：實現相對困難)

我方需要做的事情

　　讀取中間表的資料，並同步到業務系統中

模型抽離(生產者消費者模型)

1. 生產者：讀取中間表的資料
2. 消費者：消費生產者生產的資料

介面協議的制定

1. 取我方業務上需要用到的欄位
2. 需要有欄位記錄資料什麼時候進入中間表
3. 增加相應的資料標誌位，用於標誌資料的同步狀態
4. 記錄資料的同步時間

技術選型

1. mybatis
2. 單一生產者多消費者
3. 多執行緒併發操作

中間表設計

專案搭建

專案結構

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

    <groupId>com.cyb</groupId>
    <artifactId>ybchen_syn</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <!-- 新增MyBatis框架 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version> <!-- 版本號視情況修改 -->
        </dependency>
        <!-- 新增MySql驅動包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
        <!--連線池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.1</version>
        </dependency>
        <!--日誌-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.30</version>
        </dependency>
        <!--單元測試-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

log4j.properties

### 設定###
log4j.rootLogger = debug,stdout,D,E

### 輸出資訊到控制抬 ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

### 輸出DEBUG 級別以上的日誌到=E://logs/error.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = ./logs/debug.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG 
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### 輸出ERROR 級別以上的日誌到=E://logs/error.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =./logs/error.log 
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR 
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

middle-student.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="middle-student">
    <resultMap id="BaseResultMap" type="com.cyb.entity.middle.Student">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="sex" property="sex" jdbcType="VARCHAR"/>
        <result column="birth" property="birth" jdbcType="TIMESTAMP"/>
        <result column="department" property="department" jdbcType="VARCHAR"/>
        <result column="add_time" property="addTime" jdbcType="TIMESTAMP"/>
        <result column="data_status" property="dataStatus" jdbcType="VARCHAR"/>
        <result column="deal_time" property="dealTime" jdbcType="TIMESTAMP"/>
    </resultMap>
    <select id="selectList" resultMap="BaseResultMap">
        SELECT
        *
        FROM student
        WHERE data_status = 'I'  limit #{count}
    </select>
    <update id="updateStatusById">
        update  student
        set data_status = #{dataStatus}, deal_time = #{dealTime}
        where id =#{id}
    </update>
</mapper>

test-student.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test-student">
    <insert id="addStudent">
        insert into student (name,sex,department) values (#{name},#{sex},#{department})
    </insert>
</mapper>

mybatis-config-middle.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!-- MyBatis的全域性配置檔案 -->
<configuration >
    <!-- 1.配置開發環境 -->
    <environments default="develop">
        <!-- 這裡可以配置多個環境，比如develop，test等 -->
        <environment id="develop">
            <!-- 1.1.配置事務管理方式：JDBC：將事務交給JDBC管理（推薦） -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 1.2.配置資料來源，即連線池方式:JNDI/POOLED/UNPOOLED -->
            <dataSource type="com.cyb.datasource.DruidDataSourceFactory">
                <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
                <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/middle?characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;autoReconnect=true"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
                <property name="initialSize" value="2"/>
                <property name="maxActive" value="300"/>
                <property name="maxWait" value="60000"/>
                <property name="poolPreparedStatements" value="true"/>
                <property name="maxPoolPreparedStatementPerConnectionSize" value="200"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 2.載入Mapper配置檔案,路徑以斜槓間隔: xx/xx/../xx.xml -->
    <mappers>
        <mapper resource="middle-student.xml"/>
    </mappers>
</configuration>

mybatis-config-test.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!-- MyBatis的全域性配置檔案 -->
<configuration >
    <!-- 1.配置開發環境 -->
    <environments default="develop">
        <!-- 這裡可以配置多個環境，比如develop，test等 -->
        <environment id="develop">
            <!-- 1.1.配置事務管理方式：JDBC：將事務交給JDBC管理（推薦） -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 1.2.配置資料來源，即連線池方式:JNDI/POOLED/UNPOOLED -->
            <dataSource type="com.cyb.datasource.DruidDataSourceFactory">
                <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
                <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;autoReconnect=true"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
                <property name="initialSize" value="2"/>
                <property name="maxActive" value="300"/>
                <property name="maxWait" value="60000"/>
                <property name="poolPreparedStatements" value="true"/>
                <property name="maxPoolPreparedStatementPerConnectionSize" value="200"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 2.載入Mapper配置檔案,路徑以斜槓間隔: xx/xx/../xx.xml -->
    <mappers>
        <mapper resource="test-student.xml"/>
    </mappers>
</configuration>

StudentConst.java

package com.cyb.cost;

public class StudentConst {
    //I:第三方系統入庫；D:處理中；F:處理完成；E:發生錯誤或異常
    public static final String INIT="I";
    public static final String DEALING="D";
    public static final String FINISH="F";
    public static final String ERROR="E";
}

DruidDataSourceFactory.java

package com.cyb.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;

/**
 * @ClassName：DruidDataSourceFactory
 * @Description：Druid連線池工廠類
 * @Author：chenyb
 * @Date：2020/10/7 7:30 下午
 * @Versiion：1.0
 */
public class DruidDataSourceFactory extends UnpooledDataSourceFactory {
    public DruidDataSourceFactory() {
        this.dataSource = new DruidDataSource();
    }
}

Student.java(middle包下)

package com.cyb.entity.middle;

import java.io.Serializable;
import java.util.Date;

public class Student implements Serializable {
    private Integer id;
    private String name;
    private String sex;
    private String address;
    private String department;
    private Date addTime;
    private String dataStatus;
    private Date dealTime;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public Date getAddTime() {
        return addTime;
    }

    public void setAddTime(Date addTime) {
        this.addTime = addTime;
    }

    public String getDataStatus() {
        return dataStatus;
    }

    public void setDataStatus(String dataStatus) {
        this.dataStatus = dataStatus;
    }

    public Date getDealTime() {
        return dealTime;
    }

    public void setDealTime(Date dealTime) {
        this.dealTime = dealTime;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", department='" + department + '\'' +
                ", addTime=" + addTime +
                ", dataStatus='" + dataStatus + '\'' +
                ", dealTime=" + dealTime +
                '}';
    }
}

Student.java(test包下)

package com.cyb.entity.test;

import java.io.Serializable;

public class Student implements Serializable {
    private Integer id;
    private String name;
    private String sex;
    private String department;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    @Override
    public String toString() {
        return "student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", department='" + department + '\'' +
                '}';
    }
}

MiddleProcess.java

package com.cyb.process;

import com.cyb.entity.middle.Student;

import java.util.List;

public interface MiddleProcess {
    /**
     * 查詢資料
     * @param count 一次查詢的數量
     * @return
     */
    List<Student> queryList(int count);

    /**
     * 修改資料狀態
     * @param data 待修改資料
     * @param status 要修改成的狀態
     * @return
     */
    int modifyListStatus(List<Student> data, String status);
}

TestProcess.java

package com.cyb.process;



import com.cyb.entity.middle.Student;

import java.util.List;

public interface TestProcess {
    /**
     * 處理資料
     * @param data
     */
    void hand(List<Student> data);
}

MiddleProcessImpl.java

package com.cyb.process.impl;

import com.cyb.entity.middle.Student;
import com.cyb.process.MiddleProcess;
import com.cyb.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Date;
import java.util.List;

public class MiddleProcessImpl implements MiddleProcess {
private static final Logger LOGGER= LoggerFactory.getLogger(MiddleProcess.class);
    @Override
    public List<Student> queryList(int count) {
        SqlSession middleSqlSession = SqlSessionUtil.getSqlSession("middle");
        List<Student> objects =null;
        try {
            objects = middleSqlSession.selectList("middle-student.selectList", count);
        }catch (Exception e){
            LOGGER.error("查詢發生異常=======》",e);
        }
        finally {
            //關閉連線
            middleSqlSession.close();
        }
        return objects;
    }

    @Override
    public int modifyListStatus(List<Student> data, String status) {
        data.forEach(stu->{
            stu.setDataStatus(status);
            SqlSession middleSqlSession = SqlSessionUtil.getSqlSession("middle");
            try {
                middleSqlSession.update("middle-student.updateStatusById",stu);
                middleSqlSession.commit();
            }catch (Exception e){
                //回滾當前提交
                middleSqlSession.rollback();
                LOGGER.error("修改狀態失敗=======》",e);
            }finally {
                middleSqlSession.close();
            }
        });
        return 0;
    }
}

TestProcessImpl.java

package com.cyb.process.impl;

import com.cyb.cost.StudentConst;
import com.cyb.entity.middle.Student;
import com.cyb.process.TestProcess;
import com.cyb.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class TestProcessImpl implements TestProcess {
    private Logger LOGGER = LoggerFactory.getLogger(TestProcess.class);

    @Override
    public void hand(List<Student> data) {
        //將data轉換成業務庫的實體
        List<com.cyb.entity.test.Student> students = adapter(data);
        //處理資料，併入庫
        students.forEach(stu -> {
            stu.setName(stu.getName() + "_test");
            SqlSession testSqlSession = SqlSessionUtil.getSqlSession("test");
            try {
                testSqlSession.insert("test-student.addStudent", stu);
                testSqlSession.commit();
                //修改中間表狀態
                modifyMiddle(stu.getId(), StudentConst.FINISH);
            } catch (Exception e) {
                //回滾操作
                testSqlSession.rollback();
                LOGGER.error("處理資料發生異常============》",e);
            } finally {
                testSqlSession.close();
            }
        });
    }

    /**
     * 資料介面卡
     *
     * @param data
     * @return
     */
    public List<com.cyb.entity.test.Student> adapter(List<Student> data) {
        List<com.cyb.entity.test.Student> result = new ArrayList<>();
        data.forEach(stu -> {
            com.cyb.entity.test.Student student = new com.cyb.entity.test.Student();
            student.setId(stu.getId());
            student.setName(stu.getName());
            student.setDepartment(stu.getDepartment());
            student.setSex(stu.getSex());
            result.add(student);
        });
        return result;
    }

    /**
     * 修改中間表狀態
     * @param id
     * @param status
     */
    private void modifyMiddle(int id, String status) {
        Student student = new Student();
        student.setId(id);
        student.setDataStatus(status);
        student.setDealTime(new Date());
        SqlSession middleSqlSession = SqlSessionUtil.getSqlSession("middle");
        try {
            middleSqlSession.update("middle-student.updateStatusById", student);
            middleSqlSession.commit();
        } catch (Exception e) {
            middleSqlSession.rollback();
            LOGGER.error("修改中間表狀態失敗===========》",e);
        } finally {
            middleSqlSession.close();
        }
    }
}

Consumer.java

package com.cyb.start;

import com.cyb.entity.middle.Student;
import com.cyb.process.TestProcess;

import java.util.List;
import java.util.concurrent.LinkedBlockingDeque;


/**
 * @ClassName：Consumer
 * @Description：消費者
 * @Author：chenyb
 * @Date：2020/10/7 9:23 下午
 * @Versiion：1.0
 */
public class Consumer implements Runnable{
    private List<Student> data;
    private TestProcess testProcess;
    private LinkedBlockingDeque<Runnable> consumer;

    public Consumer(TestProcess testProcess, LinkedBlockingDeque<Runnable> consumer) {
        this.testProcess = testProcess;
        this.consumer = consumer;
    }

    @Override
    public void run() {
        try {
            testProcess.hand(data);
        }finally {
            try {
                //新增元素,佇列滿，進入阻塞狀態
                consumer.put(this);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
    public void setData(List<Student> data){
        this.data=data;
    }
}

Producer.java

package com.cyb.start;

import com.cyb.cost.StudentConst;
import com.cyb.entity.middle.Student;
import com.cyb.process.MiddleProcess;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Collections;
import java.util.List;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;

/**
 * @ClassName：Producer
 * @Description：提供者
 * @Author：chenyb
 * @Date：2020/10/7 9:22 下午
 * @Versiion：1.0
 */
public class Producer implements Runnable {
    private static final Logger LOGGER = LoggerFactory.getLogger(Producer.class);
    private MiddleProcess middleProcess;
    private LinkedBlockingDeque<Runnable> consumer;
    private ThreadPoolExecutor executor;

    public Producer(MiddleProcess middleProcess, LinkedBlockingDeque<Runnable> consumer, ThreadPoolExecutor executor) {
        this.middleProcess = middleProcess;
        this.consumer = consumer;
        this.executor = executor;
    }

    @Override
    public void run() {
        while (true) {
            //每次生產10條資料
            List<Student> students = middleProcess.queryList(10);
            try {
                if (students != null && students.size() > 0) {
                    //將資料修改為處理中
                    middleProcess.modifyListStatus(students, StudentConst.DEALING);
                    Consumer con = (Consumer) consumer.take();
                    con.setData(students);
                    executor.execute(con);
                } else {
                    //如果沒有資料，睡眠5秒
                    try {
                        Thread.sleep(5000L);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){
             LOGGER.error("生產者發生異常========>",e);
            }
        }
    }
}

Main.java

package com.cyb.start;

import com.cyb.process.MiddleProcess;
import com.cyb.process.TestProcess;
import com.cyb.process.impl.MiddleProcessImpl;
import com.cyb.process.impl.TestProcessImpl;

import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

/**
 * 生產著消費者：1 VS 10
 */
public class Main {
    public static void main(String[] args) {
        TestProcess testProcess=new TestProcessImpl();
        MiddleProcess middleProcess=new MiddleProcessImpl();
        LinkedBlockingDeque<Runnable> runnables=new LinkedBlockingDeque<>(10);
        ThreadPoolExecutor threadPoolExecutor=new ThreadPoolExecutor(10,20,5L, TimeUnit.SECONDS, new LinkedBlockingQueue<>(20));
        //10個消費者
        for (int i = 0; i < 10; i++) {
            try {
                runnables.put(new Consumer(testProcess,runnables));
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        //開啟一個執行緒-》生產者
        Producer producer=new Producer(middleProcess,runnables,threadPoolExecutor);
        new Thread(producer).start();
    }
}

SqlSessionUtil.java

package com.cyb.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.io.Reader;

/**
 * @ClassName：SqlSessionUtil
 * @Description：SqlSession工具類
 * @Author：chenyb
 * @Date：2020/10/7 7:37 下午
 * @Versiion：1.0
 */
public class SqlSessionUtil {
    private static final String MYBATIS_CONFIG_MIDDLE = "mybatis-config-middle.xml";
    private static final String MYBATIS_CONFIG_TEST = "mybatis-config-test.xml";
    private static SqlSessionFactory middleSqlSessionFactory;
    private static SqlSessionFactory testSqlSessionFactory;
    private static Reader middleResourceAsReader =null;
    private static Reader testResourceAsReader =null;
    static {
        try {
            middleResourceAsReader = Resources.getResourceAsReader(MYBATIS_CONFIG_MIDDLE);
            testResourceAsReader = Resources.getResourceAsReader(MYBATIS_CONFIG_TEST);
            middleSqlSessionFactory=new SqlSessionFactoryBuilder().build(middleResourceAsReader);
            testSqlSessionFactory=new SqlSessionFactoryBuilder().build(testResourceAsReader);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                middleResourceAsReader.close();
                testResourceAsReader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public static SqlSession getSqlSession(String type){
        if ("test".equals(type)){
            return testSqlSessionFactory.openSession();
        }
        return middleSqlSessionFactory.openSession();
    }
}

sql指令碼

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50728
 Source Host           : localhost:3306
 Source Schema         : middle

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

 Date: 07/10/2020 22:42:55
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `sex` varchar(255) DEFAULT NULL COMMENT '性別',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `department` varchar(255) DEFAULT NULL COMMENT '系',
  `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '資料進入中間表時間',
  `data_status` varchar(10) NOT NULL DEFAULT 'I' COMMENT 'I:第三方系統入庫；D:處理中；F:處理完成；E:發生錯誤或異常',
  `deal_time` datetime DEFAULT NULL COMMENT '處理時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, '張三', '男', '上海', '英語系', '2020-10-07 22:19:25', 'F', '2020-10-07 22:19:26');
INSERT INTO `student` VALUES (2, '李四', '女', '北京', '中文系', '2020-10-07 22:19:25', 'F', '2020-10-07 22:19:26');
INSERT INTO `student` VALUES (3, '王五', '男', '天津', '計算機系', '2020-10-07 22:19:25', 'F', '2020-10-07 22:19:26');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50728
 Source Host           : localhost:3306
 Source Schema         : test

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

 Date: 07/10/2020 22:45:03
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `sex` varchar(255) DEFAULT NULL COMMENT '性別',
  `department` varchar(255) DEFAULT NULL COMMENT '系',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

演示

專案原始碼下載

連結: https://pan.baidu.com/s/1C7q7_QRUhRoCZIVZ_Bp3KQ  密碼: 7hbf