List of usage examples for java.sql CallableStatement setDouble
void setDouble(String parameterName, double x) throws SQLException;
double value. From source file:org.brucalipto.sqlutil.OracleSQLManager.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 *///from w ww. java2 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(") }"); log.debug("Going to call: '" + spName + "'"); try { conn = this.dataSource.getConnection(); 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 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'-'" + inputParamValue + "'"); 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); } call.execute(); 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 = null; if (sqlType == SQLUtilTypes.CURSOR) { resultSet = (ResultSet) spResult; RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(resultSet, false); if (log.isDebugEnabled()) { log.debug("Going to return a RowSetDynaClass with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int j = 0; j < properties.length; j++) { log.debug("Name: '" + properties[j].getName() + "'; Type: '" + properties[j].getType().getName() + "'"); } } outParam = new SPParameter(sqlType, rowSetDynaClass); } else { outParam = new SPParameter(sqlType, spResult); } output.addResult(outParam); } return output; } catch (SQLException sqle) { log.error("Caught SQLException", sqle); } finally { closeResources(resultSet, call, conn); } return null; }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result/*from ww w . j av a 2s. c om*/ */ public SqlStatementResult execute(DatabaseExecutable databaseExecutable) throws SQLException { if (!prepared()) { return statementResult; } ProcedureParameter[] param = databaseExecutable.getParametersArray(); Arrays.sort(param, new ProcedureParameterSorter()); String procQuery = null; boolean hasOut = false; boolean hasParameters = (param != null && param.length > 0); List<ProcedureParameter> outs = null; List<ProcedureParameter> ins = null; if (hasParameters) { // split the params into ins and outs outs = new ArrayList<ProcedureParameter>(); ins = new ArrayList<ProcedureParameter>(); int type = -1; for (int i = 0; i < param.length; i++) { type = param[i].getType(); if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { // add to the ins list ins.add(param[i]); } else if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { // add to the outs list outs.add(param[i]); } } char QUESTION_MARK = '?'; String COMMA = ", "; // init the string buffer StringBuilder sb = new StringBuilder("{ "); if (!outs.isEmpty()) { // build the out params place holders for (int i = 0, n = outs.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" = "); } sb.append(" call "); if (databaseExecutable.supportCatalogOrSchemaInFunctionOrProcedureCalls()) { String namePrefix = null; if (databaseExecutable.supportCatalogInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getCatalogName(); } if (databaseExecutable.supportSchemaInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getSchemaName(); } if (namePrefix != null) { sb.append(namePrefix).append('.'); } } sb.append(databaseExecutable.getName()).append("( "); // build the ins params place holders for (int i = 0, n = ins.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" ) }"); // determine if we have out params hasOut = !(outs.isEmpty()); procQuery = sb.toString(); } else { StringBuilder sb = new StringBuilder(); sb.append("{ call "); if (databaseExecutable.getSchemaName() != null) { sb.append(databaseExecutable.getSchemaName()).append('.'); } sb.append(databaseExecutable.getName()).append("( ) }"); procQuery = sb.toString(); } //Log.debug(procQuery); // null value literal String NULL = "null"; // clear any warnings conn.clearWarnings(); Log.info("Executing: " + procQuery); CallableStatement cstmnt = null; try { // prepare the statement cstmnt = conn.prepareCall(procQuery); stmnt = cstmnt; } catch (SQLException e) { handleException(e); statementResult.setSqlException(e); return statementResult; } // check if we are passing parameters if (hasParameters) { // the parameter index counter int index = 1; // the java.sql.Type value int dataType = -1; // the parameter input value String value = null; // register the out params for (int i = 0, n = outs.size(); i < n; i++) { //Log.debug("setting out at index: " + index); cstmnt.registerOutParameter(index, outs.get(i).getDataType()); index++; } try { // register the in params for (int i = 0, n = ins.size(); i < n; i++) { ProcedureParameter procedureParameter = ins.get(i); value = procedureParameter.getValue(); dataType = procedureParameter.getDataType(); // try infer a type if OTHER if (dataType == Types.OTHER) { // checking only for bit/bool for now if (isTrueFalse(value)) { dataType = Types.BOOLEAN; } else if (isBit(value)) { dataType = Types.BIT; value = value.substring(2, value.length() - 1); } } if (MiscUtils.isNull(value) || value.equalsIgnoreCase(NULL)) { cstmnt.setNull(index, dataType); } else { switch (dataType) { case Types.TINYINT: byte _byte = Byte.valueOf(value).byteValue(); cstmnt.setShort(index, _byte); break; case Types.SMALLINT: short _short = Short.valueOf(value).shortValue(); cstmnt.setShort(index, _short); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: cstmnt.setString(index, value); break; case Types.BIT: case Types.BOOLEAN: boolean _boolean = false; if (NumberUtils.isNumber(value)) { int number = Integer.valueOf(value); if (number > 0) { _boolean = true; } } else { _boolean = Boolean.valueOf(value).booleanValue(); } cstmnt.setBoolean(index, _boolean); break; case Types.BIGINT: long _long = Long.valueOf(value).longValue(); cstmnt.setLong(index, _long); break; case Types.INTEGER: int _int = Integer.valueOf(value).intValue(); cstmnt.setInt(index, _int); break; case Types.REAL: float _float = Float.valueOf(value).floatValue(); cstmnt.setFloat(index, _float); break; case Types.NUMERIC: case Types.DECIMAL: cstmnt.setBigDecimal(index, new BigDecimal(value)); break; /* case Types.DATE: case Types.TIMESTAMP: case Types.TIME: cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value)); */ case Types.FLOAT: case Types.DOUBLE: double _double = Double.valueOf(value).doubleValue(); cstmnt.setDouble(index, _double); break; default: cstmnt.setObject(index, value); } } // increment the index index++; } } catch (Exception e) { statementResult.setOtherErrorMessage(e.getClass().getName() + ": " + e.getMessage()); return statementResult; } } /* test creating function for postgres: CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT; */ try { cstmnt.clearWarnings(); boolean hasResultSet = cstmnt.execute(); Map<String, Object> results = new HashMap<String, Object>(); if (hasOut) { // incrementing index int index = 1; // return value from each registered out String returnValue = null; for (int i = 0; i < param.length; i++) { int type = param[i].getType(); int dataType = param[i].getDataType(); if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { switch (dataType) { case Types.TINYINT: returnValue = Byte.toString(cstmnt.getByte(index)); break; case Types.SMALLINT: returnValue = Short.toString(cstmnt.getShort(index)); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: returnValue = cstmnt.getString(index); break; case Types.BIT: case Types.BOOLEAN: returnValue = Boolean.toString(cstmnt.getBoolean(index)); break; case Types.INTEGER: returnValue = Integer.toString(cstmnt.getInt(index)); break; case Types.BIGINT: returnValue = Long.toString(cstmnt.getLong(index)); break; case Types.REAL: returnValue = Float.toString(cstmnt.getFloat(index)); break; case Types.NUMERIC: case Types.DECIMAL: returnValue = cstmnt.getBigDecimal(index).toString(); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: returnValue = cstmnt.getDate(index).toString(); break; case Types.FLOAT: case Types.DOUBLE: returnValue = Double.toString(cstmnt.getDouble(index)); break; } if (returnValue == null) { returnValue = "NULL"; } results.put(param[i].getName(), returnValue); index++; } } } if (!hasResultSet) { statementResult.setUpdateCount(cstmnt.getUpdateCount()); } else { statementResult.setResultSet(cstmnt.getResultSet()); } useCount++; statementResult.setOtherResult(results); } catch (SQLException e) { statementResult.setSqlException(e); } catch (Exception e) { statementResult.setMessage(e.getMessage()); } return statementResult; }
From source file:org.xenei.bloomgraph.bloom.sql.MySQLCommands.java
@Override public void tripleInsert(final Connection connection, final int pageId, final PageSearchItem candidate) throws SQLException, IOException { CallableStatement stmt = null; try {//from w ww .ja va 2 s . co m final String simpleProc = "{ call add_triple(?,?,?,?,?,?,?,?) }"; stmt = connection.prepareCall(simpleProc); stmt.setInt(1, pageId); stmt.setInt(2, candidate.getTripleFilter().getHammingWeight()); stmt.setDouble(3, candidate.getTripleFilter().getApproximateLog(3)); stmt.setInt(4, candidate.getTriple().hashCode()); stmt.setBlob(5, DBIO.asInputStream(candidate.getTripleFilter().getByteBuffer())); stmt.setBlob(6, DBIO.asInputStream(candidate.getSerializable().getByteBuffer())); stmt.setBlob(7, DBIO.asInputStream(candidate.getPageFilter().getByteBuffer())); stmt.registerOutParameter(8, java.sql.Types.INTEGER); stmt.execute(); candidate.getSerializable().setIndex(stmt.getInt(8)); } finally { DbUtils.closeQuietly(stmt); } }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Add a metric alarm//from w ww . j a v a2s . c o m * * @param alarmName * @param alarmDescription * @param comparisonOperator * @param metricNamespace * @param metricName * @param satistic * @param threshold * @param unit * @return lastId */ @Override public Integer addMetricAlarm(String alarmName, String alarmDescription, AlarmComparisonOperator comparisonOperator, String metricNamespace, String metricName, MetricStatistic statistic, Double threshold, MetricUnit unit) { Integer lastId = 0; Connection conn = null; CallableStatement stmt = null; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); // create new alarm stmt = conn.prepareCall("uspMetricAlarm_Insert(?,?,?,?,?,?,?,?,?,?,?,?)"); stmt.setInt(1, comparisonOperator.getValue()); stmt.setInt(2, statistic.getValue()); stmt.setInt(3, unit.getValue()); stmt.setString(4, alarmName); stmt.setString(5, alarmDescription); stmt.setString(6, metricNamespace); stmt.setString(7, metricName); stmt.setDouble(8, threshold); stmt.setString(9, null); stmt.setString(10, null); stmt.setInt(11, AlarmState.OK.getValue()); stmt.registerOutParameter(12, Types.INTEGER); stmt.execute(); lastId = stmt.getInt(12); } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } return lastId; }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Add metric data//from w ww. j a v a2 s .c o m * * @param metricNamespace * @param metricName * @param unit * @param value * @data the extra data field */ @Override public void addMetricData(String metricNamespace, String metricName, MetricUnit unit, Double value, String data) { Connection conn = null; CallableStatement stmt = null; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); // create new metric data stmt = conn.prepareCall("uspMetricData_Insert(?,?,?,?,?)"); stmt.setString(1, metricNamespace); stmt.setString(2, metricName); stmt.setInt(3, unit.getValue()); stmt.setDouble(4, value); stmt.setString(5, data); stmt.execute(); // Create a data item MetricData dataitem = new MetricData(); dataitem.setMetricName(metricName); dataitem.setMetricNamespace(metricNamespace); dataitem.setUnit(unit); dataitem.setValue(value); dataitem.setData(data); // Evaluate for alarms evaluateMetricAlarm(dataitem); } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Add metric sla//from ww w . j av a2 s . c o m * * @param metricAlarmId * @param slaName * @param slaDescription * @param percentage * @param lastId */ @Override public Integer addMetricSla(Integer metricAlarmId, String slaName, String slaDescription, Double percentage) { Connection conn = null; CallableStatement stmt = null; Integer lastId = 0; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); // create new sla stmt = conn.prepareCall("uspMetricSla_Insert(?,?,?,?,?)"); stmt.setInt(1, metricAlarmId); stmt.setString(2, slaName); stmt.setString(3, slaDescription); stmt.setDouble(4, percentage); stmt.registerOutParameter(5, Types.INTEGER); stmt.execute(); lastId = stmt.getInt(5); } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } return lastId; }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Update a metric alarm//from ww w . java2 s . c o m * * @param metricAlarmId * @param alarmName * @param alarmDescription * @param comparisonOperator * @param metricNamespace * @param metricName * @param statistic * @param threshold * @param unit * @param stateReason * @param stateReasonData * @param alarmState * @return rowsUpdated */ @Override public Integer updateMetricAlarm(Integer metricAlarmId, String alarmName, String alarmDescription, AlarmComparisonOperator comparisonOperator, String metricNamespace, String metricName, MetricStatistic statistic, Double threshold, MetricUnit unit, String stateReason, String stateReasonData, AlarmState alarmState) { Connection conn = null; CallableStatement stmt = null; Integer rowsUpdated = 0; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); // create new alarm stmt = conn.prepareCall("uspMetricAlarm_Update(?,?,?,?,?,?,?,?,?,?,?,?)"); stmt.setInt(1, metricAlarmId); stmt.setInt(2, comparisonOperator.getValue()); stmt.setInt(3, statistic.getValue()); stmt.setInt(4, unit.getValue()); stmt.setString(5, alarmName); stmt.setString(6, alarmDescription); stmt.setString(7, metricNamespace); stmt.setString(8, metricName); stmt.setDouble(9, threshold); stmt.setString(10, stateReason); stmt.setString(11, stateReasonData); stmt.setInt(12, alarmState.getValue()); rowsUpdated = stmt.executeUpdate(); } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } return rowsUpdated; }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Update metric sla/*from w w w . j av a 2 s . c o m*/ * * @param metricSlaId * @param metricAlarmId * @param slaName * @param slaDescription * @param percentage * @return rowsUpdated */ @Override public Integer updateMetricSla(Integer metricSlaId, Integer metricAlarmId, String slaName, String slaDescription, Double percentage) { Connection conn = null; CallableStatement stmt = null; Integer rowsUpdated = 0; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); // Update sla stmt = conn.prepareCall("uspMetricSla_Update(?,?,?,?,?,?)"); stmt.setInt(1, metricSlaId); stmt.setInt(2, metricAlarmId); stmt.setString(3, slaName); stmt.setString(4, slaDescription); stmt.setDouble(5, percentage); rowsUpdated = stmt.executeUpdate(); } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } return rowsUpdated; }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
public List<ZvysenieSpotreby> getIncreasedSpendingStatistics(IncreasedSpendingStatisticParams params, double loadFactor) { try (Connection connection = OracleJDBCConnector.getConnection();) { String fn = "get_zvysena_miera_spotreby"; if (loadFactor < 1) { fn = "get_znizena_miera_spotreby"; }// w w w . j a v a 2s .c om CallableStatement stmnt = connection.prepareCall("SELECT * FROM TABLE(" + fn + "(?,?,?))"); stmnt.setDate(1, Utils.utilDateToSqlDate(params.getDatumOd())); stmnt.setDate(2, Utils.utilDateToSqlDate(params.getDatumDo())); stmnt.setDouble(3, loadFactor); ResultSet result = stmnt.executeQuery(); List<ZvysenieSpotreby> output = new LinkedList<>(); while (result.next()) { ZvysenieSpotreby o = new ZvysenieSpotreby(); o.setMeno(result.getString("MENO")); o.setPriemernaSpotrebaVMinulosti(result.getDouble("PRIEMERNA_SPOTREBA_V_MINULOSTI")); o.setVelicina(MeraciaVelicina.valueOf(result.getString("VELICINA").toUpperCase())); o.setZvysenaSpotreba(result.getDouble("ZVYSENA_SPOTREBA")); output.add(o); } return output; } catch (SQLException e) { throw new RuntimeException(e); } }