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.flexive.ejb.beans.structure.SelectListEngineBean.java

/**
 * Update a select lists default item by setting it to <code>null</code> or its value for the list
 *
 * @param listId        the affected list
 * @param defaultItemId the default item to set (no checks for correct lists are made here!!)
 * @throws FxApplicationException on errors
 *///from  w w  w .j a  va 2  s . c  o  m
private void updateDefaultItem(long listId, long defaultItemId) throws FxApplicationException {
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = Database.getDbConnection();
        //                                                                        1          2
        ps = con.prepareStatement("UPDATE " + TBL_STRUCT_SELECTLIST + " SET DEFAULT_ITEM=? WHERE ID=?");
        if (defaultItemId <= 0)
            ps.setNull(1, java.sql.Types.INTEGER);
        else
            ps.setLong(1, defaultItemId);
        ps.setLong(2, listId);
        ps.executeUpdate();
    } 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:org.globus.workspace.accounting.impls.dbdefault.DBAccountingPersistence.java

public synchronized void add(String uuid, int id, String ownerDN, long minutesRequested, Calendar creationTime,
        int CPUCores, int memory) throws WorkspaceDatabaseException {

    if (this.lager.accounting) {
        logger.trace("add(): uuid = '" + uuid + "', id = " + id + ", " + "ownerDN = '" + ownerDN
                + "', minutesRequest = " + minutesRequested + ", creationTime = "
                + creationTime.getTimeInMillis() + ", cpu cores = " + CPUCores + ", memory = " + memory);
    }//from   w  w  w .ja  v  a2  s  .c  o  m

    Connection c = null;
    PreparedStatement pstmt = null;
    try {
        c = getConnection();
        pstmt = c.prepareStatement(SQL_INSERT_DEPLOYMENT);

        pstmt.setString(1, uuid);
        pstmt.setInt(2, id);
        pstmt.setString(3, ownerDN);
        pstmt.setObject(4, new Long(creationTime.getTimeInMillis()));
        pstmt.setObject(5, new Long(minutesRequested));
        pstmt.setInt(6, 1);
        pstmt.setNull(7, Types.INTEGER);
        pstmt.setInt(8, CPUCores);
        pstmt.setInt(9, memory);

        int inserted = pstmt.executeUpdate();
        if (this.lager.accounting) {
            logger.trace(Lager.id(id) + ": 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);
        }
    }

    if (this.lager.accounting) {
        logger.trace(Lager.id(id) + ": add() done (uuid: " + uuid + ")");
    }

}

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

public void create(DocumentItem c, QueryRunner run, Connection conn, ResultSet generatedKeys)
        throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_INSERT_DOCUMENTITEM, Statement.RETURN_GENERATED_KEYS);
    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());/*  ww w  .  ja  va2s  .  com*/
    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());
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_INSERT_DOCUMENTITEM);
    }
    generatedKeys = st.getGeneratedKeys();
    if (generatedKeys.next()) {
        c.setId(generatedKeys.getLong(1));
    } else {
        throw new SQLException("Creating user failed, no generated key obtained.");
    }
}

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

@Mock
private static void loadParameters(PreparedStatement statement, Collection<ColumnInfo> columns, TableRow row)
        throws SQLException {
    int count = 0;
    for (ColumnInfo info : columns) {
        count++;//  w  w w.  j  a v a 2s  .co  m
        String column = info.getName();
        int jdbctype = info.getType();

        if (row.isColumnNull(column)) {
            statement.setNull(count, jdbctype);
        } else {
            switch (jdbctype) {
            case Types.BIT:
            case Types.BOOLEAN:
                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);
            }
        }
    }
}

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

public boolean updateBuyOrder(BuyOrderDTO dto) {
    Connection conInsert = null;//from www  . ja  v a2s  . c  om
    try {
        conInsert = getDataSource().getConnection();
        final String sqlInsert = " UPDATE acquisition_order " + " SET serial_quotation = ?, order_date = ?, "
                + " responsable = ?, obs = ?, status = ?, "
                + " invoice_number = ?, receipt_date = ?, total_value = ?, "
                + " delivered_quantity = ?, terms_of_payment = ?, deadline_date=? "
                + " WHERE serial_order = ?;";

        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() != null && dto.getStatus().equals("1") ? "1" : "0");
        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()));
        pstInsert.setInt(12, dto.getSerial());
        return pstInsert.executeUpdate() > 0;
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new DAOException(e.getMessage());
    } finally {
        closeConnection(conInsert);
    }
}

