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:org.apereo.portal.layout.dlm.RDBMDistributedLayoutStore.java

@Override
protected int saveStructure(Node node, PreparedStatement structStmt, PreparedStatement parmStmt)
        throws SQLException {
    if (node == null) { // No more
        return 0;
    }// w ww .  j  a v a2  s  . com
    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 (logger.isDebugEnabled()) {
        logger.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"))));
    logger.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());
            logger.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);
                        logger.debug(parmStmt.toString());
                        parmStmt.executeUpdate();
                    }
                }
            }
        }
    }
    return saveStructId;
}

From source file:net.unicon.mercury.fac.rdbms.RdbmsMessageFactory.java

/**
 * Creates a message and sends it to all of the provided recipients.
 * The message is returned to the caller.
 *
 * @param conn         connection to the data source.
 * @param recipients   users that the message will be sent to. Must not
 *                     contain any null elements.
 * @param subject      message subject/*  w w  w .ja  va2s.  c o  m*/
 * @param body         message content
 * @param attachments  attachments to the message. May be null.
 * @return             the message that is created and sent.
 * @throws MercuryException
 * @see net.unicon.mercury.IMessageFactory#sendMessage(IRecipient[],
 *                                         String, String, IAttachment[])
 */
public IMessage sendMessage(IRecipient[] recipients, String subject, String body, IAttachment[] attachments,
        Priority priority) throws MercuryException {

    // Assertions
    if (recipients == null) {
        throw new IllegalArgumentException("Argument 'recipients' cannot be null.");
    }
    if (subject == null) {
        throw new IllegalArgumentException("Argument 'subject' cannot be null.");
    }
    if (body == null) {
        throw new IllegalArgumentException("Argument 'body' cannot be null.");
    }
    // attachments can be null.

    IMessage rslt = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ConnState connst = null;
    Set sentSet = new HashSet();

    try {
        Calendar cal = Calendar.getInstance();

        rslt = createMessage(this, String.valueOf(getMessageSequencer().next()), subject, this.owner,
                recipients, cal.getTime(), body, priority, false);

        if (attachments != null && attachments.length > 0) {
            sendAttachments(attachments, rslt.getId());
        }

        // Setup connection
        conn = dataSource.getConnection();
        connst = beginTransaction(conn);

        // Add message
        pstmt = conn.prepareStatement(qm.getQuery("SEND_MESSAGE"));

        // Set all parameters
        pstmt.setLong(1, Long.parseLong(rslt.getId()));
        pstmt.setString(2, rslt.getSender().toNativeFormat());
        pstmt.setString(3, rslt.getSubject());
        pstmt.setTimestamp(4, new Timestamp(rslt.getDate().getTime()));
        pstmt.setString(5, rslt.getBody());
        pstmt.setInt(6, priority.toInt());
        if (expiration > 0) {
            cal.add(Calendar.DATE, expiration);
            pstmt.setTimestamp(7, new Timestamp(cal.getTime().getTime()));
        } else {
            pstmt.setTimestamp(7, null);
        }

        pstmt.executeUpdate(); // Message added, not committed

        // Clear resources for reuse
        closeStatement(pstmt);
        pstmt = null;

        // For sender, and each recipient, add a "copy" of the
        // message into dispatch table

        pstmt = conn.prepareStatement(qm.getQuery("DISPATCH_MESSAGE"));

        // Set parameters for sender
        pstmt.setLong(1, Long.parseLong(rslt.getId()));
        pstmt.setString(2, rslt.getSender().toNativeFormat());
        pstmt.setInt(3, RdbmsDispatchType.SENDER.toInt());
        pstmt.setNull(4, Types.INTEGER); // Recipient type has no meaning
        pstmt.setString(5, UNREAD); // Unread to start
        pstmt.setInt(6, (int) SpecialFolder.OUTBOX_VALUE); // Place it in sender's outbox

        pstmt.executeUpdate(); // Sender added, not committed

        // Then create each recipients copy
        pstmt.setLong(1, Long.parseLong(rslt.getId()));
        pstmt.setInt(3, RdbmsDispatchType.RECEIVER.toInt());
        pstmt.setString(5, UNREAD); // Unread to start
        pstmt.setInt(6, (int) SpecialFolder.INBOX_VALUE);
        for (int i = 0; i < recipients.length; i++) {

            // Assertions
            if (recipients[i] == null) {
                String msg = "Argument 'recipients [IRecipient[]]' " + "cannot contain null elements.";
                throw new IllegalArgumentException(msg);
            }

            if (!sentSet.add(recipients[i])) {
                continue;
            }

            IRecipientType rType = recipients[i].getType();
            if (!(rType instanceof RdbmsRecipientType)) {
                rType = RdbmsRecipientType.getType(rType.getLabel());
            }
            pstmt.setString(2, recipients[i].getAddress().toNativeFormat());
            pstmt.setInt(4, ((RdbmsRecipientType) rType).toInt());
            pstmt.executeUpdate(); // Recipient added, not committed
        }

        // Finally commit transaction
        conn.commit();

    } catch (Exception t) {
        rollBack(conn); // Message not added, nor any recipients if any fail
        String msg = "RdbmsMessageFactory was not able to send message: " + t.getMessage();
        log.error(msg, t);
        throw new MercuryException(msg, t);
    } finally {
        closeStatement(pstmt);
        pstmt = null;
        cleanupTransactionConnection(conn, connst);
        conn = null;
    }

    return rslt;
}

From source file:org.globus.workspace.persistence.PersistenceAdapterImpl.java

public void add(InstanceResource resource)

        throws WorkspaceDatabaseException {

    if (resource == null) {
        throw new WorkspaceDatabaseException("resource is null");
    }/*  ww w  .  j ava  2 s.  c o  m*/

    final int id = resource.getID();

    if (id < 0) {
        throw new WorkspaceDatabaseException("id is less than zero");
    }

    if (this.dbTrace) {
        logger.trace("add(): " + Lager.id(id) + ", WorkspaceResource = " + resource);
    }

    final byte[] faultBytes;

    try {
        faultBytes = ErrorUtil.toByteArray(resource.getStateThrowable());
    } catch (IOException e) {
        throw new WorkspaceDatabaseException(e);
    }

    Connection c = null;
    PreparedStatement pstmt = null;
    PreparedStatement[] pstmts = null;
    try {
        c = getConnection();
        c.setAutoCommit(false);
        pstmt = c.prepareStatement(SQL_INSERT_RESOURCE);

        pstmt.setInt(1, id);
        pstmt.setString(2, resource.getName());
        pstmt.setInt(3, resource.getState());
        pstmt.setInt(4, resource.getTargetState());

        if (resource.getTerminationTime() != null) {
            pstmt.setObject(5, new Long(resource.getTerminationTime().getTimeInMillis()));
        } else {
            pstmt.setInt(5, 0);
        }

        if (resource.isOpsEnabled()) {
            pstmt.setInt(6, 1);
        } else {
            pstmt.setInt(6, 0);
        }

        if (resource.getCreatorID() != null) {
            pstmt.setString(7, resource.getCreatorID());
        } else {
            pstmt.setNull(7, Types.VARCHAR);
        }

        if (resource.getStartTime() != null) {
            pstmt.setObject(8, new Long(resource.getStartTime().getTimeInMillis()));
        } else {
            pstmt.setInt(8, 0);
        }

        if (resource.isVMMaccessOK()) {
            pstmt.setInt(9, 1);
        } else {
            pstmt.setInt(9, 0);
        }

        if (resource.getEnsembleId() != null) {
            pstmt.setString(10, resource.getEnsembleId());
        } else {
            pstmt.setNull(10, Types.VARCHAR);
        }

        if (resource.getGroupId() != null) {
            pstmt.setString(11, resource.getGroupId());
        } else {
            pstmt.setNull(11, Types.VARCHAR);
        }

        pstmt.setInt(12, resource.getGroupSize());

        if (resource.isLastInGroup()) {
            pstmt.setInt(13, 1);
        } else {
            pstmt.setInt(13, 0);
        }

        pstmt.setInt(14, resource.getLaunchIndex());

        if (faultBytes != null) {
            pstmt.setObject(15, faultBytes, Types.BLOB);
        } else {
            pstmt.setNull(15, Types.BLOB);
        }

        pstmt.setString(16, resource.getClientToken());

        pstmt.setDouble(17, resource.getChargeRatio());

        if (this.dbTrace) {
            logger.trace("creating WorkspaceResource db " + "entry for " + Lager.id(id));
        }

        pstmt.executeUpdate();

        if (resource instanceof VMPersistence) {

            pstmts = VirtualMachinePersistenceUtil.getInsertVM(resource, id, c);

            if (this.dbTrace) {
                logger.trace("creating VirtualMachine db " + "entry for " + Lager.id(id) + ": " + pstmts.length
                        + " inserts");
            }

            for (int i = 0; i < pstmts.length; i++) {
                pstmts[i].executeUpdate();
            }
        }

        c.commit();

    } catch (SQLException e) {
        logger.error("", e);
        throw new WorkspaceDatabaseException(e);
    } catch (ManageException e) {
        logger.error("", e);
        throw new WorkspaceDatabaseException(e);
    } finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
            if (pstmts != null) {
                for (int i = 0; i < pstmts.length; i++) {
                    pstmts[i].close();
                }
            }
            if (c != null) {
                c.setAutoCommit(true);
                returnConnection(c);
            }
        } catch (SQLException sql) {
            logger.error("SQLException in finally cleanup", sql);
        }
    }
}

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

