Example #1
0
 private ImmutableMap<String, JdbcTable> computeTables() {
   Connection connection = null;
   ResultSet resultSet = null;
   try {
     connection = dataSource.getConnection();
     DatabaseMetaData metaData = connection.getMetaData();
     resultSet = metaData.getTables(catalog, schema, null, null);
     final ImmutableMap.Builder<String, JdbcTable> builder = ImmutableMap.builder();
     while (resultSet.next()) {
       final String tableName = resultSet.getString(3);
       final String catalogName = resultSet.getString(1);
       final String schemaName = resultSet.getString(2);
       final String tableTypeName = resultSet.getString(4);
       // Clean up table type. In particular, this ensures that 'SYSTEM TABLE',
       // returned by Phoenix among others, maps to TableType.SYSTEM_TABLE.
       // We know enum constants are upper-case without spaces, so we can't
       // make things worse.
       final String tableTypeName2 = tableTypeName.toUpperCase().replace(' ', '_');
       final TableType tableType = Util.enumVal(TableType.class, tableTypeName2);
       final JdbcTable table = new JdbcTable(this, catalogName, schemaName, tableName, tableType);
       builder.put(tableName, table);
     }
     return builder.build();
   } catch (SQLException e) {
     throw new RuntimeException("Exception while reading tables", e);
   } finally {
     close(connection, null, resultSet);
   }
 }
  public void checkTables(consumer<Boolean> consumer) {

    Connection connection = null;
    ResultSet result = null;

    try {

      connection = HikariManager.getInstance().getConnection();

      DatabaseMetaData dbm = connection.getMetaData();

      boolean playersExists = false;
      boolean periodicExists = false;
      boolean allExist = false;

      result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "players", null);
      if (result.next()) playersExists = true;

      result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "periodic", null);
      if (result.next()) periodicExists = true;

      if (playersExists && periodicExists) allExist = true;

      if (consumer != null) consumer.accept(allExist);

    } catch (SQLException e) {

      e.printStackTrace();

    } finally {

      HikariManager.getInstance().close(connection, null, result);
    }
  }
