List of usage examples for java.sql PreparedStatement setTime
void setTime(int parameterIndex, java.sql.Time x) throws SQLException;
java.sql.Time value. From source file:com.pari.nm.utils.db.InventoryDBHelper.java
public static void updateComputeDetails(IDSPDeviceInfoProvider device, int deviceid) { PreparedStatement ps = null; Connection c = null;//from w w w. j av a 2s. c o m ResultSet rs = null; Map<String, Object> computeMap = device.getComputeAttributes(); logger.debug("Compute map details = " + computeMap.entrySet().toString()); try { String query = "select id,os from nodes_compute where id=" + deviceid; rs = DBHelper.executeQuery(query); if ((rs != null) && rs.next()) { logger.debug("Executing update for compute device id = " + deviceid); c = DBHelper.getConnection(); logger.debug("Statement = " + DBHelperConstants.NODE_COMPUTE_UPDATE); ps = c.prepareStatement(DBHelperConstants.NODE_COMPUTE_UPDATE); ps.setString(1, device.getIpAddress()); ps.setString(2, device.getOsName()); ps.setString(3, device.getDeviceFamily()); ps.setString(4, device.getAvailableMemory()); ps.setString(5, device.getLowVoltageMemory()); ps.setString(6, device.getMemorySpeed()); ps.setString(7, device.getName()); ps.setString(8, device.getNumOfAdaptors()); ps.setString(9, device.getNumOfCores()); ps.setString(10, device.getNumOfCoresEnabled()); ps.setString(11, device.getNumOfCpus()); ps.setString(12, device.getNumOfEthHostIfs()); ps.setString(13, device.getNumOfEthHostIfs()); ps.setString(14, device.getNumOfThreads()); ps.setString(15, device.getOperability()); ps.setString(16, device.getOriginalUuid()); ps.setString(17, device.getPartNumber()); ps.setString(18, device.getRevision()); ps.setString(19, device.getServerId()); ps.setString(20, device.getTotalMemory()); ps.setString(21, device.getFsmProgr()); ps.setTime(22, null); ps.setTime(23, null); ps.setTime(24, null); ps.setString(25, device.getUuid()); ps.setString(26, device.getVid()); ps.setInt(27, deviceid); ps.executeUpdate(); } else { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.NODE_COMPUTE_INSERT); ps.setInt(1, deviceid); ps.setString(2, device.getIpAddress()); ps.setString(3, device.getOsName()); ps.setString(4, device.getDeviceFamily()); ps.setString(5, device.getAvailableMemory()); ps.setString(6, device.getLowVoltageMemory()); ps.setString(7, device.getMemorySpeed()); ps.setString(8, device.getName()); ps.setString(9, device.getNumOfAdaptors()); ps.setString(10, device.getNumOfCores()); ps.setString(11, device.getNumOfCoresEnabled()); ps.setString(12, device.getNumOfCpus()); ps.setString(13, device.getNumOfEthHostIfs()); ps.setString(14, device.getNumOfEthHostIfs()); ps.setString(15, device.getNumOfThreads()); ps.setString(16, device.getOperability()); ps.setString(17, device.getOriginalUuid()); ps.setString(18, device.getPartNumber()); ps.setString(19, device.getRevision()); ps.setString(20, device.getServerId()); ps.setString(21, device.getTotalMemory()); ps.setString(22, device.getFsmProgr()); ps.setTime(23, null); ps.setTime(24, null); ps.setTime(25, null); ps.setString(26, device.getUuid()); ps.setString(27, device.getVid()); ps.executeUpdate(); } } catch (Exception e) { logger.error("Error while updating Compute Details ", e); } finally { try { if (ps != null) { ps.close(); DBHelper.releaseConnection(c); } if (rs != null) { rs.close(); } } catch (Exception ee) { logger.error("Error while closing connections ", ee); } } }
From source file:nl.nn.adapterframework.jdbc.JdbcFacade.java
protected void applyParameters(PreparedStatement statement, ParameterValueList parameters) throws SQLException, SenderException { // statement.clearParameters(); /*//from ww w. j a v a 2 s. co m // getParameterMetaData() is not supported on the WebSphere java.sql.PreparedStatement implementation. int senderParameterCount = parameters.size(); int statementParameterCount = statement.getParameterMetaData().getParameterCount(); if (statementParameterCount<senderParameterCount) { throw new SenderException(getLogPrefix()+"statement has more ["+statementParameterCount+"] parameters defined than sender ["+senderParameterCount+"]"); } */ for (int i = 0; i < parameters.size(); i++) { ParameterValue pv = parameters.getParameterValue(i); String paramType = pv.getDefinition().getType(); Object value = pv.getValue(); // log.debug("applying parameter ["+(i+1)+","+parameters.getParameterValue(i).getDefinition().getName()+"], value["+parameterValue+"]"); if (Parameter.TYPE_DATE.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.DATE); } else { statement.setDate(i + 1, new java.sql.Date(((Date) value).getTime())); } } else if (Parameter.TYPE_DATETIME.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIMESTAMP); } else { statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime())); } } else if (Parameter.TYPE_TIMESTAMP.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIMESTAMP); } else { statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime())); } } else if (Parameter.TYPE_TIME.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIME); } else { statement.setTime(i + 1, new java.sql.Time(((Date) value).getTime())); } } else if (Parameter.TYPE_XMLDATETIME.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIMESTAMP); } else { statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime())); } } else if (Parameter.TYPE_NUMBER.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.NUMERIC); } else { statement.setDouble(i + 1, ((Number) value).doubleValue()); } } else if (Parameter.TYPE_INTEGER.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.INTEGER); } else { statement.setInt(i + 1, (Integer) value); } } else if (Parameter.TYPE_INPUTSTREAM.equals(paramType)) { if (value instanceof FileInputStream) { FileInputStream fis = (FileInputStream) value; long len = 0; try { len = fis.getChannel().size(); } catch (IOException e) { log.warn(getLogPrefix() + "could not determine file size", e); } statement.setBinaryStream(i + 1, fis, (int) len); } else if (value instanceof ByteArrayInputStream) { ByteArrayInputStream bais = (ByteArrayInputStream) value; long len = bais.available(); statement.setBinaryStream(i + 1, bais, (int) len); } else { throw new SenderException(getLogPrefix() + "unknown inputstream [" + value.getClass() + "] for parameter [" + pv.getDefinition().getName() + "]"); } } else if ("string2bytes".equals(paramType)) { statement.setBytes(i + 1, ((String) value).getBytes()); } else if ("bytes".equals(paramType)) { statement.setBytes(i + 1, (byte[]) value); } else { statement.setString(i + 1, (String) value); } } }
From source file:org.apache.cocoon.util.JDBCTypeConversions.java
/** * Set the Statement column so that the results are mapped correctly. * * @param statement the prepared statement * @param position the position of the column * @param value the value of the column// ww w . j a v a 2 s . co m */ public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject) throws Exception { if (value instanceof String) { value = ((String) value).trim(); } if (typeObject == null) { throw new SQLException("Can't set column because the type is unrecognized"); } if (value == null) { /** If the value is null, set the column value null and return **/ statement.setNull(position, typeObject.intValue()); return; } if ("".equals(value)) { switch (typeObject.intValue()) { case Types.CHAR: case Types.CLOB: case Types.VARCHAR: /** If the value is an empty string and the column is a string type, we can continue **/ break; default: /** If the value is an empty string and the column is something else, we treat it as a null value **/ statement.setNull(position, typeObject.intValue()); return; } } File file = null; int length = -1; InputStream asciiStream = null; //System.out.println("========================================================================"); //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue()); switch (typeObject.intValue()) { case Types.CLOB: //System.out.println("CLOB"); Clob clob = null; if (value instanceof Clob) { clob = (Clob) value; } else if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; clob = new ClobHelper(asciiStream, length); } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new ByteArrayInputStream(asciiText.getBytes()); length = asciiText.length(); clob = new ClobHelper(asciiStream, length); } statement.setClob(position, clob); break; case Types.CHAR: // simple large object, e.g. Informix's TEXT //System.out.println("CHAR"); if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes())); length = asciiText.length(); } statement.setAsciiStream(position, asciiStream, length); break; case Types.BIGINT: //System.out.println("BIGINT"); BigDecimal bd = null; if (value instanceof BigDecimal) { bd = (BigDecimal) value; } else if (value instanceof Number) { bd = BigDecimal.valueOf(((Number) value).longValue()); } else { bd = new BigDecimal(value.toString()); } statement.setBigDecimal(position, bd); break; case Types.TINYINT: //System.out.println("TINYINT"); Byte b = null; if (value instanceof Byte) { b = (Byte) value; } else if (value instanceof Number) { b = new Byte(((Number) value).byteValue()); } else { b = new Byte(value.toString()); } statement.setByte(position, b.byteValue()); break; case Types.DATE: //System.out.println("DATE"); Date d = null; if (value instanceof Date) { d = (Date) value; } else if (value instanceof java.util.Date) { d = new Date(((java.util.Date) value).getTime()); } else if (value instanceof Calendar) { d = new Date(((Calendar) value).getTime().getTime()); } else { d = Date.valueOf(value.toString()); } statement.setDate(position, d); break; case Types.DOUBLE: //System.out.println("DOUBLE"); double db; if (value instanceof Number) { db = (((Number) value).doubleValue()); } else { db = Double.parseDouble(value.toString()); } statement.setDouble(position, db); break; case Types.FLOAT: //System.out.println("FLOAT"); float f; if (value instanceof Number) { f = (((Number) value).floatValue()); } else { f = Float.parseFloat(value.toString()); } statement.setFloat(position, f); break; case Types.NUMERIC: //System.out.println("NUMERIC"); long l; if (value instanceof Number) { l = (((Number) value).longValue()); } else { l = Long.parseLong(value.toString()); } statement.setLong(position, l); break; case Types.SMALLINT: //System.out.println("SMALLINT"); Short s = null; if (value instanceof Short) { s = (Short) value; } else if (value instanceof Number) { s = new Short(((Number) value).shortValue()); } else { s = new Short(value.toString()); } statement.setShort(position, s.shortValue()); break; case Types.TIME: //System.out.println("TIME"); Time t = null; if (value instanceof Time) { t = (Time) value; } else if (value instanceof java.util.Date) { t = new Time(((java.util.Date) value).getTime()); } else { t = Time.valueOf(value.toString()); } statement.setTime(position, t); break; case Types.TIMESTAMP: //System.out.println("TIMESTAMP"); Timestamp ts = null; if (value instanceof Time) { ts = (Timestamp) value; } else if (value instanceof java.util.Date) { ts = new Timestamp(((java.util.Date) value).getTime()); } else { ts = Timestamp.valueOf(value.toString()); } statement.setTimestamp(position, ts); break; case Types.ARRAY: //System.out.println("ARRAY"); statement.setArray(position, (Array) value); // no way to convert string to array break; case Types.STRUCT: //System.out.println("STRUCT"); case Types.OTHER: //System.out.println("OTHER"); statement.setObject(position, value); break; case Types.LONGVARBINARY: //System.out.println("LONGVARBINARY"); statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime())); break; case Types.VARCHAR: //System.out.println("VARCHAR"); statement.setString(position, value.toString()); break; case Types.BLOB: //System.out.println("BLOB"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else { Blob blob = null; if (value instanceof Blob) { blob = (Blob) value; } else if (value instanceof File) { file = (File) value; blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof String) { file = new File((String) value); blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof Part) { Part anyFile = (Part) value; blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize()); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); statement.setBlob(position, blob); } break; case Types.VARBINARY: //System.out.println("VARBINARY"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else if (value instanceof Part) { statement.setBinaryStream(position, ((Part) value).getInputStream(), ((Part) value).getSize()); } else { if (value instanceof File) { file = (File) value; } else if (value instanceof String) { file = new File((String) value); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); FileInputStream input = new FileInputStream(file); statement.setBinaryStream(position, input, (int) file.length()); } break; case Types.INTEGER: //System.out.println("INTEGER"); Integer i = null; if (value instanceof Integer) { i = (Integer) value; } else if (value instanceof Number) { i = new Integer(((Number) value).intValue()); } else { i = new Integer(value.toString()); } statement.setInt(position, i.intValue()); break; case Types.BIT: //System.out.println("BIT"); Boolean bo = null; if (value instanceof Boolean) { bo = (Boolean) value; } else if (value instanceof Number) { bo = BooleanUtils.toBooleanObject(((Number) value).intValue() == 1); } else { bo = BooleanUtils.toBooleanObject(value.toString()); } statement.setBoolean(position, bo.booleanValue()); break; default: //System.out.println("default"); throw new SQLException("Impossible exception - invalid type "); } //System.out.println("========================================================================"); }
From source file:org.apache.jmeter.protocol.jdbc.AbstractJDBCTestElement.java
private void setArgument(PreparedStatement pstmt, String argument, int targetSqlType, int index) throws SQLException { switch (targetSqlType) { case Types.INTEGER: pstmt.setInt(index, Integer.parseInt(argument)); break;// w w w . ja va 2 s . c om case Types.DECIMAL: case Types.NUMERIC: pstmt.setBigDecimal(index, new BigDecimal(argument)); break; case Types.DOUBLE: case Types.FLOAT: pstmt.setDouble(index, Double.parseDouble(argument)); break; case Types.CHAR: case Types.LONGVARCHAR: case Types.VARCHAR: pstmt.setString(index, argument); break; case Types.BIT: case Types.BOOLEAN: pstmt.setBoolean(index, Boolean.parseBoolean(argument)); break; case Types.BIGINT: pstmt.setLong(index, Long.parseLong(argument)); break; case Types.DATE: pstmt.setDate(index, Date.valueOf(argument)); break; case Types.REAL: pstmt.setFloat(index, Float.parseFloat(argument)); break; case Types.TINYINT: pstmt.setByte(index, Byte.parseByte(argument)); break; case Types.SMALLINT: pstmt.setShort(index, Short.parseShort(argument)); break; case Types.TIMESTAMP: pstmt.setTimestamp(index, Timestamp.valueOf(argument)); break; case Types.TIME: pstmt.setTime(index, Time.valueOf(argument)); break; case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: pstmt.setBytes(index, argument.getBytes()); break; case Types.NULL: pstmt.setNull(index, targetSqlType); break; default: pstmt.setObject(index, argument, targetSqlType); } }
From source file:org.apache.kylin.rest.service.QueryService.java
/** * @param preparedState/*from ww w . jav a2 s . c om*/ * @param param * @throws SQLException */ private void setParam(PreparedStatement preparedState, int index, PrepareSqlRequest.StateParam param) throws SQLException { boolean isNull = (null == param.getValue()); Class<?> clazz; try { clazz = Class.forName(param.getClassName()); } catch (ClassNotFoundException e) { throw new InternalErrorException(e); } Rep rep = Rep.of(clazz); switch (rep) { case PRIMITIVE_CHAR: case CHARACTER: case STRING: preparedState.setString(index, isNull ? null : String.valueOf(param.getValue())); break; case PRIMITIVE_INT: case INTEGER: preparedState.setInt(index, isNull ? 0 : Integer.valueOf(param.getValue())); break; case PRIMITIVE_SHORT: case SHORT: preparedState.setShort(index, isNull ? 0 : Short.valueOf(param.getValue())); break; case PRIMITIVE_LONG: case LONG: preparedState.setLong(index, isNull ? 0 : Long.valueOf(param.getValue())); break; case PRIMITIVE_FLOAT: case FLOAT: preparedState.setFloat(index, isNull ? 0 : Float.valueOf(param.getValue())); break; case PRIMITIVE_DOUBLE: case DOUBLE: preparedState.setDouble(index, isNull ? 0 : Double.valueOf(param.getValue())); break; case PRIMITIVE_BOOLEAN: case BOOLEAN: preparedState.setBoolean(index, !isNull && Boolean.parseBoolean(param.getValue())); break; case PRIMITIVE_BYTE: case BYTE: preparedState.setByte(index, isNull ? 0 : Byte.valueOf(param.getValue())); break; case JAVA_UTIL_DATE: case JAVA_SQL_DATE: preparedState.setDate(index, isNull ? null : java.sql.Date.valueOf(param.getValue())); break; case JAVA_SQL_TIME: preparedState.setTime(index, isNull ? null : Time.valueOf(param.getValue())); break; case JAVA_SQL_TIMESTAMP: preparedState.setTimestamp(index, isNull ? null : Timestamp.valueOf(param.getValue())); break; default: preparedState.setObject(index, isNull ? null : param.getValue()); } }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Set the given value as a parameter to the statement. *///from w w w . j a v a 2 s . c om public void setTime(PreparedStatement stmnt, int idx, Time val, Calendar cal, Column col) throws SQLException { if (cal == null) stmnt.setTime(idx, val); else stmnt.setTime(idx, val, cal); }
From source file:org.apache.phoenix.end2end.DateTimeIT.java
@Test public void testProjectedTimeTimestampCompare() throws Exception { String tableName = generateUniqueName(); String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times TIME, timestamps TIMESTAMP)"; conn.createStatement().execute(ddl); // Differ by date String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_TIME('2004-02-04 00:10:10')," + "TO_TIMESTAMP('2006-04-12 00:10:10'))"; conn.createStatement().execute(dml); // Differ by time dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 15:10:20'))"; conn.createStatement().execute(dml); // Differ by nanoseconds PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); stmt.setInt(1, 3);/*from w w w . j av a2 s. c om*/ stmt.setTime(2, new Time(1000)); Timestamp ts = new Timestamp(1000); ts.setNanos(100); stmt.setTimestamp(3, ts); stmt.execute(); // Equality dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 00:10:10'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(true, rs.getBoolean(1)); assertFalse(rs.next()); }
From source file:org.apache.phoenix.end2end.DateTimeIT.java
@Test public void testProjectedTimeUnsignedTimestampCompare() throws Exception { String tableName = generateUniqueName(); String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times TIME, timestamps UNSIGNED_TIMESTAMP)"; conn.createStatement().execute(ddl); // Differ by date String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_TIME('2004-02-04 00:10:10')," + "TO_TIMESTAMP('2006-04-12 00:10:10'))"; conn.createStatement().execute(dml); // Differ by time dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 15:10:20'))"; conn.createStatement().execute(dml); // Differ by nanoseconds PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); stmt.setInt(1, 3);//from www .j av a2 s.c o m stmt.setTime(2, new Time(1000)); Timestamp ts = new Timestamp(1000); ts.setNanos(100); stmt.setTimestamp(3, ts); stmt.execute(); // Equality dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 00:10:10'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(true, rs.getBoolean(1)); assertFalse(rs.next()); }
From source file:org.apache.phoenix.end2end.DateTimeIT.java
@Test public void testProjectedUnsignedTimeTimestampCompare() throws Exception { String tableName = generateUniqueName(); String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times UNSIGNED_TIME, timestamps TIMESTAMP)"; conn.createStatement().execute(ddl); // Differ by date String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_TIME('2004-02-04 00:10:10')," + "TO_TIMESTAMP('2006-04-12 00:10:10'))"; conn.createStatement().execute(dml); // Differ by time dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 15:10:20'))"; conn.createStatement().execute(dml); // Differ by nanoseconds PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); stmt.setInt(1, 3);//www . jav a 2 s. c om stmt.setTime(2, new Time(1000)); Timestamp ts = new Timestamp(1000); ts.setNanos(100); stmt.setTimestamp(3, ts); stmt.execute(); // Equality dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 00:10:10'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(true, rs.getBoolean(1)); assertFalse(rs.next()); }
From source file:org.apache.phoenix.end2end.DateTimeIT.java
@Test public void testProjectedUnsignedTimeUnsignedTimestampCompare() throws Exception { String tableName = generateUniqueName(); String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times UNSIGNED_TIME, timestamps UNSIGNED_TIMESTAMP)"; conn.createStatement().execute(ddl); // Differ by date String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_TIME('2004-02-04 00:10:10')," + "TO_TIMESTAMP('2006-04-12 00:10:10'))"; conn.createStatement().execute(dml); // Differ by time dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 15:10:20'))"; conn.createStatement().execute(dml); // Differ by nanoseconds PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); stmt.setInt(1, 3);//from w w w . j a v a2 s .c o m stmt.setTime(2, new Time(1000)); Timestamp ts = new Timestamp(1000); ts.setNanos(100); stmt.setTimestamp(3, ts); stmt.execute(); // Equality dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_TIME('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 00:10:10'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(true, rs.getBoolean(1)); assertFalse(rs.next()); }