Java Code Examples for java.sql.PreparedStatement.setTime()

The following are Jave code examples for showing how to use setTime() of the java.sql.PreparedStatement class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
+ Save this method
Example 1
Project: morpheus-core   File: SQL.java   View Source Code Vote up 7 votes
/**
 * Binds arguments to prepared statement
 * @param stmt  the prepared statement reference
 * @return      the same as arg
 * @throws SQLException if binding fails
 */
private PreparedStatement bindArgs(PreparedStatement stmt) throws SQLException {
    for (int i=0; i<args.length; ++i) {
        final Object value = args[i];
        if      (value instanceof Boolean)          stmt.setBoolean(i+1, (Boolean)value);
        else if (value instanceof Short)            stmt.setShort(i+1, (Short)value);
        else if (value instanceof Integer)          stmt.setInt(i+1, (Integer)value);
        else if (value instanceof Float)            stmt.setFloat(i+1, (Float)value);
        else if (value instanceof Long)             stmt.setLong(i+1, (Long)value);
        else if (value instanceof Double)           stmt.setDouble(i+1, (Double)value);
        else if (value instanceof String)           stmt.setString(i+1, (String)value);
        else if (value instanceof java.sql.Date)    stmt.setDate(i+1, (java.sql.Date)value);
        else if (value instanceof Timestamp)        stmt.setTimestamp(i+1, (Timestamp)value);
        else if (value instanceof LocalDate)        stmt.setDate(i + 1, java.sql.Date.valueOf((LocalDate)value));
        else if (value instanceof LocalTime)        stmt.setTime(i+1, Time.valueOf((LocalTime)value));
        else if (value instanceof LocalDateTime)    stmt.setTimestamp(i+1, Timestamp.valueOf((LocalDateTime)value));
        else if (value instanceof ZonedDateTime) {
            final ZonedDateTime zonedDateTime = (ZonedDateTime)value;
            final LocalDateTime dateTime = zonedDateTime.toLocalDateTime();
            stmt.setTimestamp(i+1, Timestamp.valueOf(dateTime));
        } else {
            throw new RuntimeException("Unsupported argument, cannot be bound to SQL statement: " + value);
        }
    }
    return stmt;
}
 
Example 2
Project: the-vigilantes   File: StatementRegressionTest.java   View Source Code Vote up 6 votes
private void checkPreparedStatementForTestBug50348(Connection testConn, Timestamp timestamp, Time time, String expectedTimestamp, String expectedTime)
        throws SQLException {
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?, ?");
    testPstmt.setTimestamp(1, timestamp);
    testPstmt.setTime(2, time);

    this.rs = testPstmt.executeQuery();
    this.rs.next();
    String timestampAsString = new String(this.rs.getBytes(1));
    String timeAsString = new String(this.rs.getBytes(2));
    String alert = expectedTimestamp.equals(timestampAsString) && expectedTime.equals(timeAsString) ? "" : " <-- (!)";
    System.out.printf("[PS] expected: '%s' | '%s'%n", expectedTimestamp, expectedTime);
    System.out.printf("       actual: '%s' | '%s' %s%n", timestampAsString, timeAsString, alert);
    assertEquals(expectedTimestamp, timestampAsString);
    assertEquals(expectedTime, timeAsString);
}
 
Example 3
Project: OpenVertretung   File: StatementRegressionTest.java   View Source Code Vote up 6 votes
private void checkPreparedStatementForTestBug50348(Connection testConn, Timestamp timestamp, Time time, String expectedTimestamp, String expectedTime)
        throws SQLException {
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?, ?");
    testPstmt.setTimestamp(1, timestamp);
    testPstmt.setTime(2, time);

    this.rs = testPstmt.executeQuery();
    this.rs.next();
    String timestampAsString = new String(this.rs.getBytes(1));
    String timeAsString = new String(this.rs.getBytes(2));
    String alert = expectedTimestamp.equals(timestampAsString) && expectedTime.equals(timeAsString) ? "" : " <-- (!)";
    System.out.printf("[PS] expected: '%s' | '%s'%n", expectedTimestamp, expectedTime);
    System.out.printf("       actual: '%s' | '%s' %s%n", timestampAsString, timeAsString, alert);
    assertEquals(expectedTimestamp, timestampAsString);
    assertEquals(expectedTime, timeAsString);
}
 
