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.BriefcaseEngineBean.java

/**
 * {@inheritDoc}/*from ww  w  .  j ava2  s .c  om*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void modify(long id, String name, String description, Long aclId) throws FxApplicationException {

    // Anything to do?
    if (name != null && name.trim().length() == 0) {
        name = null;
    }
    if (name == null && description == null && aclId == null) {
        return;
    }

    // Lookup the briefcase
    Briefcase br = load(id);
    if (br == null) {
        throw new FxNotFoundException("ex.briefcase.notFound", ("#" + id));
    }
    // Permission checks
    checkEditBriefcase(br);
    // Delete operation
    Connection con = null;
    PreparedStatement ps = null;
    try {
        // Obtain a database connection
        con = Database.getDbConnection();
        String sSql = "update " + DatabaseConst.TBL_BRIEFCASE + " set" + ((name == null) ? "" : " name=?, ")
                + ((aclId == null) ? "" : " acl=?, ") + ((description == null) ? "" : " description=?, ")
                + "mandator=mandator where id=" + id;
        ps = con.prepareStatement(sSql);
        int pos = 1;
        if (name != null)
            ps.setString(pos++, name);
        if (aclId != null) {
            if (aclId == -1) {
                ps.setNull(pos++, java.sql.Types.NUMERIC);
            } else {
                ps.setLong(pos++, aclId);
            }
        }
        if (description != null)
            ps.setString(pos, description);
        ps.executeUpdate();
    } catch (SQLException exc) {
        EJBUtils.rollback(ctx);
        throw new FxLoadException(LOG, exc, "ex.briefcase.modifyFailed", br.getName());
    } finally {
        closeObjects(BriefcaseEngineBean.class, con, ps);
    }
}

From source file:com.wso2telco.aggregatorblacklist.dao.ProvisionDAO.java

public boolean insertMerchantProvision(Integer appID, String subscriber, String operator, String[] merchants)
        throws SQLException, Exception {

    Connection con = null;//w w w . j av a  2  s.c o  m
    ResultSet rs = null;
    PreparedStatement insertStatement = null;
    PreparedStatement selectStatement = null;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        if (con == null) {

            throw new Exception("Connection not found");
        }

        /**
         * Set autocommit off to handle the transaction
         */
        con.setAutoCommit(false);

        StringBuilder selectQueryString = new StringBuilder(" SELECT id ");
        selectQueryString.append(" FROM ");
        selectQueryString.append(DatabaseTables.OPERATORS.getTableName());
        selectQueryString.append(" WHERE operatorname = '" + operator + "'");

        selectStatement = con.prepareStatement(selectQueryString.toString());
        rs = selectStatement.executeQuery();

        int operatorid = 0;
        if (rs.next()) {
            operatorid = rs.getInt("id");
        } else {
            throw new Exception("Operator Not Found");
        }

        for (int i = 0; i < merchants.length; i++) {
            StringBuilder insertQueryString = new StringBuilder("INSERT INTO ");
            insertQueryString.append(DatabaseTables.MERCHANTOPCO_BLACKLIST.getTableName());
            insertQueryString.append(" (application_id, operator_id, subscriber, merchant) ");
            insertQueryString.append("VALUES (?, ?, ?, ?)");

            insertStatement = con.prepareStatement(insertQueryString.toString());
            if (appID == null) {
                insertStatement.setNull(1, Types.INTEGER);
            } else {
                insertStatement.setInt(1, appID);
            }
            insertStatement.setInt(2, operatorid);
            insertStatement.setString(3, subscriber);
            insertStatement.setString(4, merchants[i]);
            insertStatement.executeUpdate();

            /**
             * commit the transaction if all success
             */
            con.commit();
        }

    } catch (SQLException e) {
        /**
         * rollback if Exception occurs
         */
        con.rollback();

        log.error("database operation error in Merachantopco Blacklist Entry : ", e);
        throw e;
    } catch (Exception e) {
        /**
         * rollback if Exception occurs
         */
        con.rollback();
        log.error("Error while Provisioning Merchant : ", e);

        throw e;
    } finally {
        DbUtils.closeAllConnections(selectStatement, con, rs);
        DbUtils.closeAllConnections(insertStatement, null, null);
    }
    return true;
}

From source file:org.accada.epcis.repository.capture.CaptureOperationsBackendSQL.java

