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.gs.obevo.db.impl.core.jdbc.JdbcHelper.java

private int updateInternal(Connection conn, int retryCount, String sql, Object... args) {
    this.jdbcHandler.preUpdate(conn, this);

    Statement statement = null;//from  w  ww .ja  v  a 2 s .c  om
    PreparedStatement ps = null;
    try {
        if (LOG.isDebugEnabled()) {
            LOG.debug("Executing update on connection {}: {} with args: {}", displayConnection(conn), sql,
                    args);
        }
        if (args.length == 0) {
            // For args length == 0, we use regular Statements and not PreparedStatements
            // This is because of http://www.selikoff.net/2008/08/04/question-mark-%E2%80%98%E2%80%99-characters-as-text-in-jdbc/
            // In short - question marks are naively interpreted by PreparedStatements as parameters, even if in
            // strings or comments
            // This can affect legacy DDL files that may have such comments sprinkled in. So we go w/ Statements,
            // which is what spring-jdbc did (this product had used spring-jdbc in an early incarnation, which was
            // when we discovered this issue)
            statement = conn.createStatement();
            return statement.executeUpdate(sql);
        } else {
            ps = conn.prepareStatement(sql);
            for (int j = 0; j < args.length; j++) {
                if (!parameterTypeEnabled || args[j] != null) {
                    ps.setObject(j + 1, args[j]);
                } else {
                    ps.setNull(j + 1, ps.getParameterMetaData().getParameterType(j + 1));
                }
            }

            return ps.executeUpdate();
        }
    } catch (SQLException e) {
        DataAccessException dataAccessException = new DataAccessException(e);
        boolean retry = this.jdbcHandler.handleException(this, conn, retryCount, dataAccessException);
        if (retry) {
            return this.updateInternal(conn, retryCount + 1, sql, args);
        } else {
            throw dataAccessException;
        }
    } finally {
        DbUtils.closeQuietly(statement);
        DbUtils.closeQuietly(ps);
    }
}

From source file:com.nextep.designer.dbgm.services.impl.DataService.java

private void fillStorageValues(IStorageHandle handle, Collection<Object> values) throws SQLException {
    Connection conn = null;//from  ww w.  j a v  a2  s .c o  m
    PreparedStatement stmt = null;
    try {
        conn = storageService.getLocalConnection();
        final String insertStmt = handle.getInsertStatement();
        final int expectedArgCount = insertStmt.length() - insertStmt.replace("?", "").length(); //$NON-NLS-1$ //$NON-NLS-2$
        stmt = conn.prepareStatement(insertStmt);
        int i = 1;
        for (Object o : values) {
            // TODO : Testing nullity to workaround some derby jdbc problem (temporary)
            if (i <= expectedArgCount) {
                if (o == null) {
                    stmt.setNull(i++, Types.VARCHAR);
                } else {
                    stmt.setObject(i++, o);
                }
            } else {
                /*
                 * Normally, this should append when source and target data sets have different
                 * structure (more columns in target or in source), but this might also hide
                 * some other bug (not sure about what will happen when column are swapped
                 * between source and target), so we log in debug mode.
                 */
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug("DataSet DEBUG : parameter " + (i++) //$NON-NLS-1$
                            + " ignored while filling storage handle for query " + insertStmt //$NON-NLS-1$
                            + " with values : " + values); //$NON-NLS-1$
                }
            }
        }
        while (i <= expectedArgCount) {
            stmt.setNull(i++, Types.VARCHAR);
        }
        stmt.execute();
    } finally {
        safeClose(null, stmt, conn, false);
    }
}

From source file:gsn.storage.StorageManager.java

