java通用后台权限管理(springboot)高级版

1、项目结构

2、部分代码解析

用户模块controller

package com.xiaofeng.shiro.controller;
 
 
import cn.afterturn.easypoi.entity.vo.NormalExcelConstants;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.view.PoiBaseView;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.google.common.collect.Maps;
import com.xiaofeng.shiro.aspectj.annotation.Log;
import com.xiaofeng.shiro.common.response.AjaxResult;
import com.xiaofeng.shiro.common.response.TableDataInfo;
import com.xiaofeng.shiro.constant.UserConstants;
import com.xiaofeng.shiro.entity.SysRole;
import com.xiaofeng.shiro.entity.SysUser;
import com.xiaofeng.shiro.enums.LogType;
import com.xiaofeng.shiro.service.ISysRoleService;
import com.xiaofeng.shiro.service.ISysUserService;
import com.xiaofeng.shiro.service.impl.SysPasswordService;
import com.xiaofeng.shiro.util.DateUtil;
import com.xiaofeng.shiro.util.ShiroUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
 
/** * <p> * 用户信息表 前端控制器 * </p> * * @author xiaofeng * @since 2019-07-03 */
@Controller
@RequestMapping("/system/user")
public class SysUserController extends BaseController {
    private String prefix = "system/user";
 
    @Autowired
    private ISysUserService userService;
    @Autowired
    private ISysRoleService roleService;
    @Autowired
    private SysPasswordService passwordService;
 
    @RequiresPermissions("system:user:view")
    @GetMapping()
    public String user() {
        return prefix + "/user";
    }
 
    @Log(title = "用户查询", logType = LogType.SELECT)
    @RequiresPermissions("system:user:list")
    @PostMapping("/list")
    @ResponseBody
    public TableDataInfo list(SysUser user) {
        IPage<SysUser> page = getPage();
        userService.pageList(page, user);
        return getDataTable(page.getRecords(), page.getSize());
    }
 
    @Log(title = "用户管理", logType = LogType.EXPORT)
    @RequiresPermissions("system:user:export")
    @GetMapping("/export")
    public void export(SysUser user, HttpServletRequest request, HttpServletResponse response) {
        List<SysUser> list = userService.list(user);
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        String title = "用户信息";
        Map<String, Object> map = Maps.newHashMap();
        ExportParams params = new ExportParams(title, title, ExcelType.HSSF);
        map.put(NormalExcelConstants.DATA_LIST, list);
        map.put(NormalExcelConstants.CLASS, SysUser.class);
        map.put(NormalExcelConstants.PARAMS, params);
        map.put("fileName", DateUtil.getNowDateTime());
        PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }
 
    /** * 新增用户 */
    @GetMapping("/add")
    public String add(ModelMap mmap) {
        List<SysRole> roleList = roleService.list(new LambdaQueryWrapper<SysRole>().eq(SysRole::getDelFlag, 0));
        mmap.put("roles", roleList);
        return prefix + "/add";
    }
 
    /** * 新增保存用户 */
    @RequiresPermissions("system:user:add")
    @Log(title = "用户管理", logType = LogType.INSERT)
    @PostMapping("/add")
    @ResponseBody
    public AjaxResult addSave(SysUser user) {
        if (user.getId() != null && SysUser.isAdmin(ShiroUtils.getSysUser().getUserType())) {
            return error("不允许修改超级管理员用户");
        }
        String nameUnique = userService.checkLoginNameUnique(user.getUserName());
        if (UserConstants.USER_NAME_NOT_UNIQUE.equals(nameUnique)) {
            return error("保存用户'" + user.getUserName() + "'失败，登录账号已存在");
        }
        user.setSalt(ShiroUtils.randomSalt());
        user.setPassword(passwordService.encryptPassword(user.getUserName(), user.getPassword(), user.getSalt()));
        user.setCreateBy(ShiroUtils.getLoginName());
        user.setCreateTime(DateUtil.getNowTimestamp());
        return toAjax(userService.insertUser(user));
    }
 
