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.act.lcms.db.model.FeedingLCMSWell.java

protected void bindInsertOrUpdateParameters(PreparedStatement stmt, Integer plateId, Integer plateRow,
        Integer plateColumn, String msid, String composition, String extract, String chemical,
        Double concentration, String note) throws SQLException {
    stmt.setInt(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), plateId);
    stmt.setInt(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), plateRow);
    stmt.setInt(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), plateColumn);
    stmt.setString(DB_FIELD.MSID.getInsertUpdateOffset(), msid);
    stmt.setString(DB_FIELD.COMPOSITION.getInsertUpdateOffset(), composition);
    stmt.setString(DB_FIELD.EXTRACT.getInsertUpdateOffset(), extract);
    stmt.setString(DB_FIELD.CHEMICAL.getInsertUpdateOffset(), chemical);
    if (concentration != null) {
        stmt.setDouble(DB_FIELD.CONCENTRATION.getInsertUpdateOffset(), concentration);
    } else {/*w ww  .  ja  v  a 2 s  . co  m*/
        stmt.setNull(DB_FIELD.CONCENTRATION.getInsertUpdateOffset(), Types.DOUBLE);
    }
    stmt.setString(DB_FIELD.NOTE.getInsertUpdateOffset(), note);
}

From source file:org.apereo.portal.security.provider.RDBMPermissionImpl.java

/**
 * Set the params on the PreparedStatement and execute the insert.
 * @param perm org.apereo.portal.security.IPermission
 * @param ps java.sql.PreparedStatement - the PreparedStatement for inserting a Permission row.
 * @exception Exception//from  w  w w.  j  a v a2 s . c o m
 */
private void primAdd(IPermission perm, PreparedStatement ps) throws Exception {
    java.sql.Timestamp ts = null;

    // NON-NULL COLUMNS:
    ps.clearParameters();
    ps.setString(1, perm.getOwner());
    ps.setInt(2, getPrincipalType(perm));
    ps.setString(3, getPrincipalKey(perm));
    ps.setString(4, perm.getActivity());
    ps.setString(5, perm.getTarget());
    // TYPE:
    if (perm.getType() == null) {
        ps.setNull(6, Types.VARCHAR);
    } else {
        ps.setString(6, perm.getType());
    }
    // EFFECTIVE:
    if (perm.getEffective() == null) {
        ps.setNull(7, Types.TIMESTAMP);
    } else {
        ts = new java.sql.Timestamp(perm.getEffective().getTime());
        ps.setTimestamp(7, ts);
    }
    // EXPIRES:
    if (perm.getExpires() == null) {
        ps.setNull(8, Types.TIMESTAMP);
    } else {
        ts = new java.sql.Timestamp(perm.getExpires().getTime());
        ps.setTimestamp(8, ts);
    }
}

From source file:org.apache.nifi.admin.dao.impl.StandardUserDAO.java

@Override
public void updateUser(NiFiUser user) throws DataAccessException {
    PreparedStatement statement = null;
    try {//from  www . j  ava 2 s.co  m
        // create a statement
        statement = connection.prepareStatement(UPDATE_USER);
        statement.setString(1, StringUtils.left(user.getIdentity(), 4096));
        statement.setString(2, StringUtils.left(user.getUserName(), 4096));
        statement.setString(3, StringUtils.left(user.getUserGroup(), 100));
        statement.setString(6, StringUtils.left(user.getJustification(), 500));
        statement.setString(7, user.getStatus().toString());
        statement.setString(8, user.getId());

        // set the last accessed time accordingly
        if (user.getLastAccessed() == null) {
            statement.setNull(4, Types.TIMESTAMP);
        } else {
            statement.setTimestamp(4, new java.sql.Timestamp(user.getLastAccessed().getTime()));
        }

        // set the last verified time accordingly
        if (user.getLastVerified() == null) {
            statement.setNull(5, Types.TIMESTAMP);
        } else {
            statement.setTimestamp(5, new java.sql.Timestamp(user.getLastVerified().getTime()));
        }

        // perform the update
        int updateCount = statement.executeUpdate();
        if (updateCount != 1) {
            throw new DataAccessException("Unable to update user.");
        }
    } catch (SQLException sqle) {
        throw new DataAccessException(sqle);
    } catch (DataAccessException dae) {
        throw dae;
    } finally {
        RepositoryUtils.closeQuietly(statement);
    }
}

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

/**
 * {@inheritDoc}//from www  .  j ava 2s . co  m
 */
