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

public Message getLastNotNullMessageFromChat(ChatBase chat) throws SQLException {
    String selectStatementString = "SELECT * FROM " + CHAT_MESSAGE_JOIN_TABLE
            + " INNER JOIN " + MESSAGE_TABLE + " ON " + CHAT_MESSAGE_JOIN_TABLE + "." + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " = " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_ROWID
            + " WHERE " + COLUMN_CHAT_MESSAGE_CHAT_ID + " = ? "
            + " AND " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_TEXT + " IS NOT NULL "
            + " ORDER BY " + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " DESC LIMIT 1";

    PreparedStatement selectStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectStatementString);
    selectStatement.setLong(1, chat.getRowID());

    ResultSet resultSet = selectStatement.executeQuery();

    if (!resultSet.isBeforeFirst()){
        resultSet.close();
        selectStatement.close();
        return null;
    }
    long rowID = resultSet.getLong(COLUMN_CHAT_MESSAGE_MESSAGE_ID);
    Message message = getMessageByRow(rowID);

    resultSet.close();
    selectStatement.close();

    return message;
}
 

public Handle getHandleByRow(long rowID) throws SQLException {
    String selectStatementString = "SELECT * FROM " + HANDLE_TABLE + " WHERE " + COLUMN_HANDLE_ROWID + " = ?";
    PreparedStatement selectStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectStatementString);
    selectStatement.setLong(1, rowID);

    ResultSet resultSet = selectStatement.executeQuery();

    if (!resultSet.isBeforeFirst()){
        resultSet.close();
        selectStatement.close();
        return null;
    }
    Handle handle = buildHandle(resultSet);

    resultSet.close();
    selectStatement.close();
    return handle;
}
 

private void createOutcomeIndex() {
    try {
        final String sqlString = "SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_CATALOG = 'def' AND TABLE_SCHEMA = DATABASE() AND TABLE_NAME = \"game\" AND INDEX_NAME = \"OutcomeIndex\"";
        final Statement gameStatement = this.dbConnection.createStatement();
        gameStatement.execute(sqlString);
        final ResultSet resultSet = gameStatement.getResultSet();
        if(!resultSet.isBeforeFirst() ) {
            final Statement indexStatement = this.dbConnection.createStatement();
            indexStatement.execute("CREATE INDEX OutcomeIndex on Game(outcome);\n");
            indexStatement.close();
        }
        gameStatement.close();
    }
    catch (final SQLException e) {
        e.printStackTrace();
    }
}
 

public String getLastEmailByDeviceId(String deviceId) throws SQLException {
    String selectStatementString = "SELECT * FROM " + TABLE_DEVICES + " WHERE " + COLUMN_DEVICE_ID + " = ?";
    PreparedStatement findStatement = getServerDatabaseConnection().prepareStatement(selectStatementString);
    findStatement.setString(1, deviceId);
    ResultSet resultSet = findStatement.executeQuery();

    if (!resultSet.isBeforeFirst()){
        resultSet.close();
        findStatement.close();
        return null;
    }
    String deviceEmail = resultSet.getString(COLUMN_DEVICE_LAST_EMAIL);

    resultSet.close();
    findStatement.close();

    return deviceEmail;
}
 

public String getNameByDeviceId(String deviceId) throws SQLException {
    String selectStatementString = "SELECT * FROM " + TABLE_DEVICES + " WHERE " + COLUMN_DEVICE_ID + " = ?";
    PreparedStatement findStatement = getServerDatabaseConnection().prepareStatement(selectStatementString);
    findStatement.setString(1, deviceId);
    ResultSet resultSet = findStatement.executeQuery();

    if (!resultSet.isBeforeFirst()){
        resultSet.close();
        findStatement.close();
        return null;
    }
    String deviceName = resultSet.getString(COLUMN_DEVICE_NAME);

    resultSet.close();
    findStatement.close();

    return deviceName;
}
 

