jeudi 25 avril 2019

Exporting SqlRowSet to a file

I want to export the results of an SQL query, fired through JDBC, to a file; and then import that result, at some point later.

I'm currently doing it by querying the database through a NamedParameterJdbcTemplate of Spring which returns a SqlRowSet that I can iterate. In each iteration, I extract desired fields and dump the result into a CSV file, using PrintWriter.

final SqlRowSet rs = namedJdbcTemplate.queryForRowSet(sql,params);
while (rs.next()) {

The problem is that when I read back the file, they are all Strings and I need to cast them to their proper types, e.g Integer, String, Date etc.

while (line != null) {
    String[] csvLine = line.split(";");

    Object[] params = new Object[14]; 
    params[0] = csvLine[0];
    params[1] = csvLine[1];
    params[2] = Integer.parseInt(csvLine[2]);
    params[3] = csvLine[3];
    params[4] = csvLine[4];
    params[5] = Integer.parseInt(csvLine[5]);
    params[6] = Integer.parseInt(csvLine[6]);
    params[7] = Long.parseLong(csvLine[7]);
    params[8] = formatter.parse(csvLine[8]);
    params[9] = Integer.parseInt(csvLine[9]);
    params[10] = Double.parseDouble(csvLine[10]);
    params[11] = Double.parseDouble(csvLine[11]);
    params[12] = Double.parseDouble(csvLine[12]);
    params[13] = Double.parseDouble(csvLine[13]);

    batchParams.add(params);
    line = reader.readLine();
}

Is there a better way to export this SqlRowSet to a file in order to facilitate the import process later on; some way to store the schema for an easier insertion into the DB?

Aucun commentaire:

Enregistrer un commentaire