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();
}
}
}