private static void fetchSkillbooksFromReactors() {
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        
        PreparedStatement ps = con.prepareStatement("SELECT itemid FROM reactordrops WHERE itemid >= ? AND itemid < ?;");
        ps.setInt(1, skillbookMinItemid);
        ps.setInt(2, skillbookMaxItemid);
        ResultSet rs = ps.executeQuery();

        if (rs.isBeforeFirst()) {
            while(rs.next()) {
                foundSkillbooks.put(rs.getInt("itemid"), SkillBookEntry.REACTOR);
            }
        }

        rs.close();
        ps.close();
        
        con.close();
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    }
}
 

public Attachment getAttachmentByRow(long rowID) throws SQLException {
    String selectStatementString = "SELECT * FROM " + ATTACHMENT_TABLE + " WHERE " + COLUMN_ATTACHMENT_ROWID + " = ?";
    PreparedStatement selectStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectStatementString);
    selectStatement.setLong(1, rowID);

    ResultSet resultSet = selectStatement.executeQuery();

    if (!resultSet.isBeforeFirst()){
        resultSet.close();
        selectStatement.close();
        return null;
    }
    Attachment attachment = buildAttachment(resultSet);

    resultSet.close();
    selectStatement.close();
    return attachment;
}
 

public static Client getClientWithAlias(String alias){

        try(Connection con = connect();
            PreparedStatement pstmt = con.prepareStatement(selectClientWithAlias)) {

            pstmt.setString(1, alias);
            ResultSet rs  = pstmt.executeQuery();

            if (!rs.isBeforeFirst() ) {
                return null;
            }

            return new Client(
                    rs.getString(clientName),
                    rs.getString(alias),
                    rs.getString(clientGUID),
                    Client.Role.prestigeToRole(rs.getInt(clientRole)
                    ));

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;

    }
 

/** Create a table from a {@link ResultSet}. */
public static String fromResultSet(ResultSet resultSet) throws SQLException {
  if (resultSet == null) throw new NullPointerException("resultSet == null");
  if (!resultSet.isBeforeFirst()) throw new IllegalStateException("Result set not at first.");

  List<String> headers = new ArrayList<>();
  ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
  int columnCount = resultSetMetaData.getColumnCount();
  for (int column = 0; column < columnCount; column++) {
    headers.add(resultSetMetaData.getColumnName(column + 1));
  }

  List<String[]> data = new ArrayList<>();
  while (resultSet.next()) {
    String[] rowData = new String[columnCount];
    for (int column = 0; column < columnCount; column++) {
      rowData[column] = resultSet.getString(column + 1);
    }
    data.add(rowData);
  }

  String[] headerArray = headers.toArray(new String[headers.size()]);
  String[][] dataArray = data.toArray(new String[data.size()][]);
  return FlipTable.of(headerArray, dataArray);
}
 

/**
 * Function to populate Meta Data.
 * @param columnNamesSet is a set having column names given by the user
 * @throws SQLException
 */
protected void populateColumnDataTypes(HashSet<String> columnNamesSet) throws SQLException
{
  ResultSet rsColumns;
  DatabaseMetaData meta = store.getConnection().getMetaData();
  rsColumns = meta.getColumns(null, null, getTablename(), null);
  /**Identifiers (table names, column names etc.) may be stored internally in either uppercase or lowercase.**/
  if (!rsColumns.isBeforeFirst()) {
    rsColumns = meta.getColumns(null, null, getTablename().toUpperCase(), null);
    if (!rsColumns.isBeforeFirst()) {
      rsColumns = meta.getColumns(null, null, getTablename().toLowerCase(), null);
      if (!rsColumns.isBeforeFirst()) {
        throw new RuntimeException("Table name not found");
      }
    }
  }
  boolean readAllColumns = columnNamesSet.size() == 0 ? true : false;
  int remainingColumns = columnNamesSet.size();
  while (rsColumns.next()) {
    if (readAllColumns || remainingColumns > 0) {
      if (readAllColumns || columnNamesSet.contains(rsColumns.getString("COLUMN_NAME").toUpperCase())) {
        columnNames.add(rsColumns.getString("COLUMN_NAME"));
        columnNullabilities.add(rsColumns.getInt("NULLABLE"));
        columnDataTypes.add(rsColumns.getInt("DATA_TYPE"));
        remainingColumns--;
      }
    } else {
      break;
    }
  }
}
 

private boolean resultSetHasRows(ResultSet resultSet) {
	boolean hasRows = false;
	if (resultSet != null) {
		try {
			// true if the cursor is before the first row; false if the
			// cursor is at any other position or the result set contains no
			// rows }
			hasRows = resultSet.isBeforeFirst();
		} catch (SQLException e) {
		}
	}
	return hasRows;
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertIsBeforeFirst() throws SQLException {
    for (ResultSet each : resultSets) {
        each.isBeforeFirst();
    }
}
 

public Message getMessageByRow(long rowID) throws SQLException {
    String selectStatementString = "SELECT * FROM " + MESSAGE_TABLE + " WHERE " + COLUMN_MESSAGE_ROWID + " = ?";
    PreparedStatement selectStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectStatementString);
    selectStatement.setLong(1, rowID);

    ResultSet resultSet = selectStatement.executeQuery();

    if (!resultSet.isBeforeFirst()){
        resultSet.close();
        selectStatement.close();
        return null;
    }
    Handle handle = getHandleByRow(resultSet.getLong(COLUMN_MESSAGE_HANDLE_ID));
    long messageRow = resultSet.getLong(COLUMN_MESSAGE_ROWID);

    String selectChatMessageStatementString = "SELECT * FROM " + CHAT_MESSAGE_JOIN_TABLE + " WHERE " + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " = ?";
    PreparedStatement selectChatMessageStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatMessageStatementString);
    selectChatMessageStatement.setLong(1, messageRow);

    ResultSet resultChatMessageSet = selectChatMessageStatement.executeQuery();

    ChatBase chat;
    try {
        long theResultInt = resultChatMessageSet.getLong(COLUMN_CHAT_MESSAGE_CHAT_ID);
        chat = getChatByRow(theResultInt);
    }catch(Exception ex){
        return null;
    }

    List<Attachment> attachments = new ArrayList<>();
    String selectMessageAttachmentString = "SELECT * FROM " + MESSAGE_ATTACHMENT_TABLE + " WHERE " + COLUMN_MESSAGE_ATTACHMENT_MESSAGE_ID + " = ?";
    PreparedStatement selectMessageAttachmentStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectMessageAttachmentString);
    selectMessageAttachmentStatement.setLong(1, messageRow);

    boolean isResultSet = selectMessageAttachmentStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet theResultSet = selectMessageAttachmentStatement.getResultSet();
            while(theResultSet.next()) {
                long resultInt = theResultSet.getLong(COLUMN_MESSAGE_ATTACHMENT_ATTACHMENT_ID);
                attachments.add(getAttachmentByRow(resultInt));
            }
            theResultSet.close();
        } else {
            if(selectMessageAttachmentStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectMessageAttachmentStatement.getMoreResults();
    }

    Message message = buildMessage(resultSet, chat, handle, attachments);

    resultSet.close();
    resultChatMessageSet.close();
    selectStatement.close();
    selectChatMessageStatement.close();
    selectMessageAttachmentStatement.close();

    return message;
}
 

