List of usage examples for java.sql PreparedStatement setObject
default void setObject(int parameterIndex, Object x, SQLType targetSqlType) throws SQLException
From source file:org.seasar.dbflute.logic.replaceschema.loaddata.impl.DfAbsractDataWriter.java
protected boolean processArray(String tableName, String columnName, String value, PreparedStatement ps, int bindCount, Map<String, DfColumnMeta> columnInfoMap) throws SQLException { if (value == null) { return false; // basically no way }/*from w ww. ja va 2 s. com*/ final DfColumnMeta columnInfo = columnInfoMap.get(columnName); if (columnInfo != null) { if (getBasicProperties().isDatabasePostgreSQL()) { //rsMeta#getColumnTypeName() returns value starts with "_" if //rsMeta#getColumnType() returns Types.ARRAY in PostgreSQL. // e.g. UUID[] -> _uuid final int jdbcDefValue = columnInfo.getJdbcDefValue(); final String dbTypeName = columnInfo.getDbTypeName(); if (jdbcDefValue != Types.ARRAY || !dbTypeName.startsWith("_")) { return false; } value = filterArrayValue(value); ps.setObject(bindCount, value, Types.OTHER); return true; } } // unsupported when meta data is not found return false; }
From source file:architecture.common.adaptor.connector.jdbc.AbstractJdbcConnector.java
protected Object deliver(final String queryString, final List<ParameterMapping> parameterMappings, final Map<String, Object> row) { // log.debug("delivering : 1"); return getJdbcTemplate().update(queryString, new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { for (ParameterMapping mapping : parameterMappings) { JdbcType jdbcType = mapping.getJdbcType(); Object value = row.get(mapping.getProperty()); Object valueToUse = value; if (valueToUse == null && mapping.getJavaType() == Date.class) { valueToUse = new Date(); }/* ww w. j a v a 2s . c om*/ if (valueToUse instanceof Date && jdbcType == JdbcType.VARCHAR) { valueToUse = DateFormatUtils.format((Date) valueToUse, mapping.getPattern()); } if (valueToUse instanceof String && jdbcType == JdbcType.VARCHAR) { String stringValue = (String) valueToUse; if (!StringUtils.isEmpty(mapping.getEncoding())) { if (!StringUtils.isEmpty(stringValue)) { String[] encoding = StringUtils.split(mapping.getEncoding(), ">"); try { if (encoding.length == 2) valueToUse = new String(stringValue.getBytes(encoding[0]), encoding[1]); else if (encoding.length == 1) valueToUse = new String(stringValue.getBytes(), encoding[0]); } catch (UnsupportedEncodingException e) { LOG.error(e); } } } } if (valueToUse == null) ps.setNull(mapping.getIndex(), jdbcType.TYPE_CODE); else ps.setObject(mapping.getIndex(), valueToUse, jdbcType.TYPE_CODE); } } }); }
From source file:hoot.services.db.DbUtils.java
public static void batchRecordsDirectNodes(final long mapId, final List<?> records, final RecordBatchType recordBatchType, Connection conn, int maxRecordBatchSize) throws Exception { PreparedStatement ps = null; try {/*from w w w . j a v a2s . c o m*/ String sql = null; long execResult = -1; //conn.setAutoCommit(false); int count = 0; switch (recordBatchType) { case INSERT: sql = "insert into current_nodes_" + mapId + " (id, latitude, " + "longitude, changeset_id, visible, \"timestamp\", tile, version, tags) " + "values (?, ?, ?, ?, ?, ?, ?, ?, ?)"; ps = conn.prepareStatement(sql); for (Object o : records) { CurrentNodes node = (CurrentNodes) o; ps.setLong(1, node.getId()); ps.setInt(2, node.getLatitude()); ps.setInt(3, node.getLongitude()); ps.setLong(4, node.getChangesetId()); ps.setBoolean(5, node.getVisible()); ps.setTimestamp(6, node.getTimestamp()); ps.setLong(7, node.getTile()); ps.setLong(8, node.getVersion()); Map<String, String> tags = (Map<String, String>) node.getTags(); String hstoreStr = ""; Iterator it = tags.entrySet().iterator(); while (it.hasNext()) { Map.Entry pairs = (Map.Entry) it.next(); if (hstoreStr.length() > 0) { hstoreStr += ","; } hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\""; } ps.setObject(9, hstoreStr, Types.OTHER); ps.addBatch(); if (maxRecordBatchSize > -1) { if (++count % maxRecordBatchSize == 0) { ps.executeBatch(); //conn.commit(); } } } break; case UPDATE: sql = "update current_nodes_" + mapId + " set latitude=?, " + "longitude=?, changeset_id=?, visible=?, \"timestamp\"=?, tile=?, version=?, tags=? " + "where id=?"; ps = conn.prepareStatement(sql); for (Object o : records) { CurrentNodes node = (CurrentNodes) o; ps.setInt(1, node.getLatitude()); ps.setInt(2, node.getLongitude()); ps.setLong(3, node.getChangesetId()); ps.setBoolean(4, node.getVisible()); ps.setTimestamp(5, node.getTimestamp()); ps.setLong(6, node.getTile()); ps.setLong(7, node.getVersion()); Map<String, String> tags = (Map<String, String>) node.getTags(); String hstoreStr = ""; Iterator it = tags.entrySet().iterator(); while (it.hasNext()) { Map.Entry pairs = (Map.Entry) it.next(); if (hstoreStr.length() > 0) { hstoreStr += ","; } hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\""; } ps.setObject(8, hstoreStr, Types.OTHER); ps.setLong(9, node.getId()); ps.addBatch(); if (maxRecordBatchSize > -1) { if (++count % maxRecordBatchSize == 0) { ps.executeBatch(); //conn.commit(); ps.clearBatch(); } } } break; case DELETE: sql = "delete from current_nodes_" + mapId + " where id=?"; ps = conn.prepareStatement(sql); for (Object o : records) { CurrentNodes node = (CurrentNodes) o; ps.setLong(1, node.getId()); ps.addBatch(); if (maxRecordBatchSize > -1) { if (++count % maxRecordBatchSize == 0) { ps.executeBatch(); //conn.commit(); ps.clearBatch(); } } } break; default: throw new Exception(""); } ps.executeBatch(); //conn.commit(); } catch (Exception e) { //conn.rollback(); String msg = "Error executing batch query."; msg += " " + e.getMessage(); msg += " Cause:" + e.getCause().toString(); throw new Exception(msg); } finally { if (ps != null) { ps.close(); } //conn.setAutoCommit(true); } }
From source file:architecture.common.adaptor.connector.jdbc.AbstractJdbcConnector.java
/** * Batch .../* ww w .j a va2s .c om*/ * * @param queryString * @param parameterMappings * @param rows * @return */ protected Object deliver(final String queryString, final List<ParameterMapping> parameterMappings, final List<Map<String, Object>> rows) { log.debug("delivering : " + rows.size()); int[] cnt = getJdbcTemplate().batchUpdate(queryString, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Map<String, Object> row = rows.get(i); for (ParameterMapping mapping : parameterMappings) { JdbcType jdbcType = mapping.getJdbcType(); Object value = row.get(mapping.getProperty()); Object valueToUse = value; if (valueToUse == null && mapping.getJavaType() == Date.class) { valueToUse = new Date(); } if (valueToUse instanceof Date && jdbcType == JdbcType.VARCHAR) { valueToUse = DateFormatUtils.format((Date) valueToUse, mapping.getPattern()); } if (valueToUse instanceof String && jdbcType == JdbcType.VARCHAR) { String stringValue = (String) valueToUse; if (!StringUtils.isEmpty(mapping.getEncoding())) { if (!StringUtils.isEmpty(stringValue)) { String[] encoding = StringUtils.split(mapping.getEncoding(), ">"); try { if (encoding.length == 2) valueToUse = new String(stringValue.getBytes(encoding[0]), encoding[1]); else if (encoding.length == 1) valueToUse = new String(stringValue.getBytes(), encoding[0]); } catch (UnsupportedEncodingException e) { LOG.error(e); } } } } if (valueToUse == null) ps.setNull(mapping.getIndex(), jdbcType.TYPE_CODE); else ps.setObject(mapping.getIndex(), valueToUse, jdbcType.TYPE_CODE); } } public int getBatchSize() { return rows.size(); } }); int sum = 0; for (int c : cnt) { sum = sum + c; } return sum; }
From source file:com.jabyftw.lobstercraft.player.PlayerHandlerService.java
/** * Kick or ban player, online or not. This won't announce to the server and will keep a record on MySQL. This method <b>SHOULD</b> run asynchronously. * * @param offlinePlayer player to be kicked * @param banType kick type/*from w w w .j a va 2 s. c o m*/ * @param reason reason to be at record, from 4 to 120 characters * @param moderatorId moderator to be stored, can be null * @param bannedDuration ban duration, can be null * @return a ban response to the CommandSender */ public BanResponse kickPlayer(@NotNull OfflinePlayer offlinePlayer, @NotNull final BanType banType, @NotNull final String reason, @Nullable Integer moderatorId, @Nullable final Long bannedDuration) { // Check if player is registered if (!offlinePlayer.isRegistered()) return BanResponse.PLAYER_NOT_REGISTERED; // Set variables int playerId = offlinePlayer.getPlayerId(); long recordDate = System.currentTimeMillis(); // Check unban date Long unbanDate; if (banType != BanType.PLAYER_TEMPORARILY_BANNED) // Only temporary banned requires this argument unbanDate = null; else if (bannedDuration != null) unbanDate = recordDate + bannedDuration; else return BanResponse.BAN_DURATION_NOT_SET; // Check if reason has right size if (!Util.checkStringLength(reason, 4, 120)) return BanResponse.INVALID_REASON_LENGTH; try { // Retrieve connection Connection connection = LobsterCraft.dataSource.getConnection(); // Prepare statement PreparedStatement preparedStatement = connection.prepareStatement( // 6 arguments "INSERT INTO `minecraft`.`ban_records` (`user_playerId`, `user_moderatorId`, `banType`, `recordDate`, `reason`, `unbanDate`) VALUES (?, ?, ?, ?, ?, ?);", Statement.RETURN_GENERATED_KEYS); // Set variables for query preparedStatement.setInt(1, playerId); preparedStatement.setObject(2, moderatorId, Types.INTEGER); // will write null if is null preparedStatement.setByte(3, banType.getTypeId()); preparedStatement.setLong(4, recordDate); preparedStatement.setString(5, reason); preparedStatement.setObject(6, unbanDate, Types.BIGINT); // Execute statement preparedStatement.execute(); // Retrieve generated keys ResultSet generatedKeys = preparedStatement.getGeneratedKeys(); // Throw error if there is no generated key if (!generatedKeys.next()) throw new SQLException("There is no generated key"); // Create entry BannedPlayerEntry bannedPlayerEntry = new BannedPlayerEntry(generatedKeys.getLong("recordId"), moderatorId, banType, recordDate, reason, unbanDate); // Add entry to storage synchronized (playerBanEntries) { playerBanEntries.putIfAbsent(playerId, new HashSet<>()); playerBanEntries.get(playerId).add(bannedPlayerEntry); } // Close everything generatedKeys.close(); preparedStatement.close(); connection.close(); // Schedule player kick, if he is online OnlinePlayer onlinePlayer = offlinePlayer.getOnlinePlayer(null); if (onlinePlayer != null) Bukkit.getServer().getScheduler().runTask(LobsterCraft.plugin, () -> { if (onlinePlayer.getPlayer().isOnline()) // Kick player if he is online onlinePlayer.getPlayer().kickPlayer(bannedPlayerEntry.getKickMessage()); }); return BanResponse.SUCCESSFULLY_EXECUTED; } catch (SQLException exception) { exception.printStackTrace(); return BanResponse.ERROR_OCCURRED; } }
From source file:org.castor.jdo.engine.SQLTypeInfos.java
/** * Set given value on given PreparedStatement at given index with given SQL type. * //www . jav a 2 s . c om * @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: final Time time = value instanceof java.util.Date ? new Time(((java.util.Date) value).getTime()) : null; stmt.setTime(index, time != null ? time : (Time) value, getCalendar()); break; case Types.DATE: final Date date = value instanceof java.util.Date ? new Date(((java.util.Date) value).getTime()) : null; stmt.setDate(index, date != null ? date : (Date) value); break; case Types.TIMESTAMP: final Timestamp timestamp = value instanceof java.util.Date ? new Timestamp(((java.util.Date) value).getTime()) : null; stmt.setTimestamp(index, timestamp != null ? timestamp : (Timestamp) value, getCalendar()); break; case Types.BLOB: try { InputStream stream; if (value instanceof byte[]) { stream = new ByteArrayInputStream((byte[]) value); } else { stream = (InputStream) value; } stmt.setBinaryStream(index, stream, stream.available()); } catch (IOException ex) { throw new SQLException(ex.toString()); } break; case Types.CLOB: if (value instanceof String) { stmt.setCharacterStream(index, new StringReader((String) value), Math.min(((String) value).length(), Integer.MAX_VALUE)); } else { stmt.setCharacterStream(index, ((Clob) value).getCharacterStream(), (int) Math.min(((Clob) value).length(), Integer.MAX_VALUE)); } break; default: stmt.setObject(index, value, sqlType); break; } } } catch (SQLException ex) { LOG.error("Unexpected SQL exception: ", ex); } }
From source file:org.openmrs.util.databasechange.ConceptValidatorChangeSet.java
/** * Executes all the changes to the concept names as a batch update. * * @param connection The database connection *///from w w w . j a v a 2 s .co m private void runBatchUpdate(JdbcConnection connection) { PreparedStatement pStmt = null; try { connection.setAutoCommit(false); pStmt = connection.prepareStatement( "UPDATE concept_name SET locale = ?, concept_name_type = ?, locale_preferred = ?, voided = ?, date_voided = ?, void_reason = ?, voided_by = ? WHERE concept_name_id = ?"); Integer userId = DatabaseUpdater.getAuthenticatedUserId(); //is we have no authenticated user(for API users), set as Daemon if (userId == null || userId < 1) { userId = getInt(connection, "SELECT min(user_id) FROM users"); //leave it as null rather than setting it to 0 if (userId < 1) { userId = null; } } for (ConceptName conceptName : updatedConceptNames) { pStmt.setString(1, conceptName.getLocale().toString()); pStmt.setString(2, (conceptName.getConceptNameType() != null) ? conceptName.getConceptNameType().toString() : null); pStmt.setBoolean(3, conceptName.isLocalePreferred()); pStmt.setBoolean(4, conceptName.isVoided()); pStmt.setDate(5, conceptName.isVoided() ? new Date(System.currentTimeMillis()) : null); pStmt.setString(6, conceptName.getVoidReason()); // "Not all databases allow for a non-typed Null to be sent to the backend", so we can't use setInt pStmt.setObject(7, (conceptName.isVoided() && userId != null) ? userId : null, Types.INTEGER); pStmt.setInt(8, conceptName.getConceptNameId()); pStmt.addBatch(); } try { int[] updateCounts = pStmt.executeBatch(); for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] > -1) { log.debug("Successfully executed: updateCount=" + updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { log.debug("Successfully executed; No Success info"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { log.warn("Failed to execute update"); } } log.debug("Committing updates..."); connection.commit(); } catch (BatchUpdateException be) { log.warn("Error generated while processsing batch update", be); int[] updateCounts = be.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] > -1) { log.warn("Executed with exception: updateCount=" + updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { log.warn("Executed with exception; No Success info"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { log.warn("Failed to execute update with exception"); } } try { log.warn("Rolling back batch", be); connection.rollback(); } catch (Exception rbe) { log.warn("Error generated while rolling back batch update", be); } } } catch (SQLException e) { log.warn("Error generated", e); } catch (DatabaseException e) { log.warn("Error generated", e); } finally { //reset to auto commit mode try { connection.setAutoCommit(true); } catch (DatabaseException e) { log.warn("Failed to reset auto commit back to true", e); } if (pStmt != null) { try { pStmt.close(); } catch (SQLException e) { log.warn("Failed to close the prepared statement object"); } } } }
From source file:org.apache.torque.util.BasePeerImpl.java
/** * Utility method which executes a given sql statement * as prepared statement.// w w w .j ava 2 s . c o m * This method should be used for update, insert, and delete statements. * Use executeQuery() for selects. * * @param statementString A String with the sql statement to execute. * @param con The database connection to use. * @param replacementValues values to use as placeholders in the query. * or null or empty if no placeholders need to be filled. * * @return The number of rows affected. * * @throws TorqueException if executing the statement fails. */ public int executeStatement(String statementString, Connection con, List<JdbcTypedValue> replacementValues) throws TorqueException { int rowCount = -1; PreparedStatement statement = null; try { statement = con.prepareStatement(statementString); if (replacementValues != null) { int position = 1; for (JdbcTypedValue replacementValue : replacementValues) { if (replacementValue.getValue() == null) { statement.setNull(position, replacementValue.getJdbcType()); } else { statement.setObject(position, replacementValue.getValue(), replacementValue.getJdbcType()); } ++position; } } rowCount = statement.executeUpdate(); } catch (SQLException e) { throw new TorqueException(e); } finally { if (statement != null) { try { statement.close(); } catch (SQLException e) { throw new TorqueException(e); } } } return rowCount; }
From source file:edu.jhuapl.openessence.datasource.jdbc.entry.JdbcOeDataEntrySource.java
/** * Sets arguments of the proper type on a PreparedSatement * * @param pStmt prepared statement on which to set arguments * @param dimIds dimension ids that map to columns on the prepared statement * @param valueList values to set for the paramenters on the prepared statement * @param valueMap dimIds mapped to values to set for the paramenters on the prepared statement * @throws SQLException if error occurs while processing the prepared statement * @throws OeDataSourceException if error occurs converting value to it's sql type *///w w w .j a va 2 s . co m protected void setArgumentsOnSqlType(PreparedStatement pStmt, List<String> dimIds, List<Object> valueList, Map<String, Object> valueMap) throws SQLException, OeDataSourceException { if ((valueList == null && valueMap == null) || (valueList != null && valueMap != null)) { throw new OeDataSourceException("Invalid value lists. Only one form of the list can be provided."); } int argCount = 0; Object val; boolean isValueList = (valueList != null); for (String dimId : dimIds) { Dimension dim = getEditDimension(dimId); FieldType sqlType = dim.getSqlType(); if (isValueList) { val = valueList.get(argCount); } else { val = valueMap.get(dimId); } try { argCount++; switch (sqlType) { case DATE_TIME: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlTimestampType(val), Types.TIMESTAMP); continue; case DATE: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.DATE); continue; case BOOLEAN: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.BOOLEAN); continue; case FLOAT: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.FLOAT); continue; case DOUBLE: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.DOUBLE); continue; case INTEGER: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.INTEGER); continue; case LONG: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.BIGINT); continue; case TEXT: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.VARCHAR); continue; default: throw new AssertionError("Unexpected sqlType " + sqlType + "."); } } catch (OeDataSourceException e) { throw new SQLException("Error occured converting value \"" + val + "\" to its sql type.", e); } } }
From source file:architecture.common.spring.jdbc.core.ExtendedJdbcTemplate.java
public int[] batchUpdate(String sql, final List<ParameterMapping> parameterMappings, final List<Map<String, Object>> parameters) { return batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Map<String, Object> row = parameters.get(i); for (ParameterMapping mapping : parameterMappings) { JdbcType jdbcType = mapping.getJdbcType(); Object value = row.get(mapping.getProperty()); Object valueToUse = value; if (valueToUse == null && mapping.getJavaType() == Date.class) { valueToUse = new Date(); }/*from w w w. j ava 2 s . co m*/ if (valueToUse instanceof Date && jdbcType == JdbcType.VARCHAR) { valueToUse = DateFormatUtils.format((Date) valueToUse, mapping.getPattern()); } if (valueToUse instanceof String && jdbcType == JdbcType.VARCHAR) { String stringValue = (String) valueToUse; if (!StringUtils.isEmpty(mapping.getEncoding())) { if (!StringUtils.isEmpty(stringValue)) { String[] encoding = StringUtils.split(mapping.getEncoding(), ">"); try { if (encoding.length == 2) valueToUse = new String(stringValue.getBytes(encoding[0]), encoding[1]); else if (encoding.length == 1) valueToUse = new String(stringValue.getBytes(), encoding[0]); } catch (UnsupportedEncodingException e) { logger.error(e); } } } } if (valueToUse == null) ps.setNull(mapping.getIndex(), jdbcType.TYPE_CODE); else ps.setObject(mapping.getIndex(), valueToUse, jdbcType.TYPE_CODE); } } public int getBatchSize() { return parameters.size(); } }); }