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

private void processQuery(long now, Connection con, String sql)
		throws SQLException {
	PreparedStatement ps = con.prepareStatement(sql);
	ps.setLong(1, now - Math.round(TimeUnit.DAYS.toMillis(1)));
	ps.setLong(2, now);
	long t = System.currentTimeMillis();
	System.out.println("querying...");
	ResultSet rs = ps.executeQuery();
	List<String> names = Lists.newArrayList();
	int count = 0;
	while (rs.next()) {
		String name = rs.getString(1);
		double lat = rs.getDouble(2);
		double lon = rs.getDouble(3);
		count++;
		if (lat >= -6 && lat <= -5 && lon >= 136 && lon <= 138)
			names.add(name);
	}
	System.out.println("found=" + names.size() + " from " + count + " in "
			+ (System.currentTimeMillis() - t) / 1000.0 + "s");
	ps.close();
}
 

@Test
public void testArrayIndexUsedInGroupByClause() throws Exception {
	long ts = nextTimestamp();
	String tenantId = getOrganizationId();
	createTableWithArray(BaseConnectedQueryTest.getUrl(),
			getDefaultSplits(tenantId), null, ts - 2);
	initTablesWithArrays(tenantId, null, ts, false);
	String query = "SELECT a_double_array[1] FROM table_with_array  GROUP BY a_double_array[1]";
	Properties props = new Properties(TEST_PROPERTIES);
	props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
			Long.toString(ts + 2)); // Execute at timestamp 2
	Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
	try {
		PreparedStatement statement = conn.prepareStatement(query);
		Double[] doubleArr = new Double[1];
		doubleArr[0] = 40.0;
		conn.createArrayOf("DOUBLE", doubleArr);
		ResultSet rs = statement.executeQuery();
		assertTrue(rs.next());
		doubleArr = new Double[1];
		doubleArr[0] = 36.763;
		Double result =  rs.getDouble(1);
		assertEquals(result, doubleArr[0]);
		assertFalse(rs.next());
	} finally {
		conn.close();
	}
}
 

@SuppressWarnings("unchecked")
@Override
public <S> Map<S, Integer> countBy(final Field<S> byField) throws SQLException {
	final SqlContext context = new SqlContext(this);
	final List<Object> bindings = new ArrayList<Object>();
	final String fromClause = getFromClause(context, bindings);
	initTableNameMap(true);
	final Tuple2<String, List<Object>> wcab = getWhereClauseAndBindings(context);
	bindings.addAll(wcab.b);
	String sql = fromClause + wcab.a;
	sql = "select "+ Util.derefField(byField, context)
			+", count("+ Util.derefField(byField, context) +")"+ sql
			+" group by "+ Util.derefField(byField, context);
	Util.log(sql, null);
	final Tuple2<Connection,Boolean> connInfo = getConnR(getDataSource());
	final Connection conn = connInfo.a;
	final PreparedStatement ps = createPS(sql, conn);
	setBindings(ps, bindings);
	_preExecute(context, conn);
	ps.execute();
	final ResultSet rs = ps.getResultSet();
	final Map<Object, Integer> result = new LinkedHashMap<Object, Integer>();
	while (rs.next()) {
		Object key = null;
		if (byField.TYPE == Long.class) key = rs.getLong(1); else
		if (byField.TYPE == Double.class) key = rs.getDouble(1); else
		key = rs.getObject(1);
		final int value = rs.getInt(2);
		result.put(key, value);
	}
	rs.close();
	ps.close();
	_postExecute(context, conn);
	if (connInfo.b) {
		conn.close();
	}
	return (Map<S, Integer>) result;
}
 

