CODEWITHSUNDEEP
javasqloracle-databasecsv
Mike3355
Mike3355

Reputation: 12081

Create SQL insert statements to CSV file

I was hoping to get some advice on a tricky issue. I am reading data from a Oracle database and wanting to generate insert statement from the data I get. Each table will have a different number of values.

Here is the method currently writing the columnValue tot a csv file. Which prints to like in this format 1708||1||C||0||A||2018-06-19 00:00:00.0||2118-06-30 00:00:00.0||. 

private void writeToCsvFileWithDatabase() throws SQLException, IOException {

        File file = new File("data.csv");
        selectQueries = new SelectQueries();

        ArrayList<String> queries = SelectQueries.queries();
        ResultSet rs;
        ResultSetMetaData rsmd;

        if (file.exists()){
            Statement stmt = dbConnection.createStatement();
            BufferedWriter writer = new BufferedWriter(new FileWriter("data.csv"));

            for (String query: queries) {
                rs = stmt.executeQuery(query);
                rsmd = rs.getMetaData();
                int columnsNumber = rsmd.getColumnCount();

                while (rs.next()) {
                    for (int i = 1; i <= columnsNumber; i++) {
                        if (i > 1) System.out.print(",  ");
                        String columnValue = rs.getString(i);
                        String tableName = query.substring(query.lastIndexOf(" ")+1);
                        writer.write(columnValue+"||"); //I wish I could just generate the inserts with a library here
                        System.out.print(tableName);
                    }
                    System.out.println("");

                }
            }

        } else {
            throw new FileNotFoundException("data.csv was not found in ReadDatabase.class");
        }

    }

I either need code the logic to create the insert statements:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

or find a library that will take the csv file and generate the inserts for me.

I know they're are tools that can do this but I for what I am doing I cannot use them.

Advice?

-------------------Issue 1--------------------

I am currently having an issue with previous values being appended to to insert statements. Here is an example:

INSERT INTO CAMPAIGN (CAMPAIGN_ID, SHOP_ID, CAMPAIGN_TYPE, SORT_ORDER, STATUS, VALID_FROM, VALID_TILL, CREATED_AT, MODIFIED_AT, CUSTOM_GRID_LAYOUT_CSS, IMAGE_URL, KEY, SHOW_PRODUCTS_FILTER, MOBILE_APP_IMAGE_URL, LAYOUT_ID, SHOW_OWN_BRAND, VALIDATION_STATUS, CAMPAIGN_USAGE_ID, STORE_END_DATE, PAGING_ALLOWED, CAROUSEL_BUTTON_TEXT_COLOR, CAROUSEL_BUTTON_BG_COLOR, CAROUSEL_BUTTON1_LABEL, CAROUSEL_BUTTON1_URL, CAROUSEL_BUTTON2_LABEL, CAROUSEL_BUTTON2_URL, CAROUSEL_HTML_OVERLAY, MOBILE_APP_TEASER_URL, CAROUSEL_CLAIM_URL, CAROUSEL_HERO_URL, CAROUSEL_BOX_COLOR, AVOID_CAMPAIGN_NAV_TEASER, CAROUSEL_BUTTON1_NEW_TAB, CAROUSEL_BUTTON2_NEW_TAB, SALE_QUALIFICATION_ID, SALE_TARGET_CAMPAIGN) 
VALUES (952, 1, 'C', 0, 'A', to_timestamp('2016-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2016-11-23 14:58:53.878264 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2018-07-01 08:02:57.791698 AM', 'DD-MON-RR HH.MI.SSXFF AM'), null, "campaign/0/0/0/0/9/5/2/4fd7809f-aee4-4d45-a2c7-4a258e09a709.jpg", null, 1, "campaign/0/0/0/0/9/5/2/8504e780-f69f-42c5-93c7-5e163fe4140f.jpg", 5_COL, 0, null, 4, to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 0, null, null, "Artikel ab Do.,10.11.", "https://www.lidl.de", "Unsere Online-Prospekte", "https://www.lidl.de", null, "campaign/0/0/0/0/9/5/2/8943afff-0823-4c35-aad2-2332172bc675.jpg", "campaign/0/0/0/0/9/5/2/96f9c073-fc1c-481d-a2d6-903f8770fecb.png", "campaign/0/0/0/0/9/5/2/81c7c88a-c4fb-4e26-8d92-94716dad642b.jpg", null, 0, 0, 0, null, 0);

