Java file parsing into database

Question

I have to parse the file movies.txt which contains the data for 3451 movies. i have to read the movies.txt file and store all the movie data into MySQL database table movies. For each movie, data is collected under 11 columns in movies.txt file.. so how do i do it in java need suggestions

can you show us som lines of the movies.txt file ? Are the lines comma seperated ? You need to give us some more details — baliman, Jan 09 '17 at 07:11
http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table — titogeo, Jan 09 '17 at 07:11
sure... here is the one line of the file 11:14;Feature Film;Greg Marcks;7.2;86;2003;comedy,crime,drama;31782; ; ;http://www.imdb.com/title/tt0331811/; there may be blank also in that case it should insert null or empty vaue — kps, Jan 09 '17 at 07:31
Possible duplicate of [data parsing from a file into java and then into a mysql database](http://stackoverflow.com/questions/4104456/data-parsing-from-a-file-into-java-and-then-into-a-mysql-database) — Guillaume S, Jan 09 '17 at 09:27

Bob · Answer 1 · 2017-01-11T08:23:39.143

0

Hey there are thousand of ways to do it. I think the best way would creating a Class Movie, loading File and insertering into DB.

But here is an other way to do it.

private void readFile() throws IOException, SQLException{
    File f = new File("C:/Movies.txt");

    BufferedReader in = new BufferedReader(new FileReader(f));
    Connection dbConnection = getConnection();
    String line;

    while((line = in.readLine()) != null){
        String[] columns;
        columns = line.split(";");
        insertRecordIntoTable(dbConnection, columns);


    }
    in.close();     
}



private void insertRecordIntoTable(Connection dbConnection,String[] columns) throws SQLException {


    PreparedStatement preparedStatement = null;

    String insertTableSQL = "INSERT INTO MOVIES"
            + "(MOVIE_ID, NAME, AUTHOR, DUNNO) VALUES"
            + "(?,?,?,?)";

    try {

        preparedStatement = dbConnection.prepareStatement(insertTableSQL);

        preparedStatement.setString(1, columns[0]);
        preparedStatement.setString(2, columns[1]);
        preparedStatement.setString(3, columns[2]);
        preparedStatement.setString(4, columns[3]);
        //and more inserts....


        // execute insert SQL stetement
        preparedStatement.executeUpdate();

        System.out.println("Record is inserted into Movies table!");

    } catch (SQLException e) {

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

    } finally {

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

    }

}

readFile reads ur File and calls the insert Method every line. You also have to create a method (getConnection) to create ur DB connection. If u have any questions just ask :)

edited Jan 11 '17 at 08:23

answered Jan 09 '17 at 07:54

Bob

74
1
9

in above example you are mentioned to insert each col manually but in my example there more than 3000 movies means more than 3000 rows . its bad idea to insert so many rows manually. i hope u got my problem. – kps Jan 09 '17 at 09:04
i got ur problem but with 3000 rows there shouldnt be a problem. but u have to call the readFile method just ONE time. so its not really manually. u could also build ur own big insert statement. and after executing it. but i think the time difference wont be that much. – Bob Jan 09 '17 at 09:24
Ya this is good. but they are some columns with multiple values with comma separated so how can i insert those values in single coloumn . – kps Jan 09 '17 at 14:04
i am getting number format exception for that case ex: java.lang.NumberFormatException: For input string: "comedy,crime,drama" – kps Jan 09 '17 at 14:05
so some are seperated by `,` and some by `;` ? – Bob Jan 09 '17 at 14:16
yes exactly but those comma separated values should be inserted in single column only ill just type a one line 11:14;Feature Film;Greg Marcks;7.2;86;2003;comedy,crime,drama;31782; ; ;http://www.imdb.com/title/tt0331811/; – kps Jan 10 '17 at 05:06
i mean actually column should be split by ; but in some column they are multiple values splited by , .. i hope you got it. refer the line which i given in my previous comments. in simple all the columns will have single value only one column has multiple values with comm separated.. – kps Jan 10 '17 at 06:48
Can u please edit ur question by adding ur current code? I need ur readFile and ur insert function! because u should be able to insert a String with `,` . Or do you have TeamViewer? – Bob Jan 10 '17 at 08:55
u have to make sure that ur column with "comedy,crime,drama" is type varchar in ur database and ur inserting by preparedStatement.setString(...) – Bob Jan 10 '17 at 08:57
I Resolved that issue... now the another problem is i have created a table with the following query CREATE TABLE movies( movie_id VARCHAR(100) NOT NULL, movie_title VARCHAR(100) NOT NULL, movie_type VARCHAR(100) NOT NULL, movie_director VARCHAR(100) NOT NULL, movie_imdbrating INT, movie_runtime INT , movie_year INT , movie_generes VARCHAR(100), movie_ImdbVotes INT , movie_imdbtop VARCHAR(100), movie_1001mustsee VARCHAR(100), movie_url VARCHAR(100) NOT NULL, PRIMARY KEY ( movie_id ) ); is this the right way – kps Jan 10 '17 at 11:34
no error.. when i try to do, preparedStatement.setString(11, columns[11]); i will get error as : java.lang.ArrayIndexOutOfBoundsException: 11.... if i comment this statement i will get error as : No value specified for parameter 11... not understanding what the problem is.. – kps Jan 11 '17 at 05:41
Okay but i just can help you when u post ur hole code. And if possible also the first 5 lines of ur movies.txt – Bob Jan 11 '17 at 08:21
i am unable to paste full code so i will raise one more question . you can refer – kps Jan 11 '17 at 08:48
okay. but i think ur problem is that u start ur arraycount with 1. U should start with 0. preparedStatement.setString(1, columns[0]); preparedStatement.setString(2, columns[1]); ... – Bob Jan 11 '17 at 08:56
okay. but i think ur problem is that u start ur arraycount with 1. U should start with 0. preparedStatement.setString(1, columns[0]); preparedStatement.setString(2, columns[1]); – Bob Jan 11 '17 at 08:56
refer this http://stackoverflow.com/questions/41586512/java-lang-arrayindexoutofboundsexception-11-facing-this-error-with-the-below-co for full code – kps Jan 11 '17 at 09:00
i cant answer this one because it marked as duplicate. look my other answer here... – Bob Jan 11 '17 at 10:49

score 0 · Accepted Answer · answered Jan 11 '17 at 10:51

arrays are zero based index and every sql index are 1 based. so just put this in ur code:

try {

        preparedStatement = dbConnection.prepareStatement(insertTableSQL);

        preparedStatement.setString(1, columns[0]);
        preparedStatement.setString(2, columns[1]);
        preparedStatement.setString(3, columns[2]);
        preparedStatement.setString(4, columns[3]);
        preparedStatement.setString(5, columns[4]);
        preparedStatement.setString(6, columns[5]);
        preparedStatement.setString(7, columns[6].replaceAll(",", ""));
        preparedStatement.setString(8, columns[7]);
        preparedStatement.setString(9, columns[8]);
        preparedStatement.setString(10, columns[9]);
        preparedStatement.setString(11, columns[10]);

        //and more inserts....
        // execute insert SQL stetement
        preparedStatement.executeUpdate();

        System.out.println("Record is inserted into Movies table!");

    }

I think this is why u got the Interger parse exeption before.

Java file parsing into database

2 Answers2