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 vote down vote up
@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 vote down vote up
@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 vote down vote up
/**
 * 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 vote down vote up
/**
 * 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 vote down vote up
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 vote down vote up
@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 vote down vote up
/**
 *  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 vote down vote up
/**
 * 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 vote down vote up
@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 vote down vote up
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 vote down vote up
@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 vote down vote up
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 vote down vote up
/**
 * 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 vote down vote up
@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 vote down vote up
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 vote down vote up
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 vote down vote up
@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 vote down vote up
/**
 * 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 vote down vote up
@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 vote down vote up
@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);
    }
}