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.mtgi.analytics.sql.BehaviorTrackingDataSourceTest.java

@Test
public void testPreparedStatement() throws Exception {
    //test tracking through the prepared statement API, which should also
    //log parameters in the events.
    PreparedStatement stmt = conn.prepareStatement("insert into TEST_TRACKING values (?, ?, ?)");
    stmt.setLong(1, 1);/*from ww w .  j  av  a2  s  .  com*/
    stmt.setString(2, "hello");
    stmt.setObject(3, null, Types.VARCHAR);
    assertEquals(1, stmt.executeUpdate());

    //test support for batching.  each batch should log 1 event.
    stmt.setLong(1, 3);
    stmt.setString(2, "batch");
    stmt.setObject(3, "1", Types.VARCHAR);
    stmt.addBatch();

    stmt.setLong(1, 4);
    stmt.setString(2, "batch");
    stmt.setObject(3, "2", Types.VARCHAR);
    stmt.addBatch();
    stmt.executeBatch();

    //back to a regular old update.
    stmt.setLong(1, 2);
    stmt.setObject(2, "goodbye", Types.VARCHAR);
    stmt.setNull(3, Types.VARCHAR);
    assertEquals(1, stmt.executeUpdate());

    stmt = conn.prepareStatement("update TEST_TRACKING set DESCRIPTION = 'world'");
    assertEquals(4, stmt.executeUpdate());

    stmt = conn.prepareStatement("select ID from TEST_TRACKING order by ID");
    ResultSet rs = stmt.executeQuery();
    int index = 0;
    long[] keys = { 1L, 2L, 3L, 4L };
    while (rs.next())
        assertEquals(keys[index++], rs.getLong(1));
    rs.close();
    assertEquals(4, index);

    manager.flush();
    assertEventDataMatches("BehaviorTrackingDataSourceTest.testPreparedStatement-result.xml");
}

From source file:iddb.runtime.db.model.dao.impl.mysql.PenaltyDAOImpl.java

