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

public Spitter mapRow(ResultSet rs, int rowNum) throws SQLException {
	long id = rs.getLong("id");
	String username = rs.getString("username");
	String password = rs.getString("password");
	String fullName = rs.getString("fullname");
	String email = rs.getString("email");
	boolean updateByEmail = rs.getBoolean("updateByEmail");
	return new Spitter(id, username, password, fullName, email, updateByEmail);
}
 

private static void testTransaction(Connection theCon) throws SQLException {
	System.out.println("testTransaction begin");
	theCon.setAutoCommit(false);
	String sql = "select id,name from company limit 1";
	String oldName = null;
	String upSQL = null;
	ResultSet rs = theCon.createStatement().executeQuery(sql);
	if (rs.next()) {
		long id = rs.getLong(1);
		oldName = rs.getString(2);
		upSQL = "update company set name='updatedname' where id=" + id;
		// System.out.println(sql);
	}
	int count = theCon.createStatement().executeUpdate(upSQL);
	Assert.assertEquals(true, count > 0);
	theCon.rollback();
	rs = theCon.createStatement().executeQuery(sql);
	String newName = null;
	if (rs.next()) {
		newName = rs.getString(2);
	}
	Assert.assertEquals(true, oldName.equals(newName));
	System.out.println("testTransaction passed");
}
 

@Override
public TaskStatsT mapRow(ResultSet rs, int rowNum)
        throws SQLException {

    final TaskStatsT stats = new TaskStatsT();
    stats.cores = rs.getInt("int_cores");
    stats.highCores = rs.getDouble("flt_cores_high");
    stats.usedCores = 0;
    stats.ram = rs.getInt("int_ram");
    stats.usedRam = 0;
    stats.highRam = rs.getInt("int_ram_high");
    stats.startTime = rs.getLong("time_started");
    stats.stopTime = rs.getLong("time_stopped");
    stats.retryNum = rs.getInt("int_retry");
    stats.progress = rs.getInt("int_progress");
    stats.lastLogLine = "";
    stats.exitSignal = rs.getInt("int_exit_signal");
    stats.exitStatus = rs.getInt("int_exit_status");
    stats.active = true;
    return stats;
}
 

@Override
public RichVersion retrieveFromDatabase(long id) throws GroundException {
  String sql = String.format(SqlConstants.SELECT_STAR_BY_ID, "rich_version", id);

  ResultSet resultSet;
  String reference;
  long structureVersionId;

  try (Connection con = dbSource.getConnection()) {
    Statement stmt = con.createStatement();
    resultSet = stmt.executeQuery(sql);

    if (!resultSet.next()) {
      throw new GroundException(ExceptionType.VERSION_NOT_FOUND, RichVersion.class.getSimpleName(), String.format("%d", id));
    }

    reference = resultSet.getString(3);
    structureVersionId = resultSet.getLong(2);
    stmt.close();
    con.close();
  } catch (SQLException e) {
    throw new GroundException(e);
  }

  Map<String, Tag> tags = this.postgresTagDao.retrieveFromDatabaseByVersionId(id);
  Map<String, String> referenceParams = getReferenceParameters(id);
  structureVersionId = structureVersionId == 0 ? -1 : structureVersionId;

  return new RichVersion(id, tags, structureVersionId, reference, referenceParams);
}
 

public Hit mapRow(ResultSet rs, int rowNum) throws SQLException {
	Hit hit = new Hit();
	hit.setId(rs.getLong(1));

	Folder folder = new Folder();
	folder.setId(rs.getLong(2));
	folder.setName(rs.getString(3));
	folder.setType(rs.getInt(7));
	hit.setFolder(folder);
	hit.setFileName(rs.getString(3));
	if (rs.getLong(8) != 0) {
		hit.setDocRef(rs.getLong(8));
		folder.setFoldRef(rs.getLong(8));
		hit.setType("folderalias");
	} else {
		hit.setType("folder");
	}
	hit.setCustomId(Long.toString(rs.getLong(1)));
	hit.setDate(rs.getTimestamp(6));
	hit.setCreation(rs.getTimestamp(5));
	hit.setComment(rs.getString(4));
	hit.setPublished(1);

	if (rs.getLong(10) != 0) {
		hit.setTemplateId(rs.getLong(10));
		hit.setTemplateName(rs.getString(9));
		Template t = new Template();
		t.setId(rs.getLong(10));
		t.setName(rs.getString(9));
		hit.setTemplate(t);
	}

	return hit;
}
 