public void executeInsert(CharSequence tableName, DataField[] fields, StreamElement streamElement,
        Connection connection) throws SQLException {
    PreparedStatement ps = null;
    String query = getStatementInsert(tableName, fields).toString();
    try {/*from  ww  w .ja  va  2 s.co  m*/
        ps = connection.prepareStatement(query);
        int counter = 1;
        for (DataField dataField : fields) {
            if (dataField.getName().equalsIgnoreCase("timed"))
                continue;
            Serializable value = streamElement.getData(dataField.getName());

            switch (dataField.getDataTypeID()) {
            case DataTypes.VARCHAR:
                if (value == null)
                    ps.setNull(counter, Types.VARCHAR);
                else
                    ps.setString(counter, value.toString());
                break;
            case DataTypes.CHAR:
                if (value == null)
                    ps.setNull(counter, Types.CHAR);
                else
                    ps.setString(counter, value.toString());
                break;
            case DataTypes.INTEGER:
                if (value == null)
                    ps.setNull(counter, Types.INTEGER);
                else
                    ps.setInt(counter, ((Number) value).intValue());
                break;
            case DataTypes.SMALLINT:
                if (value == null)
                    ps.setNull(counter, Types.SMALLINT);
                else
                    ps.setShort(counter, ((Number) value).shortValue());
                break;
            case DataTypes.TINYINT:
                if (value == null)
                    ps.setNull(counter, Types.TINYINT);
                else
                    ps.setByte(counter, ((Number) value).byteValue());
                break;
            case DataTypes.DOUBLE:
                if (value == null)
                    ps.setNull(counter, Types.DOUBLE);
                else
                    ps.setDouble(counter, ((Number) value).doubleValue());
                break;
            case DataTypes.FLOAT:
                if (value == null)
                    ps.setNull(counter, Types.FLOAT);
                else
                    ps.setFloat(counter, ((Number) value).floatValue());
                break;
            case DataTypes.BIGINT:
                if (value == null)
                    ps.setNull(counter, Types.BIGINT);
                else
                    ps.setLong(counter, ((Number) value).longValue());
                break;
            case DataTypes.BINARY:
                if (value == null)
                    ps.setNull(counter, Types.BINARY);
                else
                    ps.setBytes(counter, (byte[]) value);
                break;
            default:
                logger.error("The type conversion is not supported for : " + dataField.getName() + "("
                        + dataField.getDataTypeID() + ") : ");
            }
            counter++;
        }
        ps.setLong(counter, streamElement.getTimeStamp());
        ps.execute();
    } catch (GSNRuntimeException e) {
        //if (e.getType() == GSNRuntimeException.UNEXPECTED_VIRTUAL_SENSOR_REMOVAL) {
        //    if (logger.isDebugEnabled())
        //        logger.debug("An stream element dropped due to unexpected virtual sensor removal. (Stream element: " + streamElement.toString() + ")+ Query: " + query, e);
        //} else
        logger.warn("Inserting a stream element failed : " + streamElement.toString(), e);
    } catch (SQLException e) {
        if (e.getMessage().toLowerCase().contains("duplicate entry"))
            logger.info("Error occurred on inserting data to the database, an stream element dropped due to: "
                    + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query);
        else
            logger.warn("Error occurred on inserting data to the database, an stream element dropped due to: "
                    + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query);
        throw e;
    } finally {
        close(ps);
    }
}

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

