Example usage for java.sql PreparedStatement setNull

List of usage examples for java.sql PreparedStatement 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.flexive.ejb.beans.structure.SelectListEngineBean.java

private long createList(FxSelectListEdit list) throws FxApplicationException {
    FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.SelectListEditor);
    checkValidListParameters(list);//  w w w . jav  a  2  s  . c  o  m
    long newId = seq.getId(FxSystemSequencer.SELECTLIST);
    list._synchronizeId(newId);
    //        System.out.println("Creating list " + list.getLabel() + " new id is " + newId);
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = Database.getDbConnection();
        //                                                            1  2        3    4
        ps = con.prepareStatement(
                "INSERT INTO " + TBL_STRUCT_SELECTLIST + "(ID,PARENTID,NAME,ALLOW_ITEM_CREATE," +
                //5              6            7            8     9             10
                        "ACL_CREATE_ITEM,ACL_ITEM_NEW,DEFAULT_ITEM,BCSEP,SAMELVLSELECT,SORTENTRIES)VALUES(?,?,?,?,?,?,?,?,?,?)");
        ps.setLong(1, newId);
        if (list.hasParentList())
            ps.setLong(2, list.getParentList().getId());
        else
            ps.setNull(2, java.sql.Types.INTEGER);
        ps.setString(3, list.getName().trim());
        ps.setBoolean(4, list.isAllowDynamicItemCreation());
        ps.setLong(5, list.getCreateItemACL().getId());
        ps.setLong(6, list.getNewItemACL().getId());
        ps.setNull(7, java.sql.Types.INTEGER);
        ps.setString(8, list.getBreadcrumbSeparator());
        ps.setBoolean(9, list.isOnlySameLevelSelect());
        ps.setBoolean(10, list.isSortEntries());
        ps.executeUpdate();
        Database.storeFxString(new FxString[] { list.getLabel(), list.getDescription() }, con,
                TBL_STRUCT_SELECTLIST, new String[] { "LABEL", "DESCRIPTION" }, "ID", newId);
        return newId;
    } catch (SQLException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } finally {
        Database.closeObjects(TypeEngineBean.class, con, ps);
    }
}

From source file:com.novartis.opensource.yada.adaptor.JDBCAdaptor.java

/**
* Sets a {@code ?i} parameter value mapped to the correct {@link java.sql.Types#INTEGER} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter/* w w w.jav a 2s.  co  m*/
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 5.1.0
*/
protected void setIntegerParameter(PreparedStatement pstmt, int index, char type, String val)
        throws SQLException {
    try {
        int ival = Integer.parseInt(val);
        pstmt.setInt(index, ival);
    } catch (NumberFormatException e) {
        l.warn("Error: " + e.getMessage() + " caused by " + e.getClass());
        l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                + "] to: null");
        pstmt.setNull(index, java.sql.Types.INTEGER);
    } catch (NullPointerException e) {
        l.warn("Error: " + e.getMessage() + " caused by " + e.getClass());
        l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                + "] to: null");
        pstmt.setNull(index, java.sql.Types.INTEGER);
    }
}

From source file:org.geowebcache.storage.jdbc.metastore.JDBCMBWrapper.java

public void putTile(TileObject stObj) throws SQLException {

    String query = "MERGE INTO "
            + "TILES(LAYER_ID,X,Y,Z,GRIDSET_ID,FORMAT_ID,PARAMETERS_ID,BLOB_SIZE,LOCK,CREATED) "
            + "KEY(LAYER_ID,X,Y,Z,GRIDSET_ID,FORMAT_ID,PARAMETERS_ID) " + "VALUES(?,?,?,?,?,?,?,?,NOW(),?)";

    long[] xyz = stObj.getXYZ();

    final Connection conn = getConnection();

    try {/*from w w w . j av a2  s.c om*/
        Long insertId;
        PreparedStatement prep = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        try {
            prep.setLong(1, stObj.getLayerId());
            prep.setLong(2, xyz[0]);
            prep.setLong(3, xyz[1]);
            prep.setLong(4, xyz[2]);
            prep.setLong(5, stObj.getGridSetIdId());
            prep.setLong(6, stObj.getFormatId());
            if (stObj.getParametersId() == -1L) {
                prep.setNull(7, java.sql.Types.BIGINT);
            } else {
                prep.setLong(7, stObj.getParametersId());
            }
            prep.setInt(8, stObj.getBlobSize());
            prep.setLong(9, System.currentTimeMillis());
            insertId = wrappedInsert(prep);
        } finally {
            close(prep);
        }
        if (insertId == null) {
            log.error("Did not receive a id for " + query);
        } else {
            stObj.setId(insertId.longValue());
        }

    } finally {
        conn.close();
    }

}

