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

@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();
}
 

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;

}
 

/**
 * 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;
}
 

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);
    }
}
 

/**
 * 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);
  }
}
 

/**
 * 目录产生
 * 
 * @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);
  }
}
 

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();
}
 

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;
}
 

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);
    }
}
 

/**
 * 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");
}
 

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;
}
 

/**
 *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();
    }
  }
}
 

@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;
}