public AccountObject convertAccount(ResultSet result) throws SQLException {
	long id = result.getLong(AccountEnum.ID);
	String username = result.getString(AccountEnum.USERNAME);
	String nickName = result.getString(AccountEnum.NICK_NAME);
	String password = result.getString(AccountEnum.PASSWORD);
	String email = result.getString(AccountEnum.EMAIL);
	boolean enable = result.getBoolean(AccountEnum.ENABLE);

	AccountObject account = new AccountObject(id, username);
	account.setPassword(password).setNickName(nickName).setEmail(email)
			.setEnable(enable);
	return account;
}
 

public Object getResult(Field field, ResultSet rs) throws SQLException {
    Class<?> type = field.getType();
    if(Integer.class == type){
        return rs.getInt(field.getName());
    }else if(String.class == type){
        return rs.getString(field.getName());
    }else if(Long.class == type){
        return rs.getLong(field.getName());
    }else if(SEX.class == type){
        MtTypeHandler typeHandler = MtTypeHandlerRegistory.TYPE_HANDLER_MAP.get(type);
        return typeHandler.getResult(resultSet,field.getName());
    }else{
        return rs.getString(field.getName());
    }
}
 

private Map<BigInteger, DigestEntry> buildResult(ResultSet rs, List<BigInteger> serialNumbers)
    throws SQLException {
  Map<BigInteger, DigestEntry> ret = new HashMap<>(serialNumbers.size());

  while (rs.next()) {
    BigInteger serialNumber = new BigInteger(rs.getString("SN"), 16);
    if (!serialNumbers.contains(serialNumber)) {
      continue;
    }

    boolean revoked = rs.getBoolean("REV");
    Integer revReason = null;
    Long revTime = null;
    Long revInvTime = null;
    if (revoked) {
      revReason = rs.getInt("RR");
      revTime = rs.getLong("RT");
      revInvTime = rs.getLong("RIT");
      if (revInvTime == 0) {
        revInvTime = null;
      }
    }

    String base64Certhash = getBase64HashValue(rs);
    DigestEntry certB = new DigestEntry(serialNumber, revoked, revReason, revTime, revInvTime,
        base64Certhash);
    ret.put(serialNumber, certB);
  }

  return ret;
}
 

@Override
public ConfigInfoWithoutPublicStatus extractData(ResultSet rs) throws SQLException, DataAccessException {
    if (rs.next()) {
        return new ConfigInfoWithoutPublicStatus(rs.getString("group_id"), rs.getString("data_id"), rs.getString("profile"),
                rs.getLong("version"), PublicStatus.isInUse(rs.getInt("public_status")),
                rs.getTimestamp("update_time"));
    } else {
        return null;
    }
}
 

public long getLong(int n) {
    long j = 0;
    try {
        ResultSet rs = getResultSet();
        if (rs != null) {
            j = rs.getLong(n);
        }
    } catch (Exception e) {
        Debug.warning(e);
    }
    return j;
}
 

public long queryMaxUserGroupPointer(String groupId, String siteUserId) {
	long startTime = System.currentTimeMillis();
	long pointer = 0;
	String sql = "SELECT MAX(pointer) FROM " + GROUP_POINTER_TABLE + " WHERE site_group_id=? AND site_user_id=?;";

	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		conn = DatabaseConnection.getSlaveConnection();
		ps = conn.prepareStatement(sql);
		ps.setString(1, groupId);
		ps.setString(2, siteUserId);

		rs = ps.executeQuery();
		if (rs.next()) {
			pointer = rs.getLong(1);
		}
	} catch (SQLException e) {
		logger.error("query max user group message pointer error.", e);
	} finally {
		DatabaseConnection.returnConnection(conn, ps, rs);
	}

	LogUtils.dbDebugLog(logger, startTime, pointer, sql, siteUserId, groupId);
	return pointer;
}
 