@Override
public void save(Penalty penalty) {
    String sql;//from w w  w  . j  a v  a 2  s.c o m
    if (penalty.getKey() == null) {
        sql = "insert into penalty (playerid, adminid, type, reason, duration, synced, active, created, updated, expires) values (?,?,?,?,?,?,?,?,?,?)";
    } else {
        sql = "update penalty set playerid = ?," + "adminid = ?," + "type = ?," + "reason = ?,"
                + "duration = ?," + "synced = ?," + "active = ?," + "created = ?," + "updated = ?,"
                + "expires = ? where id = ? limit 1";
    }
    Connection conn = null;
    try {
        conn = ConnectionFactory.getMasterConnection();
        PreparedStatement st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        st.setLong(1, penalty.getPlayer());
        if (penalty.getAdmin() != null)
            st.setLong(2, penalty.getAdmin());
        else
            st.setNull(2, Types.INTEGER);
        st.setInt(3, penalty.getType().intValue());
        if (penalty.getReason() != null)
            st.setString(4, penalty.getReason());
        else
            st.setNull(4, Types.VARCHAR);
        if (penalty.getDuration() == null)
            penalty.setDuration(0L);
        st.setLong(5, penalty.getDuration());
        st.setBoolean(6, penalty.getSynced());
        st.setBoolean(7, penalty.getActive());
        if (penalty.getCreated() == null)
            penalty.setCreated(new Date());
        if (penalty.getUpdated() == null)
            penalty.setUpdated(new Date());
        st.setTimestamp(8, new java.sql.Timestamp(penalty.getCreated().getTime()));
        st.setTimestamp(9, new java.sql.Timestamp(penalty.getUpdated().getTime()));
        st.setTimestamp(10, new java.sql.Timestamp(
                DateUtils.addMinutes(penalty.getCreated(), penalty.getDuration().intValue()).getTime()));
        if (penalty.getKey() != null)
            st.setLong(11, penalty.getKey());
        st.executeUpdate();
        if (penalty.getKey() == null) {
            ResultSet rs = st.getGeneratedKeys();
            if (rs != null && rs.next()) {
                penalty.setKey(rs.getLong(1));
            } else {
                logger.warn("Couldn't get id for penalty player id {}", penalty.getPlayer());
            }
        }
    } catch (SQLException e) {
        logger.error("Save: {}", e);
    } catch (IOException e) {
        logger.error("Save: {}", e);
    } finally {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
}

From source file:gemlite.core.internal.db.AsyncEventHelper.java

/**
 * Set column value at given index in a prepared statement. The implementation
 * tries using the matching underlying type to minimize any data type
 * conversions, and avoid creating wrapper Java objects (e.g. {@link Integer}
 * for primitive int)./*  w  ww . j  a  v a2  s .  c o m*/
 * 
 * @param type
 *          the SQL type of the column as specified by JDBC {@link Types}
 *          class
 * @param ps
 *          the prepared statement where the column value has to be set
 * @param row
 *          the source row as a {@link ResultSet} from where the value has to
 *          be extracted
 * @param rowPosition
 *          the 1-based position of the column in the provided
 *          <code>row</code>
 * @param paramIndex
 *          the 1-based position of the column in the target prepared
 *          statement (provided <code>ps</code> argument)
 * @param sync
 *          the {@link DBSynchronizer} object, if any; it is used to store
 *          whether the current driver is JDBC4 compliant to enable performing
 *          BLOB/CLOB operations {@link PreparedStatement#setBinaryStream},
 *          {@link PreparedStatement#setCharacterStream}
 * 
 * @throws SQLException
 *           in case of an exception in setting parameters
 */
public final void setColumnInPrepStatement(String type, Object val, PreparedStatement ps,
        final DBSynchronizer sync, int paramIndex) throws SQLException {
    switch (type) {
    case JavaTypes.STRING:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.VARCHAR);
        else {
            final String realVal = (String) val;
            ps.setString(paramIndex, realVal);
        }
        break;
    case JavaTypes.INT1:
    case JavaTypes.INT2:
    case JavaTypes.INT3:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.INTEGER);
        else {
            final int realVal = (int) val;
            ps.setInt(paramIndex, realVal);
        }
        break;
    case JavaTypes.DOUBLE1:
    case JavaTypes.DOUBLE2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DOUBLE);
        else {
            final double realVal = (double) val;
            ps.setDouble(paramIndex, realVal);
        }
        break;
    case JavaTypes.FLOAT1:
    case JavaTypes.FLOAT2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.FLOAT);
        else {
            final float realVal = (float) val;
            ps.setDouble(paramIndex, realVal);
        }
        break;
    case JavaTypes.BOOLEAN1:
    case JavaTypes.BOOLEAN2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.BOOLEAN);
        else {
            final boolean realVal = (boolean) val;
            ps.setBoolean(paramIndex, realVal);
        }
        break;
    case JavaTypes.DATE_SQL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DATE);
        else {
            final Date realVal = (Date) val;
            ps.setDate(paramIndex, realVal);
        }
        break;
    case JavaTypes.DATE_UTIL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DATE);
        else {
            final java.util.Date realVal = (java.util.Date) val;
            ps.setDate(paramIndex, new Date(realVal.getTime()));
        }
        break;
    case JavaTypes.BIGDECIMAL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DECIMAL);
        else {
            final BigDecimal realVal = (BigDecimal) val;
            ps.setBigDecimal(paramIndex, realVal);
        }
        break;
    case JavaTypes.TIME:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.TIME);
        else {
            final Time realVal = (Time) val;
            ps.setTime(paramIndex, realVal);
        }
        break;
    case JavaTypes.TIMESTAMP:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.TIMESTAMP);
        else {
            final Timestamp realVal = (Timestamp) val;
            ps.setTimestamp(paramIndex, realVal);
        }
        break;
    case JavaTypes.OBJECT:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.JAVA_OBJECT);
        else {
            final Object realVal = (Object) val;
            ps.setObject(paramIndex, realVal);
        }
        break;
    default:
        throw new UnsupportedOperationException("java.sql.Type = " + type + " not supported");
    }
}

From source file:com.nextep.designer.sqlclient.ui.services.impl.SQLClientService.java