/**
 * Loads a single logical row from the result set moving forward.  This is the
 * processing used from the ScrollableResults where there were collection fetches
 * encountered; thus a single logical row may have multiple rows in the underlying
 * result set.
 *
 * @param resultSet The result set from which to do the load.
 * @param session The session from which the request originated.
 * @param queryParameters The query parameters specified by the user.
 * @param returnProxies Should proxies be generated
 * @return The loaded "row".
 * @throws HibernateException
 */
public Object loadSequentialRowsForward(
        final ResultSet resultSet,
        final SessionImplementor session,
        final QueryParameters queryParameters,
        final boolean returnProxies) throws HibernateException {

	// note that for sequential scrolling, we make the assumption that
	// the first persister element is the "root entity"

	try {
		if ( resultSet.isAfterLast() ) {
			// don't even bother trying to read further
			return null;
		}

		if ( resultSet.isBeforeFirst() ) {
			resultSet.next();
		}

		// We call getKeyFromResultSet() here so that we can know the
		// key value upon which to perform the breaking logic.  However,
		// it is also then called from getRowFromResultSet() which is certainly
		// not the most efficient.  But the call here is needed, and there
		// currently is no other way without refactoring of the doQuery()/getRowFromResultSet()
		// methods
		final EntityKey currentKey = getKeyFromResultSet(
				0,
				getEntityPersisters()[0],
				null,
				resultSet,
				session
			);

		return sequentialLoad( resultSet, session, queryParameters, returnProxies, currentKey );
	}
	catch ( SQLException sqle ) {
		throw JDBCExceptionHelper.convert(
		        factory.getSQLExceptionConverter(),
		        sqle,
		        "could not perform sequential read of results (forward)",
		        getSQLString()
			);
	}
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertIsBeforeFirst() throws SQLException {
    for (ResultSet each : resultSets) {
        each.isBeforeFirst();
    }
}
 

/**
 * Applies any database updates by comparing the current version to the
 * stored version.  Does nothing if tables have not been created.
 */
private static void updateDatabase() throws Exception
{
	try (Statement stmt = connection.createStatement()) {
		int version = 0;

		// if 'info' table does not exist, assume version 0 and apply all updates
		String sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'info'";
		ResultSet rs = stmt.executeQuery(sql);
		boolean infoExists = rs.isBeforeFirst();
		rs.close();
		if (!infoExists) {
			// if 'scores' table also does not exist, databases not yet created
			sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'scores'";
			ResultSet scoresRS = stmt.executeQuery(sql);
			boolean scoresExists = scoresRS.isBeforeFirst();
			scoresRS.close();
			if (!scoresExists)
				return;
		} else {
			// try to retrieve stored version
			sql = "SELECT value FROM info WHERE key = 'version'";
			ResultSet versionRS = stmt.executeQuery(sql);
			String versionString = (versionRS.next()) ? versionRS.getString(1) : "0";
			versionRS.close();
			try {
				version = Integer.parseInt(versionString);
			} catch (NumberFormatException e) {}
		}

		// database versions match
		if (version >= DATABASE_VERSION)
			return;

		// apply updates
		for (String query : getUpdateQueries(version))
			stmt.executeUpdate(query);

		// update version
		if (infoExists) {
			PreparedStatement ps = connection.prepareStatement("REPLACE INTO info (key, value) VALUES ('version', ?)");
			ps.setString(1, Integer.toString(DATABASE_VERSION));
			ps.executeUpdate();
			ps.close();
		}
	}
}
 

public ChatBase getChatByRow(long rowID) throws SQLException {
    String selectChatStatementString = "SELECT * FROM " + CHAT_TABLE + " WHERE " + COLUMN_CHAT_ROWID + " = ?";
    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);
    selectChatStatement.setLong(1, rowID);

    ResultSet chatResultSet = selectChatStatement.executeQuery();

    if (!chatResultSet.isBeforeFirst()){
        chatResultSet.close();
        selectChatStatement.close();
        return null;
    }

    List<Handle> handles = new ArrayList<>();
    String selectChatHandleStatementString = "SELECT * FROM " + CHAT_HANDLES_TABLE + " WHERE " + COLUMN_CHAT_HANDLE_CHAT_ID + " = ?";
    PreparedStatement selectChatHandleStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatHandleStatementString);
    selectChatHandleStatement.setLong(1, rowID);

    boolean isResultSet = selectChatHandleStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatHandleStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_HANDLE_HANDLE_ID);
                handles.add(getHandleByRow(resultInt));
            }
            resultSet.close();
        } else {
            if(selectChatHandleStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatHandleStatement.getMoreResults();
    }

    ChatBase chat = buildChat(chatResultSet, handles);

    chatResultSet.close();
    selectChatStatement.close();
    selectChatHandleStatement.close();

    return chat;
}
 