/**
 * Inserts a new EPCIS event into the database by supplying a
 * PreparedStatement with the given parameters.
 * //w  ww .  ja va 2  s.c o m
 * @param session
 *            The database session.
 * @param eventTime
 *            The event's 'eventTime' parameter.
 * @param recordTime
 *            The event's 'recordTime' parameter.
 * @param eventTimeZoneOffset
 *            The event's 'eventTimeZoneOffset' parameter.
 * @param bizStepId
 *            The event's 'BusinessStepID' parameter.
 * @param dispositionId
 *            The event's 'DispositionID' parameter.
 * @param readPointId
 *            The event's 'ReadPointID' parameter.
 * @param bizLocationId
 *            The event's 'BusinessLocationID' parameter.
 * @param action
 *            The event's 'action' parameter.
 * @param parentId
 *            The event's 'ParentID' parameter.
 * @param epcClassId
 *            The event's 'EpcClassID' parameter.
 * @param quantity
 *            The event's 'quantity' parameter.
 * @param eventName
 *            The name of the event.
 * @return The database primary key of the inserted EPCIS event.
 * @throws SQLException
 *             If an SQL exception occurred.
 */
private Long insertEvent(final CaptureOperationsSession session, final Timestamp eventTime,
        final Timestamp recordTime, final String eventTimeZoneOffset, final Long bizStepId,
        final Long dispositionId, final Long readPointId, final Long bizLocationId, final String action,
        final String parentId, final Long epcClassId, final Long quantity, final String eventName)
        throws SQLException {

    PreparedStatement ps;
    if (eventName.equals(EpcisConstants.AGGREGATION_EVENT)) {
        ps = session.getInsert(SQL_INSERT_AGGREGATIONEVENT);
    } else if (eventName.equals(EpcisConstants.OBJECT_EVENT)) {
        ps = session.getInsert(SQL_INSERT_OBJECTEVENT);
    } else if (eventName.equals(EpcisConstants.QUANTITY_EVENT)) {
        ps = session.getInsert(SQL_INSERT_QUANTITYEVENT);
    } else if (eventName.equals(EpcisConstants.TRANSACTION_EVENT)) {
        ps = session.getInsert(SQL_INSERT_TRANSACTIONEVENT);
    } else {
        throw new SQLException("Encountered unknown event element '" + eventName + "'.");
    }

    // parameters 1-7 of the sql query are shared by all events

    ps.setTimestamp(1, eventTime);
    // according to the specification: recordTime is the time of capture
    ps.setTimestamp(2, recordTime != null ? recordTime : new Timestamp(System.currentTimeMillis()));
    // note: for testing it is handy to set recordTime=eventTime
    // ps.setTimestamp(2, eventTime);
    ps.setString(3, eventTimeZoneOffset);
    if (bizStepId != null) {
        ps.setLong(4, bizStepId.longValue());
    } else {
        ps.setNull(4, java.sql.Types.BIGINT);
    }
    if (dispositionId != null) {
        ps.setLong(5, dispositionId.longValue());
    } else {
        ps.setNull(5, java.sql.Types.BIGINT);
    }
    if (readPointId != null) {
        ps.setLong(6, readPointId.longValue());
    } else {
        ps.setNull(6, java.sql.Types.BIGINT);
    }
    if (bizLocationId != null) {
        ps.setLong(7, bizLocationId.longValue());
    } else {
        ps.setNull(7, java.sql.Types.BIGINT);
    }

    // special handling for QuantityEvent
    if (eventName.equals("QuantityEvent")) {
        if (epcClassId != null) {
            ps.setLong(8, epcClassId.longValue());
        } else {
            ps.setNull(8, java.sql.Types.BIGINT);
        }
        if (quantity != null) {
            ps.setLong(9, quantity.longValue());
        } else {
            ps.setNull(9, java.sql.Types.BIGINT);
        }
    } else {
        // all other events have action
        ps.setString(8, action);

        // AggregationEvent and TransactionEvent have a parentID field
        if (eventName.equals("AggregationEvent") || eventName.equals("TransactionEvent")) {
            ps.setString(9, parentId);
        }
    }

    ps.executeUpdate();

    return getLastAutoIncrementedId(session, "event_" + eventName);
}

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

/**
 * Create a new item//from   w  w  w.  j ava  2 s  . c  o  m
 *
 * @param item  the item to create
 * @param idMap map of temp. id to final db id
 * @return id
 * @throws FxApplicationException on errors
 */