Example 4
Project: ProyectoPacientes   File: StatementRegressionTest.java   View Source Code Vote up 6 votes
private void checkPreparedStatementForTestBug50348(Connection testConn, Timestamp timestamp, Time time, String expectedTimestamp, String expectedTime)
        throws SQLException {
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?, ?");
    testPstmt.setTimestamp(1, timestamp);
    testPstmt.setTime(2, time);

    this.rs = testPstmt.executeQuery();
    this.rs.next();
    String timestampAsString = new String(this.rs.getBytes(1));
    String timeAsString = new String(this.rs.getBytes(2));
    String alert = expectedTimestamp.equals(timestampAsString) && expectedTime.equals(timeAsString) ? "" : " <-- (!)";
    System.out.printf("[PS] expected: '%s' | '%s'%n", expectedTimestamp, expectedTime);
    System.out.printf("       actual: '%s' | '%s' %s%n", timestampAsString, timeAsString, alert);
    assertEquals(expectedTimestamp, timestampAsString);
    assertEquals(expectedTime, timeAsString);
}
 
Example 5
Project: BibliotecaPS   File: StatementRegressionTest.java   View Source Code Vote up 6 votes
private void checkPreparedStatementForTestBug50348(Connection testConn, Timestamp timestamp, Time time, String expectedTimestamp, String expectedTime)
        throws SQLException {
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?, ?");
    testPstmt.setTimestamp(1, timestamp);
    testPstmt.setTime(2, time);

    this.rs = testPstmt.executeQuery();
    this.rs.next();
    String timestampAsString = new String(this.rs.getBytes(1));
    String timeAsString = new String(this.rs.getBytes(2));
    String alert = expectedTimestamp.equals(timestampAsString) && expectedTime.equals(timeAsString) ? "" : " <-- (!)";
    System.out.printf("[PS] expected: '%s' | '%s'%n", expectedTimestamp, expectedTime);
    System.out.printf("       actual: '%s' | '%s' %s%n", timestampAsString, timeAsString, alert);
    assertEquals(expectedTimestamp, timestampAsString);
    assertEquals(expectedTime, timeAsString);
}
 
Example 6
Project: morpheus-core   File: DbSink.java   View Source Code Vote up 6 votes
@Override
void apply(PreparedStatement stmt, int stmtIndex, DataFrameRow<R, C> row) {
    final R rowKey = row.key();
    try {
        switch (rowKeyType) {
            case BIT:       stmt.setBoolean(stmtIndex, rowKeyMapper.applyAsBoolean(rowKey));             break;
            case BOOLEAN:   stmt.setBoolean(stmtIndex, rowKeyMapper.applyAsBoolean(rowKey));             break;
            case TINYINT:   stmt.setInt(stmtIndex, rowKeyMapper.applyAsInt(rowKey));                     break;
            case SMALLINT:  stmt.setInt(stmtIndex, rowKeyMapper.applyAsInt(rowKey));                     break;
            case FLOAT:     stmt.setDouble(stmtIndex, rowKeyMapper.applyAsDouble(rowKey));               break;
            case INTEGER:   stmt.setInt(stmtIndex, rowKeyMapper.applyAsInt(rowKey));                     break;
            case BIGINT:    stmt.setLong(stmtIndex, rowKeyMapper.applyAsLong(rowKey));                   break;
            case DOUBLE:    stmt.setDouble(stmtIndex, rowKeyMapper.applyAsDouble(rowKey));               break;
            case DECIMAL:   stmt.setDouble(stmtIndex, rowKeyMapper.applyAsDouble(rowKey));               break;
            case VARCHAR:   stmt.setString(stmtIndex, (String)rowKeyMapper.apply(rowKey));          break;
            case DATE:      stmt.setDate(stmtIndex, (Date)rowKeyMapper.apply(rowKey));              break;
            case TIME:      stmt.setTime(stmtIndex, (Time)rowKeyMapper.apply(rowKey));              break;
            case DATETIME:  stmt.setTimestamp(stmtIndex, (Timestamp)rowKeyMapper.apply(rowKey));    break;
            default:    throw new IllegalStateException("Unsupported column type:" + rowKeyType);
        }
    } catch (Exception ex) {
        throw new DataFrameException("Failed to apply row key to SQL statement at " + rowKey, ex);
    }
}
 
