List of usage examples for java.sql PreparedStatement setNull
void setNull(int parameterIndex, int sqlType) throws SQLException;
NULL. From source file:com.funambol.foundation.items.dao.PIMContactDAO.java
/** * Updates the property 'photoType' of the contact with the given id to the * given value using the given connection. * <br/>//from ww w .j a v a 2s. c o m * The contact is moreover marked as UPDATED and its last_update is updated * @param con the connection to use * @param contactId the contact to update * @param photoType the type of the photo * @return the number of the updated rows * @throws DAOException if an error occurs */ private int setPhotoType(Connection con, Long contactId, Short photoType) throws DAOException { if (!ContactWrapper.EMPTY_PHOTO.equals(photoType) && !ContactWrapper.PHOTO_IMAGE.equals(photoType) && !ContactWrapper.PHOTO_URL.equals(photoType) && photoType != null) { throw new IllegalArgumentException(photoType + " is not a valid photoType"); } PreparedStatement stmt = null; int numUpdatedRows = 0; try { stmt = con.prepareStatement(SQL_UPDATE_FNBL_PIM_CONTACT_PHOTO_TYPE); stmt.setString(1, String.valueOf(Def.PIM_STATE_UPDATED)); stmt.setLong(2, System.currentTimeMillis()); if (photoType != null) { stmt.setShort(3, photoType); } else { stmt.setNull(3, Types.SMALLINT); } stmt.setLong(4, contactId); stmt.setString(5, userId); numUpdatedRows = stmt.executeUpdate(); } catch (SQLException ex) { throw new DAOException("Error updating '" + contactId + "'", ex); } finally { DBTools.close(null, stmt, null); } return numUpdatedRows; }
From source file:org.jamwiki.db.AnsiQueryHandler.java
/** * */// w w w . jav a2 s . c om public void insertUserBlock(UserBlock userBlock, Connection conn) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; try { int index = 1; if (!this.autoIncrementPrimaryKeys()) { stmt = conn.prepareStatement(STATEMENT_INSERT_USER_BLOCK); int blockId = this.nextUserBlockId(conn); userBlock.setBlockId(blockId); stmt.setInt(index++, userBlock.getBlockId()); } else { stmt = conn.prepareStatement(STATEMENT_INSERT_USER_BLOCK_AUTO_INCREMENT, Statement.RETURN_GENERATED_KEYS); } if (userBlock.getWikiUserId() == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, userBlock.getWikiUserId()); } stmt.setString(index++, userBlock.getIpAddress()); stmt.setTimestamp(index++, userBlock.getBlockDate()); stmt.setTimestamp(index++, userBlock.getBlockEndDate()); stmt.setString(index++, userBlock.getBlockReason()); stmt.setInt(index++, userBlock.getBlockedByUserId()); stmt.setTimestamp(index++, userBlock.getUnblockDate()); stmt.setString(index++, userBlock.getUnblockReason()); if (userBlock.getUnblockedByUserId() == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, userBlock.getUnblockedByUserId()); } stmt.executeUpdate(); if (this.autoIncrementPrimaryKeys()) { rs = stmt.getGeneratedKeys(); if (!rs.next()) { throw new SQLException("Unable to determine auto-generated ID for database record"); } userBlock.setBlockId(rs.getInt(1)); } } finally { // close only the statement and result set - leave the connection open for further use DatabaseConnection.closeConnection(null, stmt, rs); } }
From source file:com.funambol.foundation.items.dao.PIMContactDAO.java
/** * Updates the given photo for the contact identified by the given id using * the given connection./* w w w . j av a2 s . c om*/ * Note the contact is NOT updated accordlying. Use updateContactPhoto to * update also the contact. * @param con the connection to use * @param id the contact id * @param photo the photo to update * @return true if the photo has been updated, false * @throws com.funambol.foundation.exception.DAOException if an error * occurs */ protected boolean updatePhoto(Connection con, Long id, Photo photo) throws DAOException { if (!verifyPermission(con, id)) { throw new DAOException("Contact '" + id + " is not a contact of the user '" + userId + "'"); } PreparedStatement stmt = null; byte[] image = null; String type = null; String url = null; if (photo != null) { image = photo.getImage(); type = photo.getType(); url = photo.getUrl(); } int numUpdatedRows = 0; try { stmt = con.prepareStatement(SQL_UPDATE_FNBL_PIM_CONTACT_PHOTO); if (type == null) { stmt.setNull(1, Types.VARCHAR); } else { stmt.setString(1, type); } if (url == null) { stmt.setNull(2, Types.VARCHAR); } else { stmt.setString(2, url); } if (image == null) { stmt.setNull(3, Types.BINARY); } else { stmt.setBinaryStream(3, new ByteArrayInputStream(image), image.length); } stmt.setLong(4, id); numUpdatedRows = stmt.executeUpdate(); } catch (SQLException ex) { throw new DAOException("Error updating photo", ex); } finally { DBTools.close(null, stmt, null); } return (numUpdatedRows == 1); }
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();// w ww . j av a 2 s.c o m 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:axiom.objectmodel.db.NodeManager.java
/** * Insert a node into a relational database. *///from w w w .j a va2 s. com protected void insertRelationalNodeInto(Node node, DbMapping dbm, Connection con, int tableNumber) throws ClassNotFoundException, SQLException { if (con == null) { throw new NullPointerException("Error inserting relational node: Connection is null"); } // set connection to write mode if (con.isReadOnly()) con.setReadOnly(false); String insertString = dbm.getInsert(tableNumber); PreparedStatement stmt = con.prepareStatement(insertString); DbColumn[] columns = dbm.getColumns(tableNumber); String nameField = dbm.getNameField(); String prototypeField = dbm.getPrototypeField(); String idField = dbm.getTableKey(tableNumber); long logTimeStart = logSql ? System.currentTimeMillis() : 0; try { int stmtNumber = 1; // first column of insert statement is always the primary key // Changed to get the primary key value from the db mapping, // in the case where a particular table's primary key is not the primary key // of the main table in the db mapping. stmt.setString(stmtNumber, dbm.getPrimaryKeyValue(node, tableNumber)); //node.getID()); Hashtable propMap = node.getPropMap(); for (int i = 0; i < columns.length; i++) { Relation rel = columns[i].getRelation(); Property p = null; if (rel != null && propMap != null && (rel.isPrimitive() || rel.isReference())) { p = (Property) propMap.get(rel.getPropName()); } String name = columns[i].getName(); if (name.equals(idField)) { continue; } //Ensure the id is not repeated. if (!((rel != null) && (rel.isPrimitive() || rel.isReference())) && !name.equalsIgnoreCase(nameField) && !name.equalsIgnoreCase(prototypeField)) { continue; } stmtNumber++; if (p != null) { this.setStatementValues(stmt, stmtNumber, p, columns[i].getType()); } else if (name.equalsIgnoreCase(nameField)) { stmt.setString(stmtNumber, node.getName()); } else if (name.equalsIgnoreCase(prototypeField)) { stmt.setString(stmtNumber, node.getPrototype()); } else { stmt.setNull(stmtNumber, columns[i].getType()); } } stmt.executeUpdate(); // node.setInteger("id", new Integer(node.getID()).longValue()); } finally { if (logSql) { long logTimeStop = java.lang.System.currentTimeMillis(); logSqlStatement("SQL INSERT", dbm.getTableName(), logTimeStart, logTimeStop, insertString); } if (stmt != null) { try { stmt.close(); } catch (Exception ex) { app.logError(ErrorReporter.errorMsg(this.getClass(), "insertRelationalNodeInto"), ex); } } } }
From source file:com.enonic.vertical.engine.handlers.ContentObjectHandler.java
public void updateContentObject(Document doc) throws VerticalUpdateException { Element docElem = doc.getDocumentElement(); Element[] contentobjectElems; if ("contentobject".equals(docElem.getTagName())) { contentobjectElems = new Element[] { docElem }; } else {// w w w . j ava 2s . c om contentobjectElems = XMLTool.getElements(doc.getDocumentElement()); } Connection con = null; PreparedStatement preparedStmt = null; int pos = 0; String tmpStr = null; try { con = getConnection(); preparedStmt = con.prepareStatement(COB_UPDATE); for (Element root : contentobjectElems) { Map subelems = XMLTool.filterElements(root.getChildNodes()); int key = -1, menuKey = -1; ResourceKey styleSheetKey = null, borderStyleSheetKey = null; pos = 0; tmpStr = root.getAttribute("key"); if (tmpStr != null && tmpStr.length() > 0) { key = Integer.parseInt(tmpStr); } else { String message = "No content object key specified."; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, null); } pos++; // was sitekey pos++; tmpStr = root.getAttribute("menukey"); if (tmpStr != null && tmpStr.length() > 0) { menuKey = Integer.parseInt(tmpStr); } else { String message = "No menu key specified."; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, null); } pos++; Element subelem = (Element) subelems.get("objectstylesheet"); if (subelem != null) { tmpStr = subelem.getAttribute("key"); if (tmpStr != null && tmpStr.length() > 0) { styleSheetKey = new ResourceKey(tmpStr); } else { String message = "No object stylesheet key specified."; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, null); } } else { String message = "No object stylesheet specified."; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, null); } pos++; subelem = (Element) subelems.get("borderstylesheet"); if (subelem != null) { tmpStr = subelem.getAttribute("key"); if (tmpStr != null && tmpStr.length() > 0) { borderStyleSheetKey = new ResourceKey(tmpStr); } } // element: name String name = null; subelem = (Element) subelems.get("name"); if (subelem != null) { name = XMLTool.getElementText(subelem); if (name == null || name.length() == 0) { String message = "Empty stylesheet name."; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, null); } } else { String message = "No stylesheet name specified."; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, null); } // element: contentobjectdata (optional) byte[] contentobjectdata; subelem = (Element) subelems.get("contentobjectdata"); if (subelem != null) { Document codDoc = XMLTool.createDocument(); codDoc.appendChild(codDoc.importNode(subelem, true)); contentobjectdata = XMLTool.documentToBytes(codDoc, "UTF-8"); } else { contentobjectdata = null; } preparedStmt.setInt(7, key); preparedStmt.setInt(1, menuKey); if (styleSheetKey != null) { preparedStmt.setString(2, styleSheetKey.toString()); } else { preparedStmt.setNull(2, Types.VARCHAR); } if (borderStyleSheetKey != null) { preparedStmt.setString(3, borderStyleSheetKey.toString()); } else { preparedStmt.setNull(3, Types.VARCHAR); } preparedStmt.setCharacterStream(4, new StringReader(name), name.length()); if (contentobjectdata != null) { preparedStmt.setBinaryStream(5, new ByteArrayInputStream(contentobjectdata), contentobjectdata.length); } else { preparedStmt.setNull(5, Types.VARCHAR); } RunAsType runAs = RunAsType.INHERIT; String runAsStr = root.getAttribute("runAs"); if (StringUtils.isNotEmpty(runAsStr)) { runAs = RunAsType.valueOf(runAsStr); } preparedStmt.setInt(6, runAs.getKey()); // update content object int result = preparedStmt.executeUpdate(); if (result <= 0) { String message = "Failed to update content object, no content object updated."; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, null); } } preparedStmt.close(); preparedStmt = null; } catch (SQLException sqle) { String message = "Failed to update content object(s): %t"; VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, sqle); } catch (NumberFormatException nfe) { String message = "Failed to parse %0: %1"; Object[] msgData; switch (pos) { case 1: msgData = new Object[] { "site key", tmpStr }; break; case 2: msgData = new Object[] { "menu key", tmpStr }; break; case 3: msgData = new Object[] { "object stylesheet key", tmpStr }; break; case 4: msgData = new Object[] { "border stylesheet key", tmpStr }; break; default: msgData = new Object[] { "content object key", tmpStr }; } VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, msgData, nfe); } finally { close(preparedStmt); close(con); } }
From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * Creates a property assignment// ww w. j a v a 2 s. c o m * * @param con a valid and open connection * @param sql an instance of StringBuilder * @param pa an instance of FxPropertyAssignmentEdit to be persisted * @return the property assignmentId * @throws FxApplicationException on errors */ private long createPropertyAssignment(Connection con, StringBuilder sql, FxPropertyAssignmentEdit pa) throws FxApplicationException { if (!pa.isNew()) throw new FxInvalidParameterException("ex.structure.assignment.create.existing", pa.getXPath()); if (sql == null) { sql = new StringBuilder(1000); } PreparedStatement ps = null; long newAssignmentId; try { sql.setLength(0); sql.append("INSERT INTO ").append(TBL_STRUCT_ASSIGNMENTS). // 1 2 3 4 5 6 7 8 9 10 11 12 13 append("(ID,ATYPE,ENABLED,TYPEDEF,MINMULT,MAXMULT,DEFMULT,POS,XPATH,XALIAS,BASE,PARENTGROUP,APROPERTY," + //14 15 16 17 "ACL,DEFLANG,SYSINTERNAL,DEFAULT_VALUE)" + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps = con.prepareStatement(sql.toString()); newAssignmentId = seq.getId(FxSystemSequencer.ASSIGNMENT); ps.setLong(1, newAssignmentId); ps.setInt(2, FxAssignment.TYPE_PROPERTY); ps.setBoolean(3, pa.isEnabled()); ps.setLong(4, pa.getAssignedType().getId()); ps.setInt(5, pa.getMultiplicity().getMin()); ps.setInt(6, pa.getMultiplicity().getMax()); ps.setInt(7, pa.getDefaultMultiplicity()); int position = getValidPosition(con, sql, pa.getPosition(), pa.getAssignedType().getId(), pa.getParentGroupAssignment()); ps.setInt(8, position); String XPath; if (!pa.getXPath().startsWith(pa.getAssignedType().getName())) XPath = XPathElement.buildXPath(false, pa.getAssignedType().getName(), pa.getXPath()); else XPath = pa.getXPath(); ps.setString(9, XPath); ps.setString(10, pa.getAlias()); if (pa.getBaseAssignmentId() == FxAssignment.NO_BASE) ps.setNull(11, Types.NUMERIC); else ps.setLong(11, pa.getBaseAssignmentId()); ps.setLong(12, pa.getParentGroupAssignment() == null ? FxAssignment.NO_PARENT : pa.getParentGroupAssignment().getId()); ps.setLong(13, pa.getProperty().getId()); ps.setLong(14, pa.getACL().getId()); ps.setInt(15, pa.hasDefaultLanguage() ? (int) pa.getDefaultLanguage() : (int) FxLanguage.SYSTEM_ID); ps.setBoolean(16, pa.isSystemInternal()); FxValue defValue = pa.getDefaultValue(); if (defValue instanceof FxBinary) { ContentStorage storage = StorageManager.getContentStorage(pa.getAssignedType().getStorageMode()); storage.prepareBinary(con, (FxBinary) defValue); } final String _def = defValue == null || defValue.isEmpty() ? null : ConversionEngine.getXStream().toXML(defValue); if (_def == null) ps.setNull(17, java.sql.Types.VARCHAR); else ps.setString(17, _def); ps.executeUpdate(); ps.close(); Database.storeFxString(new FxString[] { pa.getLabel(), pa.getHint() }, con, TBL_STRUCT_ASSIGNMENTS, new String[] { "DESCRIPTION", "HINT" }, "ID", newAssignmentId); htracker.track(pa.getAssignedType(), "history.assignment.createPropertyAssignment", XPath, pa.getAssignedType().getId(), pa.getAssignedType().getName(), pa.getProperty().getId(), pa.getProperty().getName()); // FxStructureOption inheritance boolean isInheritedAssignment = FxSharedUtils.checkAssignmentInherited(pa); if (isInheritedAssignment) { // FxStructureOptions - retrieve only those with an activated "isInherited" flag final List<FxStructureOption> inheritedOpts = FxStructureOption.cloneOptions(pa.getOptions(), true); if (inheritedOpts.size() > 0) { storeOptions(con, TBL_STRUCT_PROPERTY_OPTIONS, "ID", pa.getProperty().getId(), newAssignmentId, inheritedOpts); } } else { storeOptions(con, TBL_STRUCT_PROPERTY_OPTIONS, "ID", pa.getProperty().getId(), newAssignmentId, pa.getOptions()); } setAssignmentPosition(con, newAssignmentId, pa.getPosition()); if (!pa.isSystemInternal()) { if (divisionConfig.isFlatStorageEnabled() && divisionConfig.get(SystemParameters.FLATSTORAGE_AUTO)) { final FxFlatStorage fs = FxFlatStorageManager.getInstance(); if (fs.isFlattenable(pa)) { try { StructureLoader.reload(con); } catch (FxCacheException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e, "ex.cache", e.getMessage()); } fs.flatten(con, fs.getDefaultStorage(), (FxPropertyAssignment) CacheAdmin.getEnvironment().getAssignment(newAssignmentId)); } } //only need a reload and inheritance handling if the property is not system internal //since system internal properties are only created from the type engine we don't have to care try { StructureLoader.reloadAssignments(FxContext.get().getDivisionId()); } catch (FxApplicationException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e, "ex.cache", e.getMessage()); } if (pa.getAssignedType().getId() != FxType.ROOT_ID) createInheritedAssignments(CacheAdmin.getEnvironment().getAssignment(newAssignmentId), con, sql, pa.getAssignedType().getDerivedTypes()); } } catch (SQLException e) { final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(e); if (!ctx.getRollbackOnly()) EJBUtils.rollback(ctx); if (uniqueConstraintViolation) throw new FxEntryExistsException("ex.structure.assignment.property.exists", pa.getAlias(), pa.getAssignedType().getName() + pa.getXPath()); throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(AssignmentEngineBean.class, null, ps); } return newAssignmentId; }
From source file:org.jasig.portal.layout.dlm.RDBMDistributedLayoutStore.java
@Override protected int saveStructure(Node node, PreparedStatement structStmt, PreparedStatement parmStmt) throws SQLException { if (node == null) { // No more return 0; }//from w w w . ja v a 2 s. co m if (node.getNodeName().equals("parameter")) { //parameter, skip it and go on to the next node return this.saveStructure(node.getNextSibling(), structStmt, parmStmt); } if (!(node instanceof Element)) { return 0; } final Element structure = (Element) node; if (LOG.isDebugEnabled()) { LOG.debug("saveStructure XML content: " + XmlUtilitiesImpl.toString(node)); } // determine the struct_id for storing in the db. For incorporated nodes in // the plf their ID is a system-wide unique ID while their struct_id for // storing in the db is cached in a dlm:plfID attribute. int saveStructId = -1; final String plfID = structure.getAttribute(Constants.ATT_PLF_ID); if (!plfID.equals("")) { saveStructId = Integer.parseInt(plfID.substring(1)); } else { final String id = structure.getAttribute("ID"); saveStructId = Integer.parseInt(id.substring(1)); } int nextStructId = 0; int childStructId = 0; int chanId = -1; IPortletDefinition portletDef = null; final boolean isChannel = node.getNodeName().equals("channel"); if (isChannel) { chanId = Integer.parseInt(node.getAttributes().getNamedItem("chanID").getNodeValue()); portletDef = this.portletDefinitionRegistry.getPortletDefinition(String.valueOf(chanId)); if (portletDef == null) { //Portlet doesn't exist any more, drop the layout node return 0; } } if (node.hasChildNodes()) { childStructId = this.saveStructure(node.getFirstChild(), structStmt, parmStmt); } nextStructId = this.saveStructure(node.getNextSibling(), structStmt, parmStmt); structStmt.clearParameters(); structStmt.setInt(1, saveStructId); structStmt.setInt(2, nextStructId); structStmt.setInt(3, childStructId); final String externalId = structure.getAttribute("external_id"); if (externalId != null && externalId.trim().length() > 0) { final Integer eID = new Integer(externalId); structStmt.setInt(4, eID.intValue()); } else { structStmt.setNull(4, java.sql.Types.NUMERIC); } if (isChannel) { structStmt.setInt(5, chanId); structStmt.setNull(6, java.sql.Types.VARCHAR); } else { structStmt.setNull(5, java.sql.Types.NUMERIC); structStmt.setString(6, structure.getAttribute("name")); } final String structType = structure.getAttribute("type"); structStmt.setString(7, structType); structStmt.setString(8, RDBMServices.dbFlag(xmlBool(structure.getAttribute("hidden")))); structStmt.setString(9, RDBMServices.dbFlag(xmlBool(structure.getAttribute("immutable")))); structStmt.setString(10, RDBMServices.dbFlag(xmlBool(structure.getAttribute("unremovable")))); if (LOG.isDebugEnabled()) { LOG.debug(structStmt.toString()); } structStmt.executeUpdate(); // code to persist extension attributes for dlm final NamedNodeMap attribs = node.getAttributes(); for (int i = 0; i < attribs.getLength(); i++) { final Node attrib = attribs.item(i); final String name = attrib.getNodeName(); if (name.startsWith(Constants.NS) && !name.equals(Constants.ATT_PLF_ID) && !name.equals(Constants.ATT_FRAGMENT) && !name.equals(Constants.ATT_PRECEDENCE)) { // a cp extension attribute. Push into param table. parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, name); parmStmt.setString(3, attrib.getNodeValue()); if (LOG.isDebugEnabled()) { LOG.debug(parmStmt.toString()); } parmStmt.executeUpdate(); } } final NodeList parameters = node.getChildNodes(); if (parameters != null && isChannel) { for (int i = 0; i < parameters.getLength(); i++) { if (parameters.item(i).getNodeName().equals("parameter")) { final Element parmElement = (Element) parameters.item(i); final NamedNodeMap nm = parmElement.getAttributes(); final String parmName = nm.getNamedItem("name").getNodeValue(); final String parmValue = nm.getNamedItem("value").getNodeValue(); final Node override = nm.getNamedItem("override"); // if no override specified then default to allowed if (override != null && !override.getNodeValue().equals("yes")) { // can't override } else { // override only for adhoc or if diff from chan def final IPortletDefinitionParameter cp = portletDef.getParameter(parmName); if (cp == null || !cp.getValue().equals(parmValue)) { parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, parmName); parmStmt.setString(3, parmValue); if (LOG.isDebugEnabled()) { LOG.debug(parmStmt); } parmStmt.executeUpdate(); } } } } } return saveStructId; }
From source file:com.zimbra.cs.db.DbMailItem.java
public static void reparentChildren(MailItem oldParent, MailItem newParent) throws ServiceException { if (oldParent == newParent) { return;/* w w w.j ava 2s. com*/ } Mailbox mbox = oldParent.getMailbox(); if (mbox != newParent.getMailbox()) { throw MailServiceException.WRONG_MAILBOX(); } DbConnection conn = mbox.getOperationConnection(); PreparedStatement stmt = null; try { String relation = (oldParent instanceof VirtualConversation ? "id = ?" : "parent_id = ?"); stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(oldParent) + " SET parent_id = ?, mod_metadata = ?, change_date = ?" + " WHERE " + IN_THIS_MAILBOX_AND + relation); int pos = 1; if (newParent instanceof VirtualConversation) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, newParent.getId()); } stmt.setInt(pos++, mbox.getOperationChangeID()); stmt.setInt(pos++, mbox.getOperationTimestamp()); pos = setMailboxId(stmt, mbox, pos); stmt.setInt(pos++, oldParent instanceof VirtualConversation ? ((VirtualConversation) oldParent).getMessageId() : oldParent.getId()); stmt.executeUpdate(); } catch (SQLException e) { throw ServiceException.FAILURE("writing new parent for children of item " + oldParent.getId(), e); } finally { DbPool.closeStatement(stmt); } }
From source file:org.jamwiki.db.AnsiQueryHandler.java
/** * *///from ww w . j a va 2 s .c o m public void insertTopic(Topic topic, int virtualWikiId, Connection conn) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; try { int index = 1; if (!this.autoIncrementPrimaryKeys()) { stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC); int topicId = this.nextTopicId(conn); topic.setTopicId(topicId); stmt.setInt(index++, topic.getTopicId()); } else { stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_AUTO_INCREMENT, Statement.RETURN_GENERATED_KEYS); } stmt.setInt(index++, virtualWikiId); stmt.setString(index++, topic.getName()); stmt.setInt(index++, topic.getTopicType().id()); stmt.setInt(index++, (topic.getReadOnly() ? 1 : 0)); if (topic.getCurrentVersionId() == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, topic.getCurrentVersionId()); } stmt.setTimestamp(index++, topic.getDeleteDate()); stmt.setInt(index++, (topic.getAdminOnly() ? 1 : 0)); stmt.setString(index++, topic.getRedirectTo()); stmt.setInt(index++, topic.getNamespace().getId()); stmt.setString(index++, topic.getPageName()); stmt.setString(index++, topic.getPageName().toLowerCase()); stmt.executeUpdate(); if (this.autoIncrementPrimaryKeys()) { rs = stmt.getGeneratedKeys(); if (!rs.next()) { throw new SQLException("Unable to determine auto-generated ID for database record"); } topic.setTopicId(rs.getInt(1)); } } finally { // close only the statement and result set - leave the connection open for further use DatabaseConnection.closeConnection(null, stmt, rs); } }