private void extractAndPutDataTo(ExtensionTabData.Builder extensionTab, ExtensionDescriptive descriptive, ResultSet set) throws SQLException {
    boolean booleanValue = set.getBoolean(ExtensionPlayerValueTable.BOOLEAN_VALUE);
    if (!set.wasNull()) {
        extensionTab.putBooleanData(new ExtensionBooleanData(descriptive, booleanValue));
        return;
    }

    double doubleValue = set.getDouble(ExtensionPlayerValueTable.DOUBLE_VALUE);
    if (!set.wasNull()) {
        extensionTab.putDoubleData(new ExtensionDoubleData(descriptive, doubleValue));
        return;
    }

    double percentageValue = set.getDouble(ExtensionPlayerValueTable.PERCENTAGE_VALUE);
    if (!set.wasNull()) {
        extensionTab.putPercentageData(new ExtensionDoubleData(descriptive, percentageValue));
        return;
    }

    long numberValue = set.getLong(ExtensionPlayerValueTable.LONG_VALUE);
    if (!set.wasNull()) {
        FormatType formatType = FormatType.getByName(set.getString(ExtensionProviderTable.FORMAT_TYPE)).orElse(FormatType.NONE);
        extensionTab.putNumberData(new ExtensionNumberData(descriptive, formatType, numberValue));
        return;
    }

    String stringValue = set.getString(ExtensionPlayerValueTable.STRING_VALUE);
    if (stringValue != null) {
        boolean isPlayerName = set.getBoolean("is_player_name");
        extensionTab.putStringData(new ExtensionStringData(descriptive, isPlayerName, stringValue));
    }
}
 

private double executeValues(String functionName) throws SQLException {
	Statement stmt = createStatement();
	ResultSet rs = stmt.executeQuery("values " + functionName + "()");
	double rValue = 0.0;
	while (rs.next()) {
		rValue = rs.getDouble(1);
	}
	rs.close();
	stmt.close();
	return rValue;
}
 

@Test
public void testResultSetWithMaxRows() throws Exception {
    String sql = "select LSTG_FORMAT_NAME, sum(price) as GMV, count(1) as TRANS_CNT from test_kylin_fact \n"
            + " group by LSTG_FORMAT_NAME ";

    Connection conn = getConnection();
    Statement statement = conn.createStatement();
    statement.setMaxRows(2);

    statement.execute(sql);

    ResultSet rs = statement.getResultSet();

    int count = 0;
    while (rs.next()) {
        count++;
        String lstg = rs.getString(1);
        double gmv = rs.getDouble(2);
        int trans_count = rs.getInt(3);

        System.out.println("Get a line: LSTG_FORMAT_NAME=" + lstg + ", GMV=" + gmv + ", TRANS_CNT=" + trans_count);
    }

    Assert.assertTrue(count == 2);
    statement.close();
    rs.close();
    conn.close();

}
 

private double executeFn(String functionName) throws SQLException {
	Statement stmt = createStatement();
	ResultSet rs = stmt.executeQuery("values {fn " + functionName + "()}");
	double rValue = 0.0;
	while (rs.next()) {
		rValue = rs.getDouble(1);
	}
	rs.close();
	stmt.close();
	return rValue;
}
 

protected <V> V getScalarFromResultSet(ResultSet rs, Class<V> returnType) throws SQLException {
    Object value = null;

    if (Integer.class == returnType) {
        value = rs.getInt(1);
    } else if (Long.class == returnType) {
        value = rs.getLong(1);
    } else if (String.class == returnType) {
        value = rs.getString(1);
    } else if (Boolean.class == returnType) {
        value = rs.getBoolean(1);
    } else if (Double.class == returnType) {
        value = rs.getDouble(1);
    } else if (Date.class == returnType) {
        value = rs.getDate(1);
    } else if (Timestamp.class == returnType) {
        value = rs.getTimestamp(1);
    } else {
        value = rs.getObject(1);
    }

    if (null == value) {
        throw new NullPointerException("Cannot get value from ResultSet of type " + returnType.getName());
    }

    return returnType.cast(value);
}
 

