List of usage examples for java.sql PreparedStatement setByte
void setByte(int parameterIndex, byte x) throws SQLException;
byte value. 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 .ja v a 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:com.l2jfree.gameserver.model.items.L2ItemInstance.java
public void updateItemAttributes() { Connection con = null;/*www.j av a 2 s. c om*/ try { con = L2DatabaseFactory.getInstance().getConnection(con); PreparedStatement statement = con.prepareStatement("REPLACE INTO item_attributes VALUES(?,?,?,?,?,?)"); statement.setInt(1, getObjectId()); if (_augmentation == null) { statement.setInt(2, -1); statement.setInt(3, -1); statement.setInt(4, -1); } else { statement.setInt(2, _augmentation.getAttributes()); if (_augmentation.getSkill() == null) { statement.setInt(3, 0); statement.setInt(4, 0); } else { statement.setInt(3, _augmentation.getSkill().getId()); statement.setInt(4, _augmentation.getSkill().getLevel()); } } if (_elementals == null) { statement.setByte(5, (byte) -1); statement.setInt(6, -1); } else { statement.setByte(5, _elementals.getElement()); statement.setInt(6, _elementals.getValue()); } statement.executeUpdate(); statement.close(); } catch (Exception e) { _log.fatal("Could not remove elemental enchant for item: " + getObjectId() + " from DB:", e); } finally { L2DatabaseFactory.close(con); } }
From source file:org.apache.synapse.mediators.db.AbstractDBMediator.java
/** * Return a Prepared statement for the given Statement object, which is ready to be executed * * @param stmnt SQL stataement to be executed * @param con The connection to be used * @param msgCtx Current message context * @return a PreparedStatement//from w w w.j ava2 s .c o m * @throws SQLException on error */ protected PreparedStatement getPreparedStatement(Statement stmnt, Connection con, MessageContext msgCtx) throws SQLException { SynapseLog synLog = getLog(msgCtx); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Getting a connection from DataSource " + getDSName() + " and preparing statement : " + stmnt.getRawStatement()); } if (con == null) { String msg = "Connection from DataSource " + getDSName() + " is null."; log.error(msg); throw new SynapseException(msg); } if (dataSource instanceof BasicDataSource) { BasicDataSource basicDataSource = (BasicDataSource) dataSource; int numActive = basicDataSource.getNumActive(); int numIdle = basicDataSource.getNumIdle(); String connectionId = Integer.toHexString(con.hashCode()); DBPoolView dbPoolView = getDbPoolView(); if (dbPoolView != null) { dbPoolView.setNumActive(numActive); dbPoolView.setNumIdle(numIdle); dbPoolView.updateConnectionUsage(connectionId); } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("[ DB Connection : " + con + " ]"); synLog.traceOrDebug("[ DB Connection instance identifier : " + connectionId + " ]"); synLog.traceOrDebug("[ Number of Active Connection : " + numActive + " ]"); synLog.traceOrDebug("[ Number of Idle Connection : " + numIdle + " ]"); } } PreparedStatement ps = con.prepareStatement(stmnt.getRawStatement()); // set parameters if any List<Statement.Parameter> params = stmnt.getParameters(); int column = 1; for (Statement.Parameter param : params) { if (param == null) { continue; } String value = (param.getPropertyName() != null ? param.getPropertyName() : param.getXpath().stringValueOf(msgCtx)); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Setting as parameter : " + column + " value : " + value + " as JDBC Type : " + param.getType() + "(see java.sql.Types for valid " + "types)"); } switch (param.getType()) { // according to J2SE 1.5 /docs/guide/jdbc/getstart/mapping.html case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: { if (value != null && value.length() != 0) { ps.setString(column++, value); } else { ps.setString(column++, null); } break; } case Types.NUMERIC: case Types.DECIMAL: { if (value != null && value.length() != 0) { ps.setBigDecimal(column++, new BigDecimal(value)); } else { ps.setBigDecimal(column++, null); } break; } case Types.BIT: { if (value != null && value.length() != 0) { ps.setBoolean(column++, Boolean.parseBoolean(value)); } else { ps.setNull(column++, Types.BIT); } break; } case Types.TINYINT: { if (value != null && value.length() != 0) { ps.setByte(column++, Byte.parseByte(value)); } else { ps.setNull(column++, Types.TINYINT); } break; } case Types.SMALLINT: { if (value != null && value.length() != 0) { ps.setShort(column++, Short.parseShort(value)); } else { ps.setNull(column++, Types.SMALLINT); } break; } case Types.INTEGER: { if (value != null && value.length() != 0) { ps.setInt(column++, Integer.parseInt(value)); } else { ps.setNull(column++, Types.INTEGER); } break; } case Types.BIGINT: { if (value != null && value.length() != 0) { ps.setLong(column++, Long.parseLong(value)); } else { ps.setNull(column++, Types.BIGINT); } break; } case Types.REAL: { if (value != null && value.length() != 0) { ps.setFloat(column++, Float.parseFloat(value)); } else { ps.setNull(column++, Types.REAL); } break; } case Types.FLOAT: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.FLOAT); } break; } case Types.DOUBLE: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.DOUBLE); } break; } // skip BINARY, VARBINARY and LONGVARBINARY case Types.DATE: { if (value != null && value.length() != 0) { ps.setDate(column++, Date.valueOf(value)); } else { ps.setNull(column++, Types.DATE); } break; } case Types.TIME: { if (value != null && value.length() != 0) { ps.setTime(column++, Time.valueOf(value)); } else { ps.setNull(column++, Types.TIME); } break; } case Types.TIMESTAMP: { if (value != null && value.length() != 0) { ps.setTimestamp(column++, Timestamp.valueOf(value)); } else { ps.setNull(column++, Types.TIMESTAMP); } break; } // skip CLOB, BLOB, ARRAY, DISTINCT, STRUCT, REF, JAVA_OBJECT default: { String msg = "Trying to set an un-supported JDBC Type : " + param.getType() + " against column : " + column + " and statement : " + stmnt.getRawStatement() + " used by a DB mediator against DataSource : " + getDSName() + " (see java.sql.Types for valid type values)"; handleException(msg, msgCtx); } } } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Successfully prepared statement : " + stmnt.getRawStatement() + " against DataSource : " + getDSName()); } return ps; }
From source file:org.apache.kylin.rest.service.QueryService.java
/** * @param preparedState//from w w w . j a v a 2 s . c o m * @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.openiot.gsn.storage.StorageManager.java
public void executeInsert(CharSequence tableName, DataField[] fields, StreamElement streamElement, Connection connection) throws SQLException { PreparedStatement ps = null; String query = getStatementInsert(tableName, fields).toString(); try {/*from w w w.j a v a 2s .c o m*/ ps = connection.prepareStatement(query); int counter = 1; for (DataField dataField : fields) { if (dataField.getName().equalsIgnoreCase("timed")) continue; Serializable value = streamElement.getData(dataField.getName()); switch (dataField.getDataTypeID()) { case DataTypes.VARCHAR: if (value == null) ps.setNull(counter, Types.VARCHAR); else ps.setString(counter, value.toString()); break; case DataTypes.CHAR: if (value == null) ps.setNull(counter, Types.CHAR); else ps.setString(counter, value.toString()); break; case DataTypes.INTEGER: if (value == null) ps.setNull(counter, Types.INTEGER); else ps.setInt(counter, ((Number) value).intValue()); break; case DataTypes.SMALLINT: if (value == null) ps.setNull(counter, Types.SMALLINT); else ps.setShort(counter, ((Number) value).shortValue()); break; case DataTypes.TINYINT: if (value == null) ps.setNull(counter, Types.TINYINT); else ps.setByte(counter, ((Number) value).byteValue()); break; case DataTypes.DOUBLE: if (value == null) ps.setNull(counter, Types.DOUBLE); else ps.setDouble(counter, ((Number) value).doubleValue()); break; case DataTypes.BIGINT: if (value == null) ps.setNull(counter, Types.BIGINT); else ps.setLong(counter, ((Number) value).longValue()); break; case DataTypes.BINARY: if (value == null) ps.setNull(counter, Types.BINARY); else ps.setBytes(counter, (byte[]) value); break; default: logger.error("The type conversion is not supported for : " + dataField.getName() + "(" + dataField.getDataTypeID() + ") : "); } counter++; } ps.setLong(counter, streamElement.getTimeStamp()); ps.execute(); } catch (GSNRuntimeException e) { //if (e.getType() == GSNRuntimeException.UNEXPECTED_VIRTUAL_SENSOR_REMOVAL) { // if (logger.isDebugEnabled()) // logger.debug("An stream element dropped due to unexpected virtual sensor removal. (Stream element: " + streamElement.toString() + ")+ Query: " + query, e); //} else logger.warn("Inserting a stream element failed : " + streamElement.toString(), e); } catch (SQLException e) { if (e.getMessage().toLowerCase().contains("duplicate entry")) logger.info("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); else logger.warn("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); throw e; } finally { close(ps); } }
From source file:ro.nextreports.engine.queryexec.QueryExecutor.java
private void setParameterValue(PreparedStatement pstmt, Class paramValueClass, Object paramValue, int index) throws SQLException, QueryException { // for "NOT IN (?)" setting null -> result is undeterminated // ParameterUtil.NULL was good only for list of strings (for NOT IN)! if (ParameterUtil.NULL.equals(paramValue)) { paramValue = null;//from w w w. java2 s . co m } if (paramValueClass.equals(Object.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.JAVA_OBJECT); } else { if (paramValue instanceof IdName) { pstmt.setObject(index + 1, ((IdName) paramValue).getId()); } else { pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(Boolean.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIT); } else { if (paramValue instanceof IdName) { pstmt.setBoolean(index + 1, (Boolean) ((IdName) paramValue).getId()); } else { pstmt.setBoolean(index + 1, (Boolean) paramValue); } } } else if (paramValueClass.equals(Byte.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TINYINT); } else { if (paramValue instanceof IdName) { pstmt.setByte(index + 1, (Byte) ((IdName) paramValue).getId()); } else { pstmt.setByte(index + 1, (Byte) paramValue); } } } else if (paramValueClass.equals(Double.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DOUBLE); } else { if (paramValue instanceof IdName) { pstmt.setDouble(index + 1, (Double) ((IdName) paramValue).getId()); } else { pstmt.setDouble(index + 1, (Double) paramValue); } } } else if (paramValueClass.equals(Float.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.FLOAT); } else { if (paramValue instanceof IdName) { pstmt.setFloat(index + 1, (Float) ((IdName) paramValue).getId()); } else { pstmt.setFloat(index + 1, (Float) paramValue); } } } else if (paramValueClass.equals(Integer.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.INTEGER); } else { if (paramValue instanceof IdName) { pstmt.setObject(index + 1, ((IdName) paramValue).getId()); } else { pstmt.setInt(index + 1, (Integer) paramValue); } } } else if (paramValueClass.equals(Long.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIGINT); } else { if (paramValue instanceof IdName) { pstmt.setLong(index + 1, (Long) ((IdName) paramValue).getId()); } else { pstmt.setLong(index + 1, (Long) paramValue); } } } else if (paramValueClass.equals(Short.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.SMALLINT); } else { if (paramValue instanceof IdName) { pstmt.setShort(index + 1, (Short) ((IdName) paramValue).getId()); } else { pstmt.setShort(index + 1, (Short) paramValue); } } //@todo // ParameterUtil -> values are taken from dialect (where there is no BigDecimal yet!) // or from meta data } else if (paramValueClass.equals(BigDecimal.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DECIMAL); } else { if (paramValue instanceof IdName) { Serializable ser = ((IdName) paramValue).getId(); if (ser instanceof BigDecimal) { pstmt.setBigDecimal(index + 1, (BigDecimal) (ser)); } else { pstmt.setInt(index + 1, (Integer) (ser)); } } else { // a simple value cannot be cast to BigDecimal! pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(BigInteger.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIGINT); } else { if (paramValue instanceof IdName) { Serializable ser = ((IdName) paramValue).getId(); if (ser instanceof BigInteger) { pstmt.setBigDecimal(index + 1, new BigDecimal((BigInteger) (ser))); } else if (ser instanceof BigDecimal) { pstmt.setBigDecimal(index + 1, (BigDecimal) (ser)); } else { pstmt.setInt(index + 1, (Integer) (ser)); } } else { // a simple value cannot be cast to BigDecimal! pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(String.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.VARCHAR); } else { if (paramValue instanceof IdName) { if (((IdName) paramValue).getId() == null) { pstmt.setNull(index + 1, Types.VARCHAR); } else { pstmt.setString(index + 1, ((IdName) paramValue).getId().toString()); } } else { pstmt.setString(index + 1, paramValue.toString()); } } } else if (paramValueClass.equals(Date.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DATE); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setDate(index + 1, new java.sql.Date(date.getTime())); } else { pstmt.setDate(index + 1, new java.sql.Date(((Date) paramValue).getTime())); } } } else if (paramValueClass.equals(Timestamp.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TIMESTAMP); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setTimestamp(index + 1, new Timestamp(date.getTime())); } else { pstmt.setTimestamp(index + 1, new Timestamp(((Date) paramValue).getTime())); } } } else if (paramValueClass.equals(Time.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TIME); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setTime(index + 1, new Time(date.getTime())); } else { pstmt.setTime(index + 1, new Time(((Date) paramValue).getTime())); } } } else { throw new QueryException("Parameter type " + paramValueClass.getName() + " not supported in query"); } // for logSql() statementParameters.put(index, paramValue); }
From source file:net.pms.dlna.DLNAMediaDatabase.java
public synchronized void insertData(String name, long modified, int type, DLNAMediaInfo media) { Connection conn = null;//from ww w . ja va2s. c om PreparedStatement ps = null; try { conn = getConnection(); ps = conn.prepareStatement( "INSERT INTO FILES(FILENAME, MODIFIED, TYPE, DURATION, BITRATE, WIDTH, HEIGHT, SIZE, CODECV, FRAMERATE, ASPECT, ASPECTRATIOCONTAINER, ASPECTRATIOVIDEOTRACK, REFRAMES, AVCLEVEL, BITSPERPIXEL, THUMB, CONTAINER, MODEL, EXPOSURE, ORIENTATION, ISO, MUXINGMODE, FRAMERATEMODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); ps.setString(1, name); ps.setTimestamp(2, new Timestamp(modified)); ps.setInt(3, type); if (media != null) { if (media.getDuration() != null) { ps.setDouble(4, media.getDurationInSeconds()); } else { ps.setNull(4, Types.DOUBLE); } int databaseBitrate = 0; if (type != Format.IMAGE) { databaseBitrate = media.getBitrate(); if (databaseBitrate == 0) { logger.debug("Could not parse the bitrate from: " + name); } } ps.setInt(5, databaseBitrate); ps.setInt(6, media.getWidth()); ps.setInt(7, media.getHeight()); ps.setLong(8, media.getSize()); ps.setString(9, left(media.getCodecV(), SIZE_CODECV)); ps.setString(10, left(media.getFrameRate(), SIZE_FRAMERATE)); ps.setString(11, left(media.getAspect(), SIZE_ASPECT)); ps.setString(12, left(media.getAspect(), SIZE_ASPECTRATIO_CONTAINER)); ps.setString(13, left(media.getAspect(), SIZE_ASPECTRATIO_VIDEOTRACK)); ps.setByte(14, media.getReferenceFrameCount()); ps.setString(15, left(media.getAvcLevel(), SIZE_AVC_LEVEL)); ps.setInt(16, media.getBitsPerPixel()); ps.setBytes(17, media.getThumb()); ps.setString(18, left(media.getContainer(), SIZE_CONTAINER)); if (media.getExtras() != null) { ps.setString(19, left(media.getExtrasAsString(), SIZE_MODEL)); } else { ps.setString(19, left(media.getModel(), SIZE_MODEL)); } ps.setInt(20, media.getExposure()); ps.setInt(21, media.getOrientation()); ps.setInt(22, media.getIso()); ps.setString(23, left(media.getMuxingModeAudio(), SIZE_MUXINGMODE)); ps.setString(24, left(media.getFrameRateMode(), SIZE_FRAMERATE_MODE)); } else { ps.setString(4, null); ps.setInt(5, 0); ps.setInt(6, 0); ps.setInt(7, 0); ps.setLong(8, 0); ps.setString(9, null); ps.setString(10, null); ps.setString(11, null); ps.setString(12, null); ps.setString(13, null); ps.setByte(14, (byte) -1); ps.setString(15, null); ps.setInt(16, 0); ps.setBytes(17, null); ps.setString(18, null); ps.setString(19, null); ps.setInt(20, 0); ps.setInt(21, 0); ps.setInt(22, 0); ps.setString(23, null); ps.setString(24, null); } ps.executeUpdate(); ResultSet rs = ps.getGeneratedKeys(); int id = -1; while (rs.next()) { id = rs.getInt(1); } rs.close(); if (media != null && id > -1) { PreparedStatement insert = null; if (media.getAudioTracksList().size() > 0) { insert = conn.prepareStatement( "INSERT INTO AUDIOTRACKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); } for (DLNAMediaAudio audio : media.getAudioTracksList()) { insert.clearParameters(); insert.setInt(1, id); insert.setInt(2, audio.getId()); insert.setString(3, left(audio.getLang(), SIZE_LANG)); insert.setString(4, left(audio.getFlavor(), SIZE_FLAVOR)); insert.setInt(5, audio.getAudioProperties().getNumberOfChannels()); insert.setString(6, left(audio.getSampleFrequency(), SIZE_SAMPLEFREQ)); insert.setString(7, left(audio.getCodecA(), SIZE_CODECA)); insert.setInt(8, audio.getBitsperSample()); insert.setString(9, left(trimToEmpty(audio.getAlbum()), SIZE_ALBUM)); insert.setString(10, left(trimToEmpty(audio.getArtist()), SIZE_ARTIST)); insert.setString(11, left(trimToEmpty(audio.getSongname()), SIZE_SONGNAME)); insert.setString(12, left(trimToEmpty(audio.getGenre()), SIZE_GENRE)); insert.setInt(13, audio.getYear()); insert.setInt(14, audio.getTrack()); insert.setInt(15, audio.getAudioProperties().getAudioDelay()); insert.setString(16, left(trimToEmpty(audio.getMuxingModeAudio()), SIZE_MUXINGMODE)); insert.setInt(17, audio.getBitRate()); try { insert.executeUpdate(); } catch (JdbcSQLException e) { if (e.getErrorCode() == 23505) { logger.debug( "A duplicate key error occurred while trying to store the following file's audio information in the database: " + name); } else { logger.debug( "An error occurred while trying to store the following file's audio information in the database: " + name); } logger.debug("The error given by jdbc was: " + e); } } if (media.getSubtitleTracksList().size() > 0) { insert = conn.prepareStatement("INSERT INTO SUBTRACKS VALUES (?, ?, ?, ?, ?)"); } for (DLNAMediaSubtitle sub : media.getSubtitleTracksList()) { if (sub.getExternalFile() == null) { // no save of external subtitles insert.clearParameters(); insert.setInt(1, id); insert.setInt(2, sub.getId()); insert.setString(3, left(sub.getLang(), SIZE_LANG)); insert.setString(4, left(sub.getFlavor(), SIZE_FLAVOR)); insert.setInt(5, sub.getType().getStableIndex()); try { insert.executeUpdate(); } catch (JdbcSQLException e) { if (e.getErrorCode() == 23505) { logger.debug( "A duplicate key error occurred while trying to store the following file's subtitle information in the database: " + name); } else { logger.debug( "An error occurred while trying to store the following file's subtitle information in the database: " + name); } logger.debug("The error given by jdbc was: " + e); } } } close(insert); } } catch (SQLException se) { if (se.getErrorCode() == 23001) { logger.debug("Duplicate key while inserting this entry: " + name + " into the database: " + se.getMessage()); } else { logger.error(null, se); } } finally { close(ps); close(conn); } }
From source file:gsn.storage.StorageManager.java
public void executeInsert(CharSequence tableName, DataField[] fields, StreamElement streamElement, Connection connection) throws SQLException { PreparedStatement ps = null; String query = getStatementInsert(tableName, fields).toString(); try {//from w ww . j a v a 2 s.co m ps = connection.prepareStatement(query); int counter = 1; for (DataField dataField : fields) { if (dataField.getName().equalsIgnoreCase("timed")) continue; Serializable value = streamElement.getData(dataField.getName()); switch (dataField.getDataTypeID()) { case DataTypes.VARCHAR: if (value == null) ps.setNull(counter, Types.VARCHAR); else ps.setString(counter, value.toString()); break; case DataTypes.CHAR: if (value == null) ps.setNull(counter, Types.CHAR); else ps.setString(counter, value.toString()); break; case DataTypes.INTEGER: if (value == null) ps.setNull(counter, Types.INTEGER); else ps.setInt(counter, ((Number) value).intValue()); break; case DataTypes.SMALLINT: if (value == null) ps.setNull(counter, Types.SMALLINT); else ps.setShort(counter, ((Number) value).shortValue()); break; case DataTypes.TINYINT: if (value == null) ps.setNull(counter, Types.TINYINT); else ps.setByte(counter, ((Number) value).byteValue()); break; case DataTypes.DOUBLE: if (value == null) ps.setNull(counter, Types.DOUBLE); else ps.setDouble(counter, ((Number) value).doubleValue()); break; case DataTypes.FLOAT: if (value == null) ps.setNull(counter, Types.FLOAT); else ps.setFloat(counter, ((Number) value).floatValue()); break; case DataTypes.BIGINT: if (value == null) ps.setNull(counter, Types.BIGINT); else ps.setLong(counter, ((Number) value).longValue()); break; case DataTypes.BINARY: if (value == null) ps.setNull(counter, Types.BINARY); else ps.setBytes(counter, (byte[]) value); break; default: logger.error("The type conversion is not supported for : " + dataField.getName() + "(" + dataField.getDataTypeID() + ") : "); } counter++; } ps.setLong(counter, streamElement.getTimeStamp()); ps.execute(); } catch (GSNRuntimeException e) { //if (e.getType() == GSNRuntimeException.UNEXPECTED_VIRTUAL_SENSOR_REMOVAL) { // if (logger.isDebugEnabled()) // logger.debug("An stream element dropped due to unexpected virtual sensor removal. (Stream element: " + streamElement.toString() + ")+ Query: " + query, e); //} else logger.warn("Inserting a stream element failed : " + streamElement.toString(), e); } catch (SQLException e) { if (e.getMessage().toLowerCase().contains("duplicate entry")) logger.info("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); else logger.warn("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); throw e; } finally { close(ps); } }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private void setTinyIntValue(int queryType, String value, String paramType, PreparedStatement sqlQuery, int i) throws SQLException { Byte val = null; if (value != null) { val = new Byte(value); }/* www . j a v a 2 s . co m*/ if (QueryTypes.IN.equals(paramType)) { if (queryType == SQLQuery.DS_QUERY_TYPE_NORMAL) { if (value == null) { sqlQuery.setNull(i + 1, java.sql.Types.TINYINT); } else { sqlQuery.setByte(i + 1, val); } } else { if (value == null) { ((CallableStatement) sqlQuery).setNull(i + 1, java.sql.Types.TINYINT); } else { ((CallableStatement) sqlQuery).setByte(i + 1, val); } } } else if (QueryTypes.INOUT.equals(paramType)) { if (value == null) { ((CallableStatement) sqlQuery).setNull(i + 1, java.sql.Types.TINYINT); } else { ((CallableStatement) sqlQuery).setByte(i + 1, val); } ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TINYINT); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TINYINT); } }
From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java
/** * Sets a specific value on a prepared statement *//*from ww w . j a v a 2 s. c o m*/ protected void setStatementValue(PreparedStatement s, MetaField f, int index, Object value) throws SQLException { int j = index; switch (f.getType()) { case MetaField.BOOLEAN: { if (value == null) { s.setNull(j, Types.BIT); } else if (value instanceof Boolean) { s.setBoolean(j, ((Boolean) value).booleanValue()); } else { s.setBoolean(j, Boolean.valueOf(value.toString()).booleanValue()); } } break; case MetaField.BYTE: { if (value == null) { s.setNull(j, Types.TINYINT); } else if (value instanceof Byte) { s.setByte(j, ((Byte) value).byteValue()); } else { s.setByte(j, Byte.valueOf(value.toString()).byteValue()); } } break; case MetaField.SHORT: { if (value == null) { s.setNull(j, Types.SMALLINT); } else if (value instanceof Short) { s.setShort(j, ((Short) value).shortValue()); } else { s.setShort(j, Short.valueOf(value.toString()).shortValue()); } } break; case MetaField.INT: { if (value == null) { s.setNull(j, Types.INTEGER); } else if (value instanceof Integer) { s.setInt(j, ((Integer) value).intValue()); } else { s.setInt(j, Integer.valueOf(value.toString()).intValue()); } } break; case MetaField.DATE: // NOTE DATE IS TREATED AS LONG! { if (value == null) { s.setNull(j, Types.TIMESTAMP); } else if (value instanceof java.util.Date) { s.setTimestamp(j, new Timestamp(((java.util.Date) value).getTime())); } else { s.setTimestamp(j, new Timestamp(Long.valueOf(value.toString()).longValue())); } } break; case MetaField.LONG: { if (value == null) { s.setNull(j, Types.BIGINT); } else if (value instanceof Long) { s.setLong(j, ((Long) value).longValue()); } else { s.setLong(j, Long.valueOf(value.toString()).longValue()); } } break; // WARNING: This should not be a valid key case MetaField.FLOAT: { if (value == null) { s.setNull(j, Types.FLOAT); } else if (value instanceof Float) { s.setFloat(j, ((Float) value).floatValue()); } else { s.setFloat(j, Float.valueOf(value.toString()).floatValue()); } } break; // WARNING: This should not be a valid key case MetaField.DOUBLE: { if (value == null) { s.setNull(j, Types.DOUBLE); } else if (value instanceof Double) { s.setDouble(j, ((Double) value).doubleValue()); } else { s.setDouble(j, Double.valueOf(value.toString()).doubleValue()); } } break; case MetaField.STRING: if (value == null) { s.setNull(j, Types.VARCHAR); } else { s.setString(j, value.toString()); } break; case MetaField.OBJECT: //if ( value == null ) // s.setNull( j, Types.BLOB ); //else s.setObject(j, value); break; } }