Example 7
Project: lams   File: TimeTypeDescriptor.java   View Source Code Vote up 5 votes
@Override
public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
	return new BasicBinder<X>( javaTypeDescriptor, this ) {
		@Override
		protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
			st.setTime( index, javaTypeDescriptor.unwrap( value, Time.class, options ) );
		}
	};
}
 
Example 8
Project: aliyun-maxcompute-data-collectors   File: JdbcWritableBridge.java   View Source Code Vote up 5 votes
public static void writeTime(Time val, int paramIdx, int sqlType,
    PreparedStatement s) throws SQLException {
  if (null == val) {
    s.setNull(paramIdx, sqlType);
  } else {
    s.setTime(paramIdx, val);
  }
}
 
Example 9
Project: morpheus-core   File: DbSink.java   View Source Code Vote up 5 votes
@Override()
void apply(PreparedStatement stmt, int stmtIndex, DataFrameRow<R,C> row) {
    try {
        this.cursor.atRowOrdinal(row.ordinal());
        if (cursor.isNull()) {
            stmt.setNull(stmtIndex, colType.getTypeCode());
        } else {
            switch (colType) {
                case BIT:       stmt.setBoolean(stmtIndex, mapper.applyAsBoolean(cursor));          break;
                case BOOLEAN:   stmt.setBoolean(stmtIndex, mapper.applyAsBoolean(cursor));          break;
                case TINYINT:   stmt.setInt(stmtIndex, mapper.applyAsInt(cursor));                  break;
                case SMALLINT:  stmt.setInt(stmtIndex, mapper.applyAsInt(cursor));                  break;
                case FLOAT:     stmt.setDouble(stmtIndex, mapper.applyAsDouble(cursor));            break;
                case INTEGER:   stmt.setInt(stmtIndex, mapper.applyAsInt(cursor));                  break;
                case BIGINT:    stmt.setLong(stmtIndex, mapper.applyAsLong(cursor));                break;
                case DOUBLE:    stmt.setDouble(stmtIndex, mapper.applyAsDouble(cursor));            break;
                case DECIMAL:   stmt.setDouble(stmtIndex, mapper.applyAsDouble(cursor));            break;
                case VARCHAR:   stmt.setString(stmtIndex, (String)mapper.apply(cursor));            break;
                case DATE:      stmt.setDate(stmtIndex, (Date)mapper.apply(cursor));                break;
                case TIME:      stmt.setTime(stmtIndex, (Time)mapper.apply(cursor));                break;
                case DATETIME:  stmt.setTimestamp(stmtIndex, (Timestamp)mapper.apply(cursor));      break;
                default:    throw new IllegalStateException("Unsupported column type:" + colType);
            }
        }
    } catch (Exception ex) {
        final String coordinates = String.format("(%s, %s)", cursor.rowKey(), cursor.colKey());
        throw new DataFrameException("Failed to apply value to SQL statement at " + coordinates, ex);
    }
}
 
