@Override public Map<String, ExecutionContext> partition(int gridSize) { int min = jdbcTemplate.queryForObject("SELECT MIN(" + column + ") from " + table, Integer.class); int max = jdbcTemplate.queryForObject("SELECT MAX(" + column + ") from " + table, Integer.class); int targetSize = (max - min) / gridSize; Map<String, ExecutionContext> result = new HashMap<String, ExecutionContext>(); int number = 0; int start = min; int end = start + targetSize - 1; while (start <= max) { ExecutionContext value = new ExecutionContext(); result.put("partition" + number, value); if (end >= max) { end = max; } value.putInt("minValue", start); value.putInt("maxValue", end); start += targetSize; end += targetSize; number++; } return result; }
private void checkOutputTable(int before) { @SuppressWarnings("serial") final List<Trade> trades = new ArrayList<Trade>() { { add(new Trade("UK21341EAH41", 211, new BigDecimal("31.11"), "customer1")); add(new Trade("UK21341EAH42", 212, new BigDecimal("32.11"), "customer2")); add(new Trade("UK21341EAH43", 213, new BigDecimal("33.11"), "customer3")); add(new Trade("UK21341EAH44", 214, new BigDecimal("34.11"), "customer4")); add(new Trade("UK21341EAH45", 215, new BigDecimal("35.11"), "customer5")); } }; int after = jdbcTemplate.queryForObject("SELECT COUNT(*) from TRADE", Integer.class); assertEquals(before + 5, after); jdbcTemplate.query( GET_TRADES, new RowCallbackHandler() { private int activeRow = 0; @Override public void processRow(ResultSet rs) throws SQLException { Trade trade = trades.get(activeRow++); assertEquals(trade.getIsin(), rs.getString(1)); assertEquals(trade.getQuantity(), rs.getLong(2)); assertEquals(trade.getPrice(), rs.getBigDecimal(3)); assertEquals(trade.getCustomer(), rs.getString(4)); } }); }
public void removeMessageGroup(Object groupId) { final String groupKey = getKey(groupId); for (UUID messageIds : this.getMessageIdsForGroup(groupId)) { this.removeMessage(messageIds); } jdbcTemplate.update( getQuery(Query.REMOVE_GROUP_TO_MESSAGE_JOIN), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Removing relationships for the group with group key=" + groupKey); } ps.setString(1, groupKey); ps.setString(2, region); } }); jdbcTemplate.update( getQuery(Query.DELETE_MESSAGE_GROUP), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Marking messages with group key=" + groupKey); } ps.setString(1, groupKey); ps.setString(2, region); } }); }
@Test public void testJobLaunch() throws Exception { jdbcTemplate.update("DELETE from TRADE"); int before = jdbcTemplate.queryForObject("SELECT COUNT(*) from TRADE", Integer.class); jobLauncherTestUtils.launchJob(); checkOutputFile("target/test-outputs/CustomerReport1.txt"); checkOutputFile("target/test-outputs/CustomerReport2.txt"); checkOutputTable(before); }
@Test public void testDataSourceInitialized() throws Exception { EnvironmentTestUtils.addEnvironment(this.context, "spring.datasource.initialize:true"); this.context.register( DataSourceAutoConfiguration.class, PropertyPlaceholderAutoConfiguration.class); this.context.refresh(); DataSource dataSource = this.context.getBean(DataSource.class); assertThat(dataSource instanceof org.apache.tomcat.jdbc.pool.DataSource).isTrue(); assertThat(dataSource).isNotNull(); JdbcOperations template = new JdbcTemplate(dataSource); assertThat(template.queryForObject("SELECT COUNT(*) from BAR", Integer.class)).isEqualTo(1); }
@Test public void testDataSourceInitialized() throws Exception { this.context.register( DataSourceAutoConfiguration.class, PropertyPlaceholderAutoConfiguration.class); this.context.refresh(); DataSource dataSource = this.context.getBean(DataSource.class); assertTrue(dataSource instanceof org.apache.tomcat.jdbc.pool.DataSource); assertNotNull(dataSource); JdbcOperations template = new JdbcTemplate(dataSource); assertEquals( new Integer(0), template.queryForObject("SELECT COUNT(*) from BAR", Integer.class)); }
public MessageGroup getMessageGroup(Object groupId) { String key = getKey(groupId); final AtomicReference<Date> createDate = new AtomicReference<Date>(); final AtomicReference<Date> updateDate = new AtomicReference<Date>(); final AtomicReference<Boolean> completeFlag = new AtomicReference<Boolean>(); final AtomicReference<Integer> lastReleasedSequenceRef = new AtomicReference<Integer>(); List<Message<?>> messages = jdbcTemplate.query( getQuery(Query.LIST_MESSAGES_BY_GROUP_KEY), new Object[] {key, region}, mapper); if (messages.size() == 0) { return new SimpleMessageGroup(groupId); } jdbcTemplate.query( getQuery(Query.GET_GROUP_INFO), new Object[] {key, region}, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { updateDate.set(rs.getTimestamp("UPDATED_DATE")); createDate.set(rs.getTimestamp("CREATED_DATE")); completeFlag.set(rs.getInt("COMPLETE") > 0); lastReleasedSequenceRef.set(rs.getInt("LAST_RELEASED_SEQUENCE")); } }); if (createDate.get() == null && updateDate.get() == null) { if (logger.isWarnEnabled()) { for (Message<?> message : messages) { logger.warn("Missing group row for message id: " + message.getHeaders().getId()); } } return new SimpleMessageGroup(groupId); } long timestamp = createDate.get().getTime(); boolean complete = completeFlag.get().booleanValue(); SimpleMessageGroup messageGroup = new SimpleMessageGroup(messages, groupId, timestamp, complete); messageGroup.setLastModified(updateDate.get().getTime()); int lastReleasedSequenceNumber = lastReleasedSequenceRef.get(); messageGroup.setLastReleasedMessageSequenceNumber(lastReleasedSequenceNumber); return messageGroup; }
public void testPriority() { adPlaceUID = Long.toString(new Date().getTime()) + Double.toString(Math.random()).split("\\.")[1] + "test"; AdPlace adPlace = new AdPlace(); adPlace.setAdPlaceName("PriorityTestAdPlace"); adPlace.setUid(adPlaceUID); List<AdPlace> adPlaces = new ArrayList<AdPlace>(); adPlaces.add(adPlace); try { adPlaceDAO.saveOrUpdateAdPlaces(adPlaces); List<Integer> banners = new ArrayList<Integer>(); for (int i = 0; i < 5; i++) { saveBannerForSomePriority(1, 10); // 5 banners with 1 priority and 10 time-dayViewsLimit } for (int i = 0; i < 5; i++) { saveBannerForSomePriority(2, 20); // 5 banners with 2 priority and 20 time-dayViewsLimit } for (int i = 0; i < 10; i++) { saveBannerForSomePriority(3, 5); // 10 banners with 3 priority and 5 time-dayViewsLimit } for (int i = 0; i < 50; i++) { // 5 banners * 10 banners in day // System.out.println(i); assertTrue(getBannerForSomePriority(1, banners)); } for (int i = 0; i < 100; i++) { // 5 banners * 20 banners in day // System.out.println(i); assertTrue(getBannerForSomePriority(2, banners)); } for (int i = 0; i < 50; i++) { // 10 banners * 5 banners in day // System.out.println(i); assertTrue(getBannerForSomePriority(3, banners)); } } catch (Exception e) { e.printStackTrace(); fail(e.getMessage()); } finally { jdbcTemplate.execute("DELETE FROM banner"); jdbcTemplate.execute("DELETE FROM ad_place"); jdbcTemplate.execute("DELETE FROM aggregate_reports"); jdbcTemplate.execute("DELETE FROM ad_events_log"); } }
public Iterator<MessageGroup> iterator() { final Iterator<String> iterator = jdbcTemplate .query( getQuery(Query.LIST_GROUP_KEYS), new Object[] {region}, new SingleColumnRowMapper<String>()) .iterator(); return new Iterator<MessageGroup>() { public boolean hasNext() { return iterator.hasNext(); } public MessageGroup next() { return getMessageGroup(iterator.next()); } public void remove() { throw new UnsupportedOperationException("Cannot remove MessageGroup from this iterator."); } }; }
@Override public ProcessIndicatorItemWrapper<T> read() { if (!initialized) { throw new ReaderNotOpenException("Reader must be open before it can be used."); } Long id = null; synchronized (lock) { if (keys.hasNext()) { id = keys.next(); } } logger.debug("Retrieved key from list: " + id); if (id == null) { return null; } @SuppressWarnings("unchecked") T result = (T) jdbcTemplate.queryForObject( "SELECT VALUE FROM BATCH_STAGING WHERE ID=?", new RowMapper<Object>() { @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { byte[] blob = rs.getBytes(1); return SerializationUtils.deserialize(blob); } }, id); return new ProcessIndicatorItemWrapper<T>(id, result); }
public MessageGroup addMessageToGroup(Object groupId, Message<?> message) { final String groupKey = getKey(groupId); final String messageId = getKey(message.getHeaders().getId()); boolean groupNotExist = jdbcTemplate.queryForInt(this.getQuery(Query.GROUP_EXISTS), groupKey, region) < 1; final Timestamp updatedDate = new Timestamp(System.currentTimeMillis()); final Timestamp createdDate = groupNotExist ? updatedDate : jdbcTemplate.queryForObject( getQuery(Query.GET_GROUP_CREATED_DATE), new Object[] {groupKey, region}, Timestamp.class); if (groupNotExist) { try { this.doCreateMessageGroup(groupKey, createdDate); } catch (DuplicateKeyException e) { logger.warn("Lost race to create group; attempting update instead", e); this.doUpdateMessageGroup(groupKey, updatedDate); } } else { this.doUpdateMessageGroup(groupKey, updatedDate); } this.addMessage(message); jdbcTemplate.update( getQuery(Query.CREATE_GROUP_TO_MESSAGE), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug( "Inserting message with id key=" + messageId + " and created date=" + createdDate); } ps.setString(1, groupKey); ps.setString(2, messageId); ps.setString(3, region); } }); return getMessageGroup(groupId); }
public Message<?> getMessage(UUID id) { List<Message<?>> list = jdbcTemplate.query(getQuery(Query.GET_MESSAGE), new Object[] {getKey(id), region}, mapper); if (list.isEmpty()) { return null; } return list.get(0); }
@Test public void testDataSourceInitializedWithExplicitScript() throws Exception { this.context.register( DataSourceAutoConfiguration.class, PropertyPlaceholderAutoConfiguration.class); Map<String, Object> map = new HashMap<String, Object>(); map.put( DataSourceAutoConfiguration.CONFIGURATION_PREFIX + ".schema", ClassUtils.addResourcePathToPackagePath(getClass(), "schema.sql")); this.context.getEnvironment().getPropertySources().addFirst(new MapPropertySource("test", map)); this.context.refresh(); DataSource dataSource = this.context.getBean(DataSource.class); assertTrue(dataSource instanceof org.apache.tomcat.jdbc.pool.DataSource); assertNotNull(dataSource); JdbcOperations template = new JdbcTemplate(dataSource); assertEquals( new Integer(0), template.queryForObject("SELECT COUNT(*) from FOO", Integer.class)); }
@Test public void testDataSourceInitializedWithExplicitScript() throws Exception { this.context.register( DataSourceAutoConfiguration.class, PropertyPlaceholderAutoConfiguration.class); EnvironmentTestUtils.addEnvironment( this.context, "spring.datasource.initialize:true", "spring.datasource.schema:" + ClassUtils.addResourcePathToPackagePath(getClass(), "schema.sql"), "spring.datasource.data:" + ClassUtils.addResourcePathToPackagePath(getClass(), "data.sql")); this.context.refresh(); DataSource dataSource = this.context.getBean(DataSource.class); assertThat(dataSource instanceof org.apache.tomcat.jdbc.pool.DataSource).isTrue(); assertThat(dataSource).isNotNull(); JdbcOperations template = new JdbcTemplate(dataSource); assertThat(template.queryForObject("SELECT COUNT(*) from FOO", Integer.class)).isEqualTo(1); }
@Override @Transactional(readOnly = true) public List<Event> findForUser(int userId) { return jdbcOperations.query( EVENT_QUERY + " and (e.owner = ? or e.attendee = ?) order by e.id", EVENT_ROW_MAPPER, userId, userId); }
private void insert(SUser u) { jdbcOperations.update( INSERT_SUSER, u.getId(), u.getPassword(), u.getFirstName(), u.getLastName(), u.getEmail(), u.getCreateDT()); }
private void update(SUser u) { jdbcOperations.update( UPDATE_SUSER, u.getPassword(), u.getFirstName(), u.getLastName(), u.getEmail(), u.getCreateDT(), u.getId()); }
/** * Delete all rows from the specified tables. * * @param jdbcTemplate the SimpleJdbcTemplate with which to perform JDBC operations * @param tableNames the names of the tables from which to delete * @return the total number of rows deleted from all specified tables */ public static int deleteFromTables(JdbcOperations jdbcTemplate, String... tableNames) { int totalRowCount = 0; for (String tableName : tableNames) { int rowCount = jdbcTemplate.update("DELETE FROM " + tableName); totalRowCount += rowCount; if (LOG.isInfoEnabled()) { LOG.info("Deleted " + rowCount + " rows from table " + tableName); } } return totalRowCount; }
/** * This method executes a call to the DB to get the oldest Message in the MessageGroup Override * this method if need to. For example if you DB supports advanced function such as FIRST etc. * * @param groupIdKey String representation of message group ID * @return a message; could be null if query produced no Messages */ protected Message<?> doPollForMessage(String groupIdKey) { List<Message<?>> messages = jdbcTemplate.query( getQuery(Query.POLL_FROM_GROUP), new Object[] {groupIdKey, region, groupIdKey, region}, mapper); Assert.isTrue(messages.size() == 0 || messages.size() == 1); if (messages.size() > 0) { return messages.get(0); } return null; }
@Test public void testInitializationDisabled() throws Exception { this.context.register( DataSourceAutoConfiguration.class, PropertyPlaceholderAutoConfiguration.class); this.context.refresh(); DataSource dataSource = this.context.getBean(DataSource.class); this.context.publishEvent(new DataSourceInitializedEvent(dataSource)); assertThat(dataSource instanceof org.apache.tomcat.jdbc.pool.DataSource).isTrue(); assertThat(dataSource).isNotNull(); JdbcOperations template = new JdbcTemplate(dataSource); try { template.queryForObject("SELECT COUNT(*) from BAR", Integer.class); fail("Query should have failed as BAR table does not exist"); } catch (BadSqlGrammarException ex) { SQLException sqlException = ex.getSQLException(); int expectedCode = -5501; // user lacks privilege or object not found assertThat(sqlException.getErrorCode()).isEqualTo(expectedCode); } }
private void updateMessageGroup(final String groupId) { jdbcTemplate.update( getQuery(Query.UPDATE_GROUP), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Updating MessageGroup: " + groupId); } ps.setTimestamp(1, new Timestamp(System.currentTimeMillis())); ps.setString(2, groupId); ps.setString(3, region); } }); }
private List<Long> retrieveKeys() { synchronized (lock) { return jdbcTemplate.query( "SELECT ID FROM BATCH_STAGING WHERE JOB_ID=? AND PROCESSED=? ORDER BY ID", new RowMapper<Long>() { @Override public Long mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getLong(1); } }, stepExecution.getJobExecution().getJobId(), StagingItemWriter.NEW); } }
public Message<?> removeMessage(UUID id) { Message<?> message = getMessage(id); if (message == null) { return null; } int updated = jdbcTemplate.update( getQuery(Query.DELETE_MESSAGE), new Object[] {getKey(id), region}, new int[] {Types.VARCHAR, Types.VARCHAR}); if (updated != 0) { return message; } return null; }
private List<UUID> getMessageIdsForGroup(Object groupId) { String key = getKey(groupId); final List<UUID> messageIds = new ArrayList<UUID>(); jdbcTemplate.query( getQuery(Query.LIST_MESSAGEIDS_BY_GROUP_KEY), new Object[] {key, region}, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { messageIds.add(UUID.fromString(rs.getString(1))); } }); return messageIds; }
public void completeGroup(Object groupId) { final long updatedDate = System.currentTimeMillis(); final String groupKey = getKey(groupId); jdbcTemplate.update( getQuery(Query.COMPLETE_GROUP), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Completing MessageGroup: " + groupKey); } ps.setTimestamp(1, new Timestamp(updatedDate)); ps.setString(2, groupKey); ps.setString(3, region); } }); }
private void doUpdateMessageGroup(final String groupKey, final Timestamp updatedDate) { jdbcTemplate.update( getQuery(Query.UPDATE_MESSAGE_GROUP), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug( "Updating message group with id key=" + groupKey + " and updated date=" + updatedDate); } ps.setTimestamp(1, updatedDate); ps.setString(2, groupKey); ps.setString(3, region); } }); }
public MessageGroup removeMessageFromGroup(Object groupId, Message<?> messageToRemove) { final String groupKey = getKey(groupId); final String messageId = getKey(messageToRemove.getHeaders().getId()); jdbcTemplate.update( getQuery(Query.REMOVE_MESSAGE_FROM_GROUP), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Removing message from group with group key=" + groupKey); } ps.setString(1, groupKey); ps.setString(2, messageId); ps.setString(3, region); } }); this.removeMessage(messageToRemove.getHeaders().getId()); this.updateMessageGroup(groupKey); return getMessageGroup(groupId); }
@SuppressWarnings({"rawtypes", "unchecked"}) public <T> Message<T> addMessage(final Message<T> message) { if (message.getHeaders().containsKey(SAVED_KEY)) { Message<T> saved = (Message<T>) getMessage(message.getHeaders().getId()); if (saved != null) { if (saved.equals(message)) { return message; } // We need to save it under its own id } } final long createdDate = System.currentTimeMillis(); Message<T> result = MessageBuilder.fromMessage(message) .setHeader(SAVED_KEY, Boolean.TRUE) .setHeader(CREATED_DATE_KEY, new Long(createdDate)) .build(); Map innerMap = (Map) new DirectFieldAccessor(result.getHeaders()).getPropertyValue("headers"); // using reflection to set ID since it is immutable through MessageHeaders innerMap.put(MessageHeaders.ID, message.getHeaders().get(MessageHeaders.ID)); final String messageId = getKey(result.getHeaders().getId()); final byte[] messageBytes = serializer.convert(result); jdbcTemplate.update( getQuery(Query.CREATE_MESSAGE), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Inserting message with id key=" + messageId); } ps.setString(1, messageId); ps.setString(2, region); ps.setTimestamp(3, new Timestamp(createdDate)); lobHandler.getLobCreator().setBlobAsBytes(ps, 4, messageBytes); } }); return result; }
public void setLastReleasedSequenceNumberForGroup(Object groupId, final int sequenceNumber) { Assert.notNull(groupId, "'groupId' must not be null"); final long updatedDate = System.currentTimeMillis(); final String groupKey = getKey(groupId); jdbcTemplate.update( getQuery(Query.UPDATE_LAST_RELEASED_SEQUENCE), new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) { logger.debug( "Updating the sequence number of the last released Message in the MessageGroup: " + groupKey); } ps.setTimestamp(1, new Timestamp(updatedDate)); ps.setInt(2, sequenceNumber); ps.setString(3, groupKey); ps.setString(4, region); } }); this.updateMessageGroup(groupKey); }
public List<SUser> getAll() { List<SUser> ul = jdbcOperations.query(SELECT_ALL, new SUserRowMapper()); return ul; }