private void fillPreparedStatement(PreparedStatement stmt, boolean isUpdate, boolean fillNullValues,
        ISQLRowResult row, Object newValue) throws SQLException {
    int i = 1;/*  ww  w  .j  a  v a  2  s.  c  om*/
    // For update statement, we set new value as first argument
    if (isUpdate) {
        if (newValue != null) {
            stmt.setObject(i++, newValue);
        } else {
            stmt.setNull(i++, getSqlTypeFor(newValue));
        }
    }
    // Passing all values
    final List<Object> values = row.getValues();
    final List<Integer> columnTypes = row.getSqlTypes();
    for (int rowIndex = 0; rowIndex < row.getValues().size(); rowIndex++) {
        Object o = values.get(rowIndex);
        if (o != null) {
            stmt.setObject(i++, o);
        } else {
            // If insert needed we explicitly set null values, otherwise it will be
            // written as "is null" in the statement
            if (fillNullValues) {
                stmt.setNull(i++, columnTypes.get(rowIndex));
            }
        }
    }
}

From source file:org.mskcc.cbio.cgds.dao.DaoClinicalData.java

/**
 * Add a New Case.//from www .j a v a 2 s  . c o m
 *
 * @param caseId                    Case ID.
 * @param overallSurvivalMonths     Overall Survival Months.
 * @param overallSurvivalStatus     Overall Survival Status.
 * @param diseaseFreeSurvivalMonths Disease Free Survival Months.
 * @param diseaseFreeSurvivalStatus Disease Free Survival Status.
 * @return number of cases added.
 * @throws DaoException Error Adding new Record.
 */
public int addCase(int cancerStudyId, String caseId, Double overallSurvivalMonths, String overallSurvivalStatus,
        Double diseaseFreeSurvivalMonths, String diseaseFreeSurvivalStatus, Double ageAtDiagnosis)
        throws DaoException {
    if (caseId == null || caseId.trim().length() == 0) {
        throw new IllegalArgumentException("Case ID is null or empty");
    }
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = JdbcUtil.getDbConnection(DaoClinicalData.class);
        pstmt = con.prepareStatement(
                "INSERT INTO clinical (`CANCER_STUDY_ID`, `CASE_ID`, `OVERALL_SURVIVAL_MONTHS`, "
                        + "`OVERALL_SURVIVAL_STATUS`, "
                        + "`DISEASE_FREE_SURVIVAL_MONTHS`, `DISEASE_FREE_SURVIVAL_STATUS`,"
                        + "`AGE_AT_DIAGNOSIS`) " + "VALUES (?,?,?,?,?,?,?)");
        pstmt.setInt(1, cancerStudyId);
        pstmt.setString(2, caseId);

        //  Make sure to set to Null if we are missing data.
        if (overallSurvivalMonths == null) {
            pstmt.setNull(3, java.sql.Types.DOUBLE);
        } else {
            pstmt.setDouble(3, overallSurvivalMonths);
        }

        if (overallSurvivalStatus == null) {
            pstmt.setNull(4, java.sql.Types.VARCHAR);
        } else {
            pstmt.setString(4, overallSurvivalStatus);
        }

        if (diseaseFreeSurvivalMonths == null) {
            pstmt.setNull(5, java.sql.Types.DOUBLE);
        } else {
            pstmt.setDouble(5, diseaseFreeSurvivalMonths);
        }

        if (diseaseFreeSurvivalStatus == null) {
            pstmt.setNull(6, java.sql.Types.VARCHAR);
        } else {
            pstmt.setString(6, diseaseFreeSurvivalStatus);
        }

        if (ageAtDiagnosis == null) {
            pstmt.setNull(7, java.sql.Types.DOUBLE);
        } else {
            pstmt.setDouble(7, ageAtDiagnosis);
        }

        int rows = pstmt.executeUpdate();
        return rows;
    } catch (SQLException e) {
        throw new DaoException(e);
    } finally {
        JdbcUtil.closeAll(DaoClinicalData.class, con, pstmt, rs);
    }
}

From source file:com.softberries.klerk.dao.DocumentItemDao.java