private long createItem(FxSelectListItemEdit item, Map<Long, Long> idMap) throws FxApplicationException {
    checkValidItemParameters(item);
    UserTicket ticket = FxContext.getUserTicket();
    if (!ticket.isInRole(Role.SelectListEditor)) {
        //check the lists ACL
        if (!(ticket.mayCreateACL(item.getList().getCreateItemACL().getId(), ticket.getUserId())))
            throw new FxNoAccessException("ex.selectlist.item.create.noPerm", item.getList().getLabel(),
                    item.getList().getCreateItemACL().getLabel());
    }
    long newId;
    if (idMap == null || !idMap.containsKey(item.getId()))
        newId = seq.getId(FxSystemSequencer.SELECTLIST_ITEM);
    else
        newId = idMap.get(item.getId());
    //        System.out.println("Creating item " + item.getLabel() + " for list with id " + item.getList().getId());
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = Database.getDbConnection();
        //                                                                        1  2    3   4        5      6    7
        ps = con.prepareStatement(
                "INSERT INTO " + TBL_STRUCT_SELECTLIST_ITEM + "(ID,NAME,ACL,PARENTID,LISTID,DATA,COLOR," +
                //8         9          10          11          12      13       14
                        "CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT,DBIN_ID,DBIN_VER,DBIN_QUALITY)VALUES"
                        + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        ps.setLong(1, newId);
        ps.setString(2, item.getName());
        ps.setLong(3, item.getAcl().getId());
        if (item.hasParentItem()) {
            if (idMap != null && idMap.containsKey(item.getParentItem().getId()))
                ps.setLong(4, idMap.get(item.getParentItem().getId()));
            else
                ps.setLong(4, item.getParentItem().getId());
        } else
            ps.setNull(4, java.sql.Types.INTEGER);
        ps.setLong(5, item.getList().getId());
        ps.setString(6, item.getData());
        ps.setString(7, item.getColor());
        LifeCycleInfoImpl.store(ps, 8, 9, 10, 11);
        ps.setLong(12, item.getIconId());
        ps.setInt(13, item.getIconVer());
        ps.setInt(14, item.getIconQuality());
        ps.executeUpdate();
        Database.storeFxString(item.getLabel(), con, TBL_STRUCT_SELECTLIST_ITEM, "LABEL", "ID", newId);
        return newId;
    } catch (SQLException e) {
        try {
            if (StorageManager.isUniqueConstraintViolation(e))
                throw new FxCreateException(LOG, e, "ex.selectlist.item.name.notUnique", item.getName());
            throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage());
        } finally {
            EJBUtils.rollback(ctx);
        }
    } finally {
        Database.closeObjects(TypeEngineBean.class, con, ps);
    }
}

From source file:org.fosstrak.epcis.repository.capture.CaptureOperationsBackendSQL.java

/**
 * Inserts a new EPCIS event into the database by supplying a
 * PreparedStatement with the given parameters.
 * //from  w ww. j a  v a 2  s. c  o  m
 * @param session
 *            The database session.
 * @param eventTime
 *            The event's 'eventTime' parameter.
 * @param recordTime
 *            The event's 'recordTime' parameter.
 * @param eventTimeZoneOffset
 *            The event's 'eventTimeZoneOffset' parameter.
 * @param bizStepId
 *            The event's 'BusinessStepID' parameter.
 * @param dispositionId
 *            The event's 'DispositionID' parameter.
 * @param readPointId
 *            The event's 'ReadPointID' parameter.
 * @param bizLocationId
 *            The event's 'BusinessLocationID' parameter.
 * @param action
 *            The event's 'action' parameter.
 * @param parentId
 *            The event's 'ParentID' parameter.
 * @param epcClassId
 *            The event's 'EpcClassID' parameter.
 * @param quantity
 *            The event's 'quantity' parameter.
 * @param eventName
 *            The name of the event.
 * @return The database primary key of the inserted EPCIS event.
 * @throws SQLException
 *             If an SQL exception occurred.
 */
