java.sql.ResultSet Java Examples

/**
 * Returns the points in the specified hall of fame.
 *
 * @param transaction
 *            Transaction
 * @param charname
 *            name of the player
 * @param fametype
 *            type of the hall of fame
 * @return points or 0 in case there is no entry
 */
public int getHallOfFamePoints(final DBTransaction transaction, final String charname, final String fametype) {
	int res = 0;
	try {
		final String query = "SELECT points FROM halloffame WHERE charname="
				+ "'[charname]' AND fametype='[fametype]'";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("charname", charname);
		params.put("fametype", fametype);

		final ResultSet result = transaction.query(query, params);
		if (result.next()) {
			res = result.getInt("points");
		}
		result.close();
	} catch (final Exception sqle) {
		logger.warn("Error reading hall of fame", sqle);
	}

	return res;
}
 

public List<Employee> getEmployeeList() throws SQLException {
	PreparedStatement ps = con
			.prepareStatement("select firstname,lastname,gender,country,city,company from blueprintsdb.employee");

	// get jobposts data from database
	ResultSet result = ps.executeQuery();

	List<Employee> list = new ArrayList<Employee>();

	while (result.next()) {
		Employee employee = new Employee();

		employee.setFirstname(result.getString("firstname"));
		employee.setLastname(result.getString("lastname"));
		employee.setGender(result.getString("gender"));
		employee.setCountry(result.getString("country"));
		employee.setCity(result.getString("city"));
		employee.setCompany(result.getString("company"));

		list.add(employee);
	}

	return list;

}
 