@SuppressWarnings("unchecked")
private static <T>T getVal_BaseType(ResultSet rs, Class<T> cs, String key) {
	
	try {
		Object value = null;
		if (cs.equals(String.class)) {
			value = rs.getString(key);
		} else if (cs.equals(int.class) || cs.equals(Integer.class)) {
			value = rs.getInt(key);
		} else if (cs.equals(long.class) || cs.equals(Long.class)) {
			value = rs.getLong(key);
		} else if (cs.equals(short.class) || cs.equals(Short.class)) {
			value = rs.getShort(key);
		} else if (cs.equals(byte.class) || cs.equals(Byte.class)) {
			value = rs.getByte(key);
		} else if (cs.equals(float.class) || cs.equals(Float.class)) {
			value = rs.getFloat(key);
		} else if (cs.equals(double.class) || cs.equals(Double.class)) {
			value = rs.getDouble(key);
		} else if (cs.equals(boolean.class) || cs.equals(Boolean.class)) {
			value = rs.getBoolean(key);
		} else if (cs.equals(char.class) || cs.equals(Character.class)) {
			value = rs.getString(key).charAt(0);
		} else if (cs.equals(java.sql.Timestamp.class) || cs.equals(java.util.Date.class)) {
			value = rs.getTimestamp(key);
		} else if (cs.equals(java.sql.Date.class)) {
			java.sql.Timestamp date = rs.getTimestamp(key);
			if(date != null){
				value = new java.sql.Date(date.getTime());
			}
		} else {
			value = rs.getObject(key); // 都不匹配，走推荐路线
		}
		return (T)value;
	} catch (SQLException e) {
		throw new FlySQLException("字段(" + key + ")取值出错", e);
	}
}
 

private OrderDataBean getOrderDataFromResultSet(ResultSet rs) throws Exception {
    OrderDataBean orderData = null;

    orderData =
        new OrderDataBean(new Integer(rs.getInt("orderID")), rs.getString("orderType"),
            rs.getString("orderStatus"), rs.getTimestamp("openDate"), rs.getTimestamp("completionDate"), rs
                .getDouble("quantity"), rs.getBigDecimal("price"), rs.getBigDecimal("orderFee"), rs
                .getString("quote_symbol"));
    return orderData;
}
 

private double executeValues(String functionName, double value)
		throws SQLException {
	Statement stmt = createStatement();
	ResultSet rs = stmt.executeQuery("values " + functionName + "(" + value
			+ ")");
	double rValue = 0.0;
	while (rs.next()) {
		rValue = rs.getDouble(1);
	}
	rs.close();
	stmt.close();
	return rValue;
}
 

private double executeFn(String functionName, double value)
		throws SQLException {
	Statement stmt = createStatement();
	ResultSet rs = stmt.executeQuery("values {fn  " + functionName + "("
			+ value + ")}");
	double rValue = 0.0;
	while (rs.next()) {
		rValue = rs.getDouble(1);
	}
	rs.close();
	stmt.close();
	return rValue;
}
 

public Spittle mapRow(ResultSet rs, int rowNum) throws SQLException {
  return new Spittle(
      rs.getLong("id"),
      rs.getString("message"), 
      rs.getDate("created_at"), 
      rs.getDouble("longitude"), 
      rs.getDouble("latitude"));
}
 

@Override
public SpecificActivity fromResultSet(ResultSet resultSet) throws SQLException {
  return new SpecificActivity(resultSet.getDouble(1), resultSet.getString(2));
}
 

/**
 * Get a value from a result set column based on the current value metadata
 *
 * @param iDatabase the database metadata to use
 * @param resultSet         The JDBC result set to read from
 * @param index             The column index (1-based)
 * @return The Hop native data type based on the value metadata
 * @throws HopDatabaseException in case something goes wrong.
 */
@Override
public Object getValueFromResultSet( IDatabase iDatabase, ResultSet resultSet, int index )
  throws HopDatabaseException {
  try {
    Object data = null;

    switch ( getType() ) {
      case IValueMeta.TYPE_BOOLEAN:
        data = Boolean.valueOf( resultSet.getBoolean( index + 1 ) );
        break;
      case IValueMeta.TYPE_NUMBER:
        data = new Double( resultSet.getDouble( index + 1 ) );
        break;
      case IValueMeta.TYPE_BIGNUMBER:
        data = resultSet.getBigDecimal( index + 1 );
        break;
      case IValueMeta.TYPE_INTEGER:
        data = Long.valueOf( resultSet.getLong( index + 1 ) );
        break;
      case IValueMeta.TYPE_STRING:
        if ( isStorageBinaryString() ) {
          data = resultSet.getBytes( index + 1 );
        } else {
          data = resultSet.getString( index + 1 );
        }
        break;
      case IValueMeta.TYPE_BINARY:
        if ( iDatabase.supportsGetBlob() ) {
          Blob blob = resultSet.getBlob( index + 1 );
          if ( blob != null ) {
            data = blob.getBytes( 1L, (int) blob.length() );
          } else {
            data = null;
          }
        } else {
          data = resultSet.getBytes( index + 1 );
        }
        break;

      case IValueMeta.TYPE_DATE:
        if ( getPrecision() != 1 && iDatabase.supportsTimeStampToDateConversion() ) {
          data = resultSet.getTimestamp( index + 1 );
          break; // Timestamp extends java.util.Date
        } else if ( iDatabase.isNetezzaVariant() ) {
          // PDI-10877 workaround for IBM netezza jdbc 'special' implementation
          data = getNetezzaDateValueWorkaround( iDatabase, resultSet, index + 1 );
          break;
        } else {
          data = resultSet.getDate( index + 1 );
          break;
        }
      default:
        break;
    }
    if ( resultSet.wasNull() ) {
      data = null;
    }
    return data;
  } catch ( SQLException e ) {
    throw new HopDatabaseException( "Unable to get value '" + toStringMeta() + "' from database resultset, index "
      + index, e );
  }

}
 

