CODEWITHSUNDEEP
javasqljooq
Anurag Sahu
Anurag Sahu

Reputation: 161

Dump records from Database to file in CSV format

I am reading from Database using Jooq and dumping the records in File. I want to dump the records in CSV format and new line between each records. I looked into Jooq Docs and researched on Google but didn't find any help. Jooq support CSV format but how do I incorporate the feature in my use case.

I am using the following code to dump the record in File: 

SelectQuery<Record> query = getDslContext().selectQuery();       

        try {

            @Cleanup FileOutputStream fileOutputStream = new FileOutputStream(FILE);
            @Cleanup GZIPOutputStream gzipOutputStream = new GZIPOutputStream(fileOutputStream);
            @Cleanup OutputStreamWriter outputStreamWriter = new OutputStreamWriter(gzipOutputStream, charsetName);
            @Cleanup BufferedWriter writer = new BufferedWriter(outputStreamWriter);
            @Cleanup Cursor<Record> cursor = query.fetchSize(MAX_FETCH_SIZE).fetchLazy();
            while(cursor.hasNext()){
                Result<Record> results = cursor.fetchNext(MAX_FETCH_SIZE);
                for (Record record : results){
                    writer.write(record.formatJSON());
                    writer.newLine();
                }
            }

Upvotes: 1

Views: 438

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 221380

Use the CSVFormat.newline() property:

String csv = results.formatCSV(new CSVFormat().newline("\n\n"));

Alternatively, since you're iterating your cursor on a row subset basis, you might want to remove the headers, too:

String csv = results.formatCSV(new CSVFormat().newline("\n\n").header(false));

This will also produce two trailing newlines at the end, which you can easily remove if they're not desired:

csv = csv.trim();

Upvotes: 1

wallek876
wallek876

Reputation: 3269

You can format the records directly from Result<Record> results without needing to manually iterate over each record.

The only thing is that since you are fetching the records in batches of MAX_FETCH_SIZE, the formatted csv of each batch will contain the headers, you probably will need to only write the headers in the first batch.

@Cleanup Cursor<Record> cursor = query.fetchSize(2).fetchLazy();

boolean includeHeaders = true;
int dataStartIndex = 0;

while(cursor.hasNext()){

    Result<Record> results = cursor.fetchNext(2);
    String csv = results.formatCSV();

    if (includeHeaders) {
        writer.write(csv);
        dataStartIndex = csv.indexOf('\n') + 1;
        includeHeaders = false;
        continue;
    }

    writer.write(csv.substring(dataStartIndex));
}

Upvotes: 0

Related Questions