0

Hello!

I'm writing a Java program which inserts lines from a given Text-File into a MySQL Database. My code works fine for smaller text files (< 1KB), however for larger TextFiles (> 1MB), I'm getting the following Runtime Error:

This error typically occurs about 30,000 words in (about 250 KB). I'm not sure why this is happening or how to get around it.

Code below:

My InsertRecord classfile:

This specific program is writing to a Table named "Lexicon", and a Database named "WordGuess".

import java.sql.DriverManager;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import FileManager.MetaTextFile;

public class InsertRecord {

    private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_CONNECTION = "jdbc:mysql://localhost:8889/";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "root";

    public static void insertRecord(String db, String tbl) throws SQLException, IOException {

        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;     

        MetaTextFile txt = new MetaTextFile(tbl);
        txt.putFileIntoArray(tbl);
        String[] FileArray = txt.getArray();


        for (int i=0; i<FileArray.length; i++){
            String insert_statement = "INSERT INTO " + tbl + " VALUE (NULL, "
                    + "'" + FileArray[i] + "'" + ")";           

            try {
                dbConnection = getDBConnection(db);
                preparedStatement = dbConnection.prepareStatement(insert_statement);

                // execute create SQL statement
                preparedStatement.executeUpdate();

            } catch (SQLException e) {

                System.out.println(e.getMessage());

            } finally {

                if (preparedStatement != null) {
                    preparedStatement.close();
                }

                if (dbConnection != null) {
                    dbConnection.close();
                }

            }
        }

    }

    private static Connection getDBConnection(String db) {

        Connection dbConnection = null;

        try {

            Class.forName(DB_DRIVER);

        } catch (ClassNotFoundException e) {

            System.out.println(e.getMessage());

        }

        try {

            dbConnection = DriverManager.getConnection(
                            DB_CONNECTION + db, DB_USER,DB_PASSWORD);
            return dbConnection;

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

        return dbConnection;

    }
}
akmozo
  • 9,829
  • 3
  • 28
  • 44
realworldjoe
  • 1
  • 1
  • Things you should be doing: 1) get the connection only once, outside the loop 2) use bind variables in the insert statement 3) [batch your inserts](http://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc). – Mick Mnemonic Jun 04 '16 at 19:19
  • @MickMnemonic Sorry for the newb question. But how to alter my code to get DB connection only once while still running through my loop?? – realworldjoe Jun 04 '16 at 20:11
  • Move `dbConnection = getDBConnection(db);` to the beginning of the method where you declare the variable. – Mick Mnemonic Jun 04 '16 at 20:15
  • When you get done with this, I will show you the fast way to bring it in. – Drew Jun 04 '16 at 20:56

0 Answers0