private Long insertEvent(final CaptureOperationsSession session, final Timestamp eventTime,
        final Timestamp recordTime, final String eventTimeZoneOffset, final Long bizStepId,
        final Long dispositionId, final Long readPointId, final Long bizLocationId, final String action,
        final String parentId, final Long epcClassId, final Long quantity, final String eventName)
        throws SQLException {

    PreparedStatement ps;
    if (eventName.equals(EpcisConstants.AGGREGATION_EVENT)) {
        ps = session.getInsert(SQL_INSERT_AGGREGATIONEVENT);
    } else if (eventName.equals(EpcisConstants.OBJECT_EVENT)) {
        ps = session.getInsert(SQL_INSERT_OBJECTEVENT);
    } else if (eventName.equals(EpcisConstants.QUANTITY_EVENT)) {
        ps = session.getInsert(SQL_INSERT_QUANTITYEVENT);
    } else if (eventName.equals(EpcisConstants.TRANSACTION_EVENT)) {
        ps = session.getInsert(SQL_INSERT_TRANSACTIONEVENT);
    } else {
        throw new SQLException("Encountered unknown event element '" + eventName + "'.");
    }

    // parameters 1-7 of the sql query are shared by all events

    ps.setTimestamp(1, eventTime);
    // according to the specification: recordTime is the time of capture
    ps.setTimestamp(2, recordTime != null ? recordTime : new Timestamp(System.currentTimeMillis()));
    // note: for testing it is handy to set recordTime=eventTime
    // ps.setTimestamp(2, eventTime);
    ps.setString(3, eventTimeZoneOffset);
    if (bizStepId != null) {
        ps.setLong(4, bizStepId.longValue());
    } else {
        ps.setNull(4, java.sql.Types.BIGINT);
    }
    if (dispositionId != null) {
        ps.setLong(5, dispositionId.longValue());
    } else {
        ps.setNull(5, java.sql.Types.BIGINT);
    }
    if (readPointId != null) {
        ps.setLong(6, readPointId.longValue());
    } else {
        ps.setNull(6, java.sql.Types.BIGINT);
    }
    if (bizLocationId != null) {
        ps.setLong(7, bizLocationId.longValue());
    } else {
        ps.setNull(7, java.sql.Types.BIGINT);
    }

    // special handling for QuantityEvent
    if (eventName.equals("QuantityEvent")) {
        if (epcClassId != null) {
            ps.setLong(8, epcClassId.longValue());
        } else {
            ps.setNull(8, java.sql.Types.BIGINT);
        }
        if (quantity != null) {
            ps.setLong(9, quantity.longValue());
        } else {
            ps.setNull(9, java.sql.Types.BIGINT);
        }
    } else {
        // all other events have action
        ps.setString(8, action);

        // AggregationEvent and TransactionEvent have a parentID field
        if (eventName.equals("AggregationEvent") || eventName.equals("TransactionEvent")) {
            ps.setString(9, parentId);
        }
    }

    ps.executeUpdate();
    session.commit();

    return getLastAutoIncrementedId(session, "event_" + eventName);
}

From source file:com.novartis.opensource.yada.util.QueryUtils.java

/**
 * Calls the appropriate setter method for {@code type} in the {@code pstmt},
 * performing the appropriate type conversion or syntax change as needed
 * (e.g., for {@link java.sql.Date}s)//  www  .  j av a  2  s . c  o m
 * 
 * @param pstmt
 *          the statement to which to assign the parameter values
 * @param index
 *          the position of the parameter
 * @param type
 *          the data type of the parameter
 * @param val
 *          the value to assign
 */
@SuppressWarnings("static-method")
private void setQueryParameter(PreparedStatement pstmt, int index, char type, String val) {
    String idx = (index < 10) ? " " + String.valueOf(index) : String.valueOf(index);
    l.debug("Setting param [" + idx + "] of type [" + String.valueOf(type) + "] to: " + val);
    try {
        switch (type) {
        case DATE:

            try {
                if ("".equals(val) || val == null) {
                    pstmt.setNull(index, java.sql.Types.DATE);
                } else {
                    SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_DATE_FMT);
                    ParsePosition pp = new ParsePosition(0);
                    Date dateVal = sdf.parse(val, pp);
                    if (dateVal == null) {
                        sdf = new SimpleDateFormat(ORACLE_DATE_FMT);
                        dateVal = sdf.parse(val, pp);
                    }
                    if (dateVal != null) {
                        long t = dateVal.getTime();
                        java.sql.Date sqlDateVal = new java.sql.Date(t);
                        pstmt.setDate(index, sqlDateVal);
                    }
                }
            } catch (Exception e) {
                l.error("Error: " + e.getMessage());
            }
            break;
        case INTEGER:
            try {
                int ival = Integer.parseInt(val);
                pstmt.setInt(index, ival);
            } catch (NumberFormatException nfe) {
                l.error("Error: " + nfe.getMessage());
                l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                        + "] to: null");
                pstmt.setNull(index, java.sql.Types.INTEGER);
            } catch (NullPointerException npe) {
                l.error("Error: " + npe.getMessage());
                l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                        + "] to: null");
                pstmt.setNull(index, java.sql.Types.INTEGER);
            } catch (Exception sqle) {
                l.error("Error: " + sqle.getMessage());
                l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                        + "] to: 0");
                pstmt.setNull(index, java.sql.Types.INTEGER);
            }
            break;
        case NUMBER:
            try {
                float fval = Float.parseFloat(val);
                pstmt.setFloat(index, fval);
            } catch (NumberFormatException nfe) {
                l.error("Error: " + nfe.getMessage());
                l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                        + "] to: null");
                pstmt.setNull(index, java.sql.Types.INTEGER);
            } catch (NullPointerException npe) {
                l.error("Error: " + npe.getMessage());
                l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                        + "] to: null");
                pstmt.setNull(index, java.sql.Types.INTEGER);
            } catch (Exception sqle) {
                l.error("Error: " + sqle.getMessage());
                l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type)
                        + "] to: null");
                pstmt.setNull(index, java.sql.Types.INTEGER);
            }
            break;
        case OUTPARAM_DATE:
            ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.DATE);
            break;
        case OUTPARAM_INTEGER:
            ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.INTEGER);
            break;
        case OUTPARAM_NUMBER:
            ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.FLOAT);
            break;
        case OUTPARAM_VARCHAR:
            ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.VARCHAR);
            break;
        default: // VARCHAR2
            pstmt.setString(index, val);
            break;
        }
    } catch (SQLException e) {
        e.printStackTrace();
        l.error(e.getMessage());
    }
}

From source file:org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler.java

private int[] setArguments(PreparedStatement pstmt) throws SQLException, IOException {
    if (getQueryArguments().trim().length() == 0) {
        return new int[] {};
    }// ww  w  .ja  v a2 s . co m
    String[] arguments = CSVSaveService.csvSplitString(getQueryArguments(), COMMA_CHAR);
    String[] argumentsTypes = getQueryArgumentsTypes().split(COMMA);
    if (arguments.length != argumentsTypes.length) {
        throw new SQLException("number of arguments (" + arguments.length + ") and number of types ("
                + argumentsTypes.length + ") are not equal");
    }
    int[] outputs = new int[arguments.length];
    for (int i = 0; i < arguments.length; i++) {
        String argument = arguments[i];
        String argumentType = argumentsTypes[i];
        String[] arg = argumentType.split(" ");
        String inputOutput = "";
        if (arg.length > 1) {
            argumentType = arg[1];
            inputOutput = arg[0];
        }
        int targetSqlType = getJdbcType(argumentType);
        try {
            if (!OUT.equalsIgnoreCase(inputOutput)) {
                if (argument.equals(NULL_MARKER)) {
                    pstmt.setNull(i + 1, targetSqlType);
                } else {
                    pstmt.setObject(i + 1, argument, targetSqlType);
                }
            }
            if (OUT.equalsIgnoreCase(inputOutput) || INOUT.equalsIgnoreCase(inputOutput)) {
                CallableStatement cs = (CallableStatement) pstmt;
                cs.registerOutParameter(i + 1, targetSqlType);
                outputs[i] = targetSqlType;
            } else {
                outputs[i] = java.sql.Types.NULL; // can't have an output parameter type null
            }
        } catch (NullPointerException e) { // thrown by Derby JDBC (at least) if there are no "?" markers in statement
            throw new SQLException("Could not set argument no: " + (i + 1) + " - missing parameter marker?");
        }
    }
    return outputs;
}

From source file:com.wso2telco.dep.ratecardservice.dao.TariffDAO.java