    /** * 修改用户 */
    @GetMapping("/edit/{userId}")
    public String edit(@PathVariable("userId") Long userId, ModelMap mmap) {
        mmap.put("user", userService.getById(userId));
        mmap.put("roles", roleService.selectRolesByUserId(userId));
        return prefix + "/edit";
    }
 
    /** * 修改保存用户 */
    @RequiresPermissions("system:user:edit")
    @Log(title = "用户管理", logType = LogType.UPDATE)
    @PostMapping("/edit")
    @ResponseBody
    public AjaxResult editSave(SysUser user) {
        if (user.getId() != null && SysUser.isAdmin(ShiroUtils.getSysUser().getUserType())) {
            return error("不允许修改超级管理员用户");
        }
        user.setUpdateBy(ShiroUtils.getLoginName());
        return toAjax(userService.updateUser(user));
    }
 
 
    @RequiresPermissions("system:user:resetPwd")
    @Log(title = "重置密码", logType = LogType.UPDATE)
    @GetMapping("/resetPwd/{userId}")
    public String resetPwd(@PathVariable("userId") Long userId, ModelMap mmap) {
        mmap.put("user", userService.getById(userId));
        return prefix + "/resetPwd";
    }
 
    @RequiresPermissions("system:user:resetPwd")
    @Log(title = "重置密码", logType = LogType.UPDATE)
    @PostMapping("/resetPwd")
    @ResponseBody
    public AjaxResult resetPwdSave(SysUser user) {
        user.setSalt(ShiroUtils.randomSalt());
        user.setPassword(passwordService.encryptPassword(user.getUserName(), user.getPassword(), user.getSalt()));
        return toAjax(userService.updateById(user));
    }
 
    @RequiresPermissions("system:user:remove")
    @Log(title = "用户管理", logType = LogType.DELETE)
    @PostMapping("/remove")
    @ResponseBody
    public AjaxResult remove(String ids) {
        try {
            if (StringUtils.isNoneBlank(ids)) {
                String[] split = ids.split(",");
                return toAjax(userService.batchDeleteUser(Arrays.asList(split)));
            }
        } catch (Exception e) {
            return error(e.getMessage());
        }
        return error();
    }
 
 
    /** * 校验用户名 */
    @PostMapping("/checkLoginNameUnique")
    @ResponseBody
    public String checkLoginNameUnique(SysUser user) {
        String nameUnique = userService.checkLoginNameUnique(user.getUserName());
        return nameUnique;
    }
 
    /** * 校验手机号码 */
    @PostMapping("/checkPhoneUnique")
    @ResponseBody
    public String checkPhoneUnique(SysUser user) {
        String phoneUnique = userService.checkPhoneUnique(user);
        return phoneUnique;
    }
 
    /** * 校验email邮箱 */
    @PostMapping("/checkEmailUnique")
    @ResponseBody
    public String checkEmailUnique(SysUser user) {
        return userService.checkEmailUnique(user);
    }
 
    /** * 用户状态修改 */
    @Log(title = "用户管理", logType = LogType.UPDATE)
    @RequiresPermissions("system:user:edit")
    @PostMapping("/changeStatus")
    @ResponseBody
    public AjaxResult changeStatus(SysUser user) {
        if (SysUser.isAdmin(ShiroUtils.getSysUser().getUserType())) {
            return error("不允许修改超级管理员用户");
        }
        return toAjax(userService.updateUser(user));
    }
}

用户模块service实现：

package com.xiaofeng.shiro.service.impl;
 
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xiaofeng.shiro.constant.Constants;
import com.xiaofeng.shiro.constant.UserConstants;
import com.xiaofeng.shiro.entity.SysRole;
import com.xiaofeng.shiro.entity.SysUser;
import com.xiaofeng.shiro.entity.SysUserRole;
import com.xiaofeng.shiro.mapper.SysRoleMapper;
import com.xiaofeng.shiro.mapper.SysUserMapper;
import com.xiaofeng.shiro.mapper.SysUserRoleMapper;
import com.xiaofeng.shiro.service.ISysUserRoleService;
import com.xiaofeng.shiro.service.ISysUserService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
 