/**
 *
 *//* w  w  w  . j a v  a  2s.  c  om*/
public void updateTopicVersion(TopicVersion topicVersion, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(STATEMENT_UPDATE_TOPIC_VERSION);
        stmt.setInt(1, topicVersion.getTopicId());
        stmt.setString(2, topicVersion.getEditComment());
        stmt.setString(3, topicVersion.getVersionContent());
        if (topicVersion.getAuthorId() == null) {
            stmt.setNull(4, Types.INTEGER);
        } else {
            stmt.setInt(4, topicVersion.getAuthorId());
        }
        stmt.setInt(5, topicVersion.getEditType());
        stmt.setString(6, topicVersion.getAuthorDisplay());
        stmt.setTimestamp(7, topicVersion.getEditDate());
        if (topicVersion.getPreviousTopicVersionId() == null) {
            stmt.setNull(8, Types.INTEGER);
        } else {
            stmt.setInt(8, topicVersion.getPreviousTopicVersionId());
        }
        stmt.setInt(9, topicVersion.getCharactersChanged());
        stmt.setString(10, topicVersion.getVersionParamString());
        stmt.setInt(11, topicVersion.getTopicVersionId());
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

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

/**
 *
 *///from  w  w  w . j  a  v a 2s . c  o  m
public void updateTopic(Topic topic, int virtualWikiId, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(STATEMENT_UPDATE_TOPIC);
        stmt.setInt(1, virtualWikiId);
        stmt.setString(2, topic.getName());
        stmt.setInt(3, topic.getTopicType().id());
        stmt.setInt(4, (topic.getReadOnly() ? 1 : 0));
        if (topic.getCurrentVersionId() == null) {
            stmt.setNull(5, Types.INTEGER);
        } else {
            stmt.setInt(5, topic.getCurrentVersionId());
        }
        stmt.setTimestamp(6, topic.getDeleteDate());
        stmt.setInt(7, (topic.getAdminOnly() ? 1 : 0));
        stmt.setString(8, topic.getRedirectTo());
        stmt.setInt(9, topic.getNamespace().getId());
        stmt.setString(10, topic.getPageName());
        stmt.setString(11, topic.getPageName().toLowerCase());
        stmt.setInt(12, topic.getTopicId());
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

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

private void removeShortcut(int menuItemKey) throws VerticalRemoveException {
    StringBuffer sql = XDG.generateUpdateSQL(db.tMenuItem,
            new Column[] { db.tMenuItem.mei_mei_lShortcut, db.tMenuItem.mei_bShortcutForward },
            new Column[] { db.tMenuItem.mei_lKey }, null);
    Connection con = null;//  w  ww  .ja  v a2  s  . c  o  m
    PreparedStatement prepStmt = null;
    try {
        con = getConnection();
        prepStmt = con.prepareStatement(sql.toString());
        prepStmt.setNull(1, Types.INTEGER);
        prepStmt.setNull(2, Types.INTEGER);
        prepStmt.setInt(3, menuItemKey);
        prepStmt.executeUpdate();
    } catch (SQLException sqle) {
        String message = "Failed to create menu item shortcut: %t";
        VerticalEngineLogger.errorCreate(this.getClass(), 0, message, sqle);
    } finally {
        close(con);
        close(prepStmt);
    }
}

From source file:com.gtwm.pb.model.manageData.DataManagement.java

/**
 * Used by both the public saveRecord and globalEdit methods
 *///ww  w. j  av  a 2s  . com
private void saveRecord(HttpServletRequest request, TableInfo table,
        LinkedHashMap<BaseField, BaseValue> dataToSave, boolean newRecord, Set<Integer> rowIds,
        SessionDataInfo sessionData, List<FileItem> multipartItems)
        throws InputRecordException, ObjectNotFoundException, SQLException, CantDoThatException,
        CodingErrorException, DisallowedException, MissingParametersException {
    if ((dataToSave.size() == 0) && (!newRecord)) {
        // Note: this does actually happen quite a lot, from two particular
        // users, therefore I've commented out the log warning.
        // Haven't tracked down the cause but it doesn't seem to be creating
        // a problem.
        // logger.warn("Call to saveRecord with no data to save. User = "
        // + request.getRemoteUser() + ", table = " + table + ", rowIds = "
        // + rowIds);
        return;
    }
    this.setHiddenFieldValues(request, table, dataToSave, newRecord);
    boolean globalEdit = false;
    int rowId = -1;
    if (rowIds.size() > 1) {
        globalEdit = true;
    } else if (rowIds.size() == 1) {
        rowId = (new LinkedList<Integer>(rowIds)).getFirst();
    } else {
        throw new ObjectNotFoundException("Row ID list " + rowIds + " is invalid");
    }
    StringBuilder SQLCodeBuilder = new StringBuilder();
    // Generate CSV of fields and placeholders to use in update/insert SQL
    // string
    StringBuilder fieldsCsvBuilder = new StringBuilder();
    StringBuilder fieldsAndPlaceholdersCsvBuilder = new StringBuilder();
    StringBuilder valuePlaceholdersCsvBuilder = new StringBuilder();
    for (BaseField field : dataToSave.keySet()) {
        fieldsCsvBuilder.append(field.getInternalFieldName());
        fieldsCsvBuilder.append(", ");
        valuePlaceholdersCsvBuilder.append("?, ");
        fieldsAndPlaceholdersCsvBuilder.append(field.getInternalFieldName());
        fieldsAndPlaceholdersCsvBuilder.append("=?, ");
    }
    // Used if doing an INSERT
    String fieldsCsv = fieldsCsvBuilder.toString();
    String valuePlaceholdersCsv = valuePlaceholdersCsvBuilder.toString();
    // Used if doing an UPDATE
    String fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsvBuilder.toString();
    if (!fieldsCsv.equals("")) {
        fieldsCsv = fieldsCsv.substring(0, fieldsCsv.length() - 2);
        valuePlaceholdersCsv = valuePlaceholdersCsv.substring(0, valuePlaceholdersCsv.length() - 2);
        fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsv.substring(0, fieldsAndPlaceholdersCsv.length() - 2);
    }
    if (newRecord) {
        SQLCodeBuilder.append("INSERT INTO " + table.getInternalTableName());
        if (fieldsCsv.equals("")) {
            SQLCodeBuilder.append(" VALUES(default)");
        } else {
            SQLCodeBuilder.append("(" + fieldsCsv + ") VALUES (" + valuePlaceholdersCsv + ")");
        }
    } else {
        SQLCodeBuilder.append("UPDATE " + table.getInternalTableName() + " SET " + fieldsAndPlaceholdersCsv);
        if (globalEdit) {
            // add filter for various row ids
            SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + " in (?");
            for (int i = 1; i < rowIds.size(); i++) {
                SQLCodeBuilder.append(",?");
            }
            SQLCodeBuilder.append(")");
        } else {
            // add filter for single row id
            SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + "=?");
        }
    }
    Connection conn = null;
    int fieldNumber = 0;
    // Will be set if we're inserting a record
    int newRowId = -1;
    TableDataInfo tableData = new TableData(table);
    try {
        conn = this.dataSource.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement statement = conn.prepareStatement(SQLCodeBuilder.toString());
        for (BaseField field : dataToSave.keySet()) {
            // If an exception is raised, currentField will be the field
            // which caused it
            // currentField = field;
            fieldNumber++;
            BaseValue fieldValue = dataToSave.get(field);
            if (field instanceof FileField) {
                if (fieldValue.isNull() || fieldValue.toString().equals("")) {
                    throw new InputRecordException("No file specified for the upload", field);
                }
            }
            if (fieldValue.isNull()) {
                statement.setNull(fieldNumber, Types.NULL);
            } else {
                if (fieldValue instanceof TextValue) {
                    String textValue = ((TextValue) fieldValue).toXmlString();
                    statement.setString(fieldNumber, textValue);
                } else if (fieldValue instanceof IntegerValue) {
                    // if no related value, set relation field to null
                    if (field instanceof RelationField && (((IntegerValue) fieldValue).getValueInteger() == -1)
                            || (fieldValue.isNull())) {
                        statement.setNull(fieldNumber, Types.NULL);
                    } else {
                        statement.setInt(fieldNumber, ((IntegerValue) fieldValue).getValueInteger());
                    }
                } else if (fieldValue instanceof DurationValue) {
                    statement.setString(fieldNumber, ((DurationValue) fieldValue).getSqlFormatInterval());
                } else if (fieldValue instanceof DecimalValue) {
                    statement.setDouble(fieldNumber, ((DecimalValue) fieldValue).getValueFloat());
                } else if (fieldValue instanceof DateValue) {
                    if (((DateValue) fieldValue).getValueDate() != null) {
                        java.util.Date javaDateValue = ((DateValue) fieldValue).getValueDate().getTime();
                        java.sql.Timestamp sqlTimestampValue = new java.sql.Timestamp(javaDateValue.getTime());
                        statement.setTimestamp(fieldNumber, sqlTimestampValue);
                    } else {
                        statement.setTimestamp(fieldNumber, null);
                    }
                } else if (fieldValue instanceof CheckboxValue) {
                    statement.setBoolean(fieldNumber, ((CheckboxValue) fieldValue).getValueBoolean());
                } else if (fieldValue instanceof FileValue) {
                    statement.setString(fieldNumber, ((FileValue) fieldValue).toString());
                } else {
                    throw new CodingErrorException("Field value " + fieldValue + " is of unknown type "
                            + fieldValue.getClass().getSimpleName());
                }
            }
        }
        // We've finished setting individual fields, if an SQL error occurs
        // after here we won't know which
        // field caused it without looking for it by other means
        // currentField = null;
        if (!newRecord) {
            if (globalEdit) {
                // Fill in the 'WHERE [row id field] in (?,..,?)' for use in
                // the UPDATE statement
                for (Integer aRowId : rowIds) {
                    if (tableData.isRecordLocked(conn, sessionData, aRowId)) {
                        throw new CantDoThatException(
                                "Record " + aRowId + " from table " + table + " is locked to prevent editing");
                    }
                    statement.setInt(++fieldNumber, aRowId);
                }
            } else {
                // Fill in the 'WHERE [row id field]=?' for use in the
                // UPDATE statement
                if (tableData.isRecordLocked(conn, sessionData, rowId)) {
                    throw new CantDoThatException(
                            "Record " + rowId + " from table " + table + " is locked to prevent editing");
                }
                statement.setInt(fieldNumber + 1, rowId);
            }
        }
        int numRowsAffected = statement.executeUpdate();
        statement.close();
        if ((numRowsAffected != 1) && (!globalEdit)) {
            conn.rollback();
            if (numRowsAffected > 0) {
                throw new ObjectNotFoundException(String.valueOf(numRowsAffected)
                        + " records would be altered during a single record save");
            } else {
                throw new ObjectNotFoundException(
                        "The current record can't be found to edit - perhaps someone else has deleted it");
            }
        }
        if (newRecord) {
            // Find the newly inserted Row ID
            // postgres-specific code, not database independent
            String SQLCode = "SELECT currval('" + table.getInternalTableName() + "_"
                    + table.getPrimaryKey().getInternalFieldName() + "_seq')";
            statement = conn.prepareStatement(SQLCode);
            ResultSet results = statement.executeQuery();
            if (results.next()) {
                newRowId = results.getInt(1);
            } else {
                results.close();
                statement.close();
                throw new SQLException(
                        "Row ID not found for the newly inserted record. '" + SQLCodeBuilder + "' didn't work");
            }
            results.close();
            statement.close();
        }
        conn.commit();
    } catch (SQLException sqlex) {
        // Find out which field caused the error by looking for internal
        // field names in the error message
        String errorMessage = sqlex.getMessage();
        for (BaseField possibleCauseField : dataToSave.keySet()) {
            if (errorMessage.contains(possibleCauseField.getInternalFieldName())) {
                if (errorMessage.contains("check constraint")) {
                    errorMessage = "The value " + dataToSave.get(possibleCauseField)
                            + " falls outside the allowed range";
                } else if (errorMessage.contains("not-null constraint")) {
                    errorMessage = "No value entered";
                } else if (errorMessage.contains("unique constraint")) {
                    errorMessage = "Value " + dataToSave.get(possibleCauseField)
                            + " is already in the database and cannot be entered again";
                } else if (errorMessage.contains("foreign key constraint")
                        && possibleCauseField instanceof RelationField) {
                    errorMessage = "Please select a valid "
                            + ((RelationField) possibleCauseField).getRelatedTable() + " record first";
                } else {
                    errorMessage = "Value " + dataToSave.get(possibleCauseField) + " not allowed ("
                            + Helpers.replaceInternalNames(errorMessage, table.getDefaultReport()) + ")";
                }
                throw new InputRecordException(errorMessage, possibleCauseField, sqlex);
            }
        }
        // Not able to find field
        errorMessage = Helpers.replaceInternalNames(errorMessage, table.getDefaultReport());
        throw new InputRecordException(errorMessage, null, sqlex);
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
    // If any fields were files to upload, do the actual uploads.
    // Do this after the commit in case the uploads take a long time and
    // time out the SQL connection.
    for (BaseField field : dataToSave.keySet()) {
        if (field instanceof FileField) {
            try {
                if (newRecord) {
                    this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), newRowId,
                            multipartItems);
                } else {
                    this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), rowId,
                            multipartItems);
                }
            } catch (CantDoThatException cdtex) {
                throw new InputRecordException("Error uploading file: " + cdtex.getMessage(), field, cdtex);
            } catch (FileUploadException fuex) {
                throw new InputRecordException("Error uploading file: " + fuex.getMessage(), field, fuex);
            }
        }
    }
    if (newRecord) {
        sessionData.setRowId(table, newRowId);
    }
    this.logLastDataChangeTime(request);
    logLastTableDataChangeTime(table);
    UsageLogger usageLogger = new UsageLogger(this.dataSource);
    AppUserInfo user = null;
    if (request.getRemoteUser() == null) {
        user = ServletUtilMethods.getPublicUserForRequest(request, this.authManager.getAuthenticator());
    } else {
        user = this.authManager.getUserByUserName(request, request.getRemoteUser());
    }
    // Send websocket notification
    // UsageLogger.sendNotification(user, table, sessionData.getReport(),
    // rowId, "edit", "Record saved: " + dataToSave);
    // Log everything apart from hidden (auto set) fields
    Map<BaseField, BaseValue> dataToLog = new LinkedHashMap<BaseField, BaseValue>();
    for (Map.Entry<BaseField, BaseValue> entrySet : dataToSave.entrySet()) {
        BaseField field = entrySet.getKey();
        if (!field.getHidden()) {
            BaseValue value = entrySet.getValue();
            dataToLog.put(field, value);
        }
    }
    if (newRecord) {
        usageLogger.logDataChange(user, table, null, AppAction.SAVE_NEW_RECORD, newRowId, dataToLog.toString());
    } else if (globalEdit) {
        // TODO: need better logging of global edits
        usageLogger.logDataChange(user, table, null, AppAction.GLOBAL_EDIT, rowId, dataToLog.toString());
    } else {
        BaseField fieldUpdated = null;
        Set<BaseField> fieldSet = new TreeSet<BaseField>();
        for (BaseField field : dataToSave.keySet()) {
            if (!field.getHidden()) {
                fieldSet.add(field);
            }
        }
        if (fieldSet.size() == 1) {
            fieldUpdated = new LinkedList<BaseField>(fieldSet).getFirst();
        }
        usageLogger.logDataChange(user, table, fieldUpdated, AppAction.UPDATE_RECORD, rowId,
                dataToLog.toString());
    }
    UsageLogger.startLoggingThread(usageLogger);
}

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