Example 10
Project: rapidminer   File: DatabaseHandler.java   View Source Code Vote up 4 votes
private void applyBatchInsertIntoTable(PreparedStatement statement, Example example, Iterator<AttributeRole> attributes, boolean addAutoGeneratedPrimaryKeys, Attribute genPrimaryKey) throws SQLException {
    LinkedList attributeList = new LinkedList();

    while(attributes.hasNext()) {
        attributeList.add(((AttributeRole)attributes.next()).getAttribute());
    }

    int counter = 1;
    Iterator var8 = attributeList.iterator();

    while(true) {
        Attribute attribute;
        do {
            if(!var8.hasNext()) {
                statement.addBatch();
                return;
            }

            attribute = (Attribute)var8.next();
        } while(addAutoGeneratedPrimaryKeys && attribute == genPrimaryKey);

        double value = example.getValue(attribute);
        if(Double.isNaN(value)) {
            int valueString = this.statementCreator.getSQLTypeForRMValueType(attribute.getValueType()).getDataType();
            statement.setNull(counter, valueString);
        } else if(attribute.isNominal()) {
            String var13 = attribute.getMapping().mapIndex((int)value);
            statement.setString(counter, var13);
        } else if(Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), 9)) {
            if(Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), 11)) {
                statement.setTime(counter, new Time((long)value));
            } else {
                statement.setTimestamp(counter, new Timestamp((long)value));
            }
        } else {
            statement.setDouble(counter, value);
        }

        ++counter;
    }
}
 
