StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs this database dump design ok?
text
Body
copied!<p>I have written a Java program to do the following and would like opinions on my design:</p> <ol> <li>Read data from a CSV file. The file is a database dump with 6 columns.</li> <li>Write data into a MySQL database table.</li> </ol> <p>The database table is as follows:</p> <pre><code> CREATE TABLE MYTABLE ( ID int PRIMARY KEY not null auto_increment, ARTICLEID int, ATTRIBUTE varchar(20), VALUE text, LANGUAGE smallint, TYPE smallint ); </code></pre> <ol> <li>I created an object to store each row.</li> <li>I used OpenCSV to read each row into a list of objects created in 1.</li> <li>Iterate this list of objects and using PreparedStatements, I write each row to the database.</li> </ol> <p>The solution should be highly amenable to the changes in requirements and demonstrate good approach, robustness and code quality.</p> <p>Does that design look ok?</p> <p>Another method I tried was to use the 'LOAD DATA LOCAL INFILE' sql statement. Would that be a better choice?</p> <p>EDIT: I'm now using OpenCSV and it's handling the issue of having commas inside actual fields. The issue now is nothing is writing to the DB. Can anyone tell me why?</p> <pre><code>public static void exportDataToDb(List<Object> data) { Connection conn = connect("jdbc:mysql://localhost:3306/datadb","myuser","password"); try{ PreparedStatement preparedStatement = null; String query = "INSERT into mytable (ID, X, Y, Z) VALUES(?,?,?,?);"; preparedStatement = conn.prepareStatement(query); for(Object o : data){ preparedStatement.setString(1, o.getId()); preparedStatement.setString(2, o.getX()); preparedStatement.setString(3, o.getY()); preparedStatement.setString(4, o.getZ()); } preparedStatement.executeBatch(); }catch (SQLException s){ System.out.println("SQL statement is not executed!"); } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload