Example usage for java.sql CallableStatement setDouble

List of usage examples for java.sql CallableStatement setDouble

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. setDouble

Introduction

In this page you can find the example usage for java.sql CallableStatement setDouble.

Prototype

void setDouble(String parameterName, double x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java double value.

Usage

From source file:com.medlog.webservice.util.ToneAnalyzerExample.java

public static void processTone(DbConnection dbc, ToneAnalysis tone, int diaryID) {
    CallableStatement cs = null;
    String cat_id = "";
    try {/*w  w w . ja  v a2 s.c om*/
        //category , tone , sentance,score,text
        List<ToneCategory> to = tone.getDocumentTone().getTones();
        Connection conn = dbc.getConnnection();

        conn.prepareCall(new StringBuilder().append("{call spDiaryTextScoreInsert(").append(diaryID)
                .append(",?,?,?,?,?)}").toString());
        conn.setAutoCommit(false);
        cs.setInt(3, 0);
        cs.setNull(5, java.sql.Types.NVARCHAR);// cat_id);

        for (ToneCategory docTC : to) {
            cat_id = docTC.getId();
            cs.setString(1, cat_id);
            for (ToneScore s : docTC.getTones()) {
                cs.setString(2, s.getId());
                cs.setDouble(4, s.getScore());
                cs.addBatch();
            }
            int[] docRes = cs.executeBatch();
            cs.clearBatch();
            System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result --- "
                    + ArrayUtils.toString(docRes));
        }
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() Process "
                + tone.getSentencesTone().size() + " sentances.");
        for (SentenceTone sentT : tone.getSentencesTone()) {
            to = sentT.getTones();
            cs.setInt(3, sentT.getId());
            cs.setString(5, toS(sentT.getText()).trim());
            for (ToneCategory docTC : to) {
                cat_id = docTC.getId();
                cs.setString(1, cat_id);
                for (ToneScore s : docTC.getTones()) {
                    cs.setString(2, s.getId());
                    cs.setDouble(4, s.getScore());
                    cs.addBatch();
                }
                int[] docRes = cs.executeBatch();
                cs.clearBatch();
                System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result["
                        + sentT.getId() + "] " + ArrayUtils.toString(docRes));
            }
        }

    } catch (SQLException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.medlog.webservice.services.tone.ToneProcessorFactory.java

private static ArrayList<Integer> processTone(DbConnection dbc, ToneAnalysis tone, int diaryID) {
    CallableStatement cs = null;
    String cat_id = "";
    ArrayList<Integer> results = new ArrayList<Integer>();
    try {/*from  www. j a va 2 s. c  o m*/
        //category , tone , sentance,score,text
        List<com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory> to = tone.getDocumentTone()
                .getTones();
        Connection conn = dbc.getConnnection();

        cs = conn.prepareCall(new StringBuilder().append("{call spDiaryTextScoreInsert(").append(diaryID)
                .append(",?,?,?,?,?)}").toString());
        conn.setAutoCommit(false);
        cs.setInt(3, 0);
        cs.setNull(5, java.sql.Types.NVARCHAR);// cat_id);

        for (com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory docTC : to) {
            cat_id = docTC.getId();
            cs.setString(1, cat_id);
            for (ToneScore s : docTC.getTones()) {
                cs.setString(2, s.getId());
                cs.setDouble(4, s.getScore());
                cs.addBatch();
            }

        }
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() Process "
                + tone.getSentencesTone().size() + " sentances.");
        int[] docRes = cs.executeBatch();
        List l = Arrays.asList(docRes);
        results.addAll(l);

        cs.clearBatch();
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result --- "
                + ArrayUtils.toString(docRes));
        int[] sentRes = null;
        for (SentenceTone sentT : tone.getSentencesTone()) {
            to = sentT.getTones();
            cs.setInt(3, sentT.getId());
            cs.setString(5, toS(sentT.getText()).trim());
            for (com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory docTC : to) {
                cat_id = docTC.getId();
                cs.setString(1, cat_id);

                try {
                    for (ToneScore s : docTC.getTones()) {
                        cs.setString(2, s.getId());
                        cs.setDouble(4, s.getScore());
                        cs.addBatch();
                    }
                    if (DEBUG) {
                        DbUtl.getWarningsFromStatement(cs);
                    }
                    //                        sentRes = cs.executeBatch();
                    //                        List l = Arrays.asList(sentRes);
                    //                        results.addAll(l);
                } catch (SQLException s) {
                    System.err.println(
                            "com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(loop)"
                                    + DbUtl.printJDBCExceptionMsg(s));
                    s.printStackTrace();
                } catch (Exception s) {

                }

                System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result["
                        + sentT.getId() + "] " + ArrayUtils.toString(sentRes));
            }
        }
        sentRes = cs.executeBatch();
        try {
            l = Arrays.asList(sentRes);
            results.addAll(l);
            conn.setAutoCommit(true);
            cs.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.setAutoCommit(true);
            } catch (Exception eeee) {
                eeee.printStackTrace();
            }
        }
    } catch (BatchUpdateException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        System.err.println("com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(batch)"
                + DbUtl.printBatchUpdateException(ex));

    } catch (SQLTimeoutException ex) {

        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        ex.printStackTrace();
    } catch (SQLException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        System.err.println("com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(meth)"
                + DbUtl.printJDBCExceptionMsg(ex));

    } finally {
        DbUtl.close(cs);
    }
    return results;
}

