1 package com.quantum.model;
3 import java.sql.Connection;
4 import java.sql.DatabaseMetaData;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8 import java.util.ArrayList;
9 import java.util.Collections;
10 import java.util.HashMap;
11 import java.util.List;
14 import com.quantum.adapters.AdapterFactory;
15 import com.quantum.adapters.DatabaseAdapter;
16 import com.quantum.sql.MultiSQLServer;
17 import com.quantum.sql.SQLMetaDataResults;
18 import com.quantum.util.sql.SQLStates;
21 * This class models a table or view.
25 abstract class EntityImpl implements Entity {
27 // The JDBC-ODBC Driver is more happy if you look up metadata values
28 // using the column number than if you use the column name
30 private static final int INDEX_METADATA_INDEX_NAME = 6;
31 private static final int INDEX_METADATA_COLUMN_NAME = 9;
32 private static final int INDEX_METADATA_ASC_OR_DESC = 10;
34 private static final int PRIMARY_KEYS_METADATA_COLUMN_NAME = 4;
35 private static final int PRIMARY_KEYS_METADATA_KEY_SEQ = 5;
37 private static final int COLUMN_METADATA_COLUMN_NAME = 4;
38 private static final int COLUMN_METATDATA_DATA_TYPE = 5;
39 private static final int COLUMN_METATDATA_TYPE_NAME = 6;
40 private static final int COLUMN_METADATA_COLUMN_SIZE = 7;
41 private static final int COLUMN_METADATA_DECIMAL_DIGITS = 9;
42 private static final int COLUMN_METADATA_REMARKS = 12;
43 private static final int COLUMN_METADATA_ORDINAL_POSITION = 17;
44 private static final int COLUMN_METADATA_IS_NULLABLE = 18;
46 private String schema;
49 private Bookmark bookmark;
50 private Boolean exists = Boolean.TRUE;
52 public EntityImpl(Bookmark bookmark, String schema, String name, String type) {
56 this.bookmark = bookmark;
58 public Bookmark getBookmark() {
61 public String getName() {
64 public String getSchema() {
67 public String getType() {
70 public String getQualifiedName() {
71 return (this.schema == null || this.schema.length() == 0) ?
72 this.name : this.schema + "." + this.name;
74 public Column getColumn(String columnName) throws NotConnectedException, SQLException {
76 Column[] columns = getColumns();
77 for (int i = 0, length = (columns == null) ? 0 : columns.length;
78 column == null && i < length;
80 if (columnName != null && columnName.equals(columns[i].getName())) {
86 public Column[] getColumns() throws NotConnectedException, SQLException {
88 Connection connection = this.bookmark.getConnection();
90 return getColumnsFromMetaData(connection);
91 } catch (SQLException e) {
92 if (SQLStates.ODBC_DRIVER_NOT_CAPABLE.equals(e.getSQLState())
93 && AdapterFactory.JDBC_ODBC_BRIDGE.equals(
94 getBookmark().getJDBCDriver().getType())) {
95 return getColumnsFromQuery(connection);
106 * @throws SQLException
108 private Column[] getColumnsFromMetaData(Connection connection) throws SQLException {
109 Map temp = new HashMap();
110 DatabaseMetaData metaData = connection.getMetaData();
111 ResultSet resultSet = metaData.getColumns(null, getSchema(), getName(), null);
113 while (resultSet.next()) {
114 ColumnImpl column = new ColumnImpl(
116 resultSet.getString(COLUMN_METADATA_COLUMN_NAME),
117 resultSet.getString(COLUMN_METATDATA_TYPE_NAME),
118 resultSet.getInt(COLUMN_METATDATA_DATA_TYPE),
119 resultSet.getInt(COLUMN_METADATA_COLUMN_SIZE),
120 resultSet.getInt(COLUMN_METADATA_DECIMAL_DIGITS),
121 "YES".equalsIgnoreCase(resultSet.getString(COLUMN_METADATA_IS_NULLABLE)),
122 resultSet.getInt(COLUMN_METADATA_ORDINAL_POSITION),
124 resultSet.getString(COLUMN_METADATA_REMARKS),
126 resultSet.getString(COLUMN_METADATA_COLUMN_NAME))
128 temp.put(column.getName(), column);
134 resultSet = metaData.getPrimaryKeys(null, getSchema(), getName());
136 while (resultSet.next()) {
137 String name = resultSet.getString(PRIMARY_KEYS_METADATA_COLUMN_NAME);
138 short keySequence = resultSet.getShort(PRIMARY_KEYS_METADATA_KEY_SEQ);
139 ColumnImpl column = (ColumnImpl) temp.get(name);
140 if (column != null) {
141 column.setPrimaryKeyOrder(keySequence);
146 List columnList = Collections.synchronizedList(
147 new ArrayList(temp.values()));
148 Collections.sort(columnList);
149 return (Column[]) columnList.toArray(new Column[columnList.size()]);
157 * Some databases, (in particular, MS Access under ODBC) aren't terribly friendly
158 * about supporting metadata. This method scrapes out the data the old-fashioned way.
162 * @throws SQLException
164 private Column[] getColumnsFromQuery(Connection connection) throws SQLException {
165 List temp = new ArrayList();
166 SQLMetaDataResults results =
167 (SQLMetaDataResults) MultiSQLServer.getInstance().getMetaData(
169 SQLMetaDataResults.Row[] rows = results.getRows();
170 for (int i = 0, length = results.getRowCount(); i < length; i++) {
171 ColumnImpl column = new ColumnImpl(
173 (String) rows[i].get(1),
174 (String) rows[i].get(2),
175 ((Integer) rows[i].get(7)).intValue(),
176 ((Integer) rows[i].get(3)).intValue(),
177 ((Integer) rows[i].get(4)).intValue(),
178 "Nullable".equalsIgnoreCase((String) rows[i].get(5)),
182 return (Column[]) temp.toArray(new Column[temp.size()]);
185 * Some JDBC drivers (Oracle for example) won't return the comments
186 * We recheck with a custom query, if it's defined
187 * @param iniComment The already got comment
188 * @param tableName The fully qualified table name
189 * @param columnName The column name
191 private String getComments( String iniComment, String tableName, String columnName) {
192 if (iniComment != null && iniComment.length() > 0)
196 Connection con = this.bookmark.getConnection();
197 DatabaseAdapter adapter = this.bookmark.getAdapter();
198 Statement stmt = con.createStatement();
200 if (adapter != null && stmt != null
201 && adapter.getCommentsQuery(tableName, columnName) != null) {
203 stmt.execute(adapter.getCommentsQuery(tableName, columnName));
204 ResultSet set = stmt.getResultSet();
207 comment = set.getString(1);
216 } catch (NotConnectedException e) {
217 } catch (SQLException e) {
223 public Index[] getIndexes() {
225 List indexList = new ArrayList();
226 Map temp = new HashMap();
228 Connection connection = this.bookmark.getConnection();
229 DatabaseMetaData metaData = connection.getMetaData();
230 ResultSet resultSet = metaData.getIndexInfo(null, getSchema(), getName(), false, false);
232 while (resultSet.next()) {
233 String indexName = resultSet.getString(INDEX_METADATA_INDEX_NAME);
234 IndexImpl index = (IndexImpl) temp.get(indexName);
236 index = new IndexImpl(this, indexName);
237 temp.put(indexName, index);
239 String columnName = resultSet.getString(INDEX_METADATA_COLUMN_NAME);
240 String ascending = resultSet.getString(INDEX_METADATA_ASC_OR_DESC);
241 index.addColumn(columnName, ascending == null
242 ? null : (ascending.toUpperCase().startsWith("A")
243 ? Boolean.TRUE : Boolean.FALSE));
246 indexList.addAll(temp.values());
248 } catch (NotConnectedException e) {
249 } catch (SQLException e) {
251 return (Index[]) indexList.toArray(new Index[indexList.size()]);
254 public Boolean exists() {
260 * @see com.quantum.model.Entity#getQuotedTableName()
262 public String getQuotedTableName() {
263 return getBookmark().getAdapter().filterTableName(getQualifiedName());
266 public ForeignKey[] getExportedKeys() throws SQLException, NotConnectedException {
267 return this.bookmark.getDatabase().getExportedKeys(getSchema(), getName());
270 public ForeignKey[] getImportedKeys() throws SQLException, NotConnectedException {
271 return this.bookmark.getDatabase().getImportedKeys(getSchema(), getName());
273 public ForeignKey[] getReferences() throws SQLException, NotConnectedException {
274 ForeignKey[] importedKeys = getImportedKeys();
275 ForeignKey[] exportedKeys = getExportedKeys();
277 List list = new ArrayList(); // if we could guarantee JDK 1.4, we'd use LinkedHashSet
278 for (int i = 0, length = importedKeys == null ? 0 : importedKeys.length; i < length; i++) {
279 list.add(importedKeys[i]);
281 for (int i = 0, length = exportedKeys == null ? 0 : exportedKeys.length; i < length; i++) {
282 if (!list.contains(exportedKeys[i])) {
283 list.add(exportedKeys[i]);
286 return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
289 public int compareTo(Object object) {
290 Entity that = (Entity) object;
291 if (that.getQualifiedName() == null && this.getQualifiedName() != null) {
293 } else if (this.getQualifiedName() == null && that.getQualifiedName() != null) {
295 } else if (this.getQualifiedName() == null && that.getQualifiedName() == null) {
298 return this.getQualifiedName().compareTo(that.getQualifiedName());