private void fetchPrimaryKeys() {

    Vector temp = new Vector(20);

    try {
      if (cConn == null) {
        return;
      }

      if (dbmeta == null) {
        dbmeta = cConn.getMetaData();
      }

      ResultSet colList = dbmeta.getPrimaryKeys(null, null, tableName);

      while (colList.next()) {
        temp.addElement(colList.getString("COLUMN_NAME"));
      }

      colList.close();
    } catch (SQLException e) {
      ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
    }

    primaryKeys = new String[temp.size()];

    temp.copyInto(primaryKeys);

    pkColIndex = new int[primaryKeys.length];

    for (int i = 0; i < primaryKeys.length; i++) {
      pkColIndex[i] = this.getColIndex(primaryKeys[i]);
    } // end of for (int i=0; i<primaryKeys.length; i++)
  }
예제 #2
0
 @Override
 public void columnsForTables(Table table, DatabaseMetaData dbmd) {
   try {
     rs = dbmd.getColumns(null, "%", table.getName(), null);
     ResultSet priamryKeyRs = dbmd.getPrimaryKeys(null, null, table.getName());
     String priamryKey = null;
     while (priamryKeyRs.next()) {
       priamryKey = priamryKeyRs.getString("COLUMN_NAME");
     }
     while (rs.next()) {
       Column column = new Column();
       String name = rs.getString("COLUMN_NAME");
       column.setName(name);
       column.setType(rs.getString("TYPE_NAME"));
       int columnSize = rs.getInt("COLUMN_SIZE");
       int nullable = rs.getInt("nullable");
       column.setNullable(nullable == 1);
       column.setDataSize(columnSize);
       if (column.getName().equals(priamryKey)) {
         column.setPrimaryKey(true);
       }
       table.addColumn(column);
     }
   } catch (SQLException e) {
     e.printStackTrace();
   }
 }
예제 #3
0
  private String getPrimaryKey(
      DatabaseMetaData meta, String dbName, String schema, String tableName) throws SQLException {
    String pKey = null;
    ResultSet resultSet = meta.getPrimaryKeys(dbName, schema, tableName);

    if (resultSet.next()) {
      resultSet = meta.getPrimaryKeys(dbName, schema, tableName);
    } else {
      resultSet = meta.getPrimaryKeys(null, schema, tableName);
    }
    while (resultSet.next()) {
      pKey = resultSet.getString(DBConstants.DataServiceGenerator.COLUMN_NAME);
      return pKey;
    }
    return pKey;
  }
예제 #4
0
  /** Basic test of DatabaseMetaData functions that access system tables */
  public void testTwo() throws Exception {

    Connection conn = newConnection();
    int updateCount;

    try {
      TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt");

      DatabaseMetaData dbmeta = conn.getMetaData();

      dbmeta.allProceduresAreCallable();
      dbmeta.getBestRowIdentifier(null, null, "T_1", DatabaseMetaData.bestRowTransaction, true);
      dbmeta.getCatalogs();
      dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%");
      dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%");
      dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1");
      dbmeta.getExportedKeys(null, null, "T_1");
      dbmeta.getFunctionColumns(null, "%", "%", "%");
      dbmeta.getFunctions(null, "%", "%");
      dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1");
      dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true);
      dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1");
      dbmeta.getProcedureColumns(null, null, "%", "%");
      dbmeta.getProcedures("PUBLIC", "%", "%");
      dbmeta.getSchemas(null, "#");
      dbmeta.getTablePrivileges(null, "%", "%");
      dbmeta.getUDTs(null, "%", "%", new int[] {Types.DISTINCT});

    } catch (Exception e) {
      assertTrue("unable to prepare or execute DDL", false);
    } finally {
      conn.close();
    }
  }
 public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException {
   _conn.checkOpen();
   try {
     return DelegatingResultSet.wrapResultSet(_conn, _meta.getPrimaryKeys(catalog, schema, table));
   } catch (SQLException e) {
     handleException(e);
     throw new AssertionError();
   }
 }
  public List<TableIndex> getIndexes(DatabaseMetaData dbMeta, String tableName)
      throws SQLException {
    List<TableIndex> indexes = new ArrayList<TableIndex>();
    ResultSet pkInfo = null;
    String pkName = null;
    // ArrayList<String> pkFields = new ArrayList<String>();
    ResultSet indexInfo = null;
    try {
      indexInfo = dbMeta.getIndexInfo(null, dbSchema, tableName, false, true);
      // need to ensure that the PK is not returned as an index
      pkInfo = dbMeta.getPrimaryKeys(null, dbSchema, tableName);
      if (pkInfo.next()) {
        pkName = pkInfo.getString("PK_NAME");
      }
      // Map<Integer,String> tempPk = new HashMap<Integer,String>();
      // while ( pkInfo.next() ) {
      //	tempPk.put( pkInfo.getInt( "KEY_SEQ" ), pkInfo.getString( "COLUMN_NAME" ) );
      // }

      TableIndex currIndex = null;
      while (indexInfo.next()) {
        if (indexInfo.getString("INDEX_NAME") == null) continue;
        // System.out.println( "Row: " + indexInfo.getString( "INDEX_NAME" ) + "/" +
        // indexInfo.getString( "COLUMN_NAME" ) );
        if (currIndex == null || !indexInfo.getString("INDEX_NAME").equals(currIndex.name)) {
          currIndex = new TableIndex();
          currIndex.name = indexInfo.getString("INDEX_NAME");
          currIndex.unique = !indexInfo.getBoolean("NON_UNIQUE");
          // if has the same name as the PK, skip adding it to the index list
          if (pkName == null || !pkName.equals(currIndex.name)) {
            indexes.add(currIndex);
            // System.out.println( "Added " + currIndex.name + " to index list");
          } else {
            // System.out.println( "Skipping PK: " + currIndex.name );
          }
        }
        currIndex.columns.add(indexInfo.getString("COLUMN_NAME"));
      }

    } catch (SQLException e) {
      log(
          "WARN: Could not read indexes for Table " + tableName + " : " + e.getMessage(),
          Project.MSG_WARN);
    } finally {
      if (indexInfo != null) {
        indexInfo.close();
      }
      if (pkInfo != null) {
        pkInfo.close();
      }
    }
    return indexes;
  }
예제 #7
0
 /** Returns a table's primary key columns as a Set of strings. */
 public static Set<String> getPrimaryKey(DatabaseMetaData metadata, String tableName)
     throws Exception {
   Set<String> columns = new HashSet<String>();
   ResultSet keys =
       metadata.getPrimaryKeys(
           metadata.getConnection().getCatalog(), metadata.getUserName(), tableName);
   while (keys.next()) {
     columns.add(keys.getString(PRIMARY_PK_COL_NAME));
   }
   keys.close();
   return columns;
 }