From source file:org.ohmage.query.impl.SurveyUploadQuery.java

/**
 * Creates the prompt response entry in the corresponding table and saves
 * any attached files, images, videos, etc..
 * /*from ww  w.  ja va 2 s.co  m*/
 * @param username
 *        The username of the user saving this prompt response.
 * 
 * @param client
 *        The name of the device used to generate the response.
 * 
 * @param surveyResponseId
 *        The unique identifier for this survey response.
 * 
 * @param fileList
 *        The list of files saved to the disk, which should be a reference
 *        to a list that will be populated by this function.
 * 
 * @param promptUploadList
 *        The collection of prompt responses to store.
 * 
 * @param repeatableSetIteration
 *        If these prompt responses were part of a repeatable set, this is
 *        the iteration of that repeatable set; otherwise, null.
 * 
 * @param bufferedImageMap
 *        The map of image IDs to their contents.
 * 
 * @param videoContentsMap
 *        The map of video IDs to their contents.
 * 
 * @param transactionManager
 *        The manager for this transaction.
 * 
 * @param status
 *        The status of this transaction.
 * 
 * @throws DataAccessException
 *         There was an error saving the information.
 */
private void createPromptResponse(final String username, final String client, final Number surveyResponseId,
        final List<File> fileList, final Collection<Response> promptUploadList,
        final Integer repeatableSetIteration, final Map<UUID, Image> bufferedImageMap,
        final Map<String, Video> videoContentsMap, final Map<String, Audio> audioContentsMap,
        final DataSourceTransactionManager transactionManager, final TransactionStatus status)
        throws DataAccessException {

    for (Response response : promptUploadList) {
        if (response instanceof RepeatableSetResponse) {
            Map<Integer, Map<Integer, Response>> iterationToResponse = ((RepeatableSetResponse) response)
                    .getResponseGroups();

            for (Integer iteration : iterationToResponse.keySet()) {
                createPromptResponse(username, client, surveyResponseId, fileList,
                        iterationToResponse.get(iteration).values(), iteration, bufferedImageMap,
                        videoContentsMap, audioContentsMap, transactionManager, status);
            }
            continue;
        }
        final PromptResponse promptResponse = (PromptResponse) response;

        getJdbcTemplate().update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(SQL_INSERT_PROMPT_RESPONSE);
                ps.setLong(1, surveyResponseId.longValue());

                RepeatableSet parent = promptResponse.getPrompt().getParent();
                if (parent == null) {
                    ps.setNull(2, java.sql.Types.NULL);
                    ps.setNull(3, java.sql.Types.NULL);
                } else {
                    ps.setString(2, parent.getId());
                    ps.setInt(3, repeatableSetIteration);
                }
                ps.setString(4, promptResponse.getPrompt().getType().toString());
                ps.setString(5, promptResponse.getPrompt().getId());

                Object response = promptResponse.getResponse();
                if (response instanceof DateTime) {
                    ps.setString(6, DateTimeUtils.getW3cIso8601DateString((DateTime) response, true));
                } else if ((promptResponse instanceof MultiChoiceCustomPromptResponse)
                        && (response instanceof Collection)) {
                    JSONArray json = new JSONArray();

                    for (Object currResponse : (Collection<?>) response) {
                        json.put(currResponse);
                    }

                    ps.setString(6, json.toString());
                } else {
                    ps.setString(6, response.toString());
                }

                return ps;
            }
        });

        if (promptResponse instanceof PhotoPromptResponse) {
            // Grab the associated image and save it
            String imageId = promptResponse.getResponse().toString();

            // If it wasn't skipped and it was displayed, save the
            // associated images.
            if (!JsonInputKeys.PROMPT_SKIPPED.equals(imageId)
                    && !JsonInputKeys.PROMPT_NOT_DISPLAYED.equals(imageId)
                    && !JsonInputKeys.IMAGE_NOT_UPLOADED.equals(imageId)) {

                // Get the directory to save the image and save it.
                File originalFile;
                try {
                    originalFile = bufferedImageMap.get(UUID.fromString(imageId)).saveImage(getDirectory());
                } catch (DomainException e) {
                    rollback(transactionManager, status);
                    throw new DataAccessException("Error saving the images.", e);
                }

                // Get the image's URL.
                String url = "file://" + originalFile.getAbsolutePath();
                // Insert the image URL into the database.
                try {
                    getJdbcTemplate().update(SQL_INSERT_IMAGE, new Object[] { username, client, imageId, url });
                } catch (org.springframework.dao.DataAccessException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException("Error executing SQL '" + SQL_INSERT_IMAGE
                            + "' with parameters: " + username + ", " + client + ", " + imageId + ", " + url,
                            e);
                }
            }
        }
        // Save the video.
        else if (promptResponse instanceof VideoPromptResponse) {
            // Make sure the response contains an actual video response.
            Object responseValue = promptResponse.getResponse();
            if (!((responseValue instanceof NoResponse) || (responseValue instanceof NoResponseMedia))) {

                // Attempt to write it to the file system.
                try {
                    // Get the current video directory.
                    File currVideoDirectory = VideoDirectoryCache.getDirectory();

                    // Get the video ID.
                    String responseValueString = responseValue.toString();

                    // Get the video object.
                    Video video = videoContentsMap.get(responseValueString);

                    // Get the file.
                    File videoFile = new File(currVideoDirectory.getAbsolutePath() + "/" + responseValueString
                            + "." + video.getType());

                    // Get the video contents.
                    InputStream content = video.getContentStream();
                    if (content == null) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("The video contents did not exist in the map.");
                    }

                    // Write the video contents to disk.
                    FileOutputStream fos = new FileOutputStream(videoFile);

                    // Write the content to the output stream.
                    int bytesRead;
                    byte[] buffer = new byte[4096];
                    while ((bytesRead = content.read(buffer)) != -1) {
                        fos.write(buffer, 0, bytesRead);
                    }
                    fos.close();

                    // Store the file reference in the video list.
                    fileList.add(videoFile);

                    // Get the video's URL.
                    String url = "file://" + videoFile.getAbsolutePath();

                    // Insert the video URL into the database.
                    try {
                        getJdbcTemplate().update(SQL_INSERT_IMAGE,
                                new Object[] { username, client, responseValueString, url });
                    } catch (org.springframework.dao.DataAccessException e) {
                        videoFile.delete();
                        transactionManager.rollback(status);
                        throw new DataAccessException(
                                "Error executing SQL '" + SQL_INSERT_IMAGE + "' with parameters: " + username
                                        + ", " + client + ", " + responseValueString + ", " + url,
                                e);
                    }
                }
                // If it fails, roll back the transaction.
                catch (DomainException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException("Could not get the video directory.", e);
                } catch (IOException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException("Could not write the file.", e);
                }
            }
        } else if (promptResponse instanceof AudioPromptResponse) {
            // Make sure the response contains an actual audio response.
            Object responseValue = promptResponse.getResponse();
            if (!((responseValue instanceof NoResponse) || (responseValue instanceof NoResponseMedia))) {

                // Attempt to write it to the file system.
                try {
                    // Get the current audio directory.
                    File currAudioDirectory = AudioDirectoryCache.getDirectory();

                    // Get the audio ID.
                    String responseValueString = responseValue.toString();

                    // Get the audio object.
                    Audio audio = audioContentsMap.get(responseValueString);

                    // Get the file.
                    File audioFile = new File(currAudioDirectory.getAbsolutePath() + "/" + responseValueString
                            + "." + audio.getType());

                    // Get the video contents.
                    InputStream content = audio.getContentStream();
                    if (content == null) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("The audio contents did not exist in the map.");
                    }

                    // Write the video contents to disk.
                    FileOutputStream fos = new FileOutputStream(audioFile);

                    // Write the content to the output stream.
                    int bytesRead;
                    byte[] buffer = new byte[4096];
                    while ((bytesRead = content.read(buffer)) != -1) {
                        fos.write(buffer, 0, bytesRead);
                    }
                    fos.close();

                    // Store the file reference in the video list.
                    fileList.add(audioFile);

                    // Get the video's URL.
                    String url = "file://" + audioFile.getAbsolutePath();

                    // Insert the video URL into the database.
                    try {
                        getJdbcTemplate().update(SQL_INSERT_IMAGE,
                                new Object[] { username, client, responseValueString, url });
                    } catch (org.springframework.dao.DataAccessException e) {
                        audioFile.delete();
                        transactionManager.rollback(status);
                        throw new DataAccessException(
                                "Error executing SQL '" + SQL_INSERT_IMAGE + "' with parameters: " + username
                                        + ", " + client + ", " + responseValueString + ", " + url,
                                e);
                    }
                }
                // If it fails, roll back the transaction.
                catch (DomainException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException("Could not get the video directory.", e);
                } catch (IOException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException("Could not write the file.", e);
                }
            }
        }
    }
}

