Example usage for java.sql ResultSet getStatement

List of usage examples for java.sql ResultSet getStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getStatement

Introduction

In this page you can find the example usage for java.sql ResultSet getStatement.

Prototype

Statement getStatement() throws SQLException;

Source Link

Document

Retrieves the Statement object that produced this ResultSet object.

Usage

From source file:com.runwaysdk.dataaccess.database.general.Oracle.java

/**
 * Returns a list of string names of the attributes that participate in a
 * group unique with the given name.//from www.j  ava2  s  .  com
 *
 * @param indexName
 * @param conn it is up to the client to manage the connection object.
 * @param attributeNames
 */
public List<String> getGroupIndexAttributesFromIndexName(String indexName, Connection conn) {
    List<String> attributeNames = new LinkedList<String>();

    String sqlStmt = " SELECT column_name \n" + "   FROM user_ind_columns \n" + " WHERE index_name = '"
            + indexName.toUpperCase() + "' \n";

    ResultSet resultSet = query(sqlStmt);

    try {
        while (resultSet.next()) {
            String attrName = resultSet.getString("column_name").toLowerCase();
            attributeNames.add(attrName);
        }
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }
    return attributeNames;

}

From source file:com.runwaysdk.dataaccess.database.general.Oracle.java

/**
 * @see com.runwaysdk.dataaccess.AbstractDatabase#getColumnNames(java.lang.String)
 *//*from  w  w w .  j  ava  2s. c  o m*/
public List<String> getColumnNames(String tableName) {
    String queryString = "SELECT column_name \n" + "  FROM user_tab_columns \n" + " WHERE table_name = '"
            + tableName.toUpperCase() + "'";

    ResultSet resultSet = query(queryString);
    LinkedList<String> columnNames = new LinkedList<String>();

    try {
        while (resultSet.next()) {
            columnNames.add((String) resultSet.getString("column_name").toLowerCase());
        }
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }
    return columnNames;
}

From source file:com.runwaysdk.dataaccess.database.general.Oracle.java

/**
 * @see com.runwaysdk.dataaccess.database.relationship.AbstractDatabase#getChildCountForParent(java.lang.String, java.lang.String)
 *///from  ww w.j a v  a  2  s  . com
public long getChildCountForParent(String parent_id, String relationshipTableName) {
    String query = " SELECT COUNT(*) AS CT \n" + " FROM " + relationshipTableName + " \n" + " WHERE "
            + RelationshipDAOIF.PARENT_ID_COLUMN + " = '" + parent_id + "' \n" + " AND "
            + RelationshipDAOIF.CHILD_ID_COLUMN + " IN " + "   (SELECT DISTINCT "
            + RelationshipDAOIF.CHILD_ID_COLUMN + " \n" + "    FROM " + relationshipTableName + " \n"
            + "    WHERE " + RelationshipDAOIF.PARENT_ID_COLUMN + " = '" + parent_id + "')";

    ResultSet resultSet = this.query(query);

    long returnValue = 0;

    try {
        if (resultSet.next()) {
            BigDecimal number = (BigDecimal) resultSet.getBigDecimal("ct");
            returnValue = number.longValue();
        }
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }

    return returnValue;

    // Heads up:
    //    List<DynaBean> dynaBeanList = this.select(query);
    //
    //    if (dynaBeanList.size() == 0)
    //    {
    //      return 0;
    //    }
    //    else
    //    {
    //      DynaBean dynaBean = dynaBeanList.get(0);
    //      BigDecimal number = (BigDecimal)dynaBean.get("ct");
    //      return number.longValue();
    //    }
}

From source file:com.runwaysdk.dataaccess.database.general.Oracle.java

/**
 * @see com.runwaysdk.dataaccess.database.relationship.AbstractDatabase#getParentCountForChild(java.lang.String, java.lang.String)
 *///from   w w w.  ja v a 2s .c om