/** * <p> * 用户信息表 服务实现类 * </p> * * @author xiaofeng * @since 2019-07-03 */
@Service
@Transactional(rollbackFor = Exception.class)
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements ISysUserService {
    @Autowired
    SysUserMapper sysUserMapper;
    @Autowired
    SysUserRoleMapper sysUserRoleMapper;
    @Autowired
    SysRoleMapper sysRoleMapper;
    @Autowired
    ISysUserRoleService userRoleService;
 
    @Override
    public IPage<SysUser> pageList(IPage<SysUser> iPage, SysUser user) {
        LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<SysUser>();
        queryWrapper
                .like(StringUtils.isNoneBlank(user.getUserName()), SysUser::getUserName, user.getUserName())
                .like(StringUtils.isNoneBlank(user.getTelephone()), SysUser::getTelephone, user.getTelephone())
                .ge(user.getParams().get(Constants.START_TIME) != null && StringUtils.isNoneBlank(user.getParams().get(Constants.START_TIME).toString()), SysUser::getCreateTime, user.getParams().get(Constants.START_TIME))
                .le(user.getParams().get(Constants.END_TIME) != null && StringUtils.isNoneBlank(user.getParams().get(Constants.END_TIME).toString()), SysUser::getCreateTime, user.getParams().get(Constants.END_TIME));
        page(iPage, queryWrapper);
        return iPage;
    }
 
    /** * 不分页查询 * * @param user * @return */
    @Override
    public List<SysUser> list(SysUser user) {
        LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<SysUser>();
        queryWrapper
                .like(StringUtils.isNoneBlank(user.getUserName()), SysUser::getUserName, user.getUserName())
                .like(StringUtils.isNoneBlank(user.getTelephone()), SysUser::getTelephone, user.getTelephone())
                .like(StringUtils.isNoneBlank(user.getSearchValue()), SysUser::getUserName, user.getSearchValue())
                .like(StringUtils.isNoneBlank(user.getSearchValue()), SysUser::getTelephone, user.getSearchValue())
                .ge(user.getParams().get(Constants.START_TIME) != null && StringUtils.isNoneBlank(user.getParams().get(Constants.START_TIME).toString()), SysUser::getCreateTime, user.getParams().get(Constants.START_TIME))
                .le(user.getParams().get(Constants.END_TIME) != null && StringUtils.isNoneBlank(user.getParams().get(Constants.END_TIME).toString()), SysUser::getCreateTime, user.getParams().get(Constants.END_TIME));
        List<SysUser> list = list(queryWrapper);
        return list;
    }
 
    /** * 校验用户名称是否唯一 * * @param loginName 登录名称 * @return 结果 */
    @Override
    public String checkLoginNameUnique(String loginName) {
        int count = count(new LambdaQueryWrapper<SysUser>().eq(SysUser::getUserName, loginName));
        return count > 0 ? UserConstants.USER_NAME_NOT_UNIQUE : UserConstants.USER_NAME_UNIQUE;
    }
 
    /** * 校验手机号码是否唯一 * * @param user 用户信息 * @return 结果 */
    @Override
    public String checkPhoneUnique(SysUser user) {
        Long userId = user.getId() == null ? -1L : user.getId();
        SysUser sysUser = getOne(new LambdaQueryWrapper<SysUser>().eq(SysUser::getTelephone, user.getTelephone()));
        String s = sysUser != null && !userId.equals(sysUser.getId()) ? UserConstants.USER_NAME_NOT_UNIQUE : UserConstants.USER_NAME_UNIQUE;
        return s;
    }
 
    /** * 校验email是否唯一 * * @param user 用户信息 * @return 结果 */
    @Override
    public String checkEmailUnique(SysUser user) {
        Long userId = user.getId() == null ? -1L : user.getId();
        SysUser sysUser = getOne(new LambdaQueryWrapper<SysUser>().eq(SysUser::getEmail, user.getEmail()));
        String s = sysUser != null && !userId.equals(sysUser.getId()) ? UserConstants.USER_NAME_NOT_UNIQUE : UserConstants.USER_NAME_UNIQUE;
        return s;
    }
 
    @Override
    public List<SysUser> selectAllocatedList(SysUser user) {
        return sysUserMapper.selectAllocatedList(user);
    }
 
    @Override
    public List<SysUser> selectUnallocatedList(SysUser user) {
        return sysUserMapper.selectUnallocatedList(user);
    }
 
    /** * 新增保存用户信息 * * @param user 用户信息 * @return 结果 */
    @Override
    public int insertUser(SysUser user) {
        // 新增用户信息
        int rows = sysUserMapper.insert(user);
        // 新增用户与角色管理
        insertUserRole(user);
        return rows;
    }
 
    /** * 修改保存用户信息 * * @param user 用户信息 * @return 结果 */
    @Override
    public int updateUser(SysUser user) {
        // 删除用户与角色关联
        sysUserRoleMapper.delete(new LambdaQueryWrapper<SysUserRole>().eq(SysUserRole::getUserId, user.getId()));
        // 新增用户与角色管理
        insertUserRole(user);
        boolean b = updateById(user);
        return b ? 1 : 0;
    }
 
    /** * 查询用户所属角色组 * * @param userId 用户ID * @return 结果 */
    @Override
    public String selectUserRoleGroup(Long userId) {
        List<SysRole> sysRoles = sysRoleMapper.selectRolesByUserId(userId);
        StringBuffer idsStr = new StringBuffer();
        for (SysRole role : sysRoles) {
            idsStr.append(role.getRoleName()).append(",");
        }
        if (StringUtils.isNotEmpty(idsStr.toString())) {
            return idsStr.substring(0, idsStr.length() - 1);
        }
        return idsStr.toString();
    }
 
    /** * 查询用户所属岗位组 * * @param userId 用户ID * @return 结果 */
    @Override
    public String selectUserPostGroup(Long userId) {
 
        return "";
    }
 
    /** * 删除 * * @param userId * @return */
    @Override
    public int deleteUser(Long userId) {
        //删除用户角色关联
        sysUserRoleMapper.delete(new LambdaQueryWrapper<SysUserRole>().eq(SysUserRole::getUserId, userId));
        //删除用户信息
        boolean b = removeById(userId);
        return b ? 1 : 0;
    }
 
    /** * 批量删除 * * @param userId * @return */
    @Override
    public int batchDeleteUser(Collection<? extends Serializable> userId) {
        //删除用户角色关联
        sysUserRoleMapper.delete(new LambdaQueryWrapper<SysUserRole>().in(SysUserRole::getUserId, userId));
        //删除用户
        boolean b = removeByIds(userId);
        return b ? 1 : 0;
    }
 
    /** * 新增用户角色信息 * * @param user 用户对象 */
    public void insertUserRole(SysUser user) {
        Long[] roles = user.getRoleIds();
        if (roles != null) {
            // 新增用户与角色管理
            List<SysUserRole> list = new ArrayList<SysUserRole>();
            for (Long roleId : roles) {
                SysUserRole ur = new SysUserRole();
                ur.setUserId(user.getId());
                ur.setRoleId(roleId.intValue());
                list.add(ur);
            }
            if (list.size() > 0) {
                userRoleService.saveBatch(list);
            }
        }
    }
 
}