From source file:org.openbel.framework.core.kam.JdbcKAMLoaderImpl.java

/**
 * {@inheritDoc}/*from w ww .  j a v a2s  .  c  om*/
 */
@Override
public void loadEdges(StatementTable st, TermTable tt, ProtoNodeTable pnt, ProtoEdgeTable pet)
        throws SQLException {
    // load kam edges and associate to kam nodes (global terms)
    PreparedStatement keps = getPreparedStatement(KAM_EDGE_SQL);

    final Map<Integer, Integer> eqn = pnt.getEquivalences();
    final Map<Integer, Integer> eqs = pet.getEquivalences();
    final List<TableProtoEdge> edges = pet.getProtoEdges();
    Set<Integer> added = new HashSet<Integer>();

    for (int i = 0, n = edges.size(); i < n; i++) {
        final Integer eqId = eqs.get(i);

        // continue if we have already seen this equivalent proto edge
        if (added.contains(eqId)) {
            continue;
        }

        added.add(eqId);

        final TableProtoEdge edge = edges.get(i);

        // XXX offset
        keps.setInt(1, eqId + 1);
        // XXX offset
        keps.setInt(2, eqn.get(edge.getSource()) + 1);
        // XXX offset
        keps.setInt(3, eqn.get(edge.getTarget()) + 1);

        RelationshipType r = RelationshipType.getRelationshipType(edge.getRel());
        keps.setInt(4, r.getValue());
        keps.addBatch();
    }

    keps.executeBatch();

    // load statements
    final List<StatementTable.TableStatement> ts = st.getStatements();
    final Map<Integer, Integer> sdm = st.getStatementDocument();

    PreparedStatement sps = getPreparedStatement(STATEMENT_SQL);
    for (int i = 0, n = ts.size(); i < n; i++) {
        final TableStatement stmt = ts.get(i);

        // XXX offset
        sps.setInt(1, i + 1);
        // XXX offset
        sps.setInt(2, sdm.get(i) + 1);
        // XXX offset
        sps.setInt(3, stmt.getSubjectTermId() + 1);

        if (stmt.getRelationshipName() == null) {
            // load definitional statement
            sps.setNull(4, Types.INTEGER);
            sps.setNull(5, Types.INTEGER);
            sps.setNull(6, Types.INTEGER);
            sps.setNull(7, Types.INTEGER);
            sps.setNull(8, Types.INTEGER);
        } else if (stmt.getObjectTermId() != null) {
            // load simple statement
            RelationshipType r = RelationshipType.getRelationshipType(stmt.getRelationshipName());
            sps.setInt(4, r.getValue());

            // XXX offset
            sps.setInt(5, stmt.getObjectTermId() + 1);
            sps.setNull(6, Types.INTEGER);
            sps.setNull(7, Types.INTEGER);
            sps.setNull(8, Types.INTEGER);
        } else {
            // load nested statement
            RelationshipType r = RelationshipType.getRelationshipType(stmt.getRelationshipName());
            sps.setInt(4, r.getValue());

            // set null for object term since this is a nested statement
            sps.setNull(5, Types.INTEGER);

            // XXX offset
            sps.setInt(6, stmt.getNestedSubject() + 1);

            RelationshipType nr = RelationshipType.getRelationshipType(stmt.getNestedRelationship());

            sps.setInt(7, nr.getValue());

            // XXX offset
            sps.setInt(8, stmt.getNestedObject() + 1);
        }
        sps.addBatch();
    }

    sps.executeBatch();

    // load many-to-many association of edges to statements
    PreparedStatement skes = getPreparedStatement(KAM_EDGE_STATEMENT_SQL);
    final Map<Integer, Set<Integer>> edgeStmts = pet.getEdgeStatements();
    added.clear();
    for (int i = 0, n = edges.size(); i < n; i++) {
        final Integer eqId = eqs.get(i);

        // continue if we have already seen this equivalent proto edge
        if (added.contains(eqId)) {
            continue;
        }

        added.add(eqId);

        // retrieve statements for this edge
        final Set<Integer> stmtIds = edgeStmts.get(i);

        // if we have the edge, then assert that we have its statements
        assert stmtIds != null && !stmtIds.isEmpty();

        for (final Integer stmtId : stmtIds) {
            // XXX offset
            skes.setInt(1, eqId + 1);
            // XXX offset
            skes.setInt(2, stmtId + 1);
            skes.addBatch();
        }
    }

    skes.executeBatch();
}