@Test
public void test() throws SQLException {
    System.setProperty("derby.stream.error.file", "target/derby.log");
    Marshaller marshaller = new JsonMarshaller();
    EmbeddedDataSource dataSource = new EmbeddedDataSource();
    dataSource.setDatabaseName("target/testDB");
    dataSource.setCreateDatabase("create");
    
    deleteTable(dataSource);
    
    JdbcAppender appender = new JdbcAppender();
    appender.marshaller = marshaller;
    appender.dataSource = dataSource;
    Dictionary<String, Object> config = new Hashtable<>();
    config.put("dialect", "derby");
    appender.open(config);
    
    Map<String, Object> data = new HashMap<>();
    data.put(EventConstants.TIMESTAMP, TIMESTAMP);
    Event event = new Event(TOPIC, data);
    appender.handleEvent(event);

    try (Connection con = dataSource.getConnection(); Statement statement = con.createStatement();) {
        ResultSet res = statement.executeQuery("select timestamp, content from " + TABLE_NAME);
        res.next();
        long dbTimeStamp = res.getLong(1);
        String json = res.getString(2);
        JsonReader reader = Json.createReader(new StringReader(json));
        JsonObject jsonO = reader.readObject();
        Assert.assertEquals("Timestamp db", TIMESTAMP, dbTimeStamp);
        Assert.assertEquals("Timestamp string", "2016-02-02T15:59:40,634Z",jsonO.getString("@timestamp"));
        Assert.assertEquals("timestamp long", TIMESTAMP, jsonO.getJsonNumber(EventConstants.TIMESTAMP).longValue());
        Assert.assertEquals("Topic", TOPIC, jsonO.getString(EventConstants.EVENT_TOPIC.replace('.','_')));
        Assert.assertFalse(res.next());
    }
}
 

/**
 * select from clob table (CLOBTBL2)
 * 
 * @param cloblen select expects to retrieve a clob of this length
 * @param id id of the row to retrieve
 * @param expectedRows number of rows expected to match id
 * @param file filename to compare the retrieved data against
 */
private void selectClob2(String testId,
        PreparedStatement ps, int cloblen, int id, int expectedRows,
        String file) throws Exception {
    println("========================================");
    println("START " + testId + " - SELECT CLOB of size = "
            + cloblen);

    long ST = System.currentTimeMillis();

    int count = 0;
    ps.setInt(1, id);
    ResultSet rs = ps.executeQuery();

    while (rs.next()) {
        count++;
        Clob value = rs.getClob(1);
        long l = value.length();
        long dlen = rs.getLong(2);
        assertEquals("FAIL - MISMATCH LENGTHS GOT " + l
                + " expected " + dlen + " for row in CLOBTBL2 with ID="
                + id, l, cloblen);
        compareClobToFile(value.getCharacterStream(), file, cloblen);
    }

    rs.close();
    commit();

    verifyTest(count, expectedRows,
            "Matched rows selected with clob of size(" + cloblen + ") =");

    println("Select Clob (" + cloblen + ")" + " rows= "
               + expectedRows + " = "
               + (long) (System.currentTimeMillis() - ST));
    println("========================================");
}
 