예제 #8
0
  /**
   * @param meta
   * @throws SQLException
   */
  private void initPrimaryKeys(DatabaseMetaData meta) throws SQLException {
    if (properties == null) return;

    ResultSet rs = null;

    try {
      rs = meta.getPrimaryKeys(null, getSchema(), getName());

      while (rs.next()) setPrimaryColumn(rs);
    } finally {
      if (rs != null) rs.close();
    }
  }
  public void generate(String tableName) throws SQLException, IOException {
    String schemaName = null;
    int idx = tableName.indexOf('.');
    if (idx != -1) {
      schemaName = tableName.substring(0, idx);
      tableName = tableName.substring(idx + 1);
    }
    DatabaseMetaData meta = con.getMetaData();
    Set<String> keySet = new HashSet<String>();
    ResultSet rs = meta.getPrimaryKeys(null, schemaName, tableName);
    try {
      while (rs.next()) {
        String name = rs.getString(4);
        keySet.add(name);
      }
    } finally {
      rs.close();
    }

    TableInfo table = new TableInfo(tableName);
    rs = meta.getColumns(null, schemaName, tableName, "%");
    try {
      while (rs.next()) {
        String name = rs.getString(4);
        int type = rs.getInt(5);
        boolean pk = keySet.contains(name);
        boolean autoInc = "YES".equals(rs.getString(23));

        ColumnInfo col = new ColumnInfo(name, type, pk);
        table.addColumn(col);
        if (pk && keySet.size() == 1 && autoInc) {
          table.setUseSerialKey(true);
        }
      }
    } finally {
      rs.close();
    }

    File outputFile = new File(this.outputDir, table.getClazzName() + ".java");
    Writer writer = new OutputStreamWriter(new FileOutputStream(outputFile), "utf-8");
    try {
      Template template = engine.getTemplate("jp/co/flect/sql/Table.template");
      VelocityContext context = new VelocityContext();
      context.put("packageName", this.packageName);
      context.put("superClazz", this.superClass);
      context.put("table", table);
      template.merge(context, writer);
    } finally {
      writer.close();
    }
  }
예제 #10
0
 public CompositeMap getPrimaryKeys() throws SQLException {
   CompositeMap primaryKeyArray =
       new CommentCompositeMap(BMUtil.BMPrefix, AuroraConstant.BMUri, "primary-key");
   String tableName = getTableName();
   if (tableName == null) return primaryKeyArray;
   DatabaseMetaData dbMetaData = getDBMetaData();
   ResultSet tableRet = dbMetaData.getPrimaryKeys(null, dbMetaData.getUserName(), tableName);
   while (tableRet.next()) {
     CompositeMap field =
         new CommentCompositeMap(BMUtil.BMPrefix, AuroraConstant.BMUri, "pk-field");
     field.put("name", tableRet.getString("COLUMN_NAME").toLowerCase());
     primaryKeyArray.addChild(field);
   }
   return primaryKeyArray;
 }
예제 #11
0
 /**
  * Get a list of all primary keys that are defined for the table.
  *
  * @param conn database connection
  * @param schema schema name
  * @param table table name
  * @return list of all primary keys
  * @throws SQLException Thrown if an SQL statement failed to be executed.
  */
 private static String[] getPrimaryKeys(
     final Connection conn, final String schema, final String table) throws SQLException {
   final ArrayList<String> result = new ArrayList<String>();
   final DatabaseMetaData metaData = conn.getMetaData();
   ResultSet rs = null;
   try {
     rs = metaData.getPrimaryKeys(conn.getCatalog(), schema, table);
     while (rs.next()) {
       final StringBuilder indexInfo = new StringBuilder();
       for (int index = 4; index <= 6; index++) {
         if (indexInfo.length() > 0) {
           indexInfo.append('/');
         }
         indexInfo.append(rs.getString(index));
       }
       result.add(indexInfo.toString());
     }
   } finally {
     IOUtils.closeResultSet(rs);
   }
   return result.toArray(new String[result.size()]);
 }
예제 #12
0
파일: Table.java 프로젝트: andrus/ashwood
 public void refreshPrimaryKeys(DatabaseMetaData metaData) throws SQLException {
   primaryKeys.clear();
   ResultSet rs = null;
   try {
     rs = metaData.getPrimaryKeys(catalog, schema, name);
     while (rs.next()) {
       PrimaryKey pk = new PrimaryKey();
       primaryKeys.add(pk);
       pk.setOwner(this);
       pk.setColumnName(rs.getString("COLUMN_NAME"));
       pk.setKeySequence(rs.getShort("KEY_SEQ"));
       pk.setName(rs.getString("PK_NAME"));
     }
   } catch (SQLException sqle) {
     throw sqle;
   } finally {
     try {
       rs.close();
     } catch (Exception ex) {
     }
   }
 }
