搜索到
89
篇与
的结果
-
mybatis获取自增主键 我们在进行数据库设计时，经常使用自增主键的设计（(⊙o⊙)…，其实，我本人不太喜欢这种设计，我更喜欢使用UUID的方式）。对于自增主键，我们在进行插入时，一般不会指定具体的主键，而是让数据库自己生成一个主键。问题分析这样就会出现一个问题，比如，我们有一个学生表（student），有一个课程表（course），其中在课程表里面有一个外键（student_id），用户关联学生表。如果我们在插入学生表的同时，希望同时插入课程表，那么就会产生一个问题，在插入初始化学生实体时，我们没有设置主键值，导致插入课程表时，获取不到当前学生实体的主键。mybatis问题解决预制表我们先创建两张表。学生表（student)CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;课程表（course）CREATE TABLE `course` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `course_name` varchar(255) DEFAULT NULL, `student_id` bigint(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `course_ibfk_1` (`student_id`), CONSTRAINT `course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;预制实体Student.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ @Data public class Student { private Long id; private String name; }Course.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ @Data public class Course { private Long id; private String courseName; private Long studentId; }mapperStudentMapper.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ public interface StudentMapper { int deleteByPrimaryKey(Long id); int insert(Student record); int insertSelective(Student record); Student selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Student record); int updateByPrimaryKey(Student record); }CourseMapper.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ public interface CourseMapper { int deleteByPrimaryKey(Long id); int insert(Course record); int insertSelective(Course record); Course selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Course record); int updateByPrimaryKey(Course record); }/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ public interface CourseMapper { int deleteByPrimaryKey(Long id); int insert(Course record); int insertSelective(Course record); Course selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Course record); int updateByPrimaryKey(Course record); }xmlStudentMapper.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="net.xiangcaowuyu.mybatis.mapper.StudentMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.Student"> <!--@mbg.generated--> <!--@Table student--> <id column="id" jdbcType="BIGINT" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <sql id="Base_Column_List"> <!--@mbg.generated--> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> <!--@mbg.generated--> select <include refid="Base_Column_List" /> from student where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> <!--@mbg.generated--> delete from student where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.mybatis.entity.Student"> <!--@mbg.generated--> update student <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.mybatis.entity.Student"> <!--@mbg.generated--> update student set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>CourseMapper.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="net.xiangcaowuyu.mybatis.mapper.CourseMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.Course"> <!--@mbg.generated--> <!--@Table course--> <id column="id" jdbcType="BIGINT" property="id" /> <result column="course_name" jdbcType="VARCHAR" property="courseName" /> <result column="student_id" jdbcType="BIGINT" property="studentId" /> </resultMap> <sql id="Base_Column_List"> <!--@mbg.generated--> id, course_name, student_id </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> <!--@mbg.generated--> select <include refid="Base_Column_List" /> from course where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> <!--@mbg.generated--> delete from course where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Course" useGeneratedKeys="true"> <!--@mbg.generated--> insert into course (course_name, student_id) values (#{courseName,jdbcType=VARCHAR}, #{studentId,jdbcType=BIGINT}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Course" useGeneratedKeys="true"> <!--@mbg.generated--> insert into course <trim prefix="(" suffix=")" suffixOverrides=","> <if test="courseName != null"> course_name, </if> <if test="studentId != null"> student_id, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="courseName != null"> #{courseName,jdbcType=VARCHAR}, </if> <if test="studentId != null"> #{studentId,jdbcType=BIGINT}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.mybatis.entity.Course"> <!--@mbg.generated--> update course <set> <if test="courseName != null"> course_name = #{courseName,jdbcType=VARCHAR}, </if> <if test="studentId != null"> student_id = #{studentId,jdbcType=BIGINT}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.mybatis.entity.Course"> <!--@mbg.generated--> update course set course_name = #{courseName,jdbcType=VARCHAR}, student_id = #{studentId,jdbcType=BIGINT} where id = #{id,jdbcType=BIGINT} </update> </mapper>我们看一下插入语句<insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student (`name`) values (#{name,jdbcType=VARCHAR}) </insert>能够获取主键的关键在于keyColumn="id" keyProperty="id" useGeneratedKeys="true"其中keyColumn用于指定主键对应数据库的列，keyProperty对应实体的主键字段，useGeneratedKeys代表使用生成的主键。服务层我们看一下服务层的关键代码@Resource private StudentMapper studentMapper; @Resource private CourseMapper courseMapper; @Override public int insert(Student student) { studentMapper.insert(student); Course course = new Course(); course.setStudentId(student.getId()); course.setCourseName("测试"); courseMapper.insert(course); return 1; }调用服务层@Resource StudentService studentService; @RequestMapping("/student/insert") public String insertStudent() { Student student = new Student(); student.setName("张三"); studentService.insert(student); return "success"; }我们插入student时，没有指定student的id字段，在调用insert方法后，mybatis会自动将生成的主键，赋值给id字段，所以我们在插入course表时，就能获取到student的主键。 -
Spring Boot Mybatis一对一、一对多查询 数据库查询时，我们用的比较多的一般是一对一查询还有一对多查询。所谓一对一查询，就是查询对象关联的对象是唯一的，比如图书与作者，一般情况下，一个图书只有一个作者，这就是一对一查询。一对多查询，是指一个查询对象，关联多个对象，比如权限系统中，用户与角色的对应，一个用户可能属于一个角色，也可能属于多个角色，这就是一对多。下面我们针对介绍一下在mybatis中如何实现一对一及一对多的查询。创建项目并添加依赖这块没什么好说的，主要添加依赖如下： <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>配置控制台输出sql为了查询sql输出信息，我们配置一下输出日志信息，让sql语句在控制台输出spring: datasource: url: jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: root server: port: 8888 compression: enabled: true mybatis: type-aliases-package: net.xiangcaowuyu.mybatis.entity mapper-locations: classpath:mapper/*.xml configuration: cache-enabled: true logging: level: net.xiangcaowuyu.mybatis.mapper: DEBUG1.一对一查询这里，我们以图书与作者的对应关系为例，约定一个图书只能有一个作者。1.1 数据库表图书对应数据库表为book，作者对应数据库表为author，创建表及预制表数据sql如下：1.1.1 book表/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 01:32:16 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for book -- ---------------------------- DROP TABLE IF EXISTS `book`; CREATE TABLE `book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `aid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of book -- ---------------------------- INSERT INTO `book` VALUES (1, '《射雕英雄传》', 1); SET FOREIGN_KEY_CHECKS = 1; 1.1.2 author表/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 01:32:38 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for author -- ---------------------------- DROP TABLE IF EXISTS `author`; CREATE TABLE `author` ( `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, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of author -- ---------------------------- INSERT INTO `author` VALUES (1, '张三', 35); SET FOREIGN_KEY_CHECKS = 1;1.2 创建实体1.2.1 Author.java/** * author * @author laughing */ @Data public class Author implements Serializable { private Integer id; private String name; private Integer age; private static final long serialVersionUID = 1L; }1.2.2 Book.java因为是图书（book）关联作者（author），所以我们需要在图书（book）实体类中，加入作者（author）属性，用于sql查询映射。/** * book * @author laughing */ @Data public class Book implements Serializable { private Integer id; private String name; private Author author; private static final long serialVersionUID = 1L; }1.3 创建mapper1.3.1 BookMapper.javapublic interface BookMapper { /** * 查询book * * @param id 主键 * @return book */ Book selectByPrimaryKey(Integer id); }1.4 创建xml一对一关联查询的关键在于association关键字，这个节点中的内容，和 resultMap 一样，也是 id，result 等，在这个节点中，我们还可以继续描述一对一。由于在实际项目中，每次返回的数据类型可能都会有差异，这就需要定义多个 resultMap，而这多个 resultMap 中，又有一部份属性是相同的，所以，我们可以将相同的部分抽出来，做成一个公共的模板，然后通过extends关键字，继承公共的模板。<?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="net.xiangcaowuyu.mybatis.mapper.BookMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.Book"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <resultMap id="BookWithAuthor" type="net.xiangcaowuyu.mybatis.entity.Book" extends="BaseResultMap"> <association property="author" javaType="net.xiangcaowuyu.mybatis.entity.Author"> <id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/> <id column="name" property="name"/> <id column="age" property="age"/> </association> </resultMap> <sql id="Base_Column_List"> id, `name`, aid </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BookWithAuthor"> select <include refid="Base_Column_List" /> from book where id = #{id,jdbcType=INTEGER} </select> </mapper>1.5 创建服务层为了节省篇幅，我们这里就省略掉接口代码，具体服务层代码如下：/** * @author laughing * @date 2020-10-16 */ @Service public class BookServiceImpl implements BookService { @Resource BookMapper bookMapper; /** * 查询book * * @param id 主键 * @return book */ @Override public Book selectByPrimaryKey(Integer id) { return bookMapper.selectByPrimaryKey(id); } }1.6 创建controller创建一个rest接口，测试我们的查询/** * @author laughing * @date 2020-10-16 */ @RestController @RequestMapping("book") public class BookController { private final BookService bookService; public BookController(BookService bookService){ this.bookService=bookService; } /** * 查询book * * @param id 主键 * @return book */ @GetMapping("selectByPrimaryKey/{id}") public Book selectByPrimaryKey(@PathVariable("id") Integer id) { return bookService.selectByPrimaryKey(id); } }2.一对多查询一对多查询与一对一查询类似，区别主要在于实体的关联关系及xml文件的配置。我们以权限系统常见的用户、角色对应关系为例。约定一个用户可以关联多个角色。2.1 数据库表我们数据库有三张表，user用户存储用户信息，role用于存储角色信息，user_role用于存储用户与角色的关联关系，相关表结构及数据如下/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 02:02:01 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for role -- ---------------------------- DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `nameZh` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of role -- ---------------------------- INSERT INTO `role` VALUES (1, 'dba', '数据库管理员'); INSERT INTO `role` VALUES (2, 'admin', '系统管理员'); INSERT INTO `role` VALUES (3, 'user', '用户'); SET FOREIGN_KEY_CHECKS = 1; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `enabled` tinyint(1) NULL DEFAULT NULL, `locked` tinyint(1) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, 'root', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); INSERT INTO `user` VALUES (2, 'admin', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); INSERT INTO `user` VALUES (3, 'sang', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); SET FOREIGN_KEY_CHECKS = 1; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user_role -- ---------------------------- DROP TABLE IF EXISTS `user_role`; CREATE TABLE `user_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NULL DEFAULT NULL, `rid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user_role -- ---------------------------- INSERT INTO `user_role` VALUES (1, 1, 1); INSERT INTO `user_role` VALUES (2, 1, 2); INSERT INTO `user_role` VALUES (3, 2, 2); INSERT INTO `user_role` VALUES (4, 3, 3); SET FOREIGN_KEY_CHECKS = 1;1.2 创建实体1.2.1 User.java/** * user * * @author laughing */ @Data @JsonIgnoreProperties(value = { "handler"}) public class User implements Serializable { private Integer id; private String username; private String password; private Boolean enabled; private Boolean locked; private List<Role> roleList; private static final long serialVersionUID = 1L; }1.2.2 Role.java/** * role * @author laughing */ @Data @JsonIgnoreProperties(value = { "handler"}) public class Role implements Serializable { private Integer id; private String name; private String namezh; private static final long serialVersionUID = 1L; }1.3 创建mapperpublic interface UserMapper { /** * 根据Id查找 * @param id * @return */ User selectByPrimaryKey(Integer id); /** * 根据用户查找角色列表 * @param uid * @return */ List<Role> getRolesByUid(@Param("uid") Integer uid); }1.4 创建xml一对一的查询通过association，一对多的查询与此类似，只是通过collection关键字，替换association关键字。1.4.1 方式1<?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="net.xiangcaowuyu.mybatis.mapper.UserMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.User"> <id column="id" jdbcType="INTEGER" property="id"/> <result column="username" jdbcType="VARCHAR" property="username"/> <result column="password" jdbcType="VARCHAR" property="password"/> <result column="enabled" jdbcType="BOOLEAN" property="enabled"/> <result column="locked" jdbcType="BOOLEAN" property="locked"/> <collection property="roleList" ofType="net.xiangcaowuyu.mybatis.entity.Role"> <result column="rid" property="id" javaType="java.lang.Integer"/> <result column="rname" property="name" javaType="java.lang.String"/> <result column="rnameZh" property="namezh" javaType="java.lang.String"/> </collection> </resultMap> <sql id="Base_Column_List"> id, username, `password`, enabled, locked </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select user.*,role.id as rid,role.name as rname,role.nameZh as rnameZh from user,role,user_role where user.id = user_role.uid and role.id = user_role.rid and user.id = #{id,jdbcType=INTEGER} </select> </mapper>1.4.2 懒加载方式通过fetchType="lazy"实现懒加载<?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="net.xiangcaowuyu.mybatis.mapper.UserMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.User"> <id column="id" jdbcType="INTEGER" property="id"/> <result column="username" jdbcType="VARCHAR" property="username"/> <result column="password" jdbcType="VARCHAR" property="password"/> <result column="enabled" jdbcType="BOOLEAN" property="enabled"/> <result column="locked" jdbcType="BOOLEAN" property="locked"/> <collection property="roleList" ofType="net.xiangcaowuyu.mybatis.entity.Role" column="id" select="getRolesByUid" fetchType="lazy"> </collection> </resultMap> <sql id="Base_Column_List"> id, username, `password`, enabled, locked </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from user where user.id = #{id,jdbcType=INTEGER} </select> <select id="getRolesByUid" resultType="net.xiangcaowuyu.mybatis.entity.Role"> SELECT r.* FROM role r,user_role ur WHERE r.`id`=ur.`rid` AND ur.`uid`=#{uid} </select> </mapper>1.5 创建服务层服务层我们依然省略掉接口代码/** * @author laughing * @date 2020-10-17 */ @Service public class UserServiceImpl implements UserService { @Resource UserMapper userMapper; /** * 根据Id查找 * * @param id * @return */ @Override public User selectByPrimaryKey(Integer id) { return userMapper.selectByPrimaryKey(id); } }1.6 创建controller创建rest接口，测试代码/** * @author laughing * @date 2020-10-17 */ @RestController @RequestMapping("user") public class UserController { private final UserService userService; public UserController(UserService userService){ this.userService=userService; } @RequestMapping("selectByPrimaryKey/{id}") public User selectByPrimaryKey(@PathVariable("id") Integer id) { return userService.selectByPrimaryKey(id); } }
-
超详细Vue+Spring Boot整合Shiro前后端分离架构-Shiro权限分配设计（四） 前端设计使用npm创建web工程通过以下命令创建一个web工程vue init webpack shiroui添加axios，用于请求后端服务cnpm install axios --save在工程main.js中引入axiosVue.config.productionTip = false axios.defaults.baseURL = 'localhost:8080' Vue.prototype.$axios = axios添加elementuicnpm i element-ui -S在工程main.js中引入elementuiimport ElementUI from 'element-ui' import 'element-ui/lib/theme-chalk/index.css' ··· Vue.use(ElementUI)main.js预览实现登录功能前端界面<template> <div id="app"> <el-form ref="form" :model="user" label-width="80px"> <el-form-item label="用户编号"> <el-input v-model="user.userCode" placeholder="请输入用户编号"></el-input> </el-form-item> <el-form-item label="密 码"> <el-input v-model="user.password" placeholder="请输入密码" show-password></el-input> </el-form-item> <el-form-item> <el-button type="primary" @click="onSubmit">登录</el-button> <el-button>取消</el-button> </el-form-item> </el-form> <el-breadcrumb separator-class="el-icon-arrow-right"> <el-breadcrumb-item :to="{ path: '/' }">用户管理</el-breadcrumb-item> <el-breadcrumb-item :to="{ path: '/role' }">角色管理</el-breadcrumb-item> <el-breadcrumb-item :to="{ path: '/permission' }">权限管理</el-breadcrumb-item> <el-breadcrumb-item>角色分配</el-breadcrumb-item> <el-breadcrumb-item>权限分配</el-breadcrumb-item> </el-breadcrumb> <router-view/> </div> </template> <script> export default { name: 'App', data () { return { user: { userCode: undefined, password: undefined } } }, methods: { onSubmit: function () { debugger this.$axios({ method: 'post', url: '/shiro/login', params: this.user }).then(result => { this.$message(result.data) }).catch(e => { console.log(e) }) } } } </script> <style> </style>登录后端代码/** * @author laughing * @date 2020/10/11 * @site https://www.lisen.org */ @RestController @RequestMapping("/shiro") public class ShiroController { private final Logger logger = LoggerFactory.getLogger(ShiroController.class); @Resource ShiroService shiroService; @RequestMapping("/findUserByUserCode") public ShiroUser findUserByUserCode() { String userCode = "lisen"; ShiroUser shiroUser = shiroService.findUserByCode(userCode); return shiroUser; } @RequestMapping("/login") public String login(@RequestParam("userCode") String userCode, @RequestParam("password") String password) { // password = new SimpleHash(AuthenConst.HASH_ALGORITHM_NAME,password,AuthenConst.SALT,AuthenConst.HASH_INTERACTIONS).toHex(); UsernamePasswordToken usernamePasswordToken = new UsernamePasswordToken(userCode, password); Subject subject = SecurityUtils.getSubject(); try { //进行验证，这里可以捕获异常，然后返回对应信息 subject.login(usernamePasswordToken); } catch (UnknownAccountException e) { logger.error("用户名不存在！", e); return "用户名不存在！"; } catch (AuthenticationException e) { logger.error("账号或密码错误！", e); return "账号或密码错误！"; } catch (AuthorizationException e) { logger.error("没有权限！", e); return "没有权限"; } return "login success"; } @RequestMapping("/index") public String index(String userCode, String password) { return "index"; } @RequiresPermissions("permission") @RequestMapping("/permission") public String permission() { return "permission"; } @RequiresPermissions("dept:add") @RequestMapping("/deptadd") public String deptAdd() { return "dept:add"; } @RequestMapping("/nopermission") public String noPermission() { return "noPermission"; } }前端权限调用<template> <div> <el-form> <el-form-item> <el-button @click="findAll">加载用户信息</el-button> <el-button @click="dialogFormVisible=true">增加用户</el-button> <el-button @click="dialogFormRoleVisible=true">分配岗位</el-button> </el-form-item> </el-form> <el-table :data="tableData" style="width: 100%"> <el-table-column label="ID" width="180"> <template slot-scope="scope"> <i class="el-icon-time"></i> <span style="margin-left: 10px">{{ scope.row.id }}</span> </template> </el-table-column> <el-table-column label="用户编号" width="180"> <template slot-scope="scope"> <span style="margin-left: 10px">{{ scope.row.userCode }}</span> </template> </el-table-column> <el-table-column label="用户姓名" width="180"> <template slot-scope="scope"> <span style="margin-left: 10px">{{ scope.row.userName }}</span> </template> </el-table-column> <el-table-column label="操作"> <template slot-scope="scope"> <el-button size="mini" @click="handleEdit(scope.$index, scope.row)">编辑</el-button> <el-button size="mini" type="danger" @click="handleDelete(scope.$index, scope.row)">删除</el-button> </template> </el-table-column> </el-table> <!-- 增加用户--> <el-dialog title="增加用户" :visible.sync="dialogFormVisible"> <el-form :model="form" :rules="rules" ref="form"> <el-form-item label="用户编号" prop="userCode"> <el-input v-model="form.userCode" autocomplete="off" maxlength="30" show-word-limit></el-input> </el-form-item> <el-form-item label="用户名称"> <el-input v-model="form.userName" autocomplete="off" maxlength="30" show-word-limit></el-input> </el-form-item> <el-form-item label="密 码"> <el-input v-model="form.password" autocomplete="off" maxlength="30" show-word-limit></el-input> </el-form-item> </el-form> <div slot="footer" class="dialog-footer"> <el-button @click="dialogFormVisible = false">取 消</el-button> <el-button type="primary" @click="submitForm">确 定</el-button> </div> </el-dialog> <!-- 分配角色--> <el-dialog title="分配角色" :visible.sync="dialogFormRoleVisible"> <el-form :model="roleForm" :rules="roleRules" ref="roleForm"> <el-autocomplete popper-class="my-autocomplete" v-model="state" :fetch-suggestions="querySearch" placeholder="请输入内容" @select="handleSelect"> <i class="el-icon-edit el-input__icon" slot="suffix" @click="handleIconClick"> </i> <template slot-scope="{ item }"> <div class="name">{{ item.value }}</div> <span class="addr">{{ item.address }}</span> </template> </el-autocomplete> </el-form> <div slot="footer" class="dialog-footer"> <el-button @click="dialogFormRoleVisible = false">取 消</el-button> <el-button type="primary" @click="submitFormRole">确 定</el-button> </div> </el-dialog> </div> </template> <script> export default { data () { return { tableData: [], dialogFormVisible: false, dialogFormRoleVisible: false, form: { userCode: '', userName: '', password: '' }, rules: { userCode: [ {required: true, message: '请输入用户编号', trigger: 'blur'} ] }, roleForm: {}, restaurants: [], state: '' } }, methods: { handleEdit (index, row) { console.log(index, row) }, handleDelete (index, row) { console.log(index, row) }, findAll () { const _this = this this.$axios({ method: 'post', url: '/user/findAll' }).then(function (result) { _this.tableData = result.data }).catch(function (err) { console.log(err) }) }, submitForm: function () { debugger this.$refs['form'].validate((valid) => { if (valid) { this.dialogFormVisible = false this.$axios({ method: 'post', params: this.form, url: '/user/insert' }) } else { return false } }) }, submitFormRole: function () { debugger const self = this this.$refs['roleForm'].validate((valid) => { if (valid) { // this.dialogFormRoleVisible = false this.$axios({ method: 'post', params: this.form, url: '/user/setRole' }).then(function (response) { self.$message(response.data) }) } else { return false } }) }, querySearch (queryString, cb) { var restaurants = this.restaurants var results = queryString ? restaurants.filter(this.createFilter(queryString)) : restaurants // 调用 callback 返回建议列表的数据 cb(results) }, createFilter (queryString) { return (restaurant) => { return (restaurant.value.toLowerCase().indexOf(queryString.toLowerCase()) === 0) } }, loadAll () { return [ {'value': '三全鲜食（北新泾店）', 'address': '长宁区新渔路144号'}, {'value': 'Hot honey 首尔炸鸡（仙霞路）', 'address': '上海市长宁区淞虹路661号'}, {'value': '新旺角茶餐厅', 'address': '上海市普陀区真北路988号创邑金沙谷6号楼113'}, {'value': '泷千家(天山西路店)', 'address': '天山西路438号'}, {'value': '胖仙女纸杯蛋糕（上海凌空店）', 'address': '上海市长宁区金钟路968号1幢18号楼一层商铺18-101'}, {'value': '贡茶', 'address': '上海市长宁区金钟路633号'}, {'value': '豪大大香鸡排超级奶爸', 'address': '上海市嘉定区曹安公路曹安路1685号'}, {'value': '茶芝兰（奶茶，手抓饼）', 'address': '上海市普陀区同普路1435号'}, {'value': '十二泷町', 'address': '上海市北翟路1444弄81号B幢-107'}, {'value': '星移浓缩咖啡', 'address': '上海市嘉定区新郁路817号'}, {'value': '阿姨奶茶/豪大大', 'address': '嘉定区曹安路1611号'}, {'value': '新麦甜四季甜品炸鸡', 'address': '嘉定区曹安公路2383弄55号'}, {'value': 'Monica摩托主题咖啡店', 'address': '嘉定区江桥镇曹安公路2409号1F，2383弄62号1F'}, {'value': '浮生若茶（凌空soho店）', 'address': '上海长宁区金钟路968号9号楼地下一层'}, {'value': 'NONO JUICE 鲜榨果汁', 'address': '上海市长宁区天山西路119号'}, {'value': 'CoCo都可(北新泾店）', 'address': '上海市长宁区仙霞西路'}, {'value': '快乐柠檬（神州智慧店）', 'address': '上海市长宁区天山西路567号1层R117号店铺'}, {'value': 'Merci Paul cafe', 'address': '上海市普陀区光复西路丹巴路28弄6号楼819'}, {'value': '猫山王（西郊百联店）', 'address': '上海市长宁区仙霞西路88号第一层G05-F01-1-306'}, {'value': '枪会山', 'address': '上海市普陀区棕榈路'}, {'value': '纵食', 'address': '元丰天山花园(东门) 双流路267号'}, {'value': '钱记', 'address': '上海市长宁区天山西路'}, {'value': '壹杯加', 'address': '上海市长宁区通协路'}, {'value': '唦哇嘀咖', 'address': '上海市长宁区新泾镇金钟路999号2幢（B幢）第01层第1-02A单元'}, {'value': '爱茜茜里(西郊百联)', 'address': '长宁区仙霞西路88号1305室'}, {'value': '爱茜茜里(近铁广场)', 'address': '上海市普陀区真北路818号近铁城市广场北区地下二楼N-B2-O2-C商铺'}, {'value': '鲜果榨汁（金沙江路和美广店）', 'address': '普陀区金沙江路2239号金沙和美广场B1-10-6'}, {'value': '开心丽果（缤谷店）', 'address': '上海市长宁区威宁路天山路341号'}, {'value': '超级鸡车（丰庄路店）', 'address': '上海市嘉定区丰庄路240号'}, {'value': '妙生活果园（北新泾店）', 'address': '长宁区新渔路144号'}, {'value': '香宜度麻辣香锅', 'address': '长宁区淞虹路148号'}, {'value': '凡仔汉堡（老真北路店）', 'address': '上海市普陀区老真北路160号'}, {'value': '港式小铺', 'address': '上海市长宁区金钟路968号15楼15-105室'}, {'value': '蜀香源麻辣香锅（剑河路店）', 'address': '剑河路443-1'}, {'value': '北京饺子馆', 'address': '长宁区北新泾街道天山西路490-1号'}, {'value': '饭典*新简餐（凌空SOHO店）', 'address': '上海市长宁区金钟路968号9号楼地下一层9-83室'}, {'value': '焦耳·川式快餐（金钟路店）', 'address': '上海市金钟路633号地下一层甲部'}, {'value': '动力鸡车', 'address': '长宁区仙霞西路299弄3号101B'}, {'value': '浏阳蒸菜', 'address': '天山西路430号'}, {'value': '四海游龙（天山西路店）', 'address': '上海市长宁区天山西路'}, {'value': '樱花食堂（凌空店）', 'address': '上海市长宁区金钟路968号15楼15-105室'}, {'value': '壹分米客家传统调制米粉(天山店)', 'address': '天山西路428号'}, {'value': '福荣祥烧腊（平溪路店）', 'address': '上海市长宁区协和路福泉路255弄57-73号'}, {'value': '速记黄焖鸡米饭', 'address': '上海市长宁区北新泾街道金钟路180号1层01号摊位'}, {'value': '红辣椒麻辣烫', 'address': '上海市长宁区天山西路492号'}, {'value': '(小杨生煎)西郊百联餐厅', 'address': '长宁区仙霞西路88号百联2楼'}, {'value': '阳阳麻辣烫', 'address': '天山西路389号'}, {'value': '南拳妈妈龙虾盖浇饭', 'address': '普陀区金沙江路1699号鑫乐惠美食广场A13'} ] }, handleSelect (item) { console.log(item) }, handleIconClick (ev) { console.log(ev) } }, mounted () { this.restaurants = this.loadAll() } } </script> <style> .my-autocomplete { li { line-height: normal; padding: 7px; .name { text-overflow: ellipsis; overflow: hidden; } .addr { font-size: 12px; color: #b4b4b4; } .highlighted .addr { color: #ddd; } } } </style> 后端权限调用/** * @author laughing * @date 2020/10/13 * @site https://www.xiangcaowuyu.net */ @RestController @RequestMapping("user") public class UserController { @Resource ShiroUserService shiroUserService; /** * 查找所有用户 * @return */ @RequiresPermissions("user:find") @RequestMapping("/findAll") public List<ShiroUser> findAll() { return shiroUserService.findAll(); } /** * 查找所有用户 * @return */ @RequiresPermissions("user:find") @RequestMapping("/insert") public int save(ShiroUser shiroUser) { return shiroUserService.insert(shiroUser); } }
-
超详细Vue+Spring Boot整合Shiro前后端分离架构-Shiro后端设计（三） Shiro实战shiro实战内容包括三个部分：（1）shiro后台表结构，用于存储shiro对应的用户、角色、权限及关联关系。（2）后端代码，及基于shiro配置用户、角色、权限及对应关系以及登录、认证。（3）前端代码，维护shiro信息及登录、认证。这篇博文我们介绍第二部分，即后端设计后端代码后端数据库访问用的mybatis及lombok插件。添加依赖 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 热部署--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> </dependency> <!-- mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- shiro --> <dependency> <groupId>org.apache.shiro</groupId> <artifactId>shiro-spring</artifactId> <version>${spring.shiro.version}</version> </dependency> <dependency> <!--session持久化插件--> <groupId>org.crazycake</groupId> <artifactId>shiro-redis</artifactId> <version>${shiro.redis.version}</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> <!-- 导入配置文件处理器 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> </dependencies>配置文件主要配置数据库连接、mybatis及shiro信息。spring: datasource: url: jdbc:mysql://localhost:3306/shiro?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver mybatis: type-aliases-package: net.xiangcaowuyu.shiro.entity mapper-locations: classpath:mapper/*.xml shiro: user: loginUrl: /shiro/login/** unauthorizedUrl: /shiro/logout indexUrl: /shiro/index captchaEnabled: true captchaType: math redis: host: localhost port: 6379 cookie: domain: path: / httpOnly: false maxAge: 72 #Hours，利用 cookie 免登录。 secure: false session: expireTime: 72 #Hours dbSyncPeriod: 1 validationInterval: 10 maxSession: -1 kickoutAfter: false server: port: 8888 #配置跨域 cors: allowedOrigin: - http://localhost:8080 allowCredentials: true allowHeaders: - \* allowMethods: - GET - POST - PUT - DELETE - OPTIONS maxAge: 7200 path: /**mybatis代码配置完成后，我们先生成mybatis代码，完成mybatis的操作。我使用Free MyBatis plugin插件生成一些代码。mybatis实体ShiroUser.java@Data public class ShiroUser implements Serializable { private static final long serialVersionUID = 1L; private Long id; private String userCode; private String userName; private String password; private Set<ShiroRole> shiroRoleSet; }ShiroRole.java@Data public class ShiroRole implements Serializable { private static final long serialVersionUID = 1L; private Long id; private String roleCode; private String roleName; private Set<ShiroPermission> shiroPermissionSet; }ShiroPermission.java@Data public class ShiroPermission implements Serializable { private Long id; private String permissionCode; private String permissionName; private static final long serialVersionUID = 1L; }ShiroUserRole.java@Data public class ShiroUserRole implements Serializable { private Long id; private Long userId; private Long roleId; private static final long serialVersionUID = 1L; }ShiroRolePermission.java@Data public class ShiroRolePermission implements Serializable { private Long id; private Long roleId; private Long permissionId; private static final long serialVersionUID = 1L; }数据访问接口ShiroUserMapper.java@Mapper public interface ShiroUserMapper { int deleteByPrimaryKey(Long id); int insert(ShiroUser record); int insertSelective(ShiroUser record); /** * 根据主键获取用户信息 * @param id Id * @return 用户 */ ShiroUser selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(ShiroUser record); int updateByPrimaryKey(ShiroUser record); /** * 根据用户编号获取 * @param userCode 用户编号 * @return 用户 */ ShiroUser findUserByUserCode(String userCode); }ShiroRoleMapper.java@Mapper public interface ShiroRoleMapper { int deleteByPrimaryKey(Long id); int insert(ShiroRole record); int insertSelective(ShiroRole record); ShiroRole selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(ShiroRole record); int updateByPrimaryKey(ShiroRole record); /** * 根据用户编号获取其角色列表 * @param userCode 用户编号 * @return 权限列表 */ Set<ShiroRole> findByUserCode(String userCode); }ShiroPermissionMapper.java@Mapper public interface ShiroPermissionMapper { int deleteByPrimaryKey(Long id); int insert(ShiroPermission record); int insertSelective(ShiroPermission record); ShiroPermission selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(ShiroPermission record); int updateByPrimaryKey(ShiroPermission record); /** * 获取角色对应权限 * @param roleId 角色Id * @return 权限集合 */ Set<ShiroPermission> findByRoleId(long roleId); }ShiroUserRoleMapper.java@Mapper public interface ShiroUserRoleMapper { int deleteByPrimaryKey(Long id); int insert(ShiroUserRole record); int insertSelective(ShiroUserRole record); ShiroUserRole selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(ShiroUserRole record); int updateByPrimaryKey(ShiroUserRole record); }ShiroRolePermissionMapper.java@Mapper public interface ShiroRolePermissionMapper { int deleteByPrimaryKey(Long id); int insert(ShiroRolePermission record); int insertSelective(ShiroRolePermission record); ShiroRolePermission selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(ShiroRolePermission record); int updateByPrimaryKey(ShiroRolePermission record); }xml文件ShiroUserMapper.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="net.xiangcaowuyu.shiro.mapper.ShiroUserMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.shiro.entity.ShiroUser"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="userCode" jdbcType="VARCHAR" property="userCode"/> <result column="userName" jdbcType="VARCHAR" property="userName"/> <result column="password" jdbcType="VARCHAR" property="password"/> </resultMap> <sql id="Base_Column_List"> id, userCode, userName, `password` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from Shiro_User where id = #{id,jdbcType=BIGINT} </select> <select id="findUserByUserCode" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from Shiro_User where userCode = #{userCode} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from Shiro_User where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUser" useGeneratedKeys="true"> insert into Shiro_User (userCode, userName, `password`) values (#{usercode,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUser" useGeneratedKeys="true"> insert into Shiro_User <trim prefix="(" suffix=")" suffixOverrides=","> <if test="userCode != null"> userCode, </if> <if test="userName != null"> userName, </if> <if test="password != null"> `password`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="userCode != null"> #{usercode,jdbcType=VARCHAR}, </if> <if test="userName != null"> #{username,jdbcType=VARCHAR}, </if> <if test="password != null"> #{password,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUser"> update Shiro_User <set> <if test="userCode != null"> userCode = #{usercode,jdbcType=VARCHAR}, </if> <if test="userName != null"> userName = #{username,jdbcType=VARCHAR}, </if> <if test="password != null"> `password` = #{password,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUser"> update Shiro_User set userCode = #{usercode,jdbcType=VARCHAR}, userName = #{username,jdbcType=VARCHAR}, `password` = #{password,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>ShiroRoleMapper.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="net.xiangcaowuyu.shiro.mapper.ShiroRoleMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.shiro.entity.ShiroRole"> <id column="id" jdbcType="BIGINT" property="id" /> <result column="roleCode" jdbcType="VARCHAR" property="roleCode" /> <result column="roleName" jdbcType="VARCHAR" property="roleName" /> </resultMap> <sql id="Base_Column_List"> id, roleCode, roleName </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from Shiro_Role where id = #{id,jdbcType=BIGINT} </select> <select id="findByUserCode" resultType="net.xiangcaowuyu.shiro.entity.ShiroRole"> select Shiro_Role.* from Shiro_Role inner join Shiro_User_Role SUR on Shiro_Role.id = SUR.roleId inner join Shiro_User SU on SUR.userId = SU.id where su.userCode = #{userCode} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from Shiro_Role where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRole" useGeneratedKeys="true"> insert into Shiro_Role (roleCode, roleName) values (#{rolecode,jdbcType=VARCHAR}, #{rolename,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRole" useGeneratedKeys="true"> insert into Shiro_Role <trim prefix="(" suffix=")" suffixOverrides=","> <if test="roleCode != null"> roleCode, </if> <if test="roleName != null"> roleName, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="roleCode != null"> #{rolecode,jdbcType=VARCHAR}, </if> <if test="roleName != null"> #{rolename,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRole"> update Shiro_Role <set> <if test="roleCode != null"> roleCode = #{rolecode,jdbcType=VARCHAR}, </if> <if test="roleName != null"> roleName = #{rolename,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRole"> update Shiro_Role set roleCode = #{rolecode,jdbcType=VARCHAR}, roleName = #{rolename,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>ShiroPermissionMapper.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="net.xiangcaowuyu.shiro.mapper.ShiroPermissionMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.shiro.entity.ShiroPermission"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="permissionCode" jdbcType="VARCHAR" property="permissionCode"/> <result column="permissionName" jdbcType="VARCHAR" property="permissionName"/> </resultMap> <sql id="Base_Column_List"> id, permissionCode, permissionName </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from Shiro_Permission where id = #{id,jdbcType=BIGINT} </select> <select id="findByRoleId" resultType="net.xiangcaowuyu.shiro.entity.ShiroPermission"> select Shiro_Permission.* from Shiro_Permission inner join Shiro_Role_Permission SRP on Shiro_Permission.id = SRP.permissionId where SRP.roleId = #{roleId} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from Shiro_Permission where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroPermission" useGeneratedKeys="true"> insert into Shiro_Permission (permissionCode, permissionName) values (#{permissioncode,jdbcType=VARCHAR}, #{permissionname,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroPermission" useGeneratedKeys="true"> insert into Shiro_Permission <trim prefix="(" suffix=")" suffixOverrides=","> <if test="permissionCode != null"> permissionCode, </if> <if test="permissionName != null"> permissionName, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="permissionCode != null"> #{permissioncode,jdbcType=VARCHAR}, </if> <if test="permissionName != null"> #{permissionname,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.shiro.entity.ShiroPermission"> update Shiro_Permission <set> <if test="permissionCode != null"> permissionCode = #{permissioncode,jdbcType=VARCHAR}, </if> <if test="permissionName != null"> permissionName = #{permissionname,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.shiro.entity.ShiroPermission"> update Shiro_Permission set permissionCode = #{permissioncode,jdbcType=VARCHAR}, permissionName = #{permissionname,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>ShiroUserRoleMapper.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="net.xiangcaowuyu.shiro.mapper.ShiroUserRoleMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.shiro.entity.ShiroUserRole"> <id column="id" jdbcType="BIGINT" property="id" /> <result column="userId" jdbcType="BIGINT" property="userId" /> <result column="roleId" jdbcType="BIGINT" property="roleId" /> </resultMap> <sql id="Base_Column_List"> id, userId, roleId </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from Shiro_User_Role where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from Shiro_User_Role where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUserRole" useGeneratedKeys="true"> insert into Shiro_User_Role (userId, roleId) values (#{userid,jdbcType=BIGINT}, #{roleid,jdbcType=BIGINT}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUserRole" useGeneratedKeys="true"> insert into Shiro_User_Role <trim prefix="(" suffix=")" suffixOverrides=","> <if test="userId != null"> userId, </if> <if test="roleId != null"> roleId, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="userid != null"> #{userid,jdbcType=BIGINT}, </if> <if test="roleId != null"> #{roleid,jdbcType=BIGINT}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUserRole"> update Shiro_User_Role <set> <if test="userId != null"> userId = #{userid,jdbcType=BIGINT}, </if> <if test="roleId != null"> roleId = #{roleid,jdbcType=BIGINT}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.shiro.entity.ShiroUserRole"> update Shiro_User_Role set userId = #{userid,jdbcType=BIGINT}, roleId = #{roleid,jdbcType=BIGINT} where id = #{id,jdbcType=BIGINT} </update> </mapper>ShiroRolePermissionMapper.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="net.xiangcaowuyu.shiro.mapper.ShiroRolePermissionMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.shiro.entity.ShiroRolePermission"> <id column="id" jdbcType="BIGINT" property="id" /> <result column="roleId" jdbcType="BIGINT" property="roleId" /> <result column="permissionId" jdbcType="BIGINT" property="permissionId" /> </resultMap> <sql id="Base_Column_List"> id, roleId, permissionId </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from Shiro_Role_Permission where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from Shiro_Role_Permission where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRolePermission" useGeneratedKeys="true"> insert into Shiro_Role_Permission (roleId, permissionId) values (#{roleid,jdbcType=BIGINT}, #{permissionid,jdbcType=BIGINT}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRolePermission" useGeneratedKeys="true"> insert into Shiro_Role_Permission <trim prefix="(" suffix=")" suffixOverrides=","> <if test="roleId != null"> roleId, </if> <if test="permissionId != null"> permissionId, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="roleId != null"> #{roleid,jdbcType=BIGINT}, </if> <if test="permissionId != null"> #{permissionid,jdbcType=BIGINT}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRolePermission"> update Shiro_Role_Permission <set> <if test="roleId != null"> roleId = #{roleid,jdbcType=BIGINT}, </if> <if test="permissionId != null"> permissionId = #{permissionid,jdbcType=BIGINT}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.shiro.entity.ShiroRolePermission"> update Shiro_Role_Permission set roleId = #{roleid,jdbcType=BIGINT}, permissionId = #{permissionid,jdbcType=BIGINT} where id = #{id,jdbcType=BIGINT} </update> </mapper>服务层服务层这里忽略了接口，有需要的可以查看源代码ShiroUserServiceImpl.java@Service public class ShiroUserServiceImpl implements ShiroUserService { @Resource ShiroUserMapper shiroUserMapper; @Resource ShiroRoleService shiroRoleService; /** * 根据用户编号获取 * * @param userCode 用户编号 * @return 用户 */ @Override public ShiroUser findUserByUserCode(String userCode) { ShiroUser shiroUser = shiroUserMapper.findUserByUserCode(userCode); Set<ShiroRole> shiroRoleSet = shiroRoleService.findByUserCode(userCode); shiroUser.setShiroRoleSet(shiroRoleSet); return shiroUser; } }ShiroRoleServiceImpl.java@Service public class ShiroRoleServiceImpl implements ShiroRoleService { @Resource ShiroRoleMapper shiroRoleMapper; @Resource ShiroPermissionService shiroPermissionService; /** * 根据用户编号获取其角色列表 * * @param userCode 用户编号 * @return 权限列表 */ @Override public Set<ShiroRole> findByUserCode(String userCode) { Set<ShiroRole> shiroRoleSet = shiroRoleMapper.findByUserCode(userCode); for (ShiroRole shiroRole : shiroRoleSet) { Set<ShiroPermission> shiroPermissionSet = shiroPermissionService.findByRoleId(shiroRole.getId()); shiroRole.setShiroPermissionSet(shiroPermissionSet); } return shiroRoleSet; } } ShiroPermissionServiceImpl.java@Service public class ShiroPermissionServiceImpl implements ShiroPermissionService { @Resource ShiroPermissionMapper shiroPermissionMapper; /** * 获取角色对应权限 * * @param roleId 角色Id * @return 权限集合 */ @Override public Set<ShiroPermission> findByRoleId(long roleId) { return shiroPermissionMapper.findByRoleId(roleId); } }ShiroServiceImpl.java@Service public class ShiroServiceImpl implements ShiroService { @Resource ShiroUserService shiroUserService; /** * 根据用户编号获取 * * @param userCode 用户编号 * @return 用户 */ @Override public ShiroUser findUserByCode(String userCode) { return shiroUserService.findUserByUserCode(userCode); } }shiro配置常量配置AuthenConst常量类，用于配置加密方式、盐、加密次数等信息。/** * @author laughing * @date 2020/10/12 * @site https://www.xiangcaowuyu.net */ public class AuthenConst { public static final String SALT = "XiangCaoWuYu"; public static final String HASH_ALGORITHM_NAME = "MD5"; public static final int HASH_INTERACTIONS = 1024; public static final String OPTION_REQUEST_NAME = "OPTIONS"; }property映射/** * @author laughing * @date 2020/10/11 * @site https://www.xiangcaowuyu.net */ @ConfigurationProperties(prefix = "shiro") @Configuration @Data public class ShiroProperty { private User user; private Redis redis; } @Data class User{ private String loginUrl; private String unauthorizedUrl; private String indexUrl; private boolean captchaEnabled; private String captchaType; } @Data class Redis{ private String host; private long port; }自定义realm/** * @author laughing * @date 2020/10/11 * @site https://www.lisen.org */ public class MyAuthenRealm extends AuthorizingRealm { @Resource ShiroService shiroService; /** * 认证 * * @param authenticationToken * @return * @throws AuthenticationException */ @Override protected AuthenticationInfo doGetAuthenticationInfo(AuthenticationToken authenticationToken) throws AuthenticationException { UsernamePasswordToken token = (UsernamePasswordToken) authenticationToken; String userCode = token.getUsername(); ShiroUser shiroUser = shiroService.findUserByCode(userCode); if (shiroUser == null) { throw new AuthenticationException("账户不存在"); } //自定义盐值 // ByteSource salt = ByteSource.Util.bytes(AuthenConst.SALT); // String password = new SimpleHash(AuthenConst.HASH_ALGORITHM_NAME, new String(token.getPassword()), AuthenConst.SALT, AuthenConst.HASH_INTERACTIONS).toHex(); if (!new String(token.getPassword()).equals(shiroUser.getPassword())) { throw new IncorrectCredentialsException("账户密码不正确"); } // Subject subject = SecurityUtils.getSubject(); // ShiroUserVO shiroUserVO = new ShiroUserVO(); // BeanUtils.copyProperties(shiroUserVO,shiroUser); // shiroUserVO.setSessionId(subject.getSession().getId().toString()); return new SimpleAuthenticationInfo(userCode, shiroUser.getPassword(), getName()); } @Override protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principalCollection) { if (StringUtils.isEmpty(principalCollection)) { return null; } String userCode = principalCollection.getPrimaryPrincipal().toString(); ShiroUser shiroUser = shiroService.findUserByCode(userCode); SimpleAuthorizationInfo simpleAuthorizationInfo = new SimpleAuthorizationInfo(); for (ShiroRole shiroRole : shiroUser.getShiroRoleSet()) { simpleAuthorizationInfo.addRole(shiroRole.getRoleCode()); for (ShiroPermission shiroPermission : shiroRole.getShiroPermissionSet()) { simpleAuthorizationInfo.addStringPermission(shiroPermission.getPermissionCode()); } } return simpleAuthorizationInfo; } }禁止未登录跳转因为我们是前后端分离项目，如果未登陆跳转的话，前端无法捕捉重定向后的消息，所以我们需要配置禁止未登录跳转。/** * 对于跨域的POST请求，浏览器发起POST请求前都会发送一个OPTIONS请求已确定服务器是否可用， * OPTIONS请求通过后继续执行POST请求，而shiro自带的权限验证是无法处理OPTIONS请求的， * 所以这里需要重写isAccessAllowed方法 * @author laughing * @date 2020/10/12 * @site https://www.xiangcaowuyu.net */ public class ShiroFormAuthenticationFilter extends FormAuthenticationFilter { private static final Logger log = LoggerFactory.getLogger(ShiroFormAuthenticationFilter.class); /** * 重写是否允许访问 * @param request * @param response * @param mappedValue * @return */ @Override protected boolean isAccessAllowed(ServletRequest request, ServletResponse response, Object mappedValue) { HttpServletRequest httpServletRequest = WebUtils.toHttp(request); if(AuthenConst.OPTION_REQUEST_NAME.equals(httpServletRequest.getMethod())){ return true; } return super.isAccessAllowed(request, response, mappedValue); } @Override protected boolean onAccessDenied(ServletRequest request, ServletResponse response) throws JSONException { PrintWriter out = null; HttpServletResponse res = (HttpServletResponse) response; try { res.setCharacterEncoding("UTF-8"); res.setContentType("application/json"); out = response.getWriter(); out.println("未授权"); } catch (Exception e) { } finally { if (null != out) { out.flush(); out.close(); } } return false; } } shiro配置/** * @author laughing * @date 2020/10/11 * @site https://www.xiangcaowuyu.net */ @Configuration public class ShiroConfig { /** * 自定义验证 * @return */ @Bean MyAuthenRealm myAuthenRealm() { MyAuthenRealm myAuthenRealm = new MyAuthenRealm(); // myAuthenRealm.setCredentialsMatcher(hashedCredentialsMatcher()); return myAuthenRealm; } @Resource ShiroProperty shiroProperty; /** * 权限管理，配置主要是Realm的管理认证 * * @return */ @Bean public SecurityManager securityManager() { DefaultWebSecurityManager defaultWebSecurityManager = new DefaultWebSecurityManager(); defaultWebSecurityManager.setRealm(myAuthenRealm()); // 自定义session管理 使用redis defaultWebSecurityManager.setSessionManager(sessionManager()); return defaultWebSecurityManager; } /** * Filter工厂，设置对应的过滤条件和跳转条件 */ @Bean public ShiroFilterFactoryBean shiroFilterFactoryBean() { ShiroFilterFactoryBean shiroFilterFactoryBean = new ShiroFilterFactoryBean(); shiroFilterFactoryBean.setSecurityManager(securityManager()); Map<String, String> filterChainDefinitionMap = new HashMap<>(); //登出 filterChainDefinitionMap.put(shiroProperty.getUser().getUnauthorizedUrl(), "logout"); //登录 shiroFilterFactoryBean.setLoginUrl(shiroProperty.getUser().getLoginUrl()); //首页 shiroFilterFactoryBean.setSuccessUrl(shiroProperty.getUser().getIndexUrl()); filterChainDefinitionMap.put("/", "anon"); filterChainDefinitionMap.put("/static/**", "anon"); filterChainDefinitionMap.put(shiroProperty.getUser().getLoginUrl(), "anon"); filterChainDefinitionMap.put(shiroProperty.getUser().getUnauthorizedUrl(), "anon"); filterChainDefinitionMap.put("/error", "anon"); filterChainDefinitionMap.put("/**", "authc"); LinkedHashMap<String, Filter> filtsMap = new LinkedHashMap<>(); shiroFilterFactoryBean.setFilters(filtsMap); // 这里使用自定义的filter，禁止未登陆跳转 filtsMap.put("authc", new ShiroFormAuthenticationFilter()); shiroFilterFactoryBean.setFilterChainDefinitionMap(filterChainDefinitionMap); return shiroFilterFactoryBean; } /** * 加入注解的使用，不加入这个注解不生效 * @param securityManager * @return */ @Bean public AuthorizationAttributeSourceAdvisor authorizationAttributeSourceAdvisor(SecurityManager securityManager) { AuthorizationAttributeSourceAdvisor authorizationAttributeSourceAdvisor = new AuthorizationAttributeSourceAdvisor(); authorizationAttributeSourceAdvisor.setSecurityManager(securityManager); return authorizationAttributeSourceAdvisor; } @Bean @ConditionalOnMissingBean public DefaultAdvisorAutoProxyCreator defaultAdvisorAutoProxyCreator() { DefaultAdvisorAutoProxyCreator defaultAdvisorAutoProxyCreator = new DefaultAdvisorAutoProxyCreator(); defaultAdvisorAutoProxyCreator.setProxyTargetClass(true); return defaultAdvisorAutoProxyCreator; } // @Bean // public HashedCredentialsMatcher hashedCredentialsMatcher() { // HashedCredentialsMatcher credentialsMatcher = new HashedCredentialsMatcher(); // //指定加密方式 // credentialsMatcher.setHashAlgorithmName(AuthenConst.HASH_ALGORITHM_NAME); // //加密次数 // credentialsMatcher.setHashIterations(AuthenConst.HASH_INTERACTIONS); // //此处的设置，true加密用的hex编码，false用的base64编码 // credentialsMatcher.setStoredCredentialsHexEncoded(true); // return credentialsMatcher; // } @Bean public SessionManager sessionManager(){ MySessionManager mySessionManager = new MySessionManager(); // 取消登陆跳转URL后面的jsessionid参数 mySessionManager.setSessionIdUrlRewritingEnabled(false); mySessionManager.setSessionDAO(redisSessionDAO()); // 不过期 mySessionManager.setGlobalSessionTimeout(-1); return mySessionManager; } @Bean public RedisManager redisManager(){ RedisManager redisManager = new RedisManager(); redisManager.setHost(shiroProperty.getRedis().getHost()+":"+shiroProperty.getRedis().getPort()); redisManager.setDatabase(0); return redisManager; } @Bean public RedisSessionDAO redisSessionDAO(){ RedisSessionDAO redisSessionDAO = new RedisSessionDAO(); redisSessionDAO.setRedisManager(redisManager()); return redisSessionDAO; } }全局异常@RestControllerAdvice public class MyAuthorizationException { @ExceptionHandler(UnauthorizedException.class) public String authorization(UnauthorizedException myAuthorizationException) { return "没有"+myAuthorizationException.toString()+"权限"; } }测试类/** * @author laughing * @date 2020/10/11 * @site https://www.lisen.org */ @RestController @RequestMapping("/shiro") public class ShiroController { private final Logger logger = LoggerFactory.getLogger(ShiroController.class); @Resource ShiroService shiroService; @RequestMapping("/findUserByUserCode") public ShiroUser findUserByUserCode() { String userCode = "lisen"; ShiroUser shiroUser = shiroService.findUserByCode(userCode); return shiroUser; } @RequestMapping("/login") public String login(@RequestParam("userCode") String userCode, @RequestParam("password") String password) { // password = new SimpleHash(AuthenConst.HASH_ALGORITHM_NAME,password,AuthenConst.SALT,AuthenConst.HASH_INTERACTIONS).toHex(); UsernamePasswordToken usernamePasswordToken = new UsernamePasswordToken(userCode, password); Subject subject = SecurityUtils.getSubject(); try { //进行验证，这里可以捕获异常，然后返回对应信息 subject.login(usernamePasswordToken); } catch (UnknownAccountException e) { logger.error("用户名不存在！", e); return "用户名不存在！"; } catch (AuthenticationException e) { logger.error("账号或密码错误！", e); return "账号或密码错误！"; } catch (AuthorizationException e) { logger.error("没有权限！", e); return "没有权限"; } return "login success"; } @RequestMapping("/index") public String index(String userCode, String password) { return "index"; } @RequiresPermissions("permission") @RequestMapping("/permission") public String permission() { return "permission"; } @RequiresPermissions("dept:add") @RequestMapping("/deptadd") public String deptAdd() { return "dept:add"; } @RequestMapping("/nopermission") public String noPermission() { return "noPermission"; } }表数据因为我们这里还没设计授权的界面，所以临时在数据库插入了测试数据Shiro_UserINSERT INTO `shiro`.`Shiro_User`(`id`, `userCode`, `userName`, `password`) VALUES (1, 'lisen', '李森', 'f5e617c6615d53ae33b9d80a2087e264');Shiro_RoleINSERT INTO `shiro`.`Shiro_Role`(`id`, `roleCode`, `roleName`) VALUES (1, 'admin', '管理员');Shiro_PermissionINSERT INTO `shiro`.`Shiro_Permission`(`id`, `permissionCode`, `permissionName`) VALUES (1, 'dept:add', '部门-增加');Shiro_User_RoleINSERT INTO `shiro`.`Shiro_User_Role`(`id`, `userId`, `roleId`) VALUES (1, 1, 1);Shiro_Role_PermissionINSERT INTO `shiro`.`Shiro_Role_Permission`(`id`, `roleId`, `permissionId`) VALUES (1, 1, 1);
-
超详细Vue+Spring Boot整合Shiro前后端分离架构-Shiro表结构设计（二） 在超详细Vue+Spring Boot整合Shiro前后端分离架构-Shiro介绍（一）一文中，我们介绍了Shiro的基本概念，本章开始，我们进入Shiro的实现环节。Shiro实战shiro实战内容包括三个部分：（1）shiro后台表结构，用于存储shiro对应的用户、角色、权限及关联关系。（2）后端代码，及基于shiro配置用户、角色、权限及对应关系以及登录、认证。（3）前端代码，维护shiro信息及登录、认证。这篇博文，我们讲解第一部分，及Shiro的表结构设计。表结构设计我这里设计了5张表，分别为Shiro_User，Shiro_Role，Shiro_Permission，Shiro_User_Role，Shiro_Role_Permission。Shiro_UserShiro_User用于存储用户信息，主要涉及字段为用户Id（id）、用户名称（username）及用户密码（password）。shiro由提供的密码加密方式，所以，我们数据库存储的密文为调用shiro加密后的用户密码。CREATE TABLE `Shiro_User` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `userCode` varchar(255) NOT NULL, `userName` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `id` (`id`) USING BTREE, UNIQUE KEY `code` (`userCode`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Shiro_RoleShiro_Role用于存储角色，如系统管理员、业务人员等信息。一个用户拥有多个角色，一个角色也可以属于多个用户。CREATE TABLE `Shiro_Role` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `roleCode` varchar(255) NOT NULL, `roleName` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `id` (`id`) USING BTREE, UNIQUE KEY `roleCode` (`roleCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Shiro_PermissionShiro_Permission用于存储权限，一个角色可能有多个权限，一个权限也可能隶属于多个角色。CREATE TABLE `Shiro_Permission` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `permissionCode` varchar(255) NOT NULL, `permissionName` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `id` (`id`) USING BTREE, UNIQUE KEY `permissionCode` (`permissionCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Shiro_User_RoleShiro_User_Role用于存储用户与角色的关联关系。CREATE TABLE `Shiro_User_Role` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `userId` bigint(20) NOT NULL, `roleId` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `userId` (`userId`), KEY `roleId` (`roleId`), CONSTRAINT `shiro_user_role_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Shiro_User` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `shiro_user_role_ibfk_2` FOREIGN KEY (`roleId`) REFERENCES `Shiro_Role` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Shiro_Role_PermissionShiro_Role_Permission存储角色与权限的关联关系。CREATE TABLE `Shiro_Role_Permission` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `roleId` bigint(20) NOT NULL, `permissionId` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `roleId` (`roleId`), KEY `permissionId` (`permissionId`), CONSTRAINT `shiro_role_permission_ibfk_1` FOREIGN KEY (`roleId`) REFERENCES `Shiro_Role` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `shiro_role_permission_ibfk_2` FOREIGN KEY (`permissionId`) REFERENCES `Shiro_Permission` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;至此，数据库表结构已经设计完成。
-
超详细Vue+Spring Boot整合Shiro前后端分离架构-Shiro介绍（一） 其实网上关于spring boot整合shiro的例子并不少见，为什么我这里要重复\`造轮子\`，因为网上所谓的整合，基本上都是以Demo为主，很少能见到Vue+Spring Boot+Shiro完整整合的例子。Shiro概念Apache Shiro是一个强大且易用的Java安全框架,执行身份验证、授权、密码和会话管理。使用Shiro的易于理解的API,您可以快速、轻松地获得任何应用程序,从最小的移动应用程序到最大的网络和企业应用程序。三个核心组件：Subject, SecurityManager 和 Realms.Subject：即“当前操作用户”。但是，在Shiro中，Subject这一概念并不仅仅指人，也可以是第三方进程、后台帐户（Daemon Account）或其他类似事物。它仅仅意味着“当前跟软件交互的东西”。Subject代表了当前用户的安全操作，SecurityManager则管理所有用户的安全操作。SecurityManager：它是Shiro框架的核心，典型的Facade模式，Shiro通过SecurityManager来管理内部组件实例，并通过它来提供安全管理的各种服务。Realm： Realm充当了Shiro与应用安全数据间的“桥梁”或者“连接器”。也就是说，当对用户执行认证（登录）和授权（访问控制）验证时，Shiro会从应用配置的Realm中查找用户及其权限信息。从这个意义上讲，Realm实质上是一个安全相关的DAO：它封装了数据源的连接细节，并在需要时将相关数据提供给Shiro。当配置Shiro时，你必须至少指定一个Realm，用于认证和（或）授权。配置多个Realm是可以的，但是至少需要一个。Shiro内置了可以连接大量安全数据源（又名目录）的Realm，如LDAP、关系数据库（JDBC）、类似INI的文本配置资源以及属性文件等。如果系统默认的Realm不能满足需求，你还可以插入代表自定义数据源的自己的Realm实现。
-
spring boot使用RestTemplate优雅的调用第三方Api 简述RestTemplate是Spring用于同步client端的核心类，简化了与http服务的通信，并满足RestFul原则，程序代码可以给它提供URL，并提取结果。默认情况下，RestTemplate默认依赖jdk的HTTP连接工具。当然你也可以 通过setRequestFactory属性切换到不同的HTTP源，比如Apache HttpComponents、Netty和OkHttp。借助 RestTemplate，Spring应用能够方便地使用REST资源。Spring的 RestTemplate访问使用了模版方法的设计模式。模版方法将过程中与特定实现相关的部分委托给接口,而这个接口的不同实现定义了接口的不同行为。RestTemplate定义了36个与REST资源交互的方法，其中的大多数都对应于HTTP的方法。其实，这里面只有11个独立的方法，其中有十个有三种重载形式，而第十一个则重载了六次，这样一共形成了36个方法。delete()在特定的URL上对资源执行HTTP DELETE操作exchange()在URL上执行特定的HTTP方法，返回包含对象的ResponseEntity，这个对象是从响应体中映射得到的execute()在URL上执行特定的HTTP方法，返回一个从响应体映射得到的对象getForEntity()发送一个HTTP GET请求，返回的ResponseEntity包含了响应体所映射成的对象getForObject()发送一个HTTP GET请求，返回的请求体将映射为一个对象postForEntity()POST 数据到一个URL，返回包含一个对象的ResponseEntity，这个对象是从响应体中映射得到的postForObject()POST 数据到一个URL，返回根据响应体匹配形成的对象headForHeaders()发送HTTP HEAD请求，返回包含特定资源URL的HTTP头optionsForAllow()发送HTTP OPTIONS请求，返回对特定URL的Allow头信息postForLocation()POST 数据到一个URL，返回新创建资源的URLput()PUT 资源到特定的URLRestTemplate使用这里以请求高德地图的geo接口为例进行说明引入依赖RestTemplate位于spring-boot-starter-web中，所以我们只需要引入spring-boot-starter-web依赖即可。<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency>配置RestTemplate我这里直接放到configuration配置类了，可以通过配置文件注入，有不了解的，可以戳 这里/** * @author laughing * @date 2020/10/2 * @site https://www.xiangcaowuyu.net */ @Configuration public class RestTemplateConfig { @Bean public RestTemplate restTemplate(ClientHttpRequestFactory clientHttpRequestFactory) { return new RestTemplate(clientHttpRequestFactory); } @Bean public ClientHttpRequestFactory clientHttpRequestFactory() { SimpleClientHttpRequestFactory simpleClientHttpRequestFactory = new SimpleClientHttpRequestFactory(); simpleClientHttpRequestFactory.setReadTimeout(3600); simpleClientHttpRequestFactory.setConnectTimeout(3600); return simpleClientHttpRequestFactory; } }调用高德geo接口/** * @author laughing * @date 2020/10/2 * @site https://www.xiangcaowuyu.net */ @RestController public class AmapController { private final String url = "https://restapi.amap.com/v3/geocode/geo"; private final String key = "c17ba5b2f9277ed0326ab4fa6019b0cf"; @Resource RestTemplate restTemplate; /** * 地理位置编码 * * @return * @throws JsonProcessingException */ @RequestMapping("geo") public String geo() throws JsonProcessingException { String url = this.url + "?key=" + this.key + "&address=" + "山东省济南市历下区龙奥大厦" + "&output=JSON"; ResponseEntity<String> responseEntity = restTemplate.exchange(url, HttpMethod.GET, null, String.class); return responseEntity.getBody(); } }
-
spring boot使用Redis 什么是RedisRedis（Remote Dictionary Server )，即远程字典服务，是一个开源的使用ANSI C语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库，并提供多种语言的API。从2010年3月15日起，Redis的开发工作由VMware主持。从2013年5月开始，Redis的开发由Pivotal赞助。Redis官网：https://redis.io/spring boot 使用Redis添加依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-redis</artifactId> </dependency>修改application.yaml在配置文件中，增加redis配置信息增加Redis配置类/** * Redis配置 * @author laughing * @date 2020/10/2 * @site https://www.xiangcaowuyu.net */ @Configuration public class RedisConfig { /** * RedisTemplate配置，放置存储数据乱码 * @param redisConnectionFactory * @return */ @Bean public RedisTemplate<String,Object> redisTemplate(RedisConnectionFactory redisConnectionFactory){ RedisTemplate<String,Object> redisTemplate = new RedisTemplate<>(); redisTemplate.setConnectionFactory(redisConnectionFactory); // key采用String的序列化方式 redisTemplate.setKeySerializer(new StringRedisSerializer()); // hash的key也采用String的序列化方式 redisTemplate.setHashKeySerializer(new StringRedisSerializer()); // value序列化方式采用fastjson redisTemplate.setValueSerializer(new GenericJackson2JsonRedisSerializer()); // hash的value序列化方式采用jackson redisTemplate.setHashValueSerializer(new GenericJackson2JsonRedisSerializer()); redisTemplate.afterPropertiesSet(); return redisTemplate; } }编写Redis工具类/** * Redis工具类 * @author laughing * @date 2020/10/2 * @site https://www.xiangcaowuyu.net */ @Component public class RedisUtils { @Resource RedisTemplate<String, Object> redisTemplate; /** * 判断是否存在 * * @return */ public boolean hasKey(String key) { return redisTemplate.hasKey(key); } /** * 获取值 * @param key * @return */ public Object getValue(String key){ return redisTemplate.opsForValue().get(key); } /** * 设置值 * * @param key * @param value */ public void setValue(String key, Object value) { redisTemplate.opsForValue().set(key, value); } /** * 获取过期时间 * * @param key * @return */ public Long getExpire(String key) { return redisTemplate.getExpire(key); } /** * 获取过期时间 * * @param key * @param timeUnit * @return */ public Long getExpire(String key, TimeUnit timeUnit) { return redisTemplate.getExpire(key, timeUnit); } /** * 设置过期时间 * * @param key * @param timeOut * @return */ public Boolean setExpire(String key, Duration timeOut) { return redisTemplate.expire(key, timeOut); } /** * 删除 * * @param key * @return */ public Boolean delete(String key) { return redisTemplate.delete(key); } /** * 删除 * @param keys * @return */ public Long deleteAll(List<String> keys){ return redisTemplate.delete(keys); } }使用这里进行简单的测试。@SpringBootTest class RedisApplicationTests { @Resource RedisUtils redisUtils; @Test void contextLoads() { } @Test void setValue(){ String key = "name"; String value = "香草物语"; redisUtils.setValue(key,value); } }这里只演示一个设置值，通过Another Redis Desktop查看数据库
-
Spring boot整合mybatis多数据源简单使用 日常开发中，我们很少会在一个应用程序中同时使用多个数据源。但是，如果涉及一些数据迁移等应用，可能会涉及将数据从一个库迁移到另外一个库，甚至是不同类型的数据库，比如MySQL到Oracle。这篇博文，我们不介绍mybatis的基本使用，只介绍基于mybatis配置多数据源的方法。数据源准备我这里用了本地MySQL两个库，分别是mybatisone和mybatistwo,mybatisone库中有一张userone表，mybatistwo库中有一张usertwo表，建表sql如下：userone/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatisone Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:29 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for userone -- ---------------------------- DROP TABLE IF EXISTS `userone`; CREATE TABLE `userone` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of userone -- ---------------------------- INSERT INTO `userone` VALUES (1, '姓名1'); SET FOREIGN_KEY_CHECKS = 1; usertwo/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatistwo Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:35 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for usertwo -- ---------------------------- DROP TABLE IF EXISTS `usertwo`; CREATE TABLE `usertwo` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of usertwo -- ---------------------------- INSERT INTO `usertwo` VALUES (1, '姓名2'); SET FOREIGN_KEY_CHECKS = 1; 添加依赖主要依赖如下 <!--mybatis依赖--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--MySQL驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!--druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.14</version> </dependency>application.yaml配置文件yaml文件配置数据源信息以及mybatis的配置信息spring: datasource: mybatisone: url: jdbc:mysql://localhost:3306/mybatisone?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatistwo: url: jdbc:mysql://localhost:3306/mybatistwo?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatis: mapper-locations: classpath*:mapper/*.xml type-aliases-package: net.xiangcaowuyu.simplemybatis.entity配置druid根据yaml文件提供的DataSource，分别配置对应的两个数据源。DataSourceConfig.java/** * datasource配置文件 * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @Configuration public class DataSourceConfig { /** * * @return 第一个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatisone") DataSource dataSourceOne(){ return DruidDataSourceBuilder.create().build(); } /** * * @return 第二个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatistwo") DataSource dataSourceTwo(){ return DruidDataSourceBuilder.create().build(); } }配置mybatis数据源MyBatisConfigOne.java/** * mybatis第一个配置文件 * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @Configuration @MapperScan(basePackages = {"net.xiangcaowuyu.simplemybatis.mapper.one"},sqlSessionFactoryRef = "sqlSessionFactoryOne",sqlSessionTemplateRef = "sqlSessionTemplateOne") public class MyBatisConfigOne { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigOne.class); @Resource(name = "dataSourceOne") DataSource dataSourceOne; @Bean SqlSessionFactory sqlSessionFactoryOne() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceOne); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateOne(){ return new SqlSessionTemplate(sqlSessionFactoryOne()); } }MyBatisConfigTwo.java/** * mybatis第二个配置文件 * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @Configuration @MapperScan(basePackages = {"net.xiangcaowuyu.simplemybatis.mapper.two"},sqlSessionFactoryRef = "sqlSessionFactoryTwo",sqlSessionTemplateRef = "sqlSessionTemplateTwo") public class MyBatisConfigTwo { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigTwo.class); @Resource(name = "dataSourceTwo") DataSource dataSourceTwo; @Bean SqlSessionFactory sqlSessionFactoryTwo() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/two/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceTwo); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateTwo(){ return new SqlSessionTemplate(sqlSessionFactoryTwo()); } }生成mybatis信息mybatis不是我们要讲解的重点，我这里的信息都是通过Free MyBatis插件自动生成的。不做过多介绍。增加实体Userone.java/** * userone * @author */ @Data public class Userone implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加mapperUseroneMapper.java@Mapper public interface UseroneMapper { int deleteByPrimaryKey(Long id); int insert(Userone record); int insertSelective(Userone record); Userone selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Userone record); int updateByPrimaryKey(Userone record); }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加xmlUseroneMapper.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="net.xiangcaowuyu.simplemybatis.mapper.one.UseroneMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.simplemybatis.entity.Userone"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from userone where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from userone where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone"> update userone <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone"> update userone set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>UsertwoMapper.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="net.xiangcaowuyu.simplemybatis.mapper.two.UsertwoMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.simplemybatis.entity.Usertwo"> <id column="id" jdbcType="BIGINT" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from usertwo where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from usertwo where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo"> update usertwo <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo"> update usertwo set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>配置测试Api/** * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @RestController public class MyBatisController { @Resource UseroneMapper useroneMapper; @Resource UsertwoMapper usertwoMapper; @RequestMapping("/one") public Userone userone(){ return useroneMapper.selectByPrimaryKey(1L); } @RequestMapping("/two") public Usertwo usertwo(){ return usertwoMapper.selectByPrimaryKey(1L); } }增加两个Api，分别用于获取数据源1和数据源2的数据。整体代码结构里面封装了全局异常，如果不了解的，可以参考 SpringBoot 之 @ControllerAdvice使用场景代码测试我们在分别打开两个请求，查看获取的数据注意事项在配置数据源时，注意设置mapper的位置，即如下代码：sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml"));如果没有设置，可能会提示Invalid bound statement (not found)
-
spring boot Quartz基于持久化存储的动态管理 精彩回顾我们在spring boot quartz定时任务基本使用及介绍和spring boot quartz持久化存储分别对quartz基本信息及持久化存储进行了介绍。这篇文章我们继续介绍给予持久化存储实现任务的动态管理。创建表结构为了存储我们自己动态创建的任务，除了spring boot quartz持久化存储介绍的添加quartz表结构之外，我们还需要添加一个自己的表。以下是MySQL的表结构，其他类型的数据库请按需修改。/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : quartz Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 30/09/2020 13:34:24 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for task_quartz -- ---------------------------- DROP TABLE IF EXISTS `task_quartz`; CREATE TABLE `task_quartz` ( `id` bigint(0) NOT NULL AUTO_INCREMENT, `job_group` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务分组', `job_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务名', `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务描述', `cron_expression` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'cron表达式', `job_class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务执行时调用哪个类的方法 包名+类名', `job_status` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '任务状态', `create_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '创建者', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', `modify_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '更新者', `modify_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `NameWithGroup`(`job_group`, `job_name`) USING BTREE COMMENT '任务及分组唯一' ) ENGINE = MyISAM AUTO_INCREMENT = 68 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; 创建工程，添加依赖如果要实现任务的动态管理，这里单独借助一张表，存储任务的信息。先介绍一个依赖的情况：MySQL数据库mybatis执行sqlquartz依赖lombok具体项目依赖如下： <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-quartz</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>修改配置文件application.yamlserver: port: 8080 spring: profiles: active: devapplication-dev.yamlspring: datasource: url: jdbc:mysql://localhost:3306/quartz?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&&serverTimezone=UTC username: root password: root type: com.zaxxer.hikari.HikariDataSource quartz: job-store-type: jdbc mybatis: mapper-locations: classpath:mapper/*.xml type-aliases-package: net.xiangcaowuyu.quartztask.entity configuration: map-underscore-to-camel-case: true 主要配置信息：MySQL数据库连接mybatis配置job-store-type存储到数据库增加mybatis相关操作TaskQuartzMapper.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="net.xiangcaowuyu.quartztask.mapper.TaskQuartzMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.quartztask.entity.TaskQuartz"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="job_group" jdbcType="VARCHAR" property="jobGroup"/> <result column="job_name" jdbcType="VARCHAR" property="jobName"/> <result column="description" jdbcType="VARCHAR" property="description"/> <result column="cron_expression" jdbcType="VARCHAR" property="cronExpression"/> <result column="job_class_name" jdbcType="VARCHAR" property="jobClassName"/> <result column="job_status" jdbcType="VARCHAR" property="jobStatus"/> <result column="create_by" jdbcType="VARCHAR" property="createBy"/> <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/> <result column="modify_by" jdbcType="VARCHAR" property="modifyBy"/> <result column="modify_time" jdbcType="TIMESTAMP" property="modifyTime"/> </resultMap> <sql id="Base_Column_List"> id, job_group, job_name, description, cron_expression, job_class_name, job_status, create_by, create_time, modify_by, modify_time </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from task_quartz where id = #{id,jdbcType=BIGINT} </select> <select id="selectByJobNameAndJobGroup" resultType="net.xiangcaowuyu.quartztask.entity.TaskQuartz"> select <include refid="Base_Column_List"/> from task_quartz where job_name = #{jonName,jdbcType=VARCHAR} and job_group = #{jobGroup,jdbcType=VARCHAR} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from task_quartz where id = #{id,jdbcType=BIGINT} </delete> <delete id="deleteByJobNameAndJobGroup"> delete from task_quartz where job_name = #{jonName,jdbcType=VARCHAR} and job_group = #{jobGroup,jdbcType=VARCHAR} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.quartztask.entity.TaskQuartz" useGeneratedKeys="true"> insert into task_quartz (job_group, job_name, description, cron_expression, job_class_name, job_status, create_by, create_time, modify_by, modify_time) values (#{jobGroup,jdbcType=VARCHAR}, #{jobName,jdbcType=VARCHAR}, #{description,jdbcType=VARCHAR}, #{cronExpression,jdbcType=VARCHAR}, #{jobClassName,jdbcType=VARCHAR}, #{jobStatus,jdbcType=VARCHAR}, #{createBy,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{modifyBy,jdbcType=VARCHAR}, #{modifyTime,jdbcType=TIMESTAMP}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.quartztask.entity.TaskQuartz" useGeneratedKeys="true"> insert into task_quartz <trim prefix="(" suffix=")" suffixOverrides=","> <if test="jobGroup != null"> job_group, </if> <if test="jobName != null"> job_name, </if> <if test="description != null"> description, </if> <if test="cronExpression != null"> cron_expression, </if> <if test="jobClassName != null"> job_class_name, </if> <if test="jobStatus != null"> job_status, </if> <if test="createBy != null"> create_by, </if> <if test="createTime != null"> create_time, </if> <if test="modifyBy != null"> modify_by, </if> <if test="modifyTime != null"> modify_time, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="jobGroup != null"> #{jobGroup,jdbcType=VARCHAR}, </if> <if test="jobName != null"> #{jobName,jdbcType=VARCHAR}, </if> <if test="description != null"> #{description,jdbcType=VARCHAR}, </if> <if test="cronExpression != null"> #{cronExpression,jdbcType=VARCHAR}, </if> <if test="jobClassName != null"> #{jobClassName,jdbcType=VARCHAR}, </if> <if test="jobStatus != null"> #{jobStatus,jdbcType=VARCHAR}, </if> <if test="createBy != null"> #{createBy,jdbcType=VARCHAR}, </if> <if test="createTime != null"> #{createTime,jdbcType=TIMESTAMP}, </if> <if test="modifyBy != null"> #{modifyBy,jdbcType=VARCHAR}, </if> <if test="modifyTime != null"> #{modifyTime,jdbcType=TIMESTAMP}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.quartztask.entity.TaskQuartz"> update task_quartz <set> <if test="jobGroup != null"> job_group = #{jobGroup,jdbcType=VARCHAR}, </if> <if test="jobName != null"> job_name = #{jobName,jdbcType=VARCHAR}, </if> <if test="description != null"> description = #{description,jdbcType=VARCHAR}, </if> <if test="cronExpression != null"> cron_expression = #{cronExpression,jdbcType=VARCHAR}, </if> <if test="jobClassName != null"> job_class_name = #{jobClassName,jdbcType=VARCHAR}, </if> <if test="jobStatus != null"> job_status = #{jobStatus,jdbcType=VARCHAR}, </if> <if test="createBy != null"> create_by = #{createBy,jdbcType=VARCHAR}, </if> <if test="createTime != null"> create_time = #{createTime,jdbcType=TIMESTAMP}, </if> <if test="modifyBy != null"> modify_by = #{modifyBy,jdbcType=VARCHAR}, </if> <if test="modifyTime != null"> modify_time = #{modifyTime,jdbcType=TIMESTAMP}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.quartztask.entity.TaskQuartz"> update task_quartz set job_group = #{jobGroup,jdbcType=VARCHAR}, job_name = #{jobName,jdbcType=VARCHAR}, description = #{description,jdbcType=VARCHAR}, cron_expression = #{cronExpression,jdbcType=VARCHAR}, job_class_name = #{jobClassName,jdbcType=VARCHAR}, job_status = #{jobStatus,jdbcType=VARCHAR}, create_by = #{createBy,jdbcType=VARCHAR}, create_time = #{createTime,jdbcType=TIMESTAMP}, modify_by = #{modifyBy,jdbcType=VARCHAR}, modify_time = #{modifyTime,jdbcType=TIMESTAMP} where id = #{id,jdbcType=BIGINT} </update> </mapper>TaskQuartz.java/** * @author laughing * @date 2020/9/30 * @site https://www.lisen.org */ @Data public class TaskQuartz implements Serializable { private Long id; /** * 任务分组 */ private String jobGroup; /** * 任务名 */ private String jobName; /** * 任务描述 */ private String description; /** * cron表达式 */ private String cronExpression; /** * 任务执行时调用哪个类的方法 包名+类名 */ private String jobClassName; /** * 任务状态 */ private String jobStatus; /** * 创建者 */ private String createBy; /** * 创建时间 */ private Date createTime; /** * 更新者 */ private String modifyBy; /** * 更新时间 */ private Date modifyTime; private static final long serialVersionUID = 1L; }TaskQuartzService.java/** * @author laughing * @date 2020/9/30 * @site https://www.lisen.org */ public interface TaskQuartzService { /** * 根据主键删除 * @param id 主键 * @return 删除行数 */ int deleteByPrimaryKey(Long id); /** * 插入 * @param record 实体 * @return 成功返回1 */ int insert(TaskQuartz record); /** * 插入修改的值 * @param record 实体 * @return 成功返回1 */ int insertSelective(TaskQuartz record); /** * 根据主键获取 * @param id 主键 * @return 实体 */ TaskQuartz selectByPrimaryKey(Long id); /** * 更新 * @param record 实体 * @return 更新成功返回1 */ int updateByPrimaryKeySelective(TaskQuartz record); /** * 更新所有值 * @param record 实体 * @return 成功返回1 */ int updateByPrimaryKey(TaskQuartz record); /** * 根据名称及分组查找 * @param jobName 任务名称 * @param jobGroup 任务分组 * @return 任务 */ TaskQuartz selectByJobNameAndJobGroup(@Param("jonName") String jobName, @Param("jobGroup") String jobGroup); /** * 根据名称及分组查找 * @param jobName 任务名称 * @param jobGroup 任务分组 * @return 任务 */ int deleteByJobNameAndJobGroup(@Param("jonName") String jobName,@Param("jobGroup") String jobGroup); }TaskQuartzServiceImpl.java/** * @author laughing * @date 2020/9/30 * @site https://www.lisen.org */ @Service public class TaskQuartzServiceImpl implements TaskQuartzService { @Resource TaskQuartzMapper taskQuartzMapper; /** * 根据主键删除 * * @param id 主键 * @return 删除行数 */ @Override public int deleteByPrimaryKey(Long id) { return taskQuartzMapper.deleteByPrimaryKey(id); } /** * 插入 * * @param record 实体 * @return 成功返回1 */ @Override public int insert(TaskQuartz record) { return taskQuartzMapper.insert(record); } /** * 插入修改的值 * * @param record 实体 * @return 成功返回1 */ @Override public int insertSelective(TaskQuartz record) { return taskQuartzMapper.insertSelective(record); } /** * 根据主键获取 * * @param id 主键 * @return 实体 */ @Override public TaskQuartz selectByPrimaryKey(Long id) { return taskQuartzMapper.selectByPrimaryKey(id); } /** * 更新 * * @param record 实体 * @return 更新成功返回1 */ @Override public int updateByPrimaryKeySelective(TaskQuartz record) { return taskQuartzMapper.updateByPrimaryKeySelective(record); } /** * 更新所有值 * * @param record 实体 * @return 成功返回1 */ @Override public int updateByPrimaryKey(TaskQuartz record) { return taskQuartzMapper.updateByPrimaryKey(record); } /** * 根据名称及分组查找 * * @param jobName 任务名称 * @param jobGroup 任务分组 * @return 任务 */ @Override public TaskQuartz selectByJobNameAndJobGroup(String jobName, String jobGroup) { return taskQuartzMapper.selectByJobNameAndJobGroup(jobName,jobGroup); } /** * 根据名称及分组查找 * * @param jobName 任务名称 * @param jobGroup 任务分组 * @return 任务 */ @Override public int deleteByJobNameAndJobGroup(String jobName, String jobGroup) { return taskQuartzMapper.deleteByJobNameAndJobGroup(jobName,jobGroup); } } 增加测试任务PrintJob.java/** * @author laughing * @date 2020/9/30 * @site https://www.lisen.org */ public class PrintJob implements Job { private final Logger logger = LoggerFactory.getLogger(PrintJob.class); /** * 执行任务 * * @param jobExecutionContext * @throws JobExecutionException */ @Override public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException { try { logger.info("Hello Job执行时间: " + new Date() + " Blog:" + jobExecutionContext.getJobDetail().getJobDataMap().get("blog")); Thread.sleep(1000 * 5); System.out.println("================执行完成========================"); } catch (Exception e) { e.printStackTrace(); } } }测试新增任务新增任务时，我们并没有创建quartz相关表，只是存储到task_quartz表中，任务启动时，插入quartz表 /** * 新增或者保存任务 * 任务只是存储到task_quartz表中，任务启动时，插入quartz表 * * @param taskQuartz 实体 * @return 结果 */ @RequestMapping("saveQuartz") public Result save(@RequestBody TaskQuartz taskQuartz) { TaskQuartz task = taskQuartzService.selectByJobNameAndJobGroup(taskQuartz.getJobName(), taskQuartz.getJobGroup()); if (task == null) { task = new TaskQuartz(); BeanUtils.copyProperties(taskQuartz, task); taskQuartzService.insertSelective(task); } else { taskQuartzService.updateByPrimaryKeySelective(taskQuartz); } return Result.ok(); }测试启动任务任务启动时，启动任务时，将任务信息持久化到quartz表中/** * 启用任务 * 启动任务时，将任务信息持久化到quartz表中 * * @param taskQuartz 实体 * @return 结果 */ @RequestMapping("startJob") public Result startJob(@RequestBody TaskQuartz taskQuartz) { try { JobKey jobKey = getJobKeyByTaskQuartz(taskQuartz); // 存在先删除 if (scheduler.checkExists(jobKey)) { scheduler.deleteJob(jobKey); } Class classz = Class.forName(taskQuartz.getJobClassName()); JobDetail jobDetail = JobBuilder.newJob() .withDescription(taskQuartz.getDescription()) .withIdentity(jobKey) .usingJobData("blog", "https://www.xiangcaowuyu.net") .ofType(classz).build(); Trigger trigger = TriggerBuilder.newTrigger() .withIdentity("Trigger_" + taskQuartz.getJobName(), "TriggerGroup_" + taskQuartz.getJobGroup()) .withSchedule(CronScheduleBuilder.cronSchedule(taskQuartz.getCronExpression())) .startNow() .build(); scheduler.scheduleJob(jobDetail, trigger); } catch (Exception exception) { return Result.error(exception.getMessage()); } return Result.ok(); }通过postman测试，我们可以观察控制台，任务时10s执行一次暂停任务/** * 暂停任务 * * @param taskQuartz 实体 * @return 结果 */ @RequestMapping("shutdown") public Result shutdown(@RequestBody TaskQuartz taskQuartz) { try { JobKey jobKey = getJobKeyByTaskQuartz(taskQuartz); scheduler.pauseJob(jobKey); return Result.ok(); } catch (Exception ex) { return Result.error(ex.getMessage()); } }恢复任务 /** * 恢复任务 * * @param taskQuartz 实体 * @return 结果 */ @RequestMapping("resumeJob") public Result resumeJob(@RequestBody TaskQuartz taskQuartz) { try { JobKey jobKey = getJobKeyByTaskQuartz(taskQuartz); scheduler.resumeJob(jobKey); return Result.ok(); } catch (Exception ex) { return Result.error(ex.getMessage()); } }删除任务/** * 删除任务 * * @param taskQuartz 实体 * @return 结果 */ @RequestMapping("removeJob") public Result removeJob(@RequestBody TaskQuartz taskQuartz) { try { JobKey jobKey = getJobKeyByTaskQuartz(taskQuartz); scheduler.deleteJob(jobKey); taskQuartzService.deleteByJobNameAndJobGroup(taskQuartz.getJobName(),taskQuartz.getJobGroup()); return Result.ok(); } catch (Exception ex) { return Result.error(ex.getMessage()); } }
-
spring boot quartz持久化存储 在spring boot quartz定时任务基本使用及介绍的博文中，我们简单介绍了quartz的基础概念及简单的使用，细心的童鞋可以发现，那个demo虽然能用，但是存在一个问题：一旦应用停止，计划任务变失效了。如何解决应用停止或者重启不会丢失计划任务信息，便是我们这篇博文要讨论的问题。Quartz提供两种基本作业存储类型：RAMJobStore ：RAM也就是内存，默认情况下Quartz会将任务调度存在内存中，这种方式性能是最好的，因为内存的速度是最快的。不好的地方就是数据缺乏持久性，但程序崩溃或者重新发布的时候，所有运行信息都会丢失。JDBC作业存储：存到数据库之后，可以做单点也可以做集群，当任务多了之后，可以统一进行管理。关闭或者重启服务器，运行的信息都不会丢失。缺点就是运行速度快慢取决于连接数据库的快慢。Quartz初始化表MySQL# # Quartz seems to work best with the driver mm.mysql-2.0.7-bin.jar # # PLEASE consider using mysql with innodb tables to avoid locking issues # # In your Quartz properties file, you'll need to set # org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate # DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS; DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS; DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE; DROP TABLE IF EXISTS QRTZ_LOCKS; DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS; DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS; DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS; DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS; DROP TABLE IF EXISTS QRTZ_TRIGGERS; DROP TABLE IF EXISTS QRTZ_JOB_DETAILS; DROP TABLE IF EXISTS QRTZ_CALENDARS; 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) ); 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) ); 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) ); 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) ); 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) ); 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) ); 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) ); CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP) ); 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) ); 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) ); CREATE TABLE QRTZ_LOCKS ( SCHED_NAME VARCHAR(120) NOT NULL, LOCK_NAME VARCHAR(40) NOT NULL, PRIMARY KEY (SCHED_NAME,LOCK_NAME) ); commit; Oracle-- -- A hint submitted by a user: Oracle DB MUST be created as "shared" and the -- job_queue_processes parameter must be greater than 2 -- However, these settings are pretty much standard after any -- Oracle install, so most users need not worry about this. -- -- Many other users (including the primary author of Quartz) have had success -- runing in dedicated mode, so only consider the above as a hint ;-) -- delete from qrtz_fired_triggers; delete from qrtz_simple_triggers; delete from qrtz_simprop_triggers; delete from qrtz_cron_triggers; delete from qrtz_blob_triggers; delete from qrtz_triggers; delete from qrtz_job_details; delete from qrtz_calendars; delete from qrtz_paused_trigger_grps; delete from qrtz_locks; delete from qrtz_scheduler_state; drop table qrtz_calendars; drop table qrtz_fired_triggers; drop table qrtz_blob_triggers; drop table qrtz_cron_triggers; drop table qrtz_simple_triggers; drop table qrtz_simprop_triggers; drop table qrtz_triggers; drop table qrtz_job_details; drop table qrtz_paused_trigger_grps; drop table qrtz_locks; drop table qrtz_scheduler_state; CREATE TABLE qrtz_job_details ( SCHED_NAME VARCHAR2(120) NOT NULL, JOB_NAME VARCHAR2(200) NOT NULL, JOB_GROUP VARCHAR2(200) NOT NULL, DESCRIPTION VARCHAR2(250) NULL, JOB_CLASS_NAME VARCHAR2(250) NOT NULL, IS_DURABLE VARCHAR2(1) NOT NULL, IS_NONCONCURRENT VARCHAR2(1) NOT NULL, IS_UPDATE_DATA VARCHAR2(1) NOT NULL, REQUESTS_RECOVERY VARCHAR2(1) NOT NULL, JOB_DATA BLOB NULL, CONSTRAINT QRTZ_JOB_DETAILS_PK PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) ); CREATE TABLE qrtz_triggers ( SCHED_NAME VARCHAR2(120) NOT NULL, TRIGGER_NAME VARCHAR2(200) NOT NULL, TRIGGER_GROUP VARCHAR2(200) NOT NULL, JOB_NAME VARCHAR2(200) NOT NULL, JOB_GROUP VARCHAR2(200) NOT NULL, DESCRIPTION VARCHAR2(250) NULL, NEXT_FIRE_TIME NUMBER(13) NULL, PREV_FIRE_TIME NUMBER(13) NULL, PRIORITY NUMBER(13) NULL, TRIGGER_STATE VARCHAR2(16) NOT NULL, TRIGGER_TYPE VARCHAR2(8) NOT NULL, START_TIME NUMBER(13) NOT NULL, END_TIME NUMBER(13) NULL, CALENDAR_NAME VARCHAR2(200) NULL, MISFIRE_INSTR NUMBER(2) NULL, JOB_DATA BLOB NULL, CONSTRAINT QRTZ_TRIGGERS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT QRTZ_TRIGGER_TO_JOBS_FK FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP) ); CREATE TABLE qrtz_simple_triggers ( SCHED_NAME VARCHAR2(120) NOT NULL, TRIGGER_NAME VARCHAR2(200) NOT NULL, TRIGGER_GROUP VARCHAR2(200) NOT NULL, REPEAT_COUNT NUMBER(7) NOT NULL, REPEAT_INTERVAL NUMBER(12) NOT NULL, TIMES_TRIGGERED NUMBER(10) NOT NULL, CONSTRAINT QRTZ_SIMPLE_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT QRTZ_SIMPLE_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_cron_triggers ( SCHED_NAME VARCHAR2(120) NOT NULL, TRIGGER_NAME VARCHAR2(200) NOT NULL, TRIGGER_GROUP VARCHAR2(200) NOT NULL, CRON_EXPRESSION VARCHAR2(120) NOT NULL, TIME_ZONE_ID VARCHAR2(80), CONSTRAINT QRTZ_CRON_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT QRTZ_CRON_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_simprop_triggers ( SCHED_NAME VARCHAR2(120) NOT NULL, TRIGGER_NAME VARCHAR2(200) NOT NULL, TRIGGER_GROUP VARCHAR2(200) NOT NULL, STR_PROP_1 VARCHAR2(512) NULL, STR_PROP_2 VARCHAR2(512) NULL, STR_PROP_3 VARCHAR2(512) NULL, INT_PROP_1 NUMBER(10) NULL, INT_PROP_2 NUMBER(10) NULL, LONG_PROP_1 NUMBER(13) NULL, LONG_PROP_2 NUMBER(13) NULL, DEC_PROP_1 NUMERIC(13,4) NULL, DEC_PROP_2 NUMERIC(13,4) NULL, BOOL_PROP_1 VARCHAR2(1) NULL, BOOL_PROP_2 VARCHAR2(1) NULL, CONSTRAINT QRTZ_SIMPROP_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT QRTZ_SIMPROP_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_blob_triggers ( SCHED_NAME VARCHAR2(120) NOT NULL, TRIGGER_NAME VARCHAR2(200) NOT NULL, TRIGGER_GROUP VARCHAR2(200) NOT NULL, BLOB_DATA BLOB NULL, CONSTRAINT QRTZ_BLOB_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT QRTZ_BLOB_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_calendars ( SCHED_NAME VARCHAR2(120) NOT NULL, CALENDAR_NAME VARCHAR2(200) NOT NULL, CALENDAR BLOB NOT NULL, CONSTRAINT QRTZ_CALENDARS_PK PRIMARY KEY (SCHED_NAME,CALENDAR_NAME) ); CREATE TABLE qrtz_paused_trigger_grps ( SCHED_NAME VARCHAR2(120) NOT NULL, TRIGGER_GROUP VARCHAR2(200) NOT NULL, CONSTRAINT QRTZ_PAUSED_TRIG_GRPS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_fired_triggers ( SCHED_NAME VARCHAR2(120) NOT NULL, ENTRY_ID VARCHAR2(95) NOT NULL, TRIGGER_NAME VARCHAR2(200) NOT NULL, TRIGGER_GROUP VARCHAR2(200) NOT NULL, INSTANCE_NAME VARCHAR2(200) NOT NULL, FIRED_TIME NUMBER(13) NOT NULL, SCHED_TIME NUMBER(13) NOT NULL, PRIORITY NUMBER(13) NOT NULL, STATE VARCHAR2(16) NOT NULL, JOB_NAME VARCHAR2(200) NULL, JOB_GROUP VARCHAR2(200) NULL, IS_NONCONCURRENT VARCHAR2(1) NULL, REQUESTS_RECOVERY VARCHAR2(1) NULL, CONSTRAINT QRTZ_FIRED_TRIGGER_PK PRIMARY KEY (SCHED_NAME,ENTRY_ID) ); CREATE TABLE qrtz_scheduler_state ( SCHED_NAME VARCHAR2(120) NOT NULL, INSTANCE_NAME VARCHAR2(200) NOT NULL, LAST_CHECKIN_TIME NUMBER(13) NOT NULL, CHECKIN_INTERVAL NUMBER(13) NOT NULL, CONSTRAINT QRTZ_SCHEDULER_STATE_PK PRIMARY KEY (SCHED_NAME,INSTANCE_NAME) ); CREATE TABLE qrtz_locks ( SCHED_NAME VARCHAR2(120) NOT NULL, LOCK_NAME VARCHAR2(40) NOT NULL, CONSTRAINT QRTZ_LOCKS_PK PRIMARY KEY (SCHED_NAME,LOCK_NAME) ); create index idx_qrtz_j_req_recovery on qrtz_job_details(SCHED_NAME,REQUESTS_RECOVERY); create index idx_qrtz_j_grp on qrtz_job_details(SCHED_NAME,JOB_GROUP); create index idx_qrtz_t_j on qrtz_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP); create index idx_qrtz_t_jg on qrtz_triggers(SCHED_NAME,JOB_GROUP); create index idx_qrtz_t_c on qrtz_triggers(SCHED_NAME,CALENDAR_NAME); create index idx_qrtz_t_g on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP); create index idx_qrtz_t_state on qrtz_triggers(SCHED_NAME,TRIGGER_STATE); create index idx_qrtz_t_n_state on qrtz_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE); create index idx_qrtz_t_n_g_state on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE); create index idx_qrtz_t_next_fire_time on qrtz_triggers(SCHED_NAME,NEXT_FIRE_TIME); create index idx_qrtz_t_nft_st on qrtz_triggers(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME); create index idx_qrtz_t_nft_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME); create index idx_qrtz_t_nft_st_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE); create index idx_qrtz_t_nft_st_misfire_grp on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE); create index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME); create index idx_qrtz_ft_inst_job_req_rcvry on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY); create index idx_qrtz_ft_j_g on qrtz_fired_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP); create index idx_qrtz_ft_jg on qrtz_fired_triggers(SCHED_NAME,JOB_GROUP); create index idx_qrtz_ft_t_g on qrtz_fired_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP); create index idx_qrtz_ft_tg on qrtz_fired_triggers(SCHED_NAME,TRIGGER_GROUP); postgresql-- Thanks to Patrick Lightbody for submitting this... -- -- In your Quartz properties file, you'll need to set -- org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate DROP TABLE IF EXISTS qrtz_fired_trigger`s; DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS; DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE; DROP TABLE IF EXISTS QRTZ_LOCKS; DROP TABLE IF EXISTS qrtz_simple_triggers; DROP TABLE IF EXISTS qrtz_cron_triggers; DROP TABLE IF EXISTS qrtz_simprop_triggers; DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS; DROP TABLE IF EXISTS qrtz_triggers; DROP TABLE IF EXISTS qrtz_job_details; DROP TABLE IF EXISTS qrtz_calendars; 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 BOOL NOT NULL, IS_NONCONCURRENT BOOL NOT NULL, IS_UPDATE_DATA BOOL NOT NULL, REQUESTS_RECOVERY BOOL NOT NULL, JOB_DATA BYTEA NULL, PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) ); 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 NULL, PREV_FIRE_TIME BIGINT NULL, PRIORITY INTEGER NULL, TRIGGER_STATE VARCHAR(16) NOT NULL, TRIGGER_TYPE VARCHAR(8) NOT NULL, START_TIME BIGINT NOT NULL, END_TIME BIGINT NULL, CALENDAR_NAME VARCHAR(200) NULL, MISFIRE_INSTR SMALLINT NULL, JOB_DATA BYTEA 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) ); 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 NOT NULL, REPEAT_INTERVAL BIGINT NOT NULL, TIMES_TRIGGERED BIGINT 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) ); 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(120) 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) ); 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 BOOL NULL, BOOL_PROP_2 BOOL 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) ); 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 BYTEA 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) ); CREATE TABLE qrtz_calendars ( SCHED_NAME VARCHAR(120) NOT NULL, CALENDAR_NAME VARCHAR(200) NOT NULL, CALENDAR BYTEA NOT NULL, PRIMARY KEY (SCHED_NAME,CALENDAR_NAME) ); CREATE TABLE qrtz_paused_trigger_grps ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP) ); 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 NOT NULL, SCHED_TIME BIGINT NOT NULL, PRIORITY INTEGER NOT NULL, STATE VARCHAR(16) NOT NULL, JOB_NAME VARCHAR(200) NULL, JOB_GROUP VARCHAR(200) NULL, IS_NONCONCURRENT BOOL NULL, REQUESTS_RECOVERY BOOL NULL, PRIMARY KEY (SCHED_NAME,ENTRY_ID) ); CREATE TABLE qrtz_scheduler_state ( SCHED_NAME VARCHAR(120) NOT NULL, INSTANCE_NAME VARCHAR(200) NOT NULL, LAST_CHECKIN_TIME BIGINT NOT NULL, CHECKIN_INTERVAL BIGINT NOT NULL, PRIMARY KEY (SCHED_NAME,INSTANCE_NAME) ); CREATE TABLE qrtz_locks ( SCHED_NAME VARCHAR(120) NOT NULL, LOCK_NAME VARCHAR(40) NOT NULL, PRIMARY KEY (SCHED_NAME,LOCK_NAME) ); create index idx_qrtz_j_req_recovery on qrtz_job_details(SCHED_NAME,REQUESTS_RECOVERY); create index idx_qrtz_j_grp on qrtz_job_details(SCHED_NAME,JOB_GROUP); create index idx_qrtz_t_j on qrtz_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP); create index idx_qrtz_t_jg on qrtz_triggers(SCHED_NAME,JOB_GROUP); create index idx_qrtz_t_c on qrtz_triggers(SCHED_NAME,CALENDAR_NAME); create index idx_qrtz_t_g on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP); create index idx_qrtz_t_state on qrtz_triggers(SCHED_NAME,TRIGGER_STATE); create index idx_qrtz_t_n_state on qrtz_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE); create index idx_qrtz_t_n_g_state on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE); create index idx_qrtz_t_next_fire_time on qrtz_triggers(SCHED_NAME,NEXT_FIRE_TIME); create index idx_qrtz_t_nft_st on qrtz_triggers(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME); create index idx_qrtz_t_nft_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME); create index idx_qrtz_t_nft_st_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE); create index idx_qrtz_t_nft_st_misfire_grp on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE); create index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME); create index idx_qrtz_ft_inst_job_req_rcvry on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY); create index idx_qrtz_ft_j_g on qrtz_fired_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP); create index idx_qrtz_ft_jg on qrtz_fired_triggers(SCHED_NAME,JOB_GROUP); create index idx_qrtz_ft_t_g on qrtz_fired_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP); create index idx_qrtz_ft_tg on qrtz_fired_triggers(SCHED_NAME,TRIGGER_GROUP); commit; 其余的配置文件，大家可以去官网下载配置依赖<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-quartz</artifactId> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency>配置文件quartz.properties###################jdbcJobStore############################ org.quartz.scheduler.instanceName=MyScheduler org.quartz.scheduler.instanceId=AUTO org.quartz.threadPool.threadCount=3 # 所有的quartz数据例如job和Trigger的细节信息被保存在内存或数据库中,有两种实现：JobStoreTX(自己管理事务) #和JobStoreCMT（application server管理事务，即全局事务JTA） org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX # 类似于Hibernate的dialect，用于处理DB之间的差异，StdJDBCDelegate能满足大部分的DB org.quartz.jobStore.driverDelegateClass =org.quartz.impl.jdbcjobstore.StdJDBCDelegate #数据库表的前缀 org.quartz.jobStore.tablePrefix=QRTZ_ #配置数据源的名称 org.quartz.jobStore.dataSource=myDS #为了指示JDBCJobStore所有的JobDataMaps中的值都是字符串，并且能以“名字-值”对的方式存储而不是以复杂对象的序列化形式存储在BLOB字段中，应该设置为true(缺省方式) org.quartz.jobStore.useProperties = true # 检入到数据库中的频率(毫秒)。检查是否其他的实例到了应当检入的时候未检入这能指出一个失败的实例， #且当前Scheduler会以此来接管执行失败并可恢复的Job通过检入操作，Scheduler也会更新自身的状态记录 org.quartz.jobStore.clusterCheckinInterval=20000 # 是否集群、负载均衡、容错，如果应用在集群中设置为false会出错 org.quartz.jobStore.isClustered=false #misfire时间设置 org.quartz.jobStore.misfireThreshold=60000 # 连接超时重试连接的间隔。使用 RamJobStore时，该参数并没什么用【默认15000】【非必须】 #org.quartz.scheduler.dbFailureRetryInterval = 15000 #下面是数据库链接相关的配置 org.quartz.dataSource.myDS.driver=com.mysql.jdbc.Driver org.quartz.dataSource.myDS.URL=jdbc:mysql://localhost:3306/quartz?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=UTC org.quartz.dataSource.myDS.user=root org.quartz.dataSource.myDS.password=root org.quartz.dataSource.myDS.maxConnections=5 使用定义Job类package org.lisen.quartzjdbc.scheduler; import org.quartz.Job; import org.quartz.JobExecutionContext; import org.quartz.JobExecutionException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.text.SimpleDateFormat; import java.util.Date; /** * @author laughing * @date 2020/9/29 * @site https://www.lisen.org */ public class PrintService implements Job { private final Logger logger = LoggerFactory.getLogger(PrintService.class); /** * 执行 * @param jobExecutionContext * @throws JobExecutionException */ @Override public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException { logger.info(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())); } } 创建计划任务/** * @author laughing * @date 2020/9/29 * @site https://www.lisen.org */ @RestController public class QuartzController { @RequestMapping("/quartzJdbc") public String quartzJdbc() throws SchedulerException { Scheduler scheduler = StdSchedulerFactory.getDefaultScheduler(); Trigger trigger = TriggerBuilder.newTrigger() .withIdentity("trigger","triGroup") .startNow() .withSchedule(SimpleScheduleBuilder.simpleSchedule() .withIntervalInSeconds(1) .withRepeatCount(0)) .build(); JobDetail job = JobBuilder.newJob(PrintService.class) .usingJobData("JobKey","JobValue") .withIdentity("Job","JobGroup") .withDescription("打印任务") .build(); scheduler.scheduleJob(job, trigger); scheduler.pauseAll(); return "success"; } }
-
spring boot quartz定时任务基本使用及介绍 什么是QuartzQuartz是一个完全由java编写的开源作业调度框架，由OpenSymphony组织开源出来。所谓作业调度其实就是按照程序的设定，某一时刻或者时间间隔去执行某个代码。最常用的就是报表的制作了。Quartz基本使用添加依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-quartz</artifactId> </dependency>创建Job/** * @author laughing * @date 2020/9/29 * @site https://www.lisen.org */ public class PrintJobService implements Job { private final Logger logger = LoggerFactory.getLogger(PrintJobService.class); /** * 任务执行 * * @param jobExecutionContext 任务执行信息 */ @Override public void execute(JobExecutionContext jobExecutionContext) { logger.info("准备获取scheduler参数"); try { String schedulerValue = jobExecutionContext.getScheduler().getContext().getString("sKey"); logger.info(schedulerValue); } catch (SchedulerException e) { e.printStackTrace(); } logger.info("完成获取scheduler参数\n"); logger.info("准备获取TriggerData"); JobDataMap triggerDataMap = jobExecutionContext.getTrigger().getJobDataMap(); for (String triggerKey : triggerDataMap.getKeys()) { logger.info(triggerDataMap.getString(triggerKey)); } logger.info("完成获取TriggerData\n"); logger.info("准备获取JobData"); JobDataMap jobDataMap = jobExecutionContext.getJobDetail().getJobDataMap(); for (String triggerKey : jobDataMap.getKeys()) { logger.info(jobDataMap.getString(triggerKey)); } logger.info("完成获取JobData"); } }使用/** * @author laughing * @date 2020/9/29 * @site https://www.lisen.org */ @RestController public class QuartzController { @RequestMapping("/quartz") public String quartz() throws SchedulerException { //创建一个scheduler Scheduler scheduler = StdSchedulerFactory.getDefaultScheduler(); //通过scheduler传参数 scheduler.getContext().put("sKey", "sValue"); //创建一个Trigger Trigger trigger = TriggerBuilder.newTrigger() .withIdentity("trigger1", "group1") .usingJobData("t1", "tv1") //3秒执行一次 .withSchedule(SimpleScheduleBuilder.simpleSchedule().withIntervalInSeconds(3) //执行1次 .withRepeatCount(0)) .build(); trigger.getJobDataMap().put("t2", "tv2"); //创建一个job JobDetail job = JobBuilder.newJob(PrintJobService.class) .usingJobData("j1", "jv1") .withIdentity("myJob", "myGroup").build(); job.getJobDataMap().put("j2", "jv2"); //注册trigger并启动scheduler scheduler.scheduleJob(job, trigger); scheduler.start(); return "success"; } }Quartz的基本组成一、调度器（Scheduler)调度器用来管理触发器和作业。Trigger和JobDetail可以注册到Scheduler中，两者在Scheduler中都拥有自己的唯一的组和名称用来进行彼此的区分，Scheduler可以通过组名或者名称来对Trigger和JobDetail来进行管理。一个Trigger只能对应一个Job，但是一个Job可以对应多个Trigger。每个Scheduler都包含一个SchedulerContext，用来保存Scheduler的上下文。Job和Trigger都可以获取SchedulerContext中的信息。Scheduler包含两个重要的组件，JobStore和ThreadPool。JobStore用来存储运行时信息，包括Trigger,Schduler,JobDetail，业务锁等。它有多种实现RAMJob(内存实现)，JobStoreTX(JDBC，事务由Quartz管理）等。ThreadPool就是线程池，Quartz有自己的线程池实现。所有任务的都会由线程池执行。Scheduler是由SchdulerFactory创建，它有两个实现：DirectSchedulerFactory和StdSchdulerFactory。前者可以用来在代码里定制你自己的Schduler参数。后者是直接读取classpath下的quartz.properties（不存在就都使用默认值）配置来实例化Schduler。通常来讲，我们使用StdSchdulerFactory也就足够了二、触发器（Trigger）Trigger是用来定义Job的执行规则，直白的说就是什么时间、重复多少次。主要有四种触发器，其中SimpleTrigger和CronTrigger触发器用的最多。SimpleTrigger从某一个时间开始，以一定的时间间隔来执行任务。它主要有两个属性，repeatInterval 重复的时间间隔；repeatCount重复的次数，实际上执行的次数是n+1,因为在startTime的时候会执行一次。CronTrigger适合于复杂的任务，使用cron表达式来定义执行规则。CalendarIntervalTrigger类似于SimpleTrigger，指定从某一个时间开始，以一定的时间间隔执行的任务。 但是CalendarIntervalTrigger执行任务的时间间隔比SimpleTrigger要丰富，它支持的间隔单位有秒，分钟，小时，天，月，年，星期。相较于SimpleTrigger有两个优势：1、更方便，比如每隔1小时执行，你不用自己去计算1小时等于多少毫秒。 2、支持不是固定长度的间隔，比如间隔为月和年。但劣势是精度只能到秒。它的主要两个属性，interval 执行间隔；intervalUnit 执行间隔的单位（秒，分钟，小时，天，月，年，星期）DailyTimeIntervalTrigger指定每天的某个时间段内，以一定的时间间隔执行任务。并且它可以支持指定星期。它适合的任务类似于：指定每天9:00 至 18:00 ，每隔70秒执行一次，并且只要周一至周五执行。它的属性有startTimeOfDay 每天开始时间；endTimeOfDay 每天结束时间；daysOfWeek 需要执行的星期；interval 执行间隔；intervalUnit 执行间隔的单位（秒，分钟，小时，天，月，年，星期）；repeatCount 重复次数所有的trigger都包含了StartTime和endTIme这两个属性，用来指定Trigger被触发的时间区间。所有的trigger都可以设置MisFire策略，该策略是对于由于系统奔溃或者任务时间过长等原因导致trigger在应该触发的时间点没有触发，并且超过了misfireThreshold设置的时间（默认是一分钟，没有超过就立即执行）就算misfire了，这个时候就该设置如何应对这种变化了。激活失败指令（Misfire Instructions）是触发器的一个重要属性，它指定了misfire发生时调度器应当如何处理。所有类型的触发器都有一个默认的指令，叫做Trigger.MISFIRE_INSTRUCTION_SMART_POLICY，但是这个这个“聪明策略”对于不同类型的触发器其具体行为是不同的。对于SimpleTrigger，这个“聪明策略”将根据触发器实例的状态和配置来决定其行 为。具体如下：如果Repeat Count=0：只执行一次instruction selected = MISFIRE_INSTRUCTION_FIRE_NOW;如果Repeat Count=REPEAT_INDEFINITELY：无限次执行instruction selected = MISFIRE_INSTRUCTION_RESCHEDULE_NEXT_WITH_REMAINING_COUNT;如果Repeat Count>0： 执行多次（有限）instruction selected = MISFIRE_INSTRUCTION_RESCHEDULE_NOW_WITH_EXISTING_REPEAT_COUNT;SimpleTrigger常见策略：MISFIRE_INSTRUCTION_FIRE_NOW 立刻执行。对于不会重复执行的任务，这是默认的处理策略。MISFIRE_INSTRUCTION_RESCHEDULE_NEXT_WITH_REMAINING_COUNT 在下一个激活点执行，且超时期内错过的执行机会作废。MISFIRE_INSTRUCTION_RESCHEDULE_NOW_WITH_REMAINING_COUNT 立即执行，且超时期内错过的执行机会作废。MISFIRE_INSTRUCTION_RESCHEDULE_NEXT_WITH_EXISTING_COUNT 在下一个激活点执行，并重复到指定的次数。MISFIRE_INSTRUCTION_RESCHEDULE_NOW_WITH_EXISTING_COUNT 立即执行，并重复到指定的次数。MISFIRE_INSTRUCTION_IGNORE_MISFIRE_POLICY 忽略所有的超时状态，按照触发器的策略执行。对于CronTrigger，该“聪明策略”默认选择MISFIRE_INSTRUCTION_FIRE_ONCE_NOW以指导其行为。CronTrigger常见策略：MISFIRE_INSTRUCTION_FIRE_ONCE_NOW 立刻执行一次，然后就按照正常的计划执行。MISFIRE_INSTRUCTION_DO_NOTHING 目前不执行，然后就按照正常的计划执行。这意味着如果下次执行时间超过了end time，实际上就没有执行机会了。三、作业（Job）Job是一个任务接口，开发者定义自己的任务须实现该接口实现void execute(JobExecutionContext context)方法，JobExecutionContext中提供了调度上下文的各种信息。Job中的任务有可能并发执行，例如任务的执行时间过长，而每次触发的时间间隔太短，则会导致任务会被并发执行。如果是并发执行，就需要一个数据库锁去避免一个数据被多次处理。可以在execute()方法上添加注解@DisallowConcurrentExecution解决这个问题。四、作业详情（JobDetail）Quartz在每次执行Job时，都重新创建一个Job实例，所以它不直接接受一个Job的实例，相反它接收一个Job实现类，以便运行时通过newInstance()的反射机制实例化Job。因此需要通过一个类来描述Job的实现类及其它相关的静态信息，如Job名字、描述、关联监听器等信息，JobDetail承担了这一角色。所以说JobDetail是任务的定义，而Job是任务的执行逻辑。五、日历（Calendar） Calendar：org.quartz.Calendar和java.util.Calendar不同，它是一些日历特定时间点的集合（可以简单地将org.quartz.Calendar看作java.util.Calendar的集合——java.util.Calendar代表一个日历时间点，无特殊说明后面的Calendar即指org.quartz.Calendar）。一个Trigger可以和多个Calendar关联，以便排除或包含某些时间点。 ### 主要有以下CalendarHolidayCalendar：指定特定的日期，比如20140613。精度到天。DailyCalendar：指定每天的时间段（rangeStartingTime, rangeEndingTime)，格式是HH:MM[:SS[:mmm]]，也就是最大精度可以到毫秒。WeeklyCalendar：指定每星期的星期几，可选值比如为java.util.Calendar.SUNDAY。精度是天。MonthlyCalendar：指定每月的几号。可选值为1-31。精度是天AnnualCalendar： 指定每年的哪一天。使用方式如上例。精度是天。CronCalendar：指定Cron表达式。精度取决于Cron表达式，也就是最大精度可以到秒。六、JobDataMap用来保存JobDetail运行时的信息，JobDataMap的使用：.usingJobData("name","kyle")或者.getJobDataMap("name","kyle")七、cron表达式Cron表达式对特殊字符的大小写不敏感，对代表星期的缩写英文大小写也不敏感。星号(*)：可用在所有字段中，表示对应时间域的每一个时刻，例如， 在分钟字段时，表示“每分钟”；问号（?）：该字符只在日期和星期字段中使用，它通常指定为“无意义的值”，相当于点位符；减号(-)：表达一个范围，如在小时字段中使用“10-12”，则表示从10到12点，即10,11,12；逗号(,)：表达一个列表值，如在星期字段中使用“MON,WED,FRI”，则表示星期一，星期三和星期五；斜杠(/)：x/y表达一个等步长序列，x为起始值，y为增量步长值。如在分钟字段中使用0/15，则表示为0,15,30和45秒，而5/15在分钟字段中表示5,20,35,50，你也可以使用*/y，它等同于0/y；L：该字符只在日期和星期字段中使用，代表“Last”的意思，但它在两个字段中意思不同。L在日期字段中，表示这个月份的最后一天，如一月的31号，非闰年二月的28号；如果L用在星期中，则表示星期六，等同于7。但是，如果L出现在星期字段里，而且在前面有一个数值X，则表示“这个月的最后X天”，例如，6L表示该月的最后星期五；W：该字符只能出现在日期字段里，是对前导日期的修饰，表示离该日期最近的工作日。例如15W表示离该月15号最近的工作日，如果该月15号是星期六，则匹配14号星期五；如果15日是星期日，则匹配16号星期一；如果15号是星期二，那结果就是15号星期二。但必须注意关联的匹配日期不能够跨月，如你指定1W，如果1号是星期六，结果匹配的是3号星期一，而非上个月最后的那天。W字符串只能指定单一日期，而不能指定日期范围；LW组合：在日期字段可以组合使用LW，它的意思是当月的最后一个工作日；井号(#)：该字符只能在星期字段中使用，表示当月某个工作日。如6#3表示当月的第三个星期五(6表示星期五，#3表示当前的第三个)，而4#5表示当月的第五个星期三，假设当月没有第五个星期三，忽略不触发；C：该字符只在日期和星期字段中使用，代表“Calendar”的意思。它的意思是计划所关联的日期，如果日期没有被关联，则相当于日历中所有日期。例如5C在日期字段中就相当于日历5日以后的第一天。1C在星期字段中相当于星期日后的第一天。八、quartz.properties文件编写RAMJob的配置####################RAMJob的配置################## #在集群中每个实例都必须有一个唯一的instanceId，但是应该有一个相同的instanceName【默认“QuartzScheduler”】【非必须】 org.quartz.scheduler.instanceName = MyScheduler #Scheduler实例ID，全局唯一，【默认值NON_CLUSTERED】，或者可以使用“SYS_PROP”通过系统属性设置id。【非必须】 org.quartz.scheduler.instanceId=AUTO # 线程池的实现类（定长线程池，几乎可满足所有用户的需求）【默认null】【必须】 org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool # 指定线程数，至少为1（无默认值）(一般设置为1-100直接的整数合适)【默认-1】【必须】 org.quartz.threadPool.threadCount = 25 # 设置线程的优先级（最大为java.lang.Thread.MAX_PRIORITY 10，最小为Thread.MIN_PRIORITY 1）【默认Thread.NORM_PRIORITY (5)】【非必须】 org.quartz.threadPool.threadPriority = 5 #misfire设置的时间默认为一分钟 org.quartz.jobStore.misfireThreshold=60000 # 将schedule相关信息保存在RAM中，轻量级，速度快，遗憾的是应用重启时相关信息都将丢失。 org.quartz.jobStore.class = org.quartz.simpl.RAMJobStore # 建议设置为“org.terracotta.quartz.skipUpdateCheck=true”不会在程序运行中还去检查quartz是否有版本更新。【默认false】【非必须】 org.quartz.scheduler.skipUpdateCheck = truejdbcJobStore配置###################jdbcJobStore############################ org.quartz.scheduler.instanceName=MyScheduler org.quartz.scheduler.instanceId=AUTO org.quartz.threadPool.threadCount=3 # 所有的quartz数据例如job和Trigger的细节信息被保存在内存或数据库中,有两种实现：JobStoreTX(自己管理事务) #和JobStoreCMT（application server管理事务，即全局事务JTA） org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX # 类似于Hibernate的dialect，用于处理DB之间的差异，StdJDBCDelegate能满足大部分的DB org.quartz.jobStore.driverDelegateClass =org.quartz.impl.jdbcjobstore.StdJDBCDelegate #数据库表的前缀 org.quartz.jobStore.tablePrefix=QRTZ_ #配置数据源的名称 org.quartz.jobStore.dataSource=myDS #为了指示JDBCJobStore所有的JobDataMaps中的值都是字符串，并且能以“名字-值”对的方式存储而不是以复杂对象的序列化形式存储在BLOB字段中，应该设置为true(缺省方式) org.quartz.jobStore.useProperties = true # 检入到数据库中的频率(毫秒)。检查是否其他的实例到了应当检入的时候未检入这能指出一个失败的实例， #且当前Scheduler会以此来接管执行失败并可恢复的Job通过检入操作，Scheduler也会更新自身的状态记录 org.quartz.jobStore.clusterCheckinInterval=20000 # 是否集群、负载均衡、容错，如果应用在集群中设置为false会出错 org.quartz.jobStore.isClustered=false #misfire时间设置 org.quartz.jobStore.misfireThreshold=60000 # 连接超时重试连接的间隔。使用 RamJobStore时，该参数并没什么用【默认15000】【非必须】 #org.quartz.scheduler.dbFailureRetryInterval = 15000 #下面是数据库链接相关的配置 org.quartz.dataSource.myDS.driver=com.mysql.jdbc.Driver org.quartz.dataSource.myDS.URL=jdbc:mysql://localhost:3306/zproject?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=UTC org.quartz.dataSource.myDS.user=root org.quartz.dataSource.myDS.password=root org.quartz.dataSource.myDS.maxConnections=5参考资料quartz基本介绍和使用
