springBoot+schedule+mybatis整合demo

Demo实现的功能

简单实现定时从数据库查出数据, 通过邮件方式发送给收件人.

github源码地址

https://github.com/mikewuhao/springBoot-schedule-demo

搭建详细步骤

准备工作
获取邮箱的授权码, 以qq邮箱为例, 参考
https://service.mail.qq.com/cgi-bin/help?subtype=1&&no=1001256&&id=28

项目结构

在idea开发工具里面新建maven类型的project, 命名为springBoot-schedule-demo按上图目录结构建包

pom文件如下, 其中springBoot已集成了spring schedule基础模块, 不需额外添加依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.wuhao.demo</groupId>
    <artifactId>springBoot-schedule-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

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

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

        <!-- spring-boot整合mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

        <!-- mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

        <!--spring boot集成javamail-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>

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

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.0</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

配置文件application.properties

server.port=8081

logging.level.org.springframework=DEBUG

#数据库
spring.datasource.driver-class-name= com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/boot?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root

#mybatis
mybatis.type-aliases-package=com.wuhao.domain
mybatis.mapper-locations=classpath:mapper/*.xml

#配置mail
spring.mail.host=smtp.qq.com
#qq邮箱地址
spring.mail.username=XXXXX@qq.com
#qq邮箱授权码
spring.mail.password=XXXXX
#qq邮箱授权码
spring.mail.port=587
spring.mail.default-encoding=utf-8
spring.mail.properties.mail.smtp.auth=true
spring.mail.properties.mail.smtp.starttls.enable=true
spring.mail.properties.mail.smtp.starttls.required=true

启动类, 开始@EnableScheduling注解, 启动定时任务

package com.wuhao;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableScheduling;

/**
 * @description: 启动类
 * @author: wuhao
 * @create: 2020-06-15 23:11
 **/
@SpringBootApplication
@EnableScheduling
public class ScheduleApplication {

    public static void main(String[] args) {
        SpringApplication.run(ScheduleApplication.class, args);
    }
}

控制层, ScheduleController

package com.wuhao.controller;

import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @description: controller
 * @author: wuhao
 * @create: 2020-06-15 23:11
 **/
@RestController
@Slf4j
public class ScheduleController {

    /***
    * @Description: 测试方法
    * @return: java.lang.String
    * @Author: wuhao
    * @Date: 2020/6/15 23:06
    */
    @RequestMapping("/hello")
    public String hello() {
        return "hello";
    }


}

dao层接口类, UserMapper

package com.wuhao.dao;

import com.wuhao.domain.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @description: dao接口类
 * @author: wuhao
 * @create: 2020-06-15 23:11
 **/
@Mapper
@Repository
public interface UserMapper {

    User queryUserById(Long id);

    int addUser(User user);

    int modifyUser(User user);

    int deleteUserById(Long id);

    List<User> queryAllUsers();

}

User实体类

package com.wuhao.domain;

import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * @description: 实体类
 * @author: wuhao
 * @create: 2020-06-15 23:11
 **/
@Entity
@NoArgsConstructor
@AllArgsConstructor
public class User {

    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "username")
    private String username;

    @Column(name = "birthday")
    private String birthday;

    @Column(name = "sex")
    private String sex;

    @Column(name = "address")
    private String address;

    public Long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday='" + birthday + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

定时任务配置类, ScheduleJob

package com.wuhao.schedule;

import com.wuhao.domain.User;
import com.wuhao.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @description: 定时任务类
 * @author: wuhao
 * @create: 2020-06-15 23:11
 **/
@Component
public class ScheduleJob {

    //邮件发送人的email地址
    @Value("${spring.mail.username}")
    private String username;

    @Autowired
    private JavaMailSender mailSender;

    @Autowired
    private UserService userService;

    @Scheduled(cron = "0/60 * * * * ? ")//每60秒执行一次
    public void job(){
        //邮件接收人的email地址
        String recipientEmailAddr = "XXXX@163.com";
        List<User> userList = userService.queryAllUsers();
        SimpleMailMessage message = new SimpleMailMessage();
        message.setFrom(username);
        message.setTo(recipientEmailAddr);
        message.setSubject("主题:用户数据");
        message.setText("测试邮件内容"+userList.toString());
        mailSender.send(message);
    }

}

用户接口, UserService

package com.wuhao.service;

import com.wuhao.domain.User;

import java.util.List;

/**
 * @description: 用户接口
 * @author: wuhao
 * @create: 2020-06-15 23:11
 **/
public interface UserService {

    User queryUserById(Long id);

    int addUser(User user);

    int modifyUser(User user);

    int deleteUserById(Long id);

    List<User> queryAllUsers();

}

用户接口实现类, UserServiceImpl

package com.wuhao.service.impl;

import com.wuhao.dao.UserMapper;
import com.wuhao.domain.User;
import com.wuhao.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @description: 用户接口实现类
 * @author: wuhao
 * @create: 2020-06-15 23:11
 **/
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public User queryUserById(Long id) { return userMapper.queryUserById(id); }

    @Override
    public int addUser(User user) { return userMapper.addUser(user); }

    @Override
    public int modifyUser(User user) {
        return userMapper.modifyUser(user);
    }

    @Override
    public int deleteUserById(Long id) {
        return userMapper.deleteUserById(id);
    }

    @Override
    public List<User> queryAllUsers() { return userMapper.queryAllUsers(); }

}

sql的xml文件, userMapper.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="com.wuhao.dao.UserMapper">

    <!--按id查询用户-->
    <select id="queryUserById" resultType="com.wuhao.domain.User">
        select * from `user` where id = #{id}
    </select>

    <!--查询所有用户-->
    <select id="queryAllUsers" resultType="com.wuhao.domain.User">
        select * from `user` where 1 =1
    </select>

    <!--用户更新-->
    <update id="modifyUser" parameterType="com.wuhao.domain.User" >
        update `user` set username=#{username},birthday=#{birthday},sex=#{sex}, address=#{address} where id=#{id}
    </update>

    <!--删除用户-->
    <delete id="deleteUserById" parameterType="long">
        delete from `user` where id=#{id}
    </delete>

    <!--用户添加-->
    <insert id="addUser" parameterType="com.wuhao.domain.User">
        insert into `user` (username,birthday,sex,address)
             values(#{username},#{birthday},#{sex},#{address})
    </insert>
    
</mapper>

演示效果

项目项目后, 查看接收人邮箱, 是否定时收到用户数据的邮件.

遇到的问题

1 刚最开始, application.properties配置文件里qq邮箱端口用的是465

spring.mail.port=465

但项目启动后报端口465连接错误: Could not connect to SMTP host: smtp.qq.com, port: 465。QQ邮箱POP3的端口号bai995,SMTP的端口号是465或587
改端口为587

spring.mail.port=587

解决了端口连接问题