public void update(DocumentItem c, QueryRunner run, Connection conn) throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_UPDATE_DOCUMENTITEM);
    st.setString(1, c.getPriceNetSingle());
    st.setString(2, c.getPriceGrossSingle());
    st.setString(3, c.getPriceTaxSingle());
    st.setString(4, c.getPriceNetAll());
    st.setString(5, c.getPriceGrossAll());
    st.setString(6, c.getPriceTaxAll());
    st.setString(7, c.getTaxValue());/*from  w  w w. ja v a2  s .  c om*/
    st.setString(8, c.getQuantity());
    if (c.getProduct().getId().longValue() == 0 && c.getDocument_id().longValue() == 0) {
        throw new SQLException(
                "For DocumentItem corresponding product and document it belongs to need to be specified");
    }
    if (c.getProduct().getId() != 0) {
        st.setLong(9, c.getProduct().getId());
    } else {
        st.setNull(9, java.sql.Types.NUMERIC);
    }
    if (c.getDocument_id().longValue() != 0) {
        st.setLong(10, c.getDocument_id());
    } else {
        st.setNull(10, java.sql.Types.NUMERIC);
    }
    st.setString(11, c.getProduct().getName());
    st.setLong(12, c.getId());
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_UPDATE_DOCUMENTITEM);
    }
}

From source file:org.jivesoftware.openfire.spi.PresenceManagerImpl.java

public void userUnavailable(Presence presence) {
    // Only save the last presence status and keep track of the time when the user went
    // offline if this is an unavailable presence sent to THE SERVER and the presence belongs
    // to a local user.
    if (presence.getTo() == null && server.isLocal(presence.getFrom())) {
        String username = presence.getFrom().getNode();
        if (username == null || !userManager.isRegisteredUser(username)) {
            // Ignore anonymous users
            return;
        }//from   w w w .  j av  a  2  s  .com

        // If the user has any remaining sessions, don't record the offline info.
        if (sessionManager.getActiveSessionCount(username) > 0) {
            return;
        }

        String offlinePresence = null;
        // Save the last unavailable presence of this user if the presence contains any
        // child element such as <status>.
        if (!presence.getElement().elements().isEmpty()) {
            offlinePresence = presence.toXML();
        }
        // Keep track of the time when the user went offline
        java.util.Date offlinePresenceDate = new java.util.Date();

        boolean addedToCache;
        if (offlinePresence == null) {
            addedToCache = !NULL_STRING.equals(offlinePresenceCache.put(username, NULL_STRING));
        } else {
            addedToCache = !offlinePresence.equals(offlinePresenceCache.put(username, offlinePresence));
        }
        if (!addedToCache) {
            return;
        }
        lastActivityCache.put(username, offlinePresenceDate.getTime());

        // Insert data into the database.
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(INSERT_OFFLINE_PRESENCE);
            pstmt.setString(1, username);
            if (offlinePresence != null) {
                DbConnectionManager.setLargeTextField(pstmt, 2, offlinePresence);
            } else {
                pstmt.setNull(2, Types.VARCHAR);
            }
            pstmt.setString(3, StringUtils.dateToMillis(offlinePresenceDate));
            pstmt.execute();
        } catch (SQLException sqle) {
            Log.error("Error storing offline presence of user: " + username, sqle);
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
        if (org.apache.commons.lang.StringUtils.isNumeric(username)) {
            JMSBroker.getBroker().updateOnlineStatus(Long.parseLong(username), Boolean.FALSE);
        }
    }
}

From source file:biblivre3.acquisition.order.BuyOrderDAO.java

