001: package org.geotools.data.jdbc;
002:
003: import java.io.IOException;
004: import java.sql.Connection;
005: import java.sql.DatabaseMetaData;
006: import java.sql.ResultSet;
007: import java.sql.SQLException;
008: import java.sql.Statement;
009: import java.util.ArrayList;
010: import java.util.List;
011: import java.util.logging.Level;
012:
013: import org.geotools.feature.AttributeType;
014: import org.geotools.feature.FeatureType;
015: import org.opengis.feature.type.TypeName;
016:
017: /**
018: * Collection of convenience methods for jdbc datastores.
019: *
020: * @author Justin Deoliveira, The Open Planning Project
021: *
022: */
023: public class JDBCUtils {
024:
025: /**
026: * Creates a list of the type names ( table names )
027: * <p>
028: * The list of names is generated from the database metadata obtained from
029: * the database connection provided via the datastore.
030: * </p>
031: *
032: * @return A list of {@link TypeName}.
033: */
034: public static final List typeNames(JDBCDataStore dataStore)
035: throws Exception {
036: Connection conn = dataStore.connection();
037:
038: try {
039: DatabaseMetaData metaData = conn.getMetaData();
040: ResultSet tables = metaData.getTables(null, dataStore
041: .getDatabaseSchema(), "%", null);
042:
043: /*
044: * <LI><B>TABLE_CAT</B> String => table catalog (may be <code>null</code>)
045: * <LI><B>TABLE_SCHEM</B> String => table schema (may be <code>null</code>)
046: * <LI><B>TABLE_NAME</B> String => table name
047: * <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
048: * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
049: * "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
050: * <LI><B>REMARKS</B> String => explanatory comment on the table
051: * <LI><B>TYPE_CAT</B> String => the types catalog (may be <code>null</code>)
052: * <LI><B>TYPE_SCHEM</B> String => the types schema (may be <code>null</code>)
053: * <LI><B>TYPE_NAME</B> String => type name (may be <code>null</code>)
054: * <LI><B>SELF_REFERENCING_COL_NAME</B> String => name of the designated
055: * "identifier" column of a typed table (may be <code>null</code>)
056: * <LI><B>REF_GENERATION</B> String => specifies how values in
057: * SELF_REFERENCING_COL_NAME are created. Values are
058: * "SYSTEM", "USER", "DERIVED". (may be <code>null</code>)
059: */
060: List typeNames = new ArrayList();
061:
062: while (tables.next()) {
063: String tableName = tables.getString("TABLE_NAME");
064: typeNames.add(new org.geotools.feature.type.TypeName(
065: tableName));
066: }
067:
068: return typeNames;
069: } finally {
070: conn.close();
071: }
072: }
073:
074: /**
075: * Returns a list of sql type names which correspond to the attribute types
076: * of the provided feature type.
077: *
078: * @param featureType The feature type.
079: * @param dataStore The datastore.
080: *
081: * @return A list of database dependent type names.
082: *
083: * @throws Exception Any I/O errors that occur.
084: */
085: public static final String[] sqlTypeNames(FeatureType featureType,
086: JDBCDataStore dataStore) throws Exception {
087:
088: JDBCTypeBuilder typeBuilder = dataStore.createTypeBuilder();
089:
090: //figure out what the sql types are
091: int[] sqlTypes = new int[featureType.getAttributeCount()];
092:
093: for (int i = 0; i < featureType.getAttributeCount(); i++) {
094: AttributeType attributeType = featureType
095: .getAttributeType(i);
096: Class clazz = attributeType.getType();
097:
098: sqlTypes[i] = typeBuilder.mapping(clazz);
099: }
100:
101: //get metadata about types from the database
102: Connection conn = dataStore.connection();
103:
104: try {
105: DatabaseMetaData metaData = conn.getMetaData();
106:
107: /*
108: *<LI><B>TYPE_NAME</B> String => Type name
109: * <LI><B>DATA_TYPE</B> int => SQL data type from java.sql.Types
110: * <LI><B>PRECISION</B> int => maximum precision
111: * <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
112: * (may be <code>null</code>)
113: * <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
114: (may be <code>null</code>)
115: * <LI><B>CREATE_PARAMS</B> String => parameters used in creating
116: * the type (may be <code>null</code>)
117: * <LI><B>NULLABLE</B> short => can you use NULL for this type.
118: * <UL>
119: * <LI> typeNoNulls - does not allow NULL values
120: * <LI> typeNullable - allows NULL values
121: * <LI> typeNullableUnknown - nullability unknown
122: * </UL>
123: * <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive.
124: * <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
125: * <UL>
126: * <LI> typePredNone - No support
127: * <LI> typePredChar - Only supported with WHERE .. LIKE
128: * <LI> typePredBasic - Supported except for WHERE .. LIKE
129: * <LI> typeSearchable - Supported for all WHERE ..
130: * </UL>
131: * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned.
132: * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value.
133: * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
134: * auto-increment value.
135: * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
136: * (may be <code>null</code>)
137: * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
138: * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
139: * <LI><B>SQL_DATA_TYPE</B> int => unused
140: * <LI><B>SQL_DATETIME_SUB</B> int => unused
141: * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
142: */
143: ResultSet types = metaData.getTypeInfo();
144:
145: try {
146: //figure out the type names that correspond to the sql types
147: String[] sqlTypeNames = new String[sqlTypes.length];
148:
149: while (types.next()) {
150: int sqlType = types.getInt("DATA_TYPE");
151: String sqlTypeName = types.getString("TYPE_NAME");
152:
153: for (int i = 0; i < sqlTypes.length; i++) {
154: if (sqlType == sqlTypes[i]) {
155: sqlTypeNames[i] = sqlTypeName;
156: }
157: }
158: }
159:
160: return sqlTypeNames;
161: } finally {
162: types.close();
163: }
164: } finally {
165: conn.close();
166: }
167: }
168:
169: /**
170: * Builds a feature type for a particular type name / table name.
171: * <p>
172: * THe attributes of the feature type are derived from the database
173: * meta data.
174: * </p>
175: *
176: * @param typeName The name of the type / table.
177: * @param dataStore The datastore.
178: *
179: * @return The built type.
180: *
181: * @throws Exception Any I/O errors that occur.
182: */
183: public static final FeatureType buildFeatureType(TypeName typeName,
184: JDBCDataStore dataStore) throws Exception {
185:
186: JDBCTypeBuilder builder = dataStore.createTypeBuilder();
187:
188: //set up the name
189: builder.setName(typeName.getLocalPart());
190:
191: //set the namespace, if not null
192: if (typeName.getNamespaceURI() != null) {
193: builder.setNamespaceURI(typeName.getNamespaceURI());
194: } else {
195: //use the data store
196: builder.setNamespaceURI(dataStore.getNamespaceURI());
197: }
198:
199: //get metadata about columns from database
200: Connection conn = dataStore.connection();
201:
202: try {
203: DatabaseMetaData metaData = conn.getMetaData();
204:
205: /*
206: * <LI><B>COLUMN_NAME</B> String => column name
207: * <LI><B>DATA_TYPE</B> int => SQL type from java.sql.Types
208: * <LI><B>TYPE_NAME</B> String => Data source dependent type name,
209: * for a UDT the type name is fully qualified
210: * <LI><B>COLUMN_SIZE</B> int => column size. For char or date
211: * types this is the maximum number of characters, for numeric or
212: * decimal types this is precision.
213: * <LI><B>BUFFER_LENGTH</B> is not used.
214: * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
215: * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
216: * <LI><B>NULLABLE</B> int => is NULL allowed.
217: * <UL>
218: * <LI> columnNoNulls - might not allow <code>NULL</code> values
219: * <LI> columnNullable - definitely allows <code>NULL</code> values
220: * <LI> columnNullableUnknown - nullability unknown
221: * </UL>
222: * <LI><B>COLUMN_DEF</B> String => default value (may be <code>null</code>)
223: * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
224: * does not allow NULL values; "YES" means the column might
225: * allow NULL values. An empty string means nobody knows.
226: */
227: ResultSet columns = metaData.getColumns(null, dataStore
228: .getDatabaseSchema(), typeName.getLocalPart(), "%");
229:
230: /*
231: * <LI><B>TABLE_CAT</B> String => table catalog (may be <code>null</code>)
232: * <LI><B>TABLE_SCHEM</B> String => table schema (may be <code>null</code>)
233: * <LI><B>TABLE_NAME</B> String => table name
234: * <LI><B>COLUMN_NAME</B> String => column name
235: * <LI><B>KEY_SEQ</B> short => sequence number within primary key
236: * <LI><B>PK_NAME</B> String => primary key name (may be <code>null</code>)
237: */
238: ResultSet primaryKeys = metaData.getPrimaryKeys(null,
239: dataStore.getDatabaseSchema(), typeName
240: .getLocalPart());
241:
242: try {
243: while (columns.next()) {
244: String name = columns.getString("COLUMN_NAME");
245:
246: //do not include primary key in the type
247: while (primaryKeys.next()) {
248: String keyName = primaryKeys
249: .getString("COLUMN_NAME");
250:
251: if (name.equals(keyName)) {
252: name = null;
253:
254: break;
255: }
256: }
257:
258: primaryKeys.beforeFirst();
259:
260: if (name == null) {
261: continue;
262: }
263:
264: //get the type
265: int binding = columns.getInt("DATA_TYPE");
266:
267: //add the attribute
268: builder.attribute(name, binding);
269: }
270:
271: return builder.feature();
272: } finally {
273: columns.close();
274: primaryKeys.close();
275: }
276: } finally {
277: conn.close();
278: }
279: }
280:
281: /**
282: * Determines the elements of a primary key of a feature type / table.
283: * <p>
284: * The primary key is derived from the database metadata.
285: * </p>
286: *
287: * @param typeName The feature type / table name.
288: * @param dataStore The data store.
289: *
290: * @return The primary key.
291: *
292: * @throws Exception Any I/O errors that occur.
293: */
294: public static final PrimaryKey primaryKey(TypeName typeName,
295: JDBCDataStore dataStore) throws Exception {
296:
297: JDBCTypeBuilder builder = dataStore.createTypeBuilder();
298:
299: //get metadata from database
300: Connection conn = dataStore.connection();
301:
302: try {
303: DatabaseMetaData metaData = conn.getMetaData();
304: ResultSet primaryKey = metaData.getPrimaryKeys(null,
305: dataStore.getDatabaseSchema(), typeName
306: .getLocalPart());
307:
308: /*
309: * <LI><B>TABLE_CAT</B> String => table catalog (may be <code>null</code>)
310: * <LI><B>TABLE_SCHEM</B> String => table schema (may be <code>null</code>)
311: * <LI><B>TABLE_NAME</B> String => table name
312: * <LI><B>COLUMN_NAME</B> String => column name
313: * <LI><B>KEY_SEQ</B> short => sequence number within primary key
314: * <LI><B>PK_NAME</B> String => primary key name (may be <code>null</code>)
315: */
316: ArrayList keyColumns = new ArrayList();
317:
318: while (primaryKey.next()) {
319: String columnName = primaryKey.getString("COLUMN_NAME");
320:
321: //look up the type ( should only be one row )
322: ResultSet columns = metaData.getColumns(null, dataStore
323: .getDatabaseSchema(), typeName.getLocalPart(),
324: columnName);
325: columns.next();
326:
327: int binding = columns.getInt("DATA_TYPE");
328: Class columnType = builder.mapping(binding);
329:
330: keyColumns.add(new PrimaryKey.Column(columnName,
331: columnType));
332: }
333:
334: return new PrimaryKey((PrimaryKey.Column[]) keyColumns
335: .toArray(new PrimaryKey.Column[keyColumns.size()]));
336: } finally {
337: conn.close();
338: }
339: }
340:
341: /**
342: * Utility method to safely execute an sql statement.
343: * <p>
344: * This method ensures that statements are properly closed, even when
345: * exceptions occur.
346: * </p>
347: * <p>
348: * Any {@link SQLException}'s generated are wrapped in {@link IOException}.
349: * </p>
350: * <p>
351: * If an error occurs closing the statement n it is logged and not rethrown.
352: * </p>
353: * @param connection The database connection
354: * @param runner The code block to execute.
355: */
356: public static Object statement(Connection connection,
357: JDBCRunnable runnable) throws IOException {
358:
359: //create a statement
360: Statement st = null;
361: try {
362: st = connection.createStatement();
363:
364: //run it
365: return runnable.run(st);
366: } catch (SQLException e) {
367: throw (IOException) new IOException().initCause(e);
368: } finally {
369: if (st != null) {
370: try {
371: st.close();
372: } catch (SQLException e) {
373: String msg = "Error occurred closing statement.";
374: JDBCDataStore.LOGGER.log(Level.WARNING, msg, e);
375: }
376: }
377: }
378: }
379:
380: /**
381: * Utility method to safely execute an sql statement.
382: * <p>
383: * This method will obtain a new connection from the datastore, and close it
384: * when it is done, therefore it is not suitable for executing a statement
385: * that is intended to be part of a transaction. Use {@link #statement(Connection, JDBCRunnable)}
386: * for this case.
387: * </p>
388: * <p>
389: * This method ensures that statements and connections are properly closed,
390: * even when exceptions occur.
391: * </p>
392: * <p>
393: * Any {@link SQLException}'s generated are wrapped in {@link IOException}.
394: * </p>
395: * <p>
396: * If an error occurs closing the statement, or connection it is logged and
397: * not rethrown.
398: * </p>
399: * @param dataStore The datastore.
400: * @param runner The code block to execute.
401: */
402: public static Object statement(JDBCDataStore dataStore,
403: JDBCRunnable runnable) throws IOException {
404:
405: Connection conn = null;
406: try {
407: //grab a connection
408: conn = dataStore.connection();
409:
410: //execute the statement
411: return statement(conn, runnable);
412: } finally {
413: if (conn != null) {
414: try {
415: conn.close();
416: } catch (SQLException e) {
417: String msg = "Error occurred closing connection.";
418: JDBCDataStore.LOGGER.log(Level.WARNING, msg, e);
419: }
420: }
421:
422: }
423: }
424: }
|