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.agiletec.plugins.jpcrowdsourcing.aps.system.services.ideainstance.IdeaInstanceDAO.java

private void insertIdeaInstance(IdeaInstance ideainstance, Connection conn) {
    PreparedStatement stat = null;
    try {//from  w  w w.  j  a v a2s . c o m
        stat = conn.prepareStatement(ADD_IDEAINSTANCE);
        int index = 1;
        stat.setString(index++, ideainstance.getCode());
        if (null != ideainstance.getCreatedat()) {
            Timestamp createdatTimestamp = new Timestamp(ideainstance.getCreatedat().getTime());
            stat.setTimestamp(index++, createdatTimestamp);
        } else {
            stat.setNull(index++, Types.DATE);
        }
        stat.executeUpdate();
    } catch (Throwable t) {
        _logger.error("Error in insert ideainstance", t);
        throw new RuntimeException("Error in insert ideainstance", t);
    } finally {
        this.closeDaoResources(null, stat, null);
    }
}

From source file:org.kuali.rice.core.framework.persistence.jpa.type.HibernateKualiHashType.java

/**
 * sets the hash value on the PreparedStatement
 * /*  w w w  .  j  av  a 2 s.  c  om*/
 * @see org.hibernate.usertype.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
 */
public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {

    Object converted = value;
    if (converted != null) {
        // don't convert if already a hashed value
        if (converted.toString().endsWith(EncryptionService.HASH_POST_PREFIX)) {
            converted = StringUtils.stripEnd(converted.toString(), EncryptionService.HASH_POST_PREFIX);
        } else {
            try {
                converted = CoreApiServiceLocator.getEncryptionService().hash(converted);
            } catch (GeneralSecurityException e) {
                throw new RuntimeException("Unable to hash value to db: " + e.getMessage());
            }
        }
    }

    if (converted == null) {
        st.setNull(index, Types.VARCHAR);
    } else {
        st.setString(index, (String) converted);
    }
}

From source file:org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform.java

/**
 * {@inheritDoc}// ww  w .jav  a  2  s. co  m
 */
protected void setObject(PreparedStatement statement, int sqlIndex, DynaBean dynaBean, SqlDynaProperty property)
        throws SQLException {
    int typeCode = property.getColumn().getTypeCode();
    Object value = dynaBean.get(property.getName());

    // PostgreSQL doesn't like setNull for BYTEA columns
    if (value == null) {
        switch (typeCode) {
        case Types.BINARY:
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
        case Types.BLOB:
            statement.setBytes(sqlIndex, null);
            break;
        default:
            statement.setNull(sqlIndex, typeCode);
            break;
        }
    } else {
        super.setObject(statement, sqlIndex, dynaBean, property);
    }
}

From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.CreateMetadataHelper.java

protected int setAttrs(PreparedStatement ps, MMMetadata metadata, Map<String, String> mAttrs)
        throws SQLException {
    Map<String, String> attrs = getMetadataAttrPrepared(metadata);

    int index = 0;
    for (Iterator<String> iter = mAttrs.keySet().iterator(); iter.hasNext(); index++) {
        String mAttrName = iter.next();
        if (attrs.containsKey(mAttrName)) {
            // ,?
            String val = attrs.get(mAttrName) == null ? "" : attrs.get(mAttrName);
            ps.setString(index + 8, val);
        } else {//ww  w.  ja  v  a2s  .c  o  m
            // ?NULL
            ps.setNull(index + 8, Types.VARCHAR);
        }
    }
    return index;
}

From source file:no.polaric.aprsdb.MyDBSession.java

/**
 * Add a mission to the database.// ww  w  .  j av a2 s  .  co m
 *
 * @param src Source callsign (or identifier)
 * @param alias Alias to be used with the callsign during the mission
 * @param icon Icon to be used with the callsign during the mission
 * @param start Time when mission starts
 * @param end   Time when mission ends. May be null (open).
 * @param descr Text that describes the mission
 * 
 */