protected void setURLToNull(Connection con, MenuItemKey mikey) throws SQLException {
    PreparedStatement preparedStmt = null;

    try {//from w  w w  .j  a v a 2  s .  c o m
        preparedStmt = con
                .prepareStatement("UPDATE " + MENU_ITEM_TABLE + " SET mei_sURL = ? WHERE mei_lKey = ?");
        preparedStmt.setNull(1, Types.VARCHAR);
        preparedStmt.setInt(2, mikey.toInt());
        preparedStmt.executeUpdate();
        preparedStmt.close();
    } finally {
        close(preparedStmt);
    }
}

From source file:com.funambol.foundation.items.dao.PIMContactDAO.java

/**
 * Inserts the photo identified by the given id. The contact is NOT update.
 * Use deleteContactPhoto to update also the contact.
 * @param con the connection to use//from  w ww .  j a v a  2s .  co m
 * @param contactId the photo id
 * @param photo the Photo to insert
 * @throws com.funambol.foundation.exception.DAOException if an error
 *         occurs
 */
protected void insertPhoto(Connection con, Long contactId, Photo photo) throws DAOException {

    if (!verifyPermission(con, contactId)) {
        throw new DAOException("Contact '" + contactId + " is not a contact of the user '" + userId + "'");
    }

    PreparedStatement stmt = null;

    if (photo == null) {
        return;
    }
    byte[] image = photo.getImage();

    try {
        stmt = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_PHOTO);

        stmt.setLong(1, contactId);

        if (photo.getType() == null) {
            stmt.setNull(2, Types.VARCHAR);
        } else {
            stmt.setString(2, photo.getType());
        }

        if (image == null) {
            stmt.setNull(3, Types.BINARY);
        } else {
            stmt.setBinaryStream(3, new ByteArrayInputStream(image), image.length);
        }

        if (photo.getUrl() == null) {
            stmt.setNull(4, Types.VARCHAR);
        } else {
            stmt.setString(4, photo.getUrl());
        }

        stmt.execute();

    } catch (SQLException ex) {
        throw new DAOException("Error storing photo", ex);
    } finally {
        DBTools.close(null, stmt, null);
    }
}

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