package com.ruoyi.quartz.controller;

import java.util.List;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.quartz.SchedulerException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.common.exception.job.TaskException;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.quartz.domain.SysJob;
import com.ruoyi.quartz.service.ISysJobService;

/** * 调度任务信息操作处理 * * @author ruoyi */
@Controller
@RequestMapping("/monitor/job")
public class SysJobController extends BaseController
{
    private String prefix = "monitor/job";

    @Autowired
    private ISysJobService jobService;

    @RequiresPermissions("monitor:job:view")
    @GetMapping()
    public String job()
    {
        return prefix + "/job";
    }

    @RequiresPermissions("monitor:job:list")
    @PostMapping("/list")
    @ResponseBody
    public TableDataInfo list(SysJob job)
    {
        startPage();
        List<SysJob> list = jobService.selectJobList(job);
        return getDataTable(list);
    }

    @Log(title = "定时任务", businessType = BusinessType.EXPORT)
    @RequiresPermissions("monitor:job:export")
    @PostMapping("/export")
    @ResponseBody
    public AjaxResult export(SysJob job)
    {
        List<SysJob> list = jobService.selectJobList(job);
        ExcelUtil<SysJob> util = new ExcelUtil<SysJob>(SysJob.class);
        return util.exportExcel(list, "定时任务");
    }

