Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL

Mysql load data的使用

数据库中,最常见的写入数据方式是通过SQL INSERT来写入,另外就是通过备份文件恢复数据库,这种备份文件在MySQL中是SQL脚本,实际上执行的还是在批量INSERT语句。

在实际中,常常会遇到两类问题:一类是数据导入,比如从word、excel表格或者txt文档导入数据(这些数据一般来自于非技术人员通过OFFICE工具录入的文档);一类数据交换,比如从MySQL、Oracle、DB2数据库之间的数据交换。

这其中就面临一个问题:数据库SQL脚本有差异,SQL交换比较麻烦。但是几乎所有的数据库都支持文本数据导入(LOAD)导出(EXPORT)功能。利用这一点,就可以解决上面所提到的数据交换和导入问题。

MySQL的LOAD DATAINFILE语句用于高速地从一个文本文件中读取行,并装入一个表中。文件名称必须为一个文字字符串。下面以MySQL5为例说明,说明如何使用MySQL的LOADDATA命令实现文本数据的导入。

注意:这里所说的文本是有一定格式的文本,比如说,文本分行,每行中用相同的符号隔开文本等等。等等,获取这样的文本方法也非常的多,比如可以把word、excel表格保存成文本,或者是一个csv文件。

在项目中,使用的环境是快速上传一个csv文件,原系统中是使用的db2数据库,然后调用了与mysql的loaddata相似的一个函数sysproc.db2load。但是loaddata在mysql的存储过程是不能使用的。采取的方法时在java代码中调用此方法。

实现的例子:

准备测试表

SQL如下:

USE test;

CREATE TABLE `test` (
	`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`a` int(11) NOT NULL,
	`b` bigint(20) UNSIGNED NOT NULL,
	`c` bigint(20) UNSIGNED NOT NULL,
	`d` int(10) UNSIGNED NOT NULL,
	`e` int(10) UNSIGNED NOT NULL,
	`f` int(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`),
	KEY `a_b` (`a`, `b`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8

Java代码如下:

package com.seven.dbTools.DBTools;

import org.apache.log4j.Logger;

import org.springframework.jdbc.core.JdbcTemplate;

import java.io.ByteArrayInputStream;
import java.io.InputStream;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

/**

 *
 @author seven
 *
 @since 07.03.2013
 */
public class BulkLoadData2MySQL {
  private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class);
  private JdbcTemplate jdbcTemplate;
  private Connection conn = null;

  public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }

  public static InputStream getTestDataInputStream() {
    StringBuilder builder = new StringBuilder();

    for (int i = 1; i <= 10; i++) {
      for (int j = 0; j <= 10000; j++) {
        builder.append(4);

        builder.append("\t");

        builder.append(4 + 1);

        builder.append("\t");

        builder.append(4 + 2);

        builder.append("\t");

        builder.append(4 + 3);

        builder.append("\t");

        builder.append(4 + 4);

        builder.append("\t");

        builder.append(4 + 5);

        builder.append("\n");
      }
    }

    byte[] bytes = builder.toString().getBytes();

    InputStream is = new ByteArrayInputStream(bytes);

    return is;
  }

  /**

   *

   * load bulk data from InputStream to MySQL

   */
  public int bulkLoadFromInputStream(String loadDataSql,
    InputStream dataStream) throws SQLException {
    if (dataStream == null) {
      logger.info("InputStream is null ,No data is imported");

      return 0;
    }

    conn = jdbcTemplate.getDataSource().getConnection();

    PreparedStatement statement = conn.prepareStatement(loadDataSql);
    int result = 0;

    if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {
      com.mysql.jdbc.PreparedStatement mysqlStatement = statement.unwrap(com.mysql.jdbc.PreparedStatement.class);

      mysqlStatement.setLocalInfileInputStream(dataStream);

      result = mysqlStatement.executeUpdate();
    }

    return result;
  }

  public static void main(String[] args) {
    String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";

    InputStream dataStream = getTestDataInputStream();

    BulkLoadData2MySQL dao = new BulkLoadData2MySQL();

    try {
      long beginTime = System.currentTimeMillis();

      int rows = dao.bulkLoadFromInputStream(testSql, dataStream);

      long endTime = System.currentTimeMillis();

      logger.info("importing " + rows +
        " rows data into mysql and cost " + (endTime - beginTime) +
        " ms!");
    } catch (SQLException e) {
      e.printStackTrace();
    }

    System.exit(1);
  }
}