Example 11
Project: the-vigilantes   File: StatementRegressionTest.java   View Source Code Vote up 4 votes
/**
 * Tests fix for BUG#5874, timezone correction goes in wrong 'direction' (when useTimezone=true and server timezone differs from client timezone).
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug5874() throws Exception {
    TimeZone defaultTimezone = TimeZone.getDefault();

    try {
        String clientTimezoneName = "America/Los_Angeles";
        String serverTimezoneName = "America/Chicago";

        TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));

        long clientTimezoneOffsetMillis = TimeZone.getDefault().getRawOffset();
        long serverTimezoneOffsetMillis = TimeZone.getTimeZone(serverTimezoneName).getRawOffset();

        long offsetDifference = clientTimezoneOffsetMillis - serverTimezoneOffsetMillis;

        SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");

        long pointInTime = timestampFormat.parse("2004-10-04 09:19:00").getTime();

        Properties props = new Properties();
        props.put("useTimezone", "true");
        props.put("serverTimezone", serverTimezoneName);
        props.put("cacheDefaultTimezone", "false");

        Connection tzConn = getConnectionWithProps(props);
        Statement tzStmt = tzConn.createStatement();
        createTable("testBug5874", "(tstamp DATETIME, t TIME)");

        PreparedStatement tsPstmt = tzConn.prepareStatement("INSERT INTO testBug5874 VALUES (?, ?)");

        tsPstmt.setTimestamp(1, new Timestamp(pointInTime));
        tsPstmt.setTime(2, new Time(pointInTime));
        tsPstmt.executeUpdate();

        this.rs = tzStmt.executeQuery("SELECT * from testBug5874");

        while (this.rs.next()) { // Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types when calling getString()...
            String retrTimestampString = new String(this.rs.getBytes(1));
            Timestamp retrTimestamp = this.rs.getTimestamp(1);

            java.util.Date timestampOnServer = timestampFormat.parse(retrTimestampString);

            long retrievedOffsetForTimestamp = retrTimestamp.getTime() - timestampOnServer.getTime();

            assertEquals("Original timestamp and timestamp retrieved using client timezone are not the same", offsetDifference,
                    retrievedOffsetForTimestamp);

            String retrTimeString = new String(this.rs.getBytes(2));
            Time retrTime = this.rs.getTime(2);

            java.util.Date timeOnServerAsDate = timeFormat.parse(retrTimeString);
            Time timeOnServer = new Time(timeOnServerAsDate.getTime());

            long retrievedOffsetForTime = retrTime.getTime() - timeOnServer.getTime();

            assertEquals("Original time and time retrieved using client timezone are not the same", offsetDifference, retrievedOffsetForTime);
        }

        tzConn.close();
    } finally {
        TimeZone.setDefault(defaultTimezone);
    }
}
 
Example 12
Project: OpenVertretung   File: StatementRegressionTest.java   View Source Code Vote up 4 votes
/**
 * Tests fix for BUG#5874, timezone correction goes in wrong 'direction' (when useTimezone=true and server timezone differs from client timezone).
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug5874() throws Exception {
    TimeZone defaultTimezone = TimeZone.getDefault();

    try {
        String clientTimezoneName = "America/Los_Angeles";
        String serverTimezoneName = "America/Chicago";

        TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));

        long clientTimezoneOffsetMillis = TimeZone.getDefault().getRawOffset();
        long serverTimezoneOffsetMillis = TimeZone.getTimeZone(serverTimezoneName).getRawOffset();

        long offsetDifference = clientTimezoneOffsetMillis - serverTimezoneOffsetMillis;

        SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");

        long pointInTime = timestampFormat.parse("2004-10-04 09:19:00").getTime();

        Properties props = new Properties();
        props.put("useTimezone", "true");
        props.put("serverTimezone", serverTimezoneName);
        props.put("cacheDefaultTimezone", "false");

        Connection tzConn = getConnectionWithProps(props);
        Statement tzStmt = tzConn.createStatement();
        createTable("testBug5874", "(tstamp DATETIME, t TIME)");

        PreparedStatement tsPstmt = tzConn.prepareStatement("INSERT INTO testBug5874 VALUES (?, ?)");

        tsPstmt.setTimestamp(1, new Timestamp(pointInTime));
        tsPstmt.setTime(2, new Time(pointInTime));
        tsPstmt.executeUpdate();

        this.rs = tzStmt.executeQuery("SELECT * from testBug5874");

        while (this.rs.next()) { // Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types when calling getString()...
            String retrTimestampString = new String(this.rs.getBytes(1));
            Timestamp retrTimestamp = this.rs.getTimestamp(1);

            java.util.Date timestampOnServer = timestampFormat.parse(retrTimestampString);

            long retrievedOffsetForTimestamp = retrTimestamp.getTime() - timestampOnServer.getTime();

            assertEquals("Original timestamp and timestamp retrieved using client timezone are not the same", offsetDifference,
                    retrievedOffsetForTimestamp);

            String retrTimeString = new String(this.rs.getBytes(2));
            Time retrTime = this.rs.getTime(2);

            java.util.Date timeOnServerAsDate = timeFormat.parse(retrTimeString);
            Time timeOnServer = new Time(timeOnServerAsDate.getTime());

            long retrievedOffsetForTime = retrTime.getTime() - timeOnServer.getTime();

            assertEquals("Original time and time retrieved using client timezone are not the same", offsetDifference, retrievedOffsetForTime);
        }

        tzConn.close();
    } finally {
        TimeZone.setDefault(defaultTimezone);
    }
}
 
Example 13
Project: ProyectoPacientes   File: StatementRegressionTest.java   View Source Code Vote up 4 votes
/**
 * Tests fix for BUG#5874, timezone correction goes in wrong 'direction' (when useTimezone=true and server timezone differs from client timezone).
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug5874() throws Exception {
    TimeZone defaultTimezone = TimeZone.getDefault();

    try {
        String clientTimezoneName = "America/Los_Angeles";
        String serverTimezoneName = "America/Chicago";

        TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));

        long clientTimezoneOffsetMillis = TimeZone.getDefault().getRawOffset();
        long serverTimezoneOffsetMillis = TimeZone.getTimeZone(serverTimezoneName).getRawOffset();

        long offsetDifference = clientTimezoneOffsetMillis - serverTimezoneOffsetMillis;

        SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");

        long pointInTime = timestampFormat.parse("2004-10-04 09:19:00").getTime();

        Properties props = new Properties();
        props.put("useTimezone", "true");
        props.put("serverTimezone", serverTimezoneName);
        props.put("cacheDefaultTimezone", "false");

        Connection tzConn = getConnectionWithProps(props);
        Statement tzStmt = tzConn.createStatement();
        createTable("testBug5874", "(tstamp DATETIME, t TIME)");

        PreparedStatement tsPstmt = tzConn.prepareStatement("INSERT INTO testBug5874 VALUES (?, ?)");

        tsPstmt.setTimestamp(1, new Timestamp(pointInTime));
        tsPstmt.setTime(2, new Time(pointInTime));
        tsPstmt.executeUpdate();

        this.rs = tzStmt.executeQuery("SELECT * from testBug5874");

        while (this.rs.next()) { // Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types when calling getString()...
            String retrTimestampString = new String(this.rs.getBytes(1));
            Timestamp retrTimestamp = this.rs.getTimestamp(1);

            java.util.Date timestampOnServer = timestampFormat.parse(retrTimestampString);

            long retrievedOffsetForTimestamp = retrTimestamp.getTime() - timestampOnServer.getTime();

            assertEquals("Original timestamp and timestamp retrieved using client timezone are not the same", offsetDifference,
                    retrievedOffsetForTimestamp);

            String retrTimeString = new String(this.rs.getBytes(2));
            Time retrTime = this.rs.getTime(2);

            java.util.Date timeOnServerAsDate = timeFormat.parse(retrTimeString);
            Time timeOnServer = new Time(timeOnServerAsDate.getTime());

            long retrievedOffsetForTime = retrTime.getTime() - timeOnServer.getTime();

            assertEquals("Original time and time retrieved using client timezone are not the same", offsetDifference, retrievedOffsetForTime);
        }

        tzConn.close();
    } finally {
        TimeZone.setDefault(defaultTimezone);
    }
}
 
Example 14
Project: dev-courses   File: TestOdbcTypes.java   View Source Code Vote up 4 votes
public void testTimeComplex() {
    PreparedStatement ps = null;
    ResultSet rs = null;
    Time aTime = Time.valueOf("21:19:27");

    try {
        ps = netConn.prepareStatement(
            "INSERT INTO alltypes(id, t) VALUES(?, ?)");
        ps.setInt(1, 3);
        ps.setTime(2, aTime);
        assertEquals(1, ps.executeUpdate());
        ps.setInt(1, 4);
        assertEquals(1, ps.executeUpdate());
        ps.close();
        netConn.commit();
        ps = netConn.prepareStatement(
            "SELECT * FROM alltypes WHERE t = ?");
        ps.setTime(1, aTime);
        rs = ps.executeQuery();
        assertTrue("Got no rows with t = aTime", rs.next());
        assertEquals(Time.class, rs.getObject("t").getClass());
        assertTrue("Got only one row with t = aTime", rs.next());
        assertEquals(aTime, rs.getTime("t"));
        assertFalse("Got too many rows with t = aTime", rs.next());
    } catch (SQLException se) {
        junit.framework.AssertionFailedError ase
            = new junit.framework.AssertionFailedError(se.getMessage());
        ase.initCause(se);
        throw ase;
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch(Exception e) {
        }
    }
}
 
Example 15
Project: dev-courses   File: TestDateTime.java   View Source Code Vote up 4 votes
public void testBasicDefaultTimeSupport() throws Throwable {

        final String INSERT_TIME =
            "insert into time_test(time_test) values (?)";

        // See OracleTests class why we need to select tablename.*
        final String SELECT_TIME =
            "select time_test.* from time_test where time_test = ?";
        final String DELETE_TIME = "delete from time_test where time_test = ?";
        java.sql.Time     insertTime;
        Connection        connection = super.newConnection();
        PreparedStatement insertStatement;
        int               iUpdateCount = 0;

        insertTime      = new java.sql.Time(3600000);
        insertStatement = connection.prepareStatement(INSERT_TIME);

        insertStatement.setTime(1, insertTime);

        iUpdateCount = insertStatement.executeUpdate();

        insertStatement.close();
        Assert.assertEquals(
            "Exactly one record with time data shoud have been inserted.",
            iUpdateCount, 1);

        // Now select it back to be sure it is there
        PreparedStatement selectStatement = null;
        PreparedStatement deleteStatement = null;
        ResultSet         results         = null;
        java.sql.Time     retrievedTime;
        int               iDeletedCount = 0;
        java.sql.Time     selectTime;

        selectStatement = connection.prepareStatement(SELECT_TIME);
        selectTime      = new java.sql.Time(3600000);

        selectStatement.setTime(1, selectTime);

        results = selectStatement.executeQuery();

        // Get the date from the database
        Assert.assertTrue("The inserted time is not in the database.",
                          results.next());

        retrievedTime = results.getTime(1);

        //
        deleteStatement = connection.prepareStatement(DELETE_TIME);

        deleteStatement.setTime(1, insertTime);

        iDeletedCount = deleteStatement.executeUpdate();

        Assert.assertEquals(
            "Exactly one record with time data shoud have been deleted.",
            iDeletedCount, 1);

        // And now test the date
        Assert.assertNotNull(
            "The inserted time shouldn't be retrieved as null from the database",
            retrievedTime);

        // Ignore milliseconds when comparing dates
        String  selectString    = selectTime.toString();
        String  retrievedString = retrievedTime.toString();
        boolean result          = retrievedString.equals(selectString);

        Assert.assertTrue(
            "The time retrieved from database "
            + DateFormat.getDateTimeInstance().format(retrievedTime)
            + " is not the same as the inserted one "
            + DateFormat.getDateTimeInstance().format(insertTime), result);
    }
 
Example 16
Project: BibliotecaPS   File: StatementRegressionTest.java   View Source Code Vote up 4 votes
/**
 * Tests fix for BUG#5874, timezone correction goes in wrong 'direction' (when useTimezone=true and server timezone differs from client timezone).
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug5874() throws Exception {
    TimeZone defaultTimezone = TimeZone.getDefault();

    try {
        String clientTimezoneName = "America/Los_Angeles";
        String serverTimezoneName = "America/Chicago";

        TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));

        long clientTimezoneOffsetMillis = TimeZone.getDefault().getRawOffset();
        long serverTimezoneOffsetMillis = TimeZone.getTimeZone(serverTimezoneName).getRawOffset();

        long offsetDifference = clientTimezoneOffsetMillis - serverTimezoneOffsetMillis;

        SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");

        long pointInTime = timestampFormat.parse("2004-10-04 09:19:00").getTime();

        Properties props = new Properties();
        props.put("useTimezone", "true");
        props.put("serverTimezone", serverTimezoneName);
        props.put("cacheDefaultTimezone", "false");

        Connection tzConn = getConnectionWithProps(props);
        Statement tzStmt = tzConn.createStatement();
        createTable("testBug5874", "(tstamp DATETIME, t TIME)");

        PreparedStatement tsPstmt = tzConn.prepareStatement("INSERT INTO testBug5874 VALUES (?, ?)");

        tsPstmt.setTimestamp(1, new Timestamp(pointInTime));
        tsPstmt.setTime(2, new Time(pointInTime));
        tsPstmt.executeUpdate();

        this.rs = tzStmt.executeQuery("SELECT * from testBug5874");

        while (this.rs.next()) { // Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types when calling getString()...
            String retrTimestampString = new String(this.rs.getBytes(1));
            Timestamp retrTimestamp = this.rs.getTimestamp(1);

            java.util.Date timestampOnServer = timestampFormat.parse(retrTimestampString);

            long retrievedOffsetForTimestamp = retrTimestamp.getTime() - timestampOnServer.getTime();

            assertEquals("Original timestamp and timestamp retrieved using client timezone are not the same", offsetDifference,
                    retrievedOffsetForTimestamp);

            String retrTimeString = new String(this.rs.getBytes(2));
            Time retrTime = this.rs.getTime(2);

            java.util.Date timeOnServerAsDate = timeFormat.parse(retrTimeString);
            Time timeOnServer = new Time(timeOnServerAsDate.getTime());

            long retrievedOffsetForTime = retrTime.getTime() - timeOnServer.getTime();

            assertEquals("Original time and time retrieved using client timezone are not the same", offsetDifference, retrievedOffsetForTime);
        }

        tzConn.close();
    } finally {
        TimeZone.setDefault(defaultTimezone);
    }
}
 
Example 17
Project: tangyuan2   File: TimeOnlyTypeHandler.java   View Source Code Vote up 4 votes
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
	ps.setTime(i, new Time(parameter.getTime()));
}
 
Example 18
Project: tangyuan2   File: SqlTimeTypeHandler.java   View Source Code Vote up 4 votes
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Time parameter, JdbcType jdbcType) throws SQLException {
	ps.setTime(i, parameter);
}
 
Example 19
Project: calcite-avatica   File: RemoteDriverTest.java   View Source Code Vote up 4 votes
private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception {
  final String sql0 =
      "select cast(? as varchar(20)) as c\n"
          + "from (values (1, 'a'))";
  final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))";

  final Date date = Date.valueOf("2015-04-08");
  final long time = date.getTime();

  PreparedStatement ps;
  ParameterMetaData parameterMetaData;
  ResultSet resultSet;

  ps = connection.prepareStatement(sql0);
  parameterMetaData = ps.getParameterMetaData();
  assertThat(parameterMetaData.getParameterCount(), equalTo(1));
  ps.setDate(1, date);
  resultSet = ps.executeQuery();
  assertThat(resultSet.next(), is(true));
  assertThat(resultSet.getString(1), is("2015-04-08"));

  ps.setTimestamp(1, new Timestamp(time));
  resultSet = ps.executeQuery();
  assertThat(resultSet.next(), is(true));
  assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0"));

  ps.setTime(1, new Time(time));
  resultSet = ps.executeQuery();
  assertThat(resultSet.next(), is(true));
  assertThat(resultSet.getString(1), is("00:00:00"));
  ps.close();

  ps = connection.prepareStatement(sql1);
  parameterMetaData = ps.getParameterMetaData();
  assertThat(parameterMetaData.getParameterCount(), equalTo(1));

  ps.setDate(1, date);
  resultSet = ps.executeQuery();
  assertTrue(resultSet.next());
  assertThat(resultSet.getDate(1),
      equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

  ps.setTimestamp(1, new Timestamp(time));
  resultSet = ps.executeQuery();
  assertTrue(resultSet.next());
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

  ps.setObject(1, new java.util.Date(time));
  resultSet = ps.executeQuery();
  assertTrue(resultSet.next());
  assertThat(resultSet.getDate(1),
      equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

  resultSet.close();
  ps.close();
  connection.close();
}
 
Example 20
Project: sqlify   File: Sqlify.java   View Source Code Vote up 4 votes
private PreparedStatement applyParameterMapToPreparedStatement(
    PreparedStatement preparedStatement, 
    Map<String, Object> parameterMap, 
    List<String> parametersInSqlSorted) {
  try {
    for (int i = 0; i < parametersInSqlSorted.size(); i++) {
      Object value = parameterMap.get(parametersInSqlSorted.get(i));
      int positionInPreparedStatement = i + 1; // jdbc parameters start with 1...
      
      if (value instanceof BigDecimal) {
        preparedStatement.setBigDecimal(positionInPreparedStatement, (BigDecimal) value);
      } else if (value instanceof Boolean) {
        preparedStatement.setBoolean(positionInPreparedStatement, (Boolean) value);
      } else if (value instanceof Date) {
        preparedStatement.setDate(positionInPreparedStatement, (Date) value);
      } else if (value instanceof Double) {
        preparedStatement.setDouble(positionInPreparedStatement, (Double) value);
      } else if (value instanceof Float) {
        preparedStatement.setFloat(positionInPreparedStatement, (Float) value);
      } else if (value instanceof Integer) {
        preparedStatement.setInt(positionInPreparedStatement, (Integer) value);
      } else if (value instanceof Long) {
        preparedStatement.setLong(positionInPreparedStatement, (Long) value);
      } else if (value instanceof Short) {
        preparedStatement.setShort(positionInPreparedStatement, (Short) value);
      } else if (value instanceof String) {
        preparedStatement.setString(positionInPreparedStatement, (String) value);
      } else if (value instanceof Time) {
        preparedStatement.setTime(positionInPreparedStatement, (Time) value);
      } else if (value instanceof Timestamp) {
        preparedStatement.setTimestamp(positionInPreparedStatement, (Timestamp) value);
      } else if (value instanceof URL) {
        preparedStatement.setURL(positionInPreparedStatement, (URL) value);
      } else {
        // Kind of a fallback. If you expect some other behavior feel
        // free to implement it.
        preparedStatement.setObject(positionInPreparedStatement, value);
      }
    }
  } catch (SQLException ex) {
    throw new SqlifyException("Ops. An error occurred.", ex);
  }
  return preparedStatement;
}