public boolean insertBuyOrder(BuyOrderDTO dto) {
    Connection conInsert = null;//from  w  w w.  ja v a2 s . co m
    try {
        conInsert = getDataSource().getConnection();
        final String sqlInsert = " INSERT INTO acquisition_order (serial_quotation, order_date, "
                + " responsable, obs, status, invoice_number, "
                + " receipt_date, total_value, delivered_quantity, " + " terms_of_payment, deadline_date) "
                + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ";
        PreparedStatement pstInsert = conInsert.prepareStatement(sqlInsert);
        pstInsert.setInt(1, dto.getSerialQuotation());
        pstInsert.setDate(2, new java.sql.Date(dto.getOrderDate().getTime()));
        pstInsert.setString(3, dto.getResponsible());
        pstInsert.setString(4, dto.getObs());
        pstInsert.setString(5, dto.getStatus());
        pstInsert.setString(6, dto.getInvoiceNumber());

        Date receiptDate = dto.getReceiptDate();
        if (receiptDate != null) {
            pstInsert.setDate(7, new java.sql.Date(receiptDate.getTime()));
        } else {
            pstInsert.setNull(7, java.sql.Types.DATE);
        }

        Float totalValue = dto.getTotalValue();
        if (totalValue != null) {
            pstInsert.setFloat(8, totalValue);
        } else {
            pstInsert.setNull(8, java.sql.Types.FLOAT);
        }

        Integer deliveryQuantity = dto.getDeliveredQuantity();
        if (deliveryQuantity != null) {
            pstInsert.setInt(9, deliveryQuantity);
        } else {
            pstInsert.setNull(9, java.sql.Types.INTEGER);
        }

        pstInsert.setString(10, dto.getTermsOfPayment());
        pstInsert.setDate(11, new java.sql.Date(dto.getDeadlineDate().getTime()));
        return pstInsert.executeUpdate() > 0;
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new DAOException(e.getMessage());
    } finally {
        closeConnection(conInsert);
    }
}

From source file:org.globus.workspace.scheduler.defaults.pilot.PilotSlotManagementDB.java

void newSlot(String uuid, int vmid, String lrmid, long duration) throws WorkspaceDatabaseException {

    Connection c = null;//from   w ww.j a  v a 2s .  co m
    PreparedStatement pstmt = null;
    try {
        c = getConnection();
        pstmt = c.prepareStatement(SQL_INSERT_SLOT);
        pstmt.setString(1, uuid);
        pstmt.setInt(2, vmid);
        pstmt.setString(3, lrmid);
        pstmt.setLong(4, duration);
        pstmt.setNull(5, Types.VARCHAR);

        final int inserted = pstmt.executeUpdate();

        if (this.lager.dbLog) {
            logger.trace("inserted " + inserted + " rows");
        }

    } catch (SQLException e) {
        logger.error("", e);
        throw new WorkspaceDatabaseException(e);
    } finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
            if (c != null) {
                returnConnection(c);
            }
        } catch (SQLException sql) {
            logger.error("SQLException in finally cleanup", sql);
        }
    }

    // newSlot affects slotnum
    this.numSlotsCached(true);
}

From source file:org.dspace.storage.rdbms.DatabaseManager.java

private static void loadParameters(PreparedStatement statement, Collection<ColumnInfo> columns, TableRow row)
        throws SQLException {
    int count = 0;
    for (ColumnInfo info : columns) {
        count++;/*from  w  w w .ja  v a 2 s.c  om*/
        String column = info.getCanonicalizedName();
        int jdbctype = info.getType();

        if (row.isColumnNull(column)) {
            statement.setNull(count, jdbctype);
        } else {
            switch (jdbctype) {
            case Types.BIT:
                statement.setBoolean(count, row.getBooleanColumn(column));
                break;

            case Types.INTEGER:
                if (isOracle) {
                    statement.setLong(count, row.getLongColumn(column));
                } else {
                    statement.setInt(count, row.getIntColumn(column));
                }
                break;

            case Types.NUMERIC:
            case Types.DECIMAL:
                statement.setLong(count, row.getLongColumn(column));
                // FIXME should be BigDecimal if TableRow supported that
                break;

            case Types.BIGINT:
                statement.setLong(count, row.getLongColumn(column));
                break;

            case Types.CLOB:
                if (isOracle) {
                    // Support CLOBs in place of TEXT columns in Oracle
                    statement.setString(count, row.getStringColumn(column));
                } else {
                    throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype);
                }
                break;

            case Types.VARCHAR:
                statement.setString(count, row.getStringColumn(column));
                break;

            case Types.DATE:
                statement.setDate(count, new java.sql.Date(row.getDateColumn(column).getTime()));
                break;

            case Types.TIME:
                statement.setTime(count, new Time(row.getDateColumn(column).getTime()));
                break;

            case Types.TIMESTAMP:
                statement.setTimestamp(count, new Timestamp(row.getDateColumn(column).getTime()));
                break;

            default:
                throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype);
            }
        }
    }
}