Revision 45709 trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.db/org.gvsig.fmap.dal.db.jdbc/src/main/java/org/gvsig/fmap/dal/store/jdbc2/spi/operations/ResultSetForSetProviderOperation.java
| ResultSetForSetProviderOperation.java | ||
|---|---|---|
| 52 | 52 |
import org.gvsig.tools.evaluator.Evaluator; |
| 53 | 53 |
|
| 54 | 54 |
public class ResultSetForSetProviderOperation extends AbstractConnectionOperation {
|
| 55 |
|
|
| 55 | 56 |
private final TableReference table; |
| 56 | 57 |
private final String baseFilter; |
| 57 | 58 |
private final String baseOrder; |
| ... | ... | |
| 73 | 74 |
long limit, |
| 74 | 75 |
long offset, |
| 75 | 76 |
int fetchSize |
| 76 |
) {
|
|
| 77 |
) {
|
|
| 77 | 78 |
super(helper); |
| 78 | 79 |
this.table = table; |
| 79 | 80 |
this.baseFilter = baseFilter; |
| ... | ... | |
| 83 | 84 |
this.query = query; |
| 84 | 85 |
this.limit = limit; |
| 85 | 86 |
this.offset = offset; |
| 86 |
this.fetchSize = fetchSize;
|
|
| 87 |
this.fetchSize = fetchSize; |
|
| 87 | 88 |
} |
| 88 | 89 |
|
| 89 | 90 |
@Override |
| ... | ... | |
| 91 | 92 |
ResultSetEntry rs = createResultSet(); |
| 92 | 93 |
return rs; |
| 93 | 94 |
} |
| 94 |
|
|
| 95 |
|
|
| 95 | 96 |
@Override |
| 96 | 97 |
public Object perform(Connection conn) throws DataException {
|
| 97 |
throw new UnsupportedOperationException("Not supported yet.");
|
|
| 98 |
throw new UnsupportedOperationException("Not supported yet.");
|
|
| 98 | 99 |
} |
| 99 | 100 |
|
| 100 | 101 |
public String getSQL() {
|
| ... | ... | |
| 103 | 104 |
String sql = this.getSQL(sqlbuilder, columns, null); |
| 104 | 105 |
return sql; |
| 105 | 106 |
} |
| 106 |
|
|
| 107 |
|
|
| 107 | 108 |
public String getSQL( |
| 108 |
JDBCSQLBuilderBase sqlbuilder,
|
|
| 109 |
List<FeatureAttributeDescriptor> columns,
|
|
| 109 |
JDBCSQLBuilderBase sqlbuilder, |
|
| 110 |
List<FeatureAttributeDescriptor> columns, |
|
| 110 | 111 |
List<String> extraColumnNames |
| 111 |
) {
|
|
| 112 |
double tolerance = -1 ; //query.getScale();
|
|
| 112 |
) {
|
|
| 113 |
double tolerance = -1; //query.getScale(); |
|
| 113 | 114 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
| 114 | 115 |
SelectBuilder select = sqlbuilder.select(); |
| 115 |
|
|
| 116 |
|
|
| 116 | 117 |
List<String> primaryKeys = new ArrayList<>(); |
| 117 |
for(FeatureAttributeDescriptor attr : storeType.getPrimaryKey() ) {
|
|
| 118 |
for (FeatureAttributeDescriptor attr : storeType.getPrimaryKey()) {
|
|
| 118 | 119 |
primaryKeys.add(attr.getName()); |
| 119 | 120 |
} |
| 120 | 121 |
List<String> forcedColumns = new ArrayList<>(primaryKeys); |
| 121 | 122 |
|
| 122 | 123 |
String[] constantsAttributeNames = null; |
| 123 |
if(query !=null && query.hasConstantsAttributeNames() ) {
|
|
| 124 |
if (query != null && query.hasConstantsAttributeNames()) {
|
|
| 124 | 125 |
constantsAttributeNames = query.getConstantsAttributeNames(); |
| 125 | 126 |
} |
| 126 |
for(FeatureAttributeDescriptor attr : setType ) {
|
|
| 127 |
if( attr.isComputed() ) {
|
|
| 127 |
for (FeatureAttributeDescriptor attr : setType) {
|
|
| 128 |
if (attr.isComputed()) {
|
|
| 128 | 129 |
continue; |
| 129 | 130 |
} |
| 130 |
if( ArrayUtils.contains(constantsAttributeNames, attr.getName()) ) {
|
|
| 131 |
if (ArrayUtils.contains(constantsAttributeNames, attr.getName())) {
|
|
| 131 | 132 |
continue; |
| 132 | 133 |
} |
| 133 |
if( attr.isPrimaryKey() ) {
|
|
| 134 |
if (attr.isPrimaryKey()) {
|
|
| 134 | 135 |
forcedColumns.remove(attr.getName()); |
| 135 | 136 |
} |
| 136 |
if( query !=null && query.hasGroupByColumns()) {
|
|
| 137 |
String aggregate = query.getAggregate(this.table.getTable(), attr.getName()); |
|
| 138 |
if( this.query.isAGroupByColumn(attr.getName()) ) {
|
|
| 139 |
select.column().name(attr.getName()); |
|
| 140 |
} else if( aggregate == null ) {
|
|
| 141 |
select.column().value(expbuilder.constant(null)).as(attr.getName()); |
|
| 142 |
} else {
|
|
| 143 |
select.column() |
|
| 144 |
.value(ExpressionUtils.compile(aggregate).toValue(expbuilder)) |
|
| 145 |
.as(attr.getName()); |
|
| 146 |
} |
|
| 137 |
if (query != null && query.hasGroupByColumns()) {
|
|
| 138 |
String aggregate = query.getAggregate(this.table.getTable(), attr.getName()); |
|
| 139 |
if (this.query.isAGroupByColumn(attr.getName())) {
|
|
| 140 |
select.column().name(attr.getName()); |
|
| 141 |
} else if (aggregate == null) {
|
|
| 142 |
select.column().value(expbuilder.constant(null)).as(attr.getName()); |
|
| 147 | 143 |
} else {
|
| 148 |
if( attr.getType() == DataTypes.GEOMETRY ) {
|
|
| 149 |
select.column().name(attr.getName()).as_geometry(); |
|
| 150 |
// if( tolerance<=0 || !sqlbuilder.getConfig().has_functionality(Config.ST_Simplify)) {
|
|
| 151 |
// select.column().name(attr.getName()).as_geometry(); |
|
| 152 |
// } else {
|
|
| 153 |
// select.column().value( |
|
| 154 |
// sqlbuilder.ST_Simplify( |
|
| 155 |
// sqlbuilder.column(attr.getName()), |
|
| 156 |
// sqlbuilder.constant(tolerance) |
|
| 157 |
// ) |
|
| 158 |
// ).as_geometry(); |
|
| 159 |
// } |
|
| 160 |
} else {
|
|
| 161 |
select.column().name(attr.getName()); |
|
| 144 |
select.column() |
|
| 145 |
.value(ExpressionUtils.compile(aggregate).toValue(expbuilder)) |
|
| 146 |
.as(attr.getName()); |
|
| 147 |
} |
|
| 148 |
} else {
|
|
| 149 |
if (attr.getType() == DataTypes.GEOMETRY) {
|
|
| 150 |
select.column().name(attr.getName()).as_geometry(); |
|
| 151 |
// if( tolerance<=0 || !sqlbuilder.getConfig().has_functionality(Config.ST_Simplify)) {
|
|
| 152 |
// select.column().name(attr.getName()).as_geometry(); |
|
| 153 |
// } else {
|
|
| 154 |
// select.column().value( |
|
| 155 |
// sqlbuilder.ST_Simplify( |
|
| 156 |
// sqlbuilder.column(attr.getName()), |
|
| 157 |
// sqlbuilder.constant(tolerance) |
|
| 158 |
// ) |
|
| 159 |
// ).as_geometry(); |
|
| 160 |
// } |
|
| 161 |
} else {
|
|
| 162 |
select.column().name(attr.getName()); |
|
| 163 |
} |
|
| 162 | 164 |
} |
| 163 |
} |
|
| 164 | 165 |
columns.add(attr); |
| 165 | 166 |
} |
| 166 |
|
|
| 167 |
if( query !=null && query.hasGroupByColumns() ) {
|
|
| 167 |
|
|
| 168 |
if (query != null && query.hasGroupByColumns()) {
|
|
| 168 | 169 |
for (Map.Entry<String, String> entry : query.getAggregateFunctions().entrySet()) {
|
| 169 |
EditableFeatureAttributeDescriptor attr = query.getExtraColumn().get(entry.getKey());
|
|
| 170 |
if( attr!=null ) {
|
|
| 170 |
EditableFeatureAttributeDescriptor attr = query.getExtraColumn().get(entry.getKey()); |
|
| 171 |
if (attr != null) {
|
|
| 171 | 172 |
Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression(); |
| 172 |
ExpressionBuilder.Function aggregateExp = expbuilder.function(entry.getValue(),exp.getCode().toValue()); |
|
| 173 |
ExpressionBuilder.Function aggregateExp = expbuilder.function(entry.getValue(), exp.getCode().toValue());
|
|
| 173 | 174 |
if (!select.has_column(attr.getName())) {
|
| 174 | 175 |
select.column().value(aggregateExp).as(attr.getName()); |
| 175 | 176 |
} |
| 176 | 177 |
if (!extraColumnNames.contains(attr.getName())) {
|
| 177 | 178 |
extraColumnNames.add(attr.getName()); |
| 178 | 179 |
} |
| 179 |
} |
|
| 180 |
}
|
|
| 180 | 181 |
} |
| 181 |
for(String attrName : query.getGroupByColumns() ) {
|
|
| 182 |
for (String attrName : query.getGroupByColumns()) {
|
|
| 182 | 183 |
if (this.setType.getExtraColumns().get(attrName) != null) {
|
| 183 | 184 |
EditableFeatureAttributeDescriptor attr = this.setType.getExtraColumns().get(attrName); |
| 184 | 185 |
select.group_by(expbuilder.column(attrName)); |
| ... | ... | |
| 197 | 198 |
throw new RuntimeException("Not able to create column by expression in groupby query", ex);
|
| 198 | 199 |
} |
| 199 | 200 |
} else {
|
| 200 |
ExpressionBuilder.Function atrcolumn = expbuilder.getattr(this.table.getTable(), attrName);
|
|
| 201 |
ExpressionBuilder.Function atrcolumn = expbuilder.getattr(this.table.getTable(), attrName); |
|
| 201 | 202 |
select.group_by(atrcolumn); |
| 202 | 203 |
} |
| 203 |
}
|
|
| 204 |
} |
|
| 204 | 205 |
} else {
|
| 205 |
for(String attrName : forcedColumns ) {
|
|
| 206 |
for (String attrName : forcedColumns) {
|
|
| 206 | 207 |
select.column().name(attrName); |
| 207 | 208 |
columns.add(setType.getAttributeDescriptor(attrName)); |
| 208 | 209 |
} |
| 209 | 210 |
} |
| 210 |
|
|
| 211 |
|
|
| 211 | 212 |
select.from().table() |
| 212 | 213 |
.database(this.table.getDatabase()) |
| 213 | 214 |
.schema(this.table.getSchema()) |
| 214 | 215 |
.name(this.table.getTable()); |
| 215 | 216 |
select.from().subquery(this.table.getSubquery()); |
| 216 |
|
|
| 217 |
Evaluator filter = query==null? null:query.getFilter();
|
|
| 218 |
if( filter != null ) {
|
|
| 217 |
|
|
| 218 |
Evaluator filter = query == null ? null : query.getFilter();
|
|
| 219 |
if (filter != null) {
|
|
| 219 | 220 |
String sqlfilter = filter.getSQL(); |
| 220 |
if( ! StringUtils.isEmpty(sqlfilter) ) {
|
|
| 221 |
if( this.helper.supportFilter(this.storeType, filter) ) {
|
|
| 221 |
if (!StringUtils.isEmpty(sqlfilter)) {
|
|
| 222 |
if (this.helper.supportFilter(this.storeType, filter)) {
|
|
| 222 | 223 |
select.where().set(expbuilder.toValue(sqlfilter)); |
| 223 | 224 |
} |
| 224 | 225 |
} |
| 225 | 226 |
} |
| 226 |
if( ! StringUtils.isEmpty(baseFilter) ) {
|
|
| 227 |
if (!StringUtils.isEmpty(baseFilter)) {
|
|
| 227 | 228 |
select.where().and(expbuilder.toValue(baseFilter)); |
| 228 | 229 |
} |
| 229 |
|
|
| 230 |
|
|
| 230 | 231 |
FeatureQueryOrder order = query == null ? null : query.getOrder(); |
| 231 | 232 |
if (order != null) {
|
| 232 | 233 |
for (FeatureQueryOrderMember member : order.members()) {
|
| ... | ... | |
| 246 | 247 |
} |
| 247 | 248 |
} |
| 248 | 249 |
} |
| 249 |
|
|
| 250 |
if( !StringUtils.isEmpty(baseOrder) ) {
|
|
| 250 |
|
|
| 251 |
if (!StringUtils.isEmpty(baseOrder)) {
|
|
| 251 | 252 |
select.order_by().custom(baseOrder); |
| 252 | 253 |
} |
| 253 |
if( !select.has_order_by() ) {
|
|
| 254 |
// Si no tenemos order by comprobamos si lo necesitamos y lo a?adimos. |
|
| 255 |
if( offset>0 || (offset==0 && limit>0) ) {
|
|
| 256 |
// No tengo claro que (offset==0 && limit>0) sea lo mas correcto, |
|
| 257 |
// Pero cuando se va a paginar y se pide la primera pagina offset es |
|
| 258 |
// 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados |
|
| 259 |
// que se obtienen no son correctos, ya que la primera pagina se saca |
|
| 260 |
// sin ordenar y el resto ordenadas. |
|
| 261 |
// Probablemente deberiamos tener alguna otra forma de detectar que |
|
| 262 |
// estamos paginanado ya que asi no distinguimo si solo queremos |
|
| 263 |
// obtener los primeros elementos sin importarnos su orden. |
|
| 264 |
if (select.has_group_by()) {
|
|
| 265 |
ExpressionBuilder.Value group = select.getGroups().get(0); |
|
| 254 |
if (!select.has_order_by()) {
|
|
| 255 |
// Si no tenemos order by comprobamos si lo necesitamos y lo a?adimos.
|
|
| 256 |
if (offset > 0 || (offset == 0 && limit > 0)) {
|
|
| 257 |
// No tengo claro que (offset==0 && limit>0) sea lo mas correcto,
|
|
| 258 |
// Pero cuando se va a paginar y se pide la primera pagina offset es
|
|
| 259 |
// 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados
|
|
| 260 |
// que se obtienen no son correctos, ya que la primera pagina se saca
|
|
| 261 |
// sin ordenar y el resto ordenadas.
|
|
| 262 |
// Probablemente deberiamos tener alguna otra forma de detectar que
|
|
| 263 |
// estamos paginanado ya que asi no distinguimo si solo queremos
|
|
| 264 |
// obtener los primeros elementos sin importarnos su orden.
|
|
| 265 |
if (select.has_group_by()) {
|
|
| 266 |
ExpressionBuilder.Value group = select.getGroups().get(0);
|
|
| 266 | 267 |
// if(!(group instanceof ExpressionBuilder.Function)) {
|
| 267 | 268 |
// expbuilder.getattr(this.table.getTable(), group.XXX); |
| 268 | 269 |
// } |
| 269 |
select.order_by().value(group).ascending(); |
|
| 270 |
|
|
| 271 |
} else if( primaryKeys.isEmpty() ) {
|
|
| 272 |
// Muy probablemente si no tiene pk sea una vista, asi que |
|
| 273 |
// pasaremos de ordenar y esperemos que la vista este ya ordenada. |
|
| 274 |
select.disable_check_order_and_offset(); |
|
| 275 |
} else {
|
|
| 276 |
for(String attrName : primaryKeys ) {
|
|
| 270 |
select.order_by().value(group).ascending();
|
|
| 271 |
|
|
| 272 |
} else if (primaryKeys.isEmpty()) {
|
|
| 273 |
// Muy probablemente si no tiene pk sea una vista, asi que
|
|
| 274 |
// pasaremos de ordenar y esperemos que la vista este ya ordenada.
|
|
| 275 |
select.disable_check_order_and_offset();
|
|
| 276 |
} else {
|
|
| 277 |
for (String attrName : primaryKeys) {
|
|
| 277 | 278 |
// Se precisa indicar un orden para usar OFFSET. |
| 278 |
// select.order_by().column(sqlbuilder.as_identifier(attrName)).ascending(); |
|
| 279 |
// select.order_by().column(sqlbuilder.as_identifier(attrName)).ascending();
|
|
| 279 | 280 |
select.order_by().column(attrName).ascending(); |
| 280 | 281 |
} |
| 281 |
|
|
| 282 |
} |
|
| 283 |
}
|
|
| 284 |
}
|
|
| 285 |
if( limit > 0 ) {
|
|
| 282 |
|
|
| 283 |
}
|
|
| 284 |
}
|
|
| 285 |
} |
|
| 286 |
if (limit > 0) {
|
|
| 286 | 287 |
select.limit(limit); |
| 287 | 288 |
} else {
|
| 288 |
select.limit(query==null? null:query.getLimit());
|
|
| 289 |
select.limit(query == null ? null : query.getLimit());
|
|
| 289 | 290 |
} |
| 290 |
if( offset>0 ) {
|
|
| 291 |
if (offset > 0) {
|
|
| 291 | 292 |
select.offset(offset); |
| 292 |
}
|
|
| 293 |
} |
|
| 293 | 294 |
sqlbuilder.setProperties( |
| 294 |
null,
|
|
| 295 |
null, |
|
| 295 | 296 |
PROP_FEATURE_TYPE, this.storeType, |
| 296 | 297 |
PROP_TABLE, table |
| 297 |
);
|
|
| 298 |
); |
|
| 298 | 299 |
this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames); |
| 299 | 300 |
String sql = sqlbuilder.toString(); |
| 300 | 301 |
return sql; |
| 301 | 302 |
} |
| 302 |
|
|
| 303 |
|
|
| 303 | 304 |
public ResultSetEntry createResultSet() throws DataException {
|
| 304 | 305 |
List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
| 305 | 306 |
List<String> extraColumnNames = new ArrayList<>(); |
| 306 | 307 |
|
| 307 | 308 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
| 308 | 309 |
String sql = this.getSQL(sqlbuilder, columns, extraColumnNames); |
| 309 |
|
|
| 310 |
|
|
| 310 | 311 |
ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create( |
| 311 |
sql, fetchSize,
|
|
| 312 |
sql, fetchSize, |
|
| 312 | 313 |
columns.toArray(new FeatureAttributeDescriptor[columns.size()]), |
| 313 | 314 |
extraColumnNames.toArray(new String[extraColumnNames.size()]) |
| 314 | 315 |
); |
| 315 | 316 |
return resultSetEntry; |
| 316 | 317 |
} |
| 317 |
|
|
| 318 |
|
|
| 318 | 319 |
} |
Also available in: Unified diff