@Override
public void loadAnnotationDefinitions(AnnotationDefinitionTable adt) throws SQLException {
    PreparedStatement adps = getPreparedStatement(ANNOTATION_DEFINITION_SQL);

    for (Map.Entry<Integer, TableAnnotationDefinition> ade : adt.getIndexDefinition().entrySet()) {
        TableAnnotationDefinition ad = ade.getValue();
        adps.setInt(1, (ade.getKey() + 1));
        adps.setString(2, ad.getName());

        if (AnnotationDefinitionTable.URL_ANNOTATION_TYPE_ID == ad.getAnnotationType()) {
            adps.setNull(3, Types.VARCHAR);
            adps.setNull(4, Types.VARCHAR);
        } else {
            adps.setString(3, StringUtils.abbreviate(ad.getDescription(), MAX_MEDIUM_VARCHAR_LENGTH));
            adps.setString(4, StringUtils.abbreviate(ad.getUsage(), MAX_MEDIUM_VARCHAR_LENGTH));
        }

        final int oid;
        final String domain = ad.getAnnotationDomain();
        final Integer objectId = valueIndexMap.get(domain);
        if (objectId != null) {
            oid = objectId;
        } else {
            oid = saveObject(1, domain);
            valueIndexMap.put(domain, oid);
        }

        adps.setInt(5, oid);
        adps.setInt(6, ad.getAnnotationType());
        adps.addBatch();
    }

    adps.executeBatch();

    // associate annotate definitions to documents
    PreparedStatement dadmps = getPreparedStatement(DOCUMENT_ANNOTATION_DEFINITION_MAP_SQL);
    Map<Integer, Set<Integer>> dadm = adt.getDocumentAnnotationDefinitions();

    Set<Entry<Integer, Set<Integer>>> entries = dadm.entrySet();
    for (final Entry<Integer, Set<Integer>> entry : entries) {
        final Integer key = entry.getKey();
        for (final Integer adid : entry.getValue()) {
            dadmps.setInt(1, (key + 1));
            dadmps.setInt(2, (adid + 1));
            dadmps.addBatch();
        }
        dadmps.executeBatch();
    }
}

From source file:org.infoglue.cms.util.workflow.InfoGlueJDBCPropertySet.java

private void setValues(PreparedStatement ps, int type, String key, Object value)
        throws SQLException, PropertyException {
    // Patched by Edson Richter for MS SQL Server JDBC Support!
    String driverName;// w  w w.j a  v a2 s .c  o  m

    try {
        driverName = ps.getConnection().getMetaData().getDriverName().toUpperCase();
    } catch (Exception e) {
        driverName = "";
    }

    ps.setNull(1, Types.VARCHAR);
    ps.setNull(2, Types.TIMESTAMP);

    // Patched by Edson Richter for MS SQL Server JDBC Support!
    // Oracle support suggestion also Michael G. Slack
    if ((driverName.indexOf("SQLSERVER") >= 0) || (driverName.indexOf("ORACLE") >= 0)) {
        ps.setNull(3, Types.BINARY);
    } else {
        ps.setNull(3, Types.BLOB);
    }

    ps.setNull(4, Types.FLOAT);
    ps.setNull(5, Types.NUMERIC);
    ps.setInt(6, type);
    ps.setString(7, globalKey);
    ps.setString(8, key);

    switch (type) {
    case PropertySet.BOOLEAN:

        Boolean boolVal = (Boolean) value;
        ps.setInt(5, boolVal.booleanValue() ? 1 : 0);

        break;

    case PropertySet.DATA:

        Data data = (Data) value;
        ps.setBytes(3, data.getBytes());

        break;

    case PropertySet.DATE:

        Date date = (Date) value;
        ps.setTimestamp(2, new Timestamp(date.getTime()));

        break;

    case PropertySet.DOUBLE:

        Double d = (Double) value;
        ps.setDouble(4, d.doubleValue());

        break;

    case PropertySet.INT:

        Integer i = (Integer) value;
        ps.setInt(5, i.intValue());

        break;

    case PropertySet.LONG:

        Long l = (Long) value;
        ps.setLong(5, l.longValue());

        break;

    case PropertySet.STRING:
        ps.setString(1, (String) value);

        break;

    default:
        throw new PropertyException("This type isn't supported!");
    }

    if (valueMap == null)
        valueMap = new HashMap();
    if (typeMap == null)
        typeMap = new HashMap();

    valueMap.put(key, value);
    typeMap.put(key, new Integer(type));
}

