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
4
-
can you show us som lines of the movies.txt file ? Are the lines comma seperated ? You need to give us some more detailsbaliman– baliman2017-01-09 07:11:17 +00:00Commented Jan 9, 2017 at 7:11
-
stackoverflow.com/questions/3635166/…titogeo– titogeo2017-01-09 07:11:39 +00:00Commented Jan 9, 2017 at 7:11
-
sure... here is the one line of the file 11:14;Feature Film;Greg Marcks;7.2;86;2003;comedy,crime,drama;31782; ; ;imdb.com/title/tt0331811; there may be blank also in that case it should insert null or empty vauekps– kps2017-01-09 07:31:38 +00:00Commented Jan 9, 2017 at 7:31
-
Possible duplicate of data parsing from a file into java and then into a mysql databaseGuillaume S– Guillaume S2017-01-09 09:27:41 +00:00Commented Jan 9, 2017 at 9:27
Add a comment
|
2 Answers
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.
Comments
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 :)
17 Comments
kps
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.
Bob
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.
kps
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
i am getting number format exception for that case ex: java.lang.NumberFormatException: For input string: "comedy,crime,drama"
Bob
so some are seperated by
, and some by ; ? |