List of usage examples for java.sql PreparedStatement setBoolean
void setBoolean(int parameterIndex, boolean x) throws SQLException;
boolean value. From source file:info.raack.appliancedetection.evaluation.data.JDBCDatabase.java
@Transactional public void saveSimulation(final Simulation simulation) { // save simulation / evaluation KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(insertForNewSimulationEvaluation, new Object[] { simulation.getId(), simulation.getStartTime().getTime(), simulation.getDurationInSeconds(), simulation.getNumAppliances(), simulation.getOnConcurrency(), simulation.getLabelsPerOnOff(), simulation.getGroup() != null ? simulation.getGroup().getId() : null, false }); // save simulated appliances for (final SimulatedAppliance appliance : simulation.getSimulatedAppliances()) { keyHolder = new GeneratedKeyHolder(); if (appliance.getLabeledAppliance() != null) { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(insertForNewSimulatedAppliance, new String[] { "id" }); ps.setString(1, simulation.getId()); ps.setInt(2, appliance.getLabeledAppliance().getId()); ps.setString(3, appliance.getClass().getName()); ps.setInt(4, appliance.getApplianceNum()); return ps; }//from w ww .j a v a 2 s . co m }, keyHolder); } else { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement( insertForNewSimulatedApplianceWithoutLabelLink, new String[] { "id" }); ps.setString(1, simulation.getId()); ps.setString(2, appliance.getClass().getName()); ps.setInt(3, appliance.getApplianceNum()); return ps; } }, keyHolder); } final int simulatedApplianceDatabaseId = keyHolder.getKey().intValue(); // save simulated appliance energy consumption jdbcTemplate.batchUpdate(insertForNewSimulatedApplianceEnergyConsumption, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { EnergyTimestep energyTimestep = appliance.getEnergyTimesteps().get(i); ps.setInt(1, simulatedApplianceDatabaseId); ps.setLong(2, energyTimestep.getStartTime().getTime()); ps.setLong(3, energyTimestep.getEndTime().getTime()); ps.setInt(4, (int) energyTimestep.getEnergyConsumed()); } public int getBatchSize() { return appliance.getEnergyTimesteps().size(); } }); // save simulated appliance state transitions jdbcTemplate.batchUpdate(insertForNewSimulatedApplianceStateTransitions, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ApplianceStateTransition stateTransition = appliance.getAllApplianceStateTransitions() .get(i); ps.setInt(1, simulatedApplianceDatabaseId); ps.setLong(2, stateTransition.getTime()); ps.setBoolean(3, stateTransition.isOn()); } public int getBatchSize() { return appliance.getAllApplianceStateTransitions().size(); } }); } }
From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java
@Override public void batchInsertMessageContent(MessageContent messageContent) { logger.debug(messageContent.getChannelId() + "/" + messageContent.getMessageId() + "/" + messageContent.getMetaDataId() + ": batch inserting message content (" + messageContent.getContentType().toString() + ")"); try {/*w w w . ja va 2s . c o m*/ String content; boolean encrypted; // Only encrypt if the content is not already encrypted if (encryptData && encryptor != null && !messageContent.isEncrypted()) { content = encryptor.encrypt(messageContent.getContent()); encrypted = true; } else { content = messageContent.getContent(); encrypted = messageContent.isEncrypted(); } PreparedStatement statement = prepareStatement("batchInsertMessageContent", messageContent.getChannelId()); statement.setInt(1, messageContent.getMetaDataId()); statement.setLong(2, messageContent.getMessageId()); statement.setInt(3, messageContent.getContentType().getContentTypeCode()); statement.setString(4, content); statement.setString(5, messageContent.getDataType()); statement.setBoolean(6, encrypted); statement.addBatch(); statement.clearParameters(); } catch (SQLException e) { throw new DonkeyDaoException(e); } }
From source file:org.osaf.cosmo.migrate.ZeroPointSixOneToZeroPointSevenMigration.java
/** * Calculate time-range index for each event stamp row. *//*from ww w . j a va2s . c o m*/ private void migrateTimeRangeIndexes(Connection conn) throws Exception { PreparedStatement stmt = null; PreparedStatement updateStmt = null; PreparedStatement selectMasterCalStmt = null; ResultSet rs = null; long count = 0; System.setProperty("ical4j.unfolding.relaxed", "true"); CalendarBuilder calBuilder = new CalendarBuilder(); log.debug("starting migrateTimeRangeIndexes()"); try { // get all stamp/item data to migrate stmt = conn.prepareStatement( "select i.modifiesitemid, s.id, es.icaldata from item i, stamp s, event_stamp es where i.id=s.itemid and s.id=es.stampid"); // migration statment updateStmt = conn.prepareStatement( "update event_stamp set isfloating=?, isrecurring=?, startdate=?, enddate=? where stampid=?"); // get the master calendar data for a master event note selectMasterCalStmt = conn.prepareStatement( "select es.icaldata from item i, stamp s, event_stamp es where i.id=? and i.id=s.itemid and s.id=es.stampid"); rs = stmt.executeQuery(); // migrate each event_stamp row while (rs.next()) { long modifiesItemId = rs.getLong(1); long eventId = rs.getLong(2); String icalData = rs.getString(3); Calendar calendar = null; Calendar masterCalendar = null; try { calendar = calBuilder.build(new StringReader(icalData)); } catch (ParserException e) { log.error("unable to parse: " + icalData); throw e; } // Get master calendar if event is a modification if (modifiesItemId != 0) { selectMasterCalStmt.setLong(1, modifiesItemId); ResultSet masterCalRs = selectMasterCalStmt.executeQuery(); masterCalRs.next(); String masterIcalData = masterCalRs.getString(1); try { masterCalendar = calBuilder.build(new StringReader(masterIcalData)); } catch (ParserException e) { log.error("unable to parse: " + masterIcalData); throw e; } masterCalRs.close(); } // calculate and store time-range indexes Object[] indexes = null; try { indexes = getIndexValues(calendar, masterCalendar); } catch (RuntimeException e) { log.error("error processing stampid " + eventId); throw e; } updateStmt.setBoolean(1, (Boolean) indexes[2]); updateStmt.setBoolean(2, (Boolean) indexes[3]); updateStmt.setString(3, (String) indexes[0]); updateStmt.setString(4, (String) indexes[1]); updateStmt.setLong(5, eventId); updateStmt.executeUpdate(); count++; } } finally { close(stmt); close(updateStmt); close(selectMasterCalStmt); } log.debug("processed " + count + " event stamps"); }
From source file:at.alladin.rmbt.controlServer.RegistrationResource.java
@Post("json") public String request(final String entity) { long startTime = System.currentTimeMillis(); final String secret = getContext().getParameters().getFirstValue("RMBT_SECRETKEY"); addAllowOrigin();/*from w w w . ja v a2 s .com*/ JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); String answerString; final String clientIpRaw = getIP(); final InetAddress clientAddress = InetAddresses.forString(clientIpRaw); final String clientIpString = InetAddresses.toAddrString(clientAddress); System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST"), clientIpRaw)); final String geoIpCountry = GeoIPHelper.lookupCountry(clientAddress); // public_ip_asn final Long asn = Helperfunctions.getASN(clientAddress); // public_ip_as_name // country_asn (2 digit country code of AS, eg. AT or EU) final String asName; final String asCountry; if (asn == null) { asName = null; asCountry = null; } else { asName = Helperfunctions.getASName(asn); asCountry = Helperfunctions.getAScountry(asn); } if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); int typeId = 0; final 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)); } // System.out.println(request.toString(4)); if (conn != null) { final Client clientDb = new Client(conn); if (!request.optString("type").isEmpty()) { typeId = clientDb.getTypeId(request.getString("type")); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } final List<String> clientNames = Arrays .asList(settings.getString("RMBT_CLIENT_NAME").split(",\\s*")); final List<String> clientVersions = Arrays .asList(settings.getString("RMBT_VERSION_NUMBER").split(",\\s*")); if (clientNames.contains(request.optString("client")) && clientVersions.contains(request.optString("version")) && typeId > 0) { UUID uuid = null; final String uuidString = request.optString("uuid", ""); if (uuidString.length() != 0) uuid = UUID.fromString(uuidString); final String clientName = request.getString("client"); final String clientVersion = request.getString("version"); String timeZoneId = request.getString("timezone"); // String tmpTimeZoneId = timeZoneId; final long clientTime = request.getLong("time"); final Timestamp clientTstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); final JSONObject location = request.optJSONObject("location"); long geotime = 0; double geolat = 0; double geolong = 0; float geoaccuracy = 0; double geoaltitude = 0; float geobearing = 0; float geospeed = 0; String geoprovider = ""; if (!request.isNull("location")) { geotime = location.optLong("time", 0); geolat = location.optDouble("lat", 0); geolong = location.optDouble("long", 0); geoaccuracy = (float) location.optDouble("accuracy", 0); geoaltitude = location.optDouble("altitude", 0); geobearing = (float) location.optDouble("bearing", 0); geospeed = (float) location.optDouble("speed", 0); geoprovider = location.optString("provider", ""); } Calendar timeWithZone = null; if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); long clientUid = 0; /* * if (uuid == null) { * clientDb.setTimeZone(timeWithZone); * clientDb.setTime(tstamp); * clientDb.setClient_type_id(typeId); uuid = * clientDb.storeClient(); if (clientDb.hasError()) { * errorList.addError(clientDb.getError()); } else { * answer.put("uuid", uuid.toString()); } } */ if (errorList.getLength() == 0 && uuid != null) { clientUid = clientDb.getClientByUuid(uuid); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } if (clientUid > 0) { final String testUuid = UUID.randomUUID().toString(); final String testOpenUuid = UUID.randomUUID().toString(); boolean testServerEncryption = true; // default is // true // hack for android api <= 10 (2.3.x) // using encryption with test doesn't work if (request.has("plattform") && request.optString("plattform").equals("Android")) if (request.has("api_level")) { final String apiLevelString = request.optString("api_level"); try { final int apiLevel = Integer.parseInt(apiLevelString); if (apiLevel <= 10) testServerEncryption = false; } catch (final NumberFormatException e) { } } final String serverType; if (request.optString("client").equals("RMBTws")) serverType = "RMBTws"; else serverType = "RMBT"; final Boolean ipv6; if (clientAddress instanceof Inet6Address) ipv6 = true; else if (clientAddress instanceof Inet4Address) ipv6 = false; else // should never happen, unless ipv > 6 is available ipv6 = null; final TestServer server = getNearestServer(errorList, geolat, geolong, geotime, clientIpString, asCountry, geoIpCountry, serverType, testServerEncryption, ipv6); try { if (server == null) throw new JSONException("could not find server"); if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); answer.put("test_server_address", server.address); answer.put("test_server_port", server.port); answer.put("test_server_name", server.name); answer.put("test_server_encryption", testServerEncryption); answer.put("test_duration", getSetting("rmbt_duration")); answer.put("test_numthreads", getSetting("rmbt_num_threads")); answer.put("test_numpings", getSetting("rmbt_num_pings")); answer.put("client_remote_ip", clientIpString); final String resultUrl = new Reference(getURL(), settings.getString("RMBT_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_url", resultUrl); final String resultQoSUrl = new Reference(getURL(), settings.getString("RMBT_QOS_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_qos_url", resultQoSUrl); } catch (final JSONException e) { System.out.println("Error generating Answer " + e.toString()); errorList.addError("ERROR_RESPONSE_JSON"); } if (errorList.getLength() == 0) try { PreparedStatement st; st = conn.prepareStatement( "INSERT INTO test(time, uuid, open_test_uuid, client_id, client_name, client_version, client_software_version, client_language, client_public_ip, client_public_ip_anonymized, country_geoip, server_id, port, use_ssl, timezone, client_time, duration, num_threads_requested, status, software_revision, client_test_counter, client_previous_test_status, public_ip_asn, public_ip_as_name, country_asn, public_ip_rdns, run_ndt)" + "VALUES(NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); int i = 1; // uuid st.setObject(i++, UUID.fromString(testUuid)); // open_test_uuid st.setObject(i++, UUID.fromString(testOpenUuid)); // client_id st.setLong(i++, clientUid); // client_name st.setString(i++, clientName); // client_version st.setString(i++, clientVersion); // client_software_version st.setString(i++, request.optString("softwareVersion", null)); // client_language st.setString(i++, lang); // client_public_ip st.setString(i++, clientIpString); // client_public_ip_anonymized st.setString(i++, Helperfunctions.anonymizeIp(clientAddress)); // country_geoip (2digit country code derived from public IP of client) st.setString(i++, geoIpCountry); // server_id st.setInt(i++, server.id); // port st.setInt(i++, server.port); // use_ssl st.setBoolean(i++, testServerEncryption); // timezone (of client) st.setString(i++, timeZoneId); // client_time (local time of client) st.setTimestamp(i++, clientTstamp, timeWithZone); // duration (requested) st.setInt(i++, Integer.parseInt(getSetting("rmbt_duration"))); // num_threads_requested st.setInt(i++, Integer.parseInt(getSetting("rmbt_num_threads"))); // status (of test) st.setString(i++, "STARTED"); //was "RUNNING" before // software_revision (of client) st.setString(i++, request.optString("softwareRevision", null)); // client_test_counter (number of tests the client has performed) final int testCounter = request.optInt("testCounter", -1); if (testCounter == -1) // older clients did not support testCounter st.setNull(i++, Types.INTEGER); else st.setLong(i++, testCounter); // client_previous_test_status (outcome of previous test) st.setString(i++, request.optString("previousTestStatus", null)); // AS name if (asn == null) st.setNull(i++, Types.BIGINT); else st.setLong(i++, asn); if (asName == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asName); // AS country if (asCountry == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asCountry); //public_ip_rdns String reverseDNS = Helperfunctions.reverseDNSLookup(clientAddress); if (reverseDNS == null || reverseDNS.isEmpty()) st.setNull(i++, Types.VARCHAR); else { reverseDNS = reverseDNS.replaceFirst("\\.$", ""); st.setString(i++, reverseDNS); // cut off last dot (#332) } // run_ndt if (request.has("ndt")) st.setBoolean(i++, request.getBoolean("ndt")); else st.setNull(i++, Types.BOOLEAN); final int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_STORE_TEST"); else { long key = 0; final ResultSet rs = st.getGeneratedKeys(); if (rs.next()) // Retrieve the auto generated // key(s). key = rs.getLong(1); rs.close(); final PreparedStatement getProviderSt = conn .prepareStatement("SELECT rmbt_set_provider_from_as(?)"); getProviderSt.setLong(1, key); String provider = null; if (getProviderSt.execute()) { final ResultSet rs2 = getProviderSt.getResultSet(); if (rs2.next()) provider = rs2.getString(1); } if (provider != null) answer.put("provider", provider); final PreparedStatement testSlotStatement = conn .prepareStatement("SELECT rmbt_get_next_test_slot(?)"); testSlotStatement.setLong(1, key); int testSlot = -1; if (testSlotStatement.execute()) { final ResultSet rs2 = testSlotStatement.getResultSet(); if (rs2.next()) testSlot = rs2.getInt(1); } if (testSlot < 0) errorList.addError("ERROR_DB_STORE_GENERAL"); else { final String data = testUuid + "_" + testSlot; final String hmac = Helperfunctions.calculateHMAC(secret, data); if (hmac.length() == 0) errorList.addError("ERROR_TEST_TOKEN"); final String token = data + "_" + hmac; final PreparedStatement updateSt = conn .prepareStatement("UPDATE test SET token = ? WHERE uid = ?"); updateSt.setString(1, token); updateSt.setLong(2, key); updateSt.executeUpdate(); answer.put("test_token", token); answer.put("test_uuid", testUuid); answer.put("test_id", key); final long now = System.currentTimeMillis(); int wait = testSlot - (int) (now / 1000); if (wait < 0) wait = 0; answer.put("test_wait", wait); if (geotime != 0 && geolat != 0 && geolong != 0) { final GeoLocation clientLocation = new GeoLocation(conn); clientLocation.setTest_id(key); final Timestamp geotstamp = java.sql.Timestamp .valueOf(new Timestamp(geotime).toString()); clientLocation.setTime(geotstamp, timeZoneId); clientLocation.setAccuracy(geoaccuracy); clientLocation.setAltitude(geoaltitude); clientLocation.setBearing(geobearing); clientLocation.setSpeed(geospeed); clientLocation.setProvider(geoprovider); clientLocation.setGeo_lat(geolat); clientLocation.setGeo_long(geolong); clientLocation.storeLocation(); if (clientLocation.hasError()) errorList.addError(clientLocation.getError()); } } } st.close(); } catch (final SQLException e) { errorList.addError("ERROR_DB_STORE_GENERAL"); e.printStackTrace(); } } else errorList.addError("ERROR_CLIENT_UUID"); } else errorList.addError("ERROR_CLIENT_VERSION"); } else errorList.addError("ERROR_DB_CONNECTION"); // System.out.println(answer.toString(4)); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON 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(); long elapsedTime = System.currentTimeMillis() - startTime; System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST_SUCCESS"), clientIpRaw, Long.toString(elapsedTime))); return answerString; }
From source file:org.apache.marmotta.kiwi.persistence.KiWiConnection.java
/** * Store a triple in the database. This method assumes that all nodes used by the triple are already persisted. * * @param triple the triple to store * @throws SQLException/* w w w . jav a2 s. c o m*/ * @throws NullPointerException in case the subject, predicate, object or context have not been persisted * @return true in case the update added a new triple to the database, false in case the triple already existed */ public synchronized void storeTriple(final KiWiTriple triple) throws SQLException { // mutual exclusion: prevent parallel adding and removing of the same triple synchronized (triple) { requireJDBCConnection(); if (triple.getId() < 0) { triple.setId(getNextSequence()); } if (deletedStatementsLog.mightContain(triple.getId())) { // this is a hack for a concurrency problem that may occur in case the triple is removed in the // transaction and then added again; in these cases the createStatement method might return // an expired state of the triple because it uses its own database connection //deletedStatementsLog.remove(triple.getId()); undeleteTriple(triple); } else { if (batchCommit) { commitLock.lock(); try { cacheTriple(triple); tripleBatch.add(triple); if (tripleBatch.size() >= batchSize) { flushBatch(); } } finally { commitLock.unlock(); } } else { Preconditions.checkNotNull(triple.getSubject().getId()); Preconditions.checkNotNull(triple.getPredicate().getId()); Preconditions.checkNotNull(triple.getObject().getId()); try { RetryExecution<Boolean> execution = new RetryExecution<>("STORE"); execution.setUseSavepoint(true); execution.execute(connection, new RetryCommand<Boolean>() { @Override public Boolean run() throws SQLException { PreparedStatement insertTriple = getPreparedStatement("store.triple"); insertTriple.setLong(1, triple.getId()); insertTriple.setLong(2, triple.getSubject().getId()); insertTriple.setLong(3, triple.getPredicate().getId()); insertTriple.setLong(4, triple.getObject().getId()); if (triple.getContext() != null) { insertTriple.setLong(5, triple.getContext().getId()); } else { insertTriple.setNull(5, Types.BIGINT); } insertTriple.setBoolean(6, triple.isInferred()); insertTriple.setTimestamp(7, new Timestamp(triple.getCreated().getTime())); int count = insertTriple.executeUpdate(); cacheTriple(triple); return count > 0; } }); } catch (SQLException ex) { if ("HYT00".equals(ex.getSQLState())) { // H2 table locking timeout throw new ConcurrentModificationException( "the same triple was modified in concurrent transactions (triple=" + triple + ")"); } else { throw ex; } } } } } }
From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java
private void updateAttendees(AccessToken token, Connection con, Event event) throws SQLException { String q = "update EventLink set eventlink_state=?, eventlink_required=?, eventlink_userupdate=?, eventlink_percent=?, eventlink_is_organizer=? " + "where eventlink_event_id = ? AND eventlink_entity_id = ?"; PreparedStatement ps = null; int[] updatedAttendees; List<Attendee> mightInsert = new LinkedList<Attendee>(); List<Attendee> toInsert = new LinkedList<Attendee>(); try {/* w ww . j av a2 s . com*/ ps = con.prepareStatement(q); for (Attendee at : event.getAttendees()) { int idx = 1; ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, at.getParticipation().getState().toString())); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VROLE, at.getParticipationRole().toString())); ps.setInt(idx++, token.getObmId()); ps.setInt(idx++, at.getPercent()); ps.setBoolean(idx++, at.isOrganizer()); ps.setInt(idx++, event.getObmId().getObmId()); ps.setInt(idx++, at.getEntityId().getId()); ps.addBatch(); mightInsert.add(at); } updatedAttendees = ps.executeBatch(); } finally { obmHelper.cleanup(null, ps, null); } for (int i = 0; i < updatedAttendees.length; i++) { if (updatedAttendees[i] == 0) { Attendee at = mightInsert.get(i); toInsert.add(at); } } logger.info("event modification needs to add " + toInsert.size() + " attendees."); insertAttendees(token, event, con, toInsert); }
From source file:com.flexive.ejb.beans.ACLEngineBean.java
/** * {@inheritDoc}/* www . jav a 2s .c o m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public void update(long id, String name, FxString label, String color, String description, List<ACLAssignment> assignments) throws FxApplicationException { UserTicket ticket = FxContext.getUserTicket(); boolean keepAssignment = assignments == null; // Load the current version of the ACL ACL theACL = load(id); List<ACLAssignment> orgAssignments = null; if (!keepAssignment) orgAssignments = loadAssignments(id); // Check the assignments array: Every group may only be contained once if (assignments == null) { assignments = new ArrayList<ACLAssignment>(0); } else if (assignments.size() > 0) { Hashtable<Long, Boolean> uniqueCheck = new Hashtable<Long, Boolean>(assignments.size()); for (ACLAssignment ass : assignments) { // Group defined more than once in the list? if (uniqueCheck.put(ass.getGroupId(), Boolean.TRUE) != null) { String groupName = "unknown"; try { groupName = CacheAdmin.getEnvironment().getUserGroup(ass.getGroupId()).getName(); } catch (Throwable t) { //ignore } throw new FxUpdateException("ex.aclAssignment.update.groupDefinedMoreThanOnce", theACL.getName(), groupName); } } } // Security if (!ticket.isGlobalSupervisor()) { // Security if (!ticket.isInRole(Role.MandatorSupervisor) && !ticket.isInRole(Role.ACLManagement)) throw new FxNoAccessException(LOG, "ex.acl.updateFailed.noPermission", theACL.getName()); if (ticket.getMandatorId() != theACL.getMandatorId()) throw new FxNoAccessException(LOG, "ex.acl.updateFailed.foreignMandator", theACL.getName()); } // Parameter checks if (color != null) color = FxFormatUtils.processColorString("COLOR", color); if (name != null) name = checkName(name); if (label != null) label = checkLabel(label); if (description != null) description = checkDescription(description); // Set values if (color != null) theACL.setColor(color); if (name != null) theACL.setName(name); if (label != null) theACL.setLabel(label); if (description != null) theACL.setDescription(description); Connection con = null; PreparedStatement stmt = null; String curSql; try { // Obtain a database connection con = Database.getDbConnection(); curSql = "UPDATE " + TBL_ACLS + " SET " + // 1 2 3 4 5 6 "cat_type=?,color=?,description=?,name=?,modified_by=?,modified_at=? WHERE id=" + id; final long NOW = System.currentTimeMillis(); stmt = con.prepareStatement(curSql); stmt.setInt(1, theACL.getCategory().getId()); stmt.setString(2, theACL.getColor()); stmt.setString(3, theACL.getDescription()); stmt.setString(4, theACL.getName()); stmt.setLong(5, ticket.getUserId()); stmt.setLong(6, NOW); final int uCount = stmt.executeUpdate(); stmt.close(); if (uCount != 1) { throw new SQLException(uCount + " rows affected instead of 1"); } Database.storeFxString(theACL.getLabel(), con, TBL_ACLS, "LABEL", "ID", theACL.getId()); final List<Long> affectedUserGroupIds = new ArrayList<Long>(); if (!keepAssignment) { //remove assignments curSql = "DELETE FROM " + TBL_ACLS_ASSIGNMENT + " WHERE ACL=?"; stmt = con.prepareStatement(curSql); stmt.setLong(1, theACL.getId()); stmt.executeUpdate(); if (assignments.size() > 0) { stmt.close(); // 1 2 3 4 5 6 7 8 curSql = "INSERT INTO " + TBL_ACLS_ASSIGNMENT + " (USERGROUP, ACL, PEDIT, PREMOVE, PEXPORT, PREL, PREAD, PCREATE, " + //9 10 11 12 "CREATED_BY, CREATED_AT, MODIFIED_BY, MODIFIED_AT) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"; stmt = con.prepareStatement(curSql); stmt.setLong(2, theACL.getId()); stmt.setLong(9, ticket.getUserId()); stmt.setLong(10, NOW); stmt.setLong(11, ticket.getUserId()); stmt.setLong(12, NOW); for (ACLAssignment assignment : assignments) { affectedUserGroupIds.add(assignment.getGroupId()); stmt.setLong(1, assignment.getGroupId()); stmt.setBoolean(3, assignment.getMayEdit()); stmt.setBoolean(4, assignment.getMayDelete()); stmt.setBoolean(5, assignment.getMayExport()); stmt.setBoolean(6, assignment.getMayRelate()); stmt.setBoolean(7, assignment.getMayRead()); stmt.setBoolean(8, assignment.getMayCreate()); if (assignment.getMayCreate() || assignment.getMayDelete() || assignment.getMayEdit() || assignment.getMayExport() || assignment.getMayRelate() || assignment.getMayRead()) stmt.executeUpdate(); } // update usertickets of affected groups for (Long groupId : affectedUserGroupIds) { UserTicketStore.flagDirtyHavingGroupId(groupId); } } } try { StructureLoader.updateACL(FxContext.get().getDivisionId(), this.load(id)); } catch (FxLoadException e) { LOG.error(e, e); } catch (FxNotFoundException e) { LOG.error(e, e); } StringBuilder sbHistory = new StringBuilder(1000); final XStream xStream = ConversionEngine.getXStream(); sbHistory.append("<original>\n").append(xStream.toXML(theACL)).append("</original>\n"); if (orgAssignments != null) { sbHistory.append("<originalAssignments>\n"); for (ACLAssignment aa : orgAssignments) sbHistory.append(xStream.toXML(aa)); sbHistory.append("</originalAssignments>\n"); } sbHistory.append("<new>\n").append(xStream.toXML(CacheAdmin.getEnvironment().getACL(id))) .append("</new>\n"); if (orgAssignments != null) { sbHistory.append("<newAssignments>\n"); for (ACLAssignment aa : loadAssignments(id)) sbHistory.append(xStream.toXML(aa)); sbHistory.append("</newAssignments>\n"); } EJBLookup.getHistoryTrackerEngine().trackData(sbHistory.toString(), "history.acl.update", theACL.getName()); } catch (SQLException exc) { final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(exc); EJBUtils.rollback(ctx); if (uniqueConstraintViolation) throw new FxEntryExistsException(LOG, "ex.acl.updateFailed.nameTaken", theACL.getName(), name); else throw new FxUpdateException(LOG, "ex.acl.updateFailed", theACL.getName(), exc.getMessage()); } finally { Database.closeObjects(ACLEngineBean.class, con, stmt); } }
From source file:dk.netarkivet.harvester.datamodel.JobDBDAO.java
/** * Creates an instance in persistent storage of the given job. * If the job doesn't have an ID, one is generated for it. * * @param job a given job to add to persistent storage * @throws PermissionDenied If a job already exists in persistent storage * with the same id as the given job * @throws IOFailure If some IOException occurs while * writing the job to persistent storage *//*from w ww. ja va 2 s. c om*/ public synchronized void create(Job job) { ArgumentNotValid.checkNotNull(job, "Job job"); // Check that job.getOrigHarvestDefinitionID() refers to // existing harvestdefinition Long harvestId = job.getOrigHarvestDefinitionID(); if (!HarvestDefinitionDAO.getInstance().exists(harvestId)) { throw new UnknownID("No harvestdefinition with ID=" + harvestId); } Connection connection = HarvestDBConnection.get(); if (job.getJobID() != null) { log.warn("The jobId for the job is already set. " + "This should probably never happen."); } else { job.setJobID(generateNextID(connection)); } if (job.getCreationDate() != null) { log.warn("The creation time for the job is already set. " + "This should probably never happen."); } else { job.setCreationDate(new Date()); } log.debug("Creating " + job.toString()); PreparedStatement statement = null; try { connection.setAutoCommit(false); statement = connection .prepareStatement("INSERT INTO jobs " + "(job_id, harvest_id, status, channel, forcemaxcount, " + "forcemaxbytes, forcemaxrunningtime, orderxml, " + "orderxmldoc, seedlist, " + "harvest_num, startdate, enddate, submitteddate, creationdate, " + "num_configs, edition, resubmitted_as_job, harvestname_prefix, snapshot) " + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," + "?, ?, ?, ?, ?, ?)"); statement.setLong(1, job.getJobID()); statement.setLong(2, job.getOrigHarvestDefinitionID()); statement.setInt(3, job.getStatus().ordinal()); statement.setString(4, job.getChannel()); statement.setLong(5, job.getForceMaxObjectsPerDomain()); statement.setLong(6, job.getMaxBytesPerDomain()); statement.setLong(7, job.getMaxJobRunningTime()); DBUtils.setStringMaxLength(statement, 8, job.getOrderXMLName(), Constants.MAX_NAME_SIZE, job, "order.xml name"); final String orderreader = job.getOrderXMLdoc().asXML(); DBUtils.setClobMaxLength(statement, 9, orderreader, Constants.MAX_ORDERXML_SIZE, job, "order.xml"); DBUtils.setClobMaxLength(statement, 10, job.getSeedListAsString(), Constants.MAX_COMBINED_SEED_LIST_SIZE, job, "seedlist"); statement.setInt(11, job.getHarvestNum()); DBUtils.setDateMaybeNull(statement, 12, job.getActualStart()); DBUtils.setDateMaybeNull(statement, 13, job.getActualStop()); DBUtils.setDateMaybeNull(statement, 14, job.getSubmittedDate()); DBUtils.setDateMaybeNull(statement, 15, job.getCreationDate()); // The size of the configuration map == number of configurations statement.setInt(16, job.getDomainConfigurationMap().size()); long initialEdition = 1; statement.setLong(17, initialEdition); DBUtils.setLongMaybeNull(statement, 18, job.getResubmittedAsJob()); statement.setString(19, job.getHarvestFilenamePrefix()); statement.setBoolean(20, job.isSnapshot()); statement.executeUpdate(); createJobConfigsEntries(connection, job); connection.commit(); job.setEdition(initialEdition); } catch (SQLException e) { String message = "SQL error creating job " + job + " in database" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } finally { DBUtils.rollbackIfNeeded(connection, "create job", job); HarvestDBConnection.release(connection); } }
From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "INSERT" on the given table and and table prefix for * the supplied attributeValues//from ww w. j ava 2s . com * * @param table * @param attributeValues * @return * @throws SQLException */ public boolean insertRow(String table, Map attributeValues) throws SQLException { PreparedStatement prepStmt = null; Object attribute = null; boolean success = false; try { prepStmt = sqlConnection_.prepareStatement(getSQLTableConstants().getInsertStatementSQL(table)); for (int i = 0; i < attributeValues.size(); i++) { attribute = attributeValues.get("" + (i + 1)); // If null, we are unable to determine the SQL param type, // so String is assumed by default. if (attribute == null) { prepStmt.setString(i + 1, null); } else if (attribute instanceof String) { prepStmt.setString(i + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(i + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(i + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(i + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(i + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(i + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(i + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(i + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(i + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(i + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(i + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(i + 1, (Timestamp) attribute); } } success = prepStmt.execute(); } finally { prepStmt.close(); } return success; }
From source file:com.concursive.connect.web.modules.login.dao.User.java
/** * Description of the Method/*from w w w .j a v a2 s . c o m*/ * * @param db The database connection * @param ipAddress The ip address requesting the user to be added * @param prefs The application prefs * @return true if the record was added successfully * @throws SQLException Database exception */ public boolean insert(Connection db, String ipAddress, ApplicationPrefs prefs) throws SQLException { boolean commit = db.getAutoCommit(); try { if (commit) { db.setAutoCommit(false); } // Insert the user PreparedStatement pst = db.prepareStatement("INSERT INTO users " + "(instance_id, group_id, department_id, first_name, last_name, username, password, temporary_password, " + "company, email, enteredby, modifiedby, enabled, start_page, access_personal, access_enterprise, " + "access_admin, access_inbox, access_resources, expiration, registered, " + "account_size, access_invite, access_add_projects, terms, timezone, currency, language" + (entered != null ? ", entered" : "") + (modified != null ? ", modified" : "") + ") " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" + (entered != null ? ", ?" : "") + (modified != null ? ", ?" : "") + ") "); int i = 0; DatabaseUtils.setInt(pst, ++i, instanceId); pst.setInt(++i, groupId); pst.setInt(++i, departmentId); pst.setString(++i, firstName); pst.setString(++i, lastName); pst.setString(++i, username); pst.setString(++i, password); pst.setString(++i, temporaryPassword); pst.setString(++i, company); pst.setString(++i, email); pst.setInt(++i, enteredBy); pst.setInt(++i, modifiedBy); pst.setBoolean(++i, enabled); pst.setInt(++i, startPage); pst.setBoolean(++i, true); pst.setBoolean(++i, true); pst.setBoolean(++i, accessAdmin); pst.setBoolean(++i, false); pst.setBoolean(++i, false); DatabaseUtils.setTimestamp(pst, ++i, expiration); pst.setBoolean(++i, registered); DatabaseUtils.setInt(pst, ++i, accountSize); pst.setBoolean(++i, accessInvite); pst.setBoolean(++i, accessAddProjects); pst.setBoolean(++i, terms); pst.setString(++i, timeZone); pst.setString(++i, currency); pst.setString(++i, language); if (entered != null) { pst.setTimestamp(++i, entered); } if (modified != null) { pst.setTimestamp(++i, modified); } pst.execute(); pst.close(); id = DatabaseUtils.getCurrVal(db, "users_user_id_seq", -1); // Record the IP if (ipAddress != null) { pst = db.prepareStatement("INSERT INTO user_log (user_id, ip_address) VALUES (?, ?)"); pst.setInt(1, id); pst.setString(2, ipAddress); pst.execute(); pst.close(); } if (!isApiRestore()) { // Insert a corresponding user profile project UserUtils.addUserProfile(db, this, prefs); if (profileProjectId == -1) { LOG.error("profileProjectId did not get assigned!"); } } if (commit) { db.commit(); } } catch (Exception e) { LOG.error("adding user", e); if (commit) { db.rollback(); } throw new SQLException(e.getMessage()); } finally { if (commit) { db.setAutoCommit(true); } } return true; }