List of usage examples for java.sql CallableStatement setObject
void setObject(String parameterName, Object x) throws SQLException;
From source file:com.ibm.research.rdf.store.runtime.service.sql.UpdateHelper.java
private static String executeCall(Connection conn, String sql, int retPid, Object... params) { CallableStatement stmt = null; String ret = null;//w ww . j a va 2 s.c o m try { conn.setAutoCommit(false); } catch (SQLException ex) { log.error(ex); ex.printStackTrace(); System.out.println(ex.getLocalizedMessage()); return ret; } try { stmt = conn.prepareCall(sql); int i = 1; for (Object o : params) { stmt.setObject(i, o); i++; } stmt.registerOutParameter(retPid, Types.VARCHAR); stmt.execute(); ret = stmt.getString(retPid); conn.commit(); } catch (SQLException e) { // log.error(e); // e.printStackTrace(); // System.out.println(e.getLocalizedMessage()); ret = null; try { conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block log.error(e1); e1.printStackTrace(); System.out.println(e1.getLocalizedMessage()); ret = null; } } finally { closeSQLObjects(stmt, null); } try { conn.setAutoCommit(true); } catch (SQLException ex) { log.error(ex); ex.printStackTrace(); System.out.println(ex.getLocalizedMessage()); ret = null; } return ret; }
From source file:ips1ap101.lib.core.db.util.DB.java
public static CallableStatement prepareCall(Connection connection, String sql, Object[] args, EnumTipoResultadoSQL resultType, EnumTipoDatoSQL dataType) { CallableStatement callableStatement; if (connection != null && sql != null) { try {/*from ww w .j a v a2 s.com*/ callableStatement = connection.prepareCall(sql); int n = args == null ? 0 : args.length; if (n > 0) { for (int i = 0; i < n; i++) { if (args[i] == null) { // callableStatement.setNull(i + 1, java.sql.Types.OTHER); callableStatement.setNull(i + 1, java.sql.Types.NULL); } else if (args[i] instanceof EnumTipoDatoSQL) { EnumTipoDatoSQL tipoDatoSQL = (EnumTipoDatoSQL) args[i]; callableStatement.setNull(i + 1, tipoDatoSQL.intValue()); } else { callableStatement.setObject(i + 1, args[i]); } } } if (EnumTipoResultadoSQL.SIMPLE.equals(resultType) && dataType != null) { callableStatement.registerOutParameter(n + 1, dataType.intValue()); } return callableStatement; } catch (SQLException ex) { Bitacora.logFatal(ex); } } return null; }
From source file:org.gofleet.openLS.ddbb.dao.postgis.PostGisHBGeoCodingDAO.java
@Transactional(readOnly = true) public List<AbstractResponseParametersType> reverseGeocode(final ReverseGeocodeRequestType param) { HibernateCallback<List<AbstractResponseParametersType>> action = new HibernateCallback<List<AbstractResponseParametersType>>() { public List<AbstractResponseParametersType> doInHibernate(Session session) throws HibernateException, SQLException { 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 w w w .j ava 2s. c om*/ 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); return res_; } }; return hibernateTemplate.executeWithNativeSession(action); }
From source file:com.aw.core.dao.DAOSql.java
/** * Call example/*from w w w . j av a 2 s .co m*/ * execSqlFunction(sqlUpdSqldoActor, Types.NUMERIC, new Object[]{"dss"}) */ public boolean execSqlProcedure(String sql, Object[] sqlParams) { try { CallableStatement cstmt = getHibernateConnection().prepareCall(sql); if (sqlParams != null) for (int i = 0; i < sqlParams.length; i++) { cstmt.setObject(i + 1, sqlParams[i]); } logger.debug("SQL Exec:" + buildSQL(sql, sqlParams)); boolean result = cstmt.execute(); cstmt.close(); return result; } catch (SQLException e) { logger.error("SQL:" + buildSQL(sql, sqlParams), e); throw AWBusinessException.wrapUnhandledException(logger, e); } }
From source file:com.jbrisbin.vpc.jobsched.sql.SqlMessageHandler.java
public SqlMessage handleMessage(final SqlMessage msg) throws Exception { log.debug("handling message: " + msg.toString()); DataSource ds = appCtx.getBean(msg.getDatasource(), DataSource.class); JdbcTemplate tmpl = new JdbcTemplate(ds); String sql = msg.getSql();/* w w w .j a v a 2 s .c om*/ CallableStatementCreator stmtCreator = null; CallableStatementCallback<SqlMessage> callback = null; if (sql.startsWith("plugin:")) { // Use a plugin to get the sql String pluginName = (sql.contains("?") ? sql.substring(7, sql.indexOf('?')) : sql.substring(7)); final Plugin plugin = groovyPluginManager.getPlugin(pluginName); Map<String, Object> vars = new LinkedHashMap<String, Object>(); vars.put("message", msg); vars.put("datasource", ds); vars.put("listen", groovyClosureFactory.createListenClosure(msg)); vars.put("mapreduce", groovyClosureFactory.createMapReduceClosure(msg)); plugin.setContext(vars); // Execute this plugin plugin.run(); Object o = plugin.get("sql"); if (null != o && o instanceof Closure) { sql = ((Closure) o).call(msg).toString(); } else if (o instanceof String || o instanceof GString) { sql = o.toString(); } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to SQL statement."); } msg.setSql(sql); o = plugin.get("statementCreator"); if (null != o && o instanceof Closure) { stmtCreator = new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { Object obj = ((Closure) plugin.get("statementCreator")).call(new Object[] { con, msg }); log.debug("from plugin statementCreator: " + String.valueOf(obj)); return (CallableStatement) obj; } }; } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to CallableStatementCreator. Define a closure named 'statementCreator' in your plugin."); } o = plugin.get("callback"); if (null != o && o instanceof Closure) { callback = new CallableStatementCallback<SqlMessage>() { public SqlMessage doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { Object obj = ((Closure) plugin.get("callback")).call(new Object[] { cs, msg }); log.debug("from plugin callback: " + String.valueOf(obj)); return (SqlMessage) obj; } }; } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to CallableStatementCallback. Define a closure named 'callback' in your plugin."); } } else { stmtCreator = new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection connection) throws SQLException { CallableStatement stmt = connection.prepareCall(msg.getSql()); List<Object> params = msg.getParams(); if (null != params) { int index = 1; for (Object obj : params) { stmt.setObject(index++, obj); } } return stmt; } }; callback = new CallableStatementCallback<SqlMessage>() { public SqlMessage doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException { if (null == msg.getResults().getData()) { msg.getResults().setData(new ArrayList<List<Object>>()); } if (callableStatement.execute()) { ResultSet results = callableStatement.getResultSet(); // Pull out column names ResultSetMetaData meta = results.getMetaData(); String[] columns = new String[meta.getColumnCount()]; for (int i = 1; i <= meta.getColumnCount(); i++) { columns[i - 1] = meta.getColumnName(i); } msg.getResults().getColumnNames().addAll(Arrays.asList(columns)); int total = 0; while (results.next()) { List<Object> row = new ArrayList<Object>(columns.length); for (int i = 1; i <= columns.length; i++) { row.add(results.getObject(i)); } msg.getResults().getData().add(row); total++; } msg.getResults().setTotalRows(total); } else { msg.getResults().getColumnNames().add("updateCount"); msg.getResults().setTotalRows(1); List<Object> updCnt = new ArrayList<Object>(1); updCnt.add(callableStatement.getUpdateCount()); msg.getResults().getData().add(updCnt); } return msg; } }; } try { tmpl.setExceptionTranslator(appCtx.getBean(SQLExceptionTranslator.class)); } catch (NoSuchBeanDefinitionException notfound) { // IGNORED } if (null != stmtCreator && null != callback) { try { tmpl.execute(stmtCreator, callback); } catch (Throwable t) { log.error(t.getMessage(), t); List<String> errors = new ArrayList<String>(); errors.add(t.getMessage()); Throwable cause = t.getCause(); if (null != cause) { do { errors.add(cause.getMessage()); } while (null != (cause = cause.getCause())); } msg.getResults().setErrors(errors); } } else { log.warn("CallableStatementCreator and/or CallableStatementCallback where empty. " + "Make sure your plugin provides these under 'statementCreator' and 'callback' respectively."); } return msg; }
From source file:com.aw.core.dao.DAOSql.java
/** * Call example// ww w .ja v a 2s . co m * execSqlFunction(sqlUpdSqldoActor, Types.NUMERIC, new Object[]{"dss"}) */ public Object execSqlFunction(String sql, int returnSqlType, Object[] sqlParams) { try { CallableStatement cstmt = getHibernateConnection().prepareCall(sql); cstmt.registerOutParameter(1, returnSqlType); if (sqlParams != null) for (int i = 0; i < sqlParams.length; i++) { cstmt.setObject(i + 2, sqlParams[i]); } logger.debug("SQL Exec:" + buildSQL(sql, sqlParams)); cstmt.execute(); Object returnValue = cstmt.getObject(1); cstmt.close(); return returnValue; } catch (SQLException e) { logger.error("SQL:" + buildSQL(sql, sqlParams), e); throw AWBusinessException.wrapUnhandledException(logger, e); } }
From source file:common.dao.impl.BaseDAOImpl.java
/** * SQL(?)//from w w w. ja va2 s .c om * @param queryString * @param params */ public void executeVoidProcedureSql(final String queryString, final Object[] params) throws Exception { Session session = sessionFactory.getCurrentSession(); session.doWork(new Work() { public void execute(Connection conn) throws SQLException { ResultSet rs = null; CallableStatement call = conn.prepareCall("{" + queryString + "}"); if (null != params) { for (int i = 0; i < params.length; i++) { call.setObject(i + 1, params[i]); } } rs = call.executeQuery(); call.close(); rs.close(); } }); }
From source file:common.dao.impl.BaseDAOImpl.java
public List<Map<String, Object>> callQueryProcedure(final String sql, final Object[] params) { logger.debug("start to call procedure" + sql + ", params is " + params); final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); getCurrentSession().doWork(new Work() { public void execute(Connection conn) throws SQLException { CallableStatement cs = conn.prepareCall(sql); if (params != null) { logger.debug("params is not null it's members is " + Arrays.asList(params)); for (int i = 0; i < params.length; i++) { cs.setObject(i + 1, params[i]); }/*from w ww . j a va 2s .c o m*/ } else logger.debug("params is null"); ResultSet rs = cs.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int colCount = metaData.getColumnCount(); while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 1; i <= colCount; i++) { String colName = metaData.getColumnName(i); map.put(colName, rs.getObject(colName)); } result.add(map); } rs.close(); cs.close(); } }); return result; }
From source file:common.dao.impl.BaseDAOImpl.java
public Integer callUpdateProcedure(final String sql, final Object[] params) { logger.debug("start to call procedure" + sql + ", params is " + params); final ArrayList<Integer> returnHitCount = new ArrayList<Integer>(); getCurrentSession().doWork(new Work() { public void execute(Connection conn) throws SQLException { try { CallableStatement cs = conn.prepareCall(sql); if (params != null) { logger.debug("params is not null it's members is " + Arrays.asList(params)); for (int i = 0; i < params.length; i++) { cs.setObject(i + 1, params[i]); }/*from ww w . j ava 2 s. c om*/ } else logger.debug("params is null"); int hitCount = cs.executeUpdate(); cs.close(); logger.debug("call procedure ended, hitted record counts is " + hitCount); returnHitCount.add(new Integer(hitCount)); } catch (Exception e) { throw new RuntimeException(e); } } }); return returnHitCount.get(0); }
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 2s.co 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); } }