Java Code Examples for java.sql.ResultSet#getDate()
The following examples show how to use
java.sql.ResultSet#getDate() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: ConvertTimezoneFunctionIT.java From phoenix with Apache License 2.0 | 6 votes |
@Test public void nullInDateParameter() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String timezone_offset_test = generateUniqueName(); String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test + " (k1 INTEGER NOT NULL, dates DATE CONSTRAINT pk PRIMARY KEY (k1))"; conn.createStatement().execute(ddl); String dml = "UPSERT INTO " + timezone_offset_test + " (k1) VALUES (1)"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery( "SELECT k1, dates, CONVERT_TZ(dates, 'UTC', 'America/Adak') FROM " + timezone_offset_test); assertTrue(rs.next()); rs.getDate(3); assertTrue(rs.wasNull()); }
Example 2
Source File: ProductMetricsIT.java From phoenix with Apache License 2.0 | 5 votes |
@Test public void testRoundAggregation() throws Exception { long ts = nextTimestamp(); String tenantId = getOrganizationId(); String query = "SELECT round(date,'hour',1) r,count(1) FROM PRODUCT_METRICS WHERE organization_id=? GROUP BY r"; String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5 Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(url, props); try { initTableValues(tenantId, getSplits(tenantId), ts); PreparedStatement statement = conn.prepareStatement(query); statement.setString(1, tenantId); Date d; int c; ResultSet rs = statement.executeQuery(); assertTrue(rs.next()); d = rs.getDate(1); c = rs.getInt(2); assertEquals(1 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 1 hr assertEquals(2, c); assertTrue(rs.next()); d = rs.getDate(1); c = rs.getInt(2); assertEquals(2 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 2 hr assertEquals(3, c); assertTrue(rs.next()); d = rs.getDate(1); c = rs.getInt(2); assertEquals(4 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 4 hr assertEquals(1, c); assertFalse(rs.next()); } finally { conn.close(); } }
Example 3
Source File: JdbcUtils.java From lams with GNU General Public License v2.0 | 5 votes |
/** * Retrieve a JDBC column value from a ResultSet, using the most appropriate * value type. The returned value should be a detached value object, not having * any ties to the active ResultSet: in particular, it should not be a Blob or * Clob object but rather a byte array or String representation, respectively. * <p>Uses the {@code getObject(index)} method, but includes additional "hacks" * to get around Oracle 10g returning a non-standard object for its TIMESTAMP * datatype and a {@code java.sql.Date} for DATE columns leaving out the * time portion: These columns will explicitly be extracted as standard * {@code java.sql.Timestamp} object. * @param rs is the ResultSet holding the data * @param index is the column index * @return the value object * @throws SQLException if thrown by the JDBC API * @see java.sql.Blob * @see java.sql.Clob * @see java.sql.Timestamp */ public static Object getResultSetValue(ResultSet rs, int index) throws SQLException { Object obj = rs.getObject(index); String className = null; if (obj != null) { className = obj.getClass().getName(); } if (obj instanceof Blob) { Blob blob = (Blob) obj; obj = blob.getBytes(1, (int) blob.length()); } else if (obj instanceof Clob) { Clob clob = (Clob) obj; obj = clob.getSubString(1, (int) clob.length()); } else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) { obj = rs.getTimestamp(index); } else if (className != null && className.startsWith("oracle.sql.DATE")) { String metaDataClassName = rs.getMetaData().getColumnClassName(index); if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) { obj = rs.getTimestamp(index); } else { obj = rs.getDate(index); } } else if (obj instanceof java.sql.Date) { if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) { obj = rs.getTimestamp(index); } } return obj; }
Example 4
Source File: ResultSets.java From sqlhelper with GNU Lesser General Public License v3.0 | 5 votes |
/** * Retrieve a JDBC column value from a ResultSet, using the most appropriate * value type. The returned value should be a detached value object, not having * any ties to the active ResultSet: in particular, it should not be a Blob or * Clob object but rather a byte array or String representation, respectively. * <p>Uses the {@code getObject(index)} method, but includes additional "hacks" * to get around Oracle 10g returning a non-standard object for its TIMESTAMP * datatype and a {@code java.sql.Date} for DATE columns leaving out the * time portion: These columns will explicitly be extracted as standard * {@code java.sql.Timestamp} object. * * @param rs is the ResultSet holding the data * @param index is the column index * @return the value object * @throws SQLException if thrown by the JDBC API * @see java.sql.Blob * @see java.sql.Clob * @see java.sql.Timestamp */ @Nullable public static Object getResultSetValue(ResultSet rs, int index) throws SQLException { Object obj = rs.getObject(index); String className = null; if (obj != null) { className = obj.getClass().getName(); } if (obj instanceof Blob) { Blob blob = (Blob) obj; obj = blob.getBytes(1, (int) blob.length()); } else if (obj instanceof Clob) { Clob clob = (Clob) obj; obj = clob.getSubString(1, (int) clob.length()); } else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) { obj = rs.getTimestamp(index); } else if (className != null && className.startsWith("oracle.sql.DATE")) { String metaDataClassName = rs.getMetaData().getColumnClassName(index); if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) { obj = rs.getTimestamp(index); } else { obj = rs.getDate(index); } } else if (obj instanceof java.sql.Date) { if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) { obj = rs.getTimestamp(index); } } return obj; }
Example 5
Source File: JdbcSpittleRepository.java From Project with Apache License 2.0 | 5 votes |
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")); }
Example 6
Source File: ParameterizedTransactionIT.java From phoenix with Apache License 2.0 | 5 votes |
@Test public void testCurrentDate() throws Exception { String transTableName = generateUniqueName(); String fullTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + transTableName; String selectSql = "SELECT current_date() FROM "+fullTableName; try (Connection conn = DriverManager.getConnection(getUrl())) { conn.createStatement().execute("create table " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions + (tableDDLOptions.length() > 0 ? "," : "") + "TRANSACTIONAL=true"); conn.setAutoCommit(false); ResultSet rs = conn.createStatement().executeQuery(selectSql); assertFalse(rs.next()); String upsert = "UPSERT INTO " + fullTableName + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)"; PreparedStatement stmt = conn.prepareStatement(upsert); // upsert two rows TestUtil.setRowKeyColumns(stmt, 1); stmt.execute(); conn.commit(); rs = conn.createStatement().executeQuery(selectSql); assertTrue(rs.next()); Date date1 = rs.getDate(1); assertFalse(rs.next()); Thread.sleep(1000); rs = conn.createStatement().executeQuery(selectSql); assertTrue(rs.next()); Date date2 = rs.getDate(1); assertFalse(rs.next()); assertTrue("current_date() should change while executing multiple statements", date2.getTime() > date1.getTime()); } }
Example 7
Source File: AbstractCopySQLData.java From CloverETL-Engine with GNU Lesser General Public License v2.1 | 5 votes |
/** * Sets the Jetel attribute of the CopyDate object * * @param resultSet The new Jetel value * @exception SQLException Description of Exception * @since October 7, 2002 */ @Override public void setJetel(ResultSet resultSet) throws SQLException { Date date = resultSet.getDate(fieldSQL); if (resultSet.wasNull()) { ((DateDataField) field).setValue((Object)null); }else{ ((DateDataField) field).setValue(date); } }
Example 8
Source File: JdbcUtils.java From spring-analysis-note with MIT License | 5 votes |
/** * Retrieve a JDBC column value from a ResultSet, using the most appropriate * value type. The returned value should be a detached value object, not having * any ties to the active ResultSet: in particular, it should not be a Blob or * Clob object but rather a byte array or String representation, respectively. * <p>Uses the {@code getObject(index)} method, but includes additional "hacks" * to get around Oracle 10g returning a non-standard object for its TIMESTAMP * datatype and a {@code java.sql.Date} for DATE columns leaving out the * time portion: These columns will explicitly be extracted as standard * {@code java.sql.Timestamp} object. * @param rs is the ResultSet holding the data * @param index is the column index * @return the value object * @throws SQLException if thrown by the JDBC API * @see java.sql.Blob * @see java.sql.Clob * @see java.sql.Timestamp */ @Nullable public static Object getResultSetValue(ResultSet rs, int index) throws SQLException { Object obj = rs.getObject(index); String className = null; if (obj != null) { className = obj.getClass().getName(); } if (obj instanceof Blob) { Blob blob = (Blob) obj; obj = blob.getBytes(1, (int) blob.length()); } else if (obj instanceof Clob) { Clob clob = (Clob) obj; obj = clob.getSubString(1, (int) clob.length()); } else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) { obj = rs.getTimestamp(index); } else if (className != null && className.startsWith("oracle.sql.DATE")) { String metaDataClassName = rs.getMetaData().getColumnClassName(index); if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) { obj = rs.getTimestamp(index); } else { obj = rs.getDate(index); } } else if (obj instanceof java.sql.Date) { if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) { obj = rs.getTimestamp(index); } } return obj; }
Example 9
Source File: DateOnlyTypeHandler.java From mybatis with Apache License 2.0 | 5 votes |
@Override public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException { java.sql.Date sqlDate = rs.getDate(columnIndex); if (sqlDate != null) { return new java.util.Date(sqlDate.getTime()); } return null; }
Example 10
Source File: AbstractDatabaseService.java From abixen-platform with GNU Lesser General Public License v2.1 | 4 votes |
private DataValueDto getValueAsDataSourceValueDateWeb(ResultSet row, String columnName) throws SQLException { Date value = row.getDate(row.findColumn(columnName)); return new DataValueDto<Date>() .setValue(value); }
Example 11
Source File: DateHandler.java From L2jOrg with GNU General Public License v3.0 | 4 votes |
@Override public LocalDate handleColumn(ResultSet resultSet, int column) throws SQLException { var date = resultSet.getDate(column); return nonNull(date) ? date.toLocalDate() : null; }
Example 12
Source File: JdbcResultSet.java From calcite-avatica with Apache License 2.0 | 4 votes |
private static Object getValue(ResultSet resultSet, int type, int j, Calendar calendar) throws SQLException { switch (type) { case Types.BIGINT: final long aLong = resultSet.getLong(j + 1); return aLong == 0 && resultSet.wasNull() ? null : aLong; case Types.INTEGER: final int anInt = resultSet.getInt(j + 1); return anInt == 0 && resultSet.wasNull() ? null : anInt; case Types.SMALLINT: final short aShort = resultSet.getShort(j + 1); return aShort == 0 && resultSet.wasNull() ? null : aShort; case Types.TINYINT: final byte aByte = resultSet.getByte(j + 1); return aByte == 0 && resultSet.wasNull() ? null : aByte; case Types.DOUBLE: case Types.FLOAT: final double aDouble = resultSet.getDouble(j + 1); return aDouble == 0D && resultSet.wasNull() ? null : aDouble; case Types.REAL: final float aFloat = resultSet.getFloat(j + 1); return aFloat == 0D && resultSet.wasNull() ? null : aFloat; case Types.DATE: final Date aDate = resultSet.getDate(j + 1, calendar); return aDate == null ? null : (int) (aDate.getTime() / DateTimeUtils.MILLIS_PER_DAY); case Types.TIME: final Time aTime = resultSet.getTime(j + 1, calendar); return aTime == null ? null : (int) (aTime.getTime() % DateTimeUtils.MILLIS_PER_DAY); case Types.TIMESTAMP: final Timestamp aTimestamp = resultSet.getTimestamp(j + 1, calendar); return aTimestamp == null ? null : aTimestamp.getTime(); case Types.ARRAY: final Array array = resultSet.getArray(j + 1); if (null == array) { return null; } try { // Recursively extracts an Array using its ResultSet-representation return extractUsingResultSet(array, calendar); } catch (UnsupportedOperationException | SQLFeatureNotSupportedException e) { // Not every database might implement Array.getResultSet(). This call // assumes a non-nested array (depends on the db if that's a valid assumption) return extractUsingArray(array, calendar); } case Types.STRUCT: Struct struct = resultSet.getObject(j + 1, Struct.class); Object[] attrs = struct.getAttributes(); List<Object> list = new ArrayList<>(attrs.length); for (Object o : attrs) { list.add(o); } return list; default: return resultSet.getObject(j + 1); } }
Example 13
Source File: ValueMetaBase.java From pentaho-kettle with Apache License 2.0 | 4 votes |
/** * Get a value from a result set column based on the current value metadata * * @param databaseInterface * the database metadata to use * @param resultSet * The JDBC result set to read from * @param index * The column index (1-based) * @return The Kettle native data type based on the value metadata * @throws KettleDatabaseException * in case something goes wrong. */ @Override public Object getValueFromResultSet( DatabaseInterface databaseInterface, ResultSet resultSet, int index ) throws KettleDatabaseException { try { Object data = null; switch ( getType() ) { case ValueMetaInterface.TYPE_BOOLEAN: data = Boolean.valueOf( resultSet.getBoolean( index + 1 ) ); break; case ValueMetaInterface.TYPE_NUMBER: data = new Double( resultSet.getDouble( index + 1 ) ); break; case ValueMetaInterface.TYPE_BIGNUMBER: data = resultSet.getBigDecimal( index + 1 ); break; case ValueMetaInterface.TYPE_INTEGER: data = Long.valueOf( resultSet.getLong( index + 1 ) ); break; case ValueMetaInterface.TYPE_STRING: if ( isStorageBinaryString() ) { data = resultSet.getBytes( index + 1 ); } else { data = resultSet.getString( index + 1 ); } break; case ValueMetaInterface.TYPE_BINARY: if ( databaseInterface.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 ValueMetaInterface.TYPE_DATE: if ( getPrecision() != 1 && databaseInterface.supportsTimeStampToDateConversion() ) { data = resultSet.getTimestamp( index + 1 ); break; // Timestamp extends java.util.Date } else if ( databaseInterface instanceof NetezzaDatabaseMeta ) { // PDI-10877 workaround for IBM netezza jdbc 'special' implementation data = getNetezzaDateValueWorkaround( databaseInterface, 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 KettleDatabaseException( "Unable to get value '" + toStringMeta() + "' from database resultset, index " + index, e ); } }
Example 14
Source File: OnDuplicateKeyIT.java From phoenix with Apache License 2.0 | 4 votes |
@Test public void testNewAndUpdateOnSingleNumericColumnWithOtherColumns() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); String tableName = generateUniqueName(); String ddl = " create table " + tableName + "(k1 varchar, k2 varchar, counter1 varchar, counter2 date, other1 char(3), other2 varchar default 'f', constraint pk primary key (k1,k2))"; conn.createStatement().execute(ddl); createIndex(conn, tableName); String dml = "UPSERT INTO " + tableName + " VALUES('a','b','c',null,'eee') " + "ON DUPLICATE KEY UPDATE counter1 = counter1 || CASE WHEN LENGTH(counter1) < 10 THEN 'SMALL' ELSE 'LARGE' END || k2 || other2 || other1 "; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName); assertTrue(rs.next()); assertEquals("a",rs.getString(1)); assertEquals("b",rs.getString(2)); assertEquals("c",rs.getString(3)); assertEquals(null,rs.getDate(4)); assertEquals("eee",rs.getString(5)); assertEquals("f",rs.getString(6)); assertFalse(rs.next()); conn.createStatement().execute(dml); conn.commit(); rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName); assertTrue(rs.next()); assertEquals("a",rs.getString(1)); assertEquals("b",rs.getString(2)); assertEquals("cSMALLbfeee",rs.getString(3)); assertEquals(null,rs.getDate(4)); assertEquals("eee",rs.getString(5)); assertEquals("f",rs.getString(6)); assertFalse(rs.next()); conn.createStatement().execute(dml); conn.commit(); rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName); assertTrue(rs.next()); assertEquals("a",rs.getString(1)); assertEquals("b",rs.getString(2)); assertEquals("cSMALLbfeeeLARGEbfeee",rs.getString(3)); assertEquals(null,rs.getDate(4)); assertEquals("eee",rs.getString(5)); assertEquals("f",rs.getString(6)); assertFalse(rs.next()); conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a','b','c',null,'eee') " + "ON DUPLICATE KEY UPDATE counter1 = to_char(rand()), counter2 = current_date() + 1"); conn.commit(); rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName); assertTrue(rs.next()); assertEquals("a",rs.getString(1)); assertEquals("b",rs.getString(2)); double d = Double.parseDouble(rs.getString(3)); assertTrue(d >= 0.0 && d <= 1.0); Date date = rs.getDate(4); assertTrue(date.after(new Date(System.currentTimeMillis()))); assertEquals("eee",rs.getString(5)); assertEquals("f",rs.getString(6)); assertFalse(rs.next()); conn.close(); }
Example 15
Source File: JdbcWritableBridge.java From aliyun-maxcompute-data-collectors with Apache License 2.0 | 4 votes |
public static Date readDate(int colNum, ResultSet r) throws SQLException { return r.getDate(colNum); }
Example 16
Source File: RowValueConstructorIT.java From phoenix with Apache License 2.0 | 4 votes |
private void _testQueryMoreFunctionalityUsingAllPkColsInRowValueConstructor(boolean salted) throws Exception, SQLException { String tenantId = getOrganizationId(); Date date = new Date(System.currentTimeMillis()); String tableName; if(salted) { tableName = initSaltedEntityHistoryTableValues(null, tenantId, null, date, null); } else { tableName = initEntityHistoryTableValues(null, tenantId, getDefaultSplits(tenantId), date, null); } Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); String startingOrgId = tenantId; String startingParentId = PARENTID1; Date startingDate = date; String startingEntityHistId = ENTITYHISTID1; PreparedStatement statement = null; statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + tableName + " WHERE (organization_id, parent_id, created_date, entity_history_id) > (?, ?, ?, ?) ORDER BY organization_id, parent_id, created_date, entity_history_id LIMIT 3 "); statement.setString(1, startingOrgId); statement.setString(2, startingParentId); statement.setDate(3, startingDate); statement.setString(4, startingEntityHistId); ResultSet rs = statement.executeQuery(); int count = 0; int i = 1; //this loop should work on rows 2, 3, 4. while(rs.next()) { assertTrue(rs.getString(2).equals(PARENTIDS.get(i))); assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i))); count++; i++; if(count == 3) { startingOrgId = rs.getString(1); startingParentId = rs.getString(2); startingDate = rs.getDate(3); startingEntityHistId = rs.getString(4); } } assertTrue("Number of rows returned: ", count == 3); //We will now use the row 4's pk values for bind variables. statement.setString(1, startingOrgId); statement.setString(2, startingParentId); statement.setDate(3, startingDate); statement.setString(4, startingEntityHistId); rs = statement.executeQuery(); //this loop now should work on rows 5, 6, 7. while(rs.next()) { assertTrue(rs.getString(2).equals(PARENTIDS.get(i))); assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i))); i++; count++; } assertTrue("Number of rows returned: ", count == 6); }
Example 17
Source File: SqlDateTypeHandler.java From mybaties with Apache License 2.0 | 4 votes |
@Override public Date getNullableResult(ResultSet rs, String columnName) throws SQLException { return rs.getDate(columnName); }
Example 18
Source File: TimeHandlingTest.java From spliceengine with GNU Affero General Public License v3.0 | 4 votes |
/** * Check the consistency of a ResultSet column that returns * a TIME value. Can be used for any column of type TIME. * * @param rs ResultSet holding the column, positioned on a row * @param column Column with the TIME value. * @return Returns the Time object obtained from the column. * @throws SQLException */ private Time checkTimeValue(ResultSet rs, int column) throws SQLException { assertEquals(java.sql.Types.TIME, rs.getMetaData().getColumnType(column)); try { rs.getDate(column); fail("ResultSet.getDate() succeeded on TIME column"); } catch (SQLException e) { assertSQLState("22005", e); } Time tv = rs.getTime(column); assertEquals(tv == null, rs.wasNull()); Object ov = rs.getObject(column); assertEquals(ov == null, rs.wasNull()); if (tv == null) { assertNull(ov); return null; } assertTrue(ov instanceof java.sql.Time); assertEquals(tv, ov); // Check the date portion is set to 1970/01/01 assertTime1970(tv); cal.clear(); cal.setTime(tv); // Check the milli-seconds portion is 0 // Derby does not support that precision in TIME assertEquals(0, cal.get(Calendar.MILLISECOND)); long now = System.currentTimeMillis(); Timestamp tsv = rs.getTimestamp(column); long now2 = System.currentTimeMillis(); assertNotNull(tsv); assertFalse(rs.wasNull()); // Check the TIME portion is set to the same as tv assertTimeEqual(tv, tsv); // DERBY-1811, DERBY-889 being fixed could add tests // Check the returned date portion is the current date // using the value from 'now' and 'now2'. Double check // just in case this test runs at midnight. if (!(isDateEqual(now, tsv) || isDateEqual(now2, tsv))) { fail("TIME to java.sql.Timestamp does not contain current date " + tsv); } String sv = rs.getString(column); assertNotNull(sv); assertFalse(rs.wasNull()); // Assert the string converted back into a Time matches the Time returned. assertEquals("ResultSet String converted to java.sql.Time mismatch", tv, getTime19700101(sv, cal)); return tv; }
Example 19
Source File: ProductMetricsTest.java From phoenix with BSD 3-Clause "New" or "Revised" License | 4 votes |
@Test public void testTruncAggregation() throws Exception { long ts = nextTimestamp(); String tenantId = getOrganizationId(); String query = "SELECT trunc(date,'hour'),count(1) FROM PRODUCT_METRICS WHERE organization_id=? GROUP BY trunc(date,'hour')"; String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5 Properties props = new Properties(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(url, props); try { initTableValues(tenantId, getSplits(tenantId), ts); PreparedStatement statement = conn.prepareStatement(query); statement.setString(1, tenantId); Date d; int c; ResultSet rs = statement.executeQuery(); assertTrue(rs.next()); d = rs.getDate(1); c = rs.getInt(2); assertEquals(0, d.getTime()); // Date bucketed into 0 hr assertEquals(1, c); assertTrue(rs.next()); d = rs.getDate(1); c = rs.getInt(2); assertEquals(1 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 1 hr assertEquals(3, c); assertTrue(rs.next()); d = rs.getDate(1); c = rs.getInt(2); assertEquals(2 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 2 hr assertEquals(1, c); assertTrue(rs.next()); d = rs.getDate(1); c = rs.getInt(2); assertEquals(4 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 4 hr assertEquals(1, c); assertFalse(rs.next()); } finally { conn.close(); } }
Example 20
Source File: ProductMetricsIT.java From phoenix with Apache License 2.0 | 4 votes |
@Test public void testSaltedOrderBy() throws Exception { String tablename=generateUniqueName(); String ddl = "create table " + tablename + " (organization_id char(15) not null," + " \"DATE\" date not null," + " feature char(1) not null," + " unique_users integer not null,\n" + " db_utilization decimal(31,10),\n" + " transactions bigint,\n" + " cpu_utilization decimal(31,10),\n" + " response_time bigint,\n" + " io_time bigint,\n" + " region varchar,\n" + " unset_column decimal(31,10)\n" + " CONSTRAINT pk PRIMARY KEY (organization_id, \"DATE\", feature, unique_users)) salt_buckets=3"; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); try(Connection conn = DriverManager.getConnection(getUrl(), props);) { conn.createStatement().execute(ddl); } String tenantId = getOrganizationId(); Date startDate = new Date(System.currentTimeMillis()); initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate); // Add more date data props = PropertiesUtil.deepCopy(TEST_PROPERTIES); try(Connection conn = DriverManager.getConnection(getUrl(), props);) { initDateTableValues(tablename, conn, tenantId, new Date(startDate.getTime() + MILLIS_IN_DAY * 10), 2.0); initDateTableValues(tablename, conn, tenantId, new Date(startDate.getTime() + MILLIS_IN_DAY * 20), 2.0); conn.commit(); } props = PropertiesUtil.deepCopy(TEST_PROPERTIES); try(Connection conn = DriverManager.getConnection(getUrl(), props);){ PreparedStatement statement = conn.prepareStatement("SELECT count(1) FROM "+tablename+" WHERE organization_id = ?"); statement.setString(1, tenantId); ResultSet rs = statement.executeQuery(); assertTrue(rs.next()); assertEquals(18, rs.getLong(1)); statement = conn.prepareStatement("SELECT \"DATE\" FROM "+tablename+" WHERE organization_id = ? order by \"DATE\" desc limit 10"); statement.setString(1, tenantId); rs = statement.executeQuery(); Date date = null; int count = 0; while (rs.next()) { if (date != null) { assertTrue(date.getTime() >= rs.getDate(1).getTime()); } count++; date = rs.getDate(1); } assertEquals(10,count); } }