下面列出了org.junit.jupiter.api.Disabled#java.sql.ResultSet 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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;
}
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 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 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 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;
}
}
/**
* 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;
}
@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;
}
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();
}
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;
}
@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;
}
};
}
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);
}
}
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;
}
@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);
}
/**
* 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 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;
}
@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 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 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;
}
protected ResultSet[] callDerbyProcedure( Object[] inOut, boolean[] success) {
success[0] = true;
try {
return callProcedure(dConn, inOut);
} catch (SQLException se) {
if (! exceptionHandler.handleDerbyException(dConn, se) ) {
success[0] = false;
return null;
}
}
return null;
}
@Override
public void map(Key key, Row value, OutputCollector<Text, TradeBuyOrdersRow> output, Reporter reporter) throws IOException {
try {
if ( ! value.getEventType().equals(Type.AFTER_DELETE)) {
ResultSet rs = value.getRowAsResultSet();
int oid=rs.getInt("oid");
output.collect(new Text(Integer.toString(oid)), new TradeBuyOrdersRow(oid, rs.getInt("cid"), rs.getInt("sid"), rs.getInt("tid"), rs.getInt("qty"), rs.getString("status"), rs.getBigDecimal("bid"), rs.getTimestamp("ordertime")) );
}
} catch (SQLException se) {
System.err.println("mapper - -Error logging result set" + se);
throw new IOException(se);
}
}
@Override
public int loadPartnerId(final Player player) {
Connection con = null;
int playerId = player.getObjectId();
int partnerId = 0;
try {
con = DatabaseFactory.getConnection();
PreparedStatement stmt = con.prepareStatement(SELECT_QUERY);
stmt.setInt(1, playerId);
stmt.setInt(2, playerId);
ResultSet rset = stmt.executeQuery();
int partner1Id = 0;
int partner2Id = 0;
if (rset.next()) {
partner1Id = rset.getInt("player1");
partner2Id = rset.getInt("player2");
}
partnerId = playerId == partner1Id ? partner2Id : partner1Id;
rset.close();
stmt.close();
}
catch (Exception e) {
log.error("Could not get partner for player: " + playerId + " from DB: " + e.getMessage(), e);
}
finally {
DatabaseFactory.close(con);
}
return partnerId;
}
/**
* 通过Librarian的名字来修改Librarian的密码
*
* @param name
* Librarian的用户名
* @param oldPassword
* 旧密码
* @param newPassword
* 新密码
* @return 旧密码错误,返回false;修改成功返回true
* @author zengyaoNPU
*/
public boolean changePasswordByOldPassword_NewPassword(String name, String oldPassword, String newPassword) {
Connection conn = null;
Statement st = null;
ResultSet rs;
try {
conn = DatabaseUtil.getInstance().getConnection();
st = conn.createStatement();
String sql = "select * from librarian where librarian_name='" + name + "'";
rs = st.executeQuery(sql);
if (rs.next()) {
String oldPw = rs.getString("librarian_password");
if (oldPassword.equals(oldPw)) {// 检查旧密码是否匹配
sql = "update librarian set librarian_password='" + newPassword + "' where librarian_name='" + name
+ "'";
int row = st.executeUpdate(sql);// 更新数据库,将新密码填入
if (row == 0) {// 改变0行,说明没有改变,返回false
System.out.println("--changePasswordByOldPassword_NewPassword--,row==0");
return false;
} else {
return true;// 改变1行,更改密码成功
}
} else {
System.out.println("密码不正确");
return false;
}
} else {// 未找到数据
return false;// 没有该用户,一般不会出现这种情况
}
} catch (Exception e) {
System.out.println("--LibrarianDAO--,--changePasswordByOldPassword_NewPassword()--suffers exception");
return false;
}
}
/**
* Binding null as all types.
*/
@Test
public void testBindNullForAllTypes() throws Throwable
{
try (Connection connection = getConnection())
{
connection.createStatement().execute(
"create or replace table TEST_BIND_ALL_TYPES(C0 string," +
"C1 number(20, 3), C2 INTEGER, C3 double, C4 varchar(1000)," +
"C5 string, C6 date, C7 time, C8 timestamp_ntz, " +
"C9 timestamp_ltz, C10 timestamp_tz," +
"C11 BINARY, C12 BOOLEAN)");
for (SnowflakeType.JavaSQLType t : SnowflakeType.JavaSQLType.ALL_TYPES)
{
PreparedStatement preparedStatement = connection.prepareStatement(
"insert into TEST_BIND_ALL_TYPES values(?, ?,?,?, ?,?,?, ?,?,?, ?,?,?)"
);
preparedStatement.setString(1, t.toString());
for (int i = 2; i <= 13; ++i)
{
preparedStatement.setNull(i, t.getType());
}
preparedStatement.executeUpdate();
}
ResultSet result = connection.createStatement().executeQuery("select * from TEST_BIND_ALL_TYPES");
while (result.next())
{
String testType = result.getString(1);
for (int i = 2; i <= 13; ++i)
{
assertNull(String.format("Java Type: %s is not null", testType), result.getString(i));
}
}
}
}
@Test
public void testSimpleWithStatementWithAggregate() throws Exception {
ResultSet rs = methodWatcher.executeQuery("with footest as " +
"(select count(*) as count, col2 from foo group by col2) " +
"select foo2.col1, count from foo2 " +
"inner join footest on foo2.col1 = footest.col2"
);
Assert.assertTrue("with join algebra incorrect",rs.next());
Assert.assertEquals("Wrong Count", 1, rs.getInt(1));
Assert.assertEquals("Wrong Join Column" , 5, rs.getInt(2));
Assert.assertFalse("with join algebra incorrect",rs.next());
}
private static LocalDateTime getLocalDateTimeFromTimeStamp(ResultSet resultSet, String field) throws SQLException {
Timestamp timestamp = resultSet.getTimestamp(field);
if (timestamp != null) {
return timestamp.toLocalDateTime();
}
return null;
}
/**
* Various tests for JIRA-614: handling of rows which span QRYDTA blocks. What happens
* when the SplitQRYDTA has to span 3+ blocks.
*
* @throws SQLException
*/
public void testSpanQRYDTABlocks() throws SQLException {
int[] lens = { 32672, 32672, 32672, 32672, };
boolean[] useClob = { false, false, false, false, };
createTable(BIG_TABLE_NAME, lens, useClob);
String[] sa = { "a", "b", "c", "d", };
insertOneRow(BIG_TABLE_NAME, sa, lens);
String[] row = getStringArray(sa, lens);
String[][] expected = { row, };
validTable(expected, BIG_TABLE_NAME);
insertOneRow(BIG_TABLE_NAME, sa, lens);
insertOneRow(BIG_TABLE_NAME, sa, lens);
expected = new String[][] { row, row, row, };
validTable(expected, BIG_TABLE_NAME);
String sql1 = getSelectSql(BIG_TABLE_NAME);
Statement st = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery(sql1);
assertEquals("Before operation, row No. is 0.", 0, rs.getRow());
rs.first();
assertEquals("After calling first(), row No. is 1.", 1, rs.getRow());
validSingleRow(row, useClob, rs);
rs.next();
assertEquals("After calling next(), row No. is 2.", 2, rs.getRow());
validSingleRow(row, useClob, rs);
rs.previous();
assertEquals("After calling previous(), row No. is 1.", 1, rs.getRow());
validSingleRow(row, useClob, rs);
rs.last();
assertEquals("After calling last(), row No. is 3.", 3, rs.getRow());
validSingleRow(row, useClob, rs);
rs.close();
st.close();
}
public void testDAPLocalBug() throws Exception {
startVMs(1, 2);
Connection conn = TestUtil.getConnection();
Statement st = conn.createStatement();
st.execute("CREATE TABLE APP.EMP (ID VARCHAR(10) NOT NULL, NAME VARCHAR(25), CONSTRAINT EMP_PK PRIMARY KEY (ID)) PARTITION BY PRIMARY KEY REDUNDANCY 1");
//st.execute("CREATE TABLE APP.EMP (ID VARCHAR(10) NOT NULL, NAME VARCHAR(25), CONSTRAINT EMP_PK PRIMARY KEY (ID)) PARTITION BY column(NAME) REDUNDANCY 1");
st.execute("insert into APP.EMP values('1', 'one'), ('2', 'two'), ('3', 'three')");
st.execute("CREATE PROCEDURE TESTLOCAL(loc INT) "
+ "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '"
+ ProcedureTestDUnit.class.getName() + ".testLocal' "
+ "DYNAMIC RESULT SETS 1");
CallableStatement cs = conn.prepareCall(
"CALL TESTLOCAL(?) on table APP.EMP");
cs.setInt(1, 3);
cs.execute();
int rsIndex = 0;
int cnt = 0;
do {
++rsIndex;
ResultSet rs = cs.getResultSet();
while (rs.next()) {
cnt++;
System.out.println("KN: " + rs.getObject(1) + ", " + rs.getObject(2));
}
} while (cs.getMoreResults());
assertEquals(1, rsIndex);
assertEquals(1, cnt);
conn.close();
}