public void fillStatement(PreparedStatement stmt, List params) throws SQLException {
if (params == null || params.size() == 0) {
return;
}
for (int i = 0; i < params.size(); i++) {
Object obj = params.get(i);
if (obj != null) {
if (obj instanceof java.sql.Date) {
java.sql.Date sqlDate = (java.sql.Date) obj;
stmt.setDate(i + 1, sqlDate);
} else if (obj instanceof java.sql.Time) {
java.sql.Time sqlTime = (java.sql.Time) obj;
stmt.setTime(i + 1, sqlTime);
} else if (obj instanceof java.sql.Timestamp) {
Timestamp datetime = (Timestamp) obj;
stmt.setTimestamp(i + 1, datetime);
} else if (obj instanceof java.util.Date) {
Timestamp datetime = DateTools.toTimestamp((java.util.Date) obj);
stmt.setTimestamp(i + 1, datetime);
} else {
stmt.setObject(i + 1, obj);
}
} else {
stmt.setString(i + 1, null);
}
}
}
/**
* Creates the entity in the database if it does not exist in the database (this is the case if it was constructed using {@link RelationManager#newTimeslot} rather than {@link RelationManager#createTimeslot).
* @since Iteration2
*/
@Override
public Timeslot create() throws de.fu.bakery.orm.java.DatabaseException {
String query =
"INSERT INTO "
+ "\"scetris\".\"Timeslot\""
+ " (\"day\", \"startingTime\", \"timekey\")"
+ " VALUES (?, ?, ?) RETURNING id;";
timekey(true);
try {
java.sql.PreparedStatement stmt =
manager.connectionManager.getConnection().prepareStatement(query);
int i = 1;
stmt.setInt(i++, ref_day);
stmt.setTime(i++, _startingTime);
stmt.setTimestamp(i++, _timekey);
java.sql.ResultSet keys = manager.executeQuery(stmt);
if (keys.next()) {
_id = keys.getInt(1);
} else {
throw new de.fu.bakery.orm.java.DatabaseException("no key was generated. phail.");
}
changed_day = false;
changed_startingTime = false;
} catch (java.sql.SQLException e) {
throw new de.fu.bakery.orm.java.DatabaseException(query, e);
}
exists = true;
return this;
}
private void writeToDatabase(Date time, Value<?> value) {
try {
Parameter<?> parameter = value.getParameter();
if (!parameter.isNumeric()) {
throw new IllegalArgumentException("The value '" + value.toString() + "' is not numeric!");
}
BigDecimal decimalValue = null;
BigInteger integerValue = null;
if (Long.class.isAssignableFrom(parameter.getType())) {
integerValue = BigInteger.valueOf((Long) value.getValue());
} else {
decimalValue = BigDecimal.valueOf((Double) value.getValue());
}
preparedStatement.setTime(1, new Time(time.getTime()));
preparedStatement.setString(2, server);
preparedStatement.setString(3, parameter.getName());
preparedStatement.setString(4, parameter.getUnit());
preparedStatement.setString(5, parameter.getType().getName());
preparedStatement.setString(6, parameter.getDescription());
preparedStatement.setBigDecimal(7, decimalValue);
preparedStatement.setLong(8, integerValue.longValue());
preparedStatement.setString(9, parameter.getLevelOfMeasurement().name());
preparedStatement.execute();
} catch (SQLException e) {
throw new RuntimeException("Could not insert event into event log.", e);
}
}
private void handleNullValue(int i, PreparedStatement pstmt) throws SQLException {
if (BigDecimal.class.equals(dataTypes.get(i))) {
pstmt.setBigDecimal(i + 1, null);
} else if (Boolean.class.equals(dataTypes.get(i))) {
pstmt.setNull(i + 1, Types.BOOLEAN);
} else if (Byte.class.equals(dataTypes.get(i))) {
pstmt.setNull(i + 1, Types.SMALLINT);
} else if (Date.class.equals(dataTypes.get(i))) {
pstmt.setDate(i + 1, null);
} else if (Double.class.equals(dataTypes.get(i))) {
pstmt.setNull(i + 1, Types.DOUBLE);
} else if (Float.class.equals(dataTypes.get(i))) {
pstmt.setNull(i + 1, Types.FLOAT);
} else if (Integer.class.equals(dataTypes.get(i))) {
pstmt.setNull(i + 1, Types.INTEGER);
} else if (Long.class.equals(dataTypes.get(i))) {
pstmt.setNull(i + 1, Types.BIGINT);
} else if (Short.class.equals(dataTypes.get(i))) {
pstmt.setNull(i + 1, Types.SMALLINT);
} else if (String.class.equals(dataTypes.get(i))) {
pstmt.setString(i + 1, null);
} else if (Time.class.equals(dataTypes.get(i))) {
pstmt.setTime(i + 1, null);
} else if (Timestamp.class.equals(dataTypes.get(i))) {
pstmt.setTimestamp(i + 1, null);
} else {
throw new SQLException(
"Data type not supported by SQLContainer: " + parameters.get(i).getClass().toString());
}
}
protected void writeToHBase(Event event) {
try {
Throwable throwable = event.getThrowable();
String exceptionMessage = null;
String exceptionStacktrace = null;
if (throwable != null) {
exceptionMessage = throwable.getMessage();
exceptionStacktrace = getStackTrace(throwable);
}
if (!connection.isClosed()) {
String email = event.getUserEmail() != null ? event.getUserEmail().getAddress() : null;
preparedLogEventStatement.setTime(1, new Time(event.getTime().getTime()));
preparedLogEventStatement.setString(2, event.getComponent());
preparedLogEventStatement.setLong(3, event.getEventId());
preparedLogEventStatement.setString(4, server);
preparedLogEventStatement.setString(5, event.getType().name());
preparedLogEventStatement.setString(6, event.getSeverity().name());
preparedLogEventStatement.setString(7, event.getMessage());
preparedLogEventStatement.setString(8, email);
preparedLogEventStatement.setLong(9, event.getUserId());
preparedLogEventStatement.setString(10, event.getClientHostname());
preparedLogEventStatement.setString(11, exceptionMessage);
preparedLogEventStatement.setString(12, exceptionStacktrace);
preparedLogEventStatement.execute();
connection.commit();
} else {
throw new IllegalStateException("Connection to HBase was closed already!");
}
} catch (SQLException e) {
throw new RuntimeException("Could not insert event into event log.", e);
}
}
// update close time
public Restaurants updateCloseTime(Restaurants restaurant, Time newCloseTime)
throws SQLException {
String updateStar = "UPDATE Restaurants SET CloseTime=? WHERE RestaurantId=?;";
Connection connection = null;
PreparedStatement updateStmt = null;
try {
connection = connectionManager.getConnection();
updateStmt = connection.prepareStatement(updateStar);
updateStmt.setTime(1, newCloseTime);
updateStmt.setString(2, restaurant.getRestaurantId());
updateStmt.executeUpdate();
restaurant.setClose(newCloseTime);
return restaurant;
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
if (connection != null) {
connection.close();
}
if (updateStmt != null) {
updateStmt.close();
}
}
}
public static boolean saveDetalle(Detalle a) {
try {
conn = ds.getConnection();
stmt = conn.prepareStatement("call savedetalle(?,?,?,?,?)");
Date fech = new Date(a.getFecha().getTime());
stmt.setInt(1, a.getFormaDePago().getId());
stmt.setInt(2, a.getUsuario().getIdUsuario());
stmt.setDate(3, fech);
stmt.setDouble(4, a.getTotal());
stmt.setTime(5, new java.sql.Time(a.getHora().toDateTimeToday().getMillis()));
rs = stmt.executeQuery();
while (rs.next()) {
a.setId(rs.getInt(1));
}
stmt = conn.prepareStatement("call saveproductosventa(?,?)");
for (Producto x : a.getProductos()) {
stmt.setInt(1, a.getId());
stmt.setInt(2, x.getIdProducto());
stmt.execute();
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public void setTime(int parameterIndex, java.sql.Time x, Calendar cal) throws SQLException {
checkOpen();
try {
_stmt.setTime(parameterIndex, x, cal);
} catch (SQLException e) {
handleException(e);
}
}
/**
* When updating the database, insert the appropriate datatype into the prepared statment at the
* given variable position.
*/
public void setPreparedStatementValue(PreparedStatement pstmt, Object value, int position)
throws java.sql.SQLException {
if (value == null) {
pstmt.setNull(position, _colDef.getSqlType());
} else {
pstmt.setTime(position, ((Time) value));
}
}
public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {
Profiler profiler = _profilerPoint.start();
try {
_preparedStatement.setTime(parameterIndex, x, cal);
} finally {
profiler.finish();
}
}
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();
}
public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {
String methodCall = "setTime(" + parameterIndex + ", " + x + ", " + cal + ")";
argTraceSet(parameterIndex, "(Time)", x);
try {
realPreparedStatement.setTime(parameterIndex, x, cal);
} catch (SQLException s) {
reportException(methodCall, s);
throw s;
}
reportReturn(methodCall);
}
/**
* Save the Persons instance by storing it in your MySQL instance. This runs a INSERT statement.
*/
public Restaurants create(Restaurants restaurant) throws SQLException {
String insertRestaurant =
"INSERT INTO Restaurants(RestaurantId,RestaurantName,AcceptsCreditCard,WIFI,PriceRange,OpenTime,"
+ "CloseTime,NoiseLevel,Neighborhood,Star,Parking,Street,City,State,ZipCode) "
+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
Connection connection = null;
PreparedStatement insertStmt = null;
try {
connection = connectionManager.getConnection();
insertStmt = connection.prepareStatement(insertRestaurant);
insertStmt.setString(1, restaurant.getRestaurantId());
insertStmt.setString(2, restaurant.getName());
insertStmt.setBoolean(3, restaurant.getAcceptsCreditCard());
insertStmt.setBoolean(4, restaurant.getWIFI());
insertStmt.setInt(5, restaurant.getPriceRange());
insertStmt.setTime(6, new Time(restaurant.getOpen().getTime()));
insertStmt.setTime(7, new Time(restaurant.getClose().getTime()));
insertStmt.setInt(8, restaurant.getNoiseLevel());
insertStmt.setString(9, restaurant.getNeighborhood());
insertStmt.setDouble(10, restaurant.getStar());
insertStmt.setInt(11, restaurant.getParking());
insertStmt.setString(12, restaurant.getStreet());
insertStmt.setString(13, restaurant.getCity());
insertStmt.setString(14, restaurant.getState());
insertStmt.setInt(15, restaurant.getZipCode());
insertStmt.executeUpdate();
return restaurant;
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
if (connection != null) {
connection.close();
}
if (insertStmt != null) {
insertStmt.close();
}
}
}
/**
* Set given value on given PreparedStatement at given index with given SQL type.
*
* @param stmt The PreparedStatement to set value on.
* @param index The index of the value in the PreparedStatement.
* @param value The value to set.
* @param sqlType The SQL type of the value.
*/
public static void setValue(
final PreparedStatement stmt, final int index, final Object value, final int sqlType) {
try {
if (value == null) {
stmt.setNull(index, sqlType);
} else {
// Special processing for BLOB and CLOB types, because they are mapped
// by Castor to java.io.InputStream and java.io.Reader, respectively,
// while JDBC driver expects java.sql.Blob and java.sql.Clob.
switch (sqlType) {
case Types.FLOAT:
case Types.DOUBLE:
stmt.setDouble(index, ((Double) value).doubleValue());
break;
case Types.REAL:
stmt.setFloat(index, ((Float) value).floatValue());
break;
case Types.TIME:
stmt.setTime(index, (Time) value, getCalendar());
break;
case Types.DATE:
stmt.setDate(index, (Date) value);
break;
case Types.TIMESTAMP:
stmt.setTimestamp(index, (Timestamp) value, getCalendar());
break;
case Types.BLOB:
try {
InputStream stream = (InputStream) value;
stmt.setBinaryStream(index, stream, stream.available());
} catch (IOException ex) {
throw new SQLException(ex.toString());
}
break;
case Types.CLOB:
Clob clob = (Clob) value;
stmt.setCharacterStream(
index, clob.getCharacterStream(), (int) Math.min(clob.length(), Integer.MAX_VALUE));
break;
default:
stmt.setObject(index, value, sqlType);
break;
}
}
} catch (SQLException ex) {
LOG.error("Unexpected SQL exception: ", ex);
}
}
private void setSqlValues(PreparedStatement st, String uniqueId, List<String> sqlCols)
throws SQLException {
// parse on / that isn't preceded by escape char _
// (a / that is preceded by _ is part of column value)
String parts[] = uniqueId.split("(?<!_)/", -1);
if (parts.length != names.size()) {
throw new IllegalStateException(
"wrong number of values for primary key: "
+ "id: "
+ uniqueId
+ ", parts: "
+ Arrays.asList(parts));
}
Map<String, String> zip = new TreeMap<String, String>();
for (int i = 0; i < parts.length; i++) {
String columnValue = decodeSlashInData(parts[i]);
zip.put(names.get(i), columnValue);
}
for (int i = 0; i < sqlCols.size(); i++) {
String colName = sqlCols.get(i);
ColumnType typeOfCol = types.get(colName);
String valueOfCol = zip.get(colName);
switch (typeOfCol) {
case INT:
st.setInt(i + 1, Integer.parseInt(valueOfCol));
break;
case STRING:
st.setString(i + 1, valueOfCol);
break;
case TIMESTAMP:
long timestamp = Long.parseLong(valueOfCol);
st.setTimestamp(i + 1, new java.sql.Timestamp(timestamp));
break;
case DATE:
st.setDate(i + 1, java.sql.Date.valueOf(valueOfCol));
break;
case TIME:
st.setTime(i + 1, java.sql.Time.valueOf(valueOfCol));
break;
case LONG:
st.setLong(i + 1, Long.parseLong(valueOfCol));
break;
default:
throw new AssertionError("invalid type: `" + typeOfCol + "'");
}
}
}
@SuppressWarnings("deprecation")
@Test
public void time() throws SQLException {
connectWithJulianDayModeActivated();
Time d1 = new Time(System.currentTimeMillis());
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
prep.setTime(1, d1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1.getHours(), rs.getTime(1).getHours());
assertEquals(d1.getMinutes(), rs.getTime(1).getMinutes());
assertEquals(d1.getSeconds(), rs.getTime(1).getSeconds());
}
static void addEmployee(
String sql, int id, String firstName, String lastName, double salary, int superiorId)
throws SQLException {
PreparedStatement stmt = connHolder.get().prepareStatement(sql);
stmt.setInt(1, id);
stmt.setString(2, firstName);
stmt.setString(3, lastName);
stmt.setDouble(4, salary);
stmt.setDate(5, Constants.date);
stmt.setTime(6, Constants.time);
if (superiorId <= 0) {
stmt.setNull(7, Types.INTEGER);
} else {
stmt.setInt(7, superiorId);
}
stmt.execute();
stmt.close();
}
public Boolean writeTimeRecord() throws Exception {
try {
if (!newConnection()) {
return false;
}
// Current date and time
DateTime dt = new DateTime();
Timestamp _at = new Timestamp(dt.getMillis());
java.sql.Date timerecord_date = new java.sql.Date(dt.getMillis());
java.sql.Time timerecord_time = new java.sql.Time(dt.getMillis());
// PreparedStatement to INSERT values:
// 0: id
// 1: timerecord_date
// 2: timerecord_time,
// 3: worker_id
// 4: timerecord_type
// 5: timerecord_code,
// 6: created_at
// 7: updated_at
// 8: created_by
// 9: updated_by
// 10: source_ip
preparedStatement =
connection.prepareStatement(
"insert into time_records values (default, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?)");
preparedStatement.setDate(1, timerecord_date);
preparedStatement.setTime(2, timerecord_time);
preparedStatement.setInt(3, Global.worker_id);
preparedStatement.setInt(4, Global.timerecord_type_id);
preparedStatement.setInt(5, Global.timerecord_code_id);
preparedStatement.setTimestamp(6, _at);
preparedStatement.setTimestamp(7, _at);
preparedStatement.setInt(8, Global.user_id);
preparedStatement.setInt(9, Global.user_id);
preparedStatement.setString(10, Global.clientIP());
preparedStatement.executeUpdate();
return true;
} catch (Exception e) {
return false;
} finally {
closeDataObjects();
}
}
/**
* Sets the parameter with the given index in a prepared statement.
*
* @param index parameter index
* @param stmt prepared statement
* @param paramType parameter type
* @param value parameter value
* @param isNull indicator if the parameter is null or not
* @throws QueryException query exception
*/
private void setParam(
final int index,
final PreparedStatement stmt,
final byte[] paramType,
final String value,
final boolean isNull)
throws QueryException {
try {
if (eq(BOOL, paramType)) {
if (isNull) stmt.setNull(index, Types.BOOLEAN);
else stmt.setBoolean(index, Boolean.parseBoolean(value));
} else if (eq(DATE, paramType)) {
if (isNull) stmt.setNull(index, Types.DATE);
else stmt.setDate(index, Date.valueOf(value));
} else if (eq(DOUBLE, paramType)) {
if (isNull) stmt.setNull(index, Types.DOUBLE);
else stmt.setDouble(index, Double.parseDouble(value));
} else if (eq(FLOAT, paramType)) {
if (isNull) stmt.setNull(index, Types.FLOAT);
else stmt.setFloat(index, Float.parseFloat(value));
} else if (eq(INT, paramType)) {
if (isNull) stmt.setNull(index, Types.INTEGER);
else stmt.setInt(index, Integer.parseInt(value));
} else if (eq(SHORT, paramType)) {
if (isNull) stmt.setNull(index, Types.SMALLINT);
else stmt.setShort(index, Short.parseShort(value));
} else if (eq(STRING, paramType)) {
if (isNull) stmt.setNull(index, Types.VARCHAR);
else stmt.setString(index, value);
} else if (eq(TIME, paramType)) {
if (isNull) stmt.setNull(index, Types.TIME);
else stmt.setTime(index, Time.valueOf(value));
} else if (eq(TIMESTAMP, paramType)) {
if (isNull) stmt.setNull(index, Types.TIMESTAMP);
else stmt.setTimestamp(index, Timestamp.valueOf(value));
} else {
throw SQLEXC.thrw(input, "unsupported type: " + string(paramType));
}
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
} catch (final IllegalArgumentException ex) {
throw ILLFORMAT.thrw(input, string(paramType));
}
}
public static void CadLembrete(Lembrete lembrete) {
PreparedStatement stmt;
try {
String sql =
("INSERT INTO tablembrete (dataContato,hora,descricao,tabCliente_idcliente) VALUES(?,?,?,?)");
stmt = Conexao.getConnection().prepareStatement(sql);
stmt.setDate(1, (Date) lembrete.getDataLembrete());
stmt.setTime(2, lembrete.getHora());
stmt.setString(3, lembrete.getDescricao());
stmt.setInt(4, lembrete.getCodCliente());
stmt.executeUpdate();
stmt.close();
} catch (SQLException ex) {
Logger.getLogger(ClienteDAO.class.getName()).log(Level.SEVERE, null, ex);
throw new RuntimeException("Erro ao Cadastrar Lembrete: ", ex);
}
}
/**
* Updates the associated data inside the database
*
* @since Iteration2
*/
@Override
public void pushChanges() throws de.fu.bakery.orm.java.DatabaseException {
if (!exists) {
create();
return;
}
String query =
"UPDATE "
+ "\"scetris\".\"Timeslot\""
+ " SET \"day\" = ?"
+ ", \"startingTime\" = ?"
+ ", \"timekey\" = ? "
+ "WHERE \"timekey\" = ? AND id = ?;";
try {
java.sql.PreparedStatement stmt =
manager.connectionManager.getConnection().prepareStatement(query);
int i = 1;
stmt.setInt(i++, ref_day);
stmt.setTime(i++, _startingTime);
java.sql.Timestamp currentTimekey = _timekey;
java.sql.Timestamp newTimekey = new java.sql.Timestamp(System.currentTimeMillis());
stmt.setTimestamp(i++, newTimekey);
stmt.setTimestamp(i++, currentTimekey);
stmt.setInt(i++, this.id());
int rowsUpdated = stmt.executeUpdate();
if (rowsUpdated == 0) {
throw new de.fu.bakery.orm.java.OutdatedRecordException();
}
_timekey = newTimekey;
changed_day = false;
changed_startingTime = false;
} catch (java.sql.SQLException e) {
throw new de.fu.bakery.orm.java.DatabaseException(query, e);
}
}
/**
* Iterate over the given parameters and add them to the given prepared statement. Only a select
* number of datatypes are supported by the JDBC driver.
*
* @param statement The unparameterized statement.
* @param parameters The parameters to be set on the statement.
*/
protected static void loadParameters(PreparedStatement statement, Object[] parameters)
throws SQLException {
statement.clearParameters();
int idx = 1;
for (Object parameter : parameters) {
if (parameter instanceof String) {
statement.setString(idx, (String) parameter);
} else if (parameter instanceof Long) {
statement.setLong(idx, ((Long) parameter).longValue());
} else if (parameter instanceof Integer) {
statement.setInt(idx, ((Integer) parameter).intValue());
} else if (parameter instanceof Short) {
statement.setShort(idx, ((Short) parameter).shortValue());
} else if (parameter instanceof Date) {
statement.setDate(idx, (Date) parameter);
} else if (parameter instanceof Time) {
statement.setTime(idx, (Time) parameter);
} else if (parameter instanceof Timestamp) {
statement.setTimestamp(idx, (Timestamp) parameter);
} else if (parameter instanceof Double) {
statement.setDouble(idx, ((Double) parameter).doubleValue());
} else if (parameter instanceof Float) {
statement.setFloat(idx, ((Float) parameter).floatValue());
} else if (parameter == null) {
throw new SQLException("Attempting to insert null value into SQL query.");
} else {
throw new SQLException(
"Attempting to insert unknown datatype ("
+ parameter.getClass().getName()
+ ") into SQL statement.");
}
idx++;
}
}
public void set(PreparedStatement prep, int parameterIndex) throws SQLException {
prep.setTime(parameterIndex, getTime());
}
private void testDatetimeWithCalendar() throws SQLException {
trace("Test DATETIME with Calendar");
ResultSet rs;
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, java.sql.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, java.sql.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, java.sql.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");
}
public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {
saveParam(parameterIndex, "" + x, "Time");
statement.setTime(parameterIndex, x, cal);
}
/**
* 填充PreparedStatement
*
* @param pstmt
* @param parameterElements
* @return 返回下次填充的起始位置
* @throws SQLException
* @throws ServiceInvocationException
*/
public static int fulfillPreparedStatement(
PreparedStatement pstmt, List<Element> parameterElements, int startIndex)
throws SQLException, ServiceInvocationException {
ParameterMetaData parameterMetaData = pstmt.getParameterMetaData();
int preparedParamCount = parameterMetaData.getParameterCount();
int paramCount = parameterElements.size();
int currentParamIndex = 0;
if (preparedParamCount > 0) {
for (int i = 0; i < paramCount; i++) {
currentParamIndex = i + startIndex;
int type = parameterMetaData.getParameterType(currentParamIndex);
Element element = parameterElements.get(i);
String strValue = null;
NodeList nodeList = element.getChildNodes();
for (int k = 0; k < nodeList.getLength(); k++) {
Node node = nodeList.item(k);
if (node.getNodeType() == Node.TEXT_NODE) {
strValue = ((Text) node).getNodeValue();
}
}
switch (type) {
case java.sql.Types.CHAR:
case java.sql.Types.VARCHAR:
case java.sql.Types.LONGVARCHAR:
// case java.sql.Types.NCHAR:
// case java.sql.Types.NVARCHAR:
// case java.sql.Types.LONGNVARCHAR:
pstmt.setString(currentParamIndex, strValue);
break;
case java.sql.Types.NUMERIC:
case java.sql.Types.DECIMAL:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
if (strValue.indexOf(".") == -1) {
Long theLong = Long.valueOf(strValue);
pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(theLong));
} else {
Double d = Double.valueOf(strValue);
pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(d));
}
}
break;
case java.sql.Types.BOOLEAN:
case java.sql.Types.BIT:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setBoolean(currentParamIndex, Boolean.valueOf(strValue));
}
break;
case java.sql.Types.TINYINT:
case java.sql.Types.SMALLINT:
case java.sql.Types.INTEGER:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setInt(currentParamIndex, Integer.valueOf(strValue));
}
break;
case java.sql.Types.BIGINT:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setLong(currentParamIndex, Long.valueOf(strValue));
}
break;
case java.sql.Types.REAL:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setFloat(currentParamIndex, Float.valueOf(strValue));
}
break;
case java.sql.Types.FLOAT:
case java.sql.Types.DOUBLE:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setDouble(currentParamIndex, Double.valueOf(strValue));
}
break;
case java.sql.Types.BINARY:
case java.sql.Types.VARBINARY:
case java.sql.Types.LONGVARBINARY:
case java.sql.Types.BLOB:
case java.sql.Types.CLOB:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
byte[] content = null;
try {
content = Base64.decodeBase64(strValue.getBytes("UTF-8"));
} catch (Exception e) {
e.printStackTrace();
}
if (content == null) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setBytes(currentParamIndex, content);
}
}
break;
case java.sql.Types.DATE:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
java.sql.Date sqlDate = java.sql.Date.valueOf(strValue);
pstmt.setDate(currentParamIndex, sqlDate);
}
break;
case java.sql.Types.TIME:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
java.sql.Time t = java.sql.Time.valueOf(strValue);
pstmt.setTime(currentParamIndex, t);
}
break;
case java.sql.Types.TIMESTAMP:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
String dt = strValue;
if (dt.indexOf("T") >= 0) { // 如果是“YYYY-MM-DDThh:mm:ss”这种格式，则将T替换成空格
dt = dt.replace("T", " ");
}
java.sql.Timestamp ts = Timestamp.valueOf(dt);
pstmt.setTimestamp(currentParamIndex, ts);
}
break;
}
}
}
return currentParamIndex + 1;
}
/**
* Demonstration of a reported bug.
*
* <p>Because all values were turned into strings with toString before
* PreparedStatement.executeQuery() was called, special values such as NaN were not accepted.
*
* <p>This test can be extended to cover various conversions through JDBC
*/
public void testDoubleNaN() {
double value = 0;
boolean wasEqual = false;
String message = "DB operation completed";
String ddl1 =
"DROP TABLE t1 IF EXISTS;"
+ "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, "
+ "dt DATE DEFAULT CURRENT_DATE, ti TIME DEFAULT CURRENT_TIME, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );";
try {
stmnt.execute(ddl1);
PreparedStatement ps =
connection.prepareStatement(
"INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)");
ps.setString(1, "0.2");
ps.setDouble(2, 0.2);
ps.setLong(3, java.lang.Long.MAX_VALUE);
ps.setInt(4, Integer.MAX_VALUE);
ps.setInt(5, Short.MAX_VALUE);
ps.setInt(6, 0);
ps.setDate(7, new java.sql.Date(System.currentTimeMillis()));
ps.setTime(8, new java.sql.Time(System.currentTimeMillis()));
ps.setTimestamp(9, new java.sql.Timestamp(System.currentTimeMillis()));
ps.execute();
ps.setInt(1, 0);
ps.setDouble(2, java.lang.Double.NaN);
ps.setLong(3, java.lang.Long.MIN_VALUE);
ps.setInt(4, Integer.MIN_VALUE);
ps.setInt(5, Short.MIN_VALUE);
ps.setInt(6, 0);
// allowed conversions
ps.setTimestamp(7, new java.sql.Timestamp(System.currentTimeMillis() + 1));
ps.setTime(8, new java.sql.Time(System.currentTimeMillis() + 1));
ps.setDate(9, new java.sql.Date(System.currentTimeMillis() + 1));
ps.execute();
//
ps.setInt(1, 0);
ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY);
ps.setInt(4, Integer.MIN_VALUE);
// test conversion
// ps.setObject(5, Boolean.TRUE); // no longer converts boolean to int
// ps.setBoolean(5, true);
ps.setObject(5, new Short((short) 2), Types.SMALLINT);
ps.setObject(6, new Integer(2), Types.TINYINT);
// allowed conversions
ps.setObject(7, new java.sql.Date(System.currentTimeMillis() + 2));
ps.setObject(8, new java.sql.Time(System.currentTimeMillis() + 2));
ps.setObject(9, new java.sql.Timestamp(System.currentTimeMillis() + 2));
ps.execute();
ps.setObject(1, new Float(0), Types.INTEGER);
ps.setObject(4, new Float(1), Types.INTEGER);
ps.setDouble(2, java.lang.Double.NEGATIVE_INFINITY);
ps.execute();
ResultSet rs = stmnt.executeQuery("SELECT d, f, l, i, s*2, t FROM t1");
boolean result = rs.next();
value = rs.getDouble(2);
// int smallintValue = rs.getShort(3);
int integerValue = rs.getInt(4);
if (rs.next()) {
value = rs.getDouble(2);
wasEqual = Double.isNaN(value);
integerValue = rs.getInt(4);
// tests for conversion
// getInt on DECIMAL
integerValue = rs.getInt(1);
}
if (rs.next()) {
value = rs.getDouble(2);
wasEqual = wasEqual && value == Double.POSITIVE_INFINITY;
}
if (rs.next()) {
value = rs.getDouble(2);
wasEqual = wasEqual && value == Double.NEGATIVE_INFINITY;
}
rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");
if (rs.next()) {
int max = rs.getInt(1);
System.out.println("Max value for i: " + max);
}
try {
// cause errors
ps.setString(5, "three");
fail("update did not fail");
} catch (SQLException e) {
}
{
stmnt.execute("drop table CDTYPE if exists");
// test for the value MAX(column) in an empty table
stmnt.execute(
"CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))");
rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType");
if (rs.next()) {
int max = rs.getInt(1);
System.out.println("Max value for ID: " + max);
} else {
System.out.println("Max value for ID not returned");
}
stmnt.executeUpdate("INSERT INTO cdType VALUES (10,'Test String');");
stmnt.execute("CALL IDENTITY();");
try {
stmnt.executeUpdate("INSERT INTO cdType VALUES (10,'Test String');");
} catch (SQLException e1) {
stmnt.execute("ROLLBACK");
connection.rollback();
}
}
} catch (SQLException e) {
fail(e.getMessage());
}
System.out.println("testDoubleNaN complete");
// assert new behaviour
assertEquals(true, wasEqual);
}
public void testPreparedStatementTimes() throws Exception {
createTable(
"marktest",
"(intField INT, floatField DOUBLE, timeField TIME, datetimeField DATETIME, stringField VARCHAR(64))");
this.stmt.executeUpdate(
"INSERT INTO marktest VALUES (123456789, 12345.6789, NOW(), NOW(), 'abcdefghijklmnopqrstuvABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@')");
long start = currentTimeMillis();
long blockStart = currentTimeMillis();
long lastBlock = 0;
int numLoops = 100000;
int numPrepares = 100000;
if (versionMeetsMinimum(4, 1)) {
numPrepares = 10000; // we don't need to do so many for
// server-side prep statements...
}
for (int i = 0; i < numPrepares; i++) {
if (i % 1000 == 0) {
long blockEnd = currentTimeMillis();
long totalTime = blockEnd - blockStart;
blockStart = blockEnd;
StringBuffer messageBuf = new StringBuffer();
messageBuf.append(i + " prepares, the last 1000 prepares took " + totalTime + " ms");
if (lastBlock == 0) {
lastBlock = totalTime;
messageBuf.append(".");
} else {
double diff = (double) totalTime / (double) lastBlock;
messageBuf.append(", difference is " + diff + " x");
lastBlock = totalTime;
}
System.out.println(messageBuf.toString());
}
PreparedStatement pStmt =
this.conn.prepareStatement("INSERT INTO test.marktest VALUES (?, ?, ?, ?, ?)");
pStmt.close();
}
double getPrepareStmtAvgMs = (double) (currentTimeMillis() - start) / numPrepares;
// checkTime("Connection.prepareStatement()", getPrepareStmtAvgMs);
PreparedStatement pStmt =
this.conn.prepareStatement("INSERT INTO marktest VALUES (?, ?, ?, ?, ?)");
System.out.println(pStmt.toString());
start = currentTimeMillis();
for (int i = 0; i < numLoops; i++) {
pStmt.setInt(1, 1);
}
System.out.println(pStmt.toString());
double setIntAvgMs = (double) (currentTimeMillis() - start) / numLoops;
checkTime("PreparedStatement.setInt()", setIntAvgMs);
start = currentTimeMillis();
for (int i = 0; i < numLoops; i++) {
pStmt.setDouble(2, 1234567890.1234);
}
double setDoubleAvgMs = (double) (currentTimeMillis() - start) / numLoops;
checkTime("PreparedStatement.setDouble()", setDoubleAvgMs);
start = currentTimeMillis();
Time tm = new Time(start);
for (int i = 0; i < numLoops; i++) {
pStmt.setTime(3, tm);
}
double setTimeAvgMs = (double) (currentTimeMillis() - start) / numLoops;
checkTime("PreparedStatement.setTime()", setTimeAvgMs);
start = currentTimeMillis();
Timestamp ts = new Timestamp(start);
for (int i = 0; i < numLoops; i++) {
pStmt.setTimestamp(4, ts);
}
double setTimestampAvgMs = (double) (currentTimeMillis() - start) / numLoops;
checkTime("PreparedStatement.setTimestamp()", setTimestampAvgMs);
start = currentTimeMillis();
Date dt = new Date(start);
for (int i = 0; i < numLoops; i++) {
pStmt.setDate(4, dt);
}
double setDateAvgMs = (double) (currentTimeMillis() - start) / numLoops;
checkTime("PreparedStatement.setDate()", setDateAvgMs);
start = currentTimeMillis();
for (int i = 0; i < numLoops; i++) {
pStmt.setString(5, "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@");
}
double setStringAvgMs = (double) (currentTimeMillis() - start) / numLoops;
checkTime("PreparedStatement.setString()", setStringAvgMs);
start = currentTimeMillis();
for (int i = 0; i < numLoops; i++) {
pStmt.setObject(5, "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@");
}
double setStringObjAvgMs = (double) (currentTimeMillis() - start) / numLoops;
checkTime("PreparedStatement.setObject() on a string", setStringObjAvgMs);
start = currentTimeMillis();
}
@Override
public void setValue(PreparedStatement st, int startIndex, OffsetTime value) throws SQLException {
OffsetTime normalized = value.withOffsetSameInstant(ZoneOffset.UTC);
st.setTime(startIndex, new Time(normalized.get(ChronoField.MILLI_OF_DAY)), utc());
}
/**
* Set the value for a parameter. The method used is based on the SQL type of the parameter and we
* can handle complex types like arrays and LOBs.
*
* @param ps the prepared statement or callable statement
* @param paramIndex index of the parameter we are setting
* @param sqlType the SQL type of the parameter
* @param typeName the type name of the parameter (optional, only used for SQL NULL and
* SqlTypeValue)
* @param scale the number of digits after the decimal point (for DECIMAL and NUMERIC types)
* @param inValue the value to set (plain value or a SqlTypeValue)
* @throws SQLException if thrown by PreparedStatement methods
*/
private static void setParameterValueInternal(
PreparedStatement ps,
int paramIndex,
int sqlType,
String typeName,
Integer scale,
Object inValue)
throws SQLException {
if (inValue == null) {
if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
boolean useSetObject = false;
try {
useSetObject =
(ps.getConnection().getMetaData().getDatabaseProductName().indexOf("Informix") != -1);
} catch (Throwable ex) {
// logger.debug("Could not check database product name", ex);
}
if (useSetObject) {
ps.setObject(paramIndex, null);
} else {
ps.setNull(paramIndex, Types.NULL);
}
} else if (typeName != null) {
ps.setNull(paramIndex, sqlType, typeName);
} else {
ps.setNull(paramIndex, sqlType);
}
} else { // inValue != null
if (inValue instanceof SqlTypeValue) {
((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName);
} else if (sqlType == Types.VARCHAR || sqlType == -9) { // -9 is Types.NVARCHAR in java 1.6
ps.setString(paramIndex, inValue.toString());
} else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) {
if (inValue instanceof BigDecimal) {
ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
} else if (scale != null) {
ps.setObject(paramIndex, inValue, sqlType, scale);
} else {
ps.setObject(paramIndex, inValue, sqlType);
}
} else if (sqlType == Types.DATE) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Date) {
ps.setDate(paramIndex, (java.sql.Date) inValue);
} else {
ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime()));
}
} else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal);
} else {
ps.setObject(paramIndex, inValue, Types.DATE);
}
} else if (sqlType == Types.TIME) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Time) {
ps.setTime(paramIndex, (java.sql.Time) inValue);
} else {
ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime()));
}
} else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal);
} else {
ps.setObject(paramIndex, inValue, Types.TIME);
}
} else if (sqlType == Types.TIMESTAMP) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Timestamp) {
ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue);
} else {
ps.setTimestamp(
paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
}
} else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
} else {
ps.setObject(paramIndex, inValue, Types.TIMESTAMP);
}
} else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
if (isStringValue(inValue)) {
ps.setString(paramIndex, inValue.toString());
} else if (isDateValue(inValue)) {
ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
} else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()));
} else {
// Fall back to generic setObject call without SQL type specified.
ps.setObject(paramIndex, inValue);
}
} else {
// Fall back to generic setObject call with SQL type specified.
ps.setObject(paramIndex, inValue, sqlType);
}
}
}