Java Code Examples for java.sql.ResultSet#findColumn()

@Override
public String getHDMFristAddress(int hdSeedId) {
    String address = null;
    try {
        PreparedStatement statement = this.mDb.getPreparedStatement("select hdm_address from hd_seeds where hd_seed_id=?"
                , new String[]{Integer.toString(hdSeedId)});
        ResultSet cursor = statement.executeQuery();
        if (cursor.next()) {
            int idColumn = cursor.findColumn(AbstractDb.HDSeedsColumns.HDM_ADDRESS);
            if (idColumn != -1) {
                address = cursor.getString(idColumn);
            }
        }
        cursor.close();
        statement.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return address;
}
 

@Override
public List<Integer> getHDAccountSeeds() {
    List<Integer> hdSeedIds = new ArrayList<Integer>();

    try {

        String sql = "select " + AbstractDb.HDAccountColumns.HD_ACCOUNT_ID + " from " + AbstractDb.Tables.HD_ACCOUNT;
        PreparedStatement statement = this.mDb.getPreparedStatement(sql, null);
        ResultSet c = statement.executeQuery();
        while (c.next()) {
            int idColumn = c.findColumn(AbstractDb.HDAccountColumns.HD_ACCOUNT_ID);
            if (idColumn != -1) {
                hdSeedIds.add(c.getInt(idColumn));
            }
        }
        c.close();
        statement.close();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return hdSeedIds;
}
 

@Override
public boolean isTableExists(String tableName) throws SQLException {
	DatabaseMetaData metaData = connection.getMetaData();
	logger.trace("Got meta data from connection");
	ResultSet results = null;
	try {
		results = metaData.getTables(null, null, "%", new String[] { "TABLE" });
		// we do it this way because some result sets don't like us to findColumn if no results
		if (!results.next()) {
			return false;
		}
		int col = results.findColumn(JDBC_META_TABLE_NAME_COLUMN);
		do {
			String dbTableName = results.getString(col);
			if (tableName.equalsIgnoreCase(dbTableName)) {
				return true;
			}
		} while (results.next());
		return false;
	} finally {
		if (results != null) {
			results.close();
		}
	}
}
 

@Override
public int allGeneratedAddressCount(AbstractHD.PathType pathType) {
    int count = 0;
    try {
        PreparedStatement statement = this.mDb.getPreparedStatement("select ifnull(count(address),0) count from "
                        + AbstractDb.Tables.HD_ACCOUNT_ADDRESS + " where path_type=? ",
                new String[]{Integer.toString(pathType.getValue())});
        ResultSet cursor = statement.executeQuery();
        if (cursor.next()) {
            int idColumn = cursor.findColumn("count");
            if (idColumn != -1) {
                count = cursor.getInt(idColumn);
            }
        }
        cursor.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return count;
}
 

@Override
public int hdAccountTxCount() {
    int result = 0;
    try {
        String sql = "select count( distinct a.tx_hash) cnt from addresses_txs a ,hd_account_addresses b where a.address=b.address  ";
        PreparedStatement statement = this.mDb.getPreparedStatement(sql, null);
        ResultSet c = statement.executeQuery();
        if (c.next()) {
            int idColumn = c.findColumn("cnt");
            if (idColumn != -1) {
                result = c.getInt(idColumn);
            }
        }
        c.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return result;
}
 

@Override
public String getHDAccountEncryptSeed(int hdSeedId) {
    String hdAccountEncryptSeed = null;
    try {
        PreparedStatement statement = this.mDb.getPreparedStatement("select " + AbstractDb.HDAccountColumns.ENCRYPT_SEED + " from hd_account where hd_account_id=? "
                , new String[]{Integer.toString(hdSeedId)});
        ResultSet c = statement.executeQuery();
        if (c.next()) {
            int idColumn = c.findColumn(AbstractDb.HDAccountColumns.ENCRYPT_SEED);
            if (idColumn != -1) {
                hdAccountEncryptSeed = c.getString(idColumn);
            }
        }
        c.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return hdAccountEncryptSeed;

}
 

@Override
public int hdAccountTxCount() {
    int result = 0;
    try {
        String sql = "select count( distinct a.tx_hash) cnt from addresses_txs a ,hd_account_addresses b where a.address=b.address  ";
        PreparedStatement statement = this.mDb.getPreparedStatement(sql, null);
        ResultSet c = statement.executeQuery();
        if (c.next()) {
            int idColumn = c.findColumn("cnt");
            if (idColumn != -1) {
                result = c.getInt(idColumn);
            }
        }
        c.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return result;
}
 

@Override
public int getUnspendOutCountByHDAccountWithPath(int hdAccountId, AbstractHD.PathType pathType) {
    int result = 0;
    String sql = "select count(tx_hash) cnt from outs where out_address in " +
            "(select address from hd_account_addresses where path_type =? and out_status=?) " +
            "and hd_account_id=?";
    try {
        PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Integer.toString(pathType.getValue())
                , Integer.toString(Out.OutStatus.unspent.getValue())
                , Integer.toString(hdAccountId)
        });
        ResultSet c = statement.executeQuery();
        if (c.next()) {
            int idColumn = c.findColumn("cnt");
            if (idColumn != -1) {
                result = c.getInt(idColumn);
            }
        }
        c.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return result;
}
 

@Override
public int maxHDMAddressPubIndex(int hdSeedId) {
    int maxIndex = -1;
    try {
        PreparedStatement statement = this.mDb.getPreparedStatement("select ifnull(max(hd_seed_index),-1)  hd_seed_index from hdm_addresses where hd_seed_id=?  ", new String[]{
                Integer.toString(hdSeedId)
        });
        ResultSet cursor = statement.executeQuery();

        if (cursor.next()) {
            int idColumn = cursor.findColumn(AbstractDb.HDMAddressesColumns.HD_SEED_INDEX);
            if (idColumn != -1) {
                maxIndex = cursor.getInt(idColumn);
            }
        }
        cursor.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return maxIndex;
}
 

public static Tx applyCursor(ResultSet c) throws AddressFormatException, SQLException {
    Tx txItem = new Tx();
    int idColumn = c.findColumn(AbstractDb.TxsColumns.BLOCK_NO);
    if (idColumn != -1 && c.getObject(idColumn) != null) {
        txItem.setBlockNo(c.getInt(idColumn));
    } else {
        txItem.setBlockNo(Tx.TX_UNCONFIRMED);
    }
    idColumn = c.findColumn(AbstractDb.TxsColumns.TX_HASH);
    if (idColumn != -1) {
        txItem.setTxHash(Base58.decode(c.getString(idColumn)));
    }
    idColumn = c.findColumn(AbstractDb.TxsColumns.SOURCE);
    if (idColumn != -1) {
        txItem.setSource(c.getInt(idColumn));
    }
    if (txItem.getSource() >= 1) {
        txItem.setSawByPeerCnt(txItem.getSource() - 1);
        txItem.setSource(1);
    } else {
        txItem.setSawByPeerCnt(0);
        txItem.setSource(0);
    }
    idColumn = c.findColumn(AbstractDb.TxsColumns.TX_TIME);
    if (idColumn != -1) {
        txItem.setTxTime(c.getInt(idColumn));
    }
    idColumn = c.findColumn(AbstractDb.TxsColumns.TX_VER);
    if (idColumn != -1) {
        txItem.setTxVer(c.getInt(idColumn));
    }
    idColumn = c.findColumn(AbstractDb.TxsColumns.TX_LOCKTIME);
    if (idColumn != -1) {
        txItem.setTxLockTime(c.getInt(idColumn));
    }
    return txItem;

}
 

protected boolean hasColumn(ResultSet rs, String name) {
    try {
        rs.findColumn(name);
        return true;
    } catch (SQLException e) {
        return false;
    }
}
 

@Override
public int addHDKey(final String encryptedMnemonicSeed, final String encryptHdSeed, final String firstAddress, final boolean isXrandom, final String addressOfPS) {
    int result = 0;
    try {
        this.mDb.getConn().setAutoCommit(false);
        String[] params = new String[]{encryptedMnemonicSeed, encryptHdSeed, Integer.toString(isXrandom ? 1 : 0), firstAddress};
        PreparedStatement stmt = this.mDb.getConn().prepareStatement(insertHDSeedSql);
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                stmt.setString(i + 1, params[i]);
            }
        }
        stmt.executeUpdate();
        stmt.close();
        if (!hasPasswordSeed(this.mDb.getConn()) && !Utils.isEmpty(addressOfPS)) {
            addPasswordSeed(this.mDb.getConn(), new PasswordSeed(addressOfPS, encryptedMnemonicSeed));
        }
        this.mDb.getConn().commit();
        PreparedStatement statement = this.mDb.getPreparedStatement("select hd_seed_id from hd_seeds where encrypt_seed=? and encrypt_hd_seed=? and is_xrandom=? and hdm_address=?"
                , new String[]{encryptedMnemonicSeed, encryptHdSeed, Integer.toString(isXrandom ? 1 : 0), firstAddress});
        ResultSet cursor = statement.executeQuery();

        if (cursor.next()) {
            int idColumn = cursor.findColumn(AbstractDb.HDSeedsColumns.HD_SEED_ID);
            if (idColumn != -1) {
                result = cursor.getInt(idColumn);
            }

        }
        cursor.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return result;
}
 

private void validateResultSetMetadata(List<ConfigIssue> issues, Source.Context context, ResultSet rs) {
  Set<String> allTables = new HashSet<>();
  try {
    Set<String> columnLabels = new HashSet<>();
    ResultSetMetaData metadata = rs.getMetaData();
    int columnIdx = metadata.getColumnCount() + 1;
    while (--columnIdx > 0) {
      String columnLabel = metadata.getColumnLabel(columnIdx);
      if (columnLabels.contains(columnLabel)) {
        issues.add(context.createConfigIssue(Groups.JDBC.name(), QUERY, JdbcErrors.JDBC_31, columnLabel));
      } else {
        columnLabels.add(columnLabel);
      }
      allTables.add(metadata.getTableName(columnIdx));
    }
    if (!StringUtils.isEmpty(offsetColumn) && offsetColumn.contains(".")) {
      issues.add(context.createConfigIssue(Groups.JDBC.name(), OFFSET_COLUMN, JdbcErrors.JDBC_32, offsetColumn));
    } else {
      rs.findColumn(offsetColumn);
    }
  } catch (SQLException e) {
    // Log a warning instead of an error because some implementations such as Oracle have implicit
    // "columns" such as ROWNUM that won't appear as part of the result set.
    LOG.warn(JdbcErrors.JDBC_33.getMessage(), offsetColumn, query);
    LOG.warn(jdbcUtil.formatSqlException(e));
  }
  tableNames = StringUtils.join(allTables, ", ");
}
 

/**
 * Get the address from a query against an order entry WAREHOUSE, DISTRICT
 * or CUSTOMER table.
 * 
 * @param address Object to fill in
 * @param rs ResultSet already positioned on the current row.
 * @param firstColumnName First column that makes up the address.
 * @throws SQLException
 */
protected Address getAddress(Address address,
        ResultSet rs, String firstColumnName) throws SQLException {
    
    address.clear();

    int col = rs.findColumn(firstColumnName);
    address.setStreet1(rs.getString(col++));
    address.setStreet2(rs.getString(col++));
    address.setCity(rs.getString(col++));
    address.setState(rs.getString(col++));
    address.setZip(rs.getString(col));

    return address;
}
 

protected List<Object> getColumn(ResultSet rs, String columnName) {
	List<Object> data = new LinkedList<Object>();
	try {
		int index = rs.findColumn(columnName);
		while(rs.next()) {
			data.add(rs.getObject(index));
		}
		rs.first();
	} catch(SQLException e) {
		throw new OperateException(e);
	}

	return data;
}
 

/**
 * Get the address from a query against an order entry WAREHOUSE, DISTRICT
 * or CUSTOMER table.
 * 
 * @param address Object to fill in
 * @param rs ResultSet already positioned on the current row.
 * @param firstColumnName First column that makes up the address.
 * @throws SQLException
 */
protected Address getAddress(Address address,
        ResultSet rs, String firstColumnName) throws SQLException {
    
    address.clear();

    int col = rs.findColumn(firstColumnName);
    address.setStreet1(rs.getString(col++));
    address.setStreet2(rs.getString(col++));
    address.setCity(rs.getString(col++));
    address.setState(rs.getString(col++));
    address.setZip(rs.getString(col));

    return address;
}
 

/**
 * Creates a copy of an {@link Iterable} of columns, where the copy is sorted such that the
 * columns appear in the same order as they do in the supplied {@link ResultSet}.
 *
 * @param columns Columns expected.
 * @param resultSet The result set containing the values.
 * @return The sorted columns.
 */
public static Collection<Column> sortedCopy(Iterable<Column> columns, ResultSet resultSet) {
  Column[] result = new Column[Iterables.size(columns)];
  for (Column column : columns) {
    try {
      result[resultSet.findColumn(column.getName()) - 1] = column;
    } catch(SQLException ex) {
      throw new IllegalStateException("Could not retrieve column [" + column.getName() + "]", ex);
    }
  }
  return Collections.unmodifiableCollection(Arrays.asList(result));
}
 

/**
 * 在rs中获取column字段的typeClass型的值
 *
 * @param rs
 * @param column
 * @param paramClass
 *
 * @return
 *
 * @throws SQLException
 */
public static Object getValue4Type(ResultSet rs, String column, Class<?> typeClass) throws SQLException {

    if (Collection.class.isAssignableFrom(typeClass)) {
        return null;
    }

    try {
        rs.findColumn(column);
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }

    if (typeClass.equals(Integer.class) || typeClass.equals(Integer.TYPE)) {
        return rs.getInt(column);
    }

    if (typeClass.equals(Long.class) || typeClass.equals(Long.TYPE)) {
        return rs.getLong(column);
    }

    if (typeClass.equals(Boolean.class) || typeClass.equals(Boolean.TYPE)) {
        return rs.getBoolean(column);
    }

    if (typeClass.equals(Float.class) || typeClass.equals(Float.TYPE)) {
        return rs.getFloat(column);
    }

    if (typeClass.equals(Double.class) || typeClass.equals(Double.TYPE)) {
        return rs.getDouble(column);
    }

    if (typeClass.equals(Byte.class) || typeClass.equals(Byte.TYPE)) {
        return rs.getByte(column);
    }

    if (typeClass.equals(String.class)) {
        return rs.getString(column);
    }

    if (Date.class.isAssignableFrom(typeClass)) {
        return rs.getTimestamp(column);
    }

    if (java.sql.Date.class.isAssignableFrom(typeClass)) {
        return rs.getDate(column);
    }

    return rs.getObject(column);
}
 

private HDAccount.HDAccountAddress formatAddress(ResultSet c) throws SQLException {
    String address = null;
    byte[] pubs = null;
    AbstractHD.PathType ternalRootType = AbstractHD.PathType.EXTERNAL_ROOT_PATH;
    int index = 0;
    boolean isIssued = false;
    boolean isSynced = true;
    HDAccount.HDAccountAddress hdAccountAddress = null;
    try {
        int idColumn = c.findColumn(AbstractDb.HDAccountAddressesColumns.ADDRESS);
        if (idColumn != -1) {
            address = c.getString(idColumn);
        }
        idColumn = c.findColumn(AbstractDb.HDAccountAddressesColumns.PUB);
        if (idColumn != -1) {
            pubs = Base58.decode(c.getString(idColumn));
        }
        idColumn = c.findColumn(AbstractDb.HDAccountAddressesColumns.PATH_TYPE);
        if (idColumn != -1) {
            ternalRootType = AbstractHD.getTernalRootType(c.getInt(idColumn));

        }
        idColumn = c.findColumn(AbstractDb.HDAccountAddressesColumns.ADDRESS_INDEX);
        if (idColumn != -1) {
            index = c.getInt(idColumn);
        }
        idColumn = c.findColumn(AbstractDb.HDAccountAddressesColumns.IS_ISSUED);
        if (idColumn != -1) {
            isIssued = c.getInt(idColumn) == 1;
        }
        idColumn = c.findColumn(AbstractDb.HDAccountAddressesColumns.IS_SYNCED);
        if (idColumn != -1) {
            isSynced = c.getInt(idColumn) == 1;
        }
        hdAccountAddress = new HDAccount.HDAccountAddress(address, pubs,
                ternalRootType, index, isIssued, isSynced);
    } catch (AddressFormatException e) {
        e.printStackTrace();
    }
    return hdAccountAddress;
}
 

@Override
    public List<TableMeta> buildResult(ResultSet rs) throws SQLException {
        List<TableMeta> list = new ArrayList<>();
        TableMeta meta;
        while (rs.next()) {
            meta = new TableMeta();

            meta.setOriginalColumnName(rs.getString("column_name"));
            meta.setColumnName(meta.getOriginalColumnName().replaceAll("[^A-Za-z0-9_]", "_"));

            Integer colId = rs.getInt("column_id");
            meta.setColumnId(colId == 0 ? 9999 : colId);

            //meta.setVersion(rs.getInt("version"));
            meta.setDataType(rs.getString("data_type"));
            meta.setDataLength(rs.getLong("data_length"));
            meta.setDataPrecision(rs.getInt("data_precision"));

            if (SupportedOraDataType.NUMBER.toString().equals(meta.getDataType())) {
                Object scale = rs.getObject("data_scale");
                meta.setDataScale(scale == null ? -127 : Integer.parseInt(scale.toString()));
            } else {
                meta.setDataScale(rs.getInt("data_scale"));
            }

            meta.setNullable(rs.getString("nullable"));
            //meta.setDdlTime(rs.getTimestamp("ddl_time"));
            meta.setIsPk(rs.getString("is_pk"));
            meta.setPkPosition(rs.getInt("pk_position"));
            meta.setCharLength(rs.getInt("char_length"));
            meta.setCharUsed(rs.getString("char_used"));
            meta.setInternalColumnId(rs.getInt("internal_column_id"));
            meta.setHiddenColumn(rs.getString("hidden_column"));
            meta.setVirtualColumn(rs.getString("virtual_column"));
            String defaultValue = rs.getString("data_default");
            if (defaultValue != null) {
                meta.setDefaultValue(defaultValue.trim());
            }

            // 2.X版本没有注释信息
            try {
                rs.findColumn("comments");
                meta.setComments(rs.getString("comments"));
            } catch (SQLException e) {
//            logger.warn("source is 2.X, comments does not exist", e);
            }
            list.add(meta);
        }
        return list;
    }