예제 #13
0
  public List<Table> queryFields(DBConfig config) {
    List<Table> tableInfoList = new ArrayList<Table>();
    Connection conn = null;
    try {
      List<TableConfiguration> tableList = (List<TableConfiguration>) config.getTableList();
      if (tableList == null || tableList.size() <= 0) return tableInfoList;

      conn = ConnectionFactory.getInstance().getConnection(config);
      DatabaseMetaData databaseMetaData = conn.getMetaData();
      for (TableConfiguration table : tableList) {
        Table tableInfo = new Table();

        String localCatalog = table.getCatalog();
        String localSchema = table.getSchema();
        String localTableName = table.getTableName();
        if (databaseMetaData.storesLowerCaseIdentifiers()) {
          localCatalog = localCatalog == null ? null : localCatalog.toLowerCase();
          localSchema = localSchema == null ? null : localSchema.toLowerCase();
          localTableName = localTableName == null ? null : localTableName.toLowerCase();
        } else if (databaseMetaData.storesUpperCaseIdentifiers()) {
          localCatalog = localCatalog == null ? null : localCatalog.toUpperCase();
          localSchema = localSchema == null ? null : localSchema.toUpperCase();
          localTableName = localTableName == null ? null : localTableName.toUpperCase();
        }

        Statement stmt = conn.createStatement();
        ResultSet tableRs = stmt.executeQuery("SHOW CREATE TABLE " + localTableName);
        if (tableRs != null && tableRs.next()) {
          String create = tableRs.getString(2);
          String comment = parse(create);
          tableInfo.setComment(comment);
        }

        ResultSet rs = databaseMetaData.getColumns(localCatalog, localSchema, localTableName, null);
        tableInfo.setSerialVersionUID(System.nanoTime() + "L");
        while (rs.next()) {
          tableInfo.setCatalog(rs.getString("TABLE_CAT"));
          tableInfo.setSchema(rs.getString("TABLE_SCHEM"));
          tableInfo.setName(rs.getString("TABLE_NAME"));
          tableInfo.setCode(rs.getString("TABLE_NAME"));

          Column introspectedColumn = new Column();
          introspectedColumn.setTableAlias(table.getTableName());
          introspectedColumn.setName(rs.getString("COLUMN_NAME"));
          introspectedColumn.setJdbcType(rs.getInt("DATA_TYPE"));
          introspectedColumn.setDataType(
              JdbcTypeNameTranslator.getJdbcTypeName(rs.getInt("DATA_TYPE"))); // $NON-NLS-1$
          introspectedColumn.setLength(rs.getInt("COLUMN_SIZE")); // $NON-NLS-1$
          introspectedColumn.setCode(rs.getString("COLUMN_NAME"));
          /*introspectedColumn.setActualColumnName(rs
          .getString("COLUMN_NAME"));*/
          //$NON-NLS-1$
          introspectedColumn.setNullable(
              rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable); // $NON-NLS-1$
          introspectedColumn.setScale(rs.getInt("DECIMAL_DIGITS")); // $NON-NLS-1$
          introspectedColumn.setComment(rs.getString("REMARKS"));

          tableInfo.addColumn(introspectedColumn);

          PropertyBean pb = new PropertyBean();

          pb.setName(convertFirstUpper(getFieldName(rs.getString("COLUMN_NAME"))));
          pb.setType(JdbcType2Java.calculateJavaType(introspectedColumn));
          String importType = JdbcType2Java.importJavaType(introspectedColumn);
          if (importType != null && !importType.equals("")) {
            if (importType.indexOf("java.lang") < 0
                && !tableInfo.getImportList().contains(importType))
              tableInfo.getImportList().add(importType);
          }
          tableInfo.getPropertyBeanList().add(pb);
        }
        closeResultSet(rs);

        rs = databaseMetaData.getPrimaryKeys(localCatalog, localSchema, localTableName);
        while (rs.next()) {
          tableInfo.addPrimaryKeyColumn(rs.getString("COLUMN_NAME"));
        }
        closeResultSet(rs);
        tableInfoList.add(tableInfo);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }

    return tableInfoList;
  }
예제 #14
0
  public void copyDbData(String dbin, String dbout) throws SQLException {
    // Do some connections and collect statements
    Connection inConn = makeConnection(dbin);
    Statement inSt = inConn.createStatement();
    DatabaseMetaData dbmeta = inConn.getMetaData();

    Connection outConn = makeConnection(dbout);
    Statement outSt = outConn.createStatement();

    ResultSet tables = getTables(inConn);
    forEach(
        tables,
        rs -> {
          try {
            // Okay, we'v got our table name
            String tableName = rs.getString("TABLE_NAME");

            // PreparedStatement st = DriverManager.getConnection(addr, user,
            // passwd).prepareStatement(format("CREATE TABLE %s.%s (LIKE %s.%s)", dbout, tableName,
            // dbin, tableName));
            // st.execute();
            // st.close();

            // Lets fetch all data from it
            ResultSet data = inSt.executeQuery(format("SELECT * FROM \"%s\"", tableName));

            // Lets get table schema from metadata and generate creation operator
            ResultSetMetaData datamd = data.getMetaData();
            int columnCount = datamd.getColumnCount();

            StringBuilder sb = new StringBuilder();
            sb.append("CREATE TABLE ").append(tableName).append(" (");

            for (int i = 1; i <= columnCount; i++) {
              if (i > 1) sb.append(", ");
              sb.append(datamd.getColumnName(i)).append(" ").append(datamd.getColumnTypeName(i));

              if (datamd.isNullable(i) == ResultSetMetaData.columnNoNulls) sb.append(" NOT NULL");
              // Tons of parameters could be parsed here except of references and keys

              // if (precision != 0) {
              //    sb.append("(").append(precision).append(")");
              // }

              switch (datamd.getColumnTypeName(i)) {
                case "character":
                case "time":
                case "":
                  sb.append("(").append(datamd.getPrecision(i)).append(")");
              }
            }
            sb.append(");");

            // Looks good, lets create this in our outer database
            LOGGER.info("Execute: '{}'", sb.toString());

            outSt.execute(sb.toString());

            // Setting up primary keys
            Map<String, StringBuilder> primaryKeys = new HashMap<>();
            ResultSet primaryKeysRs = dbmeta.getPrimaryKeys(null, null, tableName);
            forEach(
                primaryKeysRs,
                pkRs -> {
                  try {
                    String columName = pkRs.getString("COLUMN_NAME");
                    String pkName = pkRs.getString("PK_NAME");

                    if (!primaryKeys.containsKey(pkName)) {
                      primaryKeys.put(pkName, new StringBuilder(columName));
                    } else {
                      primaryKeys.get(pkName).append(", ").append(columName);
                    }

                  } catch (SQLException e) {
                    // Should never be reached
                    e.printStackTrace();
                  }
                });

            primaryKeys
                .entrySet()
                .forEach(
                    entry -> {
                      String pkName = entry.getKey();
                      String pk = entry.getValue().toString();
                      String query = format("ALTER TABLE %s ADD PRIMARY KEY (%s)", tableName, pk);
                      try {
                        LOGGER.info("Execute: '{}'", query);
                        outSt.executeUpdate(query);
                      } catch (SQLException e) {
                        LOGGER.error("Setting '{}' as primary key in '{}' failed", pk, tableName);
                        e.printStackTrace();
                      }
                    });

            // Generate insertion statement
            StringBuilder prepareQuery = new StringBuilder("INSERT INTO ");
            prepareQuery.append(tableName).append(" VALUES (");
            for (int i = 0; i < columnCount; i++) {
              if (i > 0) prepareQuery.append(", ");
              prepareQuery.append("?");
            }
            prepareQuery.append(");");

            LOGGER.info("Prepare statement with: {}", prepareQuery);
            PreparedStatement pst = outConn.prepareStatement(prepareQuery.toString());

            // And put all our's data in it
            forEach(
                data,
                dataRs -> {
                  try {
                    for (int i = 1; i <= columnCount; i++) {
                      pst.setObject(i, dataRs.getObject(i));
                    }

                    LOGGER.info("Execute: '{}'", pst.toString());
                    pst.execute();
                  } catch (SQLException e) {
                    e.printStackTrace();
                  }

                  /*
                  try {
                      StringBuilder insert = new StringBuilder();
                      insert.append("INSERT INTO \"").append(tableName).append("\" VALUES (");
                      for (int i = 1; i <= columnCount; i++) {
                          if (i > 1) insert.append(", ");
                          insert.append(dataRs.getString(i));
                      }
                      insert.append(");");
                      // Finally put insert it in table
                      // Timestamps would be inserted incorrect
                      LOGGER.info("Execute: '{}'", insert.toString());
                      outSt.executeUpdate(insert.toString());
                  } catch (SQLException e) {
                      LOGGER.error("Failed to insert into '{}'", tableName);
                  }
                  */
                });
          } catch (SQLException e) {
            LOGGER.error("Failed to create table");
            e.printStackTrace();
          }
        });

    // Lets deal up with foreign keys
    tables.beforeFirst();
    forEach(
        tables,
        rs -> {
          try {
            String tableName = rs.getString("TABLE_NAME");
            ResultSet foreignKeysRs = dbmeta.getImportedKeys(null, null, tableName);
            Map<String, String> pkTables = new HashMap<>();
            Map<String, StringBuilder> pkKeys = new HashMap<>();
            Map<String, StringBuilder> fkKeys = new HashMap<>();
            forEach(
                foreignKeysRs,
                keyRs -> {
                  try {

                    String pkTable = keyRs.getString("PKTABLE_NAME");
                    String pkColumn = keyRs.getString("PKCOLUMN_NAME");
                    String fkColumn = keyRs.getString("FKCOLUMN_NAME");
                    String fkName = keyRs.getString("FK_NAME");

                    if (!pkTables.containsKey(fkName)) {
                      pkTables.put(fkName, pkTable);
                      pkKeys.put(fkName, new StringBuilder(pkColumn));
                      fkKeys.put(fkName, new StringBuilder(fkColumn));
                    } else {
                      pkKeys.get(fkName).append(", ").append(pkColumn);
                      fkKeys.get(fkName).append(", ").append(fkColumn);
                    }

                  } catch (SQLException e) {
                    e.printStackTrace();
                    // Should newer be reached
                  }
                });
            pkTables
                .entrySet()
                .forEach(
                    entry -> {
                      String keyName = entry.getKey();
                      String extTableName = entry.getValue();

                      String alterFK =
                          format(
                              "ALTER TABLE %s ADD FOREIGN KEY (%s) REFERENCES %s(%s);",
                              tableName,
                              // keyName,
                              fkKeys.get(keyName),
                              extTableName,
                              pkKeys.get(keyName));

                      LOGGER.info("Execute: '{}'", alterFK);

                      try {
                        outSt.executeUpdate(alterFK);
                      } catch (SQLException e) {
                        e.printStackTrace();
                      }
                    });

          } catch (SQLException e) {
            e.printStackTrace();
          }
        });
  }
예제 #15
0
  /** Dump the whole database to an SQL string */
  public static void dumpDB(ArrayList<String> tab, Connection dbConn, String filePath) {

    String columnNameQuote = "`";
    BufferedWriter out = null;

    try {
      out = new BufferedWriter(new FileWriter(filePath));
      DatabaseMetaData dbMetaData = dbConn.getMetaData();
      StringBuffer result;
      ResultSet rs = dbMetaData.getTables(null, null, null, new String[] {"TABLE"});
      if (!rs.next()) {
        System.err.println("Unable to find any tables");
        rs.close();
      } else {
        // Right, we have some tables, so we can go to work.
        // the details we have are
        // TABLE_CAT String => table catalog (may be null)
        // TABLE_SCHEM String => table schema (may be null)
        // TABLE_NAME String => table name
        // TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE",
        // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
        // REMARKS String => explanatory comment on the table
        // TYPE_CAT String => the types catalog (may be null)
        // TYPE_SCHEM String => the types schema (may be null)
        // TYPE_NAME String => type name (may be null)
        // SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed
        // table (may be null)
        // REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created.
        // Values are "SYSTEM", "USER", "DERIVED". (may be null)
        // We will ignore the schema and stuff, because people might want to import it somewhere
        // else
        // We will also ignore any tables that aren't of type TABLE for now.
        // We use a do-while because we've already caled rs.next to see if there are any rows
        do {
          result = new StringBuffer();
          String tableName = rs.getString("TABLE_NAME");
          if (tab != null && !tab.contains(tableName)) {
            continue;
          }

          String tableType = rs.getString("TABLE_TYPE");
          if ("TABLE".equalsIgnoreCase(tableType)) {
            result.append("\n\n-- " + tableName);
            result.append(
                "\nCREATE TABLE " + columnNameQuote + tableName + columnNameQuote + " (\n");
            ResultSet tableMetaData = dbMetaData.getColumns(null, null, tableName, "%");
            boolean firstLine = true;
            while (tableMetaData.next()) {
              if (firstLine) {
                firstLine = false;
              } else {
                // If we're not the first line, then finish the previous line with a comma
                result.append(",\n");
              }
              String columnName = tableMetaData.getString("COLUMN_NAME");
              String columnType = tableMetaData.getString("TYPE_NAME");
              // WARNING: this may give daft answers for some types on some databases (eg JDBC-ODBC
              // link)
              int columnSize = tableMetaData.getInt("COLUMN_SIZE");
              String nullable = tableMetaData.getString("IS_NULLABLE");
              String nullString = "NULL";
              if ("NO".equalsIgnoreCase(nullable)) {
                nullString = "NOT NULL";
              }
              result.append(
                  "    "
                      + columnNameQuote
                      + columnName
                      + columnNameQuote
                      + " "
                      + columnType
                      + " ("
                      + columnSize
                      + ")"
                      + " "
                      + nullString);
            }
            tableMetaData.close();

            // Now we need to put the primary key constraint
            try {
              ResultSet primaryKeys = dbMetaData.getPrimaryKeys(null, null, tableName);
              // What we might get:
              // TABLE_CAT String => table catalog (may be null)
              // TABLE_SCHEM String => table schema (may be null)
              // TABLE_NAME String => table name
              // COLUMN_NAME String => column name
              // KEY_SEQ short => sequence number within primary key
              // PK_NAME String => primary key name (may be null)
              String primaryKeyName = null;
              StringBuffer primaryKeyColumns = new StringBuffer();
              while (primaryKeys.next()) {
                String thisKeyName = primaryKeys.getString("PK_NAME");
                if ((thisKeyName != null && primaryKeyName == null)
                    || (thisKeyName == null && primaryKeyName != null)
                    || (thisKeyName != null && !thisKeyName.equals(primaryKeyName))
                    || (primaryKeyName != null && !primaryKeyName.equals(thisKeyName))) {
                  // the keynames aren't the same, so output all that we have so far (if anything)
                  // and start a new primary key entry
                  if (primaryKeyColumns.length() > 0) {
                    // There's something to output
                    result.append(",\n    PRIMARY KEY ");
                    if (primaryKeyName != null) {
                      result.append(primaryKeyName);
                    }
                    result.append("(" + primaryKeyColumns.toString() + ")");
                  }
                  // Start again with the new name
                  primaryKeyColumns = new StringBuffer();
                  primaryKeyName = thisKeyName;
                }
                // Now append the column
                if (primaryKeyColumns.length() > 0) {
                  primaryKeyColumns.append(", ");
                }
                primaryKeyColumns.append(primaryKeys.getString("COLUMN_NAME"));
              }
              if (primaryKeyColumns.length() > 0) {
                // There's something to output
                result.append(",\n    PRIMARY KEY ");
                if (primaryKeyName != null) {
                  result.append(primaryKeyName);
                }
                result.append(" (" + primaryKeyColumns.toString() + ")");
              }
            } catch (SQLException e) {
              // NB you will get this exception with the JDBC-ODBC link because it says
              // [Microsoft][ODBC Driver Manager] Driver does not support this function
              System.err.println(
                  "Unable to get primary keys for table " + tableName + " because " + e);
            }

            result.append("\n);\n");

            // Right, we have a table, so we can go and dump it
            dumpTable(dbConn, result, tableName);
            out.write(result.toString());
          }
        } while (rs.next());
        rs.close();
      }
      dbConn.close();
      out.close();
    } catch (IOException ex) {
      Logger.getLogger(Db2Sql.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException e) {
      e.printStackTrace(); // To change body of catch statement use Options | File Templates.
    }
    return;
  }
예제 #16
0
 /** INTERNAL */
 @Override
 public void init(
     Connection conn,
     String schemaName,
     String triggerName,
     String tableName,
     boolean before,
     int type)
     throws SQLException {
   this.schema = schemaName;
   this.table = tableName;
   this.indexPath = getIndexPath(conn);
   this.indexAccess = getIndexAccess(conn);
   ArrayList<String> keyList = New.arrayList();
   DatabaseMetaData meta = conn.getMetaData();
   ResultSet rs =
       meta.getColumns(
           null,
           StringUtils.escapeMetaDataPattern(schemaName),
           StringUtils.escapeMetaDataPattern(tableName),
           null);
   ArrayList<String> columnList = New.arrayList();
   while (rs.next()) {
     columnList.add(rs.getString("COLUMN_NAME"));
   }
   columnTypes = new int[columnList.size()];
   columns = new String[columnList.size()];
   columnList.toArray(columns);
   rs =
       meta.getColumns(
           null,
           StringUtils.escapeMetaDataPattern(schemaName),
           StringUtils.escapeMetaDataPattern(tableName),
           null);
   for (int i = 0; rs.next(); i++) {
     columnTypes[i] = rs.getInt("DATA_TYPE");
   }
   if (keyList.size() == 0) {
     rs = meta.getPrimaryKeys(null, StringUtils.escapeMetaDataPattern(schemaName), tableName);
     while (rs.next()) {
       keyList.add(rs.getString("COLUMN_NAME"));
     }
   }
   if (keyList.size() == 0) {
     throw throwException("No primary key for table " + tableName);
   }
   ArrayList<String> indexList = New.arrayList();
   PreparedStatement prep =
       conn.prepareStatement(
           "SELECT COLUMNS FROM " + SCHEMA + ".INDEXES WHERE SCHEMA=? AND TABLE=?");
   prep.setString(1, schemaName);
   prep.setString(2, tableName);
   rs = prep.executeQuery();
   if (rs.next()) {
     String cols = rs.getString(1);
     if (cols != null) {
       for (String s : StringUtils.arraySplit(cols, ',', true)) {
         indexList.add(s);
       }
     }
   }
   if (indexList.size() == 0) {
     indexList.addAll(columnList);
   }
   keys = new int[keyList.size()];
   setColumns(keys, keyList, columnList);
   indexColumns = new int[indexList.size()];
   setColumns(indexColumns, indexList, columnList);
 }
예제 #17
0
  /**
   * Reads the DatabaseMetaData for the details of this table including primary keys and indexes.
   *
   * @param metaData the database meta data
   */
  void read(DatabaseMetaData metaData) throws SQLException {
    ResultSet rs = null;

    // primary keys
    try {
      rs = metaData.getPrimaryKeys(null, schema, table);
      while (rs.next()) {
        String c = rs.getString("COLUMN_NAME");
        primaryKeys.add(c);
      }
      closeSilently(rs);

      // indexes
      rs = metaData.getIndexInfo(null, schema, table, false, true);
      indexes = Utils.newHashMap();
      while (rs.next()) {
        IndexInspector info = new IndexInspector(rs);
        if (info.type.equals(IndexType.UNIQUE)) {
          String name = info.name.toLowerCase();
          if (name.startsWith("primary")
              || name.startsWith("sys_idx_sys_pk")
              || name.startsWith("sql")
              || name.endsWith("_pkey")) {
            // skip primary key indexes
            continue;
          }
        }
        if (indexes.containsKey(info.name)) {
          indexes.get(info.name).addColumn(rs);
        } else {
          indexes.put(info.name, info);
        }
      }
      closeSilently(rs);

      // columns
      rs = metaData.getColumns(null, schema, table, null);
      columns = Utils.newHashMap();
      while (rs.next()) {
        ColumnInspector col = new ColumnInspector();
        col.name = rs.getString("COLUMN_NAME");
        col.type = rs.getString("TYPE_NAME");
        col.clazz = ModelUtils.getClassForSqlType(col.type, dateTimeClass);
        col.size = rs.getInt("COLUMN_SIZE");
        col.nullable = rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
        try {
          Object autoIncrement = rs.getObject("IS_AUTOINCREMENT");
          if (autoIncrement instanceof Boolean) {
            col.isAutoIncrement = (Boolean) autoIncrement;
          } else if (autoIncrement instanceof String) {
            String val = autoIncrement.toString().toLowerCase();
            col.isAutoIncrement = val.equals("true") | val.equals("yes");
          } else if (autoIncrement instanceof Number) {
            Number n = (Number) autoIncrement;
            col.isAutoIncrement = n.intValue() > 0;
          }
        } catch (SQLException s) {
          //					throw s;
        }
        if (primaryKeys.size() == 1) {
          if (col.name.equalsIgnoreCase(primaryKeys.get(0))) {
            col.isPrimaryKey = true;
          }
        }
        if (!col.isAutoIncrement) {
          col.defaultValue = rs.getString("COLUMN_DEF");
        }
        columns.put(col.name.toLowerCase(), col);
      }
    } finally {
      closeSilently(rs);
    }
  }
예제 #18
0
 @Test
 public void testPrimaryKeys() throws Exception {
   TestMMDatabaseMetaData.compareResultSet(
       dbMetadata.getPrimaryKeys(VDB, null, "%")); // $NON-NLS-1$ //$NON-NLS-2$
 }
 public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException {
   return throwExceptionDelegate.getPrimaryKeys(catalog, schema, table);
 }
예제 #20
0
  @Test
  public void columnOrderOfgetPrimaryKeys() throws SQLException {
    ResultSet rs;
    ResultSetMetaData rsmeta;

    stat.executeUpdate("create table nopk (c1, c2, c3, c4);");
    stat.executeUpdate("create table pk1 (col1 primary key, col2, col3);");
    stat.executeUpdate("create table pk2 (col1, col2 primary key, col3);");
    stat.executeUpdate("create table pk3 (col1, col2, col3, col4, primary key (col3, col2  ));");
    // extra spaces and mixed case are intentional, do not remove!
    stat.executeUpdate(
        "create table pk4 (col1, col2, col3, col4, "
            + "\r\nCONSTraint\r\nnamed  primary\r\n\t\t key   (col3, col2  ));");

    rs = meta.getPrimaryKeys(null, null, "nopk");
    assertFalse(rs.next());
    rsmeta = rs.getMetaData();
    assertEquals(rsmeta.getColumnCount(), 6);
    assertEquals(rsmeta.getColumnName(1), "TABLE_CAT");
    assertEquals(rsmeta.getColumnName(2), "TABLE_SCHEM");
    assertEquals(rsmeta.getColumnName(3), "TABLE_NAME");
    assertEquals(rsmeta.getColumnName(4), "COLUMN_NAME");
    assertEquals(rsmeta.getColumnName(5), "KEY_SEQ");
    assertEquals(rsmeta.getColumnName(6), "PK_NAME");
    rs.close();

    rs = meta.getPrimaryKeys(null, null, "pk1");
    assertTrue(rs.next());
    assertEquals(rs.getString("PK_NAME"), null);
    assertEquals(rs.getString("COLUMN_NAME"), "col1");
    assertFalse(rs.next());
    rs.close();

    rs = meta.getPrimaryKeys(null, null, "pk2");
    assertTrue(rs.next());
    assertEquals(rs.getString("PK_NAME"), null);
    assertEquals(rs.getString("COLUMN_NAME"), "col2");
    assertFalse(rs.next());
    rs.close();

    rs = meta.getPrimaryKeys(null, null, "pk3");
    assertTrue(rs.next());
    assertEquals(rs.getString("COLUMN_NAME"), "col2");
    assertEquals(rs.getString("PK_NAME"), null);
    assertEquals(rs.getInt("KEY_SEQ"), 1);
    assertTrue(rs.next());
    assertEquals(rs.getString("COLUMN_NAME"), "col3");
    assertEquals(rs.getString("PK_NAME"), null);
    assertEquals(rs.getInt("KEY_SEQ"), 0);
    assertFalse(rs.next());
    rs.close();

    rs = meta.getPrimaryKeys(null, null, "pk4");
    assertTrue(rs.next());
    assertEquals(rs.getString("COLUMN_NAME"), "col2");
    assertEquals(rs.getString("PK_NAME"), "named");
    assertEquals(rs.getInt("KEY_SEQ"), 1);
    assertTrue(rs.next());
    assertEquals(rs.getString("COLUMN_NAME"), "col3");
    assertEquals(rs.getString("PK_NAME"), "named");
    assertEquals(rs.getInt("KEY_SEQ"), 0);
    assertFalse(rs.next());
    rs.close();
  }
예제 #21
0
  public void testOne() throws Exception {

    Connection conn = newConnection();
    PreparedStatement pstmt;
    int updateCount;

    try {
      pstmt = conn.prepareStatement("DROP TABLE t1 IF EXISTS");

      pstmt.executeUpdate();
      pstmt.close();

      pstmt =
          conn.prepareStatement(
              "CREATE TABLE t1 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
                  + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );");
      updateCount = pstmt.executeUpdate();

      assertTrue("expected update count of zero", updateCount == 0);

      pstmt = conn.prepareStatement("CREATE INDEX t1 ON t1 (cha );");
      updateCount = pstmt.executeUpdate();
      pstmt = conn.prepareStatement("DROP TABLE t2 IF EXISTS");
      updateCount = pstmt.executeUpdate();
      pstmt =
          conn.prepareStatement(
              "CREATE TABLE t2 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
                  + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );");
      updateCount = pstmt.executeUpdate();
      pstmt = conn.prepareStatement("CREATE INDEX t2 ON t2 (cha );");
      updateCount = pstmt.executeUpdate();

      DatabaseMetaData dbmd = conn.getMetaData();
      ResultSet rsp = dbmd.getTablePrivileges(null, null, "T1");

      while (rsp.next()) {
        System.out.println("Table: " + rsp.getString(3) + " priv: " + rsp.getString(6));
      }

      rsp = dbmd.getIndexInfo(null, null, "T1", false, false);

      while (rsp.next()) {
        System.out.println("Table: " + rsp.getString(3) + " IndexName: " + rsp.getString(6));
      }

      rsp = dbmd.getIndexInfo(null, null, "T2", false, false);

      while (rsp.next()) {
        System.out.println("Table: " + rsp.getString(3) + " IndexName: " + rsp.getString(6));
      }

      pstmt = conn.prepareStatement("DROP INDEX t2;");
      updateCount = pstmt.executeUpdate();
      rsp = dbmd.getIndexInfo(null, null, "T2", false, false);

      assertTrue("expected getIndexInfo returns empty resultset", rsp.next() == false);

      ResultSet rs = dbmd.getTables(null, null, "T1", new String[] {"TABLE"});
      ArrayList tablesarr = new ArrayList();
      int i;

      for (i = 0; rs.next(); i++) {
        String tempstr = rs.getString("TABLE_NAME").trim().toLowerCase();

        tablesarr.add(tempstr);
      }

      rs.close();
      assertTrue("expected table t1 count of 1", i == 1);

      Iterator it = tablesarr.iterator();

      for (; it.hasNext(); ) {

        // create new ArrayList and HashMap for the table
        String tablename = ((String) it.next()).trim();
        List collist = new ArrayList(30);

        rs = dbmd.getColumns(null, null, tablename.toUpperCase(), null);

        for (i = 0; rs.next(); i++) {
          collist.add(rs.getString("COLUMN_NAME").trim().toLowerCase());
        }

        rs.close();
      }

      pstmt = conn.prepareStatement("DROP TABLE t_1 IF EXISTS");

      pstmt.executeUpdate();
      pstmt.close();

      pstmt =
          conn.prepareStatement(
              "CREATE TABLE t_1 (cha CHARACTER(10), deci DECIMAL(10,2), doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
                  + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP, bool BOOLEAN );");
      updateCount = pstmt.executeUpdate();

      assertTrue("expected update count of zero", updateCount == 0);

      rs = dbmd.getTables(null, null, "T\\_1", new String[] {"TABLE"});

      for (i = 0; rs.next(); i++) {
        String tempstr = rs.getString("TABLE_NAME").trim().toLowerCase();

        tablesarr.add(tempstr);
      }

      rs.close();
      assertTrue("expected table t_1 count of 1", i == 1);

      // test various methods
      dbmd.getPrimaryKeys(null, null, "T_1");
      dbmd.getImportedKeys(null, null, "T_1");
      dbmd.getCrossReference(null, null, "T_1", null, null, "T_1");

      // test ResultSetMetaData
      pstmt = conn.prepareStatement("INSERT INTO T_1 (cha, deci, doub) VALUES ('name', 10.23, 0)");

      pstmt.executeUpdate();
      pstmt.close();

      pstmt = conn.prepareStatement("SELECT * FROM T_1");
      rs = pstmt.executeQuery();

      ResultSetMetaData md = rs.getMetaData();
      int x = md.getColumnDisplaySize(1);
      int y = md.getColumnDisplaySize(2);
      int b = md.getPrecision(2);
      int c = md.getScale(1);
      int d = md.getScale(2);
      String e = md.getColumnClassName(10);
      boolean testresult =
          (x == 10)
              && (y == 12)
              && (b == 10)
              && (c == 0)
              && (d == 2)
              && e.equals("java.sql.Timestamp");

      assertTrue("wrong result metadata", testresult);

      e = md.getColumnClassName(11);
      testresult = e.equals("java.lang.Boolean");

      assertTrue("wrong result metadata", testresult);
      pstmt.close();

      //
    } catch (Exception e) {
      assertTrue("unable to prepare or execute DDL", false);
    } finally {
      conn.close();
    }
  }
예제 #22
0
  /**
   * Read metadata about a table from the database.
   *
   * @param table The RDBMS table.
   * @return A map of information about the columns. The key is the name of the column, a String;
   *     the value is a ColumnInfo object.
   * @exception SQLException If there is a problem retrieving information from the RDBMS.
   */
  private static Map<String, ColumnInfo> retrieveColumnInfo(String table) throws SQLException {
    Connection connection = null;
    ResultSet pkcolumns = null;
    ResultSet columns = null;

    try {
      String schema = ConfigurationManager.getProperty("db.schema");
      if (StringUtils.isBlank(schema)) {
        schema = null;
      }
      String catalog = null;

      int dotIndex = table.indexOf('.');
      if (dotIndex > 0) {
        catalog = table.substring(0, dotIndex);
        table = table.substring(dotIndex + 1, table.length());
        log.warn("catalog: " + catalog);
        log.warn("table: " + table);
      }

      connection = getConnection();

      DatabaseMetaData metadata = connection.getMetaData();
      Map<String, ColumnInfo> results = new HashMap<String, ColumnInfo>();

      int max = metadata.getMaxTableNameLength();
      String tname = ((max > 0) && (table.length() >= max)) ? table.substring(0, max - 1) : table;

      pkcolumns = metadata.getPrimaryKeys(catalog, schema, tname);

      Set<String> pks = new HashSet<String>();

      while (pkcolumns.next()) {
        pks.add(pkcolumns.getString(4));
      }

      columns = metadata.getColumns(catalog, schema, tname, null);

      while (columns.next()) {
        String column = columns.getString(4);
        ColumnInfo cinfo = new ColumnInfo();
        cinfo.setName(column);
        cinfo.setType((int) columns.getShort(5));

        if (pks.contains(column)) {
          cinfo.setIsPrimaryKey(true);
        }

        results.put(column, cinfo);
      }

      return Collections.unmodifiableMap(results);
    } finally {
      if (pkcolumns != null) {
        try {
          pkcolumns.close();
        } catch (SQLException sqle) {
        }
      }

      if (columns != null) {
        try {
          columns.close();
        } catch (SQLException sqle) {
        }
      }

      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException sqle) {
        }
      }
    }
  }
 protected ResultSet getPrimaryKeyResultSet(
     DatabaseMetaData md, String schemaName, String tableName) throws SQLException {
   return md.getPrimaryKeys(schemaName, null, tableName);
 }
  protected void alter(Class<?> tableClass, Alterable... alterables) throws Exception {

    try (LoggingTimer loggingTimer = new LoggingTimer()) {
      Field tableNameField = tableClass.getField("TABLE_NAME");

      String tableName = (String) tableNameField.get(null);

      DatabaseMetaData databaseMetaData = connection.getMetaData();

      try (ResultSet rs1 = databaseMetaData.getPrimaryKeys(null, null, tableName);
          ResultSet rs2 =
              databaseMetaData.getIndexInfo(
                  null, null, normalizeName(tableName, databaseMetaData), false, false)) {

        Set<String> primaryKeyNames = new HashSet<>();

        while (rs1.next()) {
          String primaryKeyName = StringUtil.toUpperCase(rs1.getString("PK_NAME"));

          if (primaryKeyName != null) {
            primaryKeyNames.add(primaryKeyName);
          }
        }

        Map<String, Set<String>> columnNamesMap = new HashMap<>();

        while (rs2.next()) {
          String indexName = StringUtil.toUpperCase(rs2.getString("INDEX_NAME"));

          if ((indexName == null) || primaryKeyNames.contains(indexName)) {

            continue;
          }

          Set<String> columnNames = columnNamesMap.get(indexName);

          if (columnNames == null) {
            columnNames = new HashSet<>();

            columnNamesMap.put(indexName, columnNames);
          }

          columnNames.add(StringUtil.toUpperCase(rs2.getString("COLUMN_NAME")));
        }

        for (Alterable alterable : alterables) {
          for (Map.Entry<String, Set<String>> entry : columnNamesMap.entrySet()) {

            if (alterable.shouldDropIndex(entry.getValue())) {
              runSQL("drop index " + entry.getKey() + " on " + tableName);
            }
          }

          runSQL(alterable.getSQL(tableName));

          List<ObjectValuePair<String, IndexMetadata>> objectValuePairs =
              getIndexesSQL(tableClass.getClassLoader(), tableName);

          if (objectValuePairs == null) {
            continue;
          }

          for (ObjectValuePair<String, IndexMetadata> objectValuePair : objectValuePairs) {

            IndexMetadata indexMetadata = objectValuePair.getValue();

            if (alterable.shouldAddIndex(Arrays.asList(indexMetadata.getColumnNames()))) {

              runSQLTemplateString(objectValuePair.getKey(), false, true);
            }
          }
        }
      } catch (SQLException sqle) {
        if (_log.isWarnEnabled()) {
          _log.warn("Fallback to recreating the table", sqle);
        }

        Field tableColumnsField = tableClass.getField("TABLE_COLUMNS");
        Field tableSQLCreateField = tableClass.getField("TABLE_SQL_CREATE");
        Field tableSQLAddIndexesField = tableClass.getField("TABLE_SQL_ADD_INDEXES");

        upgradeTable(
            tableName,
            (Object[][]) tableColumnsField.get(null),
            (String) tableSQLCreateField.get(null),
            (String[]) tableSQLAddIndexesField.get(null));
      }
    }
  }
  public void testMetaData() {

    String ddl0 =
        "DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;";
    String ddl1 =
        "CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
    String ddl2 =
        "CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
    String ddl3 =
        "CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
    String result1 = "1";
    String result2 = "2";
    String result3 = "3";
    String result4 = "4";
    String result5 = "5";

    try {
      stmnt.execute(ddl0);
      stmnt.execute(ddl1);
      stmnt.execute(ddl2);
      stmnt.execute(ddl3);

      DatabaseMetaData md = connection.getMetaData();

      {
        System.out.println("Testing DatabaseMetaData methods");
        System.out.println(md.getDatabaseMajorVersion());
        System.out.println(md.getDatabaseMinorVersion());
        System.out.println(md.getDatabaseProductName());
        System.out.println(md.getDatabaseProductVersion());
        System.out.println(md.getDefaultTransactionIsolation());
        System.out.println(md.getDriverMajorVersion());
        System.out.println(md.getDriverMinorVersion());
        System.out.println(md.getDriverName());
        System.out.println(md.getDriverVersion());
        System.out.println(md.getExtraNameCharacters());
        System.out.println(md.getIdentifierQuoteString());
        System.out.println(md.getJDBCMajorVersion());
        System.out.println(md.getJDBCMinorVersion());
        System.out.println(md.getMaxBinaryLiteralLength());
        System.out.println(md.getMaxCatalogNameLength());
        System.out.println(md.getMaxColumnsInGroupBy());
        System.out.println(md.getMaxColumnsInIndex());
        System.out.println(md.getMaxColumnsInOrderBy());
        System.out.println(md.getMaxColumnsInSelect());
        System.out.println(md.getMaxColumnsInTable());
        System.out.println(md.getMaxConnections());
        System.out.println(md.getMaxCursorNameLength());
        System.out.println(md.getMaxIndexLength());
        System.out.println(md.getMaxProcedureNameLength());
        System.out.println(md.getMaxRowSize());
        System.out.println(md.getMaxSchemaNameLength());
        System.out.println(md.getMaxStatementLength());
        System.out.println(md.getMaxStatements());
        System.out.println(md.getMaxTableNameLength());
        System.out.println(md.getMaxUserNameLength());
        System.out.println(md.getNumericFunctions());
        System.out.println(md.getProcedureTerm());
        System.out.println(md.getResultSetHoldability());
        System.out.println(md.getSchemaTerm());
        System.out.println(md.getSearchStringEscape());
        System.out.println("Testing DatabaseMetaData.getSQLKeywords()");
        System.out.println(md.getSQLKeywords());
        System.out.println(md.getSQLStateType());
        System.out.println("Testing DatabaseMetaData.getStringFunctions()");
        System.out.println(md.getStringFunctions());
        System.out.println("Testing DatabaseMetaData.getSystemFunctions()");
        System.out.println(md.getSystemFunctions());
        System.out.println("Testing DatabaseMetaData.getTimeDateFunctions()");
        System.out.println(md.getTimeDateFunctions());
        System.out.println(md.getURL());
        System.out.println(md.getUserName());
        System.out.println(DatabaseMetaData.importedKeyCascade);
        System.out.println(md.isCatalogAtStart());
        System.out.println(md.isReadOnly());

        ResultSet rs;

        rs = md.getPrimaryKeys(null, null, "USER");

        ResultSetMetaData rsmd = rs.getMetaData();
        String result0 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result0 += rs.getString(i + 1) + ":";
          }

          result0 += "\n";
        }

        rs.close();
        System.out.println(result0);
      }

      {
        ResultSet rs;

        rs = md.getBestRowIdentifier(null, null, "USER", 0, true);

        ResultSetMetaData rsmd = rs.getMetaData();
        String result0 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result0 += rs.getString(i + 1) + ":";
          }

          result0 += "\n";
        }

        rs.close();
        System.out.println(result0);
      }

      {
        ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK");
        ResultSetMetaData rsmd = rs.getMetaData();

        result1 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result1 += rs.getString(i + 1) + ":";
          }

          result1 += "\n";
        }

        rs.close();
        System.out.println(result1);
      }

      {
        ResultSet rs =
            md.getCrossReference(null, null, "ADDRESSBOOK_CATEGORY", null, null, "ADDRESSBOOK");
        ResultSetMetaData rsmd = rs.getMetaData();

        result2 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result2 += rs.getString(i + 1) + ":";
          }

          result2 += "\n";
        }

        rs.close();
        System.out.println(result2);
      }

      {
        ResultSet rs = md.getExportedKeys(null, null, "USER");
        ResultSetMetaData rsmd = rs.getMetaData();

        result3 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result3 += rs.getString(i + 1) + ":";
          }

          result3 += "\n";
        }

        rs.close();
        System.out.println(result3);
      }

      {
        ResultSet rs = md.getCrossReference(null, null, "USER", null, null, "ADDRESSBOOK_CATEGORY");
        ResultSetMetaData rsmd = rs.getMetaData();

        result4 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result4 += rs.getString(i + 1) + ":";
          }

          result4 += "\n";
        }

        rs.close();
        System.out.println(result4);
      }

      {
        stmnt.execute("DROP TABLE T IF EXISTS;");
        stmnt.execute("CREATE TABLE T (I IDENTITY, A CHAR(20), B CHAR(20));");
        stmnt.execute("INSERT INTO T VALUES (NULL, 'get_column_name', '" + getColumnName + "');");

        ResultSet rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\", 1 FROM T;");
        ResultSetMetaData rsmd = rs.getMetaData();

        result5 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result5 += rsmd.getColumnName(i + 1) + ":" + rs.getString(i + 1) + ":";
          }

          result5 += "\n";
        }

        rs.close();

        rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\", 1 FROM T;");
        rsmd = rs.getMetaData();

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result5 += rsmd.getColumnLabel(i + 1) + ":" + rs.getString(i + 1) + ":";
          }

          result5 += "\n";
        }

        System.out.println(result5);
        System.out.println("first column identity: " + rsmd.isAutoIncrement(1));
        rsmd.isCaseSensitive(1);
        rsmd.isCurrency(1);
        rsmd.isDefinitelyWritable(1);
        rsmd.isNullable(1);
        rsmd.isReadOnly(1);
        rsmd.isSearchable(1);
        rsmd.isSigned(1);
        rsmd.isWritable(1);
        rs.close();

        // test identity with PreparedStatement
        pstmnt = connection.prepareStatement("INSERT INTO T VALUES (?,?,?)");

        pstmnt.setString(1, null);
        pstmnt.setString(2, "test");
        pstmnt.setString(3, "test2");
        pstmnt.executeUpdate();

        pstmnt = connection.prepareStatement("call identity()");

        ResultSet rsi = pstmnt.executeQuery();

        rsi.next();

        int identity = rsi.getInt(1);

        System.out.println("call identity(): " + identity);
        rsi.close();
      }
    } catch (SQLException e) {
      fail(e.getMessage());
    }

    System.out.println("testMetaData complete");

    // assert equality of exported and imported with xref
    assertEquals(result1, result2);
    assertEquals(result3, result4);
  }