Example #3
0
 SqlDialect get(DataSource dataSource) {
   Connection connection = null;
   try {
     connection = dataSource.getConnection();
     DatabaseMetaData metaData = connection.getMetaData();
     String productName = metaData.getDatabaseProductName();
     String productVersion = metaData.getDatabaseProductVersion();
     List key = Arrays.asList(productName, productVersion);
     SqlDialect dialect = map.get(key);
     if (dialect == null) {
       final SqlDialect.DatabaseProduct product =
           SqlDialect.getProduct(productName, productVersion);
       dialect = new SqlDialect(product, productName, metaData.getIdentifierQuoteString());
       map.put(key, dialect);
     }
     connection.close();
     connection = null;
     return dialect;
   } catch (SQLException e) {
     throw new RuntimeException(e);
   } finally {
     if (connection != null) {
       try {
         connection.close();
       } catch (SQLException e) {
         // ignore
       }
     }
   }
 }
  /** Creates tables if they do not exist. */
  private void initializeTables() {
    Connection conn = null;
    Statement statement = null;
    try {
      conn = retrieveConnection();
      statement = conn.createStatement();
      statement.setQueryTimeout(30);

      // One table holding the playername id relation
      String playerQuery =
          String.format(
              "CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT, %s STRING)",
              playerTable, "name");
      statement.executeUpdate(playerQuery);

      // One column for every message
      StringBuilder columns = new StringBuilder();
      for (MessageNode node : MessageNode.getMessageNodes()) {
        MsgCategory cat = messages.getCat(node);
        if (node.getColumnName() != null
            && (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME)) {
          columns.append(',');
          columns.append(node.getColumnName());
        }
      }

      String msgQuery =
          String.format(
              "CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY UNIQUE %s)",
              msgTable, columns);
      statement.executeUpdate(msgQuery);

      // Check if all columns are present
      DatabaseMetaData dmd = conn.getMetaData();
      // Add missing columns
      for (MessageNode node : MessageNode.getMessageNodes()) {
        MsgCategory cat = messages.getCat(node);
        if (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME) {
          ResultSet set = dmd.getColumns(null, null, msgTable, node.getColumnName());
          if (!set.next()) {
            String updateQuery =
                String.format("ALTER TABLE %s ADD COLUMN %s", msgTable, node.getColumnName());
            statement.executeUpdate(updateQuery);
          }
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (conn != null) conn.close();
        if (statement != null) statement.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
 private List getAllTables(Connection conn) throws SQLException {
   DatabaseMetaData dbMetaData = conn.getMetaData();
   ResultSet rs = dbMetaData.getTables(getCatalog(), getSchema(), null, null);
   List tables = new ArrayList();
   while (rs.next()) {
     tables.add(createTable(conn, rs));
   }
   return tables;
 }
  public synchronized void serialEvent(SerialPortEvent oEvent) {

    try {
      switch (oEvent.getEventType()) {
        case SerialPortEvent.DATA_AVAILABLE:
          if (input == null) {
            System.out.println("here11");
            input = new BufferedReader(new InputStreamReader(serialPort.getInputStream()));
          }
          String inputLine = input.readLine();
          // System.out.println(input.readLine().trim());
          if (inputLine.equals("")) {
          } else {
            String url = "jdbc:mysql://localhost/secureplanet";
            Properties prop = new Properties();
            prop.setProperty("user", "root");
            prop.setProperty("password", "toor");
            Driver d = new com.mysql.jdbc.Driver();
            Connection conn = d.connect(url, prop);
            if (conn == null) {
              System.out.println("connection failed");
              return;
            }
            DatabaseMetaData dm = conn.getMetaData();
            String dbversion = dm.getDatabaseProductVersion();
            String dbname = dm.getDatabaseProductName();
            System.out.println("name:" + dbname);
            System.out.println("version:" + dbversion);

            String rfidtoken = inputLine.trim();
            Statement stmt = conn.createStatement();

            Double lat = 17.4416;
            Double lng = 78.3826;

            String sql =
                "INSERT INTO smarttracking "
                    + "VALUES ('"
                    + rfidtoken
                    + "','"
                    + lat
                    + "','"
                    + lng
                    + "')";
            stmt.executeUpdate(sql);
          }
          break;

        default:
          break;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #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;
 }
Example #8
0
 public static String[] getSchemas(Connection connection) throws Exception {
   if (connection != null) {
     List<String> schemaList = new ArrayList<String>();
     DatabaseMetaData mObject = connection.getMetaData();
     ResultSet schemas = mObject.getSchemas();
     while (schemas.next()) {
       schemaList.add(schemas.getString(DBConstants.DataServiceGenerator.TABLE_SCHEM));
     }
     String str[] = schemaList.toArray(new String[schemaList.size()]);
     return str;
   } else {
     return null;
   }
 }
  private Table _getTable(String catalog, String schema, String tableName) throws SQLException {
    if (tableName == null || tableName.trim().length() == 0)
      throw new IllegalArgumentException("tableName must be not empty");
    catalog = StringHelper.defaultIfEmpty(catalog, null);
    schema = StringHelper.defaultIfEmpty(schema, null);

    Connection conn = getConnection();
    DatabaseMetaData dbMetaData = conn.getMetaData();
    ResultSet rs = dbMetaData.getTables(catalog, schema, tableName, null);
    while (rs.next()) {
      Table table = createTable(conn, rs);
      return table;
    }
    return null;
  }
Example #10
0
  boolean tiene_tablas() {
    DatabaseMetaData dbmd = null;
    Integer contador = 0;
    try {
      dbmd = conexion.getMetaData();
      String[] tipo = {"TABLE"};
      ResultSet rs = dbmd.getTables(null, null, "%", tipo);
      while (rs.next()) {
        contador++;
      }
    } catch (SQLException e) {
      System.out.println("Error al obtener información de la bd...");
    }

    return contador > 0;
  }
Example #11
0
  private ResultSet getColumnNames(
      DatabaseMetaData metaData,
      String schema,
      String dbName,
      String tableName,
      String columnNamePattern)
      throws SQLException {
    ResultSet columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
    if (columnNames.next()) {
      columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
    } else {
      columnNames = metaData.getColumns(null, schema, tableName, columnNamePattern);
    }

    return columnNames;
  }
Example #12
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;
  }
Example #13
0
  // UTILITY
  public boolean hasTable(String tableName) {

    try {
      DatabaseMetaData dbm = connection.getMetaData();
      ResultSet tables = dbm.getTables(null, null, tableName, null);
      while (tables.next()) {
        String foundName = tables.getString("TABLE_NAME");
        if (tableName.equals(foundName)) {
          return true;
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return false;
  }
Example #14
0
  @Override
  public ArrayList<String> getListOfTables(Connection conn)
      throws SQLException, ClassNotFoundException {

    ArrayList<String> tableNames = new ArrayList<String>();
    DatabaseMetaData dmd = conn.getMetaData();
    // Pedro: 2012/12/03 comment out to enable loading Views in Karma (on behalf of Maria)
    // ResultSet rs = dmd.getTables(null, null, null, new String[] {"TABLE"});
    ResultSet rs = dmd.getTables(null, null, null, new String[] {"TABLE", "VIEW"});
    while (rs.next()) {
      String tablename = rs.getString(3);
      String schema = rs.getString(2);
      if (schema != null && schema.length() > 0) tableNames.add(schema + "." + tablename);
      else tableNames.add(tablename);
    }
    Collections.sort(tableNames);
    return tableNames;
  }
 private Connection createDatasource() throws ClassNotFoundException, SQLException {
   Connection connection = getConnection();
   boolean exist = false;
   int tableNameColumn = 3;
   DatabaseMetaData dbm = connection.getMetaData();
   for (ResultSet rs = dbm.getTables(null, null, null, null); rs.next(); ) {
     if (rs.getString(tableNameColumn).equals(TABLE_NAME)) {
       exist = true;
       rs.close();
       break;
     }
   }
   if (!exist) {
     Statement statement = connection.createStatement();
     statement.executeUpdate(
         "CREATE TABLE " + TABLE_NAME + " (name VARCHAR(32) PRIMARY KEY, balance FLOAT)");
   }
   return connection;
 }
Example #16
0
  public static List<String> getTableNamesList(
      DatabaseMetaData mObject, String dbName, String schema) throws SQLException {
    List<String> tableList = new ArrayList<String>();
    ResultSet tableNamesList = mObject.getTables(dbName, schema, null, null);
    while (tableNamesList.next()) {
      tableList.add(tableNamesList.getString(DBConstants.DataServiceGenerator.TABLE_NAME));
    }

    return tableList;
  }
Example #17
0
  @Signature
  public Memory getSchemas(Environment env) throws SQLException {
    ResultSet schemas = metaData.getSchemas();
    ArrayMemory r = new ArrayMemory();

    while (schemas.next()) {
      r.add(new PSqlResult(env, schemas).toArray(env));
    }

    return r.toConstant();
  }
Example #18
0
  public static void main(String args[]) {
    try {
      Class.forName("org.gjt.mm.mysql.Driver");

      Connection con =
          DriverManager.getConnection("jdbc:mysql://localhost:3333/world", "root", "root");

      DatabaseMetaData dbmd = con.getMetaData();
      String table[] = {"TABLE"};
      ResultSet rs = dbmd.getTables(null, null, null, table);

      while (rs.next()) {
        System.out.println(rs.getString(3));
      }

      con.close();

    } catch (Exception e) {
      System.out.println(e);
    }
  }
Example #19
0
 private static Map<PwmAboutProperty, String> getConnectionDebugProperties(
     final Connection connection) {
   if (connection != null) {
     try {
       final Map<PwmAboutProperty, String> returnObj = new LinkedHashMap<>();
       final DatabaseMetaData databaseMetaData = connection.getMetaData();
       returnObj.put(PwmAboutProperty.database_driverName, databaseMetaData.getDriverName());
       returnObj.put(PwmAboutProperty.database_driverVersion, databaseMetaData.getDriverVersion());
       returnObj.put(
           PwmAboutProperty.database_databaseProductName,
           databaseMetaData.getDatabaseProductName());
       returnObj.put(
           PwmAboutProperty.database_databaseProductVersion,
           databaseMetaData.getDatabaseProductVersion());
       return Collections.unmodifiableMap(returnObj);
     } catch (SQLException e) {
       LOGGER.error("error rading jdbc meta data: " + e.getMessage());
     }
   }
   return Collections.emptyMap();
 }
Example #20
0
  /**
   * Returns a table's foreign keys and their columns as a Map from the key name to the ForeignKey
   * object.
   *
   * <p>A foreign key may not have a name. On such a database, 2 foreign keys must reference 2
   * different tables. Otherwise there's no way to tell them apart and the foreign key information
   * reported by DatabaseMetaData becomes ill-formed.
   */
  public static Map<String, ForeignKey> getForeignKeys(DatabaseMetaData metadata, String tableName)
      throws Exception {
    ResultSet keys =
        metadata.getImportedKeys(
            metadata.getConnection().getCatalog(), metadata.getUserName(), tableName);
    Map<String, ForeignKey> map = new HashMap<String, ForeignKey>();

    while (keys.next()) {
      String table = keys.getString(IMPORTED_PK_TAB_NAME);
      String name = keys.getString(IMPORTED_FK_KEY_NAME);
      if (name == null || name.length() == 0) name = "UNNAMED_FK_" + table;

      ForeignKey key = map.get(name);
      if (key == null) {
        map.put(name, key = new ForeignKey(table));
      }
      key.add(keys.getString(IMPORTED_FK_COL_NAME));
    }
    keys.close();
    return map;
  }
Example #21
0
  static void truncateTable(String strTable) {

    String DBMS = "";
    try {
      DatabaseMetaData metaData = conn.getMetaData();
      DBMS = metaData.getDatabaseProductName().toLowerCase();
    } catch (SQLException e) {
      System.out.println("Problem determining database product name: " + e);
    }
    System.out.println("Truncating '" + strTable + "' ...");
    try {
      if (DBMS.startsWith("db2")) {
        stmt.execute("TRUNCATE TABLE " + strTable + " IMMEDIATE");
      } else {
        stmt.execute("TRUNCATE TABLE " + strTable);
      }
      transCommit();
    } catch (SQLException se) {
      System.out.println(se.getMessage());
      transRollback();
    }
  }
Example #22
0
  /** Returns a table's columns */
  public static List<Column> getColumns(DatabaseMetaData metadata, String tableName)
      throws Exception {
    List<Column> columns = new ArrayList<Column>();

    PreparedStatement stmt =
        metadata.getConnection().prepareStatement("SELECT * FROM " + tableName);
    ResultSetMetaData rsmeta = stmt.getMetaData();
    for (int i = 1, ii = rsmeta.getColumnCount(); i <= ii; ++i) {
      columns.add(new Column(rsmeta, i));
    }
    stmt.close();
    return columns;
  }
Example #23
0
  /**
   * 初始化连接池
   *
   * @param props
   * @param show_sql
   */
  private static final void initDataSource(Properties dbProperties) {
    try {
      if (dbProperties == null) {
        dbProperties = new Properties();
        dbProperties.load(DBManager.class.getResourceAsStream(CONFIG_PATH));
      }
      // Class.forName(dbProperties.getProperty("jdbc.driverClass"));
      for (Object key : dbProperties.keySet()) {
        String skey = (String) key;
        if (skey.startsWith("jdbc.")) {
          String name = skey.substring(5);
          cp_props.put(name, dbProperties.getProperty(skey));
          if ("show_sql".equalsIgnoreCase(name)) {
            show_sql = "true".equalsIgnoreCase(dbProperties.getProperty(skey));
          }
        }
      }
      dataSource = (DataSource) Class.forName(cp_props.getProperty("datasource")).newInstance();
      if (dataSource.getClass().getName().indexOf("c3p0") > 0) {
        // Disable JMX in C3P0
        System.setProperty(
            "com.mchange.v2.c3p0.management.ManagementCoordinator",
            "com.mchange.v2.c3p0.management.NullManagementCoordinator");
      }
      log.info("Using DataSource : " + dataSource.getClass().getName());
      BeanUtils.populate(dataSource, cp_props);

      Connection conn = getConnection();
      DatabaseMetaData mdm = conn.getMetaData();
      log.info(
          "Connected to " + mdm.getDatabaseProductName() + " " + mdm.getDatabaseProductVersion());
      closeConnection();
    } catch (Exception e) {
      e.printStackTrace();
      throw new DBException(e);
    }
  }
Example #24
0
  public Map<String, List<String>> querySchema(DBConfig config) {
    Map<String, List<String>> map = new HashMap<String, List<String>>();
    List<String> schemaList = config.getSchemaList();
    Connection conn = null;
    DatabaseMetaData databaseMetaData = null;
    try {
      conn = ConnectionFactory.getInstance().getConnection(config);
      databaseMetaData = conn.getMetaData();
    } catch (Exception e) {
      e.printStackTrace();
      return map;
    }

    for (String schema : schemaList) {
      List<String> tableList = new ArrayList<String>();
      try {
        String localSchema = schema;
        if (databaseMetaData.storesLowerCaseIdentifiers()) {
          localSchema = localSchema == null ? null : localSchema.toLowerCase();
        } else if (databaseMetaData.storesUpperCaseIdentifiers()) {
          localSchema = localSchema == null ? null : localSchema.toUpperCase();
        }

        ResultSet rs = databaseMetaData.getTables(null, localSchema, null, null);
        while (rs.next()) {
          String tableName = rs.getString("TABLE_NAME");
          tableList.add(tableName);
          // System.out.println(tableName);
        }
        map.put(schema, tableList);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }

    return map;
  }
Example #25
0
  private void loadDatabaseMetadata() {
    if (!databaseMetadataLoaded) {
      String componentName = "UNKNOWN";
      Connection con = null;
      try {
        componentName = getMetaComponent().getName();

        con = DataSourceConnectionProvider.getByComponent(componentName).getConnection();
        DatabaseMetaData metaData = con.getMetaData();
        supportsSchemasInDataManipulation = metaData.supportsSchemasInDataManipulation();
        Collection timeDateFunctions =
            Strings.toCollection(metaData.getTimeDateFunctions().toUpperCase());

        //
        // another solution instead of the use of 'if' would be to use a xml with
        //	the information of the functions from each BBDD
        if ("DB2 UDB for AS/400".equals(metaData.getDatabaseProductName())
            || "Oracle".equals(metaData.getDatabaseProductName())
            || "PostgresSQL".equals(metaData.getDatabaseProductName())) {
          supportsTranslateFunction = true;
        }
        if ("Oracle".equals(metaData.getDatabaseProductName())
            || "PostgreSQL".equals(metaData.getDatabaseProductName())) {
          supportsYearFunction = supportsMonthFunction = false;
        } else {
          supportsYearFunction = timeDateFunctions.contains("YEAR");
          supportsMonthFunction = timeDateFunctions.contains("MONTH");
        }
        databaseMetadataLoaded = true;
      } catch (Exception ex) {
        log.warn(XavaResources.getString("load_database_metadata_warning"));
      } finally {
        try {
          if (con != null) {
            con.close();
          }
        } catch (SQLException e) {
          log.warn(XavaResources.getString("close_connection_warning"));
        }
      }
    }
  }
Example #26
0
  RelProtoDataType getRelDataType(
      DatabaseMetaData metaData, String catalogName, String schemaName, String tableName)
      throws SQLException {
    final ResultSet resultSet = metaData.getColumns(catalogName, schemaName, tableName, null);

    // Temporary type factory, just for the duration of this method. Allowable
    // because we're creating a proto-type, not a type; before being used, the
    // proto-type will be copied into a real type factory.
    final RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl();
    final RelDataTypeFactory.FieldInfoBuilder fieldInfo = typeFactory.builder();
    while (resultSet.next()) {
      final String columnName = resultSet.getString(4);
      final int dataType = resultSet.getInt(5);
      final String typeString = resultSet.getString(6);
      final int size = resultSet.getInt(7);
      final int scale = resultSet.getInt(9);
      RelDataType sqlType = sqlType(typeFactory, dataType, size, scale, typeString);
      boolean nullable = resultSet.getBoolean(11);
      fieldInfo.add(columnName, sqlType).nullable(nullable);
    }
    resultSet.close();
    return RelDataTypeImpl.proto(fieldInfo.build());
  }
  /** Method declaration */
  private void refreshTree() {

    tTree.removeAll();

    try {
      int color_table = Color.yellow.getRGB();
      int color_column = Color.orange.getRGB();
      int color_index = Color.red.getRGB();

      tTree.addRow("", dMeta.getURL(), "-", 0);

      String usertables[] = {"TABLE"};
      ResultSet result = dMeta.getTables(null, null, null, usertables);
      Vector tables = new Vector();

      // sqlbob@users Added remarks.
      Vector remarks = new Vector();

      while (result.next()) {
        tables.addElement(result.getString(3));
        remarks.addElement(result.getString(5));
      }

      result.close();

      for (int i = 0; i < tables.size(); i++) {
        String name = (String) tables.elementAt(i);
        String key = "tab-" + name + "-";

        tTree.addRow(key, name, "+", color_table);

        // sqlbob@users Added remarks.
        String remark = (String) remarks.elementAt(i);

        if ((remark != null) && !remark.trim().equals("")) {
          tTree.addRow(key + "r", " " + remark);
        }

        ResultSet col = dMeta.getColumns(null, null, name, null);

        while (col.next()) {
          String c = col.getString(4);
          String k1 = key + "col-" + c + "-";

          tTree.addRow(k1, c, "+", color_column);

          String type = col.getString(6);

          tTree.addRow(k1 + "t", "Type: " + type);

          boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;

          tTree.addRow(k1 + "n", "Nullable: " + nullable);
        }

        col.close();
        tTree.addRow(key + "ind", "Indices", "+", 0);

        ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
        String oldiname = null;

        while (ind.next()) {
          boolean nonunique = ind.getBoolean(4);
          String iname = ind.getString(6);
          String k2 = key + "ind-" + iname + "-";

          if ((oldiname == null || !oldiname.equals(iname))) {
            tTree.addRow(k2, iname, "+", color_index);
            tTree.addRow(k2 + "u", "Unique: " + !nonunique);

            oldiname = iname;
          }

          String c = ind.getString(9);

          tTree.addRow(k2 + "c-" + c + "-", c);
        }

        ind.close();
      }

      tTree.addRow("p", "Properties", "+", 0);
      tTree.addRow("pu", "User: "******"pr", "ReadOnly: " + cConn.isReadOnly());
      tTree.addRow("pa", "AutoCommit: " + cConn.getAutoCommit());
      tTree.addRow("pd", "Driver: " + dMeta.getDriverName());
      tTree.addRow("pp", "Product: " + dMeta.getDatabaseProductName());
      tTree.addRow("pv", "Version: " + dMeta.getDatabaseProductVersion());
    } catch (SQLException e) {
      tTree.addRow("", "Error getting metadata:", "-", 0);
      tTree.addRow("-", e.getMessage());
      tTree.addRow("-", e.getSQLState());
    }

    tTree.update();
  }
Example #28
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();
          }
        });
  }
Example #29
0
  /**
   * Each table description has the following columns:
   *
   * <p>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)
   */
  public ResultSet getTables(Connection conn) throws SQLException {
    DatabaseMetaData md = conn.getMetaData();
    ResultSet rs = md.getTables(null, null, "%", new String[] {"TABLE"});

    return rs;
  }
  public static void main(String args[]) {

    Connection connection = null;
    SQLMXJdbcRowSet jrs = null;
    Statement stmt = null;
    ResultSet rs = null;
    DatabaseMetaData dbMeta = null;
    ResultSetMetaData rsMD = null;
    int rowNo;

    try {
      Class.forName("org.trafodion.jdbc.t2.T2Driver");
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println(e.getMessage());
      return;
    }
    try {
      String url = "jdbc:t2jdbc:";
      connection = DriverManager.getConnection(url);
      stmt = connection.createStatement();
      try {
        stmt.executeUpdate("drop table jdbcrsdemo");
      } catch (SQLException e) {
      }
      stmt.executeUpdate(
          "create table jdbcrsdemo (c1 char(20), c2 smallint, c3 integer, c4 largeint, c5 varchar(120), c6 numeric(10,2), c7 decimal(10,2),c8 date, c9 time, c10 timestamp, c11 float, c12 double precision)");
      stmt.executeUpdate(
          "insert into jdbcrsdemo values('Doberman Pinscher', 100, 12345678, 123456789012, 'Canine', 100.12, 100.12, {d '2005-12-23'}, {t '10:11:12'}, {ts '2000-12-23 10:11:12'}, 100.12, 100.12)");
      stmt.executeUpdate(
          "insert into jdbcrsdemo values('Rottweiler', 150, -12345678, -123456789012, 'Canine', -100.12, -100.12, {d '2005-12-24'}, {t '10:11:12'}, {ts '2005-12-24 10:11:12'}, -100.12, -100.12)");
      stmt.executeUpdate(
          "insert into jdbcrsdemo values('Tiger', 101, -12345678, 123456789012, 'Feline', -100.12, 100.12, {d '2005-12-25'}, {t '10:11:12'}, {ts '2005-12-25 10:11:12.0'}, -100.12, 100.12)");

      System.out.println("Creating JdbcRowSet");
      jrs = new SQLMXJdbcRowSet();
      jrs.setUrl(url);

      for (int i = 0; i < 5; i++) {
        switch (i) {
          case 0:
            System.out.println("");
            System.out.println("Simple Select ");
            jrs.setCommand("select * from jdbcrsdemo");
            jrs.execute();
            break;
          case 1:
            System.out.println("");
            System.out.println("Parameterized Select - CHAR");
            jrs.setCommand("select c1, c2 from jdbcrsdemo where c1 = ?");
            jrs.setString(1, "Rottweiler");
            jrs.execute();
            break;
          case 2:
            System.out.println("");
            System.out.println("Parameterized Select - INT");
            jrs.setCommand("select c1, c2, c3 from jdbcrsdemo where c2 = ?  or c2 = ?");
            jrs.setInt(1, 100);
            jrs.setInt(2, 150);
            jrs.execute();
            break;
          case 3:
            System.out.println("");
            System.out.println("Parameterized Select - TIMESTAMP");
            jrs.setCommand("select c1, c2, c3, c10 from jdbcrsdemo where c10 = ?");
            jrs.setTimestamp(1, Timestamp.valueOf("2005-12-25 10:11:12.0"));
            jrs.execute();
            break;
          case 4:
            System.out.println("");
            System.out.println("Parameterized Select - DECIMAL");
            jrs.setCommand("select c1, c2, c3, c7 from jdbcrsdemo where c7 = ? or c7 = ?");
            jrs.setBigDecimal(1, new BigDecimal("100.12"));
            jrs.setBigDecimal(2, new BigDecimal("-100.12"));
            jrs.execute();
            break;
        }
        rsMD = jrs.getMetaData();
        System.out.println("");
        System.out.println("Printing JdbcRowSet ResultSetMetaData ...");
        System.out.println("No. of Columns " + rsMD.getColumnCount());
        for (int j = 1; j <= rsMD.getColumnCount(); j++) {
          System.out.println(
              "Column "
                  + j
                  + " Data Type: "
                  + rsMD.getColumnTypeName(j)
                  + " Name: "
                  + rsMD.getColumnName(j));
        }
        System.out.println("");
        System.out.println("Fetching JdbcRowSet rows...");
        rowNo = 0;
        while (jrs.next()) {
          rowNo++;
          System.out.println("");
          System.out.println("Printing Row " + rowNo + " using getString(), getObject()");
          for (int j = 1; j <= rsMD.getColumnCount(); j++) {
            System.out.println("Column " + j + " - " + jrs.getString(j) + "," + jrs.getObject(j));
          }
        }
        System.out.println("");
      }

      System.out.println("Calling JdbcRowSet.getDatabaseMetaData()");
      dbMeta = jrs.getDatabaseMetaData();

      for (int k = 0; k < 4; k++) {
        switch (k) {
          case 0:
            System.out.println("");
            System.out.println("getTypeInfo() ");
            rs = dbMeta.getTypeInfo();
            break;
          case 1:
            System.out.println("");
            System.out.println("getCatalogs()");
            rs = dbMeta.getCatalogs();
            break;
          case 2:
            System.out.println("");
            System.out.println("getTables() ");
            rs = dbMeta.getTables(null, null, "JDB%", null);
            break;
          case 3:
            System.out.println("");
            System.out.println("getColumns()");
            rs = dbMeta.getColumns(null, null, "JDBCRSDEMO", "%");
            break;
          default:
            rs = null;
            continue;
        }

        if (rs != null) {
          rsMD = rs.getMetaData();
          System.out.println("");

          System.out.println("Printing ResultSetMetaData ...");
          System.out.println("No. of Columns " + rsMD.getColumnCount());
          for (int j = 1; j <= rsMD.getColumnCount(); j++) {
            System.out.println(
                "Column "
                    + j
                    + " Data Type: "
                    + rsMD.getColumnTypeName(j)
                    + " Name: "
                    + rsMD.getColumnName(j));
          }
          System.out.println("");
          System.out.println("Fetching resultSet rows...");
          rowNo = 0;
          while (rs.next()) {
            rowNo++;
            System.out.println("");
            System.out.println("Printing Row " + rowNo + " using getString(), getObject()");
            for (int j = 1; j <= rsMD.getColumnCount(); j++) {
              System.out.println("Column " + j + " - " + rs.getString(j) + "," + rs.getObject(j));
            }
          }
        }
        System.out.println("");
        System.out.println("End of Data");
        if (rs != null) rs.close();
      }
      System.out.println("Demo Completed");
    } catch (SQLException e) {
      SQLException nextException;

      nextException = e;
      do {
        System.out.println(nextException.getMessage());
        System.out.println("SQLState   " + nextException.getSQLState());
        System.out.println("Error Code " + nextException.getErrorCode());
      } while ((nextException = nextException.getNextException()) != null);
    } finally {
      try {
        if (connection != null) connection.close();
        if (jrs != null) jrs.close();
      } catch (SQLException sqle) {
        sqle.printStackTrace();
        System.out.println(sqle.getMessage());
      }
    }
  }