From source file:de.ingrid.importer.udk.strategy.v1.IDCStrategy1_0_2_clean.java

protected void importDefaultCatalogData() throws Exception {

    if (log.isInfoEnabled()) {
        log.info("Creating default catalog...");
    }/* w  w w. ja  va 2 s. com*/

    pSqlStr = "INSERT INTO t03_catalogue (id, cat_uuid, cat_name, partner_name , provider_name, country_code,"
            + "workflow_control, expiry_duration, create_time, mod_uuid, mod_time, language_code) VALUES "
            + "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    PreparedStatement p = jdbc.prepareStatement(pSqlStr);

    sqlStr = "DELETE FROM t03_catalogue";
    jdbc.executeUpdate(sqlStr);

    String currentTime = IDCStrategyHelper.transDateTime(new Date());

    int cnt = 1;
    dataProvider.setId(dataProvider.getId() + 1);
    p.setLong(cnt++, dataProvider.getId()); // id
    p.setString(cnt++, UuidGenerator.getInstance().generateUuid()); // cat_uuid
    p.setString(cnt++, getImportDescriptor().getIdcCatalogueName()); // cat_name
    p.setString(cnt++, getImportDescriptor().getIdcPartnerName()); // partner_name
    p.setString(cnt++, getImportDescriptor().getIdcProviderName()); // provider_name
    p.setString(cnt++, getImportDescriptor().getIdcCatalogueCountry()); // country_code
    p.setString(cnt++, "N"); // workflow_control
    p.setNull(cnt++, Types.INTEGER); // expiry_duration
    p.setString(cnt++, currentTime); // create_time

    String modUuid = null;
    String modTime = null;

    String sql = "SELECT adr_uuid FROM t02_address";
    Statement st = jdbc.createStatement();
    ResultSet rs = jdbc.executeQuery(sql, st);
    if (rs.next()) {
        modUuid = rs.getString("adr_uuid");
        if (modUuid != null) {
            modTime = currentTime;
        }
    }
    rs.close();
    st.close();

    p.setString(cnt++, modUuid); // mod_uuid,
    p.setString(cnt++, modTime); // mod_time
    p.setString(cnt++, getCatalogLanguageFromDescriptor()); // language_code
    try {
        p.executeUpdate();
    } catch (Exception e) {
        log.error("Error executing SQL: " + p.toString(), e);
        throw e;
    }

    if (log.isInfoEnabled()) {
        log.info("Creating default catalog... done.");
    }
}

From source file:com.cnd.greencube.server.dao.jdbc.JdbcDAO.java