public ChatBase getChatByGuid(String guid) throws SQLException {
    String selectChatStatementString = "SELECT * FROM " + CHAT_TABLE + " WHERE " + COLUMN_CHAT_GUID + " = ?";
    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);
    selectChatStatement.setString(1, guid);

    ResultSet chatResultSet = selectChatStatement.executeQuery();

    if (!chatResultSet.isBeforeFirst()){
        chatResultSet.close();
        selectChatStatement.close();
        return null;
    }

    List<Handle> handles = new ArrayList<>();
    String selectChatHandleStatementString = "SELECT * FROM " + CHAT_HANDLES_TABLE + " WHERE " + COLUMN_CHAT_HANDLE_CHAT_ID + " = ?";
    PreparedStatement selectChatHandleStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatHandleStatementString);
    selectChatHandleStatement.setLong(1, chatResultSet.getLong(COLUMN_CHAT_ROWID));

    boolean isResultSet = selectChatHandleStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatHandleStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_HANDLE_HANDLE_ID);
                handles.add(getHandleByRow(resultInt));
            }
            resultSet.close();
        } else {
            if(selectChatHandleStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatHandleStatement.getMoreResults();
    }

    ChatBase chat = buildChat(chatResultSet, handles);

    chatResultSet.close();
    selectChatStatement.close();
    selectChatHandleStatement.close();

    return chat;
}
 