public TariffDTO addTariff(TariffDTO tariff) throws BusinessException {

    Connection con = null;//from w ww . j  a  va2 s  . c  om
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer tariffId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.TARIFF.getTObject());
        query.append(
                " (tariffname, tariffdesc, tariffdefaultval, tariffmaxcount, tariffexcessrate, tariffdefrate, tariffspcommission, tariffadscommission, tariffopcocommission, tariffsurchargeval, tariffsurchargeAds, tariffsurchargeOpco, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addTariff : " + ps);

        ps.setString(1, tariff.getTariffName());
        ps.setString(2, tariff.getTariffDescription());

        Double tariffDefaultVal = tariff.getTariffDefaultVal();
        if (tariffDefaultVal != null) {
            ps.setDouble(3, tariffDefaultVal);
        } else {
            ps.setNull(3, Types.DOUBLE);
        }

        Integer tariffMaxCount = tariff.getTariffMaxCount();
        if (tariffMaxCount != null) {
            ps.setInt(4, tariffMaxCount);
        } else {
            ps.setNull(4, Types.INTEGER);
        }

        Double tariffExcessRate = tariff.getTariffExcessRate();
        if (tariffExcessRate != null) {
            ps.setDouble(5, tariffExcessRate);
        } else {
            ps.setNull(5, Types.DOUBLE);
        }

        Double tariffDefRate = tariff.getTariffDefRate();
        if (tariffDefRate != null) {
            ps.setDouble(6, tariffDefRate);
        } else {
            ps.setNull(6, Types.DOUBLE);
        }

        Double tariffSPCommission = tariff.getTariffSPCommission();
        if (tariffSPCommission != null) {
            ps.setDouble(7, tariffSPCommission);
        } else {
            ps.setNull(7, Types.DOUBLE);
        }

        Double tariffAdsCommission = tariff.getTariffAdsCommission();
        if (tariffAdsCommission != null) {
            ps.setDouble(8, tariffAdsCommission);
        } else {
            ps.setNull(8, Types.DOUBLE);
        }

        Double tariffOpcoCommission = tariff.getTariffOpcoCommission();
        if (tariffOpcoCommission != null) {
            ps.setDouble(9, tariffOpcoCommission);
        } else {
            ps.setNull(9, Types.DOUBLE);
        }

        Double tariffSurChargeval = tariff.getTariffSurChargeval();
        if (tariffSurChargeval != null) {
            ps.setDouble(10, tariffSurChargeval);
        } else {
            ps.setNull(10, Types.DOUBLE);
        }

        Double tariffSurChargeAds = tariff.getTariffSurChargeAds();
        if (tariffSurChargeAds != null) {
            ps.setDouble(11, tariffSurChargeAds);
        } else {
            ps.setNull(11, Types.DOUBLE);
        }

        Double tariffSurChargeOpco = tariff.getTariffSurChargeOpco();
        if (tariffSurChargeOpco != null) {
            ps.setDouble(12, tariffSurChargeOpco);
        } else {
            ps.setNull(12, Types.DOUBLE);
        }

        ps.setString(13, tariff.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            tariffId = rs.getInt(1);
        }

        tariff.setTariffId(tariffId);
    } catch (SQLException e) {

        log.error("database operation error in addTariff : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addTariff : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return tariff;
}

From source file:org.jetbrains.webdemo.database.MySqlConnector.java

private String addProject(UserInfo userInfo, Project project, String type, Integer taskId)
        throws DatabaseOperationException {
    if (!checkCountOfProjects(userInfo)) {
        throw new DatabaseOperationException("You can't save more than 100 projects");
    }//from w  w w  .  j a  v a2 s  . c  o m

    int userId = getUserId(userInfo);

    PreparedStatement st = null;
    try (Connection connection = dataSource.getConnection()) {
        String publicId = idGenerator.nextProjectId();

        st = connection.prepareStatement(
                "INSERT INTO projects (owner_id, name, args, run_configuration, origin, public_id, read_only_files, type, task_id) VALUES (?,?,?,?,?,?,?,?,?) ");
        st.setString(1, userId + "");
        st.setString(2, escape(project.name));
        st.setString(3, project.args);
        st.setString(4, project.confType);
        st.setString(5, project.originUrl);
        st.setString(6, publicId);
        st.setString(7, objectMapper.writeValueAsString(project.readOnlyFileNames));
        st.setString(8, type);
        if (taskId == null) {
            st.setNull(9, Types.INTEGER);
        } else {
            st.setInt(9, taskId);
        }
        st.execute();

        int projectId = getProjectId(userInfo, publicId);
        for (ProjectFile file : project.files) {
            addFileToProject(userInfo, projectId, file.getName(), file.getText());
        }

        return publicId;
    } catch (SQLException e) {
        if (e.getErrorCode() == 1062) {
            throw new DatabaseOperationException("Project with this name already exist", e);
        } else {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    "Add project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName()
                            + " " + project.name);
            throw new DatabaseOperationException("Unknown exception", e);
        }
    } catch (Throwable e) {
        ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                "Add project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " "
                        + project.name);
        throw new DatabaseOperationException("Unknown exception", e);
    } finally {
        closeStatement(st);
    }
}