public void addMission(String src, String alias, String icon, java.util.Date start, java.util.Date end,
        String descr) throws java.sql.SQLException {
    PreparedStatement stmt = getCon().prepareStatement(
            " INSERT INTO \"Mission\" (src, alias, icon, start, end, descr)" + " VALUES (?, ?, ?, ?, ?)");
    stmt.setString(1, src);
    stmt.setString(2, alias);
    stmt.setString(3, icon);
    stmt.setTimestamp(4, date2ts(start));
    if (end == null)
        stmt.setNull(5, java.sql.Types.TIMESTAMP);
    else
        stmt.setTimestamp(5, date2ts(end));
    stmt.setString(6, descr);
    stmt.executeUpdate();
}

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

public void create(Address c, QueryRunner run, Connection conn, ResultSet generatedKeys) throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_INSERT_ADDRESS, Statement.RETURN_GENERATED_KEYS);
    st.setString(1, c.getCountry());//from   w  ww  . jav a  2  s.c om
    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().longValue() == 0 && c.getCompany_id().longValue() == 0) {
        throw new SQLException("For Address 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);
    }
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_INSERT_ADDRESS);
    }
    generatedKeys = st.getGeneratedKeys();
    if (generatedKeys.next()) {
        c.setId(generatedKeys.getLong(1));
    } else {
        throw new SQLException("Creating address failed, no generated key obtained.");
    }
}

From source file:com.buckwa.dao.impl.excise4.Form23DaoImpl.java