/**
 * {@inheritDoc}/*from   w w  w  .j ava2  s  .c  o m*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public long create(String name, String color, long mandatorId) throws FxApplicationException {
    final UserTicket ticket = FxContext.getUserTicket();
    // Permission checks
    try {
        if (!ticket.isGlobalSupervisor()) {
            if (ticket.getMandatorId() != mandatorId) {
                throw new FxNoAccessException("ex.usergroup.create.foreignMandator");
            }
            if (!ticket.isInRole(Role.MandatorSupervisor))
                FxPermissionUtils.checkRole(ticket, Role.AccountManagement);
        }
    } catch (FxNoAccessException nae) {
        if (LOG.isInfoEnabled())
            LOG.info(nae);
        throw nae;
    }

    Connection con = null;
    Statement stmt = null;
    PreparedStatement ps = null;
    String sql = null;
    try {

        // Sanity checks
        color = FxFormatUtils.processColorString("color", color);
        checkName(name);

        // Obtain a database connection
        con = Database.getDbConnection();

        // Obtain a new id
        long groupId = seq.getId(FxSystemSequencer.GROUP);

        // Create the new group
        sql = "INSERT INTO " + TBL_USERGROUPS + " "
                + "(ID,MANDATOR,AUTOMANDATOR,ISSYSTEM,NAME,COLOR,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT) VALUES ("
                + "?,?,?,?,?,?,?,?,?,?)";
        final long NOW = System.currentTimeMillis();
        ps = con.prepareStatement(sql);
        ps.setLong(1, groupId);
        ps.setLong(2, mandatorId);
        ps.setNull(3, java.sql.Types.NUMERIC);
        ps.setBoolean(4, false);
        ps.setString(5, name);
        ps.setString(6, color);
        ps.setLong(7, ticket.getUserId());
        ps.setLong(8, NOW);
        ps.setLong(9, ticket.getUserId());
        ps.setLong(10, NOW);
        ps.executeUpdate();

        StructureLoader.updateUserGroups(FxContext.get().getDivisionId(), loadAll(-1));

        // Return the new id
        return groupId;
    } catch (SQLException exc) {
        final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(exc);
        EJBUtils.rollback(ctx);
        if (uniqueConstraintViolation) {
            FxEntryExistsException eee = new FxEntryExistsException("ex.usergroup.create.groupExists", name);
            if (LOG.isInfoEnabled())
                LOG.info(eee);
            throw eee;
        } else {
            FxCreateException ce = new FxCreateException(exc, "ex.usergroup.sqlError", exc.getMessage(), sql);
            LOG.error(ce);
            throw ce;
        }
    } finally {
        Database.closeObjects(UserGroupEngineBean.class, null, ps);
        Database.closeObjects(UserGroupEngineBean.class, con, stmt);
    }
}

From source file:org.apache.ode.bpel.extvar.jdbc.JdbcExternalVariableModule.java

RowKey execInsert(DbExternalVariable dbev, Locator locator, RowKey keys, RowVal values) throws SQLException {
    Connection conn = dbev.dataSource.getConnection();
    PreparedStatement stmt = null;
    try {/*from w  w  w .  ja  v a  2 s  .  c  o m*/
        if (__log.isDebugEnabled()) {
            __log.debug("execInsert: keys=" + keys + " values=" + values);
            __log.debug("Prepare statement: " + dbev.insert);
            __log.debug("missingDatabaseGeneratedValues: " + keys.missingDatabaseGeneratedValues());
            __log.debug("_autoColNames: " + ObjectPrinter.stringifyNvList(dbev._autoColNames));
        }

        stmt = keys.missingDatabaseGeneratedValues() ? conn.prepareStatement(dbev.insert, dbev._autoColNames)
                : conn.prepareStatement(dbev.insert);

        int idx = 1;
        for (Column c : dbev._inscolumns) {
            Object val = c.getValue(c.name, keys, values, locator.iid);
            values.put(c.name, val);
            if (__log.isDebugEnabled())
                __log.debug("Set parameter " + idx + ": " + val);
            if (val == null)
                stmt.setNull(idx, c.dataType);
            else
                stmt.setObject(idx, val);
            idx++;
        }

        stmt.execute();

        for (Column ck : keys._columns) {
            Object val = values.get(ck.name);
            if (__log.isDebugEnabled())
                __log.debug("Key " + ck.name + ": " + val);
            keys.put(ck.name, val);
        }

        if (keys.missingDatabaseGeneratedValues()) {
            // With JDBC 3, we can get the values of the key columns (if the db supports it)
            ResultSet keyRS = stmt.getGeneratedKeys();
            try {
                if (keyRS == null)
                    throw new SQLException("Database did not return generated keys");
                keyRS.next();
                for (Column ck : keys._columns) {
                    Object value = keyRS.getObject(ck.idx + 1);
                    if (__log.isDebugEnabled())
                        __log.debug("Generated key " + ck.name + ": " + value);
                    keys.put(ck.name, value);
                }
            } finally {
                keyRS.close();
            }
        }
        return keys;
    } finally {
        if (stmt != null)
            stmt.close();
        try {
            conn.close();
        } catch (SQLException e) {
            // ignore
        }
    }
}

From source file:com.webpagebytes.wpbsample.database.WPBDatabase.java

