如何使用 Java 在 MySQL 数据库中导入制表符分隔的文件?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/6544129/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-10-30 16:17:49  来源:igfitidea点击:

How to import a tab delimited file in MySQL database using Java?

javamysqltab-delimited

提问by Sameek Mishra

I want to import a tab delimited file in MySQL database. How I can do this using Java?

我想在 MySQL 数据库中导入一个制表符分隔的文件。我如何使用 Java 做到这一点?

回答by Candide

This is very vague, but it sounds like a mysql specific question. Here's the manual for loading files. The default is:

这很模糊,但听起来像是一个特定于 mysql 的问题。这是加载文件手册。默认为:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'
LINES TERMINATED BY '\n' STARTING BY ''

回答by Eric Leschinski

Import a tab delimited file into MySQL:

将制表符分隔的文件导入 MySQL:

  1. Create your table:

    mysql> create table foo(id INT, mytext TEXT, sparkle DECIMAL(9,4));
    Query OK, 0 rows affected (0.02 sec)
    
  2. Create your data file, put this in foo.txt, note tabs delimit the 3 columns

    1   twilight sparkle is best pony!  6.6 
    2   pinkie pie is best pony!    3.3 
    3   derpy hooves is best pony!  1.1 
    
  3. Then import the file:

    mysqlimport --fields-terminated-by='\t' 
                --columns=id,mytext,sparkle 
                --local -u root -ppassword your_database foo.txt
    yourdatabase.foo: Records: 4  Deleted: 0  Skipped: 0  Warnings: 3
    
  4. Look in the table.

    mysql> select * from foo;
    +------+-------------------------------+---------+
    | id   | mytext                        | sparkle |
    +------+-------------------------------+---------+
    |    1 | twilight sparkle is best pony |  6.6000 |
    |    2 | pinkie pie is best pony       |  3.3000 |
    |    3 | derpy hooves is best pony     |  1.1000 |
    +------+-------------------------------+---------+
    4 rows in set (0.00 sec)
    
  1. 创建你的表:

    mysql> create table foo(id INT, mytext TEXT, sparkle DECIMAL(9,4));
    Query OK, 0 rows affected (0.02 sec)
    
  2. 创建您的数据文件,将其放入 foo.txt,注意制表符分隔 3 列

    1   twilight sparkle is best pony!  6.6 
    2   pinkie pie is best pony!    3.3 
    3   derpy hooves is best pony!  1.1 
    
  3. 然后导入文件:

    mysqlimport --fields-terminated-by='\t' 
                --columns=id,mytext,sparkle 
                --local -u root -ppassword your_database foo.txt
    yourdatabase.foo: Records: 4  Deleted: 0  Skipped: 0  Warnings: 3
    
  4. 看表。

    mysql> select * from foo;
    +------+-------------------------------+---------+
    | id   | mytext                        | sparkle |
    +------+-------------------------------+---------+
    |    1 | twilight sparkle is best pony |  6.6000 |
    |    2 | pinkie pie is best pony       |  3.3000 |
    |    3 | derpy hooves is best pony     |  1.1000 |
    +------+-------------------------------+---------+
    4 rows in set (0.00 sec)
    

The rows were loaded.

已加载行。

Read up on my mysqlimport command to see all the things it can do.

阅读我的 mysqlimport 命令以查看它可以执行的所有操作。

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

回答by anubhava

Use Scanner APIto read file and create your dynamic SQL statements. After that use JDBC preparedStatementand execute them.

使用Scanner API读取文件并创建动态 SQL 语句。之后使用JDBC PreparedStatement并执行它们。

回答by JB Nizet

Open a JDBC connection to your database. Create a prepared statement to insert a row in your table. Read the file line by line. You might use something like OpenCSVto help you. For each line, bind the parameters of the prepared statement and execute it. Close the prepared statement, the connection and the file reader.

打开与数据库的 JDBC 连接。创建一个准备好的语句以在表中插入一行。逐行读取文件。您可能会使用OpenCSV 之类的东西来帮助您。对于每一行,绑定准备好的语句的参数并执行它。关闭准备好的语句、连接和文件阅读器。

Read http://download.oracle.com/javase/tutorial/jdbc/basics/index.htmlto learn JDBC.

阅读http://download.oracle.com/javase/tutorial/jdbc/basics/index.html学习 JDBC。

回答by AlexR

BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream("myfile.tab")));

String line = null;

while((line = reader.readLine()) != null) {
    String[] fiedls = line.split("\t");
    // generate your SQL insert statement here
    // execute SQL insert.
}

If you are not familier with SQL and/or JDBC refer to appropriate tutorial. Google will help you to find one.

如果您不熟悉 SQL 和/或 JDBC,请参阅相应的教程。谷歌会帮你找到一个。