1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 package org.modeshape.connector.meta.jdbc;
25
26 import java.sql.Connection;
27 import java.sql.DatabaseMetaData;
28 import java.sql.ResultSet;
29 import java.sql.ResultSetMetaData;
30 import java.sql.SQLException;
31 import java.util.HashSet;
32 import java.util.LinkedList;
33 import java.util.List;
34 import java.util.Set;
35 import net.jcip.annotations.Immutable;
36
37
38
39
40
41
42
43 @Immutable
44 public class JdbcMetadataCollector implements MetadataCollector {
45
46 public List<String> getCatalogNames( Connection conn ) throws JdbcMetadataException {
47 List<String> catalogNames = new LinkedList<String>();
48
49 ResultSet catalogs = null;
50 try {
51 DatabaseMetaData dmd = conn.getMetaData();
52 catalogs = dmd.getCatalogs();
53 while (catalogs.next()) {
54 catalogNames.add(catalogs.getString("TABLE_CAT"));
55 }
56 return catalogNames;
57 } catch (SQLException se) {
58 throw new JdbcMetadataException(se);
59 } finally {
60 try {
61 if (catalogs != null) catalogs.close();
62 } catch (Exception ignore) {
63 }
64 }
65 }
66
67 public List<ColumnMetadata> getColumns( Connection conn,
68 String catalogName,
69 String schemaName,
70 String tableName,
71 String columnName ) throws JdbcMetadataException {
72 List<ColumnMetadata> columnData = new LinkedList<ColumnMetadata>();
73
74 ResultSet columns = null;
75 try {
76 DatabaseMetaData dmd = conn.getMetaData();
77
78 if (catalogName == null) catalogName = "";
79 if (schemaName == null) schemaName = "";
80 columns = dmd.getColumns(catalogName, schemaName, tableName, columnName);
81
82 Set<String> columnNames = columnsFor(columns);
83 while (columns.next()) {
84 ColumnMetadata column = new ColumnMetadata(columns.getString("COLUMN_NAME"), columns.getInt("DATA_TYPE"),
85 columns.getString("TYPE_NAME"), columns.getInt("COLUMN_SIZE"),
86 columns.getInt("DECIMAL_DIGITS"), columns.getInt("NUM_PREC_RADIX"),
87 getNullableBoolean(columns, "NULLABLE"), columns.getString("REMARKS"),
88 columns.getString("COLUMN_DEF"), columns.getInt("CHAR_OCTET_LENGTH"),
89 columns.getInt("ORDINAL_POSITION"), getStringIfPresent(columns,
90 "SCOPE_CATLOG",
91 columnNames),
92 getStringIfPresent(columns, "SCOPE_SCHEMA", columnNames),
93 getStringIfPresent(columns, "SCOPE_TABLE", columnNames),
94 getIntegerIfPresent(columns, "SOURCE_DATA_TYPE", columnNames));
95 columnData.add(column);
96
97 }
98
99 return columnData;
100 } catch (SQLException se) {
101 throw new JdbcMetadataException(se);
102 } finally {
103 try {
104 if (columns != null) columns.close();
105 } catch (Exception ignore) {
106 }
107 }
108 }
109
110 public List<String> getSchemaNames( Connection conn,
111 String catalogName ) throws JdbcMetadataException {
112 List<String> schemaNames = new LinkedList<String>();
113
114 ResultSet schemas = null;
115 try {
116 DatabaseMetaData dmd = conn.getMetaData();
117 schemas = dmd.getSchemas();
118
119 Set<String> columns = columnsFor(schemas);
120 boolean hasCatalog = columns.contains(identifierFor(dmd, "TABLE_CATALOG"));
121
122 while (schemas.next()) {
123
124
125
126
127 if (hasCatalog) {
128 String catalogNameForSchema = schemas.getString("TABLE_CATALOG");
129 String schemaName = schemas.getString("TABLE_SCHEM");
130 if ((catalogName == null && catalogNameForSchema == null)
131
132 || (catalogName != null && catalogName.equals(catalogNameForSchema))) {
133
134 schemaNames.add(schemaName);
135 }
136 } else {
137 schemaNames.add(schemas.getString("TABLE_SCHEM"));
138 }
139 }
140
141 return schemaNames;
142 } catch (SQLException se) {
143 throw new JdbcMetadataException(se);
144 } finally {
145 try {
146 if (schemas != null) schemas.close();
147 } catch (Exception ignore) {
148 }
149 }
150 }
151
152 public List<TableMetadata> getTables( Connection conn,
153 String catalogName,
154 String schemaName,
155 String tableName ) throws JdbcMetadataException {
156 List<TableMetadata> tableData = new LinkedList<TableMetadata>();
157
158 ResultSet tables = null;
159 try {
160 DatabaseMetaData dmd = conn.getMetaData();
161
162
163 if (catalogName == null) catalogName = "";
164 if (schemaName == null) schemaName = "";
165 tables = dmd.getTables(catalogName, schemaName, tableName, null);
166 Set<String> columns = columnsFor(tables);
167 while (tables.next()) {
168 TableMetadata table = new TableMetadata(tables.getString("TABLE_NAME"), tables.getString("TABLE_TYPE"),
169 tables.getString("REMARKS"), getStringIfPresent(tables,
170 "TYPE_CAT",
171 columns),
172 getStringIfPresent(tables, "TYPE_SCHEM", columns),
173 getStringIfPresent(tables, "TYPE_NAME", columns),
174 getStringIfPresent(tables, "SELF_REFERENCING_COL_NAME", columns),
175 getStringIfPresent(tables, "REF_GENERATION", columns));
176 tableData.add(table);
177
178 }
179
180 return tableData;
181 } catch (SQLException se) {
182 throw new JdbcMetadataException(se);
183 } finally {
184 try {
185 if (tables != null) tables.close();
186 } catch (Exception ignore) {
187 }
188 }
189 }
190
191 public List<ProcedureMetadata> getProcedures( Connection conn,
192 String catalogName,
193 String schemaName,
194 String procedureName ) throws JdbcMetadataException {
195 List<ProcedureMetadata> procedureData = new LinkedList<ProcedureMetadata>();
196
197 ResultSet procedures = null;
198 try {
199 DatabaseMetaData dmd = conn.getMetaData();
200
201
202 if (catalogName == null) catalogName = "";
203 if (schemaName == null) schemaName = "";
204 procedures = dmd.getProcedures(catalogName, schemaName, procedureName);
205 while (procedures.next()) {
206 ProcedureMetadata procedure = new ProcedureMetadata(procedures.getString("PROCEDURE_NAME"),
207 procedures.getString("REMARKS"),
208 procedures.getShort("PROCEDURE_TYPE"));
209 procedureData.add(procedure);
210
211 }
212
213 return procedureData;
214 } catch (SQLException se) {
215 throw new JdbcMetadataException(se);
216 } finally {
217 try {
218 if (procedures != null) procedures.close();
219 } catch (Exception ignore) {
220 }
221 }
222 }
223
224 private Boolean getNullableBoolean( ResultSet rs,
225 String columnName ) throws SQLException {
226 Boolean b = rs.getBoolean(columnName);
227 if (rs.wasNull()) b = null;
228 return b;
229 }
230
231 private Set<String> columnsFor( ResultSet rs ) throws SQLException {
232 ResultSetMetaData rmd = rs.getMetaData();
233 int count = rmd.getColumnCount();
234
235 Set<String> columns = new HashSet<String>(count);
236 for (int i = 1; i <= count; i++) {
237 columns.add(rmd.getColumnName(i));
238 }
239 return columns;
240 }
241
242 private String getStringIfPresent( ResultSet rs,
243 String columnName,
244 Set<String> resultSetColumns ) throws SQLException {
245 if (!resultSetColumns.contains(columnName)) {
246 return null;
247 }
248
249 return rs.getString(columnName);
250
251 }
252
253 private Integer getIntegerIfPresent( ResultSet rs,
254 String columnName,
255 Set<String> resultSetColumns ) throws SQLException {
256 if (!resultSetColumns.contains(columnName)) {
257 return null;
258 }
259
260 int i = rs.getInt(columnName);
261 if (rs.wasNull()) return null;
262 return i;
263
264 }
265
266 private String identifierFor( DatabaseMetaData dmd,
267 String rawIdentifier ) throws SQLException {
268 assert rawIdentifier != null;
269 if (dmd.storesLowerCaseIdentifiers()) {
270 return rawIdentifier.toLowerCase();
271 }
272
273 return rawIdentifier;
274 }
275 }