From source file:org.broad.igv.dev.db.SQLCodecSource.java

private CloseableTribbleIterator query(String chr, int start, int end) throws IOException {
    initQueryStatement();//  w  ww  .  j av a 2 s. c  om
    PreparedStatement statement = queryStatement;
    Set<Integer> bins = calculateBins(start, end);
    //System.out.println("number of bins: " + bins.size());
    if (bins.size() < MAX_BINS && binnedQueryStatement != null) {
        statement = binnedQueryStatement;
    }

    try {
        statement.clearParameters();
        statement.setString(1, chr);
        statement.setInt(3, end);
        int[] cols = new int[] { 2, 4, 5 };
        for (Integer cc : cols) {
            statement.setInt(cc, start);
        }

        if (statement == binnedQueryStatement) {
            int qnum = 6;
            for (Integer bin : bins) {
                statement.setInt(qnum, bin);
                qnum++;
            }

            for (; qnum <= statement.getParameterMetaData().getParameterCount(); qnum++) {
                statement.setNull(qnum, Types.INTEGER);
            }
        }

    } catch (SQLException e) {
        log.error(e);
        throw new IOException(e);
    }

    return loadIterator(statement);
}

From source file:org.ojbc.adapters.analyticaldatastore.dao.AnalyticalDatastoreDAOImpl.java

@Override
public Integer savePretrialServiceParticipation(
        final PretrialServiceParticipation pretrialServiceParticipation) {

    log.debug("Inserting row into PretrialServiceParticipation table: "
            + pretrialServiceParticipation.toString());

    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

            String pretrialInsertStatement = "";
            String[] insertArgs = null;

            if (pretrialServiceParticipation.getPretrialServiceParticipationID() != null) {
                insertArgs = new String[] { "PersonID", "CountyID", "RiskScoreID", "AssessedNeedID",
                        "RiskScore", "IntakeDate", "ArrestingAgencyORI", "ArrestIncidentCaseNumber",
                        "RecordType", "PretrialServiceUniqueID", "PretrialServiceParticipationID" };

                pretrialInsertStatement = "INSERT into PretrialServiceParticipation (PersonID, CountyID,RiskScore,IntakeDate,RecordType,ArrestingAgencyORI,ArrestIncidentCaseNumber,PretrialServiceUniqueID,PretrialServiceParticipationID) values (?,?,?,?,?,?,?,?,?)";
            } else {
                insertArgs = new String[] { "PersonID", "CountyID", "RiskScoreID", "AssessedNeedID",
                        "RiskScore", "IntakeDate", "ArrestingAgencyORI", "ArrestIncidentCaseNumber",
                        "RecordType", "PretrialServiceUniqueID" };

                pretrialInsertStatement = "INSERT into PretrialServiceParticipation (PersonID, CountyID,RiskScore,IntakeDate,RecordType,ArrestingAgencyORI,ArrestIncidentCaseNumber,PretrialServiceUniqueID) values (?,?,?,?,?,?,?,?)";

            }//from   w  w  w  . j a  v  a2 s . c  o m

            PreparedStatement ps = connection.prepareStatement(pretrialInsertStatement, insertArgs);
            ps.setInt(1, pretrialServiceParticipation.getPersonID());

            if (pretrialServiceParticipation.getCountyID() != null) {
                ps.setInt(2, pretrialServiceParticipation.getCountyID());
            } else {
                ps.setNull(2, java.sql.Types.NULL);
            }
            ps.setInt(3, pretrialServiceParticipation.getRiskScore());

            if (pretrialServiceParticipation.getIntakeDate() != null) {
                ps.setDate(4, new java.sql.Date(pretrialServiceParticipation.getIntakeDate().getTime()));
            } else {
                ps.setNull(4, java.sql.Types.NULL);
            }

            if (pretrialServiceParticipation.getRecordType() != null) {
                ps.setString(5, String.valueOf(pretrialServiceParticipation.getRecordType()));
            } else {
                ps.setNull(5, java.sql.Types.NULL);
            }
            ps.setString(6, pretrialServiceParticipation.getArrestingAgencyORI());
            ps.setString(7, pretrialServiceParticipation.getArrestIncidentCaseNumber());
            ps.setString(8, pretrialServiceParticipation.getPretrialServiceUniqueID());

            if (pretrialServiceParticipation.getPretrialServiceParticipationID() != null) {
                ps.setInt(9, pretrialServiceParticipation.getPretrialServiceParticipationID());
            }

            return ps;
        }
    }, keyHolder);

    Integer returnValue = null;

    if (pretrialServiceParticipation.getPretrialServiceParticipationID() != null) {
        returnValue = pretrialServiceParticipation.getPretrialServiceParticipationID();
    } else {
        returnValue = keyHolder.getKey().intValue();
    }

    return returnValue;

}