提示:

例子中的代码使用setLocalInfileInputStream方法,会直接忽略掉文件名称,而直接将IO流导入到数据库中。在实际的实现中也可以把文件上传到服务器,然后读文件再导入文件,此时load data的local参数应该去掉,并且文件名应该是完整的绝对路径的名字。

最后附上LOAD DATA INFILE语法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
  [REPLACE | IGNORE]
 INTO TABLE tbl_name
  [FIELDS
  [TERMINATED BY 'string']
  [[OPTIONALLY] ENCLOSED BY 'char']
  [ESCAPED BY 'char' ]
 ]
  [LINES
  [STARTING BY 'string']
 [TERMINATED BY 'string']
 ]
  [IGNORE number LINES]
 [(col_name_or_user_var,...)]
  [SET col_name = expr,...]]

总结

LOADDATA是一个很有用的命令,从文件中导入数据比insert语句要快,MySQL文档上说要快20倍左右。但是命令的选项很多,然而大多都用不到,如果真的需要,用的时候看看官方文档即可。

您可能感兴趣的文章:

  • Java实现批量向mysql写入数据的方法
  • java 下执行mysql 批量插入的几种方法及用时
  • java基于jdbc连接mysql数据库功能实例详解
  • java连接MySQL数据库的代码
  • java向mysql插入数据乱码问题的解决方法
  • java连接mysql数据库及测试是否连接成功的方法
  • Java中如何获取mysql连接的3种方法总结
时间: 2018-03-10

Java实现批量向mysql写入数据的方法

本文实例讲述了Java实现批量向mysql写入数据的方法.分享给大家供大家参考,具体如下: private static String user = "root"; private static String pass = "123456"; private static String URL = "jdbc:mysql://192.168.1.116:3306/test"; public static void main(String args

Java中如何获取mysql连接的3种方法总结

前言 本文主要来说说三种 Java 中获取 mysql 连接的方式,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍: 第一种:传统的连接方式: 第二种:读取配置文件方式: 第三种:数据库连接池. 一.传统的连接方式: 首先在 src 目录下创建名为 lib 的文件夹,导入数据库驱动的 jar 包,随后 "add to build path". 1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); 2.获取连接 S

java向mysql插入数据乱码问题的解决方法

遇到java向mysql插入数据乱码问题,如何解决? MySQL默认编码是latin1 mysql> show variables like 'character%'; +--------------------------+--------------------------+ | Variable_name | Value | +--------------------------+--------------------------+ | character_set_client | la

java基于jdbc连接mysql数据库功能实例详解

本文实例讲述了java基于jdbc连接mysql数据库的方法.分享给大家供大家参考,具体如下: 一.JDBC简介 Java 数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法.JDBC也是Sun Microsystems的商标.它JDBC是面向关系型数据库的. 1.JDBC架构: JDBC API支持两层和三层处理模型进行数据库访问,但在一般的JDBC体系结构由

java连接mysql数据库及测试是否连接成功的方法

本文实例讲述了java连接mysql数据库及测试是否连接成功的方法.分享给大家供大家参考,具体如下: package com.test.tool; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement

java连接MySQL数据库的代码