public DispatchFrame mapRow(ResultSet rs, int rowNum) throws SQLException {
    DispatchFrame frame = new DispatchFrame();
    frame.id = rs.getString("pk_frame");
    frame.name = rs.getString("frame_name");
    frame.layerId = rs.getString("pk_layer");
    frame.jobId = rs.getString("pk_job");
    frame.showId = rs.getString("pk_show");
    frame.facilityId = rs.getString("pk_facility");
    frame.retries = rs.getInt("int_retries");
    frame.state = FrameState.valueOf(rs.getString("frame_state"));
    frame.command = rs.getString("str_cmd");
    frame.jobName = rs.getString("job_name");
    frame.layerName = rs.getString("layer_name");
    frame.chunkSize = rs.getInt("int_chunk_size");
    frame.range = rs.getString("str_range");
    frame.logDir = rs.getString("str_log_dir");
    frame.shot = rs.getString("str_shot");
    frame.show = rs.getString("show_name");
    frame.owner = rs.getString("str_user");
    int uid = rs.getInt("int_uid");
    frame.uid = rs.wasNull() ? Optional.empty() : Optional.of(uid);
    frame.state = FrameState.valueOf(rs.getString("frame_state"));
    frame.minCores = rs.getInt("int_cores_min");
    frame.maxCores = rs.getInt("int_cores_max");
    frame.threadable = rs.getBoolean("b_threadable");
    frame.minMemory = rs.getLong("int_mem_min");
    frame.minGpu = rs.getLong("int_gpu_min");
    frame.version = rs.getInt("int_version");
    frame.services = rs.getString("str_services");
    return frame;
}
 

public PaloLoadInfo(ResultSet result) throws SQLException {
    this.jobId = result.getLong("JobId");
    this.label = result.getString("Label");
    this.state = PaloLoadInfo.State.valueOf(result.getString("State"));
    this.progress = result.getString("Progress");
    this.etlInfo = result.getString("EtlInfo");
    this.taskInfo = result.getString("TaskInfo");
    this.errorMsg = result.getString("ErrorMsg");
    this.createTime = result.getDate("CreateTime");
    this.etlStartTime = result.getDate("EtlStartTime");
    this.etlFinishTime = result.getDate("EtlFinishTime");
    this.loadStartTime = result.getDate("LoadStartTime");
    this.loadFinishTime = result.getDate("LoadFinishTime");
    this.url = result.getString("URL");
}
 

public Workgroup getGroup(Long groupId) throws DataAccessException {
    try {
        Workgroup group = null;
        db.openConnection();
        String sql = "select GROUP_NAME, COMMENTS, PARENT_GROUP_ID, END_DATE "
                + " from USER_GROUP where USER_GROUP_ID=?";
        ResultSet rs = db.runSelect(sql, groupId);
        if (rs.next()) {
            String groupName = rs.getString(1);
            String comments = rs.getString(2);
            group = new Workgroup(groupId, groupName, comments);
            long pid = rs.getLong(3);
            if (pid > 0L) {
                rs = db.runSelect(sql, pid);
                if (rs.next())
                    group.setParentGroup(rs.getString(1));
            }
            group.setEndDate(rs.getString(4));
        }
        if (group != null)
            loadAttributesForGroup(group);
        return group;
    }
    catch (Exception ex) {
        throw new DataAccessException(-1, "Failed to get user group", ex);
    }
    finally {
        db.closeConnection();
    }
}
 

@Override
public Long map(ResultSet r, int index, StatementContext ctx) throws SQLException {
  return r.getLong("timestamp");
}
 

/**
 * Loads the event values of a trace from the database.
 *
 * @param connection Connection to the database.
 * @param traceList The trace to load.
 *
 * @return The event values.
 *
 * @throws SQLException Thrown if the values could not be loaded.
 */
private static List<List<TraceRegister>> loadTraceEventValues(final CConnection connection,
    final TraceList traceList) throws SQLException {
  final List<List<TraceRegister>> values = new ArrayList<>();

  final String query = "select position, register_name, register_value, memory_value from "
      + CTableNames.TRACE_EVENT_VALUES_TABLE + " where trace_id = " + traceList.getId()
      + " order by position asc";

  final ResultSet resultSet = connection.executeQuery(query, true);

  int currentPosition = -1;

  try {
    List<TraceRegister> registers = new ArrayList<TraceRegister>();

    while (resultSet.next()) {
      final int position = resultSet.getInt("position");

      if (position != currentPosition) {
        if (!registers.isEmpty()) {
          values.add(Lists.newArrayList(registers));
          registers = new FilledList<TraceRegister>();
        }

        currentPosition = position;
      }

      final String name = PostgreSQLHelpers.readString(resultSet, "register_name");
      final long value = resultSet.getLong("register_value");
      final byte[] memory = resultSet.getBytes("memory_value");

      registers.add(new TraceRegister(name, new CAddress(value), memory));
    }

    if (!registers.isEmpty()) {
      values.add(Lists.newArrayList(registers));
    }
  } finally {
    resultSet.close();
  }

  return values;
}
 