@SuppressWarnings("rawtypes")
public void update(Object obj, String[] columns) throws Exception {
    // ???/*  ww  w .jav  a2 s .  co  m*/
    Class clazz = obj.getClass();
    String tableName = getTableName(clazz);

    if (StringUtils.isEmpty(tableName))
        throw new SQLException("No @Table annotation in Class " + clazz.getName());
    List<Column2Property> setterColumnsNames = getColumnsFromObj(obj, columns);
    if (null == setterColumnsNames || setterColumnsNames.size() == 0)
        throw new SQLException("Column is nul, you must specified update columns.");

    StringBuffer sb = new StringBuffer("update " + tableName);
    sb.append(" set ");
    int size = setterColumnsNames.size();
    Column2Property c;
    for (int i = 0; i < size; i++) {
        c = setterColumnsNames.get(i);
        if (i == 0)
            sb.append(c.columnName + " = ?");
        else
            sb.append("," + c.columnName + " = ? ");
    }

    Connection conn = null;
    try {
        conn = getJdbcTemplate().getDataSource().getConnection();

        TableMetaManager tableManager = TableMetaManager.getInstance();
        Table t = tableManager.getTable(tableName);
        if (t == null) {
            _loadTable_(conn, tableName, clazz);
            t = tableManager.getTable(tableName);
        }

        sb.append(" where " + t.getIdColumnName() + " = ?");

        if (conn.isClosed()) {
            throw new SQLException("Connection is closed!");
        }
        PreparedStatement st = conn.prepareStatement(sb.toString());
        for (int i = 1; i <= size; i++) {
            Column2Property column = setterColumnsNames.get(i - 1);
            if (obj == null) {
                st.setNull(i, java.sql.Types.NULL);
                continue;
            }

            Object value = MethodUtils.invokeMethod(obj, column.getterMethodName, null);
            if (value == null) {
                st.setNull(i, java.sql.Types.NULL);
                continue;
            }

            setColumnValue(st, column, t.getMeta(column.columnName), value, i);
        }

        // ?ID
        Column2Property id = getIdFromObject(obj.getClass());
        Object idValue = MethodUtils.invokeMethod(obj, id.getterMethodName, null);
        setColumnValue(st, t.getIdColumnName(), t.getIdMetaData(), idValue, size + 1);

        st.execute();
    } finally {
        try {
            conn.close();
        } catch (Exception e) {
        }
    }
}

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