private void markFailed(Connection connection, Collection<Tuple2<String, String>> failedSnapshotDefinitions)
    throws Exception
{
    ResultSet resultSet = connection.createStatement(
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeQuery(SD_SELECT_ALL);
    while (resultSet.next())
    {
        String rscName = resultSet.getString(SD_RES_NAME);
        String snapshotName = resultSet.getString(SD_NAME);
        if (failedSnapshotDefinitions.contains(Tuples.of(rscName, snapshotName)))
        {
            resultSet.updateLong(SD_FLAGS,
                (resultSet.getLong(SD_FLAGS) & ~SD_FLAG_SUCCESSFUL) | SD_FLAG_FAILED_DEPLOYMENT
            );
            resultSet.updateRow();
        }
    }
    resultSet.close();
}
 

/**
 * Test update to heap, should get exclusive table lock.
 */
public void testUpdateToHeap () throws SQLException { 
    Statement st = createStatement();
    constructHeap(st);
    
    st.execute("update heap_only set a = 1000 where a = 2");
    ResultSet rs = st.executeQuery(
            " select * from lock_table order by tabname, type "
            + "desc, mode, cnt, lockname");
    JDBC.assertFullResultSet(rs, new String[][]{
            {"SPLICE", "UserTran", "TABLE", "2", "X", "HEAP_ONLY", "Tablelock", "GRANT", "ACTIVE"},
    });
    commit();

    st.close();
    dropTable("heap_only");
}
 

@Override
public DBRow.Column getRandomColumnForDML(String query, Object value, int type)
     {
  
  try {
  PreparedStatement stmt = gConn.prepareStatement(query,
      ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  PrepareStatementSetter ps = new PrepareStatementSetter(stmt);
  ps.setValues(value, type);
  ResultSet rs = stmt.executeQuery();
  if (rs.next()) {
    rs.last();
    int randomRow = GenericDML.rand.nextInt(rs.getRow());
    rs.absolute(randomRow == 0 ? 1 : randomRow);
    return new DBRow.Column(rs.getMetaData().getColumnName(1), rs
        .getMetaData().getColumnType(1), rs.getObject(1));
  }
  else {
    return new DBRow.Column(null, 0, value);
  }
  
  } catch (  SQLException  se) {
    throw new TestException (" Error while retrieving a row from database " + se.getSQLState() + TestHelper.getStackTrace(se));
  }
}
 

public int getBlockCount() {
    String sql = "select count(*) cnt from blocks ";
    int count = 0;
    try {
        PreparedStatement statement = this.mDb.getPreparedStatement(sql, null);
        ResultSet c = statement.executeQuery();
        if (c.next()) {
            int idColumn = c.findColumn("cnt");
            if (idColumn != -1) {
                count = c.getInt(idColumn);
            }
        }
        c.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return count;
}
 

/**
 * Creates a frame containing a given number or unlimited number of rows
 * from a result set.
 */
static Meta.Frame frame(ResultSet resultSet, long offset,
                        int fetchMaxRowCount, Calendar calendar) throws SQLException {
  final ResultSetMetaData metaData = resultSet.getMetaData();
  final int columnCount = metaData.getColumnCount();
  final int[] types = new int[columnCount];
  for (int i = 0; i < types.length; i++) {
    types[i] = metaData.getColumnType(i + 1);
  }
  final List<Object> rows = new ArrayList<>();
  // Meta prepare/prepareAndExecute 0 return 0 row and done
  boolean done = fetchMaxRowCount == 0;
  for (int i = 0; fetchMaxRowCount < 0 || i < fetchMaxRowCount; i++) {
    if (!resultSet.next()) {
      done = true;
      resultSet.close();
      break;
    }
    Object[] columns = new Object[columnCount];
    for (int j = 0; j < columnCount; j++) {
      columns[j] = getValue(resultSet, types[j], j, calendar);
    }
    rows.add(columns);
  }
  return new Meta.Frame(offset, done, rows);
}
 

public boolean isHealthy() {
  final String sql = "SELECT ID FROM CA";

  try {
    PreparedStatement ps = borrowPreparedStatement(sql);

    ResultSet rs = null;
    try {
      rs = ps.executeQuery();
    } finally {
      datasource.releaseResources(ps, rs);
    }
    return true;
  } catch (Exception ex) {
    LOG.error("isHealthy(). {}: {}", ex.getClass().getName(), ex.getMessage());
    LOG.debug("isHealthy()", ex);
    return false;
  }
}
 

public DbSchemaInfo(DataSourceWrapper datasource) throws DataAccessException {
  Args.notNull(datasource, "datasource");

  final String sql = "SELECT NAME,VALUE2 FROM DBSCHEMA";

  Statement stmt = null;
  ResultSet rs = null;

  try {
    stmt = datasource.createStatement();
    if (stmt == null) {
      throw new DataAccessException("could not create statement");
    }

    rs = stmt.executeQuery(sql);
    while (rs.next()) {
      variables.put(rs.getString("NAME"), rs.getString("VALUE2"));
    }
  } catch (SQLException ex) {
    throw datasource.translate(sql, ex);
  } finally {
    datasource.releaseResources(stmt, rs);
  }
}
 

public int getVotePoints(){
	int points = 0;
	try {
                       Connection con = DatabaseConnection.getConnection();
		PreparedStatement ps = con.prepareStatement("SELECT `votepoints` FROM accounts WHERE id = ?");
		ps.setInt(1, accId);
		ResultSet rs = ps.executeQuery();

		if (rs.next()) {
			points = rs.getInt("votepoints");
		}
		ps.close();
		rs.close();

                       con.close();
	} catch (SQLException e) {
                   e.printStackTrace();
	}
	votePoints = points;
	return votePoints;
}
 

@Test
public void testReadMetricsForSelect() throws Exception {
    String tableName = generateUniqueName();
    long numSaltBuckets = 6;
    String ddl = "CREATE TABLE " + tableName + " (K VARCHAR NOT NULL PRIMARY KEY, V VARCHAR)" + " SALT_BUCKETS = "
            + numSaltBuckets;
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute(ddl);

    long numRows = 1000;
    long numExpectedTasks = numSaltBuckets;
    insertRowsInTable(tableName, numRows);

    String query = "SELECT * FROM " + tableName;
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    PhoenixResultSet resultSetBeingTested = rs.unwrap(PhoenixResultSet.class);
    changeInternalStateForTesting(resultSetBeingTested);
    while (resultSetBeingTested.next()) {}
    resultSetBeingTested.close();
    Set<String> expectedTableNames = Sets.newHashSet(tableName);
    assertReadMetricValuesForSelectSql(Lists.newArrayList(numRows), Lists.newArrayList(numExpectedTasks),
        resultSetBeingTested, expectedTableNames);
}
 

private NewsletterContentReportVO getContentReport(String contentId, Connection conn) {
	NewsletterContentReportVO contentReport = null;
	PreparedStatement stat = null;
	ResultSet res = null;
	try {
		stat = conn.prepareStatement(LOAD_CONTENT_REPORT);
		stat.setString(1, contentId);
		res = stat.executeQuery();
		if (res.next()) {
			contentReport = this.createContentReportFromRecord(res);
		}
	} catch (Throwable t) {
		this.processDaoException(t, "Error loading content report", "getContentReport");
	} finally {
		closeDaoResources(res, stat);
	}
	return contentReport;
}
 

private void dropIndexIfExists(Connection conn) {
    try {
        PreparedStatement pstmt = conn.prepareStatement("SHOW INDEX FROM domain WHERE KEY_NAME = 'path'");
        ResultSet rs = pstmt.executeQuery();

        if (rs.next()) {
            pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`domain` DROP INDEX `path`");
            pstmt.executeUpdate();
            s_logger.debug("Unique key 'path' is removed successfully");
        }

        rs.close();
        pstmt.close();
    } catch (SQLException e) {
        throw new CloudRuntimeException("Unable to drop 'path' index for 'domain' table due to:", e);
    }
}
 

public List<Event> getAllEvents() {
    List<Event> list = jdbcTemplate.query("select * from t_event", new RowMapper<Event>() {
        @Override
        public Event mapRow(ResultSet rs, int rowNum) throws SQLException {
            Integer id = rs.getInt("id");
            Date date = rs.getDate("date");
            String msg = rs.getString("msg");
            Event event = new Event(id, new Date(date.getTime()), msg);
            return event;
        }
    });
    return list;
}
 

public void testRelationshipWithIntegerInOperation() throws SQLException
{
	Book book = BookFinder.findOne(BookFinder.inventoryId().eq(1));
	SupplierList suppliersList = book.getSuppliersWithSpecificId();
	suppliersList.forceResolve();
	String sql = "select count(distinct SUPPLIER_ID) from SUPPLIER_INVENTORY_ITEM " +
			"where INVENTORY_ID = 1 and SUPPLIER_ID in (?, ?)";
	Connection conn = getConnection();
	int count;
	try
	{
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, 1);
		pstmt.setInt(2, 2);
		ResultSet rs = pstmt.executeQuery();
		rs.next();
		count = rs.getInt(1);
	}
	finally
	{
           if(conn != null) conn.close();
	}
	assertEquals(suppliersList.size(), count);
}
 

/**
 * Helper method for retrieving the value for a column from the given result set
 * using the type code of the column.
 * 
 * @param resultSet The result set
 * @param column    The column
 * @param idx       The value's index in the result set (starting from 1) 
 * @return The value
 */
protected Object getObjectFromResultSet(ResultSet resultSet, Column column, int idx) throws SQLException
{
    int    originalJdbcType = column.getTypeCode();
    int    targetJdbcType   = getPlatformInfo().getTargetJdbcType(originalJdbcType);
    int    jdbcType         = originalJdbcType;
    Object value            = null;

    // in general we're trying to retrieve the value using the original type
    // but sometimes we also need the target type:
    if ((originalJdbcType == Types.BLOB) && (targetJdbcType != Types.BLOB))
    {
        // we should not use the Blob interface if the database doesn't map to this type 
        jdbcType = targetJdbcType;
    }
    if ((originalJdbcType == Types.CLOB) && (targetJdbcType != Types.CLOB))
    {
        // we should not use the Clob interface if the database doesn't map to this type 
        jdbcType = targetJdbcType;
    }
    value = extractColumnValue(resultSet, null, idx, jdbcType);
    return resultSet.wasNull() ? null : value;
}
 

@Override
public List<ApsEntityRecord> searchRecords(EntitySearchFilter[] filters) {
    Connection conn = null;
    List<ApsEntityRecord> records = new ArrayList<>();
    PreparedStatement stat = null;
    ResultSet result = null;
    try {
        conn = this.getConnection();
        stat = this.buildStatement(filters, false, true, conn);
        result = stat.executeQuery();
        while (result.next()) {
            ApsEntityRecord record = this.createRecord(result);
            if (!records.contains(record)) {

                records.add(record);
            }
        }
    } catch (Throwable t) {
        _logger.error("Error while loading records list", t);
        throw new RuntimeException("Error while loading records list", t);
    } finally {
        closeDaoResources(result, stat, conn);
    }
    return records;
}
 

@Test
public void testListJars() throws Exception {
    Connection conn = driver.connect(url, EMPTY_PROPS);
    Path jarPath = new Path(util.getConfiguration().get(QueryServices.DYNAMIC_JARS_DIR_KEY));
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("list jars");
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar1.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar2.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar3.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar4.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar5.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar6.jar").toString(), rs.getString("jar_location"));
    assertFalse(rs.next());
}
 

private Query<Collection<Integer>> inactiveTableProviderIDsQuery() {
    String sql = SELECT + "pr." + ExtensionTableProviderTable.ID +
            FROM + ExtensionTableProviderTable.TABLE_NAME + " pr" +
            INNER_JOIN + ExtensionPluginTable.TABLE_NAME + " pl on pl." + ExtensionPluginTable.ID + "=pr." + ExtensionTableProviderTable.PLUGIN_ID +
            WHERE + ExtensionPluginTable.LAST_UPDATED + "<?" +
            AND + ExtensionPluginTable.SERVER_UUID + "=?";
    return new QueryStatement<Collection<Integer>>(sql, 100) {
        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, deleteOlder);
            statement.setString(2, serverUUID.toString());
        }

        @Override
        public Collection<Integer> processResults(ResultSet set) throws SQLException {
            Collection<Integer> providerIds = new HashSet<>();
            while (set.next()) {
                providerIds.add(set.getInt(ExtensionProviderTable.ID));
            }
            return providerIds;
        }
    };
}
 

