下面列出了java.sql.ResultSet#getDouble ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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();
}
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"));
}
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;
}
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 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;
}
@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);
}
}
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);
}
@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();
}
}
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;
}
@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 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;
}
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));
}
}
@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 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.");
}
}
@Override
public Double getNullableResult(ResultSet rs, int columnIndex)
throws SQLException {
return rs.getDouble(columnIndex);
}
@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 );
}
}
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.");
}
}
/**
* 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;
}