0

I have created a connection with Mysql and java program via jdbc. Now I want to populate the tables in the mysql database. How do I parse the data into the tables from the java code?

I have two input data files.The format of file is like: 

"AAH196","17:13:00","02:49:00",287,166.03,"Austin","TX","Virginia Beach","VA"
"AAH3727","21:38:00","03:04:00",273,176.44,"Los Angeles","CA","Colorado Springs","CO"
  • possible duplicate of [splitting of string in java](http://stackoverflow.com/questions/4104298/splitting-of-string-in-java) – Bart Kiers Nov 05 '10 at 20:22

2 Answers2

1

You can use the LOAD DATA INFILE SQL command.

Jacob Relkin
  • 161,348
  • 33
  • 346
  • 320
0

An easy solution: read a single line an use the content as a part of an SQL INSERT command:

List<String> lines = getAllLinesFromFile(file);
for (String line: lines) {
  String query = "INSERT INTO \"TABLE\" (COL1, ..., COL9) VALUES("+line+");";
  stmt.executeUpdate(query);
}

Replace TABLE with your actual tablename and COL1, ..., COL9 with an enumeration of your column names. There may be solutions with a better database performance (like using prepared statements) but the algorithm is easy and sufficient to get some data into the database.

Andreas Dolk
  • 113,398
  • 19
  • 180
  • 268
  • Hope, there ist no line with "","",",287,166.03,"","","",""); DROP TABLE tablename;-- in that file. – Christian Kuetbach Nov 05 '10 at 07:47
  • @ckuetbach - me too ;-) But I think, [sql injection](http://en.wikipedia.org/wiki/SQL_injection) is out of scope of shilps concerns... – Andreas Dolk Nov 05 '10 at 08:03
  • @Andreas_D Yes, you are probably right, but sql injection is an important Issue. Lots of sequrity-holes are opend by SQL-injection. – Christian Kuetbach Nov 05 '10 at 08:33
  • i also have that type of data in my files said by ckuetbach. –  Nov 05 '10 at 11:10
  • @shipls - if this is the case (and you're not playing with malicious code to hack databases...), don't use my quick solution **and** don't use a csv parser, because with sql injection code in data lines the complete file isn't a CSV file anymore. Clean up the data files or ask for files that don't have corrupt line. – Andreas Dolk Nov 05 '10 at 11:20
  • then what should I do? If I cant use csv reader and your method? –  Nov 05 '10 at 11:28
  • My code will be chekced on various types of data files. Tehy can have redundant data or they have null value in the files. –  Nov 05 '10 at 11:29
  • @shilps - before providing more help: please edit one of your various question, add the information, that some lines are malformed and contain sql injection code and add the information **why** you want to send that data to the database. – Andreas Dolk Nov 05 '10 at 11:40