this is a good Insert statement on the first loop but look at what happens on the second loop:

    INSERT INTO CAMPAIGN (CAMPAIGN_ID, SHOP_ID, CAMPAIGN_TYPE, SORT_ORDER, STATUS, VALID_FROM, VALID_TILL, CREATED_AT, MODIFIED_AT, CUSTOM_GRID_LAYOUT_CSS, IMAGE_URL, KEY, SHOW_PRODUCTS_FILTER, MOBILE_APP_IMAGE_URL, LAYOUT_ID, SHOW_OWN_BRAND, VALIDATION_STATUS, CAMPAIGN_USAGE_ID, STORE_END_DATE, PAGING_ALLOWED, CAROUSEL_BUTTON_TEXT_COLOR, CAROUSEL_BUTTON_BG_COLOR, CAROUSEL_BUTTON1_LABEL, CAROUSEL_BUTTON1_URL, CAROUSEL_BUTTON2_LABEL, CAROUSEL_BUTTON2_URL, CAROUSEL_HTML_OVERLAY, MOBILE_APP_TEASER_URL, CAROUSEL_CLAIM_URL, CAROUSEL_HERO_URL, CAROUSEL_BOX_COLOR, AVOID_CAMPAIGN_NAV_TEASER, CAROUSEL_BUTTON1_NEW_TAB, CAROUSEL_BUTTON2_NEW_TAB, SALE_QUALIFICATION_ID, SALE_TARGET_CAMPAIGN) 
VALUES (952, 1, 'C', 0, 'A', to_timestamp('2016-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2016-11-23 14:58:53.878264 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2018-07-01 08:02:57.791698 AM', 'DD-MON-RR HH.MI.SSXFF AM'), null, "campaign/0/0/0/0/9/5/2/4fd7809f-aee4-4d45-a2c7-4a258e09a709.jpg", null, 1, "campaign/0/0/0/0/9/5/2/8504e780-f69f-42c5-93c7-5e163fe4140f.jpg", 5_COL, 0, null, 4, to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 0, null, null, "Artikel ab Do.,10.11.", "https://www.lidl.de", "Unsere Online-Prospekte", "https://www.lidl.de", null, "campaign/0/0/0/0/9/5/2/8943afff-0823-4c35-aad2-2332172bc675.jpg", "campaign/0/0/0/0/9/5/2/96f9c073-fc1c-481d-a2d6-903f8770fecb.png", "campaign/0/0/0/0/9/5/2/81c7c88a-c4fb-4e26-8d92-94716dad642b.jpg", null, 0, 0, 0, null, 0);
 (953, 1, 'C', 0, 'A', to_timestamp('2016-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2016-11-23 15:09:40.201996 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2018-07-01 08:02:57.791756 AM', 'DD-MON-RR HH.MI.SSXFF AM'), null, "campaign/0/0/0/0/9/5/3/7c019b7c-5cf7-415c-bbe1-7316083a1339.jpg", null, 1, "campaign/0/0/0/0/9/5/3/c3c97d4c-4018-4e6f-91dd-00417a0e9f08.jpg", 5_COL, 0, null, null, to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 0, null, null, "Ab Do., 24.11.", "https://www.lidl.de", "Unsere Online-Prospekte", "https://www.lidl.de", null, "campaign/0/0/0/0/9/5/3/78e01f6a-435c-4aa4-af17-6231f13abb55.jpg", "campaign/0/0/0/0/9/5/3/a268d712-26fc-4eac-bf58-ebf7d38b2b12.png", "campaign/0/0/0/0/9/5/3/43bacb3e-6b9e-4ada-8b4c-3e7b5d9d9090.jpg", null, 0, 0, 0, null, 0);

The first insert statements is appended to the seconds ones. This will happen every single time till I have a huge amount of duplicates in each insert statement. I am not sure why it keep appending the all the previous statements over and over.

Current code:

if (file.exists()){
            Statement stmt = dbConnection.createStatement();
            BufferedWriter writer = new BufferedWriter(new FileWriter("data.csv"));

            for (String query: queries) {
                rs = stmt.executeQuery(query);
                rsmd = rs.getMetaData();
                int columnsNumber = rsmd.getColumnCount();
                TABLE_NAME = query.substring(query.lastIndexOf(" ") + 1);
                sb.append(TABLE_NAME).append(" (");
                for (int i = 0; i < columnsNumber; ++i) {
                    String name = rsmd.getColumnName(i + 1);
                    if (i > 0) {
                        sb.append(", ");
                    }

                    sb.append(name);
                }
                sb.append(") VALUES ");

                // generate rows
                while (rs.next()) {

                    sb.append("(");
                    //might not need for loop anymore..
                    for (int i = 0; i < columnsNumber; i++) {
                        if (i > 0) {
                            sb.append(", ");
                        }
                        String columnValue = rs.getString(i + 1);

                        if (columnValue == null) {
                            sb.append("null");
                        } else if (columnValue.contains("-") && Character.isDigit(columnValue.charAt(0))) {
                            columnValue = convertToSqlDate(columnValue);
                            sb.append(columnValue);
                        } else if (Character.isLetter(columnValue.charAt(0)) && columnValue.length() > 2) {
                            //is a varchar
                            sb.append("\"");
                            sb.append(columnValue);
                            sb.append("\"");
                        } else if (Character.isLetter(columnValue.charAt(0))) {
                            //is a Character
                            sb.append("\'");
                            sb.append(columnValue);
                            sb.append("\'");
                        } else {
                            sb.append(columnValue);
                        }
                    }
                    sb.append(");");
                    sb.append("\n");
                    writer.write(sb.toString());

                }
            }
            //writer.write(sb.toString());

        } else {
            throw new FileNotFoundException("data.csv was not found in ReadDatabase.class");
        }

Upvotes: 3

Views: 1273

Answers (2)

Yevgen
Yevgen

Reputation: 1657

You can use DBUtils from Apache Commons to do some work for you. It has RowProcessor class which allows you to get ResultSet as a map, so it will be easier to process it for your purposes. Further steps would be to add custom method to that class and to define template for your statements.

Consider the example. 

class CustomRowProcessor extends BasicRowProcessor {
  List<String> getInsertList(ResultSet rs) throws SQLException {
    List<String> res = new ArrayList<>();
    while (rs.next()) {
      String tableName = rs.getMetaData().getTableName(1);
      String template = "INSERT INTO %s (%s) VALUES (%s);";
      String columns = toMap(rs).keySet().stream()
          .collect(Collectors.joining(", "));
      String values = toMap(rs).values().stream()
          .map(v -> "'" + v + "'")
          .collect(Collectors.joining(", "));
      res.add(String.format(template, tableName, columns, values));
    }
    return res;
  }
}

Having the above and DBUtils on classpath, a significant part of your original code can be refactored as follows.

for (String query : queries) {
  rs = stmt.executeQuery(query);
  for (String insert : new CustomRowProcessor().getInsertList(rs)) {
    writer.write(insert + "\n");
  }
}

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Here is some pseudo code which generates an insert statement based on a JDBC result set:

String TABLE_NAME = "yourTable";
StringBuilder sb = new StringBuilder("INSERT INTO ");
sb.append(TABLE_NAME).append(" (");

rs = stmt.executeQuery(query);
rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
for (int i=0; i < columnsNumber; ++i) {
    String name = rsmd.getColumnName(i+1);
    if (i > 0) {
        sb.append(", ");
    }
    sb.append(name);
}
sb.append(")\nVALUES\n");

// now generate rows
boolean start = true;
while (rs.next()) {
    if (start) {
        start = false;
    }
    else {
        sb.append(",\n");
    }
    sb.append("(");
    for (int i=0; i < columnsNumber; i++) {
        if (i > 0) {
            sb.append(", ");
        }
        String columnValue = rs.getString(i+1);
        sb.append(columnValue);
    }
    sb.append(")");
}

sb.append(";");

Upvotes: 2

Related Questions