CSV Iterator

package com.css; import java.io.File; import java.io.FileReader; import java.io.IOException; import java.sql.*; import java.util.ArrayList; import java.util.Collection; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import org.json.simple.JSONObject; import com.fasterxml.jackson.databind.MappingIterator; import com.fasterxml.jackson.databind.ObjectMapper; import com.fasterxml.jackson.dataformat.csv.CsvMapper; import com.fasterxml.jackson.dataformat.csv.CsvSchema; import com.fasterxml.jackson.dataformat.csv.impl.CsvReader; public class JSONUtilities { static File csvFile = new File("d:\\sapient\\sample.csv"); static File jsonFile = new File("d:\\sapient\\sample.csv.json"); // JDBC driver name and database URL static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver"; static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl"; // Database credentials static final String USER = "system"; public static void main(String[] args) throws Exception { convertCSVToJSON(csvFile); } public static void convertCSVToJSON(File csvFile) throws IOException { CsvSchema bootstrap = CsvSchema.emptySchema().withHeader().withoutEscapeChar(); CsvMapper csvMapper = new CsvMapper(); MappingIterator> mappingIterator = csvMapper.reader(Map.class).with(bootstrap).readValues(csvFile); while (mappingIterator.hasNext()) { @SuppressWarnings("rawtypes") Map row = mappingIterator.next(); // System.out.println("Column names: " +row.keySet()); @SuppressWarnings("rawtypes") Set headerRow = row.keySet(); // Set headerColumnSet = new HashSet<>(); ArrayList headerColumnSet = new ArrayList(); @SuppressWarnings("unchecked") Iterator headerColumns = headerRow.iterator(); while (headerColumns.hasNext()) { String value = headerColumns.next(); // System.out.println("value= " + value ); headerColumnSet.add(value); } @SuppressWarnings("unchecked") Collection collection = row.values(); Iterator columns = collection.iterator(); // while loop int columnNum = 0; while (columns.hasNext()) { String columnVal = columns.next(); String headerColumn = headerColumnSet.get(columnNum); System.out.println(headerColumn + ":" + columnVal); columnNum++; } } ObjectMapper mapper = new ObjectMapper(); mapper.writerWithDefaultPrettyPrinter().writeValue(jsonFile, mappingIterator); System.out.println("output JSON file has been created at " + jsonFile); } }

DB2JSON

package com.css;

import org.json.simple.JSONObject;

import com.fasterxml.jackson.core.JsonProcessingException;

import com.fasterxml.jackson.databind.ObjectMapper;

import org.json.simple.JSONArray;

import java.io.File;

import java.io.FileWriter;

import java.io.IOException;

import java.sql.*;

public class DB2JSON {

private Connection connection = null;

static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";

static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";

static File jsonFile = new File("d:\\sapient\\sample.sql.json");

// Database credentials

static final String USER = "system";

static final String PASS = "oracle";

public static void main(String a[]) throws JsonProcessingException {

DB2JSON db = new DB2JSON(DB_URL, USER, PASS, JDBC_DRIVER);

JSONArray resultJson = db.getResultsJSONArray("select * from all_tab_cols where table_name =   'LOANTABLE'");

ObjectMapper mapper = new ObjectMapper();

try (FileWriter file = new FileWriter(jsonFile)) {

file.write(mapper.writerWithDefaultPrettyPrinter().writeValueAsString(resultJson));

file.flush();

} catch (IOException e) {

e.printStackTrace();

}

}

public DB2JSON(String db_url, String username, String password, String jdbc_driver) {

try {

Class.forName(jdbc_driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

try {

Class.forName(JDBC_DRIVER);

// STEP 3: Open a connection

System.out.println("Connecting to a selected database...");

connection = DriverManager.getConnection(db_url, username, password);

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException cnfe) {

cnfe.printStackTrace();

}

if (connection != null) {

System.out.println("Connected to " + db_url);

} else {

System.out.println("Not Connected");

}

}

@SuppressWarnings("unchecked")

public JSONArray getResultsJSONArray(String sql) {

java.sql.Statement statement;

JSONArray jsonArray = new JSONArray();

try {

statement = connection.createStatement();

ResultSet rs = statement.executeQuery(sql);

Integer i = 0;

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

while (rs.next()) {

JSONObject jsonObject = new JSONObject();

for (int ii = 1; ii < columnCount + 1; ii++) {

String key = rsmd.getColumnLabel(ii);

jsonObject.put(key, rs.getObject(key));

}

jsonArray.add(i, jsonObject);

;

i++;

}

} catch (SQLException e) {

e.printStackTrace();

}

return jsonArray;

}

public void CloseConnection() {

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}