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

/**
 * Since the ResultSet class mysteriously lacks a "size()" method, and
 * since simply iterating thru what might be a large ResultSet could be
 * a costly exercise, we play the following games.
 * We take care to try and leave R as we found it, cursor-wise.
 *
 * @param R - instance of jdbc ResultSet
 * @return - boolean true if R has 1 or more rows, false if not.
 */
public static boolean nonemptyQueryResult(ResultSet R) {

    logger.trace("nonemptyQueryResult(R)");
    boolean nonEmpty = false;
    if (R == null) {
        return false;
    }

    try {
        if (R.getRow() != 0) {
            nonEmpty = true;
        } else {
            logger.trace("nonemptyQueryResult(R) - check R.first()...");
            nonEmpty = R.first();
            R.beforeFirst();
        }
    } catch (Throwable t) {
        surfaceThrowable("nonemptyQueryResult()", t);
    }

    return nonEmpty;

}
 

/**
	 * 说明：判断查询结果集内的记录个数，如果小于1条则返回true
	 * 
	 * @param res
	 *            查询结果集
	 * @return boolean 结果集异常或等于0条返回true，否则返回false
	 */
	private static boolean ResultIsNull(ResultSet res) {
		if (null == res) {
//			log.info("数据库连接异常");
			return true;
		}
		
		try {
			res.last();
			if (res.getRow() == 0) {
				log.info("查询结果集为0条");
				return true;
			} else {
				res.beforeFirst();
				return false;
			}
		} catch (SQLException e) {

			log.error("计算查询结果集个数失败!");
			log.error(e.getMessage());
			return true;
		}

	}
 

