List of usage examples for java.sql PreparedStatement setLong
void setLong(int parameterIndex, long x) throws SQLException;
long
value. From source file:com.microsoftopentechnologies.azchat.web.dao.UserDAOImpl.java
/** * This method generates the prepare statement from userEntity object. * /*from w ww. j av a 2s . com*/ * @param preparedStatement * @param user * @return * @throws SQLException */ public PreparedStatement generatePreparedStatement(PreparedStatement preparedStatement, UserEntity user) throws SQLException { preparedStatement.setString(1, user.getNameId()); preparedStatement.setString(2, user.getIdentityProvider()); preparedStatement.setString(3, user.getFirstName()); preparedStatement.setString(4, user.getLastName()); preparedStatement.setString(5, user.getPhotoBlobUrl()); preparedStatement.setString(6, user.getEmailAddress()); preparedStatement.setInt(7, user.getPhoneCountryCode()); preparedStatement.setLong(8, user.getPhoneNumber()); preparedStatement.setDate(9, new java.sql.Date(user.getDateCreated().getTime())); preparedStatement.setDate(10, new java.sql.Date(user.getCreatedBy().getTime())); preparedStatement.setDate(11, new java.sql.Date(user.getDateModified().getTime())); preparedStatement.setDate(12, new java.sql.Date(user.getModifiedBy().getTime())); return preparedStatement; }
From source file:at.alladin.rmbt.controlServer.NewsResource.java
@Post("json") public String request(final String entity) { addAllowOrigin();//from w w w . j av a 2 s .c om JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); String answerString; System.out.println(MessageFormat.format(labels.getString("NEW_NEWS"), getIP())); if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); String lang = request.optString("language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } else lang = settings.getString("RMBT_DEFAULT_LANGUAGE"); String sqlLang = lang; if (!sqlLang.equals("de")) sqlLang = "en"; if (conn != null) { final long lastNewsUid = request.optLong("lastNewsUid"); final String plattform = request.optString("plattform"); final int softwareVersionCode = request.optInt("softwareVersionCode", -1); String uuid = request.optString("uuid"); final JSONArray newsList = new JSONArray(); try { final PreparedStatement st = conn.prepareStatement("SELECT uid,title_" + sqlLang + " AS title, text_" + sqlLang + " AS text FROM news " + " WHERE" + " (uid > ? OR force = true)" + " AND active = true" + " AND (plattform IS NULL OR plattform = ?)" + " AND (max_software_version_code IS NULL OR ? <= max_software_version_code)" + " AND (min_software_version_code IS NULL OR ? >= min_software_version_code)" + " AND (uuid IS NULL OR uuid::TEXT = ?)" + //convert to text so that empty uuid-strings are tolerated " ORDER BY time ASC"); st.setLong(1, lastNewsUid); st.setString(2, plattform); st.setInt(3, softwareVersionCode); st.setInt(4, softwareVersionCode); st.setString(5, uuid); final ResultSet rs = st.executeQuery(); while (rs.next()) { final JSONObject jsonItem = new JSONObject(); jsonItem.put("uid", rs.getInt("uid")); jsonItem.put("title", rs.getString("title")); jsonItem.put("text", rs.getString("text")); newsList.put(jsonItem); } rs.close(); st.close(); } catch (final SQLException e) { e.printStackTrace(); errorList.addError("ERROR_DB_GET_NEWS_SQL"); } // } answer.put("news", newsList); } else errorList.addError("ERROR_DB_CONNECTION"); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSON Data " + e.toString()); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } answerString = answer.toString(); return answerString; }
From source file:com.oic.event.SetProfile.java
@Override public void ActionEvent(JSONObject json, WebSocketListener webSocket) { JSONObject responseJSON = new JSONObject(); responseJSON.put("method", "setprofile"); if (!validation(json, webSocket)) { return;//from w w w. j a v a 2s . c o m } Connection con = DatabaseConnection.getConnection(); PreparedStatement ps; try { con = DatabaseConnection.getConnection(); con.setAutoCommit(false); String sql = "UPDATE user SET studentnumber = ?, name = ?, avatarid = ?, grade = ?, sex = ?, birth = ?, comment = ? " + "WHERE userid = ?"; ps = con.prepareStatement(sql); ps.setString(1, json.get("studentid").toString()); ps.setString(2, json.get("username").toString()); ps.setInt(3, Integer.parseInt(json.get("avatarid").toString())); ps.setInt(4, Integer.parseInt(json.get("grade").toString())); ps.setInt(5, Integer.parseInt(json.get("gender").toString())); ps.setDate(6, toDate(json.get("birthday").toString())); ps.setString(7, json.get("comment").toString()); ps.setLong(8, webSocket.getCharacter().getUserId()); ps.executeUpdate(); ps.close(); sql = "UPDATE setting SET privategrade = ?, privatesex = ?, privatebirth =? WHERE userid = ?"; ps = con.prepareStatement(sql); ps.setInt(1, Integer.parseInt(json.get("vgrade").toString())); ps.setInt(2, Integer.parseInt(json.get("vgender").toString())); ps.setInt(3, Integer.parseInt(json.get("vbirthday").toString())); ps.setLong(4, webSocket.getCharacter().getUserId()); ps.executeUpdate(); ps.close(); con.commit(); //TODO responseJSON.put("status", 0); } catch (Exception e) { try { con.rollback(); } catch (SQLException sq) { LOG.warning("[setProfile]Error Rolling back."); } e.printStackTrace(); responseJSON.put("status", 1); } finally { try { con.setAutoCommit(true); } catch (SQLException ex) { Logger.getLogger(SetProfile.class.getName()).log(Level.WARNING, "Error going back to AutoCommit mode", ex); } } webSocket.sendJson(responseJSON); }
From source file:dk.netarkivet.harvester.datamodel.ScheduleDBDAO.java
/** Sets the first twelve parameters of a Schedule in the order. * name, comments, startdate, enddate, maxrepeats, * timeunit, numtimeunits, anytime, onminute, onhour, * ondayofweek, ondayofmonth/*from www .j av a 2 s . c o m*/ * @param s a prepared SQL statement * @param schedule a given schedule. * @throws SQLException If the operation fails. */ private void setScheduleParameters(PreparedStatement s, Schedule schedule) throws SQLException { DBUtils.setName(s, 1, schedule, Constants.MAX_NAME_SIZE); DBUtils.setComments(s, 2, schedule, Constants.MAX_COMMENT_SIZE); final Date startDate = schedule.getStartDate(); final int fieldNum = 3; DBUtils.setDateMaybeNull(s, fieldNum, startDate); if (schedule instanceof TimedSchedule) { TimedSchedule ts = (TimedSchedule) schedule; DBUtils.setDateMaybeNull(s, 4, ts.getEndDate()); s.setNull(5, Types.BIGINT); } else { s.setNull(4, Types.DATE); RepeatingSchedule rs = (RepeatingSchedule) schedule; s.setLong(5, rs.getRepeats()); } Frequency freq = schedule.getFrequency(); s.setInt(6, freq.ordinal()); s.setInt(7, freq.getNumUnits()); s.setBoolean(8, freq.isAnytime()); DBUtils.setIntegerMaybeNull(s, 9, freq.getOnMinute()); DBUtils.setIntegerMaybeNull(s, 10, freq.getOnHour()); DBUtils.setIntegerMaybeNull(s, 11, freq.getOnDayOfWeek()); DBUtils.setIntegerMaybeNull(s, 12, freq.getOnDayOfMonth()); }
From source file:com.globalsight.everest.permission.Permission.java
/** * Adds the given permission to the database PERMISSION table if it does not * already exist in the map. This does update one by one, but this method * should almost never be called except on startup and if new permissions * were actually added, so there is no reason to batch. * /*from w w w .j av a2 s. co m*/ * @param p_added * set to true if anything was added * @param p_perm * permission name (should be above defined constant) */ static private boolean addPermission(long id, String p_perm) { boolean added = false; Connection c = null; PreparedStatement stmt = null; try { c = ConnectionPool.getConnection(); c.setAutoCommit(false); if (s_idMap.isEmpty()) { stmt = c.prepareStatement(SQL_INSERT_FIRST_PERM); stmt.executeUpdate(); s_idMap.put(p_perm, new Long(1)); if (logger.isDebugEnabled()) { logger.debug("Added " + p_perm + " to the table."); } } else if (!s_idMap.containsKey(p_perm)) { stmt = c.prepareStatement(SQL_INSERT_PERM); stmt.setLong(1, id); stmt.setString(2, p_perm); stmt.executeUpdate(); added = true; if (logger.isDebugEnabled()) { logger.debug("Added " + p_perm + " to the table."); } } c.commit(); } catch (Exception ex) { logger.error("Failed to add permission" + p_perm + " to the database.", ex); added = false; } finally { ConnectionPool.silentClose(stmt); ConnectionPool.silentReturnConnection(c); } return added; }
From source file:gsn.storage.StorageManager.java
/** * Executes the query of the database. Returns the specified colIndex of the * first row. Useful for image recovery of the web interface. * * @param query The query to be executed. * @return A resultset with only one row and one column. The user of the * method should first call next on the result set to make sure that * the row is there and then retrieve the value for the row. * @throws SQLException/* ww w .ja v a 2 s. c o m*/ */ public ResultSet getBinaryFieldByQuery(StringBuilder query, String colName, long pk, Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(query.toString()); ps.setLong(1, pk); return ps.executeQuery(); }
From source file:com.mirth.connect.server.controllers.tests.TestUtils.java
public static void createTestMessagesFast(String channelId, Message templateMessage, int power) throws Exception { long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId); deleteAllMessages(channelId);//w w w .j av a 2s . c o m createTestMessages(channelId, templateMessage, 1); Connection connection = null; PreparedStatement messageStatement = null; PreparedStatement metaDataStatement = null; PreparedStatement contentStatement = null; long idOffset = templateMessage.getMessageId(); logger.debug("Replicating messages in channel \"" + channelId + "\""); try { connection = getConnection(); messageStatement = connection.prepareStatement("INSERT INTO d_m" + localChannelId + " (id, server_id, received_date, processed) SELECT id + ?, server_id, received_date, processed FROM d_m" + localChannelId); metaDataStatement = connection.prepareStatement("INSERT INTO d_mm" + localChannelId + " (id, server_id, message_id, chain_id, received_date, status, order_id) SELECT id, server_id, message_id + ?, chain_id, received_date, status, order_id FROM d_mm" + localChannelId); contentStatement = connection.prepareStatement("INSERT INTO d_mc" + localChannelId + " (metadata_id, message_id, content_type, content, is_encrypted, data_type) SELECT metadata_id, message_id + ?, content_type, content, is_encrypted, data_type FROM d_mc" + localChannelId); for (int i = 0; i < power; i++) { messageStatement.setLong(1, idOffset); metaDataStatement.setLong(1, idOffset); contentStatement.setLong(1, idOffset); messageStatement.executeUpdate(); metaDataStatement.executeUpdate(); contentStatement.executeUpdate(); idOffset *= 2; connection.commit(); logger.debug("# of messages in channel \"" + channelId + "\" is now " + getNumMessages(channelId)); } } finally { close(messageStatement); close(metaDataStatement); close(contentStatement); close(connection); } fixMessageIdSequence(channelId); logger.debug("Finished replicating messages in channel \"" + channelId + "\""); }
From source file:org.ohmage.query.impl.AuditQueries.java
@Override public void createAudit(final RequestServlet.RequestType requestType, final String uri, final String client, final String requestId, final String deviceId, final Map<String, String[]> parameters, final Map<String, String[]> extras, final String response, final long receivedMillis, final long respondMillis) throws DataAccessException { if (requestType == null) { throw new IllegalArgumentException("The request type is required and cannot be null."); } else if (uri == null) { throw new IllegalArgumentException("The request URI is required and cannot be null."); } else if (response == null) { throw new IllegalArgumentException("The response is required and cannot be null."); }//from w ww .j a v a 2s. co m // Create the transaction. DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("Creating a request audit."); try { // Begin the transaction. PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource()); TransactionStatus status = transactionManager.getTransaction(def); // Create a key holder that will be responsible for referencing // which row was just inserted. KeyHolder keyHolder = new GeneratedKeyHolder(); // Insert the audit entry. try { getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(SQL_INSERT_AUDIT, new String[] { "id" }); ps.setString(1, requestType.name().toLowerCase()); ps.setString(2, uri); ps.setString(3, client); ps.setString(4, requestId); ps.setString(5, deviceId); ps.setString(6, response); ps.setLong(7, receivedMillis); ps.setLong(8, respondMillis); return ps; } }, keyHolder); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException("Error while executing SQL '" + SQL_INSERT_AUDIT + "' with parameters: " + requestType.name().toLowerCase() + ", " + uri + ", " + client + ", " + deviceId + ", " + response + ", " + receivedMillis + ", " + respondMillis, e); } // Add all of the parameters. if (parameters != null) { for (String key : parameters.keySet()) { for (String value : parameters.get(key)) { try { getJdbcTemplate().update(SQL_INSERT_PARAMETER, keyHolder.getKey().longValue(), key, value); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException( "Error while executing SQL '" + SQL_INSERT_PARAMETER + "' with parameters: " + keyHolder.getKey().longValue() + ", " + key + ", " + value, e); } } } } // Add all of the extras. if (extras != null) { for (String key : extras.keySet()) { for (String value : extras.get(key)) { try { getJdbcTemplate().update(SQL_INSERT_EXTRA, keyHolder.getKey().longValue(), key, value); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException( "Error while executing SQL '" + SQL_INSERT_EXTRA + "' with parameters: " + keyHolder.getKey().longValue() + ", " + key + ", " + value, e); } } } } // Commit the transaction. try { transactionManager.commit(status); } catch (TransactionException e) { transactionManager.rollback(status); throw new DataAccessException("Error while committing the transaction.", e); } } catch (TransactionException e) { throw new DataAccessException("Error while attempting to rollback the transaction.", e); } }
From source file:com.flexive.ejb.beans.MandatorEngineBean.java
/** * {@inheritDoc}/*from w w w.j a va 2 s . com*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public void remove(long mandatorId) throws FxApplicationException { final UserTicket ticket = FxContext.getUserTicket(); final FxEnvironment environment = CacheAdmin.getEnvironment(); // Security FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor); //exist check Mandator mand = environment.getMandator(mandatorId); Connection con = null; PreparedStatement ps = null; String sql; try { try { FxContext.get().runAsSystem(); grp.remove(grp.loadMandatorGroup(mandatorId).getId()); } finally { FxContext.get().stopRunAsSystem(); } con = Database.getDbConnection(); // 1 sql = "DELETE FROM " + TBL_USERGROUPS + " WHERE MANDATOR=? AND AUTOMANDATOR=1"; ps = con.prepareStatement(sql); ps.setLong(1, mandatorId); ps.executeUpdate(); ps.close(); // 1 sql = "DELETE FROM " + TBL_MANDATORS + " WHERE ID=?"; ps = con.prepareStatement(sql); ps.setLong(1, mandatorId); ps.executeUpdate(); StructureLoader.removeMandator(FxContext.get().getDivisionId(), mandatorId); StructureLoader.updateUserGroups(FxContext.get().getDivisionId(), grp.loadAll(-1)); } catch (SQLException exc) { final boolean keyViolation = StorageManager.isForeignKeyViolation(exc); EJBUtils.rollback(ctx); if (keyViolation) throw new FxEntryInUseException(exc, "ex.mandator.removeFailed.inUse", mand.getName()); throw new FxRemoveException(LOG, exc, "ex.mandator.removeFailed", mand.getName(), exc.getMessage()); } finally { Database.closeObjects(MandatorEngineBean.class, con, ps); } }
From source file:com.flexive.ejb.beans.MandatorEngineBean.java
/** * {@inheritDoc}/*from w w w .j a va2 s.c o m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public void changeName(long mandatorId, String name) throws FxApplicationException { FxSharedUtils.checkParameterEmpty(name, "name"); final UserTicket ticket = FxContext.getUserTicket(); final FxEnvironment environment = CacheAdmin.getEnvironment(); // Security FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor); //exist check Mandator mand = environment.getMandator(mandatorId); Connection con = null; PreparedStatement ps = null; String sql; try { name = name.trim(); // Obtain a database connection con = Database.getDbConnection(); // 1 2 3 4 sql = "UPDATE " + TBL_MANDATORS + " SET NAME=?, MODIFIED_BY=?, MODIFIED_AT=? WHERE ID=?"; final long NOW = System.currentTimeMillis(); ps = con.prepareStatement(sql); ps.setString(1, name.trim()); ps.setLong(2, ticket.getUserId()); ps.setLong(3, NOW); ps.setLong(4, mandatorId); ps.executeUpdate(); ps.close(); sql = "UPDATE " + TBL_USERGROUPS + " SET NAME=? WHERE AUTOMANDATOR=?"; ps = con.prepareStatement(sql); ps.setString(1, "Everyone (" + name + ")"); ps.setLong(2, mandatorId); ps.executeUpdate(); StructureLoader.updateMandator(FxContext.get().getDivisionId(), new Mandator(mand.getId(), name, mand.getMetadataId(), mand.isActive(), new LifeCycleInfoImpl(mand.getLifeCycleInfo().getCreatorId(), mand.getLifeCycleInfo().getCreationTime(), ticket.getUserId(), NOW))); StructureLoader.updateUserGroups(FxContext.get().getDivisionId(), grp.loadAll(-1)); } catch (SQLException exc) { // check before rollback, because it might need an active transaciton final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(exc); EJBUtils.rollback(ctx); if (uniqueConstraintViolation) { throw new FxUpdateException(LOG, "ex.mandator.update.name.unique", name); } else { throw new FxUpdateException(LOG, exc, "ex.mandator.updateFailed", mand.getName(), exc.getMessage()); } } finally { Database.closeObjects(MandatorEngineBean.class, con, ps); } }