本文实例为大家分享了java连接MySQL数据库的具体代码,供大家参考,具体内容如下 package connect; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Connect { //驱动程序位置 public static final String DBDRIVER="com.mysql.jdbc.Driver"; //连接地址

java 下执行mysql 批量插入的几种方法及用时

方法1: Java code 复制代码 代码如下: conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);        pstmt = conn                .prepareStatement("insert into loadtest (id, data) values (?, ?)");        for (int i = 1; i <= COUNT; i++) {    

MYSQL开发性能研究之批量插入数据的优化方法

一.我们遇到了什么问题 在标准SQL里面,我们通常会写下如下的SQL insert语句. INSERT INTO TBL_TEST (id) VALUES(1); 很显然,在MYSQL中,这样的方式也是可行的.但是当我们需要批量插入数据的时候,这样的语句却会出现性能问题.例如说,如果有需要插入100000条数据,那么就需要有100000条insert语句,每一句都需要提交到关系引擎那里去解析,优化,然后才能够到达存储引擎做真的插入工作. 正是由于性能的瓶颈问题,MYSQL官方文档也就提到了使用批

MySql批量插入优化Sql执行效率实例详解

MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},

Node.js下向MySQL数据库插入批量数据的方法

项目(nodejs)中需要一次性插入多笔数据到数据库,数据库是mysql的,由于循环插入的性能太差,就像使用批量插入的方法提高数据的插入性能. 批量插入的数据库的表结构如下: 1.数据库连接 var mysql = require('mysql'); // 数据库信息 var connection = mysql.createConnection({ host : 'localhost', user : '数据库用户名', password : '数据库登录密码', database : '操作

MySQL批量插入和唯一索引问题的解决方法

MySQL批量插入问题 在开发项目时,因为有一些旧系统的基础数据需要提前导入,所以我在导入时做了批量导入操作 ,但是因为MySQL中的一次可接受的SQL语句大小受限制所以我每次批量虽然只有500条,但依然无法插入,这个时候代码报错如下: nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576). You can change this va

MySQL批量插入数据脚本

MySQL批量插入数据脚本 #!/bin/bash i=1; MAX_INSERT_ROW_COUNT=$1; while [ $i -le $MAX_INSERT_ROW_COUNT ] do mysql -uroot -proot dbname -e "insert into tablename (name,age,createTime) values ('HELLO$i',$i % 99,NOW());" d=$(date +%M-%d\ %H\:%m\:%S) echo &qu

mybatis中批量插入的两种方式(高效插入)

MyBatis简介 MyBatis是一个支持普通SQL查询,存储过程和高级映射的优秀持久层框架.MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装.MyBatis可以使用简单的XML或注解用于配置和原始映射,将接口和Java的POJO(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录. 一.mybiats foreach标签 foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合.foreach元素的属性主

python消费kafka数据批量插入到es的方法

1.es的批量插入 这是为了方便后期配置的更改,把配置信息放在logging.conf中 用elasticsearch来实现批量操作,先安装依赖包,sudo pip install Elasticsearch2 from elasticsearch import Elasticsearch class ImportEsData: logging.config.fileConfig("logging.conf") logger = logging.getLogger("msg&

Java多线程执行处理业务时间太久解决方法代码示例

背景:在政府开发了一个应用系统,主要功能是让企业填写企业资质信息,然后通过给定的公式,统计这一系列的信息,以得分的形式展示给政府领导查看.目前有1300家企业填报.由于得分是实时显示的,所以导致统计功能很慢. 代码运行流程: 1.查出1300企业信息 2.遍历1300企业信息,ji计算每家企业得分信息.每家预计时间为0.3秒.合计390秒.导致页面请求超时 3.导出(用jxl jar) 解决方案: 由于处理业务的,所以需要能有返回值的线程.用:Callable 直接上代码 1.调用线程的代码 L

使用Java构造和解析Json数据的两种方法(详解二)

JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式,采用完全独立于语言的文本格式,是理想的数据交换格式.同时,JSON是 JavaScript 原生格式,这意味着在 JavaScript 中处理 JSON数据不须要任何特殊的 API 或工具包. 在www.json.org上公布了很多JAVA下的json构造和解析工具,其中org.json和json-lib比较简单,两者使用上差不多但还是有些区别.下面接着介绍用org.json构造和解析Json数据的方法