@Override
public void create(final Form23 form23) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final StringBuilder sql = new StringBuilder();
    sql.append("INSERT INTO `form23`").append(
            " (`form23_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`,"
                    + "`totalScrap`,`part4flag`,`part4fullName`,`part4Date`,"
                    + "`part5flag`,`part5licenseNo`,`part5licenseDate`,`part5billingNo`,`part5billingDate`,`part5amount`,`part5Date`,"
                    + "`part6flag`,`part6Date`,`step`)")
            .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'0')");

    logger.info("SQL : " + sql.toString());

    String user = "";
    try {/*from   w  w w  . ja va2s .c om*/
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;

    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            Timestamp currentDate = new Timestamp(System.currentTimeMillis());
            Industry industry = form23.getIndustry();
            Factory factory = form23.getFactory();

            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, industry.getIndustryId());
            if (factory.getFactoryId() != null) {
                ps.setLong(2, factory.getFactoryId());
            } else {
                ps.setNull(2, java.sql.Types.BIGINT);
            }
            ps.setTimestamp(3, currentDate);
            ps.setString(4, userName);
            ps.setTimestamp(5, currentDate);
            ps.setString(6, userName);
            ps.setBigDecimal(7, form23.getTotalScrap());

            ps.setString(8, form23.getPart4flag());
            ps.setString(9, form23.getPart4fullName());
            ps.setTimestamp(10, currentDate);
            ps.setString(11, form23.getPart5flag());
            ps.setString(12, form23.getPart5licenseNo());
            ps.setTimestamp(13, getDateFormString(form23.getPart5licenseDate()));//part5licenseDate
            ps.setString(14, form23.getPart5billingNo());
            ps.setTimestamp(15, getDateFormString(form23.getPart5billingDate()));//part5billingDate
            ps.setBigDecimal(16, form23.getPart5amount());
            ps.setTimestamp(17, currentDate);//part5Date
            ps.setString(18, form23.getPart6flag());//part5Date
            ps.setTimestamp(19, currentDate);//part6Date

            return ps;
        }

    }, keyHolder);

    final Long returnidform23 = keyHolder.getKey().longValue();
    form23.setForm23Id(returnidform23);
    form23.setStep("0");
    logger.info("returnidform23 : " + returnidform23);

    //ID PRODUCT
    List<Product> products = form23.getProductList();
    if (products != null) {
        final StringBuilder psql = new StringBuilder();
        psql.append(
                "INSERT INTO `form23_product`(`form23_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
                .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

        logger.info("SQL : " + psql.toString());

        for (final Product p : products) {

            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    Timestamp currentDate = new Timestamp(System.currentTimeMillis());
                    PreparedStatement ps = connection.prepareStatement(psql.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, returnidform23);
                    ps.setString(2, p.getSeq());
                    ps.setString(3, p.getProductName());
                    ps.setString(4, p.getSize());
                    ps.setString(5, p.getBandColor());
                    ps.setString(6, p.getBackgroudColor());
                    ps.setString(7, p.getLicenseNo());
                    ps.setBigDecimal(8, p.getGrossnumber200());
                    ps.setBigDecimal(9, p.getGrossnumber400());
                    ps.setBigDecimal(10, p.getCorkScrap());
                    ps.setBigDecimal(11, p.getTotalScrap());

                    ps.setTimestamp(12, currentDate);
                    ps.setString(13, userName);
                    ps.setTimestamp(14, currentDate);
                    ps.setString(15, userName);
                    return ps;
                }

            }, keyHolder);

            long returnidproduct = keyHolder.getKey().longValue();
            p.setProcuctId(returnidproduct);
            logger.info("returnidproduct : " + returnidproduct);
        }
    }
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void create(final Form24 form24) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final StringBuilder sql = new StringBuilder();
    sql.append(//  w  w w .  j a  v a2  s.  c  o  m
            "INSERT INTO `form24`(`form24_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`,step,industry_name,tax_no,factory_name) ")
            .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?)");

    logger.info("SQL : " + sql.toString());

    String user = "";
    try {
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;

    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            Timestamp currentDate = new Timestamp(System.currentTimeMillis());
            final Industry industry = form24.getIndustry();
            final Factory factory = form24.getFactory();

            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, industry.getIndustryId());
            if (factory.getFactoryId() != null) {
                ps.setLong(2, factory.getFactoryId());
            } else {
                ps.setNull(2, java.sql.Types.BIGINT);
            }
            ps.setTimestamp(3, currentDate);
            ps.setString(4, userName);
            ps.setTimestamp(5, currentDate);
            ps.setString(6, userName);
            ps.setString(7, form24.getStep());
            ps.setString(8, industry.getIndustryName());
            ps.setString(9, industry.getTaxNo());
            ps.setString(10, factory.getFactoryName());
            return ps;
        }

    }, keyHolder);

    final Long returnidform24 = keyHolder.getKey().longValue();
    form24.setForm24Id(returnidform24);
    logger.info("returnidform24 : " + returnidform24);

    //ID PRODUCT
    List<Product> products = form24.getProductList();
    if (products != null) {
        final StringBuilder psql = new StringBuilder();
        psql.append(
                "INSERT INTO `form24_product`(`form24_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
                .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

        logger.info("SQL : " + psql.toString());

        for (final Product p : products) {

            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    Timestamp currentDate = new Timestamp(System.currentTimeMillis());
                    PreparedStatement ps = connection.prepareStatement(psql.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, returnidform24);
                    ps.setString(2, p.getSeq());
                    ps.setString(3, p.getProductName());
                    ps.setString(4, p.getSize());
                    ps.setString(5, p.getBandColor());
                    ps.setString(6, p.getBackgroudColor());
                    ps.setString(7, p.getLicenseNo());
                    ps.setBigDecimal(8, p.getGrossnumber200());
                    ps.setBigDecimal(9, p.getGrossnumber400());
                    ps.setBigDecimal(10, p.getCorkScrap());
                    ps.setBigDecimal(11, p.getTotalScrap());

                    ps.setTimestamp(12, currentDate);
                    ps.setString(13, userName);
                    ps.setTimestamp(14, currentDate);
                    ps.setString(15, userName);
                    return ps;
                }

            }, keyHolder);

            long returnidproduct = keyHolder.getKey().longValue();
            p.setProcuctId(returnidproduct);
            logger.info("returnidproduct : " + returnidproduct);
        }
    }
}

From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.CreateMetadataAlterHelper.java

protected void setPs(PreparedStatement ps, AbstractMetadata metadata, String metaModelCode,
        boolean hasChildMetaModel) throws SQLException {
    // ?,0//from  w  ww. j a  v a2  s .c o  m
    ps.setString(2, "0");

    // ??
    String namespace = metadata.getNamespace();
    ps.setString(6,
            namespace == null ? genNamespace(metadata.getParentMetadata(), metadata.getId(), hasChildMetaModel)
                    : namespace);

    // ?START_TIME
    // ?startTime
    ps.setLong(8, startTime); //metadata.getStartTime());

    // OLD_START_TIME ???OLD_START_TIME??
    ps.setNull(10, java.sql.Types.BIGINT);
}

From source file:com.opensymphony.module.propertyset.database.JDBCPropertySet.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;/*from w  w  w.j a v a 2  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!");
    }
}