API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 24th, 2018
75
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.74 KB | None | 0 0
raw download clone embed print report
  1. package db.oracle.trueminer;
  2.  
  3. import org.flywaydb.core.api.migration.spring.SpringJdbcMigration;
  4. import org.springframework.beans.BeanUtils;
  5. import org.springframework.jdbc.core.JdbcTemplate;
  6. import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
  7. import org.springframework.jdbc.support.GeneratedKeyHolder;
  8.  
  9. import java.sql.*;
  10. import java.util.List;
  11. import java.util.Objects;
  12.  
  13. import static java.util.Collections.singletonList;
  14. import static java.util.stream.Collectors.toList;
  15. import static org.apache.commons.collections.CollectionUtils.isNotEmpty;
  16. import static org.camunda.bpm.engine.impl.util.CollectionUtil.singletonMap;
  17.  
  18. /**
  19. * @author Pacific GeoTech System
  20. * <p>
  21. * Migration to move Right fields from Independent Right Type groups to Dependent Right Type groups;
  22. * to delete Independent Right Type groups;
  23. * to delete unnecessary Setting Custom Field Configuration
  24. */
  25. public class V1_2_0_666__MoveRightFieldsFromIndependentRightTypeGroupToDependentLol implements SpringJdbcMigration {
  26. //public class V1_2_0_666__MoveRightFieldsFromIndependentRightTypeGroupToDependentLol {
  27.  
  28. private static final String INDEPENDENT_RIGHT_TYPE_GROUPS = "select g.ID_PK, c.JURISDICTION_CODE, g.CUSTOM_GROUP_TYPE from TM_SETTING_CUSTOM_FIELD_CONFIG c " +
  29. "inner join TM_SETTING_CSTM_FLD_GRP g on g.CUSTOM_FIELD_SETTING_CONFIG_ID = c.ID_PK where c.SCREEN_TYPE = 'RIGHT'";
  30.  
  31. private static final String FIELD_TEMPLATES = "select ID_PK, ALIAS, BINDING, DATA_TYPE, DEFAULT_VALUE, DISPLAY, EFFECTIVE_DATE, EXPIRY_DATE, FIELD_LABEL, HAS_CHILD, " +
  32. "ITEM_ORDER, IS_REQUIRED, IS_MULTISELECT, IS_VISIBLE, SCHEMA_CF_TEMP_PARENT_ID, CUSTOM_FIELD_GROUP_ID, NOT_MANAGEABLE from TM_SETTING_CSTM_FLD_TMPLTE " +
  33. "where CUSTOM_FIELD_GROUP_ID = ?";
  34.  
  35. private static final String DEPENDENT_RIGHT_TYPE_GROUPS = "select g.ID_PK from TM_SETTING_CUSTOM_FIELD_CONFIG c " +
  36. "inner join TM_SETTING_CSTM_FLD_GRP g on g.CUSTOM_FIELD_SETTING_CONFIG_ID = c.ID_PK inner join TM_RIGHT_TYPE_CODE t on c.ID_PK = t.CUSTOM_FIELD_SETTING_CONFIG_ID " +
  37. "where c.SCREEN_TYPE = 'RIGHT_TYPE' and g.CUSTOM_GROUP_TYPE = ? and c.JURISDICTION_CODE = ?";
  38.  
  39. private static final String DEPENDENT_DEFAULT_RIGHT_TYPE_GROUPS = "select g.ID_PK from TM_SETTING_CSTM_FLD_GRP g inner join TM_SETTING_CUSTOM_FIELD_CONFIG c " +
  40. "on c.ID_PK = g.CUSTOM_FIELD_SETTING_CONFIG_ID where c.IS_DEFAULT = '1' and c.SCREEN_TYPE = 'RIGHT_TYPE' and g.CUSTOM_GROUP_TYPE = ? and c.JURISDICTION_CODE = ?";
  41.  
  42. private static final String INSERT_TEMPLATE = "Insert into TM_SETTING_CSTM_FLD_TMPLTE (ID_PK, ALIAS, BINDING, DATA_TYPE, DEFAULT_VALUE, DISPLAY, EFFECTIVE_DATE, EXPIRY_DATE, " +
  43. "FIELD_LABEL, HAS_CHILD, ITEM_ORDER, IS_REQUIRED, IS_MULTISELECT, IS_VISIBLE, SCHEMA_CF_TEMP_PARENT_ID, CUSTOM_FIELD_GROUP_ID, NOT_MANAGEABLE)" +
  44. " values (TM_SET_CUS_FIELD_TEMP_SEQ.nextVal,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  45.  
  46. private static final String DELETE_TEMPLATES = "delete from TM_SETTING_CSTM_FLD_TMPLTE where CUSTOM_FIELD_GROUP_ID in (:templateIds)";
  47. private static final String DELETE_GROUPS = "delete from TM_SETTING_CSTM_FLD_GRP where ID_PK in (:groupIds)";
  48. private static final String DELETE_CONFIG = "delete from TM_SETTING_CUSTOM_FIELD_CONFIG where SCREEN_TYPE = 'RIGHT'";
  49.  
  50. @Override
  51. public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
  52. List<IndependentRightTypeGroup> independentRightTypeGroups = findIndependentRightTypeGroups(jdbcTemplate);
  53.  
  54. NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
  55.  
  56. for (IndependentRightTypeGroup independentRightTypeGroup : independentRightTypeGroups) {
  57. List<FieldTemplate> relatedTemplates = findRelatedTemplates(jdbcTemplate, independentRightTypeGroup);
  58. List<Long> dependentRightTypeGroupIds = findGroupIdsToAddTemplates(jdbcTemplate, independentRightTypeGroup);
  59.  
  60. addTemplates(jdbcTemplate, dependentRightTypeGroupIds, relatedTemplates);
  61.  
  62. removeTemplates(namedParameterJdbcTemplate, relatedTemplates);
  63. }
  64.  
  65. removeGroups(namedParameterJdbcTemplate, independentRightTypeGroups);
  66.  
  67. removeConfig(jdbcTemplate);
  68. }
  69.  
  70. private void removeConfig(JdbcTemplate jdbcTemplate) {
  71. jdbcTemplate.update(DELETE_CONFIG);
  72. }
  73.  
  74. private void removeGroups(NamedParameterJdbcTemplate jdbcTemplate, List<IndependentRightTypeGroup> independentRightTypeGroups) {
  75. List<Long> groupIds = independentRightTypeGroups.stream()
  76. .map(IndependentRightTypeGroup::getId)
  77. .collect(toList());
  78.  
  79. if (isNotEmpty(groupIds)) {
  80. jdbcTemplate.update(DELETE_GROUPS, singletonMap("groupIds", groupIds));
  81. }
  82. }
  83.  
  84. private void removeTemplates(NamedParameterJdbcTemplate jdbcTemplate, List<FieldTemplate> relatedTemplates) {
  85. List<Long> templateIds = relatedTemplates.stream()
  86. .map(FieldTemplate::getId)
  87. .collect(toList());
  88.  
  89. if (isNotEmpty(templateIds)) {
  90. jdbcTemplate.update(DELETE_TEMPLATES, singletonMap("templateIds", templateIds));
  91. }
  92. }
  93.  
  94. private void addTemplates(JdbcTemplate jdbcTemplate, List<Long> dependentRightTypeGroupIds, List<FieldTemplate> fieldTemplates) {
  95. for (FieldTemplate template : fieldTemplates) {
  96. addTemplateToGroups(jdbcTemplate, dependentRightTypeGroupIds, template, fieldTemplates);
  97. }
  98. }
  99.  
  100. private void addTemplateToGroups(JdbcTemplate jdbcTemplate, List<Long> dependentRightTypeGroupIds, FieldTemplate template, List<FieldTemplate> fieldTemplates) {
  101. for (Long dependentRightTypeGroupId : dependentRightTypeGroupIds) {
  102. if (hasChildren(template)) {
  103. processTemplateWithChildren(jdbcTemplate, template, dependentRightTypeGroupId, fieldTemplates);
  104. } else {
  105. addTemplate(jdbcTemplate, template, dependentRightTypeGroupId);
  106. }
  107. }
  108. }
  109.  
  110. /**
  111. * Returns inserted template id.
  112. */
  113. private Long addTemplate(JdbcTemplate jdbcTemplate, FieldTemplate template, Long groupId) {
  114. GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
  115.  
  116. jdbcTemplate.update(con -> {
  117. PreparedStatement statement = con.prepareStatement(INSERT_TEMPLATE, new String[]{"ID_PK"});
  118.  
  119. statement.setString(1, template.getAlias());
  120. statement.setString(2, template.getBinding());
  121. statement.setString(3, template.getDataType());
  122. statement.setString(4, template.getDefaultValue());
  123. setNumber(statement, 5, template.getDisplay(), 6);
  124. statement.setDate(6, template.getEffectiveDate());
  125. statement.setDate(7, template.getExpiryDate());
  126. statement.setString(8, template.getFieldLabel());
  127. setNumber(statement, 9, template.getHasChild(), 10);
  128. setNumber(statement, 10, template.getItemOrder(), 11);
  129. setNumber(statement, 11, template.getIsRequired(), 12);
  130. setNumber(statement, 12, template.getIsMultiselect(), 13);
  131. setNumber(statement, 13, template.getIsVisible(), 14);
  132. setNumber(statement, 14, template.getSchemaCfTempParentId(), 15);
  133. statement.setLong(15, groupId);
  134. setNumber(statement, 16, template.getNotManageable(), 17);
  135.  
  136. return statement;
  137. }, keyHolder);
  138.  
  139. return keyHolder.getKey().longValue();
  140. }
  141.  
  142. private <T extends Number> void setNumber(PreparedStatement statement, int targetColumnIndex, T number, int sourceColumnIndex) throws SQLException {
  143. if (number == null) {
  144. statement.setNull(targetColumnIndex, Types.NUMERIC);
  145. } else {
  146. statement.setLong(targetColumnIndex, number.longValue());
  147. }
  148. }
  149.  
  150.  
  151. private void processTemplateWithChildren(JdbcTemplate jdbcTemplate, FieldTemplate template, Long groupId, List<FieldTemplate> fieldTemplates) {
  152. List<FieldTemplate> children = fieldTemplates.stream()
  153. .filter(fieldTemplate -> Objects.equals(fieldTemplate.getSchemaCfTempParentId(), template.getId()))
  154. .collect(toList());
  155.  
  156. Long newTemplateId = addTemplate(jdbcTemplate, template, groupId);
  157.  
  158. for (FieldTemplate child : children) {
  159. FieldTemplate clone = new FieldTemplate();
  160. BeanUtils.copyProperties(child, clone);
  161. clone.setSchemaCfTempParentId(newTemplateId);
  162.  
  163. addTemplateToGroups(jdbcTemplate, singletonList(groupId), clone, fieldTemplates);
  164. }
  165. }
  166.  
  167. private boolean hasChildren(FieldTemplate template) {
  168. return template.getHasChild() == 1;
  169. }
  170.  
  171. private List<Long> findGroupIdsToAddTemplates(JdbcTemplate jdbcTemplate, IndependentRightTypeGroup independentRightTypeGroup) {
  172. List<Long> dependentRightTypeGroupIds = jdbcTemplate.queryForList(DEPENDENT_RIGHT_TYPE_GROUPS, Long.class,
  173. independentRightTypeGroup.getCustomGroupType(), independentRightTypeGroup.getJurisdictionCode());
  174.  
  175. List<Long> dependentDefaultRightTypeGroupIds = jdbcTemplate.queryForList(DEPENDENT_DEFAULT_RIGHT_TYPE_GROUPS, Long.class,
  176. independentRightTypeGroup.getCustomGroupType(), independentRightTypeGroup.getJurisdictionCode());
  177.  
  178. dependentRightTypeGroupIds.addAll(dependentDefaultRightTypeGroupIds);
  179.  
  180. return dependentRightTypeGroupIds;
  181. }
  182.  
  183. private List<FieldTemplate> findRelatedTemplates(JdbcTemplate jdbcTemplate, IndependentRightTypeGroup independentRightTypeGroup) {
  184. return jdbcTemplate.query(FIELD_TEMPLATES, new Long[]{independentRightTypeGroup.getId()}, this::createTemplate);
  185. }
  186.  
  187. private FieldTemplate createTemplate(ResultSet rs, int rowNumber) throws SQLException {
  188. FieldTemplate template = new FieldTemplate();
  189. template.setId(rs.getLong("ID_PK"));
  190. template.setAlias(rs.getString("ALIAS"));
  191. template.setBinding(rs.getString("BINDING"));
  192. template.setDataType(rs.getString("DATA_TYPE"));
  193. template.setDefaultValue(rs.getString("DEFAULT_VALUE"));
  194. template.setDisplay(getNullableInt(rs, "DISPLAY"));
  195. template.setEffectiveDate(rs.getDate("EFFECTIVE_DATE"));
  196. template.setExpiryDate(rs.getDate("EXPIRY_DATE"));
  197. template.setFieldLabel(rs.getString("FIELD_LABEL"));
  198. template.setHasChild(getNullableInt(rs, "HAS_CHILD"));
  199. template.setItemOrder(getNullableInt(rs, "ITEM_ORDER"));
  200. template.setIsRequired(getNullableInt(rs, "IS_REQUIRED"));
  201. template.setIsMultiselect(getNullableInt(rs, "IS_MULTISELECT"));
  202. template.setIsVisible(getNullableInt(rs, "IS_VISIBLE"));
  203. template.setSchemaCfTempParentId(getNullableLong(rs, "SCHEMA_CF_TEMP_PARENT_ID"));
  204. template.setCustomFieldGroupId(getNullableLong(rs, "CUSTOM_FIELD_GROUP_ID"));
  205. template.setNotManageable(getNullableInt(rs, "NOT_MANAGEABLE"));
  206. return template;
  207. }
  208.  
  209. private Integer getNullableInt(ResultSet rs, String columnLabel) throws SQLException {
  210. int intValue = rs.getInt(columnLabel);
  211. return rs.wasNull() ? null : intValue;
  212. }
  213.  
  214. private Long getNullableLong(ResultSet rs, String columnLabel) throws SQLException {
  215. long longValue = rs.getLong(columnLabel);
  216. return rs.wasNull() ? null : longValue;
  217. }
  218.  
  219. private List<IndependentRightTypeGroup> findIndependentRightTypeGroups(JdbcTemplate jdbcTemplate) {
  220. return jdbcTemplate.query(INDEPENDENT_RIGHT_TYPE_GROUPS,
  221. (rs, rowNum) -> new IndependentRightTypeGroup(rs.getLong("ID_PK"), rs.getString("JURISDICTION_CODE"), rs.getString("CUSTOM_GROUP_TYPE")));
  222. }
  223.  
  224.  
  225. private static class IndependentRightTypeGroup {
  226. private Long id;
  227. private String jurisdictionCode;
  228. private String customGroupType;
  229.  
  230. public IndependentRightTypeGroup(Long id, String jurisdictionCode, String customGroupType) {
  231. this.id = id;
  232. this.jurisdictionCode = jurisdictionCode;
  233. this.customGroupType = customGroupType;
  234. }
  235.  
  236. public Long getId() {
  237. return id;
  238. }
  239.  
  240. public void setId(Long id) {
  241. this.id = id;
  242. }
  243.  
  244. public String getJurisdictionCode() {
  245. return jurisdictionCode;
  246. }
  247.  
  248. public void setJurisdictionCode(String jurisdictionCode) {
  249. this.jurisdictionCode = jurisdictionCode;
  250. }
  251.  
  252. public String getCustomGroupType() {
  253. return customGroupType;
  254. }
  255.  
  256. public void setCustomGroupType(String customGroupType) {
  257. this.customGroupType = customGroupType;
  258. }
  259. }
  260.  
  261. private static class FieldTemplate {
  262. private Long id;
  263. private String alias;
  264. private String binding;
  265. private String dataType;
  266. private String defaultValue;
  267. private Integer display;
  268. private Date effectiveDate;
  269. private Date expiryDate;
  270. private String fieldLabel;
  271. private Integer hasChild;
  272. private Integer itemOrder;
  273. private Integer isRequired;
  274. private Integer isMultiselect;
  275. private Integer isVisible;
  276. private Long schemaCfTempParentId;
  277. private Long customFieldGroupId;
  278. private Integer notManageable;
  279.  
  280. public Long getId() {
  281. return id;
  282. }
  283.  
  284. public void setId(Long id) {
  285. this.id = id;
  286. }
  287.  
  288. public String getAlias() {
  289. return alias;
  290. }
  291.  
  292. public void setAlias(String alias) {
  293. this.alias = alias;
  294. }
  295.  
  296. public String getBinding() {
  297. return binding;
  298. }
  299.  
  300. public void setBinding(String binding) {
  301. this.binding = binding;
  302. }
  303.  
  304. public String getDataType() {
  305. return dataType;
  306. }
  307.  
  308. public void setDataType(String dataType) {
  309. this.dataType = dataType;
  310. }
  311.  
  312. public String getDefaultValue() {
  313. return defaultValue;
  314. }
  315.  
  316. public void setDefaultValue(String defaultValue) {
  317. this.defaultValue = defaultValue;
  318. }
  319.  
  320. public Integer getDisplay() {
  321. return display;
  322. }
  323.  
  324. public void setDisplay(Integer display) {
  325. this.display = display;
  326. }
  327.  
  328. public Date getEffectiveDate() {
  329. return effectiveDate;
  330. }
  331.  
  332. public void setEffectiveDate(Date effectiveDate) {
  333. this.effectiveDate = effectiveDate;
  334. }
  335.  
  336. public Date getExpiryDate() {
  337. return expiryDate;
  338. }
  339.  
  340. public void setExpiryDate(Date expiryDate) {
  341. this.expiryDate = expiryDate;
  342. }
  343.  
  344. public String getFieldLabel() {
  345. return fieldLabel;
  346. }
  347.  
  348. public void setFieldLabel(String fieldLabel) {
  349. this.fieldLabel = fieldLabel;
  350. }
  351.  
  352. public Integer getHasChild() {
  353. return hasChild;
  354. }
  355.  
  356. public void setHasChild(Integer hasChild) {
  357. this.hasChild = hasChild;
  358. }
  359.  
  360. public Integer getItemOrder() {
  361. return itemOrder;
  362. }
  363.  
  364. public void setItemOrder(Integer itemOrder) {
  365. this.itemOrder = itemOrder;
  366. }
  367.  
  368. public Integer getIsRequired() {
  369. return isRequired;
  370. }
  371.  
  372. public void setIsRequired(Integer isRequired) {
  373. this.isRequired = isRequired;
  374. }
  375.  
  376. public Integer getIsMultiselect() {
  377. return isMultiselect;
  378. }
  379.  
  380. public void setIsMultiselect(Integer isMultiselect) {
  381. this.isMultiselect = isMultiselect;
  382. }
  383.  
  384. public Integer getIsVisible() {
  385. return isVisible;
  386. }
  387.  
  388. public void setIsVisible(Integer isVisible) {
  389. this.isVisible = isVisible;
  390. }
  391.  
  392. public Long getSchemaCfTempParentId() {
  393. return schemaCfTempParentId;
  394. }
  395.  
  396. public void setSchemaCfTempParentId(Long schemaCfTempParentId) {
  397. this.schemaCfTempParentId = schemaCfTempParentId;
  398. }
  399.  
  400. public Long getCustomFieldGroupId() {
  401. return customFieldGroupId;
  402. }
  403.  
  404. public void setCustomFieldGroupId(Long customFieldGroupId) {
  405. this.customFieldGroupId = customFieldGroupId;
  406. }
  407.  
  408. public Integer getNotManageable() {
  409. return notManageable;
  410. }
  411.  
  412. public void setNotManageable(Integer notManageable) {
  413. this.notManageable = notManageable;
  414. }
  415. }
  416. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!