    @Log(title = "定时任务", businessType = BusinessType.DELETE)
    @RequiresPermissions("monitor:job:remove")
    @PostMapping("/remove")
    @ResponseBody
    public AjaxResult remove(String ids) throws SchedulerException
    {
        jobService.deleteJobByIds(ids);
        return success();
    }

    @RequiresPermissions("monitor:job:detail")
    @GetMapping("/detail/{jobId}")
    public String detail(@PathVariable("jobId") Long jobId, ModelMap mmap)
    {
        mmap.put("name", "job");
        mmap.put("job", jobService.selectJobById(jobId));
        return prefix + "/detail";
    }

    /** * 任务调度状态修改 */
    @Log(title = "定时任务", businessType = BusinessType.UPDATE)
    @RequiresPermissions("monitor:job:changeStatus")
    @PostMapping("/changeStatus")
    @ResponseBody
    public AjaxResult changeStatus(SysJob job) throws SchedulerException
    {
        SysJob newJob = jobService.selectJobById(job.getJobId());
        newJob.setStatus(job.getStatus());
        return toAjax(jobService.changeStatus(newJob));
    }

    /** * 任务调度立即执行一次 */
    @Log(title = "定时任务", businessType = BusinessType.UPDATE)
    @RequiresPermissions("monitor:job:changeStatus")
    @PostMapping("/run")
    @ResponseBody
    public AjaxResult run(SysJob job) throws SchedulerException
    {
        jobService.run(job);
        return success();
    }

    /** * 新增调度 */
    @GetMapping("/add")
    public String add()
    {
        return prefix + "/add";
    }

    /** * 新增保存调度 */
    @Log(title = "定时任务", businessType = BusinessType.INSERT)
    @RequiresPermissions("monitor:job:add")
    @PostMapping("/add")
    @ResponseBody
    public AjaxResult addSave(@Validated SysJob job) throws SchedulerException, TaskException
    {
        return toAjax(jobService.insertJob(job));
    }

    /** * 修改调度 */
    @GetMapping("/edit/{jobId}")
    public String edit(@PathVariable("jobId") Long jobId, ModelMap mmap)
    {
        mmap.put("job", jobService.selectJobById(jobId));
        return prefix + "/edit";
    }

    /** * 修改保存调度 */
    @Log(title = "定时任务", businessType = BusinessType.UPDATE)
    @RequiresPermissions("monitor:job:edit")
    @PostMapping("/edit")
    @ResponseBody
    public AjaxResult editSave(@Validated SysJob job) throws SchedulerException, TaskException
    {
        return toAjax(jobService.updateJob(job));
    }

    /** * 校验cron表达式是否有效 */
    @PostMapping("/checkCronExpressionIsValid")
    @ResponseBody
    public boolean checkCronExpressionIsValid(SysJob job)
    {
        return jobService.checkCronExpressionIsValid(job.getCronExpression());
    }
}

package com.ruoyi.generator.mapper;

import java.util.List;
import com.ruoyi.generator.domain.GenTableColumn;

/** * 业务字段 数据层 * * @author ruoyi */
public interface GenTableColumnMapper
{
    /** * 根据表名称查询列信息 * * @param tableName 表名称 * @return 列信息 */
    public List<GenTableColumn> selectDbTableColumnsByName(String tableName);
    
    /** * 查询业务字段列表 * * @param genTableColumn 业务字段信息 * @return 业务字段集合 */
    public List<GenTableColumn> selectGenTableColumnListByTableId(GenTableColumn genTableColumn);

    /** * 新增业务字段 * * @param genTableColumn 业务字段信息 * @return 结果 */
    public int insertGenTableColumn(GenTableColumn genTableColumn);

    /** * 修改业务字段 * * @param genTableColumn 业务字段信息 * @return 结果 */
    public int updateGenTableColumn(GenTableColumn genTableColumn);