@Override
public Double getNullableResult(ResultSet rs, int columnIndex)
    throws SQLException {
  return rs.getDouble(columnIndex);
}
 

public static void testNth(String table, String type, Integer nthRow, Integer nthCol, ResultSet[] resultSets) throws SQLException {

        try{
            Connection conn = DriverManager.getConnection("jdbc:default:connection");
            PreparedStatement pstmt = conn.prepareStatement("select * from " + table.toUpperCase());
            ResultSet res = pstmt.executeQuery();

            // Convert result set to Dataframe
            Dataset<Row> resultSetDF = SparkUtils.resultSetToDF(res);
            //Retrieve nthRow of DataFrame
            org.apache.spark.sql.Row[] r = (Row[])resultSetDF.collect();


            //Retrieve nthRow of ResultSet
            int i = 0;
            Boolean equalsTest = false;
            while(res.next() && i<nthRow){
                i++;
            }
            //System.out.println("Type="+type+"nthrow="+nthRow+" nthcol="+nthCol+" rs="+res.getObject(nthCol)+" i="+i+" df="+r[i]);

            // if either null both have to be null
            if (res.getObject(nthCol) == null) {
                equalsTest = r[i].isNullAt(nthCol-1);
            }
            else if(r[i].isNullAt(nthCol-1)) {
                equalsTest = false;
            }
            else {

                // Test nth element of ResultSet
                switch (type.toLowerCase()){
                    case "string":
                            equalsTest = res.getString(nthCol).equals(r[i].getString(nthCol-1));
                            break;
                    case "integer":
                        equalsTest = res.getInt(nthCol) == r[i].getInt(nthCol-1);
                        break;
                    case "boolean":
                        equalsTest = res.getBoolean(nthCol) == r[i].getBoolean(nthCol-1);
                        break;
                    case "double":
                        equalsTest = res.getDouble(nthCol) == (r[i].getDouble(nthCol-1));
                        break;
                    case "timestamp":
                        equalsTest = res.getTimestamp(nthCol).equals(r[i].getTimestamp(nthCol-1));
                        break;
                    default: equalsTest = false;
                        break;
                }
              }

            // Construct Stored Procedure Result
            List<ExecRow> rows = Lists.newArrayList();
            ExecRow row = new ValueRow(1);
            row.setColumn(1, new SQLBoolean(equalsTest));
            rows.add(row);
            IteratorNoPutResultSet resultsToWrap = wrapResults((EmbedConnection) conn, rows, DATAFRAME_NTH_STORED_PROCEDURE_COLUMN_DECSRIPTOR);
            resultSets[0] = new EmbedResultSet40((EmbedConnection)conn, resultsToWrap, false, null, true);
            conn.close();
        }
        catch (Exception e) {
            throw new SQLException(Throwables.getRootCause(e));
        }
    }
 