From source file:com.novartis.opensource.yada.adaptor.JDBCAdaptor.java

/**
* Sets a {@code ?n} parameter value mapped to the correct {@link java.sql.Types#FLOAT} JDBC setter.  
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter/*from  w w w . ja v a  2 s  . c om*/
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 5.1.0
*/
protected void setNumberParameter(PreparedStatement pstmt, int index, char type, String val)
        throws SQLException {
    try {
        float fval = Float.parseFloat(val);
        pstmt.setFloat(index, fval);
    } catch (NumberFormatException | NullPointerException e) {
        l.warn("Error: " + e.getMessage() + " caused by " + e.getClass());
        l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                + "] to: null");
        pstmt.setNull(index, java.sql.Types.INTEGER);
    }
}

From source file:com.wso2telco.dep.ratecardservice.dao.OperationRateDAO.java

public OperationRateDTO addOperationRate(OperationRateDTO operationRate) throws BusinessException {

    Connection con = null;/*from  w w  w .  j a va  2s. co  m*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer operationRateId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.OPERATION_RATE.getTObject());
        query.append(" (operator_id, api_operationid, rate_defid, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addOperationRate : " + ps);

        Integer operatorId = operationRate.getOperator().getOperatorId();
        if (operatorId != null) {
            ps.setInt(1, operatorId);
        } else {
            ps.setNull(1, Types.INTEGER);
        }

        ps.setInt(2, operationRate.getApiOperation().getApiOperationId());
        ps.setInt(3, operationRate.getRateDefinition().getRateDefId());
        ps.setString(4, operationRate.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            operationRateId = rs.getInt(1);
        }

        operationRate.setOperationRateId(operationRateId);
    } catch (SQLException e) {

        log.error("database operation error in addOperationRate : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addOperationRate : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return operationRate;
}

From source file:com.alfaariss.oa.engine.tgt.jdbc.JDBCTGTAliasStore.java

/**
 * @see com.alfaariss.oa.engine.core.tgt.factory.ITGTAliasStore#removeAlias(java.lang.String, java.lang.String, java.lang.String)
 *///  ww  w .j a  v  a2  s .co m
public void removeAlias(String type, String entityID, String alias) throws OAException {
    Connection connection = null;
    PreparedStatement ps = null;
    StringBuffer sbQueryRemove = null;
    try {
        sbQueryRemove = new StringBuffer("UPDATE ");
        sbQueryRemove.append(_sAliasTableName);
        sbQueryRemove.append(" SET ");
        sbQueryRemove.append(type);
        sbQueryRemove.append("=? WHERE ");
        sbQueryRemove.append(_sAliasColumnEntityID);
        sbQueryRemove.append("=? AND ");
        sbQueryRemove.append(type);
        sbQueryRemove.append("=?");

        connection = _oDataSource.getConnection();
        ps = connection.prepareStatement(sbQueryRemove.toString());
        ps.setNull(1, Types.VARCHAR);
        ps.setString(2, entityID);
        ps.setString(3, alias);
        ps.executeUpdate();
    } catch (SQLException e) {
        _logger.error("Could not execute alias remove query: " + sbQueryRemove.toString(), e);
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            _logger.debug("Could not close statement", e);
        }
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            _logger.debug("Could not close connection", e);
        }
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/**
 * Set the CLOB maxlength./*from   w w  w . j  a va  2s. com*/
 * If contents.length() > maxSize, contents is truncated to contain
 * the first maxSize characters of the contents, and a warning is logged.
 * @param s a prepared statement
 * @param fieldNum the field-index, where the contents are inserted
 * @param contents the contents
 * @param maxSize the maxsize for this contents
 * @param o the Object, which is assumed to have a field named fieldName
 * @param fieldName a given field (Assumed to present in Object o)
 * @throws SQLException If fieldNum does not correspond to a
 * parameter marker in the PreparedStatement, or a database access error
 * occurs or this method is called on a closed PreparedStatement
 */
public static void setClobMaxLength(PreparedStatement s, int fieldNum, String contents, long maxSize, Object o,
        String fieldName) throws SQLException {
    ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
    if (contents != null) {
        if (contents.length() > maxSize) {
            log.warn("The field '" + fieldName + "' is " + contents.length() + " characters long, which is "
                    + (contents.length() - maxSize) + " longer than the allowed " + maxSize
                    + " characters. The contents is now truncated to " + "length " + maxSize);
            // This caused OOM if both the 'contents' and o.toString() was large
            // (See NAS-2015).
            // It is therefore omitted from this log-entry.

            // truncate to length maxSize (if maxSize <= Integer.MAX_VALUE)
            // else truncate to length Integer.MAX_VALUE
            if (maxSize > Integer.MAX_VALUE) {
                log.warn("The maxSize is larger than maxint (" + Integer.MAX_VALUE
                        + "), which is not allowed. MaxSize changed to maxint");
                maxSize = Integer.MAX_VALUE;
            }
            contents = contents.substring(0, (int) maxSize);
        }
        s.setCharacterStream(fieldNum, new StringReader(contents), contents.length());
        s.setString(fieldNum, contents);
    } else {
        s.setNull(fieldNum, Types.CLOB);
    }
}

From source file:org.apache.james.mailrepository.jdbc.JDBCMailRepository.java

/**
 * @see org.apache.james.mailrepository.lib.AbstractMailRepository#internalStore(Mail)
 *///from   w  w  w .  ja  v a  2  s. c o  m
protected void internalStore(Mail mc) throws IOException, MessagingException {
    Connection conn = null;
    try {
        conn = datasource.getConnection();
        // Need to determine whether need to insert this record, or update
        // it.

        // Determine whether the message body has changed, and possibly
        // avoid
        // updating the database.
        boolean saveBody;

        MimeMessage messageBody = mc.getMessage();
        // if the message is a CopyOnWrite proxy we check the modified
        // wrapped object.
        if (messageBody instanceof MimeMessageCopyOnWriteProxy) {
            MimeMessageCopyOnWriteProxy messageCow = (MimeMessageCopyOnWriteProxy) messageBody;
            messageBody = messageCow.getWrappedMessage();
        }
        if (messageBody instanceof MimeMessageWrapper) {
            MimeMessageWrapper message = (MimeMessageWrapper) messageBody;
            saveBody = message.isModified();
            if (saveBody) {
                message.loadMessage();
            }
        } else {
            saveBody = true;
        }
        MessageInputStream is = new MessageInputStream(mc, sr, inMemorySizeLimit, true);

        // Begin a transaction
        conn.setAutoCommit(false);

        PreparedStatement checkMessageExists = null;
        ResultSet rsExists = null;
        boolean exists = false;
        try {
            checkMessageExists = conn.prepareStatement(sqlQueries.getSqlString("checkMessageExistsSQL", true));
            checkMessageExists.setString(1, mc.getName());
            checkMessageExists.setString(2, repositoryName);
            rsExists = checkMessageExists.executeQuery();
            exists = rsExists.next() && rsExists.getInt(1) > 0;
        } finally {
            theJDBCUtil.closeJDBCResultSet(rsExists);
            theJDBCUtil.closeJDBCStatement(checkMessageExists);
        }

        if (exists) {
            // MessageInputStream is = new
            // MessageInputStream(mc,sr,inMemorySizeLimit, true);

            // Update the existing record
            PreparedStatement updateMessage = null;

            try {
                updateMessage = conn.prepareStatement(sqlQueries.getSqlString("updateMessageSQL", true));
                updateMessage.setString(1, mc.getState());
                updateMessage.setString(2, mc.getErrorMessage());
                if (mc.getSender() == null) {
                    updateMessage.setNull(3, java.sql.Types.VARCHAR);
                } else {
                    updateMessage.setString(3, mc.getSender().toString());
                }
                StringBuilder recipients = new StringBuilder();
                for (Iterator<MailAddress> i = mc.getRecipients().iterator(); i.hasNext();) {
                    recipients.append(i.next().toString());
                    if (i.hasNext()) {
                        recipients.append("\r\n");
                    }
                }
                updateMessage.setString(4, recipients.toString());
                updateMessage.setString(5, mc.getRemoteHost());
                updateMessage.setString(6, mc.getRemoteAddr());
                updateMessage.setTimestamp(7, new java.sql.Timestamp(mc.getLastUpdated().getTime()));
                updateMessage.setString(8, mc.getName());
                updateMessage.setString(9, repositoryName);
                updateMessage.execute();
            } finally {
                Statement localUpdateMessage = updateMessage;
                // Clear reference to statement
                updateMessage = null;
                theJDBCUtil.closeJDBCStatement(localUpdateMessage);
            }

            // Determine whether attributes are used and available for
            // storing
            if (jdbcMailAttributesReady && mc.hasAttributes()) {
                String updateMessageAttrSql = sqlQueries.getSqlString("updateMessageAttributesSQL", false);
                PreparedStatement updateMessageAttr = null;
                try {
                    updateMessageAttr = conn.prepareStatement(updateMessageAttrSql);
                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                    ObjectOutputStream oos = new ObjectOutputStream(baos);
                    try {
                        if (mc instanceof MailImpl) {
                            oos.writeObject(((MailImpl) mc).getAttributesRaw());
                        } else {
                            HashMap<String, Serializable> temp = new HashMap<String, Serializable>();
                            for (Iterator<String> i = mc.getAttributeNames(); i.hasNext();) {
                                String hashKey = i.next();
                                temp.put(hashKey, mc.getAttribute(hashKey));
                            }
                            oos.writeObject(temp);
                        }
                        oos.flush();
                        ByteArrayInputStream attrInputStream = new ByteArrayInputStream(baos.toByteArray());
                        updateMessageAttr.setBinaryStream(1, attrInputStream, baos.size());
                    } finally {
                        try {
                            if (oos != null) {
                                oos.close();
                            }
                        } catch (IOException ioe) {
                            getLogger().debug(
                                    "JDBCMailRepository: Unexpected exception while closing output stream.",
                                    ioe);
                        }
                    }
                    updateMessageAttr.setString(2, mc.getName());
                    updateMessageAttr.setString(3, repositoryName);
                    updateMessageAttr.execute();
                } catch (SQLException sqle) {
                    getLogger().info("JDBCMailRepository: Trying to update mail attributes failed.", sqle);

                } finally {
                    theJDBCUtil.closeJDBCStatement(updateMessageAttr);
                }
            }

            if (saveBody) {

                PreparedStatement updateMessageBody = conn
                        .prepareStatement(sqlQueries.getSqlString("updateMessageBodySQL", true));
                try {
                    updateMessageBody.setBinaryStream(1, is, (int) is.getSize());
                    updateMessageBody.setString(2, mc.getName());
                    updateMessageBody.setString(3, repositoryName);
                    updateMessageBody.execute();

                } finally {
                    theJDBCUtil.closeJDBCStatement(updateMessageBody);
                }
            }

        } else {
            // Insert the record into the database
            PreparedStatement insertMessage = null;
            try {
                String insertMessageSQL = sqlQueries.getSqlString("insertMessageSQL", true);
                int number_of_parameters = getNumberOfParameters(insertMessageSQL);
                insertMessage = conn.prepareStatement(insertMessageSQL);
                insertMessage.setString(1, mc.getName());
                insertMessage.setString(2, repositoryName);
                insertMessage.setString(3, mc.getState());
                insertMessage.setString(4, mc.getErrorMessage());
                if (mc.getSender() == null) {
                    insertMessage.setNull(5, java.sql.Types.VARCHAR);
                } else {
                    insertMessage.setString(5, mc.getSender().toString());
                }
                StringBuilder recipients = new StringBuilder();
                for (Iterator<MailAddress> i = mc.getRecipients().iterator(); i.hasNext();) {
                    recipients.append(i.next().toString());
                    if (i.hasNext()) {
                        recipients.append("\r\n");
                    }
                }
                insertMessage.setString(6, recipients.toString());
                insertMessage.setString(7, mc.getRemoteHost());
                insertMessage.setString(8, mc.getRemoteAddr());
                insertMessage.setTimestamp(9, new java.sql.Timestamp(mc.getLastUpdated().getTime()));

                insertMessage.setBinaryStream(10, is, (int) is.getSize());

                // Store attributes
                if (number_of_parameters > 10) {
                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                    ObjectOutputStream oos = new ObjectOutputStream(baos);
                    try {
                        if (mc instanceof MailImpl) {
                            oos.writeObject(((MailImpl) mc).getAttributesRaw());
                        } else {
                            HashMap<String, Serializable> temp = new HashMap<String, Serializable>();
                            for (Iterator<String> i = mc.getAttributeNames(); i.hasNext();) {
                                String hashKey = i.next();
                                temp.put(hashKey, mc.getAttribute(hashKey));
                            }
                            oos.writeObject(temp);
                        }
                        oos.flush();
                        ByteArrayInputStream attrInputStream = new ByteArrayInputStream(baos.toByteArray());
                        insertMessage.setBinaryStream(11, attrInputStream, baos.size());
                    } finally {
                        try {
                            if (oos != null) {
                                oos.close();
                            }
                        } catch (IOException ioe) {
                            getLogger().debug(
                                    "JDBCMailRepository: Unexpected exception while closing output stream.",
                                    ioe);
                        }
                    }
                }

                insertMessage.execute();
            } finally {
                theJDBCUtil.closeJDBCStatement(insertMessage);
            }
        }

        conn.commit();
        conn.setAutoCommit(true);
    } catch (SQLException e) {
        getLogger().debug("Failed to store internal mail", e);
        throw new IOException(e.getMessage());
    } finally {
        theJDBCUtil.closeJDBCConnection(conn);
    }
}