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:dk.netarkivet.common.utils.DBUtils.java

/**
 * Set String Max Length./*w ww. jav a2 s. c om*/
 * 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 a index into the above statement
 * @param contents the contents
 * @param maxSize the maximum size of field: fieldName
 * @param o the Object, which is assumed to have a field named fieldName
 * @param fieldname the name of a given field
 * @throws SQLException if set operation fails
 */
public static void setStringMaxLength(PreparedStatement s, int fieldNum, String contents, int maxSize, Object o,
        String fieldname) throws SQLException {
    ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
    ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum");

    if (contents != null) {
        if (contents.length() > maxSize) {
            log.warn(fieldname + " of " + o + " is longer than the allowed " + maxSize
                    + " characters. The contents is truncated to length " + maxSize
                    + ". The untruncated contents was: " + contents);
            // truncate to length maxSize
            contents = contents.substring(0, maxSize);
        }
        s.setString(fieldNum, contents);
    } else {
        s.setNull(fieldNum, Types.VARCHAR);
    }
}

From source file:com.jagornet.dhcp.db.JdbcIaPrefixDAO.java

public void update(final IaPrefix iaPrefix) {
    String updateQuery = "update iaprefix" + " set prefixaddress=?," + " prefixlength=?," + " starttime=?,"
            + " preferredendtime=?," + " validendtime=?," + " state=?," + " identityassoc_id=?" + " where id=?";
    getJdbcTemplate().update(updateQuery, new PreparedStatementSetter() {
        @Override//from   w ww  .  j a v a  2  s.co m
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setBytes(1, iaPrefix.getIpAddress().getAddress());
            ps.setInt(2, iaPrefix.getPrefixLength());
            Date start = iaPrefix.getStartTime();
            if (start != null) {
                java.sql.Timestamp sts = new java.sql.Timestamp(start.getTime());
                ps.setTimestamp(3, sts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(3, java.sql.Types.TIMESTAMP);
            }
            Date preferred = iaPrefix.getPreferredEndTime();
            if (preferred != null) {
                java.sql.Timestamp pts = new java.sql.Timestamp(preferred.getTime());
                ps.setTimestamp(4, pts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(4, java.sql.Types.TIMESTAMP);
            }
            Date valid = iaPrefix.getValidEndTime();
            if (valid != null) {
                java.sql.Timestamp vts = new java.sql.Timestamp(valid.getTime());
                ps.setTimestamp(5, vts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(5, java.sql.Types.TIMESTAMP);
            }
            ps.setByte(6, iaPrefix.getState());
            ps.setLong(7, iaPrefix.getIdentityAssocId());
            ps.setLong(8, iaPrefix.getId());
        }
    });
}

From source file:com.agiletec.plugins.jpcrowdsourcing.aps.system.services.ideainstance.IdeaInstanceDAO.java

private void updateIdeaInstance(IdeaInstance ideainstance, Connection conn) {
    PreparedStatement stat = null;
    try {/*from  w w w.ja va 2  s . c o  m*/
        stat = conn.prepareStatement(UPDATE_IDEAINSTANCE);
        int index = 1;
        if (null != ideainstance.getCreatedat()) {
            Timestamp createdatTimestamp = new Timestamp(ideainstance.getCreatedat().getTime());
            stat.setTimestamp(index++, createdatTimestamp);
        } else {
            stat.setNull(index++, Types.DATE);
        }
        stat.setString(index++, ideainstance.getCode());
        stat.executeUpdate();
    } catch (Throwable t) {
        _logger.error("Error updating ideainstance ", t);
        throw new RuntimeException("Error updating ideainstance ", t);
    } finally {
        this.closeDaoResources(null, stat, null);
    }
}

From source file:com.gs.obevo.db.impl.core.jdbc.JdbcHelper.java

public int[] batchUpdate(Connection conn, String sql, Object[][] argsArray) {
    PreparedStatement ps = null;
    try {/*from   ww  w.jav  a2 s .  co  m*/
        this.jdbcHandler.preUpdate(conn, this);

        if (LOG.isDebugEnabled()) {
            LOG.debug("Executing batch update on connection {}: {} with args: {}", displayConnection(conn), sql,
                    argsArray);
        }
        ps = conn.prepareStatement(sql);
        for (Object[] args : argsArray) {
            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));
                }
            }
            ps.addBatch();
        }

        return ps.executeBatch();
    } catch (SQLException e) {
        LOG.error("Error during batch execution; will print out the full batch stack trace: ");
        this.logSqlBatchException(e, 0);
        throw new DataAccessException(e);
    } finally {
        DbUtils.closeQuietly(ps);
    }
}