public long getParentCountForChild(String child_id, String relationshipTableName) {
    String query = " SELECT COUNT(*) AS CT \n" + " FROM " + relationshipTableName + " \n" + " WHERE "
            + RelationshipDAOIF.CHILD_ID_COLUMN + " = '" + child_id + "' \n" + " AND "
            + RelationshipDAOIF.PARENT_ID_COLUMN + " IN " + "   (SELECT DISTINCT "
            + RelationshipDAOIF.PARENT_ID_COLUMN + " \n" + "    FROM " + relationshipTableName + " \n"
            + "    WHERE " + RelationshipDAOIF.CHILD_ID_COLUMN + " = '" + child_id + "')";

    ResultSet resultSet = this.query(query);

    long returnValue = 0;

    try {
        if (resultSet.next()) {
            BigDecimal number = (BigDecimal) resultSet.getBigDecimal("ct");
            returnValue = number.longValue();
        }
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }

    return returnValue;

    // Heads up:
    //    List<DynaBean> dynaBeanList = this.select(query);
    //
    //    if (dynaBeanList.size() == 0)
    //    {
    //      return 0;
    //    }
    //    else
    //    {
    //      DynaBean dynaBean = dynaBeanList.get(0);
    //      BigDecimal number = (BigDecimal)dynaBean.get("ct");
    //      return number.longValue();
    //    }
}

From source file:com.runwaysdk.dataaccess.database.general.MySQL.java

/**
 * Returns true if a group attribute index exists with the given name and the
 * given attributes on the given table./*from   w  ww .ja  v a 2 s  . com*/
 * 
 * @param tableName
 * @param indexName
 * @param columnNames
 */
public boolean groupAttributeIndexExists(String table, String indexName, List<String> columnNames) {
    String sqlStmt = "SHOW INDEX FROM " + table;

    ResultSet resultSet = query(sqlStmt);

    List<String> attributeNameResultList = new LinkedList<String>();

    boolean returnResult = false;

    try {
        while (resultSet.next()) {
            String attrName = resultSet.getString("column_name").toLowerCase();
            String keyName = resultSet.getString("key_name").toLowerCase();

            if (keyName.equals(indexName) && columnNames.contains(attrName)) {
                attributeNameResultList.add(attrName);
            }
        }

        if (columnNames.size() == attributeNameResultList.size()) {
            returnResult = true;
        }
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }
    return returnResult;
}

From source file:com.runwaysdk.dataaccess.database.general.SQLServer.java

/**
 * @see com.runwaysdk.dataaccess.database.Database#uniqueAttributeExists(String, String, String);
 *//*from   w w w .  java 2  s  .  c om*/
@Override
public boolean uniqueAttributeExists(String table, String columnName, String indexName) {
    String sqlStmt = "sp_helpindex " + table;

    ResultSet resultSet = query(sqlStmt);

    boolean returnResult = false;

    try {
        while (resultSet.next()) {
            /* column name */
            String attrName = resultSet.getString("index_keys").toLowerCase();
            String indexType = resultSet.getString("index_description").toLowerCase();
            String keyName = resultSet.getString("index_name").toLowerCase();
            if (keyName.equals(indexName) && attrName.equals(columnName.toLowerCase())
                    && indexType.contains("unique")) {
                returnResult = true;
            }
        }
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }

    return returnResult;
}

From source file:com.runwaysdk.dataaccess.database.general.SQLServer.java

@Override
public boolean groupAttributeIndexExists(String table, String indexName) {
    String sqlStmt = "sp_helpindex " + table;

    ResultSet resultSet = query(sqlStmt);

    boolean indexExists = false;

    try {//  ww w. j  a  v  a  2s . c o  m
        while (resultSet.next()) {
            String attrName = resultSet.getString("index_keys").toLowerCase();
            String keyName = resultSet.getString("index_name").toLowerCase();

            // strip whitespace and convert to array
            attrName = attrName.replaceAll(" ", "");
            String[] tempNames = attrName.split(",");

            for (int j = 0; j < tempNames.length; j++) {
                if (keyName.equals(indexName)) {
                    indexExists = true;
                    break;
                }
            }
        }

    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }

    return indexExists;
}

From source file:com.thinkbiganalytics.nifi.v2.ingest.GetTableData.java

