Java: importing data from a big text file to MySQL

These 2 classes show you how to read a HUGE TEXT file and insert data from each line to the MySQL database efficiently. The parsing of the lines is out of scope of this exercise as it will be different for each application.

package com.your_package.data;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Date;

public class YourClassName

{

public static void main(String[] args)

{

String db = "your_db_name";

String user = "root";

String password = "your_password";

Connection connection = makeDbConnection(db, user, password);

BigTextFile file = null;

try

{

file = new BigTextFile("/Users/uki/Documents/file_name.txt");

} catch (Exception e)

{

e.printStackTrace();

}

for (String line : file)

{

// process line here the way you want it

System.out.println(line.substring(line.lastIndexOf("|") + 1));

String partNumber = "0001";

String attributeName = "some name";

String attributeValue = "some value";

String sql = buildInsertSqlStatement(attributeName, attributeValue, partNumber);

insertIntoDb(connection, sql);

break; // remove after all works

}

}

private static String buildInsertSqlStatement(String attributeName, String attributeValue, String partNumber)

{

StringBuffer sql = new StringBuffer();

sql.append("INSERT INTO your_database_name.table_name VALUES (");

sql.append("'" + attributeName + "'");

sql.append(", '" + attributeValue + "'");

sql.append(", '" + partNumber + "' );");

return sql.toString();

}

private static void insertIntoDb(Connection connection, String sql)

{

final String TAG = YourClassName.class.getCanonicalName();

try

{

Statement st = connection.createStatement();

System.out.println(TAG + "Executing: " + sql);

int val = st.executeUpdate(sql.toString());

System.out.println(TAG + " Returned: " + val);

} catch (SQLException e)

{

System.out.println("SQL insert failed " + e);

}

System.out.println(TAG + "Finished " + new Date());

}

private static Connection makeDbConnection(String db, String user, String password)

{

Connection con = null;

try

{

String url = "jdbc:mysql://localhost:3306/";

String driver = "com.mysql.jdbc.Driver";

Class.forName(driver);

con = DriverManager.getConnection(url + db, "root", "");

} catch (Exception e)

{

e.printStackTrace();

}

return con;

}

}

package com.your_package.data;

import java.io.BufferedReader;

import java.io.FileReader;

import java.util.Iterator;

public class BigTextFile implements Iterable

{

private class FileIterator implements Iterator

{

private String line;

public boolean hasNext()

{

try

{

line = bufferedReader.readLine();

} catch (Exception ex)

{

line = null;

ex.printStackTrace();

}

return line != null;

}

public String next()

{

return line;

}

public void remove()

{

}

}

private BufferedReader bufferedReader;

public BigTextFile(String filePath) throws Exception

{

bufferedReader = new BufferedReader(new FileReader(filePath));

}

public void Close()

{

try

{

bufferedReader.close();

} catch (Exception ex)

{

}

}

public Iterator iterator()

{

return new FileIterator();

}

}