Revision 44682
| 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/JDBCHelper.java | ||
|---|---|---|
| 3 | 3 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
| 4 | 4 |
import java.sql.Connection; |
| 5 | 5 |
import java.sql.ResultSet; |
| 6 |
import java.util.List; |
|
| 6 | 7 |
import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType; |
| 7 | 8 |
import org.gvsig.fmap.dal.SQLBuilder; |
| 8 | 9 |
import org.gvsig.fmap.dal.exception.DataException; |
| ... | ... | |
| 183 | 184 |
|
| 184 | 185 |
public boolean isThreadSafe(); |
| 185 | 186 |
|
| 186 |
public String[] replaceForeingValueFunction(SQLBuilder sqlbuilder, FeatureType type); |
|
| 187 |
public void replaceForeingValueFunction( |
|
| 188 |
SQLBuilder sqlbuilder, |
|
| 189 |
FeatureType type, |
|
| 190 |
List<String> extra_column_names // Output param |
|
| 191 |
); |
|
| 192 |
|
|
| 187 | 193 |
} |
| 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 | ||
|---|---|---|
| 6 | 6 |
import org.apache.commons.lang3.ArrayUtils; |
| 7 | 7 |
import org.apache.commons.lang3.StringUtils; |
| 8 | 8 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
| 9 |
import org.gvsig.fmap.dal.SQLBuilder; |
|
| 10 |
import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder; |
|
| 9 | 11 |
import org.gvsig.fmap.dal.exception.DataException; |
| 10 | 12 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
| 11 | 13 |
import org.gvsig.fmap.dal.feature.FeatureQuery; |
| ... | ... | |
| 70 | 72 |
public String getSQL() {
|
| 71 | 73 |
List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
| 72 | 74 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
| 73 |
String sql = this.getSQL(sqlbuilder, columns); |
|
| 75 |
String sql = this.getSQL(sqlbuilder, columns, null);
|
|
| 74 | 76 |
return sql; |
| 75 | 77 |
} |
| 76 | 78 |
|
| 77 |
public String getSQL(JDBCSQLBuilderBase sqlbuilder, List<FeatureAttributeDescriptor> columns) {
|
|
| 79 |
public String getSQL( |
|
| 80 |
JDBCSQLBuilderBase sqlbuilder, |
|
| 81 |
List<FeatureAttributeDescriptor> columns, |
|
| 82 |
List<String> extraColumnNames |
|
| 83 |
) {
|
|
| 78 | 84 |
double tolerance = -1 ; //query.getScale(); |
| 79 | 85 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
| 86 |
SelectBuilder select = sqlbuilder.select(); |
|
| 80 | 87 |
|
| 81 | 88 |
List<String> primaryKeys = new ArrayList<>(); |
| 82 | 89 |
for(FeatureAttributeDescriptor attr : storeType.getPrimaryKey() ) {
|
| ... | ... | |
| 99 | 106 |
forcedColumns.remove(attr.getName()); |
| 100 | 107 |
} |
| 101 | 108 |
if( attr.getType() == DataTypes.GEOMETRY ) {
|
| 102 |
sqlbuilder.select().column().name(attr.getName()).as_geometry();
|
|
| 109 |
select.column().name(attr.getName()).as_geometry();
|
|
| 103 | 110 |
// if( tolerance<=0 || !sqlbuilder.getConfig().has_functionality(Config.ST_Simplify)) {
|
| 104 |
// sqlbuilder.select().column().name(attr.getName()).as_geometry();
|
|
| 111 |
// select.column().name(attr.getName()).as_geometry();
|
|
| 105 | 112 |
// } else {
|
| 106 |
// sqlbuilder.select().column().value(
|
|
| 113 |
// select.column().value(
|
|
| 107 | 114 |
// sqlbuilder.ST_Simplify( |
| 108 | 115 |
// sqlbuilder.column(attr.getName()), |
| 109 | 116 |
// sqlbuilder.constant(tolerance) |
| ... | ... | |
| 112 | 119 |
// } |
| 113 | 120 |
columns.add(attr); |
| 114 | 121 |
} else {
|
| 115 |
sqlbuilder.select().column().name(attr.getName());
|
|
| 122 |
select.column().name(attr.getName());
|
|
| 116 | 123 |
columns.add(attr); |
| 117 | 124 |
} |
| 118 | 125 |
if( query !=null && query.isGrouped() ) {
|
| 119 |
sqlbuilder.select().group_by(expbuilder.column(attr.getName()));
|
|
| 126 |
select.group_by(expbuilder.column(attr.getName()));
|
|
| 120 | 127 |
} |
| 121 | 128 |
} |
| 122 | 129 |
if( query ==null || !query.isGrouped() ) {
|
| 123 | 130 |
for(String attrName : forcedColumns ) {
|
| 124 |
sqlbuilder.select().column().name(attrName);
|
|
| 131 |
select.column().name(attrName);
|
|
| 125 | 132 |
columns.add(setType.getAttributeDescriptor(attrName)); |
| 126 | 133 |
} |
| 127 | 134 |
} |
| 128 | 135 |
|
| 129 |
sqlbuilder.select().from().table()
|
|
| 136 |
select.from().table()
|
|
| 130 | 137 |
.database(this.table.getDatabase()) |
| 131 | 138 |
.schema(this.table.getSchema()) |
| 132 | 139 |
.name(this.table.getTable()); |
| 133 |
sqlbuilder.select().from().subquery(this.table.getSubquery());
|
|
| 140 |
select.from().subquery(this.table.getSubquery());
|
|
| 134 | 141 |
|
| 135 | 142 |
Evaluator filter = query==null? null:query.getFilter(); |
| 136 | 143 |
if( filter != null ) {
|
| 137 | 144 |
String sqlfilter = filter.getSQL(); |
| 138 | 145 |
if( ! StringUtils.isEmpty(sqlfilter) ) {
|
| 139 | 146 |
if( this.helper.supportFilter(this.storeType, filter) ) {
|
| 140 |
sqlbuilder.select().where().set(expbuilder.toValue(sqlfilter));
|
|
| 147 |
select.where().set(expbuilder.toValue(sqlfilter));
|
|
| 141 | 148 |
} |
| 142 | 149 |
} |
| 143 | 150 |
} |
| 144 | 151 |
if( ! StringUtils.isEmpty(baseFilter) ) {
|
| 145 |
sqlbuilder.select().where().and(expbuilder.toValue(baseFilter));
|
|
| 152 |
select.where().and(expbuilder.toValue(baseFilter));
|
|
| 146 | 153 |
} |
| 147 | 154 |
|
| 148 | 155 |
FeatureQueryOrder order = query==null? null:query.getOrder(); |
| ... | ... | |
| 151 | 158 |
if( member.hasEvaluator() ) {
|
| 152 | 159 |
String sqlorder = member.getEvaluator().getSQL(); |
| 153 | 160 |
if( ! StringUtils.isEmpty(sqlorder) ) {
|
| 154 |
sqlbuilder.select().order_by() |
|
| 155 |
.custom(sqlorder); |
|
| 161 |
select.order_by().custom(sqlorder); |
|
| 156 | 162 |
} |
| 157 | 163 |
} else {
|
| 158 |
|
|
| 159 |
sqlbuilder.select().order_by() |
|
| 164 |
select.order_by() |
|
| 160 | 165 |
.column(member.getAttributeName()) |
| 161 | 166 |
.ascending(member.getAscending()); |
| 162 | 167 |
} |
| 163 | 168 |
} |
| 164 | 169 |
} |
| 165 | 170 |
if( !StringUtils.isEmpty(baseOrder) ) {
|
| 166 |
sqlbuilder.select().order_by().custom(baseOrder);
|
|
| 171 |
select.order_by().custom(baseOrder);
|
|
| 167 | 172 |
} |
| 168 | 173 |
if( offset>0 || (offset==0 && limit>0) ) {
|
| 169 | 174 |
// No tengo claro que (offset==0 && limit>0) sea lo mas correcto, |
| ... | ... | |
| 176 | 181 |
// obtener los primeros elementos sin importarnos su orden. |
| 177 | 182 |
for(String attrName : primaryKeys ) {
|
| 178 | 183 |
// Se precisa indicar un orden para usar OFFSET. |
| 179 |
sqlbuilder.select().order_by().column(sqlbuilder.as_identifier(attrName)).ascending();
|
|
| 184 |
select.order_by().column(sqlbuilder.as_identifier(attrName)).ascending();
|
|
| 180 | 185 |
} |
| 181 | 186 |
} |
| 182 | 187 |
if( limit > 0 ) {
|
| 183 |
sqlbuilder.select().limit(limit);
|
|
| 188 |
select.limit(limit);
|
|
| 184 | 189 |
} else {
|
| 185 |
sqlbuilder.select().limit(query==null? null:query.getLimit());
|
|
| 190 |
select.limit(query==null? null:query.getLimit());
|
|
| 186 | 191 |
} |
| 187 | 192 |
if( offset>0 ) {
|
| 188 |
sqlbuilder.select().offset(offset);
|
|
| 193 |
select.offset(offset);
|
|
| 189 | 194 |
} |
| 190 | 195 |
sqlbuilder.setProperties( |
| 191 | 196 |
null, |
| 192 | 197 |
PROP_FEATURE_TYPE, this.storeType, |
| 193 | 198 |
PROP_TABLE, table |
| 194 |
); |
|
| 195 |
|
|
| 199 |
);
|
|
| 200 |
this.helper.replaceForeingValueFunction(sqlbuilder, storeType, extraColumnNames); |
|
| 196 | 201 |
String sql = sqlbuilder.toString(); |
| 197 | 202 |
return sql; |
| 198 | 203 |
} |
| 199 | 204 |
|
| 200 | 205 |
public ResultSetEntry createResultSet() throws DataException {
|
| 201 | 206 |
List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
| 207 |
List<String> extraColumnNames = new ArrayList<>(); |
|
| 208 |
|
|
| 202 | 209 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
| 203 |
String sql = this.getSQL(sqlbuilder, columns); |
|
| 204 |
String[] extraColumnNames = this.helper.replaceForeingValueFunction(sqlbuilder, storeType); |
|
| 210 |
String sql = this.getSQL(sqlbuilder, columns, extraColumnNames); |
|
| 205 | 211 |
|
| 206 | 212 |
ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create( |
| 207 | 213 |
sql, fetchSize, |
| 208 | 214 |
columns.toArray(new FeatureAttributeDescriptor[columns.size()]), |
| 209 |
extraColumnNames |
|
| 215 |
extraColumnNames.toArray(new String[extraColumnNames.size()])
|
|
| 210 | 216 |
); |
| 211 | 217 |
return resultSetEntry; |
| 212 | 218 |
} |
| 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/CountOperation.java | ||
|---|---|---|
| 64 | 64 |
// El and() hace un set() si no hay un filtro previo |
| 65 | 65 |
sqlbuilder.select().where().and(expbuilder.toValue(filter)); |
| 66 | 66 |
} |
| 67 |
this.helper.replaceForeingValueFunction(sqlbuilder, featureType); |
|
| 67 |
this.helper.replaceForeingValueFunction(sqlbuilder, featureType, null);
|
|
| 68 | 68 |
|
| 69 | 69 |
sqlbuilder.select().remove_all_columns(); |
| 70 | 70 |
sqlbuilder.select().column().value(sqlbuilder.count().all()); |
| 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/CalculateEnvelopeOfColumnOperation.java | ||
|---|---|---|
| 114 | 114 |
sqlbuilder.select().where().and( |
| 115 | 115 |
expbuilder.not_is_null(expbuilder.column(columnName)) |
| 116 | 116 |
); |
| 117 |
this.helper.replaceForeingValueFunction(sqlbuilder, featureType); |
|
| 117 |
this.helper.replaceForeingValueFunction(sqlbuilder, featureType, null);
|
|
| 118 | 118 |
sqlbuilder.setProperties( |
| 119 | 119 |
Variable.class, |
| 120 | 120 |
PROP_TABLE, table |
| 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/TableIsEmptyOperation.java | ||
|---|---|---|
| 67 | 67 |
sqlbuilder.select().where().and(expbuilder.toValue(filter)); |
| 68 | 68 |
} |
| 69 | 69 |
sqlbuilder.select().limit(1); |
| 70 |
this.helper.replaceForeingValueFunction(sqlbuilder, featureType); |
|
| 70 |
this.helper.replaceForeingValueFunction(sqlbuilder, featureType, null);
|
|
| 71 | 71 |
sqlbuilder.setProperties( |
| 72 | 72 |
ExpressionBuilder.Variable.class, |
| 73 | 73 |
PROP_TABLE, table |
| 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/JDBCHelperBase.java | ||
|---|---|---|
| 562 | 562 |
public boolean isThreadSafe() {
|
| 563 | 563 |
return true; |
| 564 | 564 |
} |
| 565 |
|
|
| 565 |
|
|
| 566 | 566 |
@Override |
| 567 |
public String[] replaceForeingValueFunction(SQLBuilder sqlbuilder, FeatureType type) {
|
|
| 567 |
public void replaceForeingValueFunction( |
|
| 568 |
SQLBuilder sqlbuilder, |
|
| 569 |
FeatureType type, |
|
| 570 |
List<String> extra_column_names) {
|
|
| 571 |
try {
|
|
| 568 | 572 |
// See test SQLBuilderTest->testForeingValue() |
| 569 | 573 |
final ExpressionBuilder where = sqlbuilder.select().where(); |
| 570 | 574 |
if (where == null || where.isEmpty()) {
|
| 571 |
return null;
|
|
| 575 |
return; |
|
| 572 | 576 |
} |
| 573 | 577 |
final SQLBuilder.TableNameBuilder table = sqlbuilder.select().from().table(); |
| 574 | 578 |
final ExpressionBuilder expbuilder = sqlbuilder.expression(); |
| 575 | 579 |
|
| 576 |
final List<String> foreing_value_args = new ArrayList<>(); |
|
| 580 |
final List<String> foreing_value_args; |
|
| 581 |
if( extra_column_names==null ) {
|
|
| 582 |
foreing_value_args = new ArrayList<>(); |
|
| 583 |
} else {
|
|
| 584 |
foreing_value_args = extra_column_names; |
|
| 585 |
} |
|
| 577 | 586 |
final List<ExpressionBuilder.Value[]> value_replacements = new ArrayList<>(); |
| 578 | 587 |
|
| 579 | 588 |
// Buscamos las llamadas a la funcion "foreing_value" y nos quedamos |
| ... | ... | |
| 640 | 649 |
// Si no habia ningun llamada a la funcion FOREING_VALUE, no hay que |
| 641 | 650 |
// hacer nada. |
| 642 | 651 |
if (foreing_value_args.isEmpty()) {
|
| 643 |
return null;
|
|
| 652 |
return; |
|
| 644 | 653 |
} |
| 645 | 654 |
|
| 646 | 655 |
// Calculamos que referencias de columnas hemos de cambiar para |
| ... | ... | |
| 717 | 726 |
} |
| 718 | 727 |
} |
| 719 | 728 |
|
| 720 |
return foreing_value_args.toArray(new String[foreing_value_args.size()]); |
|
| 729 |
} catch(Throwable th) {
|
|
| 730 |
LOGGER.warn("Can't replace FORENG_VALUE function.", th);
|
|
| 731 |
throw th; |
|
| 732 |
} finally {
|
|
| 733 |
LOGGER.debug("Exist from replaceForeingValueFunction.");
|
|
| 734 |
} |
|
| 721 | 735 |
} |
| 722 | 736 |
} |
| 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/test/java/org/gvsig/fmap/dal/store/jdbc2/SQLBuilderTest.java | ||
|---|---|---|
| 1 | 1 |
package org.gvsig.fmap.dal.store.jdbc2; |
| 2 | 2 |
|
| 3 |
import java.util.ArrayList; |
|
| 4 |
import java.util.List; |
|
| 3 | 5 |
import junit.framework.TestCase; |
| 6 |
import org.apache.commons.collections.CollectionUtils; |
|
| 7 |
import org.apache.commons.collections.ListUtils; |
|
| 4 | 8 |
import org.apache.commons.lang3.ArrayUtils; |
| 9 |
import org.apache.commons.lang3.StringUtils; |
|
| 5 | 10 |
import org.cresques.cts.IProjection; |
| 6 | 11 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
| 7 | 12 |
import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
| ... | ... | |
| 568 | 573 |
System.out.println("# Test:: testForeingValue");
|
| 569 | 574 |
System.out.println("# SQL1:: " + sqlbuilder.toString());
|
| 570 | 575 |
|
| 571 |
String[] attrNames = helper.replaceForeingValueFunction(sqlbuilder, ft); |
|
| 576 |
List<String> attrNames = new ArrayList<>(); |
|
| 577 |
helper.replaceForeingValueFunction(sqlbuilder, ft, attrNames); |
|
| 572 | 578 |
|
| 573 | 579 |
System.out.println("# SQL2:: " + sqlbuilder.toString());
|
| 574 | 580 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 575 | 581 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 576 |
System.out.println("# attrNames:: " + ArrayUtils.toString(attrNames));
|
|
| 582 |
System.out.println("# attrNames:: " + StringUtils.join(attrNames,","));
|
|
| 577 | 583 |
|
| 578 | 584 |
//# Test:: testForeingValue |
| 579 | 585 |
//# SQL1:: SELECT "ID", "NAME", "DESCRIPTION", "TYPE" FROM "dbo"."test1" WHERE (( (FOREING_VALUE('TYPE.DESCRIPTION')) LIKE ('A%') ) AND ( (FOREING_VALUE('PHONE_TYPE.DESCRIPTION')) = ('mobile') ))
|
| ... | ... | |
| 595 | 601 |
ArrayUtils.toString(sqlbuilder.parameters_names()) |
| 596 | 602 |
); |
| 597 | 603 |
assertEquals( |
| 598 |
"{TYPE.DESCRIPTION,PHONE_TYPE.DESCRIPTION}",
|
|
| 599 |
ArrayUtils.toString(attrNames)
|
|
| 604 |
"TYPE.DESCRIPTION,PHONE_TYPE.DESCRIPTION",
|
|
| 605 |
StringUtils.join(attrNames,",")
|
|
| 600 | 606 |
); |
| 601 | 607 |
} |
| 602 | 608 |
|
Also available in: Unified diff