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 @ 43093
History | View | Annotate | Download (11.1 KB)
1 | 43020 | jjdelcerro | 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.Iterator; |
||
8 | import org.gvsig.fmap.dal.DataTypes; |
||
9 | import org.gvsig.fmap.dal.exception.DataException; |
||
10 | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
||
11 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
12 | import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
||
13 | import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices; |
||
14 | import org.gvsig.fmap.dal.feature.spi.FeatureStoreProvider; |
||
15 | import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
||
16 | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
||
17 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
||
18 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
||
19 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCUpdateWithoutChangesException; |
||
20 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
21 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
22 | |||
23 | public class PerformChangesOperation extends AbstractConnectionOperation { |
||
24 | |||
25 | private final String dbName; |
||
26 | private final String schemaName; |
||
27 | private final String tableName; |
||
28 | private final FeatureType featureType; |
||
29 | private final Iterator<FeatureReferenceProviderServices> deleteds; |
||
30 | private final Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged; |
||
31 | private final Iterator<FeatureProvider> updateds; |
||
32 | private final Iterator<FeatureProvider> inserteds; |
||
33 | |||
34 | private boolean typeChanged = false; |
||
35 | |||
36 | public PerformChangesOperation(JDBCHelper helper) {
|
||
37 | this(helper, null, null, null, null, null, null, null, null); |
||
38 | } |
||
39 | |||
40 | public PerformChangesOperation(JDBCHelper helper,
|
||
41 | String dbName,
|
||
42 | String schemaName,
|
||
43 | String tableName,
|
||
44 | FeatureType featureType, |
||
45 | Iterator<FeatureReferenceProviderServices> deleteds,
|
||
46 | Iterator<FeatureProvider> inserteds,
|
||
47 | Iterator<FeatureProvider> updateds,
|
||
48 | Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged) {
|
||
49 | super(helper);
|
||
50 | this.dbName = dbName;
|
||
51 | this.deleteds = deleteds;
|
||
52 | this.inserteds = inserteds;
|
||
53 | this.updateds = updateds;
|
||
54 | this.schemaName = schemaName;
|
||
55 | this.tableName = tableName;
|
||
56 | this.featureType = featureType;
|
||
57 | this.featureTypesChanged = featureTypesChanged;
|
||
58 | } |
||
59 | |||
60 | public boolean isTypeChanged() { |
||
61 | return typeChanged;
|
||
62 | } |
||
63 | |||
64 | @Override
|
||
65 | public final Object perform(Connection conn) throws DataException { |
||
66 | if (featureTypesChanged.hasNext()) {
|
||
67 | FeatureStoreProvider.FeatureTypeChanged item = featureTypesChanged.next(); |
||
68 | this.performUpdateTable(conn, tableName, item.getSource(), item.getTarget());
|
||
69 | typeChanged = true;
|
||
70 | } else {
|
||
71 | typeChanged = false;
|
||
72 | } |
||
73 | if (deleteds.hasNext()) {
|
||
74 | performDeletes(conn, dbName, schemaName, tableName, featureType, deleteds); |
||
75 | } |
||
76 | if (updateds.hasNext()) {
|
||
77 | performUpdates(conn, dbName, schemaName, tableName, featureType, updateds); |
||
78 | } |
||
79 | if (inserteds.hasNext()) {
|
||
80 | performInserts(conn, dbName, schemaName, tableName, featureType, inserteds); |
||
81 | } |
||
82 | return true; |
||
83 | } |
||
84 | |||
85 | public void performDeletes(Connection conn, |
||
86 | String database,
|
||
87 | String schema,
|
||
88 | String table,
|
||
89 | FeatureType type, |
||
90 | Iterator<FeatureReferenceProviderServices> deleteds) throws DataException { |
||
91 | |||
92 | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
||
93 | sqlbuilder.delete().table().database(database).schema(schema).name(table); |
||
94 | for (FeatureAttributeDescriptor attr : type) {
|
||
95 | if (attr.isPrimaryKey()) {
|
||
96 | sqlbuilder.delete().where().and( |
||
97 | sqlbuilder.eq( |
||
98 | sqlbuilder.column(attr.getName()), |
||
99 | sqlbuilder.parameter(attr.getName()).as_variable() |
||
100 | ) |
||
101 | ); |
||
102 | } |
||
103 | } |
||
104 | if (!sqlbuilder.delete().has_where() ) {
|
||
105 | throw new RuntimeException("Operation requires missing pk"); |
||
106 | } |
||
107 | |||
108 | PreparedStatement st = null; |
||
109 | String sql = sqlbuilder.delete().toString();
|
||
110 | try {
|
||
111 | st = conn.prepareStatement(sql); |
||
112 | while (deleteds.hasNext()) {
|
||
113 | FeatureProvider featureProvider = (FeatureProvider) deleteds.next(); |
||
114 | sqlbuilder.setParameters(st, featureProvider); |
||
115 | int nAffected = JDBCUtils.executeUpdate(st,sql);
|
||
116 | if (nAffected == 0) { |
||
117 | throw new JDBCUpdateWithoutChangesException( |
||
118 | sqlbuilder.delete().toString(), |
||
119 | null
|
||
120 | ); |
||
121 | } |
||
122 | if (nAffected > 1) { |
||
123 | logger.warn("Remove statement affectst to {} rows ( {} )",
|
||
124 | nAffected, sql |
||
125 | ); |
||
126 | } |
||
127 | } |
||
128 | } catch (SQLException e) { |
||
129 | throw new JDBCSQLException(e); |
||
130 | } finally {
|
||
131 | JDBCUtils.closeQuietly(st); |
||
132 | } |
||
133 | } |
||
134 | |||
135 | public void performInserts(Connection conn, |
||
136 | String database,
|
||
137 | String schema,
|
||
138 | String table,
|
||
139 | FeatureType type, |
||
140 | Iterator<FeatureProvider> inserteds) throws DataException { |
||
141 | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
||
142 | |||
143 | sqlbuilder.insert().table().database(database).schema(schema).name(table); |
||
144 | for (FeatureAttributeDescriptor attr : type) {
|
||
145 | if (attr.getType() == DataTypes.GEOMETRY) {
|
||
146 | sqlbuilder.insert().column().name(attr.getName()).with_value( |
||
147 | 43093 | jjdelcerro | sqlbuilder.parameter(attr.getName()).as_geometry_variable().srs( |
148 | sqlbuilder.parameter().value(attr.getSRS()).as_constant() |
||
149 | 43020 | jjdelcerro | ) |
150 | ); |
||
151 | } else {
|
||
152 | sqlbuilder.insert().column().name(attr.getName()).with_value( |
||
153 | sqlbuilder.parameter(attr.getName()) |
||
154 | ); |
||
155 | } |
||
156 | } |
||
157 | |||
158 | PreparedStatement st;
|
||
159 | String sql = sqlbuilder.insert().toString();
|
||
160 | try {
|
||
161 | st = conn.prepareStatement(sql); |
||
162 | while (inserteds.hasNext()) {
|
||
163 | FeatureProvider feature = inserteds.next(); |
||
164 | sqlbuilder.setParameters(st, feature); |
||
165 | if (JDBCUtils.executeUpdate(st,sql) == 0) { |
||
166 | throw new JDBCExecuteSQLException( |
||
167 | sqlbuilder.insert().toString(), |
||
168 | null
|
||
169 | ); |
||
170 | } |
||
171 | |||
172 | } |
||
173 | } catch (JDBCExecuteSQLException ex) {
|
||
174 | throw ex;
|
||
175 | } catch (Exception ex) { |
||
176 | throw new JDBCExecuteSQLException(sql,ex); |
||
177 | } |
||
178 | } |
||
179 | |||
180 | public void performUpdates(Connection conn, |
||
181 | String database,
|
||
182 | String schema,
|
||
183 | String table,
|
||
184 | FeatureType type, |
||
185 | Iterator<FeatureProvider> updateds) throws DataException { |
||
186 | |||
187 | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
||
188 | sqlbuilder.update().table().database(database).schema(schema).name(table); |
||
189 | for (FeatureAttributeDescriptor attr : type) {
|
||
190 | if (attr.isPrimaryKey()) {
|
||
191 | sqlbuilder.update().where().and( |
||
192 | sqlbuilder.eq( |
||
193 | sqlbuilder.column(attr.getName()), |
||
194 | sqlbuilder.parameter(attr.getName()).as_variable() |
||
195 | ) |
||
196 | ); |
||
197 | } if ( ! attr.isAutomatic() && !attr.isReadOnly() ) {
|
||
198 | if (attr.getType() == DataTypes.GEOMETRY) {
|
||
199 | sqlbuilder.update().column().name(attr.getName()).with_value( |
||
200 | 43093 | jjdelcerro | sqlbuilder.parameter(attr.getName()).as_geometry_variable().srs( |
201 | sqlbuilder.parameter().value(attr.getSRS()).as_constant() |
||
202 | 43020 | jjdelcerro | ) |
203 | ); |
||
204 | } else {
|
||
205 | sqlbuilder.update().column().name(attr.getName()).with_value( |
||
206 | sqlbuilder.parameter(attr.getName()).as_variable() |
||
207 | ); |
||
208 | } |
||
209 | } |
||
210 | } |
||
211 | if (!sqlbuilder.update().has_where() ) {
|
||
212 | throw new RuntimeException("Operation requires missing pk"); |
||
213 | } |
||
214 | |||
215 | PreparedStatement st = null; |
||
216 | String sql = sqlbuilder.update().toString();
|
||
217 | try {
|
||
218 | st = conn.prepareStatement(sql); |
||
219 | while (updateds.hasNext()) {
|
||
220 | FeatureProvider featureProvider = (FeatureProvider) updateds.next(); |
||
221 | sqlbuilder.setParameters(st, featureProvider); |
||
222 | if (JDBCUtils.executeUpdate(st,sql) == 0) { |
||
223 | throw new JDBCUpdateWithoutChangesException(sql,null); |
||
224 | } |
||
225 | } |
||
226 | } catch (SQLException e) { |
||
227 | throw new JDBCSQLException(e); |
||
228 | } finally {
|
||
229 | JDBCUtils.closeQuietly(st); |
||
230 | } |
||
231 | } |
||
232 | |||
233 | public void performUpdateTable(Connection conn, |
||
234 | String table,
|
||
235 | FeatureType original, |
||
236 | FeatureType target) throws DataException {
|
||
237 | |||
238 | SQLBuilderBase sqlbuilder = createSQLBuilder(); |
||
239 | sqlbuilder.update().table().name(table); |
||
240 | |||
241 | for (FeatureAttributeDescriptor attrOrgiginal : original) {
|
||
242 | FeatureAttributeDescriptor attrTarget = target.getAttributeDescriptor( |
||
243 | attrOrgiginal.getName() |
||
244 | ); |
||
245 | if (attrTarget == null) { |
||
246 | sqlbuilder.alter_table().drop_column(attrOrgiginal.getName()); |
||
247 | } else {
|
||
248 | sqlbuilder.alter_table().alter_column( |
||
249 | attrTarget.getName(), |
||
250 | attrTarget.getType(), |
||
251 | attrTarget.getPrecision(), |
||
252 | attrTarget.getSize(), |
||
253 | attrTarget.isPrimaryKey(), |
||
254 | attrTarget.allowNull(), |
||
255 | attrTarget.isAutomatic(), |
||
256 | attrTarget.getDefaultValue() |
||
257 | ); |
||
258 | } |
||
259 | } |
||
260 | for (FeatureAttributeDescriptor attrTarget : target) {
|
||
261 | if (original.getAttributeDescriptor(attrTarget.getName()) == null) { |
||
262 | sqlbuilder.alter_table().add_column( |
||
263 | attrTarget.getName(), |
||
264 | attrTarget.getType(), |
||
265 | attrTarget.getPrecision(), |
||
266 | attrTarget.getSize(), |
||
267 | attrTarget.isPrimaryKey(), |
||
268 | attrTarget.allowNull(), |
||
269 | attrTarget.isAutomatic(), |
||
270 | attrTarget.getDefaultValue() |
||
271 | ); |
||
272 | } |
||
273 | } |
||
274 | Statement st = null; |
||
275 | try {
|
||
276 | st = conn.createStatement(); |
||
277 | for (String sql : sqlbuilder.alter_table().toStrings()) { |
||
278 | JDBCUtils.execute(st, sql); |
||
279 | } |
||
280 | } catch (SQLException e) { |
||
281 | throw new JDBCSQLException(e); |
||
282 | } finally {
|
||
283 | JDBCUtils.closeQuietly(st); |
||
284 | } |
||
285 | } |
||
286 | |||
287 | } |