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 / FetchFeatureTypeOperation.java @ 44198
History | View | Annotate | Download (14.9 KB)
1 | 43020 | jjdelcerro | package org.gvsig.fmap.dal.store.jdbc2.spi.operations; |
---|---|---|---|
2 | |||
3 | import java.sql.Connection; |
||
4 | import java.sql.DatabaseMetaData; |
||
5 | import java.sql.ResultSet; |
||
6 | import java.sql.ResultSetMetaData; |
||
7 | import java.sql.SQLException; |
||
8 | import java.sql.Statement; |
||
9 | import java.util.ArrayList; |
||
10 | import java.util.List; |
||
11 | import org.apache.commons.collections.CollectionUtils; |
||
12 | import org.apache.commons.lang3.StringUtils; |
||
13 | import org.cresques.cts.IProjection; |
||
14 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder; |
15 | import org.gvsig.expressionevaluator.ExpressionBuilder.Variable; |
||
16 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.DataTypes; |
17 | import org.gvsig.fmap.dal.exception.DataException; |
||
18 | import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
||
19 | import org.gvsig.fmap.dal.feature.EditableFeatureType; |
||
20 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
21 | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
||
22 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
23 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
24 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_FEATURE_TYPE; |
25 | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
||
26 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.Geometry; |
27 | import org.gvsig.fmap.geom.GeometryLocator; |
||
28 | import org.gvsig.fmap.geom.type.GeometryType; |
||
29 | 44058 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.operations.AbstractConnectionOperation.LOGGER; |
30 | 43020 | jjdelcerro | |
31 | public class FetchFeatureTypeOperation extends AbstractConnectionOperation { |
||
32 | private final EditableFeatureType featureType; |
||
33 | 44058 | jjdelcerro | private final TableReference table; |
34 | 43020 | jjdelcerro | private final List<String> primaryKeys; |
35 | private final String defaultGeometryColumn; |
||
36 | private final IProjection crs; |
||
37 | |||
38 | public FetchFeatureTypeOperation(
|
||
39 | JDBCHelper helper |
||
40 | ) { |
||
41 | 44058 | jjdelcerro | this(helper, null, null, null, null, null); |
42 | 43020 | jjdelcerro | } |
43 | |||
44 | public FetchFeatureTypeOperation(
|
||
45 | JDBCHelper helper, |
||
46 | EditableFeatureType featureType, |
||
47 | 43377 | jjdelcerro | String defaultGeometryColumn,
|
48 | IProjection crs |
||
49 | ) { |
||
50 | 44058 | jjdelcerro | this(helper, featureType, null, null, defaultGeometryColumn, crs); |
51 | 43377 | jjdelcerro | } |
52 | |||
53 | public FetchFeatureTypeOperation(
|
||
54 | JDBCHelper helper, |
||
55 | EditableFeatureType featureType, |
||
56 | 44058 | jjdelcerro | TableReference table, |
57 | 43020 | jjdelcerro | List<String> primaryKeys, |
58 | String defaultGeometryColumn,
|
||
59 | IProjection crs |
||
60 | ) { |
||
61 | super(helper);
|
||
62 | this.featureType = featureType;
|
||
63 | this.table = table;
|
||
64 | this.primaryKeys = primaryKeys;
|
||
65 | this.defaultGeometryColumn = defaultGeometryColumn;
|
||
66 | this.crs = crs;
|
||
67 | } |
||
68 | |||
69 | @Override
|
||
70 | public final Object perform(Connection conn) throws DataException { |
||
71 | 44058 | jjdelcerro | this.fetch(featureType, conn, table,
|
72 | 43020 | jjdelcerro | primaryKeys, defaultGeometryColumn, crs |
73 | ); |
||
74 | return true; |
||
75 | } |
||
76 | |||
77 | 44058 | jjdelcerro | protected TableReference getTable() {
|
78 | 43114 | jjdelcerro | return this.table; |
79 | } |
||
80 | |||
81 | 43020 | jjdelcerro | public void fetch( |
82 | EditableFeatureType featureType, |
||
83 | Connection conn,
|
||
84 | 44058 | jjdelcerro | TableReference table, |
85 | 43020 | jjdelcerro | List<String> pks, |
86 | String defaultGeometryColumn,
|
||
87 | IProjection crs |
||
88 | ) throws DataException {
|
||
89 | |||
90 | Statement st = null; |
||
91 | ResultSet rs = null; |
||
92 | try {
|
||
93 | if (CollectionUtils.isEmpty(pks)) {
|
||
94 | 44058 | jjdelcerro | if (!table.hasSubquery()) {
|
95 | pks = this.getPrimaryKeysFromMetadata(conn, null, table.getSchema(), table.getTable()); |
||
96 | 43020 | jjdelcerro | if (CollectionUtils.isEmpty(pks)) {
|
97 | 44058 | jjdelcerro | pks = getPrimaryKeysFromInformationSchema(conn, null, table.getSchema(), table.getTable());
|
98 | 43020 | jjdelcerro | } |
99 | } |
||
100 | } |
||
101 | |||
102 | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
||
103 | sqlbuilder.select().column().all(); |
||
104 | 44058 | jjdelcerro | sqlbuilder.select().from().table() |
105 | .database(this.table.getDatabase())
|
||
106 | .schema(this.table.getSchema())
|
||
107 | .name(this.table.getTable());
|
||
108 | sqlbuilder.select().from().subquery(this.table.getSubquery());
|
||
109 | 43020 | jjdelcerro | sqlbuilder.select().limit(1);
|
110 | |||
111 | st = conn.createStatement(); |
||
112 | st.setFetchSize(1);
|
||
113 | rs = JDBCUtils.executeQuery(st, sqlbuilder.toString()); |
||
114 | ResultSetMetaData rsMetadata = rs.getMetaData();
|
||
115 | |||
116 | 43377 | jjdelcerro | fetchFeatureTypeFromMetadata(conn, rsMetadata, pks); |
117 | 43020 | jjdelcerro | |
118 | } catch (SQLException ex) { |
||
119 | throw new RuntimeException("Can't fecth feature type.",ex); |
||
120 | } finally {
|
||
121 | JDBCUtils.closeQuietly(rs); |
||
122 | JDBCUtils.closeQuietly(st); |
||
123 | } |
||
124 | 43377 | jjdelcerro | } |
125 | |||
126 | public void fetchFeatureTypeFromMetadata(Connection conn, ResultSetMetaData rsMetadata) throws SQLException { |
||
127 | this.fetchFeatureTypeFromMetadata(conn, rsMetadata, new ArrayList<String>()); |
||
128 | } |
||
129 | 43020 | jjdelcerro | |
130 | 43377 | jjdelcerro | protected void fetchFeatureTypeFromMetadata(Connection conn, ResultSetMetaData rsMetadata, List<String> pks) throws SQLException { |
131 | int i;
|
||
132 | int geometriesColumns = 0; |
||
133 | String lastGeometry = null; |
||
134 | |||
135 | EditableFeatureAttributeDescriptor attr; |
||
136 | boolean firstGeometryAttrFound = false; |
||
137 | for (i = 1; i <= rsMetadata.getColumnCount(); i++) { |
||
138 | attr = getAttributeFromMetadata(featureType, conn, rsMetadata, i); |
||
139 | if ( isInPrimaryKeys(pks,attr) ) {
|
||
140 | attr.setIsPrimaryKey(true);
|
||
141 | } |
||
142 | if (attr.getType() == DataTypes.GEOMETRY) {
|
||
143 | geometriesColumns++; |
||
144 | lastGeometry = attr.getName(); |
||
145 | // Set the default geometry attribute if it is the one
|
||
146 | // given as parameter or it is the first one, just in case.
|
||
147 | if (!firstGeometryAttrFound || lastGeometry.equals(defaultGeometryColumn)) {
|
||
148 | firstGeometryAttrFound = true;
|
||
149 | featureType.setDefaultGeometryAttributeName(lastGeometry); |
||
150 | } |
||
151 | } |
||
152 | |||
153 | } |
||
154 | if (defaultGeometryColumn == null && geometriesColumns == 1) { |
||
155 | featureType.setDefaultGeometryAttributeName(lastGeometry); |
||
156 | } |
||
157 | |||
158 | 43020 | jjdelcerro | if (crs != null && featureType.getDefaultGeometryAttribute() != null) { |
159 | ((EditableFeatureAttributeDescriptor) featureType.getDefaultGeometryAttribute()).setSRS(crs); |
||
160 | } |
||
161 | } |
||
162 | |||
163 | 43114 | jjdelcerro | protected boolean isInPrimaryKeys(List<String> pks, EditableFeatureAttributeDescriptor attr) { |
164 | 43786 | jjdelcerro | if( pks == null || attr == null ) { |
165 | return false; |
||
166 | } |
||
167 | 43355 | jjdelcerro | // En algunos gestores de BBDD, los nombres obtenidos de las pks de los
|
168 | // metadados no coinciden con los nombres de los campos ya que unos estan
|
||
169 | // en mayusculas y otros en minusculas, asi que en lugar de usar un "contains"
|
||
170 | // nos los recorremos y comparamos con IgnoreCase.
|
||
171 | for (String pk : pks) { |
||
172 | if( StringUtils.equalsIgnoreCase(pk, attr.getName()) ) {
|
||
173 | return true; |
||
174 | } |
||
175 | } |
||
176 | return false; |
||
177 | 43114 | jjdelcerro | } |
178 | |||
179 | 43020 | jjdelcerro | protected List<String> getPrimaryKeysFromMetadata( |
180 | Connection conn,
|
||
181 | String catalog,
|
||
182 | String schema,
|
||
183 | String table) throws SQLException { |
||
184 | |||
185 | ResultSet rsPrimaryKeys = null; |
||
186 | ResultSet rs = null; |
||
187 | try {
|
||
188 | DatabaseMetaData metadata = conn.getMetaData();
|
||
189 | rs = metadata.getTables(catalog, schema, table, null);
|
||
190 | |||
191 | if (!rs.next()) {
|
||
192 | // No tables found with default values, ignoring catalog
|
||
193 | rs.close(); |
||
194 | catalog = null;
|
||
195 | schema = null;
|
||
196 | rs = metadata.getTables(catalog, schema, table, null);
|
||
197 | if (!rs.next()) {
|
||
198 | // table not found
|
||
199 | return null; |
||
200 | } else if (rs.next()) { |
||
201 | // More that one, cant identify
|
||
202 | return null; |
||
203 | } |
||
204 | |||
205 | } else if (rs.next()) { |
||
206 | // More that one, cant identify
|
||
207 | return null; |
||
208 | } |
||
209 | rsPrimaryKeys = metadata.getPrimaryKeys(catalog, schema, table); |
||
210 | List pks = new ArrayList(); |
||
211 | while (rsPrimaryKeys.next()) {
|
||
212 | pks.add(rsPrimaryKeys.getString("COLUMN_NAME"));
|
||
213 | } |
||
214 | return pks;
|
||
215 | |||
216 | } catch (SQLException e) { |
||
217 | return null; |
||
218 | |||
219 | } finally {
|
||
220 | JDBCUtils.closeQuietly(rs); |
||
221 | JDBCUtils.closeQuietly(rsPrimaryKeys); |
||
222 | } |
||
223 | |||
224 | } |
||
225 | |||
226 | protected List<String> getPrimaryKeysFromInformationSchema( |
||
227 | Connection conn,
|
||
228 | String catalog,
|
||
229 | String schema,
|
||
230 | String table) throws SQLException { |
||
231 | |||
232 | 44058 | jjdelcerro | String sql = getSQLToRetrievePrimaryKeysFromInformationSchema(catalog, schema, table);
|
233 | |||
234 | Statement st = null; |
||
235 | ResultSet rs = null; |
||
236 | List<String> pks = new ArrayList(); |
||
237 | try {
|
||
238 | st = conn.createStatement(); |
||
239 | rs = JDBCUtils.executeQuery(st, sql); |
||
240 | while (rs.next()) {
|
||
241 | pks.add(rs.getString(1));
|
||
242 | } |
||
243 | if (pks.isEmpty()) {
|
||
244 | return null; |
||
245 | } |
||
246 | return pks;
|
||
247 | |||
248 | } catch (Exception ex) { |
||
249 | return pks;
|
||
250 | |||
251 | } finally {
|
||
252 | JDBCUtils.closeQuietly(rs); |
||
253 | JDBCUtils.closeQuietly(st); |
||
254 | } |
||
255 | } |
||
256 | |||
257 | protected String getSQLToRetrievePrimaryKeysFromInformationSchema( |
||
258 | String catalog,
|
||
259 | String schema,
|
||
260 | String table
|
||
261 | ) throws SQLException { |
||
262 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
263 | 44198 | jjdelcerro | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
264 | 43020 | jjdelcerro | |
265 | sqlbuilder.select().column().name("COLUMN_NAME");
|
||
266 | sqlbuilder.select().column().name("CONSTRAINT_TYPE");
|
||
267 | sqlbuilder.select().from().custom( |
||
268 | "INFORMATION_SCHEMA.table_constraints t_cons "
|
||
269 | + "inner join INFORMATION_SCHEMA.key_column_usage c on "
|
||
270 | + "c.constraint_catalog = t_cons.constraint_catalog and "
|
||
271 | + "c.table_schema = t_cons.table_schema and "
|
||
272 | + "c.table_name = t_cons.table_name and "
|
||
273 | + "c.constraint_name = t_cons.constraint_name "
|
||
274 | ); |
||
275 | sqlbuilder.select().where().set( |
||
276 | 44198 | jjdelcerro | expbuilder.like( |
277 | expbuilder.custom("c.TABLE_NAME"),
|
||
278 | expbuilder.constant(table) |
||
279 | 43020 | jjdelcerro | ) |
280 | ); |
||
281 | if (schema != null) { |
||
282 | sqlbuilder.select().where().and( |
||
283 | 44198 | jjdelcerro | expbuilder.like( |
284 | expbuilder.custom("c.TABLE_SCHEMA"),
|
||
285 | expbuilder.constant(schema) |
||
286 | 43020 | jjdelcerro | ) |
287 | ); |
||
288 | } |
||
289 | if (catalog != null) { |
||
290 | sqlbuilder.select().where().and( |
||
291 | 44198 | jjdelcerro | expbuilder.like( |
292 | expbuilder.custom("c.CONSTRAINT_CATALOG"),
|
||
293 | expbuilder.constant(catalog) |
||
294 | 43020 | jjdelcerro | ) |
295 | ); |
||
296 | } |
||
297 | sqlbuilder.select().where().and( |
||
298 | 44198 | jjdelcerro | expbuilder.eq( |
299 | expbuilder.column("CONSTRAINT_TYPE"),
|
||
300 | expbuilder.constant("PRIMARY KEY")
|
||
301 | 43020 | jjdelcerro | ) |
302 | ); |
||
303 | 44058 | jjdelcerro | return sqlbuilder.toString();
|
304 | 43020 | jjdelcerro | } |
305 | 44058 | jjdelcerro | |
306 | |||
307 | 43020 | jjdelcerro | protected EditableFeatureAttributeDescriptor getAttributeFromMetadata(
|
308 | EditableFeatureType type, |
||
309 | Connection conn,
|
||
310 | ResultSetMetaData rsMetadata,
|
||
311 | int colIndex
|
||
312 | ) throws SQLException { |
||
313 | |||
314 | EditableFeatureAttributeDescriptor attr = type.add( |
||
315 | rsMetadata.getColumnName(colIndex), |
||
316 | this.getDataTypeFromMetadata(rsMetadata, colIndex)
|
||
317 | ); |
||
318 | attr.setAllowNull( |
||
319 | rsMetadata.isNullable(colIndex) == ResultSetMetaData.columnNullable
|
||
320 | ); |
||
321 | attr.setIsAutomatic(rsMetadata.isAutoIncrement(colIndex)); |
||
322 | attr.setIsReadOnly(rsMetadata.isReadOnly(colIndex)); |
||
323 | attr.setPrecision(rsMetadata.getPrecision(colIndex)); |
||
324 | attr.setSize(rsMetadata.getColumnDisplaySize(colIndex)); |
||
325 | switch(attr.getType()) {
|
||
326 | case DataTypes.OBJECT:
|
||
327 | attr.setAdditionalInfo( |
||
328 | "SQLType",
|
||
329 | rsMetadata.getColumnType(colIndex) |
||
330 | ); |
||
331 | attr.setAdditionalInfo( |
||
332 | "SQLTypeName",
|
||
333 | rsMetadata.getColumnTypeName(colIndex) |
||
334 | ); |
||
335 | break;
|
||
336 | case DataTypes.GEOMETRY:
|
||
337 | 43114 | jjdelcerro | this.fetchGeometryTypeAndSRS(attr, rsMetadata, colIndex);
|
338 | 43020 | jjdelcerro | break;
|
339 | } |
||
340 | return attr;
|
||
341 | } |
||
342 | |||
343 | protected int getDataTypeFromMetadata( |
||
344 | ResultSetMetaData rsMetadata,
|
||
345 | int colIndex
|
||
346 | ) throws SQLException { |
||
347 | |||
348 | switch (rsMetadata.getColumnType(colIndex)) {
|
||
349 | case java.sql.Types.INTEGER:
|
||
350 | return DataTypes.INT;
|
||
351 | |||
352 | case java.sql.Types.BIGINT:
|
||
353 | return DataTypes.LONG;
|
||
354 | |||
355 | case java.sql.Types.REAL:
|
||
356 | return DataTypes.DOUBLE;
|
||
357 | |||
358 | case java.sql.Types.DOUBLE:
|
||
359 | return DataTypes.DOUBLE;
|
||
360 | |||
361 | case java.sql.Types.CHAR:
|
||
362 | return DataTypes.STRING;
|
||
363 | |||
364 | case java.sql.Types.VARCHAR:
|
||
365 | case java.sql.Types.LONGVARCHAR:
|
||
366 | return DataTypes.STRING;
|
||
367 | |||
368 | case java.sql.Types.FLOAT:
|
||
369 | return DataTypes.DOUBLE;
|
||
370 | |||
371 | case java.sql.Types.NUMERIC:
|
||
372 | return DataTypes.DOUBLE;
|
||
373 | |||
374 | case java.sql.Types.DECIMAL:
|
||
375 | return DataTypes.FLOAT;
|
||
376 | |||
377 | case java.sql.Types.DATE:
|
||
378 | return DataTypes.DATE;
|
||
379 | |||
380 | case java.sql.Types.TIME:
|
||
381 | return DataTypes.TIME;
|
||
382 | |||
383 | case java.sql.Types.TIMESTAMP:
|
||
384 | return DataTypes.TIMESTAMP;
|
||
385 | |||
386 | case java.sql.Types.BOOLEAN:
|
||
387 | case java.sql.Types.BIT:
|
||
388 | return DataTypes.BOOLEAN;
|
||
389 | |||
390 | case java.sql.Types.BLOB:
|
||
391 | case java.sql.Types.BINARY:
|
||
392 | case java.sql.Types.LONGVARBINARY:
|
||
393 | return DataTypes.BYTEARRAY;
|
||
394 | |||
395 | default:
|
||
396 | String typeName = rsMetadata.getColumnTypeName(colIndex);
|
||
397 | if( "geometry".equalsIgnoreCase(typeName) ) { |
||
398 | return DataTypes.GEOMETRY;
|
||
399 | } |
||
400 | return DataTypes.OBJECT;
|
||
401 | } |
||
402 | } |
||
403 | |||
404 | 43114 | jjdelcerro | /**
|
405 | * Inicializa el tipo, subtipo y SRS del attributo de tipo geometria.
|
||
406 | *
|
||
407 | * @param attr
|
||
408 | * @param rsMetadata
|
||
409 | * @param colIndex
|
||
410 | */
|
||
411 | protected void fetchGeometryTypeAndSRS( |
||
412 | EditableFeatureAttributeDescriptor attr, |
||
413 | 43020 | jjdelcerro | ResultSetMetaData rsMetadata,
|
414 | 43114 | jjdelcerro | int colIndex
|
415 | ) { |
||
416 | if( attr.getType()!=DataTypes.GEOMETRY ) {
|
||
417 | return;
|
||
418 | } |
||
419 | try {
|
||
420 | GeometryType geomType = GeometryLocator.getGeometryManager().getGeometryType( |
||
421 | Geometry.TYPES.GEOMETRY, |
||
422 | Geometry.SUBTYPES.GEOM2D |
||
423 | ); |
||
424 | attr.setGeometryType(geomType); |
||
425 | attr.setSRS(null);
|
||
426 | } catch (Exception ex) { |
||
427 | 44058 | jjdelcerro | LOGGER.warn("Can't get default geometry type.",ex);
|
428 | 43114 | jjdelcerro | } |
429 | 43020 | jjdelcerro | } |
430 | 43114 | jjdelcerro | |
431 | 43020 | jjdelcerro | } |