List of usage examples for java.sql CallableStatement getBigDecimal
BigDecimal getBigDecimal(String parameterName) throws SQLException;
NUMERIC parameter as a java.math.BigDecimal object with as many digits to the right of the decimal point as the value contains. From source file:com.nortal.petit.core.dialect.OracleSqlDialect.java
@SuppressWarnings("unchecked") @Override//from w ww .j a v a2s .c o m public <B> B insertReturningId(JdbcOperations jdbcOperations, String sql, String idColumn, final Object... params) { final String actualSql = new StringBuilder("BEGIN ").append(sql) .append(" RETURNING " + idColumn + " INTO ?; END;").toString(); try { return (B) jdbcOperations.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement cs = con.prepareCall(actualSql); ArgPreparedStatementSetter.setValues(cs, params, 1); cs.registerOutParameter(params.length + 1, Types.DECIMAL); return cs; } }, new CallableStatementCallback<B>() { @Override public B doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { cs.execute(); BigDecimal bd = cs.getBigDecimal(params.length + 1); return (B) Long.valueOf(bd.longValue()); } }); } catch (RuntimeException e) { LOG.error("Error processing SQL '" + sql + "' with parameters: " + StringUtils.join(params, "; ")); throw e; } }
From source file:com.netspective.axiom.sql.StoredProcedureParameter.java
/** * Extract the OUT parameter values from the callable statment and * assign them to the value of the parameter. *//*from w w w .j ava2s.c o m*/ public void extract(ConnectionContext cc, CallableStatement stmt) throws SQLException { if (getType().getValueIndex() == StoredProcedureParameter.Type.IN) return; int index = this.getIndex(); QueryParameterType paramType = getSqlType(); int jdbcType = paramType.getJdbcType(); String identifier = paramType.getIdentifier(); // result sets are special if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER)) { ResultSet rs = (ResultSet) stmt.getObject(index); QueryResultSet qrs = new QueryResultSet(getParent().getProcedure(), cc, rs); value.getValue(cc).setValue(qrs); return; } switch (jdbcType) { case Types.VARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case Types.INTEGER: value.getValue(cc).setValue(new Integer(stmt.getInt(index))); break; case Types.DOUBLE: value.getValue(cc).setValue(new Double(stmt.getDouble(index))); break; case Types.CLOB: Clob clob = stmt.getClob(index); value.getValue(cc).setTextValue(clob.getSubString(1, (int) clob.length())); break; case java.sql.Types.ARRAY: Array array = stmt.getArray(index); value.getValue(cc).setValue(array); break; case java.sql.Types.BIGINT: long bigint = stmt.getLong(index); value.getValue(cc).setValue(new Long(bigint)); break; case java.sql.Types.BINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.BIT: boolean bit = stmt.getBoolean(index); value.getValue(cc).setValue(new Boolean(bit)); case java.sql.Types.BLOB: value.getValue(cc).setValue(stmt.getBlob(index)); break; case java.sql.Types.CHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case java.sql.Types.DATE: value.getValue(cc).setValue(stmt.getDate(index)); break; case java.sql.Types.DECIMAL: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.DISTINCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.FLOAT: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; case java.sql.Types.JAVA_OBJECT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.LONGVARBINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.LONGVARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; //case java.sql.Types.NULL: // value.getValue(cc).setValue(null); // break; case java.sql.Types.NUMERIC: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.OTHER: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.REAL: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; //case java.sql.Types.REF: // Ref ref = stmt.getRef(index); // break; case java.sql.Types.SMALLINT: short sh = stmt.getShort(index); value.getValue(cc).setValue(new Short(sh)); break; case java.sql.Types.STRUCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.TIME: value.getValue(cc).setValue(stmt.getTime(index)); break; case java.sql.Types.TIMESTAMP: value.getValue(cc).setValue(stmt.getTimestamp(index)); break; case java.sql.Types.TINYINT: byte b = stmt.getByte(index); value.getValue(cc).setValue(new Byte(b)); break; case java.sql.Types.VARBINARY: value.getValue(cc).setValue(stmt.getBytes(index)); break; default: throw new RuntimeException( "Unknown JDBC Type set for stored procedure parameter '" + this.getName() + "'."); } }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result// w ww . j av a2 s . co m */ 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.nuclos.server.dblayer.impl.standard.StandardSqlDBAccess.java
protected <T> T getCallableResultValue(CallableStatement stmt, int index, Class<T> javaType) throws SQLException { Object value;// w ww .j a va 2s. c o m if (javaType == String.class) { value = stmt.getString(index); } else if (javaType == NuclosPassword.class) { value = new NuclosPassword(ServerCryptUtil.decrypt(stmt.getString(index))); } else if (javaType == Double.class) { value = stmt.getDouble(index); } else if (javaType == Long.class) { value = stmt.getLong(index); } else if (javaType == Integer.class) { value = stmt.getInt(index); } else if (javaType == Boolean.class) { value = stmt.getBoolean(index); } else if (javaType == BigDecimal.class) { value = stmt.getBigDecimal(index); } else if (javaType == java.util.Date.class) { value = stmt.getDate(index); } else if (javaType == byte[].class) { value = stmt.getBytes(index); } else if (javaType == NuclosScript.class) { final XStreamSupport xs = XStreamSupport.getInstance(); final XStream xstream = xs.getXStream(); try { value = xstream.fromXML(stmt.getString(index)); } finally { xs.returnXStream(xstream); } } else { throw new IllegalArgumentException("Class " + javaType + " not supported by readField"); } return stmt.wasNull() ? null : javaType.cast(value); }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private ParamValue getOutparameterValue(CallableStatement cs, String type, int ordinal) throws DataServiceFault { try {// www. ja va2 s .com Object elementValue; if (type.equals(DBConstants.DataTypes.STRING)) { elementValue = cs.getString(ordinal); return new ParamValue(elementValue == null ? null : elementValue.toString()); } else if (type.equals(DBConstants.DataTypes.DOUBLE)) { elementValue = cs.getDouble(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Double) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIGINT)) { elementValue = cs.getLong(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Long) elementValue)); } else if (type.equals(DBConstants.DataTypes.INTEGER)) { elementValue = cs.getInt(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Integer) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIME)) { elementValue = cs.getTime(ordinal); return new ParamValue(elementValue == null ? null : this.convertToTimeString((Time) elementValue)); } else if (type.equals(DBConstants.DataTypes.DATE)) { elementValue = cs.getDate(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Date) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIMESTAMP)) { elementValue = cs.getTimestamp(ordinal, calendar); return new ParamValue( elementValue == null ? null : this.convertToTimestampString((Timestamp) elementValue)); } else if (type.equals(DBConstants.DataTypes.BLOB)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else if (type.equals(DBConstants.DataTypes.CLOB)) { elementValue = cs.getClob(ordinal); return new ParamValue(elementValue == null ? null : deriveValueFromClob((Clob) elementValue)); } else if (type.equals(DBConstants.DataTypes.STRUCT)) { elementValue = cs.getObject(ordinal); return new ParamValue(elementValue == null ? null : (Struct) elementValue); } else if (type.equals(DBConstants.DataTypes.ARRAY)) { Array dataArray = cs.getArray(ordinal); ParamValue paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY); if (dataArray != null) { this.processSQLArray(dataArray, paramValue); } return paramValue; } else if (type.equals(DBConstants.DataTypes.NUMERIC)) { elementValue = cs.getBigDecimal(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((BigDecimal) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIT)) { elementValue = cs.getBoolean(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Boolean) elementValue)); } else if (type.equals(DBConstants.DataTypes.TINYINT)) { elementValue = cs.getByte(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Byte) elementValue)); } else if (type.equals(DBConstants.DataTypes.SMALLINT)) { elementValue = cs.getShort(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Short) elementValue)); } else if (type.equals(DBConstants.DataTypes.REAL)) { elementValue = cs.getFloat(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Float) elementValue)); } else if (type.equals(DBConstants.DataTypes.BINARY)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else { throw new DataServiceFault("Unsupported data type: " + type); } } catch (SQLException e) { throw new DataServiceFault(e, "Error in getting sql output parameter values."); } }