public Object getPrimaryKeyFrom(ResultSet rs)
throws SQLException
{
    return rs.getString(1) + rs.getInt(2) + rs.getString(3) + rs.getInt(4) + rs.getLong(5);
}
 

public Object[][] doTxn(Connection conn, long f_id) throws SQLException {
    // 150 seats
    final long seatmap[] = new long[]
      {-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,     
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1};
    assert(seatmap.length == SeatsTest.FLIGHTS_NUM_SEATS);
      
    // First calculate the seat price using the flight's base price
    // and the number of seats that remaining
    PreparedStatement f_stmt = this.getPreparedStatement(conn, GetFlight);
    f_stmt.setLong(1, f_id);
    if (logDML) Log.getLogWriter().info("F_ID:" + f_id);
    ResultSet f_results = f_stmt.executeQuery();
    double base_price;
    long seats_total;
    long seats_left;
    double seat_price;
    if (f_results.next()) {
      // long status = results[0].getLong(0);
      base_price = f_results.getDouble(2);
      seats_total = f_results.getLong(3);
      seats_left = f_results.getLong(4);
      seat_price = f_results.getDouble(5);
      f_results.close();
    } else {
      throw new TestException("does not get result for " + GetFlight.getSQL() + 
          " for F_ID:" + f_id);
    }
    
    double _seat_price = base_price + (base_price * (1.0 - (seats_left/(double)seats_total)));
    if (logDML) Log.getLogWriter().info(String.format("Flight %d - SQL[%.2f] <-> JAVA[%.2f] [basePrice=%f, total=%d, left=%d]",
                                f_id, seat_price, _seat_price, base_price, seats_total, seats_left));
    
    // Then build the seat map of the remaining seats
    PreparedStatement s_stmt = this.getPreparedStatement(conn, GetSeats);
    s_stmt.setLong(1, f_id);
    ResultSet s_results = s_stmt.executeQuery();
    while (s_results.next()) {
      long r_id = s_results.getLong(1);
      int seatnum = s_results.getInt(3);
      if (logDML) Log.getLogWriter().info(String.format("Reserved Seat: fid %d / rid %d / seat %d", f_id, r_id, seatnum));
      if (seatmap[seatnum] != -1) throw new TestException("Duplicate seat reservation: R_ID=" + r_id);
      seatmap[seatnum] = 1;
    } // WHILE
    s_results.close();
    //could use select distinct (R_SEAT) FROM RESERVATION WHERE R_F_ID = ?"
    //and select r_seat from reservation where r_f_id = ? and compare results

    int ctr = 0;
    Object[][] returnResults = new Object[SeatsTest.FLIGHTS_NUM_SEATS][];
    for (int i = 0; i < seatmap.length; ++i) {
      if (seatmap[i] == -1) {
        // Charge more for the first seats
        double price = seat_price * (i < SeatsTest.FLIGHTS_FIRST_CLASS_OFFSET ? 2.0 : 1.0);
        if (!reproduce51122) price = Math.round(price*100)/100f;
        Object[] row = new Object[]{ f_id, i, price };
        returnResults[ctr++] = row;
        if (ctr == returnResults.length) break;
      }
    } // FOR
//      assert(seats_left == returnResults.getRowCount()) :
//          String.format("Flight %d - Expected[%d] != Actual[%d]", f_id, seats_left, returnResults.getRowCount());
   
    return returnResults;
  }