From source file:jongo.jdbc.JDBCExecutor.java

/**
 * Utility method which registers in a CallableStatement object the different {@link jongo.jdbc.StoredProcedureParam}
 * instances in the given list. Returns a List of {@link jongo.jdbc.StoredProcedureParam} with all the OUT parameters
 * registered in the CallableStatement/* w  w  w .  j a  va 2s. c om*/
 * @param cs the CallableStatement object where the parameters are registered.
 * @param params a list of {@link jongo.jdbc.StoredProcedureParam}
 * @return a list of OUT {@link jongo.jdbc.StoredProcedureParam} 
 * @throws SQLException if we fail to register any of the parameters in the CallableStatement
 */
private static List<StoredProcedureParam> addParameters(final CallableStatement cs,
        final List<StoredProcedureParam> params) throws SQLException {
    List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>();
    int i = 1;
    for (StoredProcedureParam p : params) {
        final Integer sqlType = p.getType();
        if (p.isOutParameter()) {
            l.debug("Adding OUT parameter " + p.toString());
            cs.registerOutParameter(i++, sqlType);
            outParams.add(p);
        } else {
            l.debug("Adding IN parameter " + p.toString());
            switch (sqlType) {
            case Types.BIGINT:
            case Types.INTEGER:
            case Types.TINYINT:
                //                    case Types.NUMERIC:
                cs.setInt(i++, Integer.valueOf(p.getValue()));
                break;
            case Types.DATE:
                cs.setDate(i++, (Date) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.TIME:
                cs.setTime(i++, (Time) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.TIMESTAMP:
                cs.setTimestamp(i++, (Timestamp) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.DECIMAL:
                cs.setBigDecimal(i++, (BigDecimal) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.DOUBLE:
                cs.setDouble(i++, Double.valueOf(p.getValue()));
                break;
            case Types.FLOAT:
                cs.setLong(i++, Long.valueOf(p.getValue()));
                break;
            default:
                cs.setString(i++, p.getValue());
                break;
            }
        }
    }
    return outParams;
}

From source file:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java

private void setDouble(CallableStatement cs, double num) throws SQLException {
    if (useName) {
        cs.setDouble(currName, num);
    } else {//  w w  w  .  j av a2 s  .  co  m
        cs.setDouble(colIdx, num);
    }
}

From source file:com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.java

public void runStoredProcedures(String coffeeNameArg, double maximumPercentageArg, double newPriceArg)
        throws SQLException {
    CallableStatement cs = null;

    try {/*  w  ww  .  ja va2  s  .  c o  m*/

        System.out.println("\nCalling the stored procedure GET_SUPPLIER_OF_COFFEE");
        cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
        cs.setString(1, coffeeNameArg);
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.execute();

        String supplierName = cs.getString(2);

        if (supplierName != null) {
            System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName);
        } else {
            System.out.println("\nUnable to find the coffee " + coffeeNameArg);
        }

        System.out.println("\nCalling the procedure SHOW_SUPPLIERS");
        cs = this.con.prepareCall("{call SHOW_SUPPLIERS()}");
        ResultSet rs = cs.executeQuery();

        while (rs.next()) {
            String supplier = rs.getString("SUP_NAME");
            String coffee = rs.getString("COF_NAME");
            System.out.println(supplier + ": " + coffee);
        }

        System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

        System.out.println("\nCalling the procedure RAISE_PRICE");
        cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
        cs.setString(1, coffeeNameArg);
        cs.setDouble(2, maximumPercentageArg);
        cs.registerOutParameter(3, Types.DOUBLE);
        cs.setDouble(3, newPriceArg);

        cs.execute();

        System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3));

        System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (cs != null) {
            cs.close();
        }
    }
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public double addFund(final Fund fund) {
    int uid = -1;

    Connection conn = null;/*  w w w . j a  va 2 s.  c  om*/
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITFUND (?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, fund.getAuctionUid());
        stmt.setInt(3, fund.getUserUid());
        stmt.setDouble(4, fund.getBidPrice());

        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("FUND [method:{} result:{}]", new Object[] { "edit", uid });
    }

    double sum = 0.0;

    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETFUNDSUM (?)}");
        stmt.setInt(1, fund.getAuctionUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            sum = rs.getDouble(1);
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("FUND [method:{} result:{}]", new Object[] { "sum", sum });
    }

    return sum;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public Item addBid(final Bid bid) {
    Item obj = null;//from w  w  w. j a  v a 2 s  . c  o m

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITBID (?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, bid.getItemUid());
        stmt.setInt(3, bid.getUserUid());
        stmt.setDouble(4, bid.getBidPrice());

        stmt.execute();
        stmt.close(); // close statement to prevent leak

        stmt = conn.prepareCall("{call SP_GETITEM (?)}");
        stmt.setInt(1, bid.getItemUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER"))
                    .setName(rs.getString("NAME")).sertCurPrice(rs.getDouble("CURPRICE"))
                    .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT"))
                    .setWatchCount(rs.getInt("WATCHCOUNT"));

            obj = builder.build();
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("BID [method:{} result:{}]",
                new Object[] { "add", obj != null ? obj.toString() : "[error]" });
    }

    return obj;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public int editItem(final Item item) {
    int uid = -1;

    Connection conn = null;/*w ww . ja va  2 s .c  om*/
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITITEM (?,?,?,?,?,?,?,?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, item.getAuctionUid());
        stmt.setString(3, item.getItemNumber());
        stmt.setString(4, item.getName());
        stmt.setString(5, item.getDescription());
        stmt.setString(6, item.getCategory());
        stmt.setString(7, item.getSeller());
        stmt.setDouble(8, item.getValPrice());
        stmt.setDouble(9, item.getMinPrice());
        stmt.setDouble(10, item.getIncPrice());
        stmt.setString(11, item.getUrl());

        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));

        uid = -1;
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("ITEM [method:{} result:{}]", new Object[] { "edit", uid });
    }

    return uid;
}

