svn-gvsig-desktop / 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 / PerformChangesOperation.java @ 44348
History | View | Annotate | Download (18.8 KB)
1 |
package org.gvsig.fmap.dal.store.jdbc2.spi.operations; |
---|---|
2 |
|
3 |
import java.sql.Connection; |
4 |
import java.sql.PreparedStatement; |
5 |
import java.sql.SQLException; |
6 |
import java.sql.Statement; |
7 |
import java.util.ArrayList; |
8 |
import java.util.Iterator; |
9 |
import java.util.List; |
10 |
import org.apache.commons.collections.CollectionUtils; |
11 |
import org.apache.commons.lang3.StringUtils; |
12 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
13 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Variable; |
14 |
import org.gvsig.fmap.dal.DataTypes; |
15 |
import org.gvsig.fmap.dal.exception.DataException; |
16 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
17 |
import org.gvsig.fmap.dal.feature.FeatureReference; |
18 |
import org.gvsig.fmap.dal.feature.FeatureType; |
19 |
import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
20 |
import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices; |
21 |
import org.gvsig.fmap.dal.feature.spi.FeatureStoreProvider; |
22 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
23 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
24 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
25 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCUpdateWithoutChangesException; |
26 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
27 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
28 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
29 |
import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_FEATURE_TYPE; |
30 |
import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
31 |
import org.gvsig.tools.dispose.Disposable; |
32 |
import org.gvsig.tools.dispose.DisposeUtils; |
33 |
|
34 |
public class PerformChangesOperation extends AbstractConnectionWritableOperation { |
35 |
|
36 |
protected TableReference table;
|
37 |
protected FeatureType featureType;
|
38 |
protected Iterator<FeatureReferenceProviderServices> deleteds; |
39 |
protected Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged; |
40 |
protected Iterator<FeatureProvider> updateds; |
41 |
protected Iterator<FeatureProvider> inserteds; |
42 |
|
43 |
protected boolean typeChanged = false; |
44 |
|
45 |
public PerformChangesOperation(JDBCHelper helper) {
|
46 |
this(helper, null, null, null, null, null, null); |
47 |
} |
48 |
|
49 |
public PerformChangesOperation(JDBCHelper helper,
|
50 |
TableReference table, |
51 |
FeatureType featureType, |
52 |
Iterator<FeatureReferenceProviderServices> deleteds,
|
53 |
Iterator<FeatureProvider> inserteds,
|
54 |
Iterator<FeatureProvider> updateds,
|
55 |
Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged) {
|
56 |
super(helper);
|
57 |
this.deleteds = deleteds;
|
58 |
this.inserteds = inserteds;
|
59 |
this.updateds = updateds;
|
60 |
this.table = table;
|
61 |
this.featureType = featureType;
|
62 |
this.featureTypesChanged = featureTypesChanged;
|
63 |
} |
64 |
|
65 |
public boolean isTypeChanged() { |
66 |
return typeChanged;
|
67 |
} |
68 |
|
69 |
@Override
|
70 |
public Object perform(Connection conn) throws DataException { |
71 |
if (featureTypesChanged.hasNext()) {
|
72 |
FeatureStoreProvider.FeatureTypeChanged item = featureTypesChanged.next(); |
73 |
this.performUpdateTable(conn, table, item.getSource(), item.getTarget());
|
74 |
typeChanged = true;
|
75 |
} else {
|
76 |
typeChanged = false;
|
77 |
} |
78 |
if (deleteds.hasNext()) {
|
79 |
performDeletes(conn, table, featureType, deleteds); |
80 |
} |
81 |
if (updateds.hasNext()) {
|
82 |
performUpdates(conn, table, featureType, updateds); |
83 |
} |
84 |
if (inserteds.hasNext()) {
|
85 |
performInserts(conn, table, featureType, inserteds); |
86 |
} |
87 |
return true; |
88 |
} |
89 |
|
90 |
protected JDBCSQLBuilderBase buildDeleteSQL(
|
91 |
TableReference table, |
92 |
FeatureType type |
93 |
) { |
94 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
95 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
96 |
|
97 |
sqlbuilder.delete().table() |
98 |
.database(this.table.getDatabase())
|
99 |
.schema(this.table.getSchema())
|
100 |
.name(this.table.getTable());
|
101 |
for (FeatureAttributeDescriptor attr : type) {
|
102 |
if (attr.isPrimaryKey()) {
|
103 |
sqlbuilder.delete().where().and( |
104 |
expbuilder.eq( |
105 |
expbuilder.column(attr.getName()), |
106 |
expbuilder.parameter(attr.getName()).as_variable() |
107 |
) |
108 |
); |
109 |
} |
110 |
} |
111 |
if (!sqlbuilder.delete().has_where() ) {
|
112 |
throw new RuntimeException("Operation requires missing pk"); |
113 |
} |
114 |
return sqlbuilder;
|
115 |
} |
116 |
|
117 |
public void performDeletes(Connection conn, |
118 |
TableReference table, |
119 |
FeatureType type, |
120 |
Iterator<FeatureReferenceProviderServices> deleteds) throws DataException { |
121 |
|
122 |
JDBCSQLBuilderBase sqlbuilder = buildDeleteSQL(table, type); |
123 |
|
124 |
PreparedStatement st = null; |
125 |
Disposable paramsDisposer = null;
|
126 |
sqlbuilder.setProperties( |
127 |
Variable.class, |
128 |
PROP_TABLE, table |
129 |
); |
130 |
String sql = sqlbuilder.delete().toString();
|
131 |
try {
|
132 |
st = conn.prepareStatement(sql); |
133 |
while (deleteds.hasNext()) {
|
134 |
FeatureReference reference = (FeatureReference) deleteds.next(); |
135 |
paramsDisposer = sqlbuilder.setParameters(st, reference); |
136 |
int nAffected = JDBCUtils.executeUpdate(st,sql);
|
137 |
if (nAffected == 0) { |
138 |
throw new JDBCUpdateWithoutChangesException( |
139 |
sqlbuilder.delete().toString(), |
140 |
null
|
141 |
); |
142 |
} |
143 |
if (nAffected > 1) { |
144 |
LOGGER.warn("Remove statement affectst to {} rows ( {} )",
|
145 |
nAffected, sql |
146 |
); |
147 |
} |
148 |
} |
149 |
} catch (SQLException e) { |
150 |
throw new JDBCSQLException(e); |
151 |
} finally {
|
152 |
JDBCUtils.closeQuietly(st); |
153 |
DisposeUtils.disposeQuietly(paramsDisposer); |
154 |
} |
155 |
} |
156 |
|
157 |
protected JDBCSQLBuilderBase buildInsertSQL(
|
158 |
TableReference table, |
159 |
FeatureType type |
160 |
) { |
161 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
162 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
163 |
|
164 |
sqlbuilder.insert().table() |
165 |
.database(this.table.getDatabase())
|
166 |
.schema(this.table.getSchema())
|
167 |
.name(this.table.getTable());
|
168 |
for (FeatureAttributeDescriptor attr : type) {
|
169 |
if( attr.isAutomatic() || attr.isComputed() ) {
|
170 |
continue;
|
171 |
} |
172 |
if (attr.getType() == DataTypes.GEOMETRY) {
|
173 |
sqlbuilder.insert().column().name(attr.getName()).with_value( |
174 |
expbuilder.parameter(attr.getName()).as_geometry_variable() |
175 |
.srs( |
176 |
expbuilder.parameter().value( |
177 |
attr.getSRS()).as_constant() |
178 |
) |
179 |
); |
180 |
} else {
|
181 |
sqlbuilder.insert().column().name(attr.getName()).with_value( |
182 |
expbuilder.parameter(attr.getName()) |
183 |
); |
184 |
} |
185 |
} |
186 |
return sqlbuilder;
|
187 |
} |
188 |
|
189 |
public void performInserts(Connection conn, |
190 |
TableReference table, |
191 |
FeatureType type, |
192 |
Iterator<FeatureProvider> inserteds) throws DataException { |
193 |
JDBCSQLBuilderBase sqlbuilder = buildInsertSQL(table, type); |
194 |
|
195 |
PreparedStatement st;
|
196 |
Disposable paramsDisposer; |
197 |
sqlbuilder.setProperties( |
198 |
Variable.class, |
199 |
PROP_FEATURE_TYPE, type, |
200 |
PROP_TABLE, table |
201 |
); |
202 |
String sql = sqlbuilder.insert().toString();
|
203 |
try {
|
204 |
st = conn.prepareStatement(sql); |
205 |
while (inserteds.hasNext()) {
|
206 |
FeatureProvider feature = inserteds.next(); |
207 |
paramsDisposer = sqlbuilder.setParameters(st, feature); |
208 |
try {
|
209 |
if (JDBCUtils.executeUpdate(st,sql) == 0) { |
210 |
throw new JDBCExecuteSQLException( |
211 |
sqlbuilder.insert().toString(), |
212 |
null
|
213 |
); |
214 |
} |
215 |
} finally {
|
216 |
DisposeUtils.disposeQuietly(paramsDisposer); |
217 |
} |
218 |
} |
219 |
} catch (JDBCExecuteSQLException ex) {
|
220 |
throw ex;
|
221 |
} catch (Exception ex) { |
222 |
throw new JDBCExecuteSQLException(sql,ex); |
223 |
} |
224 |
} |
225 |
|
226 |
protected JDBCSQLBuilderBase buildUpdateSQL(
|
227 |
TableReference table, |
228 |
FeatureType type |
229 |
) { |
230 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
231 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
232 |
|
233 |
sqlbuilder.update().table() |
234 |
.database(this.table.getDatabase())
|
235 |
.schema(this.table.getSchema())
|
236 |
.name(this.table.getTable());
|
237 |
for (FeatureAttributeDescriptor attr : type) {
|
238 |
if (attr.isPrimaryKey()) {
|
239 |
sqlbuilder.update().where().and( |
240 |
expbuilder.eq( |
241 |
expbuilder.column(attr.getName()), |
242 |
expbuilder.parameter(attr.getName()).as_variable() |
243 |
) |
244 |
); |
245 |
} |
246 |
if ( attr.isAutomatic() || attr.isReadOnly() || attr.isComputed() ) {
|
247 |
continue;
|
248 |
} |
249 |
if (attr.getType() == DataTypes.GEOMETRY) {
|
250 |
sqlbuilder.update().column().name(attr.getName()).with_value( |
251 |
expbuilder.parameter(attr.getName()).as_geometry_variable().srs( |
252 |
expbuilder.parameter().value(attr.getSRS()).as_constant() |
253 |
) |
254 |
); |
255 |
} else {
|
256 |
sqlbuilder.update().column().name(attr.getName()).with_value( |
257 |
expbuilder.parameter(attr.getName()).as_variable() |
258 |
); |
259 |
} |
260 |
} |
261 |
if (!sqlbuilder.update().has_where() ) {
|
262 |
throw new RuntimeException("Operation requires missing pk"); |
263 |
} |
264 |
return sqlbuilder;
|
265 |
} |
266 |
|
267 |
public void performUpdates(Connection conn, |
268 |
TableReference table, |
269 |
FeatureType type, |
270 |
Iterator<FeatureProvider> updateds) throws DataException { |
271 |
|
272 |
JDBCSQLBuilderBase sqlbuilder = buildUpdateSQL(table, type); |
273 |
|
274 |
PreparedStatement st = null; |
275 |
Disposable paramsDisposer = null;
|
276 |
sqlbuilder.setProperties( |
277 |
Variable.class, |
278 |
PROP_FEATURE_TYPE, type, |
279 |
PROP_TABLE, table |
280 |
); |
281 |
String sql = sqlbuilder.update().toString();
|
282 |
try {
|
283 |
st = conn.prepareStatement(sql); |
284 |
while (updateds.hasNext()) {
|
285 |
FeatureProvider featureProvider = (FeatureProvider) updateds.next(); |
286 |
paramsDisposer = sqlbuilder.setParameters(st, featureProvider); |
287 |
if (JDBCUtils.executeUpdate(st,sql) == 0) { |
288 |
throw new JDBCUpdateWithoutChangesException(sql,null); |
289 |
} |
290 |
} |
291 |
} catch (SQLException e) { |
292 |
throw new JDBCSQLException(e); |
293 |
} finally {
|
294 |
JDBCUtils.closeQuietly(st); |
295 |
DisposeUtils.disposeQuietly(paramsDisposer); |
296 |
} |
297 |
} |
298 |
|
299 |
protected List<String> buildUpdateTableSQL( |
300 |
TableReference table, |
301 |
FeatureType original, |
302 |
FeatureType target |
303 |
) { |
304 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
305 |
sqlbuilder.alter_table().table() |
306 |
.database(this.table.getDatabase())
|
307 |
.schema(this.table.getSchema())
|
308 |
.name(this.table.getTable());
|
309 |
|
310 |
for (FeatureAttributeDescriptor attrOrgiginal : original) {
|
311 |
FeatureAttributeDescriptor attrTarget = target.getAttributeDescriptor( |
312 |
attrOrgiginal.getName() |
313 |
); |
314 |
if (attrTarget == null) { |
315 |
if( attrOrgiginal.isComputed() ) {
|
316 |
continue;
|
317 |
} |
318 |
sqlbuilder.alter_table().drop_column(attrOrgiginal.getName()); |
319 |
} else if( !this.areEquals(attrOrgiginal, attrTarget) ) { |
320 |
if( attrTarget.isComputed() ) {
|
321 |
continue;
|
322 |
} |
323 |
if( attrTarget.getType()==DataTypes.GEOMETRY ) {
|
324 |
sqlbuilder.alter_table().alter_geometry_column( |
325 |
attrTarget.getName(), |
326 |
attrTarget.getGeomType().getType(), |
327 |
attrTarget.getGeomType().getSubType(), |
328 |
attrTarget.getSRS(), |
329 |
attrTarget.isIndexed(), |
330 |
attrTarget.allowNull() |
331 |
); |
332 |
} else {
|
333 |
sqlbuilder.alter_table().alter_column( |
334 |
attrTarget.getName(), |
335 |
attrTarget.getType(), |
336 |
attrTarget.getPrecision(), |
337 |
attrTarget.getSize(), |
338 |
attrTarget.isPrimaryKey(), |
339 |
attrTarget.isIndexed(), |
340 |
attrTarget.allowNull(), |
341 |
attrTarget.isAutomatic(), |
342 |
attrTarget.getDefaultValue() |
343 |
); |
344 |
} |
345 |
} |
346 |
} |
347 |
for (FeatureAttributeDescriptor attrTarget : target) {
|
348 |
if( attrTarget.isComputed() ) {
|
349 |
continue;
|
350 |
} |
351 |
if (original.getAttributeDescriptor(attrTarget.getName()) == null) { |
352 |
if( attrTarget.getType()==DataTypes.GEOMETRY ) {
|
353 |
sqlbuilder.alter_table().add_geometry_column( |
354 |
attrTarget.getName(), |
355 |
attrTarget.getGeomType().getType(), |
356 |
attrTarget.getGeomType().getSubType(), |
357 |
attrTarget.getSRS(), |
358 |
attrTarget.isIndexed(), |
359 |
attrTarget.allowNull() |
360 |
); |
361 |
} else {
|
362 |
sqlbuilder.alter_table().add_column( |
363 |
attrTarget.getName(), |
364 |
attrTarget.getType(), |
365 |
attrTarget.getPrecision(), |
366 |
attrTarget.getSize(), |
367 |
attrTarget.isPrimaryKey(), |
368 |
attrTarget.isIndexed(), |
369 |
attrTarget.allowNull(), |
370 |
attrTarget.isAutomatic(), |
371 |
attrTarget.getDefaultValue() |
372 |
); |
373 |
} |
374 |
} |
375 |
} |
376 |
sqlbuilder.setProperties( |
377 |
Variable.class, |
378 |
PROP_TABLE, table |
379 |
); |
380 |
return sqlbuilder.alter_table().toStrings();
|
381 |
} |
382 |
|
383 |
protected boolean areEquals(FeatureAttributeDescriptor attr1, FeatureAttributeDescriptor attr2) { |
384 |
// No interesa si son o no iguales en general, solo si son iguales en lo
|
385 |
// que a los atributos usados para crear la columna de la tabla se refiere.
|
386 |
if( !StringUtils.equals(attr1.getName(), attr2.getName()) ) {
|
387 |
return false; |
388 |
} |
389 |
if( attr1.getType() != attr2.getType() ) {
|
390 |
return false; |
391 |
} |
392 |
if( attr1.getPrecision() != attr2.getPrecision() ) {
|
393 |
return false; |
394 |
} |
395 |
if( attr1.getSize() != attr2.getSize() ) {
|
396 |
return false; |
397 |
} |
398 |
if( attr1.isPrimaryKey() != attr2.isPrimaryKey() ) {
|
399 |
return false; |
400 |
} |
401 |
// if( attr1.isIndexed() != attr2.isIndexed() ) {
|
402 |
// return false;
|
403 |
// }
|
404 |
if( attr1.allowNull() != attr2.allowNull() ) {
|
405 |
return false; |
406 |
} |
407 |
if( attr1.isAutomatic() != attr2.isAutomatic() ) {
|
408 |
return false; |
409 |
} |
410 |
if( attr1.getDefaultValue() != attr2.getDefaultValue() ) {
|
411 |
if( attr1.getDefaultValue()==null || attr2.getDefaultValue()==null) { |
412 |
return false; |
413 |
} |
414 |
if( !attr1.getDefaultValue().equals(attr2.getDefaultValue()) ) {
|
415 |
return false; |
416 |
} |
417 |
} |
418 |
return true; |
419 |
} |
420 |
|
421 |
protected List<String> buildCreateIndexSQL( |
422 |
TableReference table, |
423 |
FeatureType original, |
424 |
FeatureType target |
425 |
) { |
426 |
ArrayList<String> sqls = new ArrayList<>(); |
427 |
|
428 |
for (FeatureAttributeDescriptor attrTarget : target) {
|
429 |
boolean createIndex = false; |
430 |
if( attrTarget.isIndexed() ) {
|
431 |
FeatureAttributeDescriptor attrOriginal = original.getAttributeDescriptor(attrTarget.getName()); |
432 |
if ( attrOriginal == null) { |
433 |
createIndex = true;
|
434 |
} else {
|
435 |
if( attrOriginal.isIndexed() ) {
|
436 |
createIndex = false;
|
437 |
} else {
|
438 |
createIndex = true;
|
439 |
} |
440 |
} |
441 |
} |
442 |
if( createIndex ) {
|
443 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
444 |
if( attrTarget.getType()==DataTypes.GEOMETRY ) {
|
445 |
sqlbuilder.create_index().spatial(); |
446 |
} |
447 |
sqlbuilder.create_index().if_not_exist(); |
448 |
sqlbuilder.create_index().name("idx_" + table + "_" + attrTarget.getName()); |
449 |
sqlbuilder.create_index().column(attrTarget.getName()); |
450 |
sqlbuilder.create_index().table() |
451 |
.database(this.table.getDatabase())
|
452 |
.schema(this.table.getSchema())
|
453 |
.name(this.table.getTable());
|
454 |
sqlbuilder.setProperties( |
455 |
Variable.class, |
456 |
PROP_TABLE, table |
457 |
); |
458 |
sqls.addAll(sqlbuilder.create_index().toStrings()); |
459 |
} |
460 |
} |
461 |
return sqls;
|
462 |
} |
463 |
|
464 |
public void performUpdateTable(Connection conn, |
465 |
TableReference table, |
466 |
FeatureType original, |
467 |
FeatureType target) throws DataException {
|
468 |
|
469 |
ArrayList<String> sqls = new ArrayList<>(); |
470 |
|
471 |
sqls.addAll(buildUpdateTableSQL(table, original, target)); |
472 |
sqls.addAll(buildCreateIndexSQL(table, original, target)); |
473 |
|
474 |
if( !CollectionUtils.isEmpty(sqls) ) {
|
475 |
Statement st = null; |
476 |
try {
|
477 |
st = conn.createStatement(); |
478 |
for (String sql : sqls) { |
479 |
if( !StringUtils.isBlank(sql) ) {
|
480 |
JDBCUtils.execute(st, sql); |
481 |
} |
482 |
} |
483 |
} catch (SQLException e) { |
484 |
throw new JDBCSQLException(e); |
485 |
} finally {
|
486 |
JDBCUtils.closeQuietly(st); |
487 |
} |
488 |
} |
489 |
} |
490 |
} |