/**
 * {@inheritDoc}/*from w  w  w .j av a 2 s . c  o m*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void remove(FxSelectListItem item) throws FxApplicationException {
    //        System.out.println("Removing item " + item.getLabel());
    if (!(FxContext.getUserTicket().isInRole(Role.SelectListEditor) || FxContext.getUserTicket()
            .mayDeleteACL(item.getList().getCreateItemACL().getId(), FxContext.getUserTicket().getUserId())))
        throw new FxNoAccessException("ex.selectlist.item.remove.noPerm", item.getList().getLabel(),
                item.getAcl().getLabel());
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = Database.getDbConnection();
        //references (parent items)
        StringBuilder sb = new StringBuilder(500).append("UPDATE ").append(TBL_STRUCT_SELECTLIST_ITEM)
                .append(" SET PARENTID=? WHERE PARENTID=?");
        ps = con.prepareStatement(sb.toString());
        ps.setNull(1, java.sql.Types.INTEGER);
        ps.setLong(2, item.getId());
        ps.executeUpdate();
        ps.close();
        sb.setLength(0);
        //translations
        sb.append("DELETE FROM ").append(TBL_STRUCT_SELECTLIST_ITEM).append(ML).append(" WHERE ID=?");
        ps = con.prepareStatement(sb.toString());
        ps.setLong(1, item.getId());
        ps.executeUpdate();
        ps.close();
        sb.setLength(0);
        //the entry itself
        sb.append("DELETE FROM ").append(TBL_STRUCT_SELECTLIST_ITEM).append(" WHERE ID=?");
        ps = con.prepareStatement(sb.toString());
        ps.setLong(1, item.getId());
        ps.executeUpdate();
        StructureLoader.reload(null);
    } catch (SQLException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } catch (FxCacheException e1) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e1, "ex.cache", e1.getMessage());
    } finally {
        Database.closeObjects(TypeEngineBean.class, con, ps);
    }
}

From source file:com.cnd.greencube.server.dao.jdbc.JdbcDAO.java

@SuppressWarnings("rawtypes")
public void save(Object obj, String[] columns) throws Exception {
    // ???//w  w w. j a va  2  s. c o m
    Class clazz = obj.getClass();
    String tableName = getTableName(clazz);

    if (StringUtils.isEmpty(tableName))
        throw new SQLException("No @Table annotation in Class " + clazz.getName());
    List<Column2Property> setterColumnsNames = getColumnsFromObj(obj, columns);
    if (null == setterColumnsNames || setterColumnsNames.size() == 0)
        throw new SQLException("Column is nul, you must specified update columns.");

    StringBuffer sb = new StringBuffer("insert into " + tableName);
    sb.append(" ( ");
    int size = setterColumnsNames.size();
    Column2Property c;
    for (int i = 0; i < size; i++) {
        c = setterColumnsNames.get(i);
        if (i == 0)
            sb.append(c.columnName);
        else
            sb.append("," + c.columnName);
    }
    sb.append(" ) values ( ");
    for (int i = 0; i < size; i++) {
        c = setterColumnsNames.get(i);
        if (i == 0)
            sb.append("?");
        else
            sb.append(",?");
    }
    sb.append(" ) ");

    Connection conn = null;
    try {
        conn = getJdbcTemplate().getDataSource().getConnection();

        TableMetaManager tableManager = TableMetaManager.getInstance();
        Table t = tableManager.getTable(tableName);
        if (t == null) {
            _loadTable_(conn, tableName, clazz);
            t = tableManager.getTable(tableName);
        }

        if (conn.isClosed()) {
            throw new SQLException("Connection is closed!");
        }

        PreparedStatement st = conn.prepareStatement(sb.toString());
        for (int i = 1; i <= size; i++) {
            Column2Property column = setterColumnsNames.get(i - 1);
            if (obj == null) {
                st.setNull(i, java.sql.Types.NULL);
                continue;
            }

            Object value = MethodUtils.invokeMethod(obj, column.getterMethodName, null);
            if (value == null) {
                st.setNull(i, java.sql.Types.NULL);
                continue;
            }

            setColumnValue(st, column, t.getMeta(column.columnName), value, i);
        }

        st.execute();
    } finally {
        try {
            conn.close();
        } catch (Exception e) {
        }
    }
}

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

/**
 * {@inheritDoc}//from   ww  w  . j av  a2 s.c  o m
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void remove(FxSelectList list) throws FxApplicationException {
    //        System.out.println("Removing list " + list.getLabel());
    FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.SelectListEditor);
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = Database.getDbConnection();
        //fix list references
        StringBuilder sb = new StringBuilder(500).append("UPDATE ").append(TBL_STRUCT_SELECTLIST)
                .append(" SET PARENTID=? WHERE PARENTID=?");
        ps = con.prepareStatement(sb.toString());
        ps.setNull(1, java.sql.Types.INTEGER);
        ps.setLong(2, list.getId());
        ps.executeUpdate();
        ps.close();
        sb.setLength(0);
        //list translations
        sb.append("DELETE FROM ").append(TBL_STRUCT_SELECTLIST).append(ML).append(" WHERE ID=?");
        ps = con.prepareStatement(sb.toString());
        ps.setLong(1, list.getId());
        ps.executeUpdate();
        ps.close();
        sb.setLength(0);
        //item translations
        sb.append("DELETE FROM ").append(TBL_STRUCT_SELECTLIST_ITEM).append(ML)
                .append(" WHERE ID IN(SELECT DISTINCT ID FROM ").append(TBL_STRUCT_SELECTLIST_ITEM)
                .append(" WHERE LISTID=?)");
        ps = con.prepareStatement(sb.toString());
        ps.setLong(1, list.getId());
        ps.executeUpdate();
        ps.close();
        sb.setLength(0);
        //fix item references
        ps = con.prepareStatement(StorageManager.getSelectListItemReferenceFixStatement());
        ps.setNull(1, java.sql.Types.INTEGER);
        ps.setLong(2, list.getId());
        ps.executeUpdate();
        ps.close();
        //items
        sb.append("DELETE FROM ").append(TBL_STRUCT_SELECTLIST_ITEM).append(" WHERE LISTID=?");
        ps = con.prepareStatement(sb.toString());
        ps.setLong(1, list.getId());
        ps.executeUpdate();
        ps.close();
        sb.setLength(0);
        //the entry itself
        sb.append("DELETE FROM ").append(TBL_STRUCT_SELECTLIST).append(" WHERE ID=?");
        ps = con.prepareStatement(sb.toString());
        ps.setLong(1, list.getId());
        ps.executeUpdate();
        StructureLoader.reload(null);
    } catch (SQLException e) {
        e.printStackTrace();
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } catch (FxCacheException e1) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e1, "ex.cache", e1.getMessage());
    } finally {
        Database.closeObjects(TypeEngineBean.class, con, ps);
    }
}