public static GeographyCode readCode(Connection conn, PreparedStatement statement, boolean decodeAncestors) {
        if (conn == null || statement == null) {
            return null;
        }
        try {
            GeographyCode code;
            statement.setMaxRows(1);
            try ( ResultSet results = statement.executeQuery()) {
                if (results.next()) {
                    code = readResults(results);
                } else {
                    return null;
                }
            }
            if (decodeAncestors && code != null) {
                decodeAncestors(conn, code);
            }
            return code;
        } catch (Exception e) {
            failed(e);
//            logger.debug(e.toString());
            return null;
        }
    }
 

@Override
public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException {
    // Catalogs are not supported for schemas
    if (catalog != null && catalog.length() > 0) {
        return emptyResultSet;
    }
    StringBuilder buf = new StringBuilder("select /*+" + Hint.NO_INTRA_REGION_PARALLELIZATION + "*/ distinct " +
            "null " + TABLE_CATALOG_NAME + "," + // no catalog for tables
            TABLE_SCHEM_NAME +
            " from " + TYPE_SCHEMA_AND_TABLE + 
            " where " + COLUMN_NAME + " is null");
    if (schemaPattern != null) {
        buf.append(" and " + TABLE_SCHEM_NAME + " like '" + SchemaUtil.normalizeIdentifier(schemaPattern) + "'");
    }
    Statement stmt = connection.createStatement();
    return stmt.executeQuery(buf.toString());
}
 