/**
 *
 *///from  w w  w.ja  va 2 s  .  co m
protected void prepareTopicVersionStatement(TopicVersion topicVersion, PreparedStatement stmt)
        throws SQLException {
    int index = 1;
    if (!this.autoIncrementPrimaryKeys()) {
        stmt.setInt(index++, topicVersion.getTopicVersionId());
    }
    if (topicVersion.getEditDate() == null) {
        topicVersion.setEditDate(new Timestamp(System.currentTimeMillis()));
    }
    stmt.setInt(index++, topicVersion.getTopicId());
    stmt.setString(index++, topicVersion.getEditComment());
    stmt.setString(index++, topicVersion.getVersionContent());
    if (topicVersion.getAuthorId() == null) {
        stmt.setNull(index++, Types.INTEGER);
    } else {
        stmt.setInt(index++, topicVersion.getAuthorId());
    }
    stmt.setInt(index++, topicVersion.getEditType());
    stmt.setString(index++, topicVersion.getAuthorDisplay());
    stmt.setTimestamp(index++, topicVersion.getEditDate());
    if (topicVersion.getPreviousTopicVersionId() == null) {
        stmt.setNull(index++, Types.INTEGER);
    } else {
        stmt.setInt(index++, topicVersion.getPreviousTopicVersionId());
    }
    stmt.setInt(index++, topicVersion.getCharactersChanged());
    stmt.setString(index++, topicVersion.getVersionParamString());
}