mysql - 使用 Spring Data JPA 自定义存储库方法将数据从 csv 加载到 mysql 表

Question

我创建了自定义存储库方法来将数据从 csv 加载到 mysql 表。

我的代码:

@Repository
public class AppRepositoryImpl implements AppRepositoryCustom {

    @PersistenceContext
    EntityManager entityManager;

    @Override
    public void loadCsv() {

        StringBuilder builder = new StringBuilder("LOAD DATA LOCAL INFILE ");
        builder.append("'C:/test/app.csv'");
        builder.append(" INTO TABLE app FIELDS TERMINATED BY ',' ");
        builder.append(" ENCLOSED BY '\\\"'");
        builder.append(" LINES TERMINATED BY '\\n' IGNORE 1 LINES");
        builder.append(" (");
        builder.append("@col1,@col2,@col3");
        builder.append(") ");
        builder.append("SET ");
        builder.append(
                "app_name=NULLIF(IF( (@col1 REGEXP '\\\\r$' OR @col1 REGEXP '\\\\r\\\\n$' OR @col1 REGEXP '\\\\n$'), TRIM(TRAILING '\\\\n' FROM (TRIM(TRAILING '\\\\r\\\\n' FROM TRIM(TRAILING '\\\\r' FROM @col1) ) ) ),  @col1),''),\r\n"
                        + "app_date=NULLIF(IF( (@col2 REGEXP '\\\\r$' OR @col2 REGEXP '\\\\r\\\\n$' OR @col2 REGEXP '\\\\n$'), TRIM(TRAILING '\\\\n' FROM (TRIM(TRAILING '\\\\r\\\\n' FROM TRIM(TRAILING '\\\\r' FROM @col2) ) ) ),  @col2),''),\r\n"
                        + "app_desc=NULLIF(IF( (@col3 REGEXP '\\\\r$' OR @col3 REGEXP '\\\\r\\\\n$' OR @col3 REGEXP '\\\\n$'), TRIM(TRAILING '\\\\n' FROM (TRIM(TRAILING '\\\\r\\\\n' FROM TRIM(TRAILING '\\\\r' FROM @col3) ) ) ),  @col3),'')");

        try {
            entityManager.createNativeQuery(builder.toString()).getResultList();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

我收到以下异常:

2019-04-23 01:47:01.305  WARN 3764 --- [nio-7070-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S1000
2019-04-23 01:47:01.305 ERROR 3764 --- [nio-7070-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : ResultSet is from UPDATE. No Data.
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query

我应该使用哪种 JPA 查询方法来修复运行此 SQL？

Answer 1

您的 createNativeQuery(string).getResultList() 可能是错误来源，因为您正在尝试发出 Insert 语句。

改用createNativeQuery(string).executeUpdate()。