public boolean addReceiver(String uid, Receiver r) {
    boolean added = false;

    Connection conn = null;
    PreparedStatement sttmt = null;
    ResultSet rs = null;

    try {
        conn = DBUtil.getConnection();

        String add = "insert into receiver(user_id, name, address, phone) "
                + "values(?, ?, ?, ?) ;";
        sttmt = conn.prepareStatement(add);
        sttmt.setString(1, uid);
        sttmt.setString(2, r.getName());
        sttmt.setString(3, r.getAddress());
        sttmt.setString(4, r.getPhone());
        int affected = sttmt.executeUpdate();
        if (affected != 0) {
            added = true;
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        close(conn, sttmt, rs);
    }
    return added;
}
 

public boolean relative( ResultSet rs, int rows ) throws HopDatabaseException {
  try {
    return rs.relative( rows );
  } catch ( SQLException e ) {
    throw new HopDatabaseException( "Unable to move the resultset forward " + rows + " rows", e );
  }
}
 

@SuppressWarnings("unchecked")
public OracleJdbc4NativeJdbcExtractor() {
	try {
		setConnectionType((Class<Connection>) getClass().getClassLoader().loadClass("oracle.jdbc.OracleConnection"));
		setStatementType((Class<Statement>) getClass().getClassLoader().loadClass("oracle.jdbc.OracleStatement"));
		setPreparedStatementType((Class<PreparedStatement>) getClass().getClassLoader().loadClass("oracle.jdbc.OraclePreparedStatement"));
		setCallableStatementType((Class<CallableStatement>) getClass().getClassLoader().loadClass("oracle.jdbc.OracleCallableStatement"));
		setResultSetType((Class<ResultSet>) getClass().getClassLoader().loadClass("oracle.jdbc.OracleResultSet"));
	}
	catch (Exception ex) {
		throw new IllegalStateException(
				"Could not initialize OracleJdbc4NativeJdbcExtractor because Oracle API classes are not available: " + ex);
	}
}
 

/**
 * Test that when a nested routine drops a role/schema, the
 * current value is correctly reset. For roles, the current role
 * is unchanged, since it is lazily checked (and potentially reset
 * to NONE if it no longer exists or it is no longer granted to
 * session user) only when it is attempted used for anything. For
 * schema, the current schema should revert back to the session's
 * default schema. This holds for all frames on the session
 * context stack (see also caller's check).
 */
public static void dropper() throws SQLException
{
    Connection conn1 = null;

    try {
        conn1 = DriverManager.getConnection("jdbc:default:connection");

        // Drop current contexts
        Statement stm = conn1.createStatement();
        stm.executeUpdate("drop role outermost");
        stm.executeUpdate("drop schema outermost restrict");
        stm.close();

        String[] expected = new String[]{null, "TEST_DBO"};

        // check that we revert correctly
        for (int i= 0; i < variableKeywords.length; i++) {
            String curr = currentPrefix[i] + variableKeywords[i];

            PreparedStatement ps =
                conn1.prepareStatement("values " + curr);

            ResultSet rs = ps.executeQuery();
            assertCurrent(variableKeywords[i], rs, expected[i]);
            rs.close();
            ps.close();
        }
    } finally {
        if (conn1 != null) {
            try {
                conn1.close();
            } catch (Exception e) {
            }
        }
    }
}
 

/**
 * <p>
 * Casting to UDTs.
 * </p>
 */
public void test_06_casts() throws Exception
{
    Connection conn = getConnection();

    // cast a NULL as a UDT
    goodStatement
        ( conn,
          "create type price_06_b external name 'com.splicemachine.dbTesting.functionTests.tests.lang.Price' language java\n" );
    assertResults
        (
         conn,
         "values ( cast ( null as price_06_b ) )\n",
         new String[][]
         {
             { null },
         },
         false
         );

    // casting an untyped parameter to a UDT
    PreparedStatement ps = chattyPrepare
        ( conn, "values ( cast ( ? as price_06_b ) )" );
    ps.setObject( 1, Price.makePrice() );
    ResultSet rs = ps.executeQuery();
    rs.next();
    Price result = (Price) rs.getObject( 1 );
    rs.close();
    ps.close();
    assertTrue( Price.makePrice().equals( result ) );
}
 

private static void checkIfTableIsEmpty(ResultSet resultSet) {
  int columnId = 1;
  try {
    if (!resultSet.next() || !checkIfTableIsEmpty(resultSet, columnId)) {
      throw new RuntimeException("Table is not empty. Aborting COPY with disposition EMPTY");
    }
  } catch (SQLException e) {
    throw new RuntimeException("Unable run pipeline with EMPTY disposition.", e);
  }
}
 

public Object readFrom(ResultSet rs, CollectionPersister persister, CollectionAliases descriptor, Object owner)
throws HibernateException, SQLException {
	Object element = persister.readElement( rs, owner, descriptor.getSuffixedElementAliases(), getSession() );
	Object index = persister.readIndex( rs, descriptor.getSuffixedIndexAliases(), getSession() );
	if ( element!=null ) map.put(index, element);
	return element;
}
 

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

public static boolean isTableAnIndexOrganizedTable(Connection connection,
    OracleTable table) throws SQLException {

  /*
   * http://ss64.com/orad/DBA_TABLES.html IOT_TYPE: If index-only table,then
   * IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL
   */

  boolean result = false;

  PreparedStatement statement =
      connection.prepareStatement("select iot_type " + "from dba_tables "
          + "where owner = ? " + "and table_name = ?");
  statement.setString(1, table.getSchema());
  statement.setString(2, table.getName());
  ResultSet resultSet = statement.executeQuery();

  if (resultSet.next()) {
    String iotType = resultSet.getString("iot_type");
    result = iotType != null && !iotType.isEmpty();
  }

  resultSet.close();
  statement.close();

  return result;
}