List of usage examples for java.sql CallableStatement setObject
void setObject(String parameterName, Object x) throws SQLException;
From source file:com.hangum.tadpole.sql.util.executer.procedure.MSSQLProcedureExecuter.java
@Override public boolean exec(List<InOutParameterDAO> parameterList) throws Exception { initResult();//w w w . ja v a 2 s . c o m java.sql.Connection javaConn = null; java.sql.CallableStatement cstmt = null; try { if (listOutParamValues == null) getOutParameters(); SqlMapClient client = TadpoleSQLManager.getInstance(userDB); javaConn = client.getDataSource().getConnection(); // make the script String[] arrProcedureName = StringUtils.split(procedureDAO.getName(), "."); String strProcName = "[" + arrProcedureName[0] + "].[" + arrProcedureName[1] + "]"; StringBuffer sbQuery = new StringBuffer("{call " + strProcName + "("); // in script int intParamSize = this.getParametersCount(); for (int i = 0; i < intParamSize; i++) { if (i == 0) sbQuery.append("?"); else sbQuery.append(",?"); } sbQuery.append(")}"); if (logger.isDebugEnabled()) logger.debug("Execute Procedure query is\t " + sbQuery.toString()); // set prepare call cstmt = javaConn.prepareCall(sbQuery.toString()); // Set input value for (InOutParameterDAO inOutParameterDAO : parameterList) { // if(logger.isDebugEnabled()) logger.debug("Parameter " + inOutParameterDAO.getOrder() + " Value is " + inOutParameterDAO.getValue()); // if (null==inOutParameterDAO.getValue() || "".equals(inOutParameterDAO.getValue())){ // MessageDialog.openError(null, "Error", inOutParameterDAO.getName() + " parameters are required."); // return false; // } cstmt.setObject(inOutParameterDAO.getOrder(), inOutParameterDAO.getValue()); } // Set the OUT Parameter for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); if (logger.isDebugEnabled()) logger.debug("Out Parameter " + dao.getOrder() + " JavaType is " + RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); cstmt.registerOutParameter(dao.getOrder(), RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); } cstmt.execute(); // // set // // cursor list // boolean is cursor boolean isCursor = false; ResultSet rs = cstmt.getResultSet(); if (rs != null) { setResultCursor(rs); isCursor = true; // mssql? result set? ? ?. while (cstmt.getMoreResults()) { setResultCursor(cstmt.getResultSet()); } } else { for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); Object obj = cstmt.getObject(dao.getOrder()); // String? Type Cast .... String ... if (obj != null) { dao.setValue(obj.toString()); } } } if (!isCursor) { List<Map<Integer, Object>> sourceDataList = new ArrayList<Map<Integer, Object>>(); Map<Integer, Object> tmpRow = null; for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); tmpRow = new HashMap<Integer, Object>(); tmpRow.put(0, "" + dao.getOrder()); tmpRow.put(1, "" + dao.getName()); tmpRow.put(2, "" + dao.getType()); tmpRow.put(3, "" + dao.getRdbType()); tmpRow.put(4, "" + dao.getLength()); tmpRow.put(5, "" + dao.getValue()); sourceDataList.add(tmpRow); } setResultNoCursor(new TadpoleResultSet(sourceDataList)); } return true; } catch (Exception e) { logger.error("ProcedureExecutor executing error", e); throw e; } finally { try { if (cstmt != null) cstmt.close(); } catch (Exception e) { } try { if (javaConn != null) javaConn.close(); } catch (Exception e) { } } }
From source file:com.hangum.tadpole.engine.sql.util.executer.procedure.OracleProcedureExecuter.java
@Override public boolean exec(List<InOutParameterDAO> parameterList) throws Exception { initResult();//from w w w. ja va2 s . co m java.sql.Connection javaConn = null; java.sql.CallableStatement cstmt = null; java.sql.PreparedStatement pstmt = null; OracleDbmsOutputUtil dbmsOutput = null; try { if (listOutParamValues == null) getOutParameters(); SqlMapClient client = TadpoleSQLManager.getInstance(userDB); javaConn = client.getDataSource().getConnection(); try { dbmsOutput = new OracleDbmsOutputUtil(javaConn); dbmsOutput.enable(1000000); } catch (SQLException e) { logger.error("dbmsoutput exception", e); } // make the script String strExecuteScript = getMakeExecuteScript(); if (StringUtils.startsWithIgnoreCase(strExecuteScript, "SELECT")) { // function execute... pstmt = javaConn.prepareStatement(strExecuteScript); for (InOutParameterDAO inOutParameterDAO : parameterList) { pstmt.setObject(inOutParameterDAO.getOrder(), inOutParameterDAO.getValue()); } // Set the OUT Parameter for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); //pstmt.registerOutParameter(dao.getOrder(), RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); pstmt.setObject(dao.getOrder(), ""); } ResultSet rs = pstmt.executeQuery(); setResultCursor(rs); } else { // set prepare call cstmt = javaConn.prepareCall(strExecuteScript); // Set input value for (InOutParameterDAO inOutParameterDAO : parameterList) { cstmt.setObject(inOutParameterDAO.getOrder(), inOutParameterDAO.getValue()); } // Set the OUT Parameter for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); if (logger.isDebugEnabled()) logger.debug("Out Parameter " + dao.getOrder() + " JavaType is " + RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); cstmt.registerOutParameter(dao.getOrder(), RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); } cstmt.execute(); // // set // // cursor list // boolean is cursor boolean isCursor = false; for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); if (logger.isDebugEnabled()) logger.debug("Execute Procedure result " + dao.getName() + "=" + cstmt.getString(dao.getOrder())); Object obj = cstmt.getObject(dao.getOrder()); // String? Type Cast .... String ... if (obj != null) { if ("SYS_REFCURSOR".equals(dao.getRdbType())) { isCursor = true; ResultSet rs = (ResultSet) obj; setResultCursor(rs); // cursor? ? ? 1. } else { dao.setValue(obj.toString()); } } } if (!isCursor) { List<Map<Integer, Object>> sourceDataList = new ArrayList<Map<Integer, Object>>(); Map<Integer, Object> tmpRow = null; for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); tmpRow = new HashMap<Integer, Object>(); tmpRow.put(0, "" + dao.getOrder()); tmpRow.put(1, "" + dao.getName()); tmpRow.put(2, "" + dao.getType()); tmpRow.put(3, "" + dao.getRdbType()); tmpRow.put(4, "" + dao.getLength()); tmpRow.put(5, "" + dao.getValue()); sourceDataList.add(tmpRow); } setResultNoCursor(new TadpoleResultSet(sourceDataList)); } } try { dbmsOutput.show(); } catch (SQLException e) { logger.error("dbmsoutput exception", e); } setStrOutput(dbmsOutput.getOutput()); return true; } catch (Exception e) { logger.error("ProcedureExecutor executing error", e); throw e; } finally { try { if (pstmt != null) pstmt.close(); } catch (Exception e) { } try { if (cstmt != null) cstmt.close(); } catch (Exception e) { } try { if (dbmsOutput != null) dbmsOutput.close(); } catch (Exception e) { } try { if (javaConn != null) javaConn.close(); } catch (Exception e) { } } }
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 w w . j ava2s. 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); } }
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 */// w w w.ja va 2 s . c om 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 w ww . j av a 2s. 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.gofleet.openLS.ddbb.dao.GeoCodingDAO.java
@Transactional(readOnly = true) public List<List<AbstractResponseParametersType>> reverseGeocode(final ReverseGeocodeRequestType param) { HibernateCallback<List<List<AbstractResponseParametersType>>> action = new HibernateCallback<List<List<AbstractResponseParametersType>>>() { public List<List<AbstractResponseParametersType>> doInHibernate(Session session) throws HibernateException, SQLException { List<List<AbstractResponseParametersType>> res = new LinkedList<List<AbstractResponseParametersType>>(); PositionType position = param.getPosition(); position.getPoint().getPos().getValue(); Geometry geometry = GeoUtil.getGeometry(position); List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>(); // TODO change deprecation? @SuppressWarnings("deprecation") CallableStatement consulta = session.connection().prepareCall("{call gls_reverse_geocoding(?)}"); PGgeometry geom = new PGgeometry(geometry.toText()); consulta.setObject(1, geom); LOG.debug(consulta);//from ww w. j a v a2s. com ResultSet o = consulta.executeQuery(); ReverseGeocodeResponseType grt = new ReverseGeocodeResponseType(); while (o.next()) { ReverseGeocodedLocationType geocode = new ReverseGeocodedLocationType(); if (geocode.getAddress() == null) geocode.setAddress(new AddressType()); if (geocode.getAddress().getStreetAddress() == null) geocode.getAddress().setStreetAddress(new StreetAddressType()); for (int i = 1; i < o.getMetaData().getColumnCount(); i++) { String value = new String(o.getString(i).getBytes(), Charset.forName("ISO-8859-1")); if (o.getMetaData().getColumnName(i).equals("street")) { StreetNameType street = new StreetNameType(); street.setValue(value); street.setOfficialName(value); geocode.getAddress().getStreetAddress().getStreet().add(street); } else if (o.getMetaData().getColumnName(i).equals("munsub")) { NamedPlaceType place = new NamedPlaceType(); place.setValue(value); place.setType(NamedPlaceClassification.MUNICIPALITY_SUBDIVISION); geocode.getAddress().getPlace().add(place); } else if (o.getMetaData().getColumnName(i).equals("mun")) { NamedPlaceType place = new NamedPlaceType(); place.setValue(value); place.setType(NamedPlaceClassification.MUNICIPALITY); geocode.getAddress().getPlace().add(place); } else if (o.getMetaData().getColumnName(i).equals("subcountry")) { NamedPlaceType place = new NamedPlaceType(); place.setValue(value); place.setType(NamedPlaceClassification.COUNTRY_SUBDIVISION); geocode.getAddress().getPlace().add(place); } else if (o.getMetaData().getColumnName(i).equals("country")) { geocode.getAddress().setCountryCode(value); } } try { grt.getReverseGeocodedLocation().add(geocode); } catch (Throwable t) { LOG.error("Error extracting data from database.", t); } } res_.add(grt); res.add(res_); return res; } }; return hibernateTemplate.executeWithNativeSession(action); }
From source file:org.kuali.coeus.common.impl.krms.StoredFunctionDao.java
public String executeFunction(final String functionName, final List<Object> paramValues) { final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); String result = jdbcTemplate.execute(new CallableStatementCreator() { @Override//from ww w. j av a 2 s . c om public CallableStatement createCallableStatement(Connection con) throws SQLException { String paramSyntaxString = ""; int paramCount = paramValues.size(); for (int i = 0; i < paramCount; i++) { if (i == 0) paramSyntaxString += "(?"; else if (i == paramCount - 1) paramSyntaxString += ",?)"; else paramSyntaxString += ",?"; } if (paramCount == 1) paramSyntaxString += ")"; CallableStatement cs = con.prepareCall("{ ? = call " + functionName + paramSyntaxString + "}"); cs.registerOutParameter(1, Types.VARCHAR); for (int i = 0; i < paramValues.size(); i++) { cs.setObject(i + 2, paramValues.get(i)); } return cs; } }, new CallableStatementCallback<String>() { @Override public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { cs.execute(); String result = cs.getString(1); return result; } }); LOG.debug(functionName + " result: " + result); return result; }
From source file:org.rimudb.storedproc.StoredProcedure.java
public List execute(boolean autoCommit) throws RimuDBException { ArrayList resultList = null;// w w w . j a v a 2 s.c o m Connection conn = null; int statID = 0; CallableStatement stmt = null; boolean originalAutoCommit = false; try { // Get a connection conn = database.getDatabaseConnection(); originalAutoCommit = conn.getAutoCommit(); conn.setAutoCommit(autoCommit); String sql = createSQL(); // Get the statistic ID int loggingType = database.getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } // Prepare the call stmt = conn.prepareCall(sql); int parameterCount = 0; if (getReturnType() != NONE) { parameterCount++; stmt.registerOutParameter(parameterCount, getReturnType()); } // Assign parameters for (int i = 0; i < parameterList.size(); i++) { parameterCount++; // If the parameter is an IN type parameter then if (parameterList.get(i) instanceof StoredProcINParameter || parameterList.get(i) instanceof StoredProcINOUTParameter) { stmt.setObject(parameterCount, parameterList.get(i).getValue()); } // If the parameter is an OUT type if (parameterList.get(i) instanceof StoredProcOUTParameter) { stmt.registerOutParameter(parameterCount, ((StoredProcOUTParameter) parameterList.get(i)).getSqlType()); } // If the parameter is an INOUT type if (parameterList.get(i) instanceof StoredProcINOUTParameter) { stmt.registerOutParameter(parameterCount, ((StoredProcINOUTParameter) parameterList.get(i)).getSqlType()); } } if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); // Execute the call boolean result = stmt.execute(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); // If we got a result set if (result) { // Create the empty list to contain the rows resultList = new ArrayList(); // While there is a result set to be retrieved while (result) { // Get the result set ResultSet rs = stmt.getResultSet(); // Process the result set List list = processResultSet(rs); // And close the result set rs.close(); // Add the result set to the full list if (list != null) { resultList.addAll(list); } // Check for more results result = stmt.getMoreResults(); } } // If there was a return value if (getReturnType() != NONE) { Object value = stmt.getObject(1); if (value instanceof ResultSet) { resultList = new ArrayList(); ResultSet rs = (ResultSet) value; List list = processResultSet(rs); rs.close(); if (list != null) { resultList.addAll(list); } } else { returnValue = value; } } // Assign the out values for (int i = 0; i < parameterList.size(); i++) { // If the parameter is an OUT type if (parameterList.get(i) instanceof StoredProcOUTParameter || parameterList.get(i) instanceof StoredProcINOUTParameter) { Object value = stmt.getObject(i + 1); // Don't save ResultSets in the parameters if (!(value instanceof ResultSet)) { parameterList.get(i).setValue(value); } } } if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, database.getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, database.getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } return resultList; } catch (SQLException e) { throw new RimuDBException(e); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { // Don't care about a failure here } } if (conn != null) { try { conn.setAutoCommit(originalAutoCommit); conn.close(); } catch (SQLException e) { // Don't care about a failure here } } } }
From source file:org.xsystem.sql2.dml.DmlCommand.java
void setParms(CallableStatement cs, List<DmlParams> paramsSpec, Map<String, Object> values) throws SQLException { int i = 0;/*from w w w .ja v a 2 s .com*/ Connection con = cs.getConnection(); AbstactNativeHelper nativeHelper = nativeHelperFactory(con); for (DmlParams row : paramsSpec) { String paramName = row.getName(); Integer jdbcType = row.getJdbcType(); String objectType = row.getObjectType(); Object value = values.get(paramName); boolean in = row.isIn(); boolean out = row.isOut(); i = i + 1; if (in) { if (namedParams) { if (jdbcType == Types.OTHER) { cs.setObject(paramName, value); } else { value = setValue(value, jdbcType, objectType, con, nativeHelper); if (value != null) { value = convert(value, jdbcType); cs.setObject(paramName, value, jdbcType); } else { cs.setNull(paramName, jdbcType); } } } else { if (jdbcType == Types.OTHER) { cs.setObject(i, value); } else { if (jdbcType == JdbcTypeMapping.json) { nativeHelper.setJSONPARAM(cs, i, value); } else { value = setValue(value, jdbcType, objectType, con, nativeHelper); if (value != null) { value = convert(value, jdbcType); cs.setObject(i, value, jdbcType); } else { cs.setNull(i, jdbcType); } } } } } if (out) { if (namedParams) { if (jdbcType == Types.ARRAY || jdbcType == Types.STRUCT) { cs.registerOutParameter(paramName, jdbcType, objectType); } else { cs.registerOutParameter(paramName, jdbcType); } } else { if (jdbcType == Types.ARRAY || jdbcType == Types.STRUCT) { cs.registerOutParameter(i, jdbcType, objectType); } else { cs.registerOutParameter(i, jdbcType); } } } } }
From source file:pingpong.db.DBAccess.java
/** * ResultSet? ? ? ./*from w w w.j a va 2s . c o m*/ * @param sql * @param args * @return procedure? rtn_cod, rtn_msg String[] */ public String[] executeProcedure(String sql, Object args[]) throws SQLException { String result[] = { "", "" }; Connection local_con = null; CallableStatement cstmt = null; ResultSet rs = null; int idxParam = (args == null) ? 0 : args.length; log.debug(sql); if (args != null) { for (int i = 0; i < args.length; i++) { log.debug("args" + i + ":" + args[i]); } } try { if (m_con != null) { cstmt = m_con.prepareCall(sql); } else { local_con = getConnection(); cstmt = local_con.prepareCall(sql); } if (args != null) { for (int i = 0; i < args.length; i++) { if (args[i] instanceof Null) { cstmt.setNull(i + 1, ((Null) args[i]).type); } else { cstmt.setObject(i + 1, args[i]); } } } cstmt.setString(idxParam + 1, ""); cstmt.setString(idxParam + 2, ""); cstmt.registerOutParameter(idxParam + 1, Types.VARCHAR); cstmt.registerOutParameter(idxParam + 2, Types.VARCHAR); cstmt.execute(); result[0] = cstmt.getString(idxParam + 1); result[1] = cstmt.getString(idxParam + 2); } catch (SQLException e) { state = e.getErrorCode(); sqlState = e.getSQLState(); message = e.getMessage(); throw e; } catch (Exception e) { message = e.getMessage(); e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (Exception e) { } } if (cstmt != null) { try { cstmt.close(); } catch (Exception e) { } } if (local_con != null) { try { local_con.close(); } catch (Exception e) { } } } return result; }