public DepositWithdrawal createDepositOrWithdrawal(int user_id, DepositWithdrawal.OperationType type,
        long amount) throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    PreparedStatement statementAccount = null;

    DepositWithdrawal operation = new DepositWithdrawal();
    try {/* w  w  w. j  a v a 2 s . c om*/
        statement = connection.prepareStatement(CREATE_ACCOUNTOPERATIONS_STATEMENT);
        connection.setAutoCommit(false);

        statement.setInt(1, user_id);
        int typeOperation = ACCOUNT_OPERATION_DEPOSIT;
        if (type == OperationType.WITHDRAWAL) {
            typeOperation = ACCOUNT_OPERATION_WITHDRAWAL;
        }
        statement.setInt(2, typeOperation);

        statement.setLong(3, amount);
        Date now = new Date();
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(now.getTime());
        statement.setTimestamp(4, sqlDate);
        statement.setNull(5, Types.INTEGER);
        statement.setNull(6, Types.INTEGER);

        statement.execute();
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()) {
            operation.setId(rs.getInt(1));
            operation.setAmount(amount);
            operation.setDate(now);
            operation.setType(type);
        }
        Account account = getAccount(user_id);
        long balanceToSet = 0L;
        if (type == OperationType.DEPOSIT) {
            balanceToSet = account.getBalance() + amount;
        } else {
            balanceToSet = account.getBalance() - amount;
        }
        if (balanceToSet < 0) {
            throw new SQLException("Balance cannot become negative");
        }
        statementAccount = connection.prepareStatement(UPDATE_ACCOUNT_BY_ID_STATEMENT);
        statementAccount.setLong(1, balanceToSet);
        statementAccount.setInt(2, user_id);
        statementAccount.execute();

        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            connection.rollback();
        }
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
    return operation;
}

From source file:com.feedzai.commons.sql.abstraction.engine.impl.SqlServerEngine.java

@Override
protected int entityToPreparedStatement(final DbEntity entity, final PreparedStatement ps,
        final EntityEntry entry, boolean useAutoInc) throws DatabaseEngineException {

    int i = 1;// ww  w  .ja  v  a 2s. c om
    for (DbColumn column : entity.getColumns()) {
        if (column.isAutoInc() && useAutoInc) {
            continue;
        }

        try {
            final Object val;
            if (column.isDefaultValueSet() && !entry.containsKey(column.getName())) {
                val = column.getDefaultValue().getConstant();
            } else {
                val = entry.get(column.getName());
            }
            switch (column.getDbColumnType()) {
            case BLOB:
                ps.setBytes(i, objectToArray(val));

                break;

            case CLOB:
                if (val == null) {
                    ps.setNull(i, Types.CLOB);
                    break;
                }

                if (val instanceof String) {
                    StringReader sr = new StringReader((String) val);
                    ps.setClob(i, sr);
                } else {
                    throw new DatabaseEngineException("Cannot convert " + val.getClass().getSimpleName()
                            + " to String. CLOB columns only accept Strings.");
                }
                break;
            case BOOLEAN:
                Boolean b = (Boolean) val;
                if (b == null) {
                    ps.setObject(i, null);
                } else if (b) {
                    ps.setObject(i, 1);
                } else {
                    ps.setObject(i, 0);
                }

                break;
            default:
                ps.setObject(i, val);
            }
        } catch (Exception ex) {
            throw new DatabaseEngineException("Error while mapping variables to database", ex);
        }

        i++;
    }

    return i - 1;
}

From source file:org.apache.phoenix.end2end.DateTimeIT.java

private static void initDateTableValues(String tablename, Connection conn, String tenantId, Date startDate)
        throws Exception {
    double dateIncrement = 2.0;
    PreparedStatement stmt = conn.prepareStatement("upsert into " + tablename + "(" + "    ORGANIZATION_ID, "
            + "    \"DATE\", " + "    FEATURE, " + "    UNIQUE_USERS, " + "    TRANSACTIONS, "
            + "    CPU_UTILIZATION, " + "    DB_UTILIZATION, " + "    REGION, " + "    IO_TIME)"
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    stmt.setString(1, tenantId);/*from  www .  j a  va2s.co m*/
    stmt.setDate(2, startDate);
    stmt.setString(3, "A");
    stmt.setInt(4, 10);
    stmt.setLong(5, 100L);
    stmt.setBigDecimal(6, BigDecimal.valueOf(0.5));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.2));
    stmt.setString(8, R2);
    stmt.setNull(9, Types.BIGINT);
    stmt.execute();

    startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement));
    stmt.setString(1, tenantId);
    stmt.setDate(2, startDate);
    stmt.setString(3, "B");
    stmt.setInt(4, 20);
    stmt.setLong(5, 200);
    stmt.setBigDecimal(6, BigDecimal.valueOf(1.0));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.4));
    stmt.setString(8, null);
    stmt.setLong(9, 2000);
    stmt.execute();

    startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement));
    stmt.setString(1, tenantId);
    stmt.setDate(2, startDate);
    stmt.setString(3, "C");
    stmt.setInt(4, 30);
    stmt.setLong(5, 300);
    stmt.setBigDecimal(6, BigDecimal.valueOf(2.5));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.6));
    stmt.setString(8, R1);
    stmt.setNull(9, Types.BIGINT);
    stmt.execute();

    startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement));
    stmt.setString(1, tenantId);
    stmt.setDate(2, startDate);
    stmt.setString(3, "D");
    stmt.setInt(4, 40);
    stmt.setLong(5, 400);
    stmt.setBigDecimal(6, BigDecimal.valueOf(3.0));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.8));
    stmt.setString(8, R1);
    stmt.setLong(9, 4000);
    stmt.execute();

    startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement));
    stmt.setString(1, tenantId);
    stmt.setDate(2, startDate);
    stmt.setString(3, "E");
    stmt.setInt(4, 50);
    stmt.setLong(5, 500);
    stmt.setBigDecimal(6, BigDecimal.valueOf(3.5));
    stmt.setBigDecimal(7, BigDecimal.valueOf(1.2));
    stmt.setString(8, R2);
    stmt.setLong(9, 5000);
    stmt.execute();

    startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement));
    stmt.setString(1, tenantId);
    stmt.setDate(2, startDate);
    stmt.setString(3, "F");
    stmt.setInt(4, 60);
    stmt.setLong(5, 600);
    stmt.setBigDecimal(6, BigDecimal.valueOf(4.0));
    stmt.setBigDecimal(7, BigDecimal.valueOf(1.4));
    stmt.setString(8, null);
    stmt.setNull(9, Types.BIGINT);
    stmt.execute();
}