예제 #26
0
  private void tryReadMetaData(Connection conn, String oCatalog, String oSchema, String tableName)
      throws SQLException {

    DatabaseMetaData meta = conn.getMetaData();
    storesLowerCase = meta.storesLowerCaseIdentifiers();
    storesMixedCase = meta.storesMixedCaseIdentifiers();
    storesMixedCaseQuoted = meta.storesMixedCaseQuotedIdentifiers();
    supportsMixedCaseIdentifiers = meta.supportsMixedCaseIdentifiers();

    ResultSet rs = meta.getTables(oCatalog, oSchema, tableName, null);
    if (rs.next() && rs.next()) {
      throw DbException.get(ErrorCode.SCHEMA_NAME_MUST_MATCH, tableName);
    }
    rs.close();
    rs = meta.getColumns(null, null, tableName, null);
    int i = 0;
    ArrayList<Column> columnList = New.arrayList();
    HashMap<String, Column> columnMap = New.hashMap();
    String catalog = null, schema = null;
    while (rs.next()) {
      String thisCatalog = rs.getString("TABLE_CAT");
      if (catalog == null) {
        catalog = thisCatalog;
      }
      String thisSchema = rs.getString("TABLE_SCHEM");
      if (schema == null) {
        schema = thisSchema;
      }
      if (!StringUtils.equals(catalog, thisCatalog) || !StringUtils.equals(schema, thisSchema)) {
        // if the table exists in multiple schemas or tables,
        // use the alternative solution
        columnMap.clear();
        columnList.clear();
        break;
      }
      String n = rs.getString("COLUMN_NAME");
      n = convertColumnName(n);
      int sqlType = rs.getInt("DATA_TYPE");
      long precision = rs.getInt("COLUMN_SIZE");
      precision = convertPrecision(sqlType, precision);
      int scale = rs.getInt("DECIMAL_DIGITS");
      scale = convertScale(sqlType, scale);
      int displaySize = MathUtils.convertLongToInt(precision);
      int type = DataType.convertSQLTypeToValueType(sqlType);
      Column col = new Column(n, type, precision, scale, displaySize);
      col.setTable(this, i++);
      columnList.add(col);
      columnMap.put(n, col);
    }
    rs.close();
    // check if the table is accessible
    Statement stat = null;
    try {
      stat = conn.createStatement();
      rs = stat.executeQuery("SELECT * FROM " + tableName + " T WHERE 1=0");
      if (columnList.size() == 0) {
        // alternative solution
        ResultSetMetaData rsMeta = rs.getMetaData();
        for (i = 0; i < rsMeta.getColumnCount(); ) {
          String n = rsMeta.getColumnName(i + 1);
          n = convertColumnName(n);
          int sqlType = rsMeta.getColumnType(i + 1);
          long precision = rsMeta.getPrecision(i + 1);
          precision = convertPrecision(sqlType, precision);
          int scale = rsMeta.getScale(i + 1);
          scale = convertScale(sqlType, scale);
          int displaySize = rsMeta.getColumnDisplaySize(i + 1);
          int type = DataType.getValueTypeFromResultSet(rsMeta, i + 1);
          Column col = new Column(n, type, precision, scale, displaySize);
          col.setTable(this, i++);
          columnList.add(col);
          columnMap.put(n, col);
        }
      }
      rs.close();
    } catch (Exception e) {
      throw DbException.get(
          ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, e, tableName + "(" + e.toString() + ")");
    } finally {
      JdbcUtils.closeSilently(stat);
    }
    Column[] cols = new Column[columnList.size()];
    columnList.toArray(cols);
    setColumns(cols);
    // create scan index

    // load primary keys
    try {
      rs = meta.getPrimaryKeys(null, null, tableName);
    } catch (Exception e) {
      // Some ODBC bridge drivers don't support it:
      // some combinations of "DataDirect SequeLink(R) for JDBC"
      // http://www.datadirect.com/index.ssp
      rs = null;
    }
    String pkName = "";
    ArrayList<Column> list;
    if (rs != null && rs.next()) {
      // the problem is, the rows are not sorted by KEY_SEQ
      list = New.arrayList();
      do {
        int idx = rs.getInt("KEY_SEQ");
        if (pkName == null) {
          pkName = rs.getString("PK_NAME");
        }
        while (list.size() < idx) {
          list.add(null);
        }
        String col = rs.getString("COLUMN_NAME");
        col = convertColumnName(col);
        Column column = columnMap.get(col);
        if (idx == 0) {
          // workaround for a bug in the SQLite JDBC driver
          list.add(column);
        } else {
          list.set(idx - 1, column);
        }
      } while (rs.next());
      addIndex(pkName, list, IndexType.createPrimaryKey(false));
      rs.close();
    }

    try {
      rs = meta.getIndexInfo(null, null, tableName, false, true);
    } catch (Exception e) {
      // Oracle throws an exception if the table is not found or is a
      // SYNONYM
      rs = null;
    }
    String indexName = null;
    list = New.arrayList();
    IndexType indexType = null;
    if (rs != null) {
      while (rs.next()) {
        if (rs.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic) {
          // ignore index statistics
          continue;
        }
        String newIndex = rs.getString("INDEX_NAME");
        if (pkName.equals(newIndex)) {
          continue;
        }
        if (indexName != null && !indexName.equals(newIndex)) {
          addIndex(indexName, list, indexType);
          indexName = null;
        }
        if (indexName == null) {
          indexName = newIndex;
          list.clear();
        }
        boolean unique = !rs.getBoolean("NON_UNIQUE");
        indexType = unique ? IndexType.createUnique(false) : IndexType.createNonUnique();
        String col = rs.getString("COLUMN_NAME");
        col = convertColumnName(col);
        Column column = columnMap.get(col);
        list.add(column);
      }
      rs.close();
    }
    if (indexName != null) {
      addIndex(indexName, list, indexType);
    }
    shardingKeyIndex();
  }