public ChatBase getChatByGroupID(String groupID) throws SQLException {
    String selectChatStatementString = "SELECT * FROM " + CHAT_TABLE + " WHERE " + COLUMN_CHAT_GROUP_ID + " = ?";
    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);
    selectChatStatement.setString(1, groupID);

    ResultSet chatResultSet = selectChatStatement.executeQuery();

    if (!chatResultSet.isBeforeFirst()){
        chatResultSet.close();
        selectChatStatement.close();
        return null;
    }

    List<Handle> handles = new ArrayList<>();
    String selectChatHandleStatementString = "SELECT * FROM " + CHAT_HANDLES_TABLE + " WHERE " + COLUMN_CHAT_HANDLE_CHAT_ID + " = ?";
    PreparedStatement selectChatHandleStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatHandleStatementString);
    selectChatHandleStatement.setLong(1, chatResultSet.getLong(COLUMN_CHAT_ROWID));

    boolean isResultSet = selectChatHandleStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatHandleStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_HANDLE_HANDLE_ID);
                handles.add(getHandleByRow(resultInt));
            }
            resultSet.close();
        } else {
            if(selectChatHandleStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatHandleStatement.getMoreResults();
    }

    ChatBase chat = buildChat(chatResultSet, handles);

    chatResultSet.close();
    selectChatStatement.close();
    selectChatHandleStatement.close();

    return chat;
}
 

/**
 * Applies any database updates by comparing the current version to the
 * stored version.  Does nothing if tables have not been created.
 */
private static void updateDatabase() throws SQLException {
	try (Statement stmt = connection.createStatement()) {
		int version = 0;

		// if 'info' table does not exist, assume version 0 and apply all updates
		String sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'info'";
		ResultSet rs = stmt.executeQuery(sql);
		boolean infoExists = rs.isBeforeFirst();
		rs.close();
		if (!infoExists) {
			// if 'beatmaps' table also does not exist, databases not yet created
			sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'beatmaps'";
			ResultSet beatmapsRS = stmt.executeQuery(sql);
			boolean beatmapsExists = beatmapsRS.isBeforeFirst();
			beatmapsRS.close();
			if (!beatmapsExists)
				return;
		} else {
			// try to retrieve stored version
			sql = "SELECT value FROM info WHERE key = 'version'";
			ResultSet versionRS = stmt.executeQuery(sql);
			String versionString = (versionRS.next()) ? versionRS.getString(1) : "0";
			versionRS.close();
			try {
				version = Integer.parseInt(versionString);
			} catch (NumberFormatException e) {}
		}

		// database versions match
		if (version >= DATABASE_VERSION)
			return;

		// apply updates
		for (String query : getUpdateQueries(version))
			stmt.executeUpdate(query);

		// update version
		if (infoExists) {
			PreparedStatement ps = connection.prepareStatement("REPLACE INTO info (key, value) VALUES ('version', ?)");
			ps.setString(1, Integer.toString(DATABASE_VERSION));
			ps.executeUpdate();
			ps.close();
		}
	}
}