下面列出了java.sql.ResultSet#next ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
@Override
public int getUnspendOutCountByHDAccountWithPath(int hdAccountId, AbstractHD.PathType pathType) {
int result = 0;
String sql = "select count(tx_hash) cnt from outs where out_address in " +
"(select address from hd_account_addresses where path_type =? and out_status=?) " +
"and hd_account_id=?";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Integer.toString(pathType.getValue())
, Integer.toString(Out.OutStatus.unspent.getValue())
, Integer.toString(hdAccountId)
});
ResultSet c = statement.executeQuery();
if (c.next()) {
int idColumn = c.findColumn("cnt");
if (idColumn != -1) {
result = c.getInt(idColumn);
}
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
private String dropViews() throws SQLException {
final StringBuilder ddl = new StringBuilder();
ResultSet viewSet = null;
try {
viewSet = metaData.getTables(null, null, null, new String[] { "VIEW" });
while (viewSet.next()) {
String name = viewSet.getString("TABLE_NAME");
final String schema = viewSet.getString("TABLE_SCHEM");
name = dbSetting.getTableNameWithSchema(name, schema);
if (newViewNames == null || newViewNames.contains(name)) {
ddl.append(dropView(name));
ddl.append("\r\n");
}
}
} finally {
if (viewSet != null) {
viewSet.close();
}
}
return ddl.toString();
}
/**
* This method converts result set to an array of Map
*
* @param rs
* - ResultSet
* @return Map[]
* @throws Exception
*/
private static Map<String, String>[] consume2( ResultSet rs ) throws Exception
{
ResultSetMetaData rsmd = rs.getMetaData();
List<Map<String, String>> results = new ArrayList<Map<String, String>>( 10 );
int colCount = rsmd.getColumnCount();
while ( rs.next() )
{
Map<String, String> row = new HashMap<String, String>();
for ( int i = 1; i <= colCount; ++i )
{
String val = String.valueOf( rs.getObject( i ) );
row.put( rsmd.getColumnName( i ), val );
row.put( i + "", val );
}
results.add( row );
}
return toOutArray2( results );
}
public Set<String> getWhoDrops(Integer itemId) {
Set<String> list = new HashSet<>();
Connection con = null;
try {
con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT dropperid FROM drop_data WHERE itemid = ? LIMIT 50");
ps.setInt(1, itemId);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
String resultName = MapleMonsterInformationProvider.getInstance().getMobNameFromId(rs.getInt("dropperid"));
if (!resultName.isEmpty()) {
list.add(resultName);
}
}
rs.close();
ps.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* Test for Bug#73070 - prepareCall() throws NPE
*
* To test this, we create a basic stored procedure with a
* parameter, call it and check the result.
*/
public void testBug73070() throws Exception {
if (!this.isSetForFabricTest) {
return;
}
this.conn = (FabricMySQLConnection) getNewDefaultDataSource().getConnection(this.username, this.password);
this.conn.setServerGroupName("fabric_test1_global");
this.conn.createStatement().executeUpdate("drop procedure if exists bug73070");
this.conn.createStatement().executeUpdate("create procedure bug73070(in x integer) select x");
CallableStatement stmt = this.conn.prepareCall("{call bug73070(?)}");
stmt.setInt(1, 42);
ResultSet rs = stmt.executeQuery();
rs.next();
assertEquals(42, rs.getInt(1));
rs.close();
stmt.close();
this.conn.createStatement().executeUpdate("drop procedure bug73070");
this.conn.close();
}
private String getDatabasePosition(String showDatabaseSql) {
try (PreparedStatement preparedStatement =
connection.prepareStatement(showDatabaseSql)) {
ResultSet resultSet = preparedStatement.executeQuery();
if (! resultSet.next()) {
throw new RuntimeException("Execute `SELECT DATABASE()` failed!!");
}
String database = resultSet.getString(1);
if (Objects.isNull(database)) {
throw new RuntimeException("Please add db_name in `jdbcUrl`");
}
return database;
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
}
@Override
public History buildHistory(UUID taskid) throws CrudlException {
History history = new History();
try (
Connection connection = dataSource.getConnection();
PreparedStatement st = connection.prepareStatement("SELECT taskid,started,completed,report,id FROM EVENTS WHERE taskid = ?");
) {
st.setString(1, taskid.toString());
ResultSet rs = st.executeQuery();
while (rs.next()) {
try (Reader reportReader = rs.getClob(4).getCharacterStream();) {
History.Event event = new History.Event();
event.setTaskId(UUID.fromString(rs.getString(1)));
event.setStartTimestamp(new Date(rs.getTimestamp(2).getTime()));
event.setEndTimestamp(new Date(rs.getTimestamp(3).getTime()));
event.setReport(deserialize(reportReader, History.Report.class));
event.setUuid(UUID.fromString(rs.getString(5)));
history.add(event);
}
}
return history;
} catch (IOException|SQLException ex) {
throw new CrudlException("Error selecting broker definition", ex);
}
}
private int getAuthenticatorIdentifier(Connection dbConnection, int idPId, String authnType)
throws SQLException, IdentityProviderManagementException {
String sqlStmt = null;
PreparedStatement prepStmt = null;
ResultSet rs = null;
try {
sqlStmt = IdPManagementConstants.SQLQueries.GET_IDP_AUTH_SQL;
prepStmt = dbConnection.prepareStatement(sqlStmt);
prepStmt.setInt(1, idPId);
prepStmt.setString(2, authnType);
rs = prepStmt.executeQuery();
if (rs.next()) {
return rs.getInt("ID");
} else {
throw new IdentityProviderManagementException("Cannot find authenticator : "
+ authnType);
}
} finally {
IdentityDatabaseUtil.closeAllConnections(null, rs, prepStmt);
}
}
public static boolean reinstateAutoIncrement(String colName,
String tableId, StringBuilder colDef) throws SQLException
{
getAutoIncStmt.setString(1, dblook.stripQuotes(colName));
getAutoIncStmt.setString(2, tableId);
ResultSet autoIncCols = getAutoIncStmt.executeQuery();
if (autoIncCols.next()) {
long start = autoIncCols.getLong(1);
if (!autoIncCols.wasNull()) {
colDef.append(" GENERATED ");
colDef.append(autoIncCols.getObject(5) == null ?
"ALWAYS ":"BY DEFAULT ");
colDef.append("AS IDENTITY (START WITH ");
colDef.append(autoIncCols.getLong(1));
colDef.append(", INCREMENT BY ");
colDef.append(autoIncCols.getLong(2));
colDef.append(")");
return true;
}
}
return false;
}
/**
* 目录产生
*
* @param fs
* @throws Exception
*/
public void loadDirFromDB() throws Exception {
String sql = "select min(pos_id) start_pos,max(pos_id) end_pos from "
+ " x_tmnlpos where x_tmnlpos.floor_no=? and x_tmnlpos.build_no=? ";
ResultSet rs = DaoUtil.queryData(sql, new Object[] { floorNoString,
buildNoString });
if (rs.next()) {
startPos = rs.getInt("start_pos");
endPos = rs.getInt("end_pos");
}
sql = "select id from `d_building` where `BUILDING_NO`=? ";
rs = DaoUtil.queryData(sql, new Object[] { buildNoString });
if (rs.next()) {
buildNo = rs.getInt("id");
}
sql = "select id from `d_floor` where `FLOOR_NO`=? and BUILD_NO=? ";
rs = DaoUtil.queryData(sql,
new Object[] { floorNoString, buildNoString });
if (rs.next()) {
floorNo = rs.getInt("id");
}
if (buildNo == -1 || floorNo == -1) {
throw new Exception("传入楼宇编号或楼层编号不存在!");
}
}
private void addPrimaryKey(DatabaseMetaData dbMeta, PhysicalModel model, PhysicalTable table) {
PhysicalColumn column;
PhysicalPrimaryKey primaryKey;
ResultSet rs;
primaryKey = null;
try {
rs = dbMeta.getPrimaryKeys(model.getCatalog(), model.getSchema(), table.getName());
/*
* 1. TABLE_CAT String => table catalog (may be null) 2. TABLE_SCHEM String => table schema (may be null) 3. TABLE_NAME String => table name 4.
* COLUMN_NAME String => column name 5. KEY_SEQ short => sequence number within primary key 6. PK_NAME String => primary key name (may be null)
*/
while (rs.next()) {
if (primaryKey == null) {
primaryKey = FACTORY.createPhysicalPrimaryKey();
primaryKey.setName(rs.getString("PK_NAME"));
primaryKey.setTable(table);
model.getPrimaryKeys().add(primaryKey);
getPropertiesInitializer().addProperties(primaryKey);
}
column = table.getColumn(rs.getString("COLUMN_NAME"));
if (column != null) {
primaryKey.getColumns().add(column);
}
}
rs.close();
} catch (Throwable t) {
throw new RuntimeException("Impossible to retrive primaryKeys metadata", t);
}
}
private static void deletePriorTables(long ts, String tenantId, String url) throws Exception {
Properties props = new Properties();
props.put(QueryServices.QUEUE_SIZE_ATTRIB, Integer.toString(1024));
if (ts != HConstants.LATEST_TIMESTAMP) {
props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts));
}
Connection conn = DriverManager.getConnection(url, props);
try {
deletePriorTables(ts, conn, url);
deletePriorSequences(ts, conn);
// Make sure all tables and views have been dropped
props.remove(CURRENT_SCN_ATTRIB);
try (Connection seeLatestConn = DriverManager.getConnection(url, props)) {
DatabaseMetaData dbmd = seeLatestConn.getMetaData();
ResultSet rs = dbmd.getTables(null, null, null, new String[]{PTableType.VIEW.toString(), PTableType.TABLE.toString()});
while (rs.next()) {
String fullTableName = SchemaUtil.getEscapedTableName(
rs.getString(PhoenixDatabaseMetaData.TABLE_SCHEM),
rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
try {
PhoenixRuntime.getTable(conn, fullTableName);
fail("The following tables are not deleted that should be:" + getTableNames(rs));
} catch (TableNotFoundException e) {
}
}
}
}
finally {
conn.close();
}
}
@Override
public boolean isAutoIncrement(
Connection connection,
String catalogName,
String schemaName,
String tableName,
String columnName)
throws SQLException {
if (connection == null) {
throw new GenNullPointerException("connection");
}
if (tableName == null) {
throw new GenNullPointerException("tableName");
}
if (columnName == null) {
throw new GenNullPointerException("columnName");
}
String sql =
"select generated from syscat.columns where tabschema = ? and tabname = ? and colname = ?";
PreparedStatement ps = JdbcUtil.prepareStatement(connection, sql);
ps.setString(1, schemaName);
ps.setString(2, tableName);
ps.setString(3, columnName);
try {
ResultSet rs = ps.executeQuery();
try {
if (rs.next()) {
String generated = rs.getString(1);
return "A".equals(generated) || "D".equals(generated);
}
return false;
} finally {
JdbcUtil.close(rs);
}
} finally {
JdbcUtil.close(ps);
}
}
protected boolean isLastKeyArrived(Connection gConn) {
List<Integer> aList = new ArrayList<Integer>();
int last_key = (int) SQLBB.getBB().getSharedCounters().read(SQLBB.defaultEmployeesPrimary);
try {
ResultSet rs = gConn.createStatement().executeQuery("select eid from default1.employees");
while (rs.next()) {
aList.add(rs.getInt("EID"));
}
Log.getLogWriter().info("list is " + aList.toString() + ", expected last key is " + last_key);
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
if (aList.size() == last_key) return true; //how many records are in the result sets
else return false;
}
private void initAccounts() throws SQLException {
//ResultSet r = this.connection.createStatement()
// .executeQuery("select count(*) from app.holding");
ResultSet r = this.connection.createStatement()
.executeQuery("select count(*) from app.account");
r.next();
int totalAccounts = r.getInt(1);
r.close();
if (totalAccounts == 0) {
String s = "No accounts found";
throw new QueryPerfException(s);
}
listOfAccounts = new int[totalAccounts];
Log.getLogWriter().info("Caching " + totalAccounts
+ " APP.HOLDING.ACCOUNT_ACCOUNTID information.");
//r = this.connection.createStatement()
// .executeQuery("select distinct account_accountid from app.holding");
r = this.connection.createStatement()
.executeQuery("select accountid from app.account");
for (int i = 0; i < listOfAccounts.length; i++) {
r.next();
listOfAccounts[i] = r.getInt(1);
}
r.close();
TIntIntHashMap orderIdAccId = new TIntIntHashMap();
r = this.connection.createStatement().executeQuery("select orderid, account_accountid from app.orders");
while(r.next()) {
orderIdAccId.put(r.getInt(1), r.getInt(2));
}
r.close();
listOfOrderIds = orderIdAccId.keys();
listOfOrderAccAccId = orderIdAccId.getValues();
}
@Override
public List<DeviceGroup> getGroups(GroupPaginationRequest request, int tenantId)
throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
List<DeviceGroup> deviceGroupList = null;
String groupName = request.getGroupName();
boolean hasGroupName = false;
String owner = request.getOwner();
boolean hasOwner = false;
boolean hasLimit = request.getRowCount() != 0;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql = "SELECT ID, DESCRIPTION, GROUP_NAME, OWNER FROM DM_GROUP WHERE TENANT_ID = ?";
if (groupName != null && !groupName.isEmpty()) {
sql += " AND GROUP_NAME LIKE ?";
hasGroupName = true;
}
if (owner != null && !owner.isEmpty()) {
sql += " AND OWNER LIKE ?";
hasOwner = true;
}
if (hasLimit) {
sql += " LIMIT ? OFFSET ?";
}
int paramIndex = 1;
stmt = conn.prepareStatement(sql);
stmt.setInt(paramIndex++, tenantId);
if (hasGroupName) {
stmt.setString(paramIndex++, groupName + "%");
}
if (hasOwner) {
stmt.setString(paramIndex++, owner + "%");
}
if (hasLimit) {
stmt.setInt(paramIndex++, request.getRowCount());
stmt.setInt(paramIndex, request.getStartIndex());
}
resultSet = stmt.executeQuery();
deviceGroupList = new ArrayList<>();
while (resultSet.next()) {
deviceGroupList.add(GroupManagementDAOUtil.loadGroup(resultSet));
}
} catch (SQLException e) {
throw new GroupManagementDAOException("Error occurred while listing all groups in tenant: " + tenantId, e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
return deviceGroupList;
}
/**
*Printout one single table.
*
*@param table
* Table name
*@throws Exception
*/
protected void generateTableState(String table) throws Exception {
PreparedStatement ps = null;
ResultSet rs = null;
ResultSetMetaData rsmt = null;
try {
ps = getDerbyDatabaseConnection().prepareStatement("SELECT * FROM " + table);
rs = ps.executeQuery();
rsmt = rs.getMetaData();
StringBuilder sb = new StringBuilder();
System.out.println("Table " + table + ":");
for (int i = 1; i <= rsmt.getColumnCount(); i++) {
sb.append("| ").append(rsmt.getColumnName(i)).append(" ");
}
sb.append("|");
System.out.println(sb.toString());
while (rs.next()) {
sb = new StringBuilder();
for (int i = 1; i <= rsmt.getColumnCount(); i++) {
sb.append("| ").append(rs.getString(i)).append(" ");
}
sb.append("|");
System.out.println(sb.toString());
}
System.out.println("");
} finally {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
}
}
public GoodsDetail getGoodsDetail(String gid) {
GoodsDetail detail = new GoodsDetail();
Connection conn = null;
PreparedStatement sttmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
// 1. 查询除 goods_image 以外的所有属性
String queryGoods = "select goods_id, goods_name, goods_describe, discount_deadline, discount_rate, "
+ " attribute_id, attribute_value, price, inventory, level_one, level_two "
+ "from ((goods join goods_attribute using(goods_id))"
+ " join category"
+ " using(category_id)) "
+ "where goods.is_valid=true and goods_attribute.is_valid=true "
+ " and category.is_valid=true"
+ " and goods_id = ?;";
sttmt = conn.prepareStatement(queryGoods);
sttmt.setInt(1, Integer.valueOf(gid));
rs = sttmt.executeQuery();
if (rs.next()) {
detail.setGoodsId(rs.getString(1));
detail.setGoodsName(rs.getString(2));
detail.setGoodsDescribe(rs.getString(3));
detail.setDiscountDeadline(rs.getString(4));
detail.setDiscountRate(rs.getString(5));
GoodsAttrString attr = new GoodsAttrString();
attr.setAttributeId(rs.getString(6));
attr.setAttributeValue(rs.getString(7));
attr.setPrice(rs.getString(8));
attr.setInventory(rs.getString(9));
detail.addAttr(attr);
detail.setLevelOne(rs.getString(10));
detail.setLevelTwo(rs.getString(11));
}
while (rs.next()) {
GoodsAttrString a = new GoodsAttrString();
a.setAttributeId(rs.getString(6));
a.setAttributeValue(rs.getString(7));
a.setPrice(rs.getString(8));
a.setInventory(rs.getString(9));
detail.addAttr(a);
}
sttmt.close();
rs.close();
// 2. 查询 image_addr
String queryImg = "select image_addr from goods_image where is_valid=true "
+ " and goods_id = ?;";
sttmt = conn.prepareStatement(queryImg);
sttmt.setInt(1, Integer.valueOf(gid));
rs = sttmt.executeQuery();
while (rs.next()) {
detail.addImage(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(conn, sttmt, rs);
}
return detail;
}
/**
* EOFException when reading from blob's binary stream
* and calling length() twice
*
* @param lengthless Insert data with lengthless method.
* @throws SQLException
* @throws IOException
*/
public void testDerby1368 (boolean lengthless) throws SQLException, IOException
{
Statement stmt = createStatement();
stmt.execute("create table T1368 (ID char(32) PRIMARY KEY, DATA blob(2G) not null)");
// add row
int length = 1024 * 1024;
byte[] data = new byte[length];
data[0] = 1;
data[1] = 2;
ByteArrayInputStream bais = new ByteArrayInputStream(data);
PreparedStatement ps = prepareStatement("insert into T1368 (ID, DATA) values (?, ?)");
ps.setString(1, "id");
if (lengthless)
ps.setBinaryStream(2, bais);
else
ps.setBinaryStream(2, bais,length);
ps.execute();
ps.close();
// read row
ps = prepareStatement("select DATA from T1368 where ID = ?");
ps.setString(1, "id");
ResultSet rs = ps.executeQuery();
rs.next();
Blob b = rs.getBlob(1);
// test output
assertEquals(length,b.length());
InputStream in = b.getBinaryStream();
assertEquals(1, in.read());
//drain the stream
while (in.read() != -1 );
in.close();
in = b.getBinaryStream();
assertEquals(length,b.length());
assertEquals(1, in.read());
in.close();
rs.close();
stmt.executeUpdate("DROP TABLE T1368");
}
protected void addDefaultSGProvider(Connection conn, long physicalNetworkId, long zoneId, String networkType, boolean is304) {
PreparedStatement pstmtUpdate = null, pstmt2 = null;
try {
//add security group service provider (if security group service is enabled for at least one guest network)
boolean isSGServiceEnabled = false;
String selectSG = "";
if (is304) {
selectSG =
"SELECT nm.* FROM `cloud`.`ntwk_service_map` nm JOIN `cloud`.`networks` n ON nm.network_id = n.id where n.data_center_id = ? and nm.service='SecurityGroup'";
} else {
selectSG = "SELECT * from `cloud`.`networks` where is_security_group_enabled=1 and data_center_id=?";
}
pstmt2 = conn.prepareStatement(selectSG);
pstmt2.setLong(1, zoneId);
ResultSet sgDcSet = pstmt2.executeQuery();
if (sgDcSet.next()) {
isSGServiceEnabled = true;
}
sgDcSet.close();
pstmt2.close();
if (isSGServiceEnabled) {
s_logger.debug("Adding PhysicalNetworkServiceProvider SecurityGroupProvider to the physical network id=" + physicalNetworkId);
String insertPNSP =
"INSERT INTO `cloud`.`physical_network_service_providers` (`uuid`, `physical_network_id` , `provider_name`, `state` ,"
+ "`destination_physical_network_id`, `vpn_service_provided`, `dhcp_service_provided`, `dns_service_provided`, `gateway_service_provided`,"
+ "`firewall_service_provided`, `source_nat_service_provided`, `load_balance_service_provided`, `static_nat_service_provided`,"
+ "`port_forwarding_service_provided`, `user_data_service_provided`, `security_group_service_provided`) VALUES (?,?,?,?,0,0,0,0,0,0,0,0,0,0,0,1)";
pstmtUpdate = conn.prepareStatement(insertPNSP);
pstmtUpdate.setString(1, UUID.randomUUID().toString());
pstmtUpdate.setLong(2, physicalNetworkId);
pstmtUpdate.setString(3, "SecurityGroupProvider");
pstmtUpdate.setString(4, "Enabled");
pstmtUpdate.executeUpdate();
pstmtUpdate.close();
}
} catch (SQLException e) {
throw new CloudRuntimeException("Exception while adding default Security Group Provider", e);
} finally {
closeAutoCloseable(pstmt2);
closeAutoCloseable(pstmtUpdate);
}
}