From source file:com.nextep.designer.dbgm.services.impl.DataService.java

@Override
public void addDataline(IDataSet set, IDataLine... lines) {
    Connection localConn = null;/* ww  w .ja v  a 2  s.c  o m*/
    PreparedStatement stmt = null;
    IStorageHandle handle = set.getStorageHandle();
    if (handle == null) {
        storageService.createDataSetStorage(set);
        handle = set.getStorageHandle();
    }
    try {
        localConn = storageService.getLocalConnection();
        final String insertStmt = handle.getInsertStatement();
        stmt = localConn.prepareStatement(insertStmt);
        for (IDataLine line : lines) {
            int col = 1;
            // For repository handles, we specify the row id
            // if (handle.isRepositoryHandle()) {
            if (line.getRowId() == 0) {
                stmt.setNull(col++, Types.BIGINT);
            } else {
                stmt.setLong(col++, line.getRowId());
            }
            // }
            // Processing line data
            for (IReference r : set.getColumnsRef()) {
                final IColumnValue value = line.getColumnValue(r);
                Object valueObj = null;
                if (value != null) {
                    valueObj = value.getValue();
                    if (valueObj != null) {
                        stmt.setObject(col, valueObj);
                    } else {
                        IBasicColumn c = (IBasicColumn) VersionHelper.getReferencedItem(r);
                        int jdbcType = storageService.getColumnSqlType(set, c);
                        stmt.setNull(col, jdbcType);
                    }
                }
                // Incrementing column index
                col++;

            }
            stmt.addBatch();
        }
        stmt.executeBatch();
        localConn.commit();
    } catch (SQLException e) {
        LOGGER.error(DBGMMessages.getString("service.data.addDatalineFailed") + e.getMessage(), //$NON-NLS-1$
                e);
    } finally {
        safeClose(null, stmt, localConn, false);
    }

}

From source file:org.batoo.jpa.core.impl.jdbc.dbutils.AbstractQueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with the given objects.
 * /*ww w. j a  va 2 s . c  o  m*/
 * @param stmt
 *            PreparedStatement to fill
 * @param params
 *            Query replacement parameters; <code>null</code> is a valid value to pass in.
 * @throws SQLException
 *             if a database access error occurs
 */
public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {

    // check the parameter count, if we can
    ParameterMetaData pmd = null;
    if (!this.pmdKnownBroken) {
        pmd = stmt.getParameterMetaData();
        final int stmtCount = pmd.getParameterCount();
        final int paramsCount = params == null ? 0 : params.length;

        if (stmtCount != paramsCount) {
            throw new SQLException(
                    "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount);
        }
    }

    // nothing to do here
    if (params == null) {
        return;
    }

    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type. Oddly, NULL and
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!this.pmdKnownBroken) {
                try {
                    sqlType = pmd.getParameterType(i + 1);
                } catch (final SQLException e) {
                    this.pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}