List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x, Calendar cal) throws SQLException;
java.sql.Date value, using the given Calendar object. From source file:org.callimachusproject.behaviours.SqlDatasourceSupport.java
private void setValue(PreparedStatement insert, int col, Value value, Integer type) throws SQLException { if (value == null) { insert.setNull(col, type);/*from w w w. ja v a2 s . com*/ } else if (value instanceof Literal) { Literal lit = (Literal) value; URI datatype = lit.getDatatype(); if (datatype == null) { insert.setString(col, value.stringValue()); } else if (XMLDatatypeUtil.isCalendarDatatype(datatype)) { GregorianCalendar cal = lit.calendarValue().toGregorianCalendar(); insert.setDate(col, new java.sql.Date(cal.getTimeInMillis()), cal); } else { insert.setString(col, value.stringValue()); } } else { insert.setString(col, value.stringValue()); } }
From source file:org.moqui.impl.entity.EntityJavaUtil.java
public static void setPreparedStatementValue(PreparedStatement ps, int index, Object value, FieldInfo fi, boolean useBinaryTypeForBlob, EntityFacade efi) throws EntityException { try {/*from www. ja v a 2 s . c om*/ // allow setting, and searching for, String values for all types; JDBC driver should handle this okay if (value instanceof CharSequence) { ps.setString(index, value.toString()); } else { switch (fi.typeValue) { case 1: if (value != null) { ps.setString(index, value.toString()); } else { ps.setNull(index, Types.VARCHAR); } break; case 2: if (value != null) { Class valClass = value.getClass(); if (valClass == Timestamp.class) { ps.setTimestamp(index, (Timestamp) value, efi.getCalendarForTzLc()); } else if (valClass == java.sql.Date.class) { ps.setDate(index, (java.sql.Date) value, efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setTimestamp(index, new Timestamp(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date-time (Timestamp) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.TIMESTAMP); } break; case 3: Time tm = (Time) value; // logger.warn("=================== setting time tm=${tm} tm long=${tm.getTime()}, cal=${cal}") if (value != null) { ps.setTime(index, tm, efi.getCalendarForTzLc()); } else { ps.setNull(index, Types.TIME); } break; case 4: if (value != null) { Class valClass = value.getClass(); if (valClass == java.sql.Date.class) { java.sql.Date dt = (java.sql.Date) value; // logger.warn("=================== setting date dt=${dt} dt long=${dt.getTime()}, cal=${cal}") ps.setDate(index, dt, efi.getCalendarForTzLc()); } else if (valClass == Timestamp.class) { ps.setDate(index, new java.sql.Date(((Timestamp) value).getTime()), efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setDate(index, new java.sql.Date(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.DATE); } break; case 5: if (value != null) { ps.setInt(index, ((Number) value).intValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 6: if (value != null) { ps.setLong(index, ((Number) value).longValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 7: if (value != null) { ps.setFloat(index, ((Number) value).floatValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 8: if (value != null) { ps.setDouble(index, ((Number) value).doubleValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 9: if (value != null) { Class valClass = value.getClass(); // most common cases BigDecimal, Double, Float; then allow any Number if (valClass == BigDecimal.class) { ps.setBigDecimal(index, (BigDecimal) value); } else if (valClass == Double.class) { ps.setDouble(index, (Double) value); } else if (valClass == Float.class) { ps.setFloat(index, (Float) value); } else if (value instanceof Number) { ps.setDouble(index, ((Number) value).doubleValue()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for number-decimal (BigDecimal) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.NUMERIC); } break; case 10: if (value != null) { ps.setBoolean(index, (Boolean) value); } else { ps.setNull(index, Types.BOOLEAN); } break; case 11: if (value != null) { try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(os); oos.writeObject(value); oos.close(); byte[] buf = os.toByteArray(); os.close(); ByteArrayInputStream is = new ByteArrayInputStream(buf); ps.setBinaryStream(index, is, buf.length); is.close(); } catch (IOException ex) { throw new EntityException( "Error setting serialized object, for field " + fi.entityName + "." + fi.name, ex); } } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } break; case 12: if (value instanceof byte[]) { ps.setBytes(index, (byte[]) value); /* } else if (value instanceof ArrayList) { ArrayList valueAl = (ArrayList) value; byte[] theBytes = new byte[valueAl.size()]; valueAl.toArray(theBytes); ps.setBytes(index, theBytes); */ } else if (value instanceof ByteBuffer) { ByteBuffer valueBb = (ByteBuffer) value; ps.setBytes(index, valueBb.array()); } else if (value instanceof Blob) { Blob valueBlob = (Blob) value; // calling setBytes instead of setBlob // ps.setBlob(index, (Blob) value) // Blob blb = value ps.setBytes(index, valueBlob.getBytes(1, (int) valueBlob.length())); } else { if (value != null) { throw new IllegalArgumentException("Type not supported for BLOB field: " + value.getClass().getName() + ", for field " + fi.entityName + "." + fi.name); } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } } break; case 13: if (value != null) { ps.setClob(index, (Clob) value); } else { ps.setNull(index, Types.CLOB); } break; case 14: if (value != null) { ps.setTimestamp(index, (Timestamp) value); } else { ps.setNull(index, Types.TIMESTAMP); } break; // TODO: is this the best way to do collections and such? case 15: if (value != null) { ps.setObject(index, value, Types.JAVA_OBJECT); } else { ps.setNull(index, Types.JAVA_OBJECT); } break; } } } catch (SQLException sqle) { throw new EntityException("SQL Exception while setting value [" + value + "](" + (value != null ? value.getClass().getName() : "null") + "), type " + fi.type + ", for field " + fi.entityName + "." + fi.name + ": " + sqle.toString(), sqle); } catch (Exception e) { throw new EntityException( "Error while setting value for field " + fi.entityName + "." + fi.name + ": " + e.toString(), e); } }
From source file:com.jaspersoft.jasperserver.util.JasperJdbcContainer.java
/** * Given a array of jdbc parameters and a PreparedStatment loops through the parameter array * and populates the PreparedStatement// w ww . j a va 2 s . c o m * @param params jdbc parameters extracted from a CachedRowSet * @param pstmt the PreparedStatment to populate * @throws SQLException */ private void insertParameters(Object[] params, PreparedStatement pstmt) throws SQLException { Object[] param = null; for (int i = 0; i < params.length; i++) { if (params[i] instanceof Object[]) { param = (Object[]) params[i]; if (param.length == 2) { if (param[0] == null) { pstmt.setNull(i + 1, ((Integer) param[1]).intValue()); continue; } if (param[0] instanceof java.sql.Date || param[0] instanceof java.sql.Time || param[0] instanceof java.sql.Timestamp) { if (param[1] instanceof java.util.Calendar) { pstmt.setDate(i + 1, (java.sql.Date) param[0], (java.util.Calendar) param[1]); continue; } else { throw new SQLException("Unknown Parameter, expected java.util.Calendar"); } } if (param[0] instanceof Reader) { pstmt.setCharacterStream(i + 1, (Reader) param[0], ((Integer) param[1]).intValue()); continue; } /* * What's left should be setObject(int, Object, scale) */ if (param[1] instanceof Integer) { pstmt.setObject(i + 1, param[0], ((Integer) param[1]).intValue()); continue; } } else if (param.length == 3) { if (param[0] == null) { pstmt.setNull(i + 1, ((Integer) param[1]).intValue(), (String) param[2]); continue; } // need to find and fix inputstreams if (param[0] instanceof java.io.InputStream) { //logger.fine("Found parameter of type input stream"); } //inputstream if /* * no point at looking at the first element now; what's left * must be the setObject() cases. */ if (param[1] instanceof Integer && param[2] instanceof Integer) { pstmt.setObject(i + 1, param[0], ((Integer) param[1]).intValue(), ((Integer) param[2]).intValue()); continue; } throw new SQLException("Unexpected Parameter"); } else { // common case - this catches all SQL92 types pstmt.setObject(i + 1, params[i]); continue; } } else { // Try to get all the params to be set here pstmt.setObject(i + 1, params[i]); //logger.finest("Param" + i+ ": " + params[i]); } } //for //logger.exiting(getClass().getName(), "insertParameters"); }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testDateTimeTimestampWithCalendar() throws SQLException { Statement stat = conn.createStatement(); stat.execute("create table ts(x timestamp primary key)"); stat.execute("create table t(x time primary key)"); stat.execute("create table d(x date)"); Calendar utcCalendar = new GregorianCalendar(new SimpleTimeZone(0, "Z")); TimeZone old = TimeZone.getDefault(); DateTimeUtils.resetCalendar();/*w w w. j a v a 2 s.c o m*/ TimeZone.setDefault(TimeZone.getTimeZone("PST")); try { Timestamp ts1 = Timestamp.valueOf("2010-03-13 18:15:00"); Time t1 = new Time(ts1.getTime()); Date d1 = new Date(ts1.getTime()); // when converted to UTC, this is 03:15, which doesn't actually exist // because of summer time change at that day Timestamp ts2 = Timestamp.valueOf("2010-03-13 19:15:00"); Time t2 = new Time(ts2.getTime()); Date d2 = new Date(ts2.getTime()); PreparedStatement prep; ResultSet rs; prep = conn.prepareStatement("insert into ts values(?)"); prep.setTimestamp(1, ts1, utcCalendar); prep.execute(); prep.setTimestamp(1, ts2, utcCalendar); prep.execute(); prep = conn.prepareStatement("insert into t values(?)"); prep.setTime(1, t1, utcCalendar); prep.execute(); prep.setTime(1, t2, utcCalendar); prep.execute(); prep = conn.prepareStatement("insert into d values(?)"); prep.setDate(1, d1, utcCalendar); prep.execute(); prep.setDate(1, d2, utcCalendar); prep.execute(); rs = stat.executeQuery("select * from ts order by x"); rs.next(); assertEquals("2010-03-14 02:15:00.0", rs.getString(1)); assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp(1, utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString()); assertEquals("2010-03-14 02:15:00.0", rs.getString("x")); assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp("x", utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString()); rs.next(); assertEquals("2010-03-14 03:15:00.0", rs.getString(1)); assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp(1, utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getString("x")); assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp("x", utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString()); rs = stat.executeQuery("select * from t order by x"); rs.next(); assertEquals("02:15:00", rs.getString(1)); assertEquals("18:15:00", rs.getTime(1, utcCalendar).toString()); assertEquals("02:15:00", rs.getTime(1).toString()); assertEquals("02:15:00", rs.getString("x")); assertEquals("18:15:00", rs.getTime("x", utcCalendar).toString()); assertEquals("02:15:00", rs.getTime("x").toString()); rs.next(); assertEquals("03:15:00", rs.getString(1)); assertEquals("19:15:00", rs.getTime(1, utcCalendar).toString()); assertEquals("03:15:00", rs.getTime(1).toString()); assertEquals("03:15:00", rs.getString("x")); assertEquals("19:15:00", rs.getTime("x", utcCalendar).toString()); assertEquals("03:15:00", rs.getTime("x").toString()); rs = stat.executeQuery("select * from d order by x"); rs.next(); assertEquals("2010-03-14", rs.getString(1)); assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate(1).toString()); assertEquals("2010-03-14", rs.getString("x")); assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate("x").toString()); rs.next(); assertEquals("2010-03-14", rs.getString(1)); assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate(1).toString()); assertEquals("2010-03-14", rs.getString("x")); assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate("x").toString()); } finally { TimeZone.setDefault(old); DateTimeUtils.resetCalendar(); } stat.execute("drop table ts"); stat.execute("drop table t"); stat.execute("drop table d"); }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
public void testDatetimeWithCalendar() throws SQLException { trace("test DATETIME with Calendar"); ResultSet rs;/*from www. j a va 2 s . c o m*/ stat = conn.createStatement(); stat.execute("CREATE TABLE test(ID INT PRIMARY KEY, D DATE, T TIME, TS TIMESTAMP)"); PreparedStatement prep = conn.prepareStatement("INSERT INTO test VALUES(?, ?, ?, ?)"); Calendar regular = Calendar.getInstance(); Calendar other = null; // search a locale that has a _different_ raw offset long testTime = java.sql.Date.valueOf("2001-02-03").getTime(); for (String s : TimeZone.getAvailableIDs()) { TimeZone zone = TimeZone.getTimeZone(s); long rawOffsetDiff = regular.getTimeZone().getRawOffset() - zone.getRawOffset(); // must not be the same timezone (not 0 h and not 24 h difference // as for Pacific/Auckland and Etc/GMT+12) if (rawOffsetDiff != 0 && rawOffsetDiff != 1000 * 60 * 60 * 24) { if (regular.getTimeZone().getOffset(testTime) != zone.getOffset(testTime)) { other = Calendar.getInstance(zone); break; } } } trace("regular offset = " + regular.getTimeZone().getRawOffset() + " other = " + other.getTimeZone().getRawOffset()); prep.setInt(1, 0); prep.setDate(2, null, regular); prep.setTime(3, null, regular); prep.setTimestamp(4, null, regular); prep.execute(); prep.setInt(1, 1); prep.setDate(2, null, other); prep.setTime(3, null, other); prep.setTimestamp(4, null, other); prep.execute(); prep.setInt(1, 2); prep.setDate(2, java.sql.Date.valueOf("2001-02-03"), regular); prep.setTime(3, java.sql.Time.valueOf("04:05:06"), regular); prep.setTimestamp(4, Timestamp.valueOf("2007-08-09 10:11:12.131415"), regular); prep.execute(); prep.setInt(1, 3); prep.setDate(2, java.sql.Date.valueOf("2101-02-03"), other); prep.setTime(3, java.sql.Time.valueOf("14:05:06"), other); prep.setTimestamp(4, Timestamp.valueOf("2107-08-09 10:11:12.131415"), other); prep.execute(); prep.setInt(1, 4); prep.setDate(2, java.sql.Date.valueOf("2101-02-03")); prep.setTime(3, java.sql.Time.valueOf("14:05:06")); prep.setTimestamp(4, Timestamp.valueOf("2107-08-09 10:11:12.131415")); prep.execute(); rs = stat.executeQuery("SELECT * FROM test ORDER BY ID"); assertResultSetMeta(rs, 4, new String[] { "ID", "D", "T", "TS" }, new int[] { Types.INTEGER, Types.DATE, Types.TIME, Types.TIMESTAMP }, new int[] { 10, 8, 6, 23 }, new int[] { 0, 0, 0, 10 }); rs.next(); assertEquals(0, rs.getInt(1)); assertTrue(rs.getDate(2, regular) == null && rs.wasNull()); assertTrue(rs.getTime(3, regular) == null && rs.wasNull()); assertTrue(rs.getTimestamp(3, regular) == null && rs.wasNull()); rs.next(); assertEquals(1, rs.getInt(1)); assertTrue(rs.getDate(2, other) == null && rs.wasNull()); assertTrue(rs.getTime(3, other) == null && rs.wasNull()); assertTrue(rs.getTimestamp(3, other) == null && rs.wasNull()); rs.next(); assertEquals(2, rs.getInt(1)); assertEquals("2001-02-03", rs.getDate(2, regular).toString()); assertEquals("04:05:06", rs.getTime(3, regular).toString()); assertFalse(rs.getTime(3, other).toString().equals("04:05:06")); assertEquals("2007-08-09 10:11:12.131415", rs.getTimestamp(4, regular).toString()); assertFalse(rs.getTimestamp(4, other).toString().equals("2007-08-09 10:11:12.131415")); rs.next(); assertEquals(3, rs.getInt("ID")); assertFalse(rs.getTimestamp("TS", regular).toString().equals("2107-08-09 10:11:12.131415")); assertEquals("2107-08-09 10:11:12.131415", rs.getTimestamp("TS", other).toString()); assertFalse(rs.getTime("T", regular).toString().equals("14:05:06")); assertEquals("14:05:06", rs.getTime("T", other).toString()); // checkFalse(rs.getDate(2, regular).toString(), "2101-02-03"); // check(rs.getDate("D", other).toString(), "2101-02-03"); rs.next(); assertEquals(4, rs.getInt("ID")); assertEquals("2107-08-09 10:11:12.131415", rs.getTimestamp("TS").toString()); assertEquals("14:05:06", rs.getTime("T").toString()); assertEquals("2101-02-03", rs.getDate("D").toString()); assertFalse(rs.next()); stat.execute("DROP TABLE test"); }
From source file:org.apache.openaz.xacml.std.pip.engines.jdbc.ConfigurableJDBCResolver.java
@Override public PreparedStatement getPreparedStatement(PIPEngine pipEngine, PIPRequest pipRequest, PIPFinder pipFinder, Connection connection) throws PIPException { /*/* w w w .j av a 2 s . c om*/ * Do we support the request? */ if (!this.isSupported(pipRequest)) { return null; } PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(this.sqlQuery); } catch (SQLException ex) { this.logger.error("SQLException creating PreparedStatement: " + ex.toString(), ex); // TODO: throw the exception or return a null PreparedStatement? return null; } if (this.parameters.size() > 0) { /* * Gather all of the AttributeValues for parameters to the prepared statement. For now, we assume * a single value for each parameter. If there are multiple values we will log an error and return * a null PreparedStatement. TODO: Should the interface change to return a cross-product of * PreparedStatements to deal with multiple values for parameters? If not, should we just take the * first value and use it as the parameter value? */ for (int i = 0; i < this.parameters.size(); i++) { PIPRequest pipRequestParameter = this.parameters.get(i); PIPResponse pipResponse = pipFinder.getMatchingAttributes(pipRequestParameter, null); if (pipResponse.getStatus() == null || pipResponse.getStatus().isOk()) { Collection<Attribute> listAttributes = pipResponse.getAttributes(); if (listAttributes.size() > 0) { if (listAttributes.size() > 1) { this.logger.error("PIPFinder returned more than one Attribute for " + pipRequestParameter.toString()); throw new PIPException("PIPFinder returned more than one Attribute for " + pipRequestParameter.toString()); } Collection<AttributeValue<?>> listAttributeValuesReturned = listAttributes.iterator().next() .getValues(); if (listAttributeValuesReturned.size() > 0) { if (listAttributeValuesReturned.size() > 1) { this.logger.warn("PIPFinder returned more than one AttributeValue for " + pipRequestParameter.toString()); return null; } AttributeValue<?> attributeValue = listAttributeValuesReturned.iterator().next(); Identifier identifierAttributeValueDataType = attributeValue.getDataTypeId(); try { if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_INTEGER)) { preparedStatement.setInt(i + 1, DataTypes.DT_INTEGER.convert(attributeValue.getValue()).intValue()); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_DOUBLE)) { preparedStatement.setDouble(i + 1, DataTypes.DT_DOUBLE.convert(attributeValue.getValue())); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_BOOLEAN)) { preparedStatement.setBoolean(i + 1, DataTypes.DT_BOOLEAN.convert(attributeValue.getValue())); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_DATETIME)) { ISO8601DateTime iso8601DateTime = DataTypes.DT_DATETIME .convert(attributeValue.getValue()); java.sql.Date sqlDate = new java.sql.Date( iso8601DateTime.getCalendar().getTimeInMillis()); preparedStatement.setDate(i + 1, sqlDate, iso8601DateTime.getCalendar()); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_DATE)) { ISO8601Date iso8601Date = DataTypes.DT_DATE.convert(attributeValue.getValue()); java.sql.Date sqlDate = new java.sql.Date( iso8601Date.getCalendar().getTimeInMillis()); preparedStatement.setDate(i + 1, sqlDate, iso8601Date.getCalendar()); } else { preparedStatement.setString(i + 1, DataTypes.DT_STRING.convert(attributeValue.getValue())); } } catch (Exception ex) { this.logger.error("Exception setting parameter " + (i + 1) + " to " + attributeValue.toString() + ": " + ex.toString(), ex); return null; } } else { this.logger.warn( "No AttributeValues returned for parameter " + pipRequestParameter.toString()); return null; } } else { this.logger.warn("No Attributes returned for parameter " + pipRequestParameter.toString()); return null; } } else { this.logger.warn("PIPFinder returned status " + pipResponse.getStatus().toString()); return null; } } } return preparedStatement; }