From source file:com.act.lcms.db.model.InductionWell.java

protected void bindInsertOrUpdateParameters(PreparedStatement stmt, Integer plateId, Integer plateRow,
        Integer plateColumn, String msid, String chemicalSource, String composition, String chemical,
        String strainSource, String note, Integer growth) throws SQLException {
    stmt.setInt(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), plateId);
    stmt.setInt(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), plateRow);
    stmt.setInt(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), plateColumn);
    stmt.setString(DB_FIELD.MSID.getInsertUpdateOffset(), msid);
    stmt.setString(DB_FIELD.CHEMICAL_SOURCE.getInsertUpdateOffset(), chemicalSource);
    stmt.setString(DB_FIELD.COMPOSITION.getInsertUpdateOffset(), composition);
    stmt.setString(DB_FIELD.CHEMICAL.getInsertUpdateOffset(), chemical);
    stmt.setString(DB_FIELD.STRAIN_SOURCE.getInsertUpdateOffset(), strainSource);
    stmt.setString(DB_FIELD.NOTE.getInsertUpdateOffset(), note);
    if (growth == null) {
        stmt.setNull(DB_FIELD.GROWTH.getInsertUpdateOffset(), Types.INTEGER);
    } else {/*from  w  ww .  j  ava2  s .  c  o  m*/
        stmt.setInt(DB_FIELD.GROWTH.getInsertUpdateOffset(), growth);
    }
}

From source file:com.flexive.ejb.beans.structure.SelectListEngineBean.java

private void updateList(FxSelectListEdit list) throws FxApplicationException {
    if (!list.changes())
        return;/*from   w ww.  ja v  a  2  s .  c  om*/
    FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.SelectListEditor);
    checkValidListParameters(list);
    //        System.out.println("Updating list " + list.getLabel());
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = Database.getDbConnection();
        //                                                                    1      2                   3
        ps = con.prepareStatement(
                "UPDATE " + TBL_STRUCT_SELECTLIST + " SET PARENTID=?,NAME=?,ALLOW_ITEM_CREATE=?," +
                //               4              5       6               7             8          9
                        "ACL_CREATE_ITEM=?,ACL_ITEM_NEW=?,BCSEP=?,SAMELVLSELECT=?,SORTENTRIES=? WHERE ID=?");
        if (list.hasParentList())
            ps.setLong(1, list.getParentList().getId());
        else
            ps.setNull(1, java.sql.Types.INTEGER);
        ps.setString(2, list.getName().trim());
        ps.setBoolean(3, list.isAllowDynamicItemCreation());
        ps.setLong(4, list.getCreateItemACL().getId());
        ps.setLong(5, list.getNewItemACL().getId());
        ps.setString(6, list.getBreadcrumbSeparator());
        ps.setBoolean(7, list.isOnlySameLevelSelect());
        ps.setBoolean(8, list.isSortEntries());
        ps.setLong(9, list.getId());
        ps.executeUpdate();
        Database.storeFxString(new FxString[] { list.getLabel(), list.getDescription() }, con,
                TBL_STRUCT_SELECTLIST, new String[] { "LABEL", "DESCRIPTION" }, "ID", list.getId());
    } 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:info.raack.appliancelabeler.data.JDBCDatabase.java

public void storeUserOnOffLabels(final List<ApplianceStateTransition> detectedStateTransitions) {

    for (final ApplianceStateTransition transition : detectedStateTransitions) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(insertApplianceStateTransition,
                        new String[] { "id" });

                ps.setInt(1, transition.getUserAppliance().getId());
                if (transition.getDetectionAlgorithmId() > 0) {
                    ps.setInt(2, transition.getDetectionAlgorithmId());
                } else {
                    ps.setNull(2, Types.INTEGER);
                }//from  w  w w. java 2 s  . c o m
                ps.setLong(3, transition.getTime());
                ps.setBoolean(4, transition.isOn());
                return ps;
            }
        }, keyHolder);

        transition.setId(keyHolder.getKey().intValue());
    }
}