From source file:dk.netarkivet.harvester.datamodel.ScheduleDBDAO.java

/** Sets the first twelve parameters of a Schedule in the order.
 * name, comments, startdate, enddate, maxrepeats,
 * timeunit, numtimeunits, anytime, onminute, onhour,
 * ondayofweek, ondayofmonth/*from   ww  w  . ja v a2s .  c  om*/
 * @param s a prepared SQL statement
 * @param schedule a given schedule.
 * @throws SQLException If the operation fails.
 */
private void setScheduleParameters(PreparedStatement s, Schedule schedule) throws SQLException {
    DBUtils.setName(s, 1, schedule, Constants.MAX_NAME_SIZE);
    DBUtils.setComments(s, 2, schedule, Constants.MAX_COMMENT_SIZE);
    final Date startDate = schedule.getStartDate();
    final int fieldNum = 3;
    DBUtils.setDateMaybeNull(s, fieldNum, startDate);
    if (schedule instanceof TimedSchedule) {
        TimedSchedule ts = (TimedSchedule) schedule;
        DBUtils.setDateMaybeNull(s, 4, ts.getEndDate());
        s.setNull(5, Types.BIGINT);
    } else {
        s.setNull(4, Types.DATE);
        RepeatingSchedule rs = (RepeatingSchedule) schedule;
        s.setLong(5, rs.getRepeats());
    }
    Frequency freq = schedule.getFrequency();
    s.setInt(6, freq.ordinal());
    s.setInt(7, freq.getNumUnits());
    s.setBoolean(8, freq.isAnytime());
    DBUtils.setIntegerMaybeNull(s, 9, freq.getOnMinute());
    DBUtils.setIntegerMaybeNull(s, 10, freq.getOnHour());
    DBUtils.setIntegerMaybeNull(s, 11, freq.getOnDayOfWeek());
    DBUtils.setIntegerMaybeNull(s, 12, freq.getOnDayOfMonth());
}

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

public void update(Address c, QueryRunner run, Connection conn) throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_UPDATE_ADDRESS);
    st.setString(1, c.getCountry());// www  . j a  va 2s .  co  m
    st.setString(2, c.getCity());
    st.setString(3, c.getStreet());
    st.setString(4, c.getPostCode());
    st.setString(5, c.getHouseNumber());
    st.setString(6, c.getFlatNumber());
    st.setString(7, c.getNotes());
    st.setBoolean(8, c.isMain());
    if (c.getPerson_id() == null && c.getCompany_id() == null) {
        throw new SQLException("Either Person or Company needs to be specified");
    }
    if (c.getPerson_id().longValue() != 0) {
        st.setLong(9, c.getPerson_id());
    } else {
        st.setNull(9, java.sql.Types.NUMERIC);
    }
    if (c.getCompany_id().longValue() != 0) {
        st.setLong(10, c.getCompany_id());
    } else {
        st.setNull(10, java.sql.Types.NUMERIC);
    }
    st.setLong(11, c.getId());
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_UPDATE_ADDRESS);
    }
}

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

@Override
public void updateGroupVerification(String group, Date lastVerified) throws DataAccessException {
    PreparedStatement statement = null;
    try {/*from   w  ww.j  av  a 2s .  co  m*/
        // create a statement
        statement = connection.prepareStatement(UPDATE_USER_GROUP_VERIFICATION);

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

        // set the group
        statement.setString(2, group);

        // perform the update
        statement.executeUpdate();
    } catch (SQLException sqle) {
        throw new DataAccessException(sqle);
    } catch (DataAccessException dae) {
        throw dae;
    } finally {
        RepositoryUtils.closeQuietly(statement);
    }
}

From source file:org.apache.phoenix.hive.PhoenixHiveDBWritable.java

