JDBC导入CSV文件到MySQL Server

Tags: /Java/ Date Created:

在MySQL Server 8.0中，利用load data local infile导入csv的方法

Load data local infile 是 MySQL 最快最有效率的导入数据的方法，在MySQL Server 8.0中，导入数据与之前的版本有一些差异，下面是跳坑指南。在早期MySQL版本中，服务器默认的 local_infile 是 ON 的，允许直接在客户端加载文件导入，但是在 MySQL Server 8.0 以上版本中，默认是 OFF，所以默认情况下，导入数据会提示错误：

✘

Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides

解决办法:

在JDBC中，首先运行以下SQL代码，允许服务器运行 load data local infile 指令:

set global local_infile = 1

其次，在客户端的 JDBC 连接字符串，增加 allowLoadLocalInfile=true，例如:

spring.datasource.url=jdbc:mysql://server:3306/db?useUnitcode=true&characterEncoding=utf-8&allowLoadLocalInfile=true

这样，我们就可以运行导入文件的语句了，例如下面是一个文件上传导入的RestAPI Controller示例:

@RequestMapping("/import")

public AjaxResult<Object> importFile(@RequestParam("file") MultipartFile file) {
    if (file.isEmpty()) {
        return AjaxResult.fail("Upload file is empty");
    }

    String fileType = file.getContentType();
    try {
        SqlHelper.execute("set global local_infile = 1;");
        File tmpFile = File.createTempFile("apj", ".csv");
        file.transferTo(tmpFile);
        String sql = String.format("load data local infile '%s' " +
                "replace into table 你的表名 " +
                "fields terminated by ',' " +
                "lines terminated by '\\r\\n' " +
                "ignore 1 lines " +
                "(id,字段1,字段2,字段3,字段4,字段5)", tmpFile.getPath().replace("\\", "\\\\"));
        SqlHelper.execute(sql);
        tmpFile.deleteOnExit();
        return AjaxResult.ok(fileType);
    } catch (IOException e) {
        return AjaxResult.fail("Can't create temporary file: " + e.getMessage());
    } catch (Exception e) {
        return AjaxResult.fail((e.getCause() != null) ? e.getCause().getMessage() : e.getMessage());
    }
}

其中SqlHelper.java代码如下:

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * SQL Helper
 */
@Component
@Slf4j
public class SqlHelper {
    private static JdbcTemplate jdbcTemplate;

    @Autowired
    private SqlHelper(JdbcTemplate jdbcTemplate) {
        SqlHelper.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Query for first column of first record
     *
     * @param sql SQL statement
     * @return first column integer of first record
     */
    public static Integer queryForInt(String sql) {
        Map<String, Object> map = jdbcTemplate.queryForMap(sql);
        Object obj = map.values().iterator().next();
        if (obj == null) {
            return null;
        }
        return Integer.parseInt(obj.toString());
    }

    public static String version() {
        return queryForString("select version()");
    }

    /**
     * Query for single record with single field
     *
     * @param sql SQL statement
     * @return first column value of first record
     */
    public static String queryForString(String sql) {
        Map<String, Object> map = jdbcTemplate.queryForMap(sql);
        Object obj = map.values().iterator().next();
        if (obj == null) {
            return null;
        }
        return obj.toString();
    }

    /**
     * Run SQL and return List Object
     * <p>
     * Usage:
     * <pre>{@code
     *         RowMapper<Result> mapper = new BeanPropertyRowMapper<>(Result.class);
     *         List<Result> ret = SqlHelper.query("select * from ete_result", mapper);
     * }</pre>
     *
     * @param sql       SQL statement
     * @param rowMapper RowMapper
     * @param <T>       Type
     * @return List Object Of T
     */
    public static <T> List<T> query(String sql, RowMapper<T> rowMapper) {
        return jdbcTemplate.query(sql, rowMapper);
    }

    public static void execute(String sql) {
        jdbcTemplate.execute(sql);
    }
}