Example usage for java.sql PreparedStatement setNull

List of usage examples for java.sql PreparedStatement setNull

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setNull

Introduction

In this page you can find the example usage for java.sql PreparedStatement setNull.

Prototype

void setNull(int parameterIndex, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

From source file:com.enonic.vertical.engine.handlers.MenuHandler.java

private void updateMenu(User user, CopyContext copyContext, Document doc, boolean useOldKeys)
        throws VerticalUpdateException, VerticalSecurityException {
    Element root_elem = doc.getDocumentElement();

    // get menu key:
    String tmp = root_elem.getAttribute("key");
    SiteKey menuKey = new SiteKey(tmp);

    Connection con = null;//from  w w  w .  j ava 2  s .co  m
    PreparedStatement preparedStmt = null;
    try {
        con = getConnection();

        // Update the main menu table:
        preparedStmt = con.prepareStatement(MENU_UPDATE);

        // firstpage:
        Element tmpElement = XMLTool.getElement(root_elem, "firstpage");
        tmp = tmpElement.getAttribute("key");
        if (tmp != null && tmp.length() > 0) {
            preparedStmt.setInt(1, Integer.parseInt(tmp));
        } else {
            preparedStmt.setNull(1, Types.INTEGER);
        }

        // loginpage:
        tmpElement = XMLTool.getElement(root_elem, "loginpage");
        tmp = tmpElement.getAttribute("key");
        if (tmp != null && tmp.length() > 0) {
            preparedStmt.setInt(2, Integer.parseInt(tmp));
        } else {
            preparedStmt.setNull(2, Types.INTEGER);
        }

        // errorpage:
        tmpElement = XMLTool.getElement(root_elem, "errorpage");
        tmp = tmpElement.getAttribute("key");
        if (tmp != null && tmp.length() > 0) {
            preparedStmt.setInt(3, Integer.parseInt(tmp));
        } else {
            preparedStmt.setNull(3, Types.INTEGER);
        }

        // default pagetemplate:
        tmpElement = XMLTool.getElement(root_elem, "defaultpagetemplate");
        if (tmpElement != null) {
            tmp = tmpElement.getAttribute("pagetemplatekey");
            preparedStmt.setInt(4, Integer.parseInt(tmp));
        } else {
            preparedStmt.setNull(4, Types.INTEGER);
        }

        SiteData siteData = new SiteData();

        // DeviceClassResolver:
        tmpElement = XMLTool.getElement(root_elem, "deviceclassresolver");
        if (tmpElement != null) {
            String deviceClassResolverUrl = tmpElement.getAttribute("key");
            if (StringUtils.isNotEmpty(deviceClassResolverUrl)) {
                siteData.setDeviceClassResolver(new ResourceKey(deviceClassResolverUrl));
            }
        }

        // default localization resource:
        tmpElement = XMLTool.getElement(root_elem, "defaultlocalizationresource");
        if (tmpElement != null) {
            String defaultLocalizationResource = tmpElement.getAttribute("key");
            if (StringUtils.isNotEmpty(defaultLocalizationResource)) {
                siteData.setDefaultLocalizationResource(new ResourceKey(defaultLocalizationResource));
            }
        }

        // locale resolver:
        tmpElement = XMLTool.getElement(root_elem, "localeresolver");
        if (tmpElement != null) {
            String localeResolver = tmpElement.getAttribute("key");
            if (StringUtils.isNotEmpty(localeResolver)) {
                siteData.setLocaleResolver(new ResourceKey(localeResolver));
            }
        }

        // Path to public home:
        String pathToPublicHome = root_elem.getAttribute("pathtopublichome");
        if (StringUtils.isNotEmpty(pathToPublicHome)) {
            siteData.setPathToPublicResources(new ResourceKey(pathToPublicHome));
        }

        // Path to home:
        String pathToHome = root_elem.getAttribute("pathtohome");
        if (StringUtils.isNotEmpty(pathToHome)) {
            siteData.setPathToResources(new ResourceKey(pathToHome));
        }

        tmpElement = XMLTool.getElement(root_elem, "menudata");
        if (tmpElement != null) {
            parseAndAddMenudataToSiteData(tmpElement, siteData);
        }

        final byte[] xmldata = siteData.getAsBytes();
        preparedStmt.setBinaryStream(5, new ByteArrayInputStream(xmldata), xmldata.length);

        // language key:
        preparedStmt.setInt(6, Integer.parseInt(root_elem.getAttribute("languagekey")));

        Element detailsElement = XMLTool.getElement(root_elem, "details");

        // Statistics URL:
        tmpElement = XMLTool.getElement(detailsElement, "statistics");
        if (tmpElement != null) {
            String name = XMLTool.getElementText(tmpElement);
            if (name != null) {
                preparedStmt.setString(7, name);
            } else {
                preparedStmt.setNull(7, Types.VARCHAR);
            }
        } else {
            preparedStmt.setNull(7, Types.VARCHAR);
        }

        // Run As User:
        String runAsUserKey = root_elem.getAttribute("runas");
        if (StringUtils.isNotEmpty(runAsUserKey)) {
            preparedStmt.setString(8, runAsUserKey);
        } else {
            preparedStmt.setNull(8, Types.VARCHAR);
        }

        // menu key:
        preparedStmt.setInt(9, menuKey.toInt());
        preparedStmt.executeUpdate();
        preparedStmt.close();
        preparedStmt = null;

        // Update the individual menuitems (recursivly):
        try {
            Element[] elems = XMLTool.getElements(XMLTool.getElement(root_elem, "menuitems"));
            for (int i = 0; i < elems.length; i++) {
                String curDeleted = elems[i].getAttribute("deleted");
                if (!"deleted".equals(curDeleted)) {
                    String curKey = elems[i].getAttribute("key");
                    if (curKey == null || curKey.length() == 0 || !useOldKeys) {
                        createMenuItem(user, copyContext, elems[i], menuKey, i, null, useOldKeys);
                    } else {
                        updateMenuItem(user, elems[i], menuKey, i, null, true);
                    }
                }
            }
        } catch (VerticalCreateException vce) {
            VerticalEngineLogger.errorUpdate(this.getClass(), 2, "Failed to create new menuitem: %t", vce);
        }

        // get all deleted menuitems:
        String xpath = "//menuitem[@deleted = 'deleted']";

        try {
            // Search for the xpath:
            NodeList list = XMLTool.selectNodes(doc.getDocumentElement(), xpath);

            // Loop through the results.
            for (int i = 0; i < list.getLength(); i++) {
                Element n = (Element) list.item(i);
                tmp = n.getAttribute("key");
                removeMenuItem(user, Integer.parseInt(tmp));
            }

        } catch (VerticalRemoveException vre) {
            VerticalEngineLogger.errorUpdate(this.getClass(), 3, "Failed to remove menuitem: %t", vre);
        }
    } catch (SQLException sqle) {
        VerticalEngineLogger.errorUpdate(this.getClass(), 5, "A database error occurred: %t", sqle);
    } finally {
        close(preparedStmt);
        close(con);
    }
}

From source file:org.openanzo.datasource.nodecentric.sql.InsertStatementsRdbWrapper.java

/**
 * Runs the insertTempStatement prepared statement.
  * <code>/*from   ww w.j av a2s .  c o m*/
 *          INSERT INTO {0}STMTS_TMP(OPERATION,STMTID,ID,METADATA,UUID,NAMEDGRAPHID,SUBJECT,PREDICATE,OBJECT,REVISION,COMMITTED) VALUES(?,?,?,?,?,?,?,?,?,?,?)    
 * </code>
 *
 *@param stmtProvider
 *         factory and cache of PreparedStatments
 *@param connection
 *          connection to underlying database
 *
 *@param operation template parameter
 *@param stmtId template parameter
 *@param id template parameter
 *@param metadata template parameter
 *@param uuid template parameter
 *@param graphid template parameter
 *@param subject template parameter
 *@param predicate template parameter
 *@param object template parameter
 *@param revision template parameter
 *@param committed template parameter
 *
 *@param sessionPrefix template parameter
 *@return  int
 *@throws  org.openanzo.jdbc.utils.RdbException
 */
public static int insertTempStatement(final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider,
        final java.sql.Connection connection, int operation, int stmtId, String id, int metadata, long uuid,
        long graphid, long subject, long predicate, long object, Long revision, long committed,
        String sessionPrefix) throws org.openanzo.jdbc.utils.RdbException {
    java.sql.PreparedStatement ps = null;
    //long startTimer=System.currentTimeMillis();
    try {
        ps = stmtProvider.getPreparedSQLStatement(insertTempStatement, new String[] { sessionPrefix },
                connection);
        int argc = 1;
        ps.setInt(argc++, operation);
        ps.setInt(argc++, stmtId);
        if (id == null) {
            ps.setNull(argc++, java.sql.Types.VARCHAR);
        } else {
            ps.setString(argc++, id);
        }
        ps.setInt(argc++, metadata);
        ps.setLong(argc++, uuid);
        ps.setLong(argc++, graphid);
        ps.setLong(argc++, subject);
        ps.setLong(argc++, predicate);
        ps.setLong(argc++, object);
        if (revision == null) {
            ps.setNull(argc++, java.sql.Types.BIGINT);
        } else {
            ps.setLong(argc++, revision);
        }
        ps.setLong(argc++, committed);
        int counter = 0;
        try {
            counter = ps.executeUpdate();
        } catch (java.sql.SQLException sqle) {
            if (sqle.getErrorCode() == 1205) {
                int retries = 0;
                while (retries < 5) {
                    try {
                        Thread.sleep(5000);
                    } catch (InterruptedException ie) {
                        throw sqle;
                    }
                    try {
                        counter = ps.executeUpdate();
                        break;
                    } catch (java.sql.SQLException sqleInner) {
                        if (sqleInner.getErrorCode() == 1205) {
                            retries++;
                        } else {
                            throw sqleInner;
                        }
                    }
                }
                if (retries >= 5) {
                    throw sqle;
                }
            } else {
                throw sqle;
            }
        }
        return counter;

    } catch (java.sql.SQLException e) {
        throw new org.openanzo.jdbc.utils.RdbException(
                org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e, "insertTempStatement",
                stmtProvider.getSqlString(insertTempStatement),
                "" + "operation=" + (operation) + "," + "stmtId=" + (stmtId) + "," + "id="
                        + ((id != null) ? id.toString() : "null") + "," + "metadata=" + (metadata) + ","
                        + "uuid=" + (uuid) + "," + "graphid=" + (graphid) + "," + "subject=" + (subject) + ","
                        + "predicate=" + (predicate) + "," + "object=" + (object) + "," + "revision="
                        + ((revision != null) ? revision.toString() : "null") + "," + "committed="
                        + (committed),
                "" + "sessionPrefix=" + ((sessionPrefix != null) ? sessionPrefix.toString() : "null"));
    } finally {
        if (ps != null) {
            try {
                ps.close();
            } catch (java.sql.SQLException sqle) {
                if (log.isDebugEnabled())
                    log.debug(org.openanzo.exceptions.LogUtils.RDB_MARKER, "Error closing prepared statement",
                            sqle);
            }
        }
        //long endtimer=(System.currentTimeMillis()-startTimer);
        //if(endtimer>CUTOFF)System.out.println("[insertTempStatement]"+endtimer);
    }
}

From source file:com.zimbra.cs.db.DbMailItem.java

public static void setParent(MailItem[] children, MailItem parent) throws ServiceException {
    if (children == null || children.length == 0) {
        return;/*from w ww . j  a  va 2 s  .c  o m*/
    }
    Mailbox mbox = children[0].getMailbox();
    if (parent != null && mbox != parent.getMailbox()) {
        throw MailServiceException.WRONG_MAILBOX();
    }
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        for (int i = 0; i < children.length; i += Db.getINClauseBatchSize()) {
            int count = Math.min(Db.getINClauseBatchSize(), children.length - i);
            stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(mbox)
                    + " SET parent_id = ?, mod_metadata = ?, change_date = ?" + " WHERE " + IN_THIS_MAILBOX_AND
                    + DbUtil.whereIn("id", count));
            int pos = 1;
            if (parent == null || parent instanceof VirtualConversation) {
                stmt.setNull(pos++, Types.INTEGER);
            } else {
                stmt.setInt(pos++, parent.getId());
            }
            stmt.setInt(pos++, mbox.getOperationChangeID());
            stmt.setInt(pos++, mbox.getOperationTimestamp());
            pos = setMailboxId(stmt, mbox, pos);
            for (int index = i; index < i + count; index++) {
                stmt.setInt(pos++, children[index].getId());
            }
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
        }
    } catch (SQLException e) {
        throw ServiceException
                .FAILURE("adding children to parent " + (parent == null ? "NULL" : parent.getId() + ""), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

From source file:com.enonic.vertical.engine.handlers.ContentObjectHandler.java

public int[] createContentObject(CopyContext copyContext, Document doc, boolean useOldKey)
        throws VerticalCreateException {

    Element docElem = doc.getDocumentElement();
    Element[] contentobjectElems;
    if ("contentobject".equals(docElem.getTagName())) {
        contentobjectElems = new Element[] { docElem };
    } else {/*from ww w . java  2 s. c  o m*/
        contentobjectElems = XMLTool.getElements(doc.getDocumentElement());
    }

    Connection con = null;
    PreparedStatement preparedStmt = null;
    int pos = 0;
    String tmpStr = null;
    TIntArrayList newKeys = new TIntArrayList();

    try {
        con = getConnection();
        preparedStmt = con.prepareStatement(COB_CREATE);

        for (Element root : contentobjectElems) {
            Map subelems = XMLTool.filterElements(root.getChildNodes());

            int key, menuKey = -1;
            String styleSheetKey = "", borderStyleSheetKey = "";

            pos = 0;
            String keyStr = root.getAttribute("key");
            if (!useOldKey || tmpStr == null || tmpStr.length() == 0) {
                key = getNextKey(COB_TABLE);
            } else {
                key = Integer.parseInt(tmpStr);
            }
            if (copyContext != null) {
                copyContext.putContentObjectKey(Integer.parseInt(keyStr), key);
            }
            newKeys.add(key);

            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.errorCreate(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 = tmpStr;
                } else {
                    String message = "No object stylesheet key specified.";
                    VerticalEngineLogger.errorCreate(this.getClass(), 0, message, null);
                }
            } else {
                String message = "No object stylesheet specified.";
                VerticalEngineLogger.errorCreate(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 = tmpStr;
                }
            }

            String name = null;
            byte[] contentobjectdata;

            // element: name
            subelem = (Element) subelems.get("name");
            if (subelem != null) {
                name = XMLTool.getElementText(subelem);
                if (name == null || name.length() == 0) {
                    String message = "Empty stylesheet name.";
                    VerticalEngineLogger.errorCreate(this.getClass(), 0, message, null);
                }
            } else {
                String message = "No stylesheet name specified.";
                VerticalEngineLogger.errorCreate(this.getClass(), 0, message, null);
            }

            // element: contentobjectdata (optional)
            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(1, key);
            preparedStmt.setInt(2, menuKey);
            preparedStmt.setString(3, styleSheetKey);
            if (borderStyleSheetKey.length() > 0) {
                preparedStmt.setString(4, borderStyleSheetKey);
            } else {
                preparedStmt.setNull(4, Types.VARCHAR);
            }
            preparedStmt.setString(5, name);
            if (contentobjectdata != null) {
                preparedStmt.setBinaryStream(6, new ByteArrayInputStream(contentobjectdata),
                        contentobjectdata.length);
            } else {
                preparedStmt.setNull(6, Types.VARBINARY);
            }

            RunAsType runAs = RunAsType.INHERIT;
            String runAsStr = root.getAttribute("runAs");
            if (StringUtils.isNotEmpty(runAsStr)) {
                runAs = RunAsType.valueOf(runAsStr);
            }
            preparedStmt.setInt(7, runAs.getKey());

            // create content object
            int result = preparedStmt.executeUpdate();
            if (result <= 0) {
                String message = "Failed to create content object, no content object created.";
                VerticalEngineLogger.errorCreate(this.getClass(), 0, message, null);
            }
        }

        preparedStmt.close();
        preparedStmt = null;
    } catch (SQLException sqle) {
        String message = "Failed to create content object(s): %t";
        VerticalEngineLogger.errorCreate(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.errorCreate(this.getClass(), 0, message, msgData, nfe);
    } catch (VerticalKeyException gke) {
        String message = "Failed to generate content object key";
        VerticalEngineLogger.errorCreate(this.getClass(), 0, message, gke);
    } finally {
        close(preparedStmt);
        close(con);
    }

    return newKeys.toArray();
}

From source file:com.flexive.ejb.beans.PhraseEngineBean.java

/**
 * {@inheritDoc}//from   w w  w  .ja  va2 s.c om
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void setPhraseTreeNodeParent(long nodeId, long nodeMandator, long parentId, long parentMandator)
        throws FxNoAccessException, FxNotFoundException {
    Connection con = null;
    PreparedStatement ps = null;

    checkMandatorAccess(nodeMandator, FxContext.getUserTicket());
    try {
        // Obtain a database connection
        con = Database.getDbConnection();
        //check if the node and the parent node exists
        ps = con.prepareStatement(
                "SELECT ID, MANDATOR, CAT FROM " + TBL_PHRASE_TREE + " WHERE ID=? AND MANDATOR=?");
        ps.setLong(1, nodeId);
        ps.setLong(2, nodeMandator);
        ResultSet rs = ps.executeQuery();
        if (!(rs != null && rs.next()))
            throw new FxNotFoundException("ex.phrases.node.notFound.id", nodeId, nodeMandator);
        final int nodeCategory = rs.getInt(3);
        final int parentCategory;
        if (parentId != FxPhraseTreeNode.NOT_SET) {
            ps.setLong(1, parentId);
            ps.setLong(2, parentMandator);
            rs = ps.executeQuery();
            if (!(rs != null && rs.next()))
                throw new FxNotFoundException("ex.phrase.tree.parent.notFound", parentId, parentMandator);
            parentCategory = rs.getInt(3);
        } else
            parentCategory = nodeCategory;
        if (nodeCategory != parentCategory)
            throw new FxInvalidParameterException("parent", "ex.phrase.tree.category.mismatch")
                    .asRuntimeException();
        ps.close();
        ps = con.prepareStatement(
                "UPDATE " + TBL_PHRASE_TREE + " SET PARENTID=?,PARENTMANDATOR=? WHERE ID=? AND MANDATOR=?");
        if (parentId != FxPhraseTreeNode.NOT_SET) {
            ps.setLong(1, parentId);
            ps.setLong(2, parentMandator);
        } else {
            ps.setNull(1, Types.NUMERIC);
            ps.setNull(1, Types.NUMERIC);
        }
        ps.setLong(3, nodeId);
        ps.setLong(4, nodeMandator);
        ps.executeUpdate();
    } 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.enonic.vertical.engine.handlers.MenuHandler.java

public void updateMenuData(Document doc) throws VerticalUpdateException, VerticalSecurityException {
    Element root_elem = doc.getDocumentElement();

    // get menu key:
    String tmp = root_elem.getAttribute("key");
    int menuKey = Integer.parseInt(tmp);

    Connection con = null;//from   ww w.j  av  a  2s  .c o m
    PreparedStatement preparedStmt = null;
    try {
        con = getConnection();

        // Update the main menu table:
        preparedStmt = con.prepareStatement(MENUDATA_UPDATE);

        // name:
        Element tmpElement = XMLTool.getElement(root_elem, "name");
        String name = null;
        if (tmpElement != null) {
            name = XMLTool.getElementText(tmpElement);
        }
        if (name != null && name.length() > 0) {
            StringReader sreader = new StringReader(name);
            preparedStmt.setCharacterStream(1, sreader, name.length());
        } else {
            preparedStmt.setNull(1, Types.VARCHAR);
        }

        // language:
        preparedStmt.setInt(3, Integer.parseInt(root_elem.getAttribute("languagekey")));

        Element detailsElement = XMLTool.getElement(root_elem, "details");

        // Statistics URL:
        tmpElement = XMLTool.getElement(detailsElement, "statistics");
        if (tmpElement != null) {
            String statisticsURL = XMLTool.getElementText(tmpElement);
            if (statisticsURL != null) {
                preparedStmt.setString(4, statisticsURL);
            } else {
                preparedStmt.setNull(4, Types.VARCHAR);
            }
        } else {
            preparedStmt.setNull(4, Types.VARCHAR);
        }

        SiteData siteData = new SiteData();

        // DeviceClassResolver:
        tmpElement = XMLTool.getElement(root_elem, "deviceclassresolver");
        if (tmpElement != null) {
            String deviceClassResolverUrl = tmpElement.getAttribute("key");
            if (deviceClassResolverUrl != null) {
                siteData.setDeviceClassResolver(new ResourceKey(deviceClassResolverUrl));
            }
        }

        // Localization default resource:
        tmpElement = XMLTool.getElement(root_elem, "defaultlocalizationresource");
        if (tmpElement != null) {
            String defaultLocalizationResourceUrl = tmpElement.getAttribute("key");
            if (defaultLocalizationResourceUrl != null) {
                siteData.setDefaultLocalizationResource(new ResourceKey(defaultLocalizationResourceUrl));
            }
        }

        // Locale resolver
        tmpElement = XMLTool.getElement(root_elem, "localeresolver");
        if (tmpElement != null) {
            String localeResolver = tmpElement.getAttribute("key");
            if (localeResolver != null) {
                siteData.setLocaleResolver(new ResourceKey(localeResolver));
            }
        }

        // Path to public home:
        String pathToPublicHome = root_elem.getAttribute("pathtopublichome");
        if (pathToPublicHome != null && pathToPublicHome.length() > 0) {
            siteData.setPathToPublicResources(new ResourceKey(pathToPublicHome));
        }

        // Path to home:
        String pathToHome = root_elem.getAttribute("pathtohome");
        if (StringUtils.isNotEmpty(pathToHome)) {
            siteData.setPathToResources(new ResourceKey(pathToHome));
        }

        // menu data:
        tmpElement = XMLTool.getElement(root_elem, "menudata");
        if (tmpElement != null) {
            parseAndAddMenudataToSiteData(tmpElement, siteData);
        }

        final byte[] xmldata = siteData.getAsBytes();
        preparedStmt.setBinaryStream(2, new ByteArrayInputStream(xmldata), xmldata.length);

        // Run As User:
        String runAsUserGroupKey = root_elem.getAttribute("runas");
        if (runAsUserGroupKey != null && runAsUserGroupKey.length() > 0) {
            UserKey userKey = getUserKeyFromGroupKey(runAsUserGroupKey);
            preparedStmt.setString(5, userKey != null ? userKey.toString() : null);
        } else {
            preparedStmt.setNull(5, Types.VARCHAR);
        }

        // menu key:
        preparedStmt.setInt(6, menuKey);
        preparedStmt.executeUpdate();
        preparedStmt.close();
        preparedStmt = null;
    } catch (SQLException sqle) {
        String message = "SQL error: %t";
        VerticalEngineLogger.errorUpdate(this.getClass(), 0, message, sqle);
    } finally {
        close(preparedStmt);
        close(con);
    }
}

From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java

/**
 * Update the main entry/*from  ww w . j  a  v  a 2  s . c  o m*/
 *
 * @param con     an open and valid connection
 * @param content content to create
 * @throws FxUpdateException on errors
 */
protected void updateMainEntry(Connection con, FxContent content) throws FxUpdateException {
    PreparedStatement ps = null;
    try {
        ps = con.prepareStatement(CONTENT_MAIN_UPDATE);
        ps.setLong(19, content.getPk().getId());
        ps.setInt(20, content.getPk().getVersion());
        ps.setLong(1, content.getTypeId());
        ps.setLong(2, content.getAclIds().size() > 1 ? ACL.NULL_ACL_ID : content.getAclIds().get(0));
        ps.setLong(3, content.getStepId());
        ps.setInt(4, content.getMaxVersion());
        ps.setInt(5, content.getLiveVersion());
        ps.setBoolean(6, content.isMaxVersion());
        ps.setBoolean(7, content.isLiveVersion());
        ps.setBoolean(8, content.isActive());
        ps.setInt(9, (int) content.getMainLanguage());
        if (content.isRelation()) {
            ps.setLong(10, content.getRelatedSource().getId());
            ps.setInt(11, content.getRelatedSource().getVersion());
            ps.setLong(12, content.getRelatedDestination().getId());
            ps.setInt(13, content.getRelatedDestination().getVersion());
            ps.setLong(14, content.getRelatedSourcePosition());
            ps.setLong(15, content.getRelatedDestinationPosition());
        } else {
            ps.setNull(10, java.sql.Types.NUMERIC);
            ps.setNull(11, java.sql.Types.NUMERIC);
            ps.setNull(12, java.sql.Types.NUMERIC);
            ps.setNull(13, java.sql.Types.NUMERIC);
            ps.setNull(14, java.sql.Types.NUMERIC);
            ps.setNull(15, java.sql.Types.NUMERIC);
        }

        if (content.isForceLifeCycle()) {
            ps.setLong(16, content.getValue(FxLargeNumber.class, "/MODIFIED_BY").getBestTranslation());
            ps.setLong(17, content.getValue(FxDateTime.class, "/MODIFIED_AT").getBestTranslation().getTime());
        } else {
            long userId = FxContext.getUserTicket().getUserId();

            ps.setLong(16, userId);
            ps.setLong(17, System.currentTimeMillis());
        }
        setGroupPositions(ps, content, 18);
        ps.executeUpdate();

        if (content.isForceLifeCycle()) {
            ps.close();
            // update created_at/created_by
            ps = con.prepareStatement(CONTENT_MAIN_UPDATE_CREATED_AT);
            ps.setLong(1, content.getValue(FxDateTime.class, "/CREATED_AT").getBestTranslation().getTime());
            ps.setLong(2, content.getValue(FxLargeNumber.class, "/CREATED_BY").getBestTranslation());
            ps.setLong(3, content.getPk().getId());
            ps.setInt(4, content.getPk().getVersion());
            ps.executeUpdate();
        }
        updateACLEntries(con, content, content.getPk(), false);

    } catch (SQLException e) {
        throw new FxUpdateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } catch (FxCreateException e) {
        throw new FxUpdateException(e);
    } finally {
        Database.closeObjects(GenericHierarchicalStorage.class, ps);
    }
}

From source file:com.zimbra.cs.db.DbMailItem.java

public static void persistCounts(MailItem item, Metadata metadata) throws ServiceException {
    Mailbox mbox = item.getMailbox();/*w  w w.j a v a 2  s  . c o m*/
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(item)
                + " SET size = ?, unread = ?, metadata = ?, mod_metadata = ?, change_date = ?, mod_content = ?"
                + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?");
        int pos = 1;
        stmt.setLong(pos++, item.getSize());
        stmt.setInt(pos++, item.getUnreadCount());
        stmt.setString(pos++, checkMetadataLength(metadata.toString()));
        stmt.setInt(pos++, item.getModifiedSequence());
        if (item.getChangeDate() > 0) {
            stmt.setInt(pos++, (int) (item.getChangeDate() / 1000));
        } else {
            stmt.setNull(pos++, Types.INTEGER);
        }
        stmt.setInt(pos++, item.getSavedSequence());
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, item.getId());
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw ServiceException
                .FAILURE("writing metadata for mailbox " + item.getMailboxId() + ", item " + item.getId(), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///  w w w. ja v  a  2  s .  c  o m
public void deleteTopicVersion(int topicVersionId, Integer previousTopicVersionId, Connection conn)
        throws SQLException {
    PreparedStatement stmt = null;
    try {
        // delete references to the topic version from the log table
        stmt = conn.prepareStatement(STATEMENT_DELETE_LOG_ITEMS_BY_TOPIC_VERSION);
        stmt.setInt(1, topicVersionId);
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
    try {
        // delete references to the topic version from the recent changes table
        stmt = conn.prepareStatement(STATEMENT_DELETE_RECENT_CHANGES_TOPIC_VERSION);
        stmt.setInt(1, topicVersionId);
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
    try {
        // update any recent changes that refer to this record as the previous record
        stmt = conn.prepareStatement(STATEMENT_UPDATE_RECENT_CHANGES_PREVIOUS_VERSION_ID);
        if (previousTopicVersionId != null) {
            stmt.setInt(1, previousTopicVersionId);
        } else {
            stmt.setNull(1, Types.INTEGER);
        }
        stmt.setInt(2, topicVersionId);
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
    try {
        // delete the topic version record
        stmt = conn.prepareStatement(STATEMENT_DELETE_TOPIC_VERSION);
        stmt.setInt(1, topicVersionId);
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

From source file:com.enonic.vertical.engine.handlers.MenuHandler.java

protected void removePageFromMenuItem(Connection con, int key) throws SQLException, VerticalRemoveException {

    PreparedStatement preparedStmt = null;
    ResultSet resultSet = null;//from w ww  .ja  va  2  s .  c o m
    int pagKey = -1;

    try {
        // Get the page key:
        preparedStmt = con
                .prepareStatement("SELECT mei_pag_lKey FROM " + MENU_ITEM_TABLE + " WHERE mei_lKey = ?");
        preparedStmt.setInt(1, key);
        resultSet = preparedStmt.executeQuery();
        if (resultSet.next()) {
            pagKey = resultSet.getInt(1);
        }
    } finally {
        close(resultSet);
        close(preparedStmt);
    }

    try {
        // Remove the coupling between the menuitem and the page:
        preparedStmt = con
                .prepareStatement("UPDATE " + MENU_ITEM_TABLE + " SET mei_pag_lKey = ? WHERE mei_lKey = ?");
        preparedStmt.setNull(1, Types.INTEGER);
        preparedStmt.setInt(2, key);
        preparedStmt.executeUpdate();
    } finally {
        close(preparedStmt);
    }

    // Remove the page from the page table:
    if (pagKey != -1) {
        getPageHandler().removePage(pagKey);
    }
}