private OneField parseField(ResultSet rs, int i) throws Exception {
  // Indexing in rs starts at 1.
  final DataType type = this.columnTypeMapping[i];
  switch (type) {
    case SMALLINT:
      return new OneField(DataType.SMALLINT.getOID(),
          rs.getShort(this.columnIndexMapping[i]));

    case INTEGER:
      return new OneField(DataType.INTEGER.getOID(),
          rs.getInt(this.columnIndexMapping[i]));

    case BIGINT:
      return new OneField(DataType.BIGINT.getOID(),
          rs.getLong(this.columnIndexMapping[i]));

    case REAL:
      return new OneField(DataType.REAL.getOID(),
          rs.getFloat(this.columnIndexMapping[i]));

    case FLOAT8:
      return new OneField(DataType.FLOAT8.getOID(),
          rs.getDouble(this.columnIndexMapping[i]));

    case VARCHAR:
      return new OneField(DataType.VARCHAR.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    case BOOLEAN:
      return new OneField(DataType.BOOLEAN.getOID(),
          rs.getBoolean(this.columnIndexMapping[i]));

    case NUMERIC:
      return new OneField(DataType.NUMERIC.getOID(),
          rs.getBigDecimal(this.columnIndexMapping[i]));

    case TIMESTAMP:
      return new OneField(DataType.TIMESTAMP.getOID(),
          rs.getTimestamp(this.columnIndexMapping[i]));

    case DATE:
      return new OneField(DataType.DATE.getOID(),
          rs.getDate(this.columnIndexMapping[i]));

    case TIME:
      return new OneField(DataType.TIME.getOID(),
          rs.getTime(this.columnIndexMapping[i]));

    case CHAR:
      return new OneField(DataType.CHAR.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    case BPCHAR:
      return new OneField(DataType.BPCHAR.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    case BYTEA:
      return new OneField(DataType.BYTEA.getOID(),
          rs.getBytes(this.columnIndexMapping[i]));

    case TEXT:
      return new OneField(DataType.TEXT.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    default:
      throw new Exception("Column type " + type + " is not supported.");
  }
}
 

private OneField parseField(ResultSet rs, int i) throws Exception {
  // Indexing in rs starts at 1.
  final DataType type = this.columnTypeMapping[i];
  switch (type) {
    case SMALLINT:
      return new OneField(DataType.SMALLINT.getOID(),
          rs.getShort(this.columnIndexMapping[i]));

    case INTEGER:
      return new OneField(DataType.INTEGER.getOID(),
          rs.getInt(this.columnIndexMapping[i]));

    case BIGINT:
      return new OneField(DataType.BIGINT.getOID(),
          rs.getLong(this.columnIndexMapping[i]));

    case REAL:
      return new OneField(DataType.REAL.getOID(),
          rs.getFloat(this.columnIndexMapping[i]));

    case FLOAT8:
      return new OneField(DataType.FLOAT8.getOID(),
          rs.getDouble(this.columnIndexMapping[i]));

    case VARCHAR:
      return new OneField(DataType.VARCHAR.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    case BOOLEAN:
      return new OneField(DataType.BOOLEAN.getOID(),
          rs.getBoolean(this.columnIndexMapping[i]));

    case NUMERIC:
      return new OneField(DataType.NUMERIC.getOID(),
          rs.getBigDecimal(this.columnIndexMapping[i]));

    case TIMESTAMP:
      return new OneField(DataType.TIMESTAMP.getOID(),
          rs.getTimestamp(this.columnIndexMapping[i]));

    case DATE:
      return new OneField(DataType.DATE.getOID(),
          rs.getDate(this.columnIndexMapping[i]));

    case TIME:
      return new OneField(DataType.TIME.getOID(),
          rs.getTime(this.columnIndexMapping[i]));

    case CHAR:
      return new OneField(DataType.CHAR.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    case BPCHAR:
      return new OneField(DataType.BPCHAR.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    case BYTEA:
      return new OneField(DataType.BYTEA.getOID(),
          rs.getBytes(this.columnIndexMapping[i]));

    case TEXT:
      return new OneField(DataType.TEXT.getOID(),
          rs.getString(this.columnIndexMapping[i]));

    default:
      throw new Exception("Column type " + type + " is not supported.");
  }
}
 

/**
 * Returns the value from the result set at current row and col index specified
 * @param rs        the result set reference
 * @param colIndex  the result set column index
 * @return          the value extracted from result set
 * @throws SQLException
 */
public double getDouble(ResultSet rs, int colIndex) throws SQLException {
    final double value = rs.getDouble(colIndex);
    return rs.wasNull() ? Double.NaN : value;
}