    /** * 批量删除业务字段 * * @param ids 需要删除的数据ID * @return 结果 */
    public int deleteGenTableColumnByIds(Long[] ids);
}

3、运行截图

今天通用权限管理系统springboot高级版发布啦，高级版新增的功能包括：部门管理、岗位管理、在线用户、定时任务、代码生成、系统接口、邮件管理、短信管理，表单自定义构建、统计报表、图表功能等。后续还会继续完善（接入支付功能，第三方登陆、单点登陆等）

本系统基本满足的后台管理系统所需的所有基本功能，集成本系统将极大的提升你的开发效率。

4、部分sql语句

-- ----------------------------
-- 1、存储每一个已配置的 jobDetail 的详细信息
-- ----------------------------
drop table if exists QRTZ_JOB_DETAILS;
create table QRTZ_JOB_DETAILS (
    sched_name           varchar(120)    not null,
    job_name             varchar(200)    not null,
    job_group            varchar(200)    not null,
    description          varchar(250)    null,
    job_class_name       varchar(250)    not null,
    is_durable           varchar(1)      not null,
    is_nonconcurrent     varchar(1)      not null,
    is_update_data       varchar(1)      not null,
    requests_recovery    varchar(1)      not null,
    job_data             blob            null,
    primary key (sched_name,job_name,job_group)
) engine=innodb;

-- ----------------------------
-- 2、 存储已配置的 Trigger 的信息
-- ----------------------------
drop table if exists QRTZ_TRIGGERS;
create table QRTZ_TRIGGERS (
    sched_name           varchar(120)    not null,
    trigger_name         varchar(200)    not null,
    trigger_group        varchar(200)    not null,
    job_name             varchar(200)    not null,
    job_group            varchar(200)    not null,
    description          varchar(250)    null,
    next_fire_time       bigint(13)      null,
    prev_fire_time       bigint(13)      null,
    priority             integer         null,
    trigger_state        varchar(16)     not null,
    trigger_type         varchar(8)      not null,
    start_time           bigint(13)      not null,
    end_time             bigint(13)      null,
    calendar_name        varchar(200)    null,
    misfire_instr        smallint(2)     null,
    job_data             blob            null,
    primary key (sched_name,trigger_name,trigger_group),
    foreign key (sched_name,job_name,job_group) references QRTZ_JOB_DETAILS(sched_name,job_name,job_group)
) engine=innodb;

-- ----------------------------
-- 3、 存储简单的 Trigger，包括重复次数，间隔，以及已触发的次数
-- ----------------------------
drop table if exists QRTZ_SIMPLE_TRIGGERS;
create table QRTZ_SIMPLE_TRIGGERS (
    sched_name           varchar(120)    not null,
    trigger_name         varchar(200)    not null,
    trigger_group        varchar(200)    not null,
    repeat_count         bigint(7)       not null,
    repeat_interval      bigint(12)      not null,
    times_triggered      bigint(10)      not null,
    primary key (sched_name,trigger_name,trigger_group),
    foreign key (sched_name,trigger_name,trigger_group) references QRTZ_TRIGGERS(sched_name,trigger_name,trigger_group)
) engine=innodb;

-- ----------------------------
-- 4、 存储 Cron Trigger，包括 Cron 表达式和时区信息
-- ---------------------------- 
drop table if exists QRTZ_CRON_TRIGGERS;
create table QRTZ_CRON_TRIGGERS (
    sched_name           varchar(120)    not null,
    trigger_name         varchar(200)    not null,
    trigger_group        varchar(200)    not null,
    cron_expression      varchar(200)    not null,
    time_zone_id         varchar(80),
    primary key (sched_name,trigger_name,trigger_group),
    foreign key (sched_name,trigger_name,trigger_group) references QRTZ_TRIGGERS(sched_name,trigger_name,trigger_group)
) engine=innodb;