From source file:com.netspective.axiom.sql.StoredProcedureParameter.java

/**
 * Apply the IN/OUT parameters of the callable statement object
 *
 * @param vac  the context in which the apply action is being performed
 * @param stmt the statement object//from w w w  .  j  av a 2 s .  c o m
 */
public void apply(StoredProcedureParameters.ValueApplyContext vac, ConnectionContext cc, CallableStatement stmt)
        throws SQLException {
    int paramNum = vac.getNextParamNum();
    int jdbcType = getSqlType().getJdbcType();
    if (getType().getValueIndex() == Type.IN || getType().getValueIndex() == Type.IN_OUT) {
        String text = value.getTextValue(cc);
        switch (jdbcType) {
        case Types.VARCHAR:
            // user override value if it exists
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = (String) vac.getActiveParamOverrideValue();
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.VARCHAR);
            else if (text != null)
                stmt.setObject(paramNum, text);
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);
            break;

        case Types.CHAR:
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = (String) vac.getActiveParamOverrideValue();
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.CHAR);
            else if (text != null)
                stmt.setObject(paramNum, text.substring(0, 1));
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);
            break;

        case Types.INTEGER:
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = vac.getActiveParamOverrideValue() != null ? vac.getActiveParamOverrideValue().toString()
                        : null;
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.INTEGER);
            else if (text != null)
                stmt.setInt(paramNum, Integer.parseInt(text));
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);

            break;

        case Types.DOUBLE:
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = vac.getActiveParamOverrideValue() != null ? vac.getActiveParamOverrideValue().toString()
                        : null;
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.DOUBLE);
            else if (text != null)
                stmt.setDouble(paramNum, Double.parseDouble(text));
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);

            break;

        case Types.ARRAY:
            // Arrays are quite tricky. Right now, this is supporting String arrays only.
            // TODO: Support integer and float arrays also
            String[] textValues = value.getTextValues(cc);
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                textValues = (String[]) vac.getActiveParamOverrideValue();
            applyInArrayValue(cc, stmt, paramNum, textValues);
            break;
        default:
            log.warn("Unknown JDBC type for parameter '" + getName() + "' (index=" + paramNum
                    + ") of stored procedure '" + parent.getProcedure() + "'.");
            break;
        }
    }
    if (getType().getValueIndex() == Type.OUT || getType().getValueIndex() == Type.IN_OUT) {
        String identifier = getSqlType().getIdentifier();
        // result sets are returned differently for different vendors
        if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER))
            stmt.registerOutParameter(paramNum, getVendorSpecificResultSetType(cc));
        else
            stmt.registerOutParameter(paramNum, jdbcType);
    }
}