@Override
public void onTrigger(final ProcessContext context, final ProcessSession session) throws ProcessException {
    FlowFile flowFile = null;/*from   www.j  a va 2 s. co  m*/
    if (context.hasIncomingConnection()) {
        flowFile = session.get();

        // If we have no FlowFile, and all incoming connections are self-loops then we can continue on.
        // However, if we have no FlowFile and we have connections coming from other Processors, then
        // we know that we should run only if we have a FlowFile.
        if (flowFile == null && context.hasNonLoopConnection()) {
            return;
        }
    }

    final FlowFile incoming = flowFile;
    final ComponentLog logger = getLog();

    final DBCPService dbcpService = context.getProperty(JDBC_SERVICE).asControllerService(DBCPService.class);
    final MetadataProviderService metadataService = context.getProperty(METADATA_SERVICE)
            .asControllerService(MetadataProviderService.class);
    final String loadStrategy = context.getProperty(LOAD_STRATEGY).getValue();
    final String categoryName = context.getProperty(FEED_CATEGORY).evaluateAttributeExpressions(incoming)
            .getValue();
    final String feedName = context.getProperty(FEED_NAME).evaluateAttributeExpressions(incoming).getValue();
    final String tableName = context.getProperty(TABLE_NAME).evaluateAttributeExpressions(incoming).getValue();
    final String fieldSpecs = context.getProperty(TABLE_SPECS).evaluateAttributeExpressions(incoming)
            .getValue();
    final String dateField = context.getProperty(DATE_FIELD).evaluateAttributeExpressions(incoming).getValue();
    final Integer queryTimeout = context.getProperty(QUERY_TIMEOUT).asTimePeriod(TimeUnit.SECONDS).intValue();
    final Integer overlapTime = context.getProperty(OVERLAP_TIME).evaluateAttributeExpressions(incoming)
            .asTimePeriod(TimeUnit.SECONDS).intValue();
    final Integer backoffTime = context.getProperty(BACKOFF_PERIOD).asTimePeriod(TimeUnit.SECONDS).intValue();
    final String unitSize = context.getProperty(UNIT_SIZE).getValue();
    final String outputType = context.getProperty(OUTPUT_TYPE).getValue();
    String outputDelimiter = context.getProperty(OUTPUT_DELIMITER).evaluateAttributeExpressions(incoming)
            .getValue();
    final String delimiter = StringUtils.isBlank(outputDelimiter) ? "," : outputDelimiter;

    final PropertyValue waterMarkPropName = context.getProperty(HIGH_WATER_MARK_PROP)
            .evaluateAttributeExpressions(incoming);

    final String[] selectFields = parseFields(fieldSpecs);

    final LoadStrategy strategy = LoadStrategy.valueOf(loadStrategy);
    final StopWatch stopWatch = new StopWatch(true);

    try (final Connection conn = dbcpService.getConnection()) {

        FlowFile outgoing = (incoming == null ? session.create() : incoming);
        final AtomicLong nrOfRows = new AtomicLong(0L);
        final LastFieldVisitor visitor = new LastFieldVisitor(dateField, null);
        final FlowFile current = outgoing;

        outgoing = session.write(outgoing, new OutputStreamCallback() {
            @Override
            public void process(final OutputStream out) throws IOException {
                ResultSet rs = null;
                try {
                    GetTableDataSupport support = new GetTableDataSupport(conn, queryTimeout);
                    if (strategy == LoadStrategy.FULL_LOAD) {
                        rs = support.selectFullLoad(tableName, selectFields);
                    } else if (strategy == LoadStrategy.INCREMENTAL) {
                        String waterMarkValue = getIncrementalWaterMarkValue(current, waterMarkPropName);
                        LocalDateTime waterMarkTime = LocalDateTime.parse(waterMarkValue, DATE_TIME_FORMAT);
                        Date lastLoadDate = toDate(waterMarkTime);
                        visitor.setLastModifyDate(lastLoadDate);
                        rs = support.selectIncremental(tableName, selectFields, dateField, overlapTime,
                                lastLoadDate, backoffTime, GetTableDataSupport.UnitSizes.valueOf(unitSize));
                    } else {
                        throw new RuntimeException("Unsupported loadStrategy [" + loadStrategy + "]");
                    }

                    if (GetTableDataSupport.OutputType.DELIMITED
                            .equals(GetTableDataSupport.OutputType.valueOf(outputType))) {
                        nrOfRows.set(JdbcCommon.convertToDelimitedStream(rs, out,
                                (strategy == LoadStrategy.INCREMENTAL ? visitor : null), delimiter));
                    } else if (GetTableDataSupport.OutputType.AVRO
                            .equals(GetTableDataSupport.OutputType.valueOf(outputType))) {
                        avroSchema = JdbcCommon.createSchema(rs);
                        nrOfRows.set(JdbcCommon.convertToAvroStream(rs, out,
                                (strategy == LoadStrategy.INCREMENTAL ? visitor : null), avroSchema));
                    } else {
                        throw new RuntimeException("Unsupported output format type [" + outputType + "]");
                    }
                } catch (final SQLException e) {
                    throw new IOException("SQL execution failure", e);
                } finally {
                    if (rs != null) {
                        try {
                            if (rs.getStatement() != null) {
                                rs.getStatement().close();
                            }
                            rs.close();
                        } catch (SQLException e) {
                            getLog().error("Error closing sql statement and resultset");
                        }
                    }
                }
            }
        });

        // set attribute how many rows were selected
        outgoing = session.putAttribute(outgoing, RESULT_ROW_COUNT, Long.toString(nrOfRows.get()));

        //set output format type and avro schema for feed setup, if available
        outgoing = session.putAttribute(outgoing, "db.table.output.format", outputType);
        String avroSchemaForFeedSetup = (avroSchema != null) ? JdbcCommon.getAvroSchemaForFeedSetup(avroSchema)
                : EMPTY_STRING;
        outgoing = session.putAttribute(outgoing, "db.table.avro.schema", avroSchemaForFeedSetup);

        session.getProvenanceReporter().modifyContent(outgoing, "Retrieved " + nrOfRows.get() + " rows",
                stopWatch.getElapsed(TimeUnit.MILLISECONDS));

        // Terminate flow file if no work
        Long rowcount = nrOfRows.get();
        outgoing = session.putAttribute(outgoing, ComponentAttributes.NUM_SOURCE_RECORDS.key(),
                String.valueOf(rowcount));

        if (nrOfRows.get() == 0L) {
            logger.info("{} contains no data; transferring to 'nodata'", new Object[] { outgoing });
            session.transfer(outgoing, REL_NO_DATA);
        } else {

            logger.info("{} contains {} records; transferring to 'success'",
                    new Object[] { outgoing, nrOfRows.get() });

            if (strategy == LoadStrategy.INCREMENTAL) {
                String newWaterMarkStr = format(visitor.getLastModifyDate());
                outgoing = setIncrementalWaterMarkValue(session, outgoing, waterMarkPropName, newWaterMarkStr);

                logger.info("Recorded load status feed {} date {}", new Object[] { feedName, newWaterMarkStr });
            }
            session.transfer(outgoing, REL_SUCCESS);
        }
    } catch (final Exception e) {
        if (incoming == null) {
            logger.error(
                    "Unable to execute SQL select from table due to {}. No incoming flow file to route to failure",
                    new Object[] { e });
        } else {
            logger.error("Unable to execute SQL select from table due to {}; routing to failure",
                    new Object[] { incoming, e });
            session.transfer(incoming, REL_FAILURE);
        }
    }
}

