51工具盒子需求 {#需求}
线上展示的项目，在特定时间段重置数据库
其实就是在spring boot上的特定时间运行 .sql 文件。
实践 {#实践}
使用定时注解，请记得在启动类添加注解 @EnableScheduling
Controller {#Controller}
|------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | @RestController @RequestMapping("/test") public class InitDatabaseController { private Logger logger = LoggerFactory.getLogger(InitDatabaseController.class); @Autowired private DataService dataService; @GetMapping("/") //定时任务 每天8点和14点重置数据库 @Scheduled(cron = "0 0 8,14 * * ? ") public RespBean resetDatabase () { logger.info( "【开始】重置数据库vhr的数据" ); long start = System.currentTimeMillis(); if (!dataService.resetDataBase( "vhrbck.sql" )){ return RespBean.error( "重置数据库失败！" ); } long end = System.currentTimeMillis(); String cost = "用时：" + (end - start) / 1000.0 + "秒" ; logger.info( "【结束】重置成功：" + cost); return RespBean.ok( "重置数据库成功!" , cost); } } |
Service {#Service}
|------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | @Service public class DataServiceImpl implements DataService { @Autowired private DataSource dataSource; //文件放在 resources目录下 @Override public Boolean resetDataBase (String sqlScriptName) { Connection conn = null ; try { // 从Druid数据源（数据库连接池）中获取一个数据库连接 conn = dataSource.getConnection(); ClassPathResource rc = new ClassPathResource (sqlScriptName); EncodedResource er = new EncodedResource (rc, StandardCharsets.UTF_8); // ScriptUtils：是SpringBoot源码中使用的工具类，能够执行Sql脚本 // sql脚本执行中途，遇到错误，默认会抛出异常，停止执行 // 建议传入参数true，忽略中途的错误，但是后面4个参数又是必需的，只需要填入源码中的默认值即可 ScriptUtils.executeSqlScript(conn, er, true , true , "--" , ";" , "/*" , "*/" ); return true ; } catch (Exception e) { e.printStackTrace(); } finally { // 不要忘了释放连接 try { if (conn != null ) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } return false ; } } |
重置数据库的接口请求不能过于频繁，因此可以搭配 限制接口请求次数 方法来使用
