Write MySQL Upsert Statements to a File

This example shows how to generate MySQL upsert statements from records and save them to a SQL script instead of running them against a database. MySqlUpsertWriter writes one INSERT ... ON DUPLICATE KEY UPDATE statement per record: when the table already holds a row with the same primary or unique key, MySQL updates every column from the new values instead of inserting. The PostgreSQL version of this example is Write PostgreSQL Upsert Statements to a File.

Generating a script instead of executing the statements is useful when the statements must be reviewed, handed to a DBA or replayed in another environment. Values are written as quoted strings here because CSVReader reads every field as text.

Input CSV file

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;
import java.io.FileWriter;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.sql.mysql.MySqlUpsertWriter;

public class WriteMySqlUpsertStatementsToAFile {

    public static void main(String[] args) throws Throwable {
        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
                .setFieldNamesInFirstRow(true);

        DataWriter writer = new MySqlUpsertWriter("credit_balance",
                new FileWriter("example/data/output/credit-balance-upsert-mysql.sql"))
                .setPretty(true);

        Job.run(reader, writer);
    }

}

Code Walkthrough

1. A CSVReader reads credit-balance-01.csv with field names in the first row.
2. A MySqlUpsertWriter is created for the credit_balance table with a FileWriter on example/data/output/credit-balance-upsert-mysql.sql. The field names become the column names. setPretty(true) splits each statement over several lines.
3. Job.run() transfers the records, writing one statement per record and closing the file.

Output SQL file

INSERT INTO `credit_balance` (`Account`, `LastName`, `FirstName`, `Balance`, `CreditLimit`, `AccountCreated`, `Rating`)
VALUES ('101', 'Reeves', 'Keanu', '9315.45', '10000.00', '1/17/1998', 'A')
ON DUPLICATE KEY UPDATE `Account` = VALUES(`Account`), `LastName` = VALUES(`LastName`), `FirstName` = VALUES(`FirstName`), `Balance` = VALUES(`Balance`), `CreditLimit` = VALUES(`CreditLimit`), `AccountCreated` = VALUES(`AccountCreated`), `Rating` = VALUES(`Rating`);
INSERT INTO `credit_balance` (`Account`, `LastName`, `FirstName`, `Balance`, `CreditLimit`, `AccountCreated`, `Rating`)
VALUES ('312', 'Butler', 'Gerard', '90.00', '1000.00', '8/6/2003', 'B')
ON DUPLICATE KEY UPDATE `Account` = VALUES(`Account`), `LastName` = VALUES(`LastName`), `FirstName` = VALUES(`FirstName`), `Balance` = VALUES(`Balance`), `CreditLimit` = VALUES(`CreditLimit`), `AccountCreated` = VALUES(`AccountCreated`), `Rating` = VALUES(`Rating`);
INSERT INTO `credit_balance` (`Account`, `LastName`, `FirstName`, `Balance`, `CreditLimit`, `AccountCreated`, `Rating`)
VALUES ('868', 'Hewitt', 'Jennifer Love', '0', '17000.00', '5/25/1985', 'B')
ON DUPLICATE KEY UPDATE `Account` = VALUES(`Account`), `LastName` = VALUES(`LastName`), `FirstName` = VALUES(`FirstName`), `Balance` = VALUES(`Balance`), `CreditLimit` = VALUES(`CreditLimit`), `AccountCreated` = VALUES(`AccountCreated`), `Rating` = VALUES(`Rating`);
INSERT INTO `credit_balance` (`Account`, `LastName`, `FirstName`, `Balance`, `CreditLimit`, `AccountCreated`, `Rating`)
VALUES ('761', 'Pinkett-Smith', 'Jada', '49654.87', '100000.00', '12/5/2006', 'A')
ON DUPLICATE KEY UPDATE `Account` = VALUES(`Account`), `LastName` = VALUES(`LastName`), `FirstName` = VALUES(`FirstName`), `Balance` = VALUES(`Balance`), `CreditLimit` = VALUES(`CreditLimit`), `AccountCreated` = VALUES(`AccountCreated`), `Rating` = VALUES(`Rating`);
INSERT INTO `credit_balance` (`Account`, `LastName`, `FirstName`, `Balance`, `CreditLimit`, `AccountCreated`, `Rating`)
VALUES ('317', 'Murray', 'Bill', '789.65', '5000.00', '2/5/2007', 'C')
ON DUPLICATE KEY UPDATE `Account` = VALUES(`Account`), `LastName` = VALUES(`LastName`), `FirstName` = VALUES(`FirstName`), `Balance` = VALUES(`Balance`), `CreditLimit` = VALUES(`CreditLimit`), `AccountCreated` = VALUES(`AccountCreated`), `Rating` = VALUES(`Rating`);