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