public void write(PreparedStatement statement) throws SQLException {
    for (int i = 0; i < this.values.size(); i++) {
        Object o = this.values.get(i);
        try {//from ww  w.ja  v  a2  s  .c o m
            if (o != null) {
                LOG.debug(" value " + o.toString() + " type " + this.PDataTypes[i].getSqlTypeName()
                        + " int value " + this.PDataTypes[i].getSqlType());
                statement.setObject(i + 1,
                        PDataType.fromTypeId(this.PDataTypes[i].getSqlType()).toObject(o.toString()));
            } else {
                LOG.debug(" value NULL  type " + this.PDataTypes[i].getSqlTypeName() + " int value "
                        + this.PDataTypes[i].getSqlType());
                statement.setNull(i + 1, this.PDataTypes[i].getSqlType());
            }
        } catch (RuntimeException re) {
            throw new RuntimeException(String.format("Unable to process column %s, innerMessage=%s",
                    new Object[] { re.getMessage() }), re);
        }
    }
}

From source file:com.mmnaseri.dragonfly.fluent.impl.AbstractSelectQueryFinalizer.java

private <H> List<Map<Mapping, Object>> execute(SelectQueryExecution<E, H> selection) {
    final Connection connection = session.getConnection();
    final PreparedStatement preparedStatement;
    try {//  w w w. j  a  v  a2s  . c  om
        final String sql = selection.getSql() + ";";
        LogFactory.getLog(Statement.class).info("Preparing statement: " + sql);
        preparedStatement = connection.prepareStatement(sql);
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get a prepared statement from the database", e);
    }
    for (ParameterDescriptor descriptor : selection.getParameters()) {
        try {
            if (descriptor.getValue() == null) {
                preparedStatement.setNull(descriptor.getIndex(), descriptor.getSqlType());
            } else {
                preparedStatement.setObject(descriptor.getIndex(), descriptor.getValue());
            }
        } catch (SQLException e) {
            throw new StatementPreparationException(
                    "Failed to prepare statement for parameter " + descriptor.getIndex(), e);
        }
    }
    final ResultSet resultSet;
    final ResultSetMetaData metaData;
    try {
        resultSet = preparedStatement.executeQuery();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to retrieve the results from the data source", e);
    }
    try {
        metaData = resultSet.getMetaData();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get result set metadata for query", e);
    }
    final ArrayList<Map<Mapping, Object>> result = new ArrayList<Map<Mapping, Object>>();
    while (true) {
        try {
            if (!resultSet.next()) {
                break;
            }
            final HashMap<Mapping, Object> map = new HashMap<Mapping, Object>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                map.put(new ImmutableMapping(metaData.getTableName(i), metaData.getColumnName(i),
                        metaData.getColumnLabel(i)), resultSet.getObject(i));
            }
            result.add(map);
        } catch (SQLException e) {
            throw new DatabaseNegotiationException("Failed to get the next row", e);
        }

    }
    return result;
}

From source file:edu.ku.brc.specify.toycode.RegAdder.java

/**
 * @param trackId/*  ww  w  .ja va2 s  . com*/
 * @param mv
 * @param pStmt
 * @throws SQLException
 */
private void doTrackInserts(final int trackId, final HashMap<String, String> mv, final PreparedStatement pStmt)
        throws SQLException {
    for (String key : mv.keySet()) {
        String value = mv.get(key);
        pStmt.setString(1, key);
        if (!StringUtils.contains(value, ".") && StringUtils.isNumeric(value) && value.length() < 10) {
            pStmt.setInt(2, Integer.parseInt(value));
            pStmt.setNull(3, java.sql.Types.VARCHAR);

        } else if (value.length() < STR_SIZE + 1) {
            pStmt.setNull(2, java.sql.Types.INTEGER);
            pStmt.setString(3, value);

        } else {
            String v = value.substring(0, STR_SIZE);
            System.err.println(
                    "Error - On line " + lineNo + " Value[" + value + "] too big trunccating to[" + v + "]");

            pStmt.setNull(2, java.sql.Types.INTEGER);
            pStmt.setString(3, v);
        }
        pStmt.setInt(4, trackId);

        //System.out.println(pStmt2.toString());

        int rv = pStmt.executeUpdate();
        if (rv != 1) {
            for (String k : mv.keySet()) {
                System.out.println("[" + k + "][" + mv.get(k) + "]");
            }
            System.err.println("------------------------ Line No: " + lineNo);
            throw new RuntimeException("Error insert trackitem for Id: " + trackId);
        }
    }
}