List of usage examples for org.springframework.jdbc.core CallableStatementCreator CallableStatementCreator
CallableStatementCreator
From source file:org.marccarre.spring.db.testing.FooDao.java
public List<Foo> getByIdCallableStatement(int id) { final int fooId = id; return jdbcTemplate.execute(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement cs = con.prepareCall("{call GetFoosById(?)}"); cs.setInt(1, fooId);/*from w ww.j av a 2s . c o m*/ return cs; } }, new CallableStatementCallback<List<Foo>>() { public List<Foo> doInCallableStatement(CallableStatement cs) throws SQLException { cs.execute(); List<Foo> foos = new ArrayList<Foo>(); if (cs.getMoreResults()) { ResultSet rs = cs.getResultSet(); FooRowMapper mapper = new FooRowMapper(); int rowIndex = 0; while (rs.next()) { foos.add(mapper.mapRow(rs, rowIndex)); rowIndex++; } } return foos; } }); }
From source file:com.nortal.petit.core.dialect.OracleSqlDialect.java
@SuppressWarnings("unchecked") @Override/*from w w w .j a v a 2s . 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.persistent.cloudninja.dao.impl.KpiValueTempDaoImpl.java
@Override public void executeProcessKpiValues() { try {/*from w ww.ja v a 2 s .c o m*/ jdbcTemplate.call(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement cs = con.prepareCall("{ call ProcessKpiValues() }"); return cs; } }, new ArrayList<SqlParameter>()); } catch (Exception e) { LOGGER.error(e.getMessage(), e); } }
From source file:net.solarnetwork.node.dao.jdbc.derby.DerbyCompressTableJob.java
@Override protected void executeInternal(JobExecutionContext jobContext) throws Exception { if (log.isDebugEnabled()) { log.debug("Compressing Derby table " + schema + '.' + table + " with purgeRows = " + purgeRows + ", defragmentRows = " + defragmentRows + ", truncateEnd = " + truncateEnd); }//ww w . j a va2s . c o m jdbcOperations.execute(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { if (log.isTraceEnabled()) { log.trace("Preparing Derby compress table call [" + COMPRESS_CALL + ']'); } return con.prepareCall(COMPRESS_CALL); } }, new CallableStatementCallback<Object>() { public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { int idx = 1; cs.setString(idx++, schema); cs.setString(idx++, table); cs.setShort(idx++, purgeRows ? (short) 1 : (short) 0); cs.setShort(idx++, defragmentRows ? (short) 1 : (short) 0); cs.setShort(idx++, truncateEnd ? (short) 1 : (short) 0); boolean result = cs.execute(); if (log.isTraceEnabled()) { log.trace("Derby compress table call returned [" + result + ']'); } return null; } }); if (log.isInfoEnabled()) { log.info("Compressed Derby table " + schema + '.' + table); } }
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();//from w ww . ja v a 2s. c o m 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:net.solarnetwork.node.dao.jdbc.derby.DerbyOnlineSyncJob.java
private void executeProcedure(final String procedure) { jdbcOperations.execute(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { log.trace("Calling {} procedure", procedure); return con.prepareCall(procedure); }// www . j ava 2 s . c o m }, new CallableStatementCallback<Object>() { public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { cs.execute(); return null; } }); }
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/* w ww .j av a2s .co m*/ 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.springframework.jdbc.core.JdbcTemplateTests.java
public void testNativeJdbcExtractorInvoked() throws Exception { MockControl ctrlResultSet = MockControl.createControl(ResultSet.class); final ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock(); mockResultSet.close();//from ww w . ja va2 s. co m ctrlResultSet.setVoidCallable(2); MockControl ctrlStatement = MockControl.createControl(Statement.class); final Statement mockStatement = (Statement) ctrlStatement.getMock(); if (debugEnabled) { mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); } mockStatement.close(); ctrlStatement.setVoidCallable(); MockControl ctrlStatement2 = MockControl.createControl(Statement.class); final Statement mockStatement2 = (Statement) ctrlStatement2.getMock(); mockStatement2.executeQuery("my query"); ctrlStatement2.setReturnValue(mockResultSet, 1); MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class); final PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock(); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); MockControl ctrlPreparedStatement2 = MockControl.createControl(PreparedStatement.class); final PreparedStatement mockPreparedStatement2 = (PreparedStatement) ctrlPreparedStatement2.getMock(); mockPreparedStatement2.executeQuery(); ctrlPreparedStatement2.setReturnValue(mockResultSet, 1); MockControl ctrlReturnResultSet = MockControl.createControl(ResultSet.class); final ResultSet mockReturnResultSet = (ResultSet) ctrlReturnResultSet.getMock(); mockReturnResultSet.next(); ctrlReturnResultSet.setReturnValue(false); mockReturnResultSet.close(); ctrlReturnResultSet.setVoidCallable(2); MockControl ctrlCallableStatement = MockControl.createControl(CallableStatement.class); final CallableStatement mockCallableStatement = (CallableStatement) ctrlCallableStatement.getMock(); if (debugEnabled) { mockCallableStatement.getWarnings(); ctrlCallableStatement.setReturnValue(null); } mockCallableStatement.close(); ctrlCallableStatement.setVoidCallable(); MockControl ctrlCallableStatement2 = MockControl.createControl(CallableStatement.class); final CallableStatement mockCallableStatement2 = (CallableStatement) ctrlCallableStatement2.getMock(); mockCallableStatement2.execute(); ctrlCallableStatement2.setReturnValue(true); mockCallableStatement2.getUpdateCount(); ctrlCallableStatement2.setReturnValue(-1); mockCallableStatement2.getResultSet(); ctrlCallableStatement2.setReturnValue(mockReturnResultSet); mockCallableStatement2.getMoreResults(); ctrlCallableStatement2.setReturnValue(false); mockCallableStatement2.getUpdateCount(); ctrlCallableStatement2.setReturnValue(-1); ctrlResultSet.replay(); ctrlStatement.replay(); ctrlStatement2.replay(); ctrlPreparedStatement.replay(); ctrlPreparedStatement2.replay(); ctrlReturnResultSet.replay(); ; ctrlCallableStatement.replay(); ctrlCallableStatement2.replay(); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement, 1); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); template.setNativeJdbcExtractor(new NativeJdbcExtractor() { public boolean isNativeConnectionNecessaryForNativeStatements() { return false; } public boolean isNativeConnectionNecessaryForNativePreparedStatements() { return false; } public boolean isNativeConnectionNecessaryForNativeCallableStatements() { return false; } public Connection getNativeConnection(Connection con) { return con; } public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException { return stmt.getConnection(); } public Statement getNativeStatement(Statement stmt) { assertTrue(stmt == mockStatement); return mockStatement2; } public PreparedStatement getNativePreparedStatement(PreparedStatement ps) { assertTrue(ps == mockPreparedStatement); return mockPreparedStatement2; } public CallableStatement getNativeCallableStatement(CallableStatement cs) { assertTrue(cs == mockCallableStatement); return mockCallableStatement2; } public ResultSet getNativeResultSet(ResultSet rs) { return rs; } }); template.query("my query", new ResultSetExtractor() { public Object extractData(ResultSet rs2) { assertEquals(mockResultSet, rs2); return null; } }); template.query(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection conn) { return mockPreparedStatement; } }, new ResultSetExtractor() { public Object extractData(ResultSet rs2) { assertEquals(mockResultSet, rs2); return null; } }); template.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) { return mockCallableStatement; } }, new ArrayList()); ctrlStatement.verify(); ctrlStatement2.verify(); ctrlPreparedStatement.verify(); ctrlPreparedStatement2.verify(); ctrlCallableStatement.verify(); ctrlCallableStatement2.verify(); }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
public void testExecuteClosed() throws Exception { MockControl ctrlResultSet;//from w w w . j a v a 2 s. c o m ResultSet mockResultSet; MockControl ctrlCallable; CallableStatement mockCallable; ctrlResultSet = MockControl.createControl(ResultSet.class); mockResultSet = (ResultSet) ctrlResultSet.getMock(); mockResultSet.next(); ctrlResultSet.setReturnValue(true); mockResultSet.close(); ctrlResultSet.setVoidCallable(); ctrlCallable = MockControl.createControl(CallableStatement.class); mockCallable = (CallableStatement) ctrlCallable.getMock(); mockCallable.execute(); ctrlCallable.setReturnValue(true); mockCallable.getUpdateCount(); ctrlCallable.setReturnValue(-1); mockCallable.getResultSet(); ctrlCallable.setReturnValue(mockResultSet); mockCallable.close(); ctrlCallable.setVoidCallable(); mockConnection.prepareCall("my query"); ctrlConnection.setReturnValue(mockCallable); ctrlResultSet.replay(); ctrlCallable.replay(); replay(); List params = new ArrayList(); params.add(new SqlReturnResultSet("", new RowCallbackHandler() { public void processRow(ResultSet rs) { throw new InvalidDataAccessApiUsageException(""); } })); JdbcTemplate template = new JdbcTemplate(mockDataSource); try { template.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection conn) throws SQLException { return conn.prepareCall("my query"); } }, params); } catch (InvalidDataAccessApiUsageException idaauex) { // ok } // verify confirms if test is successful by checking if close() called ctrlResultSet.verify(); ctrlCallable.verify(); }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
public void testCaseInsensitiveResultsMap() throws Exception { MockControl ctrlCallable;/* w w w . j av a 2s . co m*/ CallableStatement mockCallable; ctrlCallable = MockControl.createControl(CallableStatement.class); mockCallable = (CallableStatement) ctrlCallable.getMock(); mockCallable.execute(); ctrlCallable.setReturnValue(false); mockCallable.getUpdateCount(); ctrlCallable.setReturnValue(-1); mockCallable.getObject(1); ctrlCallable.setReturnValue("X"); if (debugEnabled) { mockCallable.getWarnings(); ctrlCallable.setReturnValue(null); } mockCallable.close(); ctrlCallable.setVoidCallable(); mockConnection.prepareCall("my query"); ctrlConnection.setReturnValue(mockCallable); ctrlCallable.replay(); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); assertTrue("default should have been NOT case insensitive", !template.isResultsMapCaseInsensitive()); template.setResultsMapCaseInsensitive(true); assertTrue("now it should have been set to case insensitive", template.isResultsMapCaseInsensitive()); List params = new ArrayList(); params.add(new SqlOutParameter("a", 12)); Map out = template.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection conn) throws SQLException { return conn.prepareCall("my query"); } }, params); assertTrue("this should have been a LinkedCaseInsensitiveMap", out instanceof LinkedCaseInsensitiveMap); assertNotNull("we should have gotten the result with upper case", out.get("A")); assertNotNull("we should have gotten the result with lower case", out.get("a")); ctrlCallable.verify(); }