@Override
public int[] getUsedIDs() {
	PreparedStatement statement = DB.prepareStatement("SELECT id FROM players", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

	try {
		ResultSet rs = statement.executeQuery();
		rs.last();
		int count = rs.getRow();
		rs.beforeFirst();
		int[] ids = new int[count];
		for (int i = 0; i < count; i++) {
			rs.next();
			ids[i] = rs.getInt("id");
		}
		return ids;
	}
	catch (SQLException e) {
		log.error("Can't get list of id's from players table", e);
	}
	finally {
		DB.close(statement);
	}

	return new int[0];
}
 

@Override
public int[] getUsedIDs() {
	PreparedStatement statement = DB.prepareStatement("SELECT item_unique_id FROM inventory", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

	try {
		ResultSet rs = statement.executeQuery();
		rs.last();
		int count = rs.getRow();
		rs.beforeFirst();
		int[] ids = new int[count];
		for (int i = 0; i < count; i++) {
			rs.next();
			ids[i] = rs.getInt("item_unique_id");
		}
		return ids;
	}
	catch (SQLException e) {
		log.error("Can't get list of id's from inventory table", e);
	}
	finally {
		DB.close(statement);
	}

	return new int[0];
}
 

/**
 * {@inheritDoc}
 */
@Override
public int[] getUsedIDs() {
	PreparedStatement statement = DB.prepareStatement("SELECT id FROM players", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

	try {
		ResultSet rs = statement.executeQuery();
		rs.last();
		int count = rs.getRow();
		rs.beforeFirst();
		int[] ids = new int[count];
		for (int i = 0; i < count; i++) {
			rs.next();
			ids[i] = rs.getInt("id");
		}
		return ids;
	}
	catch (SQLException e) {
		log.error("Can't get list of id's from players table", e);
	}
	finally {
		DB.close(statement);
	}

	return new int[0];
}
 

/**
 * Gets the rows from the given {@link ResultSet}. Optionally, limits the number of rows returned using maxResult.
 * 
 * @param resultSet {@link ResultSet}
 * @param maxResult The maximum number of rows returned
 * @return {@link List} of {@link JdbcStatementResultSetRow}
 * @throws SQLException when there is an error reading from the {@link ResultSet}
 */
private List<JdbcStatementResultSetRow> getRows(ResultSet resultSet, Integer maxResult) throws SQLException
{
    List<JdbcStatementResultSetRow> rows = new ArrayList<>();
    int columnCount = resultSet.getMetaData().getColumnCount();
    while (resultSet.next())
    {
        JdbcStatementResultSetRow row = new JdbcStatementResultSetRow();
        for (int i = 1; i <= columnCount; i++)
        {
            String column = resultSet.getString(i);
            row.getColumns().add(column);
        }
        rows.add(row);

        // Exit loop if the maxResult is reached.
        if (maxResult != null && resultSet.getRow() == maxResult)
        {
            break;
        }
    }
    return rows;
}
 

@Override
public int[] getUsedIDs() {
	PreparedStatement statement = DB.prepareStatement("SELECT item_unique_id FROM player_registered_items WHERE item_unique_id <> 0", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

	try {
		ResultSet rs = statement.executeQuery();
		rs.last();
		int count = rs.getRow();
		rs.beforeFirst();
		int[] ids = new int[count];
		for (int i = 0; i < count; i++) {
			rs.next();
			ids[i] = rs.getInt(1);
		}
		return ids;
	}
	catch (SQLException e) {
		log.error("Can't get list of id's from player_registered_items table", e);
	}
	finally {
		DB.close(statement);
	}

	return new int[0];
}
 

@Override
public int[] getUsedIDs() {
	PreparedStatement statement = DB.prepareStatement("SELECT mail_unique_id FROM mail", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	try {
		ResultSet rs = statement.executeQuery();
		rs.last();
		int count = rs.getRow();
		rs.beforeFirst();
		int[] ids = new int[count];
		for (int i = 0; i < count; i++) {
			rs.next();
			ids[i] = rs.getInt("mail_unique_id");
		}
		return ids;
	}
	catch (SQLException e) {
		log.error("Can't get list of id's from mail table", e);
	}
	finally {
		DB.close(statement);
	}
	return new int[0];
}
 

private long getTotalCount(Map<String, String>  proObjectMap, Connection conn, Logger log) {
	String dataObject = proObjectMap.get(DataChecker.DATA_OBJECT);
	if(dataObject != null) {
		dataObject = dataObject.replace(" ", "").trim();
	}else{
		log.error("DataObject is null");
		return 0;
	}
	log.info("-------------------------------------- search hive/spark/mr data ");
	log.info("-------------------------------------- : " + dataObject);
	try (PreparedStatement pstmt = getStatement(conn, dataObject)) {
		ResultSet rs = pstmt.executeQuery();
		return rs.last() ? rs.getRow() : 0;
	} catch (SQLException e) {
		log.error("fetch data from Hive MetaStore error", e);
		return 0;
	}
}
 

/**
 * Executes the given query.
 *
 * @param query The query to execute.
 * @return Returns the number of rows returned or the number of rows affected by the query.
 * @throws SQLException
 */
private int executeQuery(String query) throws SQLException {
  Connection connection = connectionPool.getConnection();
  Statement statement;
  int resultCount = 0;
  try {
    statement = connection.createStatement();
    logger.log(Level.INFO, "Running query: " + query);
    boolean hasResultSet = statement.execute(query);
    if (hasResultSet) {
      ResultSet resultSet = statement.getResultSet();
      if (resultSet != null && resultSet.last()) {
        resultCount = resultSet.getRow();
      }
    } else {
      resultCount = statement.getUpdateCount();
    }
  } catch (SQLException e) {
    // Something went wrong. Close the connection.
    connection.close();
    throw e;
  }
  if (statement != null) {
    statement.close();
  }
  connection.close();
  return resultCount;
}
 

/**
 * Returns an array of the primary keys found in a given table. An array of size
 * 0 is returned in the event of a db access error.
 * 
 * @param table The entity node representing the table from which we wish to get
 *              the primary IDs
 * @return The primary IDs of all records found in the given table
 */
public static int[] getPKs(final EntityNode table) {
	final JDBCDriver dbd;
	final ColumnNaming cn;
	final ResultSet result;

	try {
		dbd = table.getMModel().getDatabase().getJDBCDriver();
		cn = new ColumnNaming(dbd);
		result = dbd.executeQuery("SELECT * FROM " + table.getName());

		result.last(); // move to last row to get row count

		final int[] pKs = new int[result.getRow()];

		// move back to first row
		result.beforeFirst();
		result.next();

		final String pIdName = cn.tablePK(table);

		for (int i = 0; i < pKs.length; i++) {
			pKs[i] = result.getInt(pIdName);

			result.next();
		}

		return pKs;
	} catch (SQLException e) {
		System.err.println("Error in DatabaseUtil: " + e.getMessage());

		return new int[0];
	}
}
 

@Override
public int getRow() throws SQLException {
    int currentRow = this.getCurrentResultSet() == null ? 0 : this.getCurrentResultSet().getRow();
    int totalRowsOfPreviousResultSets = 0;
    if (currentIndex - 2 >= 0) {
        int index = currentIndex - 2;
        for (; index >= 0; index--) {
            ResultSet set = results.get(index).getResultSet();
            set.last();
            totalRowsOfPreviousResultSets += set.getRow();
        }
    }
    return currentRow + totalRowsOfPreviousResultSets;
}
 

protected void assertResultSetLength(ResultSet rset, int len) throws Exception {
    int oldRowPos = rset.getRow();
    rset.last();
    assertEquals("Result set length", len, rset.getRow());
    if (oldRowPos > 0) {
        rset.absolute(oldRowPos);
    } else {
        rset.beforeFirst();
    }
}
 

private int getResultNumber(ResultSet rs, long maxRecToParse, int recCount) throws SQLException {
	logger.debug("IN");

	int toReturn;

	logger.debug("resultset type [" + rs.getType() + "] (" + (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) + ")");
	if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {

		int recordsCount = 0;
		if (recCount < maxRecToParse) {
			// records read where less then max records to read, therefore the resultset has been completely read
			recordsCount = getOffset() + recCount;
		} else {
			recordsCount = rs.getRow();
			while (rs.next()) {
				recordsCount++;
				// do nothing, just scroll result set
			}
		}
		toReturn = recordsCount;
	} else {
		rs.last();
		toReturn = rs.getRow();
	}

	logger.debug("Reading total record numeber is equal to [" + toReturn + "]");
	logger.debug("OUT " + toReturn);
	return toReturn;
}
 

public static Vector getListPage(Connection con,String tablename,int curpage,int pagesize,int  colcount)
{
    Vector pandy=new Vector();
    DBOject obj=new DBOject();
    String sql="SELECT * FROM "+tablename;

    ResultSet rs=obj.getrollresultset(con,sql);
    try
    {
        rs.last();
        int rowcount=rs.getRow();
        rs.beforeFirst();
        rs=obj.listpage(rs,curpage,pagesize);
        Object noteinfo[][] = new Object[pagesize][colcount];
        int counter=0;
        while(rs.next())
        {
             if(counter>=pagesize)
            {
                break;
            }
            for (int i = 0; i < colcount; i++)
            {
                noteinfo[counter][i] = rs.getObject(i + 1);
            }
            counter++;
        }
        Object noteinfo2[][]= new Object[counter][colcount];
        for(int i=0;i<counter;i++)
        for(int j=0;j<colcount;j++)
        {
            noteinfo2[i][j]=noteinfo[i][j];
        }
        pandy.add(String.valueOf(rowcount));
        pandy.add(noteinfo2);
        return pandy;
    } catch (SQLException e)
    {
        e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
    } finally
    {
        obj.freecon(con);
    }
    return pandy;
}
 

private void compareResultSets(ResultSet expected, ResultSet actual) throws Exception {
    if (expected == null) {
        if (actual != null) {
            fail("Expected null result set, actual was not null.");
        } else {
            return;
        }
    } else if (actual == null) {
        fail("Expected non-null actual result set.");
    }

    expected.last();

    int expectedRows = expected.getRow();

    actual.last();

    int actualRows = actual.getRow();

    assertEquals(expectedRows, actualRows);

    ResultSetMetaData metadataExpected = expected.getMetaData();
    ResultSetMetaData metadataActual = actual.getMetaData();

    assertEquals(metadataExpected.getColumnCount(), metadataActual.getColumnCount());

    for (int i = 0; i < metadataExpected.getColumnCount(); i++) {
        assertEquals(metadataExpected.getColumnName(i + 1), metadataActual.getColumnName(i + 1));
        assertEquals(metadataExpected.getColumnType(i + 1), metadataActual.getColumnType(i + 1));
        assertEquals(metadataExpected.getColumnClassName(i + 1), metadataActual.getColumnClassName(i + 1));
    }

    expected.beforeFirst();
    actual.beforeFirst();

    StringBuilder messageBuf = null;

    while (expected.next() && actual.next()) {

        if (messageBuf != null) {
            messageBuf.append("\n");
        }

        for (int i = 0; i < metadataExpected.getColumnCount(); i++) {
            if (expected.getObject(i + 1) == null && actual.getObject(i + 1) == null) {
                continue;
            }

            if ((expected.getObject(i + 1) == null && actual.getObject(i + 1) != null)
                    || (expected.getObject(i + 1) != null && actual.getObject(i + 1) == null)
                    || (!expected.getObject(i + 1).equals(actual.getObject(i + 1)))) {
                if ("COLUMN_DEF".equals(metadataExpected.getColumnName(i + 1))
                        && (expected.getObject(i + 1) == null && actual.getString(i + 1).length() == 0)
                        || (expected.getString(i + 1).length() == 0 && actual.getObject(i + 1) == null)) {
                    continue; // known bug with SHOW FULL COLUMNS, and we
                             // can't distinguish between null and ''
                             // for a default
                }

                if ("CHAR_OCTET_LENGTH".equals(metadataExpected.getColumnName(i + 1))) {
                    if (((com.mysql.jdbc.ConnectionImpl) this.conn).getMaxBytesPerChar(
                            CharsetMapping.getJavaEncodingForMysqlCharset(((com.mysql.jdbc.Connection) this.conn).getServerCharset())) > 1) {
                        continue; // SHOW CREATE and CHAR_OCT *will* differ
                    }
                }

                if (messageBuf == null) {
                    messageBuf = new StringBuilder();
                } else {
                    messageBuf.append("\n");
                }

                messageBuf.append("On row " + expected.getRow() + " ,for column named " + metadataExpected.getColumnName(i + 1) + ", expected '"
                        + expected.getObject(i + 1) + "', found '" + actual.getObject(i + 1) + "'");

            }
        }
    }

    if (messageBuf != null) {
        fail(messageBuf.toString());
    }
}
 

public static int countRecords(ResultSet resultSet)
{
    int rowCount = 0;

    //Determine rs size
    if (resultSet != null)
    {
        try
        {
            int currentIndex = resultSet.getRow();

            //Go to the end and get that row number
            if (resultSet.last())
            {
                rowCount = resultSet.getRow();
            }

            //Put the cursor back
            if (currentIndex > 0)
            {
                resultSet.absolute(currentIndex);
            }
            else
            {
                resultSet.beforeFirst();
            }
        }
        catch (SQLException ex)
        {
            //TODO: Decide whether if absolute() not be supported, try first() as a last resort??
            try
            {
                resultSet.first();
            }
            catch (SQLException se)
            {
                //we won't try anymore.
            }
        }
    }

    return rowCount;
}
 

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

/**
 * checks sorting a table with according to a given collation
 */
protected String checkSorting(String collationName) {

    String stmt1 = "DROP TABLE WORDLIST IF EXISTS;";
    String stmt2 =
        "CREATE TEXT TABLE WORDLIST ( ID INTEGER, WORD VARCHAR(50) );";
    String stmt3 = "SET TABLE WORDLIST SOURCE \"" + collationName
                   + ".csv;encoding=UTF-8\"";
    String selectStmt    = "SELECT ID, WORD FROM WORDLIST ORDER BY WORD";
    String returnMessage = "";

    try {

        // set database collation
        statement.execute(getSetCollationStmt(collationName));
        statement.execute(stmt1);
        statement.execute(stmt2);
        statement.execute(stmt3);

        ResultSet results = statement.executeQuery(selectStmt);

        while (results.next()) {
            int expectedPosition = results.getInt(1);
            int foundPosition    = results.getRow();

            if (expectedPosition != foundPosition) {
                String word = results.getString(2);

                return "testing collation '" + collationName
                       + "' failed\n" + "  word              : " + word
                       + "\n" + "  expected position : "
                       + expectedPosition + "\n"
                       + "  found position    : " + foundPosition + "\n";
            }
        }
    } catch (SQLException e) {
        return "testing collation '" + collationName
               + "' failed\n  exception message: " + e.getMessage() + "\n";
    }

    return "";
}
 

public int getUnreadCount() throws SQLException {
    PreparedStatement ps = connection.prepareStatement(SELECT_UNREAD_MESSAGE);

    ResultSet result = ps.executeQuery();

    return result.last() ? result.getRow() : 0;
}