CODEWITHSUNDEEP
javamysqljdbc
shilps
shilps

Reputation:

data parsing from a file into java and then into a mysql database

I have .Data file given in the above format . I am writing a program in java that will take the values from the .data file and put it in the buffer. MY java program is connected to Mysql(windows) via JDBC. So I need to read the values from the file given in the above format and put it the buffer like 

Insert Into building values ("--", "---",----)

In this way, i store these values and jdbc will populate the database tables on Mysql(windows). Please tell me teh best way.

Upvotes: 1

Views: 2550

Answers (5)

Andreas Dolk
Andreas Dolk

Reputation: 114757

This is the fourth question for the same task... If your data file is well formatted like in the example you provided, then you don't have to split the line into values:

Source:   "AAH196","Austin","TX","Virginia Beach","VA"
Target:   INSERT INTO BUILDING VALUES("AAH196","Austin","TX","Virginia Beach","VA");
      <=> "INSERT INTO BUILDING VALUES(" + Source + ");"

Just take a complete row from you csv file and concatenate a SQL expression.

(see my answer to question 1 of 4 - BTW, if SQL INJECTION is a potential problem, splitting a line of values is not a solution too)

Upvotes: 1

sfussenegger
sfussenegger

Reputation: 36096

This would be a perfect job for Groovy. Here's a gist with a small skeleton script to build upon.

Upvotes: 0

Peter Knego
Peter Knego

Reputation: 80330

The data is in CSV format, so use a CSV library to parse the file and then just add some JDBC code to insert this into database.

Or just call MySQL CSV import command from Java:

try {
    // Execute a command with arguments
    String command = "mysqlimport [options] db_name textfile1 [textfile2 ...]";
    Process child = Runtime.getRuntime().exec(command);

} catch (IOException e) {
}

Upvotes: 1

Erhan Bagdemir
Erhan Bagdemir

Reputation: 5327

you can bind your csv with java beans using opencsv. http://opencsv.sourceforge.net/

you can make these beans persistent using an ORM framework, like Hibernate, Cayenne or with JPA which're based on annotations and map your fields to tables easily without creating any sql statement.

Upvotes: 0

Sean Patrick Floyd
Sean Patrick Floyd

Reputation: 298818

Check out the answers to this question for reading file lines and splitting them into chunks. I know the question says Groovy: but most answers are Java. Then insert the values you retrieved via JDBC.

Actually, since your data file is obviously CSV, you could also use a CSV libary like OpenCSV to read the values.

Upvotes: 1

Related Questions