-- ----------------------------
-- 5、 Trigger 作为 Blob 类型存储(用于 Quartz 用户用 JDBC 创建他们自己定制的 Trigger 类型，JobStore 并不知道如何存储实例的时候)
-- ---------------------------- 
drop table if exists QRTZ_BLOB_TRIGGERS;
create table QRTZ_BLOB_TRIGGERS (
    sched_name           varchar(120)    not null,
    trigger_name         varchar(200)    not null,
    trigger_group        varchar(200)    not null,
    blob_data            blob            null,
    primary key (sched_name,trigger_name,trigger_group),
    foreign key (sched_name,trigger_name,trigger_group) references QRTZ_TRIGGERS(sched_name,trigger_name,trigger_group)
) engine=innodb;

-- ----------------------------
-- 6、 以 Blob 类型存储存放日历信息， quartz可配置一个日历来指定一个时间范围
-- ---------------------------- 
drop table if exists QRTZ_CALENDARS;
create table QRTZ_CALENDARS (
    sched_name           varchar(120)    not null,
    calendar_name        varchar(200)    not null,
    calendar             blob            not null,
    primary key (sched_name,calendar_name)
) engine=innodb;

-- ----------------------------
-- 7、 存储已暂停的 Trigger 组的信息
-- ---------------------------- 
drop table if exists QRTZ_PAUSED_TRIGGER_GRPS;
create table QRTZ_PAUSED_TRIGGER_GRPS (
    sched_name           varchar(120)    not null,
    trigger_group        varchar(200)    not null,
    primary key (sched_name,trigger_group)
) engine=innodb;

-- ----------------------------
-- 8、 存储与已触发的 Trigger 相关的状态信息，以及相联 Job 的执行信息
-- ---------------------------- 
drop table if exists QRTZ_FIRED_TRIGGERS;
create table QRTZ_FIRED_TRIGGERS (
    sched_name           varchar(120)    not null,
    entry_id             varchar(95)     not null,
    trigger_name         varchar(200)    not null,
    trigger_group        varchar(200)    not null,
    instance_name        varchar(200)    not null,
    fired_time           bigint(13)      not null,
    sched_time           bigint(13)      not null,
    priority             integer         not null,
    state                varchar(16)     not null,
    job_name             varchar(200)    null,
    job_group            varchar(200)    null,
    is_nonconcurrent     varchar(1)      null,
    requests_recovery    varchar(1)      null,
    primary key (sched_name,entry_id)
) engine=innodb;

-- ----------------------------
-- 9、 存储少量的有关 Scheduler 的状态信息，假如是用于集群中，可以看到其他的 Scheduler 实例
-- ---------------------------- 
drop table if exists QRTZ_SCHEDULER_STATE; 
create table QRTZ_SCHEDULER_STATE (
    sched_name           varchar(120)    not null,
    instance_name        varchar(200)    not null,
    last_checkin_time    bigint(13)      not null,
    checkin_interval     bigint(13)      not null,
    primary key (sched_name,instance_name)
) engine=innodb;

-- ----------------------------
-- 10、 存储程序的悲观锁的信息(假如使用了悲观锁)
-- ---------------------------- 
drop table if exists QRTZ_LOCKS;
create table QRTZ_LOCKS (
    sched_name           varchar(120)    not null,
    lock_name            varchar(40)     not null,
    primary key (sched_name,lock_name)
) engine=innodb;

drop table if exists QRTZ_SIMPROP_TRIGGERS;
create table QRTZ_SIMPROP_TRIGGERS (
    sched_name           varchar(120)    not null,
    trigger_name         varchar(200)    not null,
    trigger_group        varchar(200)    not null,
    str_prop_1           varchar(512)    null,
    str_prop_2           varchar(512)    null,
    str_prop_3           varchar(512)    null,
    int_prop_1           int             null,
    int_prop_2           int             null,
    long_prop_1          bigint          null,
    long_prop_2          bigint          null,
    dec_prop_1           numeric(13,4)   null,
    dec_prop_2           numeric(13,4)   null,
    bool_prop_1          varchar(1)      null,
    bool_prop_2          varchar(1)      null,
    primary key (sched_name,trigger_name,trigger_group),
    foreign key (sched_name,trigger_name,trigger_group) references QRTZ_TRIGGERS(sched_name,trigger_name,trigger_group)
) engine=innodb;

commit;

最后：https://pan.baidu.com/s/1Ni7EzoS3SCl7jiNlZLZTTA
需要密码的q 2439644676