java - Spring 中计划的 SQL LOAD DATA INFILE 脚本执行

Question

我正在尝试安排一个 LOAD DATA INFILE 脚本来定期从 CSV 文件更新 MySql 数据库表。

执行计划的方法，但不执行脚本本身。脚本手动执行良好。

脚本位于/resources/sql/

DbConfig.java

@Bean
public DataSource dataSource() {
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    ds.setUrl("jdbc:mysql://localhost:3306/my-database");
    ds.setUsername("username");
    ds.setPassword("password");

    // Update DB on startup
    DatabasePopulatorUtils.execute(databasePopulator(), ds);

    return ds;
}

@Bean
public ResourceDatabasePopulator databasePopulator() {
    ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    populator.setSqlScriptEncoding("UTF-8");
    populator.addScript(new ClassPathResource("/sql/ProductUpdate.sql"));
    return populator;
}

// The scheduled method
@Scheduled(fixedDelay = 15000) // Every 15 sec
public void updateProductsTable() {
    DatabasePopulatorUtils.execute(databasePopulator(), this.dataSource());
}

SQL 脚本

CREATE TEMPORARY TABLE products_temp
(
  product_code varchar(100),
  supplier_number int(10),
  price double,
  product_name varchar(255),
  stock_amount double,
  buying_price double
);


LOAD DATA CONCURRENT LOCAL INFILE 'C:\path\to\csv\product-data.txt' REPLACE 
INTO TABLE products_temp
CHARACTER SET latin1
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(product_code, supplier_number, @price, product_name, @stock_amount, 
@buying_price)
SET
  price = replace(@price, ',', '.'),
  stock_amount = replace(@stock_amount, ',', '.'),
  buying_price = replace(@buying_price, ',', '.');

INSERT INTO products (product_code, supplier_number, price, product_name, 
stock_amount, buying_price)
SELECT product_code, supplier_number, price, product_name, stock_amount, 
buying_price FROM products_temp
WHERE product_code NOT IN (SELECT product_code FROM products);

UPDATE products a
JOIN products_temp b ON a.product_code = b.product_code AND 
a.supplier_number = b.supplier_number
SET a.price = b.price,
    a.product_name = b.product_name,
    a.stock_amount = b.stock_amount,
    a.buying_price = b.buying_price;

DROP TABLE products_temp;

我期望sql脚本每15秒执行一次，但实际上脚本根本不执行。

此代码不会产生错误。

Answer 1

我认为cron作业表达式是错误的，尝试使用以下调度方法执行，我已经更新了CRON

 @Bean
 @Scheduled(cron = "0/15 * * * * ?") // Every 15 sec
 public void updateProductsTable() {
 DatabasePopulatorUtils.execute(databasePopulator(), this.dataSource());
 }