From source file:com.runwaysdk.dataaccess.database.general.PostgreSQL.java

/**
 * /* w w w . j a  va  2  s .  c o  m*/
 * @see com.runwaysdk.dataaccess.AbstractDatabase#getNextSequenceNumber()
 */
@Override
public String getNextSequenceNumber() {
    // get the sequence value
    String sqlStmt = "SELECT NEXTVAL('" + OBJECT_UPDATE_SEQUENCE + "') AS nextval";

    ResultSet resultSet = query(sqlStmt);

    String returnResult = "";

    try {
        resultSet.next();

        return resultSet.getString("nextval");
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }

    return returnResult;

}

From source file:com.runwaysdk.dataaccess.database.general.PostgreSQL.java

/**
 * /*from   ww w .  j a v a  2 s. c om*/
 * @see com.runwaysdk.dataaccess.AbstractDatabase#getNextTransactionSequence()
 */
@Override
public String getNextTransactionSequence() {
    // get the sequence value
    String sqlStmt = "SELECT NEXTVAL('" + TRANSACTION_SEQUENCE + "') AS nextval";

    ResultSet resultSet = query(sqlStmt);

    String returnResult = "";

    try {
        resultSet.next();

        return resultSet.getString("nextval");
    } catch (SQLException sqlEx1) {
        Database.throwDatabaseException(sqlEx1);
    } finally {
        try {
            java.sql.Statement statement = resultSet.getStatement();
            resultSet.close();
            statement.close();
        } catch (SQLException sqlEx2) {
            Database.throwDatabaseException(sqlEx2);
        }
    }

    return returnResult;

}