@Override public Optional<String> getPopulateTempTableStatement() { String sql = "insert into " + getTempTableName() + " " + "select coc.categoryoptioncomboid as cocid, coc.name as cocname, "; for (DataElementCategory category : objects) { sql += "(" + "select co.name from categoryoptioncombos_categoryoptions cocco " + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid " + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid " + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cco.categoryid = " + category.getId() + " " + "limit 1) as " + columnQuote + category.getName() + columnQuote + ", "; sql += "(" + "select co.uid from categoryoptioncombos_categoryoptions cocco " + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid " + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid " + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cco.categoryid = " + category.getId() + " " + "limit 1) as " + columnQuote + category.getUid() + columnQuote + ", "; } sql = TextUtils.removeLastComma(sql) + " "; sql += "from categoryoptioncombo coc "; return Optional.of(sql); }
@Override public void populateCategoryStructure(List<DataElementCategory> categories) { String sql = "insert into " + CreateCategoryTableStatement.TABLE_NAME + " " + "select coc.categoryoptioncomboid as cocid, con.categoryoptioncomboname as cocname, "; for (DataElementCategory category : categories) { sql += "(" + "select co.name from categoryoptioncombos_categoryoptions cocco " + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid " + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid " + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cco.categoryid = " + category.getId() + " " + "limit 1) as " + statementBuilder.columnQuote(category.getName()) + ", "; sql += "(" + "select co.uid from categoryoptioncombos_categoryoptions cocco " + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid " + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid " + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cco.categoryid = " + category.getId() + " " + "limit 1) as " + statementBuilder.columnQuote(category.getUid()) + ", "; } sql = TextUtils.removeLastComma(sql) + " "; sql += "from categoryoptioncombo coc " + "inner join _categoryoptioncomboname con on coc.categoryoptioncomboid = con.categoryoptioncomboid"; log.info("Populate category structure SQL: " + sql); jdbcTemplate.execute(sql); }
@Override public String getCreateTempTableStatement() { String statement = "create table " + getTempTableName() + " (" + "categoryoptioncomboid integer not null, " + "categoryoptioncomboname varchar(230), "; for (DataElementCategory category : objects) { statement += columnQuote + category.getName() + columnQuote + " varchar(230), "; statement += columnQuote + category.getUid() + columnQuote + " character(11), "; } statement += "primary key (categoryoptioncomboid))"; return statement; }