package vvm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.sql.DatabaseMetaData;
import java.sql.ResultSetMetaData;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStreamWriter;

public class XmlExporter {
/*
* URL Format: jdbc:oracle:thin:@<server>:<port>:<instance> URL Example:
* jdbc:oracle:thin:@localhost:1521:chanda Driver Class:
* oracle.jdbc.driver.OracleDriver
*
* URL Format: jdbc:db2://<server>:<port>/<database>?<props> URL Example:
* jdbc:db2://localhost:50000/chandaDB Driver Class: com.ibm.db2.jcc.DB2Driver
*
* jdbc:postgresql:database jdbc:postgresql:/ jdbc:postgresql://host/database
* jdbc:postgresql://host/ jdbc:postgresql://host:port/database
* jdbc:postgresql://host:port/ org.postgresql.Driver
*/

public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
// Параметры
// [0]Имя класса: com.ibm.db2.jcc.DB2Driver
// [1]URL базы: jdbc:db2://orochi.utair.dom:50000/tickproc
// [2]Имя юзера:
// [3]Пароль:
// [4]Схема:
// [5]Имя файла с SQL-запросом: res\\someQuery.sql
// [6]Путь для выходных файлов: tmp\\xxx
System.out.println("v 2018-10-06 1");
System.out.println("Start:    " + LocalDateTime.now());
String jdbcClassName = args.length > 0 ? args[0] : "com.ibm.db2.jcc.DB2Driver";
String jdbcUrl = args.length > 1 ? args[1] : "jdbc:db2://orochi.utair.dom:50000/tickproc";
String userName = args.length > 2 ? args[2] : "";
String password = args.length > 3 ? args[3] : "";
String schema = args.length > 4 ? args[4] : "";
String queryFileName = args.length > 5 ? args[5] : "res\\someQuery.sql";
String targetFileName = args.length > 6 ? args[6] : "tmp\\result-";

System.setProperty("oracle.jdbc.v$session.program", "VVM CSV/XML exporter");
Class.forName(jdbcClassName);
try (Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
BufferedReader br = new BufferedReader(new FileReader(queryFileName), 1024 * 1024)) {
String someQuery = "";

String line;
while ((line = br.readLine()) != null) someQuery += line + "\n";

System.out.println("Connected to database successfully");
// Perform database activities here...
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Statement stmt = conn.createStatement();
stmt.setFetchSize(1000);

try {
System.out.println();
System.out.println("Execute:  " + LocalDateTime.now());
ResultSet rs = stmt.executeQuery(someQuery);
ResultSetMetaData rsmd = rs.getMetaData(); // Для заголовков колонок в CSV
System.out.println("Fetch:    " + LocalDateTime.now());

try (BufferedWriter csv_bw = new BufferedWriter(
new OutputStreamWriter(new FileOutputStream(targetFileName + ".csv")/* , "UTF-8" */),
1024 * 1024))
{
int columnCount = rsmd.getColumnCount();
System.out.println("Column Count:" + columnCount);
for (int i = 1; i <= columnCount; i++) {
System.out.println("" + i + ". " + rsmd.getColumnName(i) + ", "
+ rsmd.getColumnLabel(i) + ", " + rsmd.getColumnTypeName(i) + ", "
+ rsmd.getColumnClassName(i));
csv_bw.write(rsmd.getColumnLabel(i));
csv_bw.write(";");
}
csv_bw.write("\n");

int rowCount = 0;
while (rs.next()) {
rowCount++;
// Process the current row in rs here
for (int i = 1; i <= columnCount; i++) {
if (rsmd.getColumnTypeName(i).equals("XML")
|| rsmd.getColumnTypeName(i).equals("SYS.XMLTYPE")) // Чтобы работало и в DB2, и в Oracle
{
/*
* Для XML-я возможно: ResultSet.getAsciiStream InputStream
* ResultSet.getBinaryStream InputStream ResultSet.getBytes byte[]
* ResultSet.getCharacterStream Reader ResultSet.getObject Object
* ResultSet.getSQLXML SQLXML ResultSet.getString String
*/
// SQLXML x = rs.getSQLXML(i);
String x = rs.getString(i);
if (x != null) {
String fn = targetFileName + rowCount + ".xml";
csv_bw.write(fn);
try (BufferedWriter bw = new BufferedWriter(
new OutputStreamWriter(new FileOutputStream(fn), "UTF-8"), 1024 * 1024))
{
bw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
bw.write(x);
}
}
} else {
Object o = rs.getObject(i);
if (o != null) csv_bw.write(o.toString());

}
csv_bw.write(";");
}
csv_bw.write("\n");
}
rs.close();
System.out.println("End:      " + LocalDateTime.now());
System.out.println("Done with:" + rowCount + " rows");
}
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
}

conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}