From source file:org.brucalipto.sqlutil.DB2SQLManager.java

/**
 * Method useful for using STORED PROCEDURE
 * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure
 * @return The {@link SPOutputBean} containing returned values
 *//*  w  ww.jav  a  2  s . c  o  m*/
public SPOutputBean executeSP(final SPInputBean spib) throws SQLException {
    Connection conn = null;
    CallableStatement call = null;
    ResultSet resultSet = null;
    final String procedureName = spib.spName;

    SPParameter[] inputParameters = spib.inputParams;
    int[] outputParameters = spib.outputParams;

    final int inputParametersSize = inputParameters.length;
    final int outputParametersSize = outputParameters.length;

    final StringBuffer spName = new StringBuffer("call ").append(procedureName).append('(');
    int totalParameters = inputParametersSize + outputParametersSize;
    for (int i = 0; i < totalParameters; i++) {
        if (i != totalParameters - 1) {
            spName.append("?,");
        } else {
            spName.append('?');
        }
    }
    spName.append(")");

    try {
        if (this.dataSource != null) {
            conn = this.dataSource.getConnection();
        } else {
            conn = this.connection;
        }
        call = conn.prepareCall(spName.toString());
        for (int i = 0; i < inputParametersSize; i++) {
            final SPParameter inputParam = inputParameters[i];
            final int sqlType = inputParam.sqlType;
            final Object inputParamValue = inputParam.value;
            log.debug((i + 1) + ") Setting input value: " + inputParam);
            if (inputParamValue == null) {
                call.setNull(i + 1, sqlType);
                continue;
            }
            switch (sqlType) {
            case Types.VARCHAR:
                call.setString(i + 1, (String) inputParamValue);
                break;
            case Types.INTEGER:
                if (inputParamValue instanceof Integer) {
                    call.setInt(i + 1, ((Integer) inputParamValue).intValue());
                } else if (inputParamValue instanceof Long) {
                    call.setLong(i + 1, ((Long) inputParamValue).longValue());
                }
                break;
            case Types.DATE:
                call.setDate(i + 1, (Date) inputParamValue);
                break;
            case Types.BOOLEAN:
                call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue());
                break;
            case Types.CHAR:
                call.setString(i + 1, ((Character) inputParamValue).toString());
                break;
            case Types.DOUBLE:
                call.setDouble(i + 1, ((Double) inputParamValue).doubleValue());
                break;
            case Types.FLOAT:
                call.setFloat(i + 1, ((Float) inputParamValue).floatValue());
                break;
            case Types.TIMESTAMP:
                call.setTimestamp(i + 1, (Timestamp) inputParamValue);
                break;
            default:
                call.setObject(i + 1, inputParamValue);
                break;
            }
        }

        for (int i = 0; i < outputParametersSize; i++) {
            int sqlType = outputParameters[i];
            log.debug((i + 1) + ") Registering output type 'Types."
                    + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'");
            call.registerOutParameter(inputParametersSize + i + 1, sqlType);
        }

        log.debug("Going to call: '" + procedureName + "'");
        long elapsedTime = System.currentTimeMillis();
        boolean hasResultSet = call.execute();
        log.debug("SP '" + procedureName + "' executed in " + (System.currentTimeMillis() - elapsedTime)
                + "millis");
        if (hasResultSet) {
            log.debug("This SP is going to return also a resultSet");
        }

        final SPOutputBean output = new SPOutputBean();
        for (int i = 0; i < outputParametersSize; i++) {
            int sqlType = outputParameters[i];
            log.debug((i + 1) + ") Getting output type 'Types."
                    + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'");
            final Object spResult = call.getObject(inputParametersSize + i + 1);
            SPParameter outParam = new SPParameter(sqlType, spResult);
            output.addResult(outParam);
        }
        if (hasResultSet) {
            RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(call.getResultSet(), false);
            if (log.isDebugEnabled()) {
                log.debug("Going to return a RowSetDynaClass with following properties:");
                DynaProperty[] properties = rowSetDynaClass.getDynaProperties();
                for (int i = 0; i < properties.length; i++) {
                    log.debug("Name: '" + properties[i].getName() + "'; Type: '"
                            + properties[i].getType().getName() + "'");
                }
            }
            SPParameter outParam = new SPParameter(Types.JAVA_OBJECT, rowSetDynaClass);
            output.addResult(outParam);
        }
        return output;
    } finally {
        closeResources(resultSet, call, conn);
    }
}