Revision 68d6d316
Added by Patrick Plitzner over 7 years ago
| src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java | ||
|---|---|---|
| 13 | 13 |
import java.util.Iterator; |
| 14 | 14 |
import java.util.List; |
| 15 | 15 |
|
| 16 |
import org.apache.commons.lang.StringUtils; |
|
| 17 |
|
|
| 16 | 18 |
import com.vaadin.data.util.sqlcontainer.query.FreeformQuery; |
| 17 | 19 |
import com.vaadin.data.util.sqlcontainer.query.QueryDelegate; |
| 18 | 20 |
|
| ... | ... | |
| 56 | 58 |
} |
| 57 | 59 |
|
| 58 | 60 |
public static QueryDelegate generateTaxonDistributionQuery(List<String> termList, List<Integer> taxonNodeIds) throws SQLException {
|
| 61 |
|
|
| 59 | 62 |
String idString = ""; |
| 60 | 63 |
Iterator<Integer> nodeIterator = taxonNodeIds.iterator(); |
| 61 | 64 |
while (nodeIterator.hasNext()) {
|
| ... | ... | |
| 66 | 69 |
} |
| 67 | 70 |
} |
| 68 | 71 |
String FROM_QUERY = |
| 69 |
" FROM TaxonNode tn " + |
|
| 70 |
"INNER JOIN TaxonBase tb on tn.taxon_id = tb.id " + |
|
| 71 |
"INNER JOIN Classification cl ON tn.classification_id = cl.id " + |
|
| 72 |
"LEFT OUTER JOIN TaxonNameBase tnb ON tnb.id=tb.id " + |
|
| 73 |
"LEFT OUTER JOIN DescriptionBase db ON db.taxon_id=tb.id " + |
|
| 74 |
"LEFT OUTER JOIN (SELECT indescription_id, area_id, status_id, DTYPE, id FROM DescriptionElementBase deb WHERE deb.DTYPE LIKE 'Distribution') AS deb ON deb.indescription_id=db.id " + |
|
| 75 |
"LEFT OUTER JOIN DefinedTermBase dtb on deb.status_id=dtb.id " + |
|
| 76 |
"LEFT OUTER JOIN DefinedTermBase dtb1 on deb.area_id=dtb1.id " + |
|
| 77 |
"LEFT OUTER JOIN DefinedTermBase dtb2 on tnb.rank_id = dtb2.id " + |
|
| 78 |
"WHERE tn.id IN ("+ idString +") AND tb.DTYPE = 'Taxon'" ;
|
|
| 79 |
|
|
| 80 |
String GROUP_BY = " GROUP BY tn.id "; |
|
| 72 |
"FROM TaxonNameBase tnb " |
|
| 73 |
+ "INNER JOIN TaxonBase tb on tnb.id = tb.name_id and tb.DTYPE='Taxon'" + // # name<->taxon |
|
| 74 |
"INNER JOIN TaxonNode tn on tn.taxon_id = tb.id "+ |
|
| 75 |
"INNER JOIN DefinedTermBase rank on tnb.rank_id = rank.id "+// # rank <-> name |
|
| 76 |
"LEFT OUTER JOIN DescriptionBase descr on descr.taxon_id = tb.id "+// # taxon <-> taxon description (not every taxon has a description) |
|
| 77 |
"LEFT OUTER JOIN DescriptionElementBase descrEl on descrEl.indescription_id = descr.id and descrEl.DTYPE = 'Distribution' "+// # distribution <-> description |
|
| 78 |
"LEFT OUTER JOIN DefinedTermBase statusTerm on statusTerm.id = descrEl.status_id "+ |
|
| 79 |
"LEFT OUTER JOIN DefinedTermBase area on area.id = descrEl.area_id "+ |
|
| 80 |
"WHERE tn.id IN ("+ idString +") ";
|
|
| 81 |
|
|
| 82 |
String GROUP_BY = " GROUP BY tb.uuid, tn.id "; |
|
| 81 | 83 |
|
| 82 | 84 |
String ORDER_BY = " ORDER BY tb.titleCache "; |
| 83 | 85 |
|
| 84 |
String SELECT_QUERY= |
|
| 85 |
"SELECT tb.DTYPE," +
|
|
| 86 |
"tb.id, " +
|
|
| 87 |
"tb.uuid, " +
|
|
| 88 |
"tn.classification_id, " +
|
|
| 86 |
String SELECT_QUERY= "SELECT "
|
|
| 87 |
+ "tb.DTYPE, "
|
|
| 88 |
+ "tb.id, "
|
|
| 89 |
+ "tb.uuid, "
|
|
| 90 |
+ "tn.classification_id, "+
|
|
| 89 | 91 |
"tb.titleCache AS "+TAXON_COLUMN+", " + |
| 90 |
"dtb2.titleCache AS "+RANK_COLUMN+", ";
|
|
| 92 |
"rank.titleCache AS "+RANK_COLUMN+", ";
|
|
| 91 | 93 |
|
| 92 |
int count = termList.size(); |
|
| 93 | 94 |
for(String term : termList){
|
| 94 |
if(count == 1){
|
|
| 95 |
SELECT_QUERY= SELECT_QUERY + |
|
| 96 |
"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"' " ; |
|
| 97 |
}else{
|
|
| 98 |
SELECT_QUERY= SELECT_QUERY + |
|
| 99 |
"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"'," ; |
|
| 100 |
} |
|
| 101 |
count--; |
|
| 95 |
SELECT_QUERY += "MAX( IF(area.titleCache = '"+ term +"', statusTerm.titleCache, NULL) ) as '"+ term +"'," ; |
|
| 102 | 96 |
} |
| 97 |
SELECT_QUERY = StringUtils.stripEnd(SELECT_QUERY, ","); |
|
| 103 | 98 |
SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY + ORDER_BY; |
| 104 | 99 |
String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY; |
| 105 |
// String CONTAINS_QUERY = "SELECT * FROM TaxonNode tn WHERE tn.id = ?"; |
|
| 106 | 100 |
String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.uuid = ?"; |
| 107 | 101 |
|
| 108 | 102 |
return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY); |
Also available in: Unified diff
ref #5458 Fix and streamline SQL distribution query