List of usage examples for java.sql PreparedStatement setNull
void setNull(int parameterIndex, int sqlType) throws SQLException;
NULL. From source file:com.zimbra.cs.db.DbMailItem.java
public static String copy(MailItem item, int id, String uuid, Folder folder, int indexId, int parentId, String locator, String metadata, boolean fromDumpster) throws ServiceException { Mailbox mbox = item.getMailbox();/*from w w w . j a v a 2s . c om*/ String prevFolders = null; if (id <= 0 || folder == null || parentId == 0) { throw ServiceException.FAILURE("invalid data for DB item copy", null); } checkNamingConstraint(mbox, folder.getId(), item.getName(), id); DbConnection conn = mbox.getOperationConnection(); PreparedStatement stmt = null; try { String srcTable = getMailItemTableName(mbox, fromDumpster); String destTable = getMailItemTableName(mbox); String mailbox_id = DebugConfig.disableMailboxGroups ? "" : "mailbox_id, "; stmt = conn.prepareStatement("INSERT INTO " + destTable + "(" + mailbox_id + " id, type, parent_id, folder_id, prev_folders, index_id, imap_id, date, size, locator, blob_digest," + " unread, flags, tag_names, sender, subject, name, metadata, mod_metadata, change_date, mod_content, uuid) " + "SELECT " + MAILBOX_ID_VALUE + " ?, type, ?, ?, ?, ?, ?, date, size, ?, blob_digest, unread," + " flags, tag_names, sender, subject, name, ?, ?, ?, ?, ? FROM " + srcTable + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?"); int pos = 1; pos = setMailboxId(stmt, mbox, pos); stmt.setInt(pos++, id); // ID if (parentId <= 0) { stmt.setNull(pos++, Types.INTEGER); // PARENT_ID null for messages in virtual convs } else { stmt.setInt(pos++, parentId); // or, PARENT_ID specified by caller } stmt.setInt(pos++, folder.getId()); // FOLDER_ID int modseq = mbox.getOperationChangeID(); prevFolders = findPrevFolders(item, modseq); stmt.setString(pos++, prevFolders); if (indexId == MailItem.IndexStatus.NO.id()) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, indexId); } stmt.setInt(pos++, id); // IMAP_ID is initially the same as ID stmt.setString(pos++, locator); stmt.setString(pos++, checkMetadataLength(metadata)); // METADATA stmt.setInt(pos++, modseq); // MOD_METADATA stmt.setInt(pos++, mbox.getOperationTimestamp()); // CHANGE_DATE stmt.setInt(pos++, mbox.getOperationChangeID()); // MOD_CONTENT stmt.setString(pos++, uuid); // UUID pos = setMailboxId(stmt, mbox, pos); stmt.setInt(pos++, item.getId()); int num = stmt.executeUpdate(); if (num != 1) { throw ServiceException.FAILURE("failed to create object", null); } DbTag.storeTagReferences(mbox, id, item.getType(), item.getInternalFlagBitmask(), item.isUnread()); DbTag.storeTagReferences(mbox, id, item.getType(), item.getTags()); } catch (SQLException e) { // catch item_id uniqueness constraint violation and return failure if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) { throw MailServiceException.ALREADY_EXISTS(id, e); } else { throw ServiceException.FAILURE("copying " + item.getType() + ": " + item.getId(), e); } } finally { DbPool.closeStatement(stmt); } return prevFolders; }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Set null as a parameter to the statement. The column * type will come from {@link Types}./*from w w w . j a v a 2 s . c om*/ */ public void setNull(PreparedStatement stmnt, int idx, int colType, Column col) throws SQLException { stmnt.setNull(idx, colType); }
From source file:org.exoplatform.social.core.mysql.storage.ActivityMysqlStorageImpl.java
@Override public void saveComment(ExoSocialActivity activity, ExoSocialActivity comment) throws ActivityStorageException { LOG.debug("begin to create comment"); // insert to mysql comment table Connection dbConnection = null; PreparedStatement preparedStatement = null; StringBuilder insertTableSQL = new StringBuilder(); insertTableSQL.append("INSERT INTO comment").append( "(_id, activityId, title, titleId, body, bodyId, postedTime, lastUpdated, posterId, mentioners, ") .append("hidable, lockable, templateParams)").append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"); StringBuilder updateActivitySQL = new StringBuilder(); updateActivitySQL.append(//from w ww . j a v a 2 s .co m "update activity set lastUpdated = ?, mentioners = ?, commenters = ?, commentIds = ? where _id = ?"); long currentMillis = System.currentTimeMillis(); long commentMillis = (comment.getPostedTime() != null ? comment.getPostedTime() : currentMillis); List<String> mentioners = new ArrayList<String>(); activity.setMentionedIds(processMentions(activity.getMentionedIds(), comment.getTitle(), mentioners, true)); List<String> commenters = new ArrayList<String>(Arrays.asList(activity.getCommentedIds())); if (comment.getUserId() != null && !commenters.contains(comment.getUserId())) { commenters.add(comment.getUserId()); } activity.setCommentedIds(commenters.toArray(new String[0])); comment.setMentionedIds(processMentions(comment.getTitle())); try { dbConnection = dbConnect.getDBConnection(); // insert comment preparedStatement = dbConnection.prepareStatement(insertTableSQL.toString()); comment.setId(UUID.randomUUID().toString()); preparedStatement.setString(1, comment.getId()); preparedStatement.setString(2, activity.getId()); preparedStatement.setString(3, comment.getTitle()); preparedStatement.setString(4, comment.getTitleId()); preparedStatement.setString(5, comment.getBody()); preparedStatement.setString(6, comment.getBodyId()); preparedStatement.setLong(7, commentMillis); preparedStatement.setLong(8, commentMillis); preparedStatement.setString(9, comment.getUserId()); preparedStatement.setString(10, StringUtils.join(comment.getMentionedIds(), ",")); preparedStatement.setBoolean(11, activity.isHidden()); preparedStatement.setBoolean(12, activity.isLocked()); // if (comment.getTemplateParams() != null) { try { ByteArrayOutputStream b = new ByteArrayOutputStream(); ObjectOutputStream output = new ObjectOutputStream(b); output.writeObject(comment.getTemplateParams()); preparedStatement.setBinaryStream(13, new ByteArrayInputStream(b.toByteArray())); } catch (IOException e) { LOG.debug("Failed to save templateParams of activity into database"); } } else { preparedStatement.setNull(13, Types.BLOB); } preparedStatement.executeUpdate(); LOG.debug("new comment created"); // List<String> commentIds = new ArrayList(Arrays.asList(activity.getReplyToId())); commentIds.add(comment.getId()); // update activity preparedStatement = dbConnection.prepareStatement(updateActivitySQL.toString()); preparedStatement.setLong(1, commentMillis); preparedStatement.setString(2, StringUtils.join(activity.getMentionedIds(), ",")); preparedStatement.setString(3, StringUtils.join(activity.getCommentedIds(), ",")); preparedStatement.setString(4, StringUtils.join(commentIds, ",")); preparedStatement.setString(5, activity.getId()); preparedStatement.executeUpdate(); LOG.debug("activity updated"); activity.setReplyToId(commentIds.toArray(new String[commentIds.size()])); activity.setUpdated(currentMillis); } catch (SQLException e) { LOG.error("error in comment creation:", e.getMessage()); } finally { try { if (preparedStatement != null) { preparedStatement.close(); } if (dbConnection != null) { dbConnection.close(); } } catch (SQLException e) { LOG.error("Cannot close statement or connection:", e.getMessage()); } } comment.setUpdated(commentMillis); Identity poster = new Identity(activity.getPosterId()); poster.setRemoteId(activity.getStreamOwner()); commenter(poster, activity, comment); updateMentioner(poster, activity, comment); }
From source file:com.enonic.vertical.engine.handlers.MenuHandler.java
private int createMenuItem(User user, CopyContext copyContext, Element menuItemElement, SiteKey siteKey, int order, MenuItemKey parentKey, boolean useOldKey) throws VerticalCreateException, VerticalSecurityException { // security check: if (!getSecurityHandler().validateMenuItemCreate(user, siteKey.toInt(), parentKey == null ? -1 : parentKey.toInt())) { VerticalEngineLogger.errorSecurity(this.getClass(), 10, "Not allowed to create menuitem in this position.", null); }//w ww.j a va2s .co m String menuItemName = XMLTool .getElementText(XMLTool.getElement(menuItemElement, ELEMENT_NAME_MENUITEM_NAME)); if (StringUtils.isEmpty(menuItemName)) { menuItemName = generateMenuItemName(menuItemElement); } menuItemName = ensureUniqueMenuItemName(siteKey, parentKey, menuItemName, null); // check whether name is unique for this parent if (menuItemNameExists(siteKey, parentKey, menuItemName, null)) { VerticalEngineLogger.errorCreate(this.getClass(), 20, "Menu item name already exists on this level: %0", new Object[] { menuItemName }, null); } Element tmp_element; Hashtable<String, Integer> menuItemTypes = getMenuItemTypesAsHashtable(); // Get menuitem type: String miType = menuItemElement.getAttribute("type"); Integer type = menuItemTypes.get(miType); if (type == null) { VerticalEngineLogger.errorCreate(this.getClass(), 20, "Invalid menu item type %0.", new Object[] { type }, null); } Connection con = null; PreparedStatement preparedStmt = null; MenuItemKey menuItemKey = null; try { con = getConnection(); // key String keyStr = menuItemElement.getAttribute("key"); if (!useOldKey || keyStr == null || keyStr.length() == 0) { try { menuItemKey = new MenuItemKey(getNextKey(MENU_ITEM_TABLE)); } catch (VerticalKeyException e) { VerticalEngineLogger.errorCreate(this.getClass(), 30, "Error generating key for tMenuItem.", e); } } else { menuItemKey = new MenuItemKey(keyStr); } if (copyContext != null) { copyContext.putMenuItemKey(Integer.parseInt(keyStr), menuItemKey.toInt()); } String tmp; preparedStmt = con.prepareStatement(MENU_ITEM_INSERT); preparedStmt.setInt(1, menuItemKey.toInt()); // element: name validateMenuItemName(menuItemName); preparedStmt.setString(2, menuItemName); // menu key: preparedStmt.setInt(3, siteKey.toInt()); // attribute: menu item type preparedStmt.setInt(4, type); // parent if (parentKey == null) { preparedStmt.setNull(5, Types.INTEGER); } else { preparedStmt.setInt(5, parentKey.toInt()); } // order: preparedStmt.setInt(6, order); // pre-fetch data element Element dataElem = XMLTool.getElement(menuItemElement, "data"); // element: parameters tmp_element = XMLTool.getElement(menuItemElement, "parameters"); if (tmp_element != null) { dataElem.appendChild(tmp_element); } // alternative name: tmp_element = XMLTool.getElement(menuItemElement, ELEMENT_NAME_MENU_NAME); if (tmp_element != null) { tmp = XMLTool.getElementText(tmp_element); preparedStmt.setString(7, tmp); } else { preparedStmt.setNull(7, Types.VARCHAR); } // visibility: tmp = menuItemElement.getAttribute("visible"); if ("no".equals(tmp)) { preparedStmt.setInt(8, 1); } else { preparedStmt.setInt(8, 0); } // description: tmp_element = XMLTool.getElement(menuItemElement, "description"); String data = XMLTool.getElementText(tmp_element); if (data != null) { StringReader reader = new StringReader(data); preparedStmt.setCharacterStream(9, reader, data.length()); } else { preparedStmt.setNull(9, Types.VARCHAR); } if (type == 4) { Element docElem = XMLTool.getElement(menuItemElement, "document"); if (docElem != null) { dataElem.appendChild(docElem); } } // attribute: owner/modifier String ownerKey = menuItemElement.getAttribute("owner"); preparedStmt.setString(10, ownerKey); preparedStmt.setString(11, ownerKey); // data if (dataElem != null) { Document dataDoc = XMLTool.createDocument(); dataDoc.appendChild(dataDoc.importNode(dataElem, true)); byte[] bytes = XMLTool.documentToBytes(dataDoc, "UTF-8"); preparedStmt.setBinaryStream(12, new ByteArrayInputStream(bytes), bytes.length); } else { preparedStmt.setNull(12, Types.BLOB); } // keywords tmp_element = XMLTool.getElement(menuItemElement, "keywords"); String keywords = XMLTool.getElementText(tmp_element); if (keywords == null || keywords.length() == 0) { preparedStmt.setNull(13, Types.VARCHAR); } else { StringReader keywordReader = new StringReader(keywords); preparedStmt.setCharacterStream(13, keywordReader, keywords.length()); } // language String lanKey = menuItemElement.getAttribute("languagekey"); if ((lanKey != null) && (lanKey.length() > 0)) { preparedStmt.setInt(14, Integer.parseInt(lanKey)); } else { preparedStmt.setNull(14, Types.INTEGER); } RunAsType runAs = RunAsType.INHERIT; String runAsStr = menuItemElement.getAttribute("runAs"); if (StringUtils.isNotEmpty(runAsStr)) { runAs = RunAsType.valueOf(runAsStr); } preparedStmt.setInt(15, runAs.getKey()); // Display-name String displayName = getElementValue(menuItemElement, ELEMENT_NAME_DISPLAY_NAME); preparedStmt.setString(16, displayName); // execute statement: preparedStmt.executeUpdate(); // Create type specific data. switch (type) { case 1: // page createPage(con, menuItemElement, type, menuItemKey); break; case 2: // URL createOrUpdateURL(con, menuItemElement, menuItemKey); break; case 4: // document: nothing // page Element pageElem = XMLTool.getElement(menuItemElement, "page"); PageTemplateKey pageTemplateKey = new PageTemplateKey(pageElem.getAttribute("pagetemplatekey")); PageTemplateType pageTemplateType = getPageTemplateHandler().getPageTemplateType(pageTemplateKey); if (pageTemplateType == PageTemplateType.SECTIONPAGE || pageTemplateType == PageTemplateType.NEWSLETTER) { createSection(menuItemElement, menuItemKey); } createPage(con, menuItemElement, type, menuItemKey); break; case 5: // label break; case 6: // section createSection(menuItemElement, menuItemKey); break; case 7: // shortcut createOrOverrideShortcut(menuItemElement, menuItemKey); break; default: VerticalEngineLogger.errorCreate(this.getClass(), 70, "Unknown menuitem type: %0", new Object[] { type }, null); } // set contentkey if present String contentKeyStr = menuItemElement.getAttribute("contentkey"); if (contentKeyStr.length() == 0) { contentKeyStr = "-1"; } setMenuItemContentKey(menuItemKey, Integer.parseInt(contentKeyStr)); // fire event if (multicaster.hasListeners() && copyContext == null) { MenuHandlerEvent e = new MenuHandlerEvent(user, siteKey.toInt(), menuItemKey.toInt(), menuItemName, this); multicaster.createdMenuItem(e); } UserSpecification userSpecification = new UserSpecification(); userSpecification.setDeletedState(UserSpecification.DeletedState.ANY); userSpecification.setKey(new UserKey(ownerKey)); UserEntity owner = userDao.findSingleBySpecification(userSpecification); String ownerGroupKey = null; if (owner.getUserGroup() != null) { ownerGroupKey = owner.getUserGroup().getGroupKey().toString(); } getSecurityHandler().inheritMenuItemAccessRights(siteKey.toInt(), parentKey == null ? -1 : parentKey.toInt(), menuItemKey.toInt(), ownerGroupKey); // Create other Element menuItemsElement = XMLTool.getElement(menuItemElement, "menuitems"); if (menuItemsElement != null) { Element[] elems = XMLTool.getElements(menuItemsElement); for (int i = 0; i < elems.length; i++) { createMenuItem(user, copyContext, elems[i], siteKey, i, menuItemKey, useOldKey); } } } catch (SQLException e) { VerticalEngineLogger.errorCreate(this.getClass(), 40, "A database error occurred: %t", e); } finally { close(preparedStmt); close(con); } return menuItemKey.toInt(); }
From source file:com.zimbra.cs.db.DbMailItem.java
public static void icopy(MailItem source, UnderlyingData data, boolean shared) throws ServiceException { Mailbox mbox = source.getMailbox();// w ww . j a v a2 s. c om if (data == null || data.id <= 0 || data.folderId <= 0 || data.parentId == 0) { throw ServiceException.FAILURE("invalid data for DB item i-copy", null); } checkNamingConstraint(mbox, data.folderId, source.getName(), data.id); DbConnection conn = mbox.getOperationConnection(); PreparedStatement stmt = null; try { String table = getMailItemTableName(mbox); String mailbox_id = DebugConfig.disableMailboxGroups ? "" : "mailbox_id, "; String flags; if (!shared) { flags = "flags"; } else if (Db.supports(Db.Capability.BITWISE_OPERATIONS)) { flags = "flags | " + Flag.BITMASK_COPIED; } else { flags = "CASE WHEN " + Db.getInstance().bitAND("flags", String.valueOf(Flag.BITMASK_COPIED)) + " <> 0 THEN flags ELSE flags + " + Flag.BITMASK_COPIED + " END"; } stmt = conn.prepareStatement("INSERT INTO " + table + "(" + mailbox_id + " id, type, parent_id, folder_id, prev_folders, index_id, imap_id, date, size, locator, blob_digest," + " unread, flags, tag_names, sender, subject, name, metadata, mod_metadata, change_date, mod_content) " + "SELECT " + mailbox_id + " ?, type, parent_id, ?, ?, ?, ?, date, size, ?, blob_digest," + " unread, " + flags + ", tag_names, sender, subject, name, metadata, ?, ?, ? FROM " + table + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?"); int pos = 1; stmt.setInt(pos++, data.id); // ID stmt.setInt(pos++, data.folderId); // FOLDER_ID stmt.setString(pos++, data.getPrevFolders()); if (data.indexId == MailItem.IndexStatus.NO.id()) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, data.indexId); } stmt.setInt(pos++, data.imapId); // IMAP_ID stmt.setString(pos++, data.locator); stmt.setInt(pos++, mbox.getOperationChangeID()); // MOD_METADATA stmt.setInt(pos++, mbox.getOperationTimestamp()); // CHANGE_DATE stmt.setInt(pos++, mbox.getOperationChangeID()); // MOD_CONTENT pos = setMailboxId(stmt, mbox, pos); stmt.setInt(pos++, source.getId()); stmt.executeUpdate(); stmt.close(); boolean needsTag = shared && !source.isTagged(Flag.FlagInfo.COPIED); if (needsTag || source.getParentId() > 0) { boolean altersMODSEQ = source.getParentId() > 0; String updateChangeID = (altersMODSEQ ? ", mod_metadata = ?, change_date = ?" : ""); stmt = conn.prepareStatement("UPDATE " + table + " SET parent_id = NULL, flags = " + flags + updateChangeID + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?"); pos = 1; if (altersMODSEQ) { stmt.setInt(pos++, mbox.getOperationChangeID()); stmt.setInt(pos++, mbox.getOperationTimestamp()); } pos = setMailboxId(stmt, mbox, pos); stmt.setInt(pos++, source.getId()); stmt.executeUpdate(); stmt.close(); } if (source instanceof Message && source.getParentId() <= 0) { changeOpenTargets(source, data.id); } MailItem.Type type = MailItem.Type.of(data.type); DbTag.storeTagReferences(mbox, data.id, type, data.getFlags() | (shared ? Flag.BITMASK_COPIED : 0), data.unreadCount > 0); DbTag.storeTagReferences(mbox, data.id, type, data.getTags()); } catch (SQLException e) { // catch item_id uniqueness constraint violation and return failure if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) { throw MailServiceException.ALREADY_EXISTS(data.id, e); } else { throw ServiceException.FAILURE("i-copying " + source.getType() + ": " + source.getId(), e); } } finally { DbPool.closeStatement(stmt); } }
From source file:com.flexive.ejb.beans.PhraseEngineBean.java
/** * {@inheritDoc}//from w w w . j a v a 2 s. c o m */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public long savePhrase(int category, String phraseKey, FxString value, FxPhraseSearchValueConverter converter, Object tag, long mandator) throws FxNoAccessException { Connection con = null; PreparedStatement ps = null; final UserTicket userTicket = FxContext.getUserTicket(); checkMandatorAccess(mandator, userTicket); checkPhraseKey(phraseKey); try { // Obtain a database connection con = Database.getDbConnection(); long phraseId; ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE + " WHERE PKEY=? AND MANDATOR=? AND CAT=?"); ps.setString(1, phraseKey); ps.setLong(2, mandator); ps.setInt(3, category); ResultSet rs = ps.executeQuery(); if (rs != null && rs.next()) { phraseId = rs.getLong(1); rs.close(); ps.close(); ps = con.prepareStatement("DELETE FROM " + TBL_PHRASE_VALUES + " WHERE ID=? AND MANDATOR=?"); ps.setLong(1, phraseId); ps.setLong(2, mandator); ps.executeUpdate(); } else { try { phraseId = fetchNextPhraseId(mandator); } catch (FxApplicationException e) { EJBUtils.rollback(ctx); throw e.asRuntimeException(); } ps.close(); ps = con.prepareStatement( "INSERT INTO " + TBL_PHRASE + "(ID,PKEY,MANDATOR,HID,CAT)VALUES(?,?,?,?,?)"); ps.setLong(1, phraseId); ps.setString(2, phraseKey); ps.setLong(3, mandator); ps.setBoolean(4, false); ps.setInt(5, category); ps.executeUpdate(); } if (!value.isEmpty()) { ps.close(); ps = con.prepareStatement( "INSERT INTO " + TBL_PHRASE_VALUES + "(ID,MANDATOR,LANG,PVAL,SVAL,TAG)VALUES(?,?,?,?,?,?)"); ps.setLong(1, phraseId); ps.setLong(2, mandator); FxString fxTag = tag instanceof FxString ? (FxString) tag : null; for (long lang : value.getTranslatedLanguages()) { ps.setLong(3, lang); final String translation = value.getTranslation(lang); if (StringUtils.isBlank(translation)) continue; ps.setString(4, translation); if (converter != null) ps.setString(5, converter.convert(translation, lang)); else ps.setString(5, translation.trim().toUpperCase()); if (fxTag != null) { if (!fxTag.isMultiLanguage() || fxTag.translationExists(lang)) ps.setString(6, fxTag.getTranslation(lang)); else ps.setNull(6, Types.VARCHAR); } else { if (tag != null && !StringUtils.isBlank(String.valueOf(tag))) ps.setString(6, String.valueOf(tag)); else ps.setNull(6, Types.VARCHAR); } ps.addBatch(); } ps.executeBatch(); } return phraseId; } catch (SQLException exc) { EJBUtils.rollback(ctx); throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException(); } finally { Database.closeObjects(PhraseEngineBean.class, con, ps); } }
From source file:com.zimbra.cs.db.DbMailItem.java
public void update(MailItem item, Metadata metadata) throws ServiceException { String name = item.getName().isEmpty() ? null : item.getName(); checkNamingConstraint(mailbox, item.getFolderId(), name, item.getId()); DbConnection conn = mailbox.getOperationConnection(); PreparedStatement stmt = null; try {/*ww w . j a v a2 s .c o m*/ stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(item) + " SET type = ?, imap_id = ?, index_id = ?, parent_id = ?, date = ?, size = ?, flags = ?," + " blob_digest = ?, sender = ?, recipients = ?, subject = ?, name = ?," + " metadata = ?, mod_metadata = ?, change_date = ?, mod_content = ?, locator = ?" + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?"); int pos = 1; stmt.setByte(pos++, item.getType().toByte()); if (item.getImapUid() >= 0) { stmt.setInt(pos++, item.getImapUid()); } else { stmt.setNull(pos++, Types.INTEGER); } if (item.getIndexStatus() == MailItem.IndexStatus.NO) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, item.getIndexId()); } // messages in virtual conversations are stored with a null parent_id if (item.getParentId() <= 0) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, item.getParentId()); } stmt.setInt(pos++, (int) (item.getDate() / 1000)); stmt.setLong(pos++, item.getSize()); stmt.setLong(pos++, item.getInternalFlagBitmask()); stmt.setString(pos++, item.getDigest()); stmt.setString(pos++, item.getSortSender()); stmt.setString(pos++, item.getSortRecipients()); stmt.setString(pos++, item.getSortSubject()); stmt.setString(pos++, name); stmt.setString(pos++, checkMetadataLength(metadata.toString())); stmt.setInt(pos++, mailbox.getOperationChangeID()); stmt.setInt(pos++, mailbox.getOperationTimestamp()); stmt.setInt(pos++, item.getSavedSequence()); stmt.setString(pos++, item.getLocator()); pos = setMailboxId(stmt, mailbox, pos); stmt.setInt(pos++, item.getId()); stmt.executeUpdate(); if (mailbox.isItemModified(item, Change.FLAGS)) { DbTag.updateTagReferences(mailbox, item.getId(), item.getType(), item.getInternalFlagBitmask(), item.isUnread(), item.getTags()); } } catch (SQLException e) { // catch item_id uniqueness constraint violation and return failure if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) { throw MailServiceException.ALREADY_EXISTS(item.getName(), e); } else { throw ServiceException .FAILURE("Failed to update item mbox=" + mailbox.getId() + ",id=" + item.getId(), e); } } finally { DbPool.closeStatement(stmt); } }
From source file:com.zimbra.cs.db.DbMailItem.java
public static void setFolder(MailItem item, Folder folder) throws ServiceException { Mailbox mbox = item.getMailbox();// w w w. ja v a2s . co m if (mbox != folder.getMailbox()) { throw MailServiceException.WRONG_MAILBOX(); } checkNamingConstraint(mbox, folder.getId(), item.getName(), item.getId()); DbConnection conn = mbox.getOperationConnection(); PreparedStatement stmt = null; try { String imapRenumber = mbox.isTrackingImap() ? ", imap_id = CASE WHEN imap_id IS NULL THEN NULL ELSE 0 END" : ""; int pos = 1; boolean hasIndexId = false; if (item instanceof Folder) { stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(item) + " SET parent_id = ?, folder_id = ?, prev_folders = ?, mod_metadata = ?, change_date = ?" + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?"); stmt.setInt(pos++, folder.getId()); } else if (item instanceof Conversation && !(item instanceof VirtualConversation)) { stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(item) + " SET folder_id = ?, prev_folders = ?, mod_metadata = ?, change_date = ?" + imapRenumber + " WHERE " + IN_THIS_MAILBOX_AND + "parent_id = ?"); } else { // set the indexId, in case it changed (moving items out of junk can trigger an index ID change) hasIndexId = true; stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(item) + " SET folder_id = ?, prev_folders = ?, index_id = ?, mod_metadata = ?, change_date = ? " + imapRenumber + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?"); } stmt.setInt(pos++, folder.getId()); int modseq = mbox.getOperationChangeID(); String prevFolders = findPrevFolders(item, modseq); stmt.setString(pos++, prevFolders); item.getUnderlyingData().setPrevFolders(prevFolders); if (hasIndexId) { if (item.getIndexStatus() == MailItem.IndexStatus.NO) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, item.getIndexId()); } } stmt.setInt(pos++, modseq); stmt.setInt(pos++, mbox.getOperationTimestamp()); pos = setMailboxId(stmt, mbox, pos); stmt.setInt(pos++, item instanceof VirtualConversation ? ((VirtualConversation) item).getMessageId() : item.getId()); stmt.executeUpdate(); } catch (SQLException e) { // catch item_id uniqueness constraint violation and return failure if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) { throw MailServiceException.ALREADY_EXISTS(item.getName(), e); } else { throw ServiceException.FAILURE("writing new folder data for item " + item.getId(), e); } } finally { DbPool.closeStatement(stmt); } }
From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * {@inheritDoc}/*from ww w .j av a2s .c o m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public long createProperty(long typeId, FxPropertyEdit property, String parentXPath, String assignmentAlias) throws FxApplicationException { FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.StructureManagement); Connection con = null; PreparedStatement ps = null; StringBuilder sql = new StringBuilder(2000); long newPropertyId; long newAssignmentId; try { parentXPath = parentXPath.toUpperCase(); assignmentAlias = assignmentAlias.toUpperCase(); FxType type = CacheAdmin.getEnvironment().getType(typeId); FxAssignment tmp = type.getAssignment(parentXPath); if (tmp != null && tmp instanceof FxPropertyAssignment) throw new FxInvalidParameterException("ex.structure.assignment.noGroup", parentXPath); property.checkConsistency(); //parentXPath is valid, create the property, then assign it to root newPropertyId = seq.getId(FxSystemSequencer.TYPEPROP); FxValue defValue = property.getDefaultValue(); ContentStorage storage = StorageManager.getContentStorage(type.getStorageMode()); con = Database.getDbConnection(); if (defValue instanceof FxBinary) { storage.prepareBinary(con, (FxBinary) defValue); } final String _def = defValue == null || defValue.isEmpty() ? null : ConversionEngine.getXStream().toXML(defValue); if (_def != null && (property.getDefaultValue() instanceof FxReference)) { //check if the type matches the instance checkReferencedType(con, (FxReference) property.getDefaultValue(), property.getReferencedType()); } // do not allow to add mandatory properties (i.e. min multiplicity > 0) to types for which content exists if (storage.getTypeInstanceCount(con, typeId) > 0 && property.getMultiplicity().getMin() > 0) { throw new FxCreateException("ex.structure.property.creation.existingContentMultiplicityError", property.getName(), property.getMultiplicity().getMin()); } //create property, no checks for existing names are performed as this is handled with unique keys sql.append("INSERT INTO ").append(TBL_STRUCT_PROPERTIES). // 1 2 3 4 5 6 7 append("(ID,NAME,DEFMINMULT,DEFMAXMULT,MAYOVERRIDEMULT,DATATYPE,REFTYPE," + //8 9 10 11 12 "ISFULLTEXTINDEXED,ACL,MAYOVERRIDEACL,REFLIST,UNIQUEMODE," + //13 14 "SYSINTERNAL,DEFAULT_VALUE)VALUES(" + "?,?,?,?,?," + "?,?,?,?,?,?,?,?,?)"); ps = con.prepareStatement(sql.toString()); ps.setLong(1, newPropertyId); ps.setString(2, property.getName()); ps.setInt(3, property.getMultiplicity().getMin()); ps.setInt(4, property.getMultiplicity().getMax()); ps.setBoolean(5, property.mayOverrideBaseMultiplicity()); ps.setLong(6, property.getDataType().getId()); if (property.hasReferencedType()) ps.setLong(7, property.getReferencedType().getId()); else ps.setNull(7, java.sql.Types.NUMERIC); ps.setBoolean(8, property.isFulltextIndexed()); ps.setLong(9, property.getACL().getId()); ps.setBoolean(10, property.mayOverrideACL()); if (property.hasReferencedList()) ps.setLong(11, property.getReferencedList().getId()); else ps.setNull(11, java.sql.Types.NUMERIC); ps.setInt(12, property.getUniqueMode().getId()); ps.setBoolean(13, false); if (_def == null) ps.setNull(14, java.sql.Types.VARCHAR); else ps.setString(14, _def); if (!property.isAutoUniquePropertyName()) ps.executeUpdate(); else { //fetch used property names PreparedStatement ps2 = null; try { ps2 = con.prepareStatement( "SELECT NAME FROM " + TBL_STRUCT_PROPERTIES + " WHERE NAME LIKE ? OR NAME=?"); ps2.setString(1, property.getName() + "_%"); ps2.setString(2, property.getName()); ResultSet rs = ps2.executeQuery(); int max = -1; while (rs.next()) { String last = rs.getString(1); if (last.equals(property.getName()) || last.startsWith(property.getName() + "_")) { if (last.equals(property.getName())) { max = Math.max(0, max); } else if (last.startsWith(property.getName() + "_")) { final String suffix = last.substring(last.lastIndexOf("_") + 1); if (StringUtils.isNumeric(suffix)) { max = Math.max(Integer.parseInt(suffix), max); } } } if (max != -1) { final String autoName = property.getName() + "_" + (max + 1); ps.setString(2, autoName); LOG.info("Assigning unique property name [" + autoName + "] to [" + type.getName() + "." + property.getName() + "]"); } } } finally { Database.closeObjects(AssignmentEngineBean.class, ps2); } ps.executeUpdate(); } Database.storeFxString(new FxString[] { property.getLabel(), property.getHint() }, con, TBL_STRUCT_PROPERTIES, new String[] { "DESCRIPTION", "HINT" }, "ID", newPropertyId); ps.close(); sql.setLength(0); //calc new position sql.append("SELECT COALESCE(MAX(POS)+1,0) FROM ").append(TBL_STRUCT_ASSIGNMENTS) .append(" WHERE PARENTGROUP=? AND TYPEDEF=?"); ps = con.prepareStatement(sql.toString()); ps.setLong(1, (tmp == null ? FxAssignment.NO_PARENT : tmp.getId())); ps.setLong(2, typeId); ResultSet rs = ps.executeQuery(); long pos = 0; if (rs != null && rs.next()) pos = rs.getLong(1); ps.close(); storeOptions(con, TBL_STRUCT_PROPERTY_OPTIONS, "ID", newPropertyId, null, property.getOptions()); sql.setLength(0); //create root assignment 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 "ACL,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, true); ps.setLong(4, typeId); ps.setInt(5, property.getMultiplicity().getMin()); ps.setInt(6, property.getMultiplicity().getMax()); if (property.getMultiplicity().isValid(property.getAssignmentDefaultMultiplicity())) { ps.setInt(7, property.getAssignmentDefaultMultiplicity()); } else { //default is min(min,1). ps.setInt(7, property.getMultiplicity().getMin() > 1 ? property.getMultiplicity().getMin() : 1); } ps.setLong(8, pos); if (parentXPath == null || "/".equals(parentXPath)) parentXPath = ""; ps.setString(9, type.getName() + XPathElement.stripType(parentXPath) + "/" + assignmentAlias); ps.setString(10, assignmentAlias); ps.setNull(11, Types.NUMERIC); if (tmp == null) ps.setLong(12, FxAssignment.NO_PARENT); else ps.setLong(12, tmp.getId()); ps.setLong(13, newPropertyId); ps.setLong(14, property.getACL().getId()); ps.setString(15, _def); ps.executeUpdate(); Database.storeFxString(new FxString[] { property.getLabel(), property.getHint() }, con, TBL_STRUCT_ASSIGNMENTS, new String[] { "DESCRIPTION", "HINT" }, "ID", newAssignmentId); StructureLoader.reloadAssignments(FxContext.get().getDivisionId()); if (divisionConfig.isFlatStorageEnabled() && divisionConfig.get(SystemParameters.FLATSTORAGE_AUTO)) { final FxFlatStorage fs = FxFlatStorageManager.getInstance(); FxPropertyAssignment pa = (FxPropertyAssignment) CacheAdmin.getEnvironment() .getAssignment(newAssignmentId); if (fs.isFlattenable(pa)) { fs.flatten(con, fs.getDefaultStorage(), pa); StructureLoader.reloadAssignments(FxContext.get().getDivisionId()); } } htracker.track(type, "history.assignment.createProperty", property.getName(), type.getId(), type.getName()); if (type.getId() != FxType.ROOT_ID) createInheritedAssignments(CacheAdmin.getEnvironment().getAssignment(newAssignmentId), con, sql, type.getDerivedTypes()); } catch (FxNotFoundException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (FxLoadException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (SQLException e) { final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(e); EJBUtils.rollback(ctx); if (uniqueConstraintViolation) throw new FxEntryExistsException("ex.structure.property.exists", property.getName(), (parentXPath.length() == 0 ? "/" : parentXPath)); throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(AssignmentEngineBean.class, con, ps); } return newAssignmentId; }