package com.fitbank.schemautils;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import javax.swing.JOptionPane;
import javax.swing.UIManager;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import com.fitbank.util.Servicios;
public final class FetchSchema {
private final static String _ = "\t";
private final static String __ = "\t\t";
private final static String ___ = "\t\t\t";
private final PreparedStatement pstmtTablas;
private final PreparedStatement pstmtCampos;
private final PreparedStatement pstmtPadres;
private final PreparedStatement pstmtHijas;
public static void main(String[] args) throws Exception {
UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
Connection conn = getConnection();
FetchSchema fs = new FetchSchema(conn);
fs.fetchSchema();
fs.close();
conn.close();
}
private static Connection getConnection() throws Exception {
String conexion = "jdbc:oracle:thin:@";
String usuario = "";
String clave = "";
String[] rotulos = { "Host Name", "JDBC_Port", "SID", "Username",
"Password" };
String[] valores = { "192.168.1.7", "1521", "fitbank", "fitdos",
"fitdos" };
for (int i = 0; i < rotulos.length; i++) {
String valor = JOptionPane.showInputDialog(String.format(
"< %s > %s\n\nIngrese %s:", conexion + (i < 3
? " . . ." : ""), !usuario.equals("") ? "\nUsername: "
+ usuario : "", rotulos[i]), valores[i]);
if (valor != null) {
if (i < 3) {
conexion += valor + (i == 2 ? "" : ":");
} else if (i == 3) {
usuario = valor;
} else {
clave = valor;
}
} else {
System.out.println(
"Se ingreso un dato nulo o no válido, se suspende proceso.");
return null;
}
}
System.out.print("Conectando < " + conexion + " > . . . ");
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
return DriverManager.getConnection(conexion, usuario, clave);
} catch (Exception e) {
System.out.println("ERROR: La conexión falló");
throw e;
}
}
private FetchSchema(Connection conn) throws SQLException {
pstmtTablas = conn.prepareStatement("select distinct tablabase, to_number(substr(lpad(ccampo, 8, '0'), 0, 5)) ctabla, comments"
+ " from tcamposconsultablesid t"
+ " join user_tab_comments c on c.table_name=t.tablabase"
+ " order by tablabase");
pstmtCampos = conn.prepareStatement("select t.ccampo ccampo, campobase, tipodatobase, primarykey, mandatorio, valordefault, descripcion"
+ " from tcamposconsultablesid tid"
+ " join tcamposconsultables t on tid.ccampo = t.ccampo"
+ " where tablabase=?"
+ " and fhasta = to_timestamp('2999-12-31','yyyy-mm-dd')"
+ " order by ccampo");
pstmtPadres = conn.prepareStatement("select distinct p.table_name"
+ " from user_constraints h, user_constraints p"
+ " where h.table_name = upper(?)"
+ " and h.r_constraint_name = p.constraint_name"
+ " and h.r_owner = p.owner"
+ " and h.constraint_name in"
+ " (select constraint_name from user_cons_columns"
+ " where constraint_name = h.constraint_name"
+ " and column_name = ?)");
pstmtHijas = conn.prepareStatement("select distinct h.table_name"
+ " from user_constraints p, user_constraints h"
+ " where p.table_name = upper(?)"
+ " and p.constraint_name = h.r_constraint_name"
+ " and p.owner = h.r_owner"
+ " and p.constraint_name in"
+ " (select constraint_name from user_cons_columns"
+ " where constraint_name = p.constraint_name"
+ " and column_name = ?)");
}
public void close() throws SQLException {
pstmtTablas.close();
pstmtCampos.close();
pstmtPadres.close();
pstmtHijas.close();
}
public void fetchSchema() throws Exception {
Writer res = new StringWriter();
res.append("\n");
res.append("\n");
processTables(res);
res.append("");
res.close();
String file = Servicios.getResource("com/fitbank/schemautils/schema.xml");
FileOutputStream fos = new FileOutputStream(file);
IOUtils.write(res.toString(), fos, "UTF-8");
fos.close();
System.out.println("");
System.out.println("Proceso terminado");
}
private void processTables(Writer res) throws SQLException, IOException {
ResultSet rsetNombresTabla = pstmtTablas.executeQuery();
int count = 0;
while (rsetNombresTabla.next()) {
final String codigoTabla = rsetNombresTabla.getString("ctabla");
final String tablaBase = rsetNombresTabla.getString("tablabase");
final String descTabla = StringUtils.defaultIfEmpty(rsetNombresTabla.getString("comments"), ".");
System.out.println("Procesando tabla " + tablaBase + " (" + (++count) + ")...");
res.append(_ + "\n");
res.append(__ + "" + StringUtils.leftPad(codigoTabla, 3, '0') + "\n");
res.append(__ + "" + tablaBase + "\n");
res.append(__ + "" + StringEscapeUtils.escapeXml(descTabla) + "\n");
processFields(tablaBase, res);
res.append(_ + "\n");
}
rsetNombresTabla.close();
}
private void processFields(String tablaBase, Writer res) throws IOException,
SQLException {
pstmtCampos.setString(1, tablaBase);
ResultSet rsetCamposTabla = pstmtCampos.executeQuery();
System.out.print("Procesando campos");
while (rsetCamposTabla.next()) {
System.out.print(".");
String codigoCampo = rsetCamposTabla.getString("ccampo");
String campoBase = rsetCamposTabla.getString("campobase");
String tipoDato = rsetCamposTabla.getString("tipodatobase");
String primaryKey = rsetCamposTabla.getString("primarykey");
String mandatorio = rsetCamposTabla.getString("mandatorio");
String defaultValue = rsetCamposTabla.getString("valordefault");
String desCampo = rsetCamposTabla.getString("descripcion");
List padres = getParentTables(tablaBase, campoBase);
List hijas = getChildTables(tablaBase, campoBase);
res.append(__ + "\n");
res.append(___ + "" + codigoCampo.substring(codigoCampo.length() - 3) + "\n");
res.append(___ + "" + campoBase + "\n");
res.append(___ + "" + StringEscapeUtils.escapeXml(desCampo) + "\n");
res.append(___ + "" + tipoDato + "\n");
if ("1".equals(primaryKey)) {
res.append(___ + "" + primaryKey + "\n");
}
if ("1".equals(mandatorio)) {
res.append(___ + "" + mandatorio + "\n");
}
if (StringUtils.isNotBlank(defaultValue)) {
res.append(___ + "" + defaultValue + "\n");
}
if (!padres.isEmpty()) {
res.append(___ + "" + StringUtils.join(padres, ",") + "\n");
}
if (!hijas.isEmpty()) {
res.append(___ + "" + StringUtils.join(hijas, ",") + "\n");
}
res.append(__ + "\n");
}
System.out.print("\n");
rsetCamposTabla.close();
}
private List getChildTables(String tablaBase, String campoBase)
throws SQLException {
pstmtHijas.setString(1, tablaBase);
pstmtHijas.setString(2, campoBase);
ResultSet rsetReferencias = pstmtHijas.executeQuery();
List hijas = new LinkedList();
while (rsetReferencias.next()) {
if (!rsetReferencias.getString("table_name").equals("")) {
hijas.add(rsetReferencias.getString("table_name"));
}
}
rsetReferencias.close();
return hijas;
}
private List getParentTables(String tablaBase, String campoBase)
throws SQLException {
pstmtPadres.setString(1, tablaBase);
pstmtPadres.setString(2, campoBase);
ResultSet rsetReferencias = pstmtPadres.executeQuery();
List padres = new LinkedList();
while (rsetReferencias.next()) {
if (!rsetReferencias.getString("table_name").equals("")) {
padres.add(rsetReferencias.getString("table_name"));
}
}
rsetReferencias.close();
return padres;
}
}