misc
[phpeclipse.git] / archive / net.sourceforge.phpeclipse.quantum.sql / src / com / quantum / sql / metadata / MetaDataJDBCInterface.java
1 /*
2  * Created on 8/04/2003
3  *
4  */
5 package com.quantum.sql.metadata;
6
7 import java.sql.Connection;
8 import java.sql.DatabaseMetaData;
9 import java.sql.ResultSet;
10 import java.sql.ResultSetMetaData;
11 import java.sql.SQLException;
12 import java.sql.Statement;
13
14 import com.quantum.util.StringMatrix;
15
16 /**
17  *      Groups functions that allow extracting data from a JDBC connection 
18  *  in the form of StringMatrix objects
19  *
20  * @author panic
21  *
22  */
23 public class MetaDataJDBCInterface {
24         
25         /**
26          * @param con : A valid (open) connection to an JDBC database
27          * @param schema : Schema of the table. Must be null if not given
28          * @param table : Name of the table.
29          * @return a StringMatrix with the info of the columns' metadata, null if error
30          */
31         public static StringMatrix getColumns(Connection con, String schema, String table) 
32                         throws SQLException {
33
34                 DatabaseMetaData meta = con.getMetaData();
35                 ResultSet set = null;
36                 StringMatrix columns = null;
37                 try {
38                         set = meta.getColumns(null, schema, table, null);
39                         columns = fillMatrix(set);
40                         set.close();
41                         
42                 } catch (SQLException e) {
43                 }
44                 if (columns == null) {
45                         columns = new StringMatrix();
46                         String query = "SELECT * FROM \"" + table + "\" WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
47                         Statement stmt = con.createStatement();
48                         stmt.execute(query);
49                         ResultSet rset = stmt.getResultSet();
50                         ResultSetMetaData rsMetaData = rset.getMetaData();
51                         int columnCount = rsMetaData.getColumnCount();
52                         String headMatrix[] = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME","COLUMN_NAME","DATA_TYPE","TYPE_NAME","COLUMN_SIZE",
53                                                                         "BUFFER_LENGTH","DECIMAL_DIGITS","NUM_PREC_RADIX","NULLABLE","REMARKS",
54                                                                         "COLUMN_DEF","SQL_DATA_TYPE","SQL_DATETIME_SUB","CHAR_OCTET_LENGTH",
55                                                                         "ORDINAL_POSITION","IS_NULLABLE","SCOPE_CATLOG","SCOPE_SCHEMA","SCOPE_TABLE","SOURCE_DATA_TYPE"};
56                         columns.addMatrixHeader(headMatrix); 
57                         for (int i = 0; i < columnCount; i++) {
58                                 columns.add(null, i); /* TABLE_CAT */
59                                 columns.add(schema, i); /* TABLE_SCHEM */
60                                 columns.add(table, i); /* TABLE_NAME */
61                                 columns.add(rsMetaData.getColumnName(i+1), i); /* COLUMN_NAME */
62                                 columns.add(String.valueOf(rsMetaData.getColumnType(i+1)), i); /* DATA_TYPE */
63                                 columns.add(rsMetaData.getColumnTypeName(i+1), i); /* TYPE_NAME */
64                                 columns.add(String.valueOf(rsMetaData.getPrecision(i+1)), i); /* COLUMN_SIZE */
65                                 columns.add(null, i); /* BUFFER_LENGTH */
66                                 columns.add(String.valueOf(rsMetaData.getScale(i+1)), i); /* DECIMAL_DIGITS */
67                                 columns.add(null, i); /* NUM_PREC_RADIX */
68                                 int isNullable = rsMetaData.isNullable(i+1);
69                                 columns.add(String.valueOf(isNullable), i); /* NULLABLE */
70                                 columns.add(null, i); /* REMARKS */
71                                 columns.add(null, i); /* COLUMN_DEF */
72                                 columns.add(null, i); /* SQL_DATA_TYPE */
73                                 columns.add(null, i); /* SQL_DATETIME_SUB */
74                                 columns.add(String.valueOf(rsMetaData.getColumnDisplaySize(i+1)), i); /* CHAR_OCTET_LENGTH */
75                                 columns.add(String.valueOf(i+1), i); /* ORDINAL_POSITION */
76                                 columns.add(isNullable == DatabaseMetaData.columnNullable ? "YES" : "NO", i );
77                                 columns.add(null, i); /* SCOPE_CATLOG */
78                                 columns.add(null, i); /* SCOPE_SCHEMA */
79                                 columns.add(null, i); /* SCOPE_TABLE */
80                                 columns.add(null, i); /* SOURCE_DATA_TYPE */
81                         }                                       
82                 }
83                 return columns;
84         }
85
86         /**
87          * @param con : A valid (open) connection to an JDBC database
88          * @param schema : Schema of the table. Must be null if not given
89          * @param table : Name of the table.
90          * @return a StringMatrix with the info of the primary keys
91          */
92         public static StringMatrix getPrimaryKeys(Connection con, String schema, String table) {
93
94                 StringMatrix keys = new StringMatrix();
95                 try {
96                         DatabaseMetaData meta = con.getMetaData();
97                         ResultSet set = meta.getPrimaryKeys(null, schema, table);
98                         keys = fillMatrix(set);
99                         set.close();
100                 } catch (SQLException e) {
101                            e.printStackTrace();
102                 }
103                 return keys;
104         }
105
106         /**
107          * @param con : A valid (open) connection to an JDBC database
108          * @param schema : Schema of the table. Must be null if not given
109          * @param table : Name of the table.
110          * @param imported : Determines if the foreign keys are the imported or exported ones
111          * @return a StringMatrix with the info of the foreign keys
112          */
113         public static StringMatrix getForeignKeys(Connection con, String schema, String table,  boolean imported ) {
114                                 
115                 ResultSet set = null;
116                 StringMatrix keys = new StringMatrix();
117                 try {
118                                 DatabaseMetaData meta = con.getMetaData();
119                         if (imported){
120                                 set = meta.getImportedKeys(null, schema, table);
121                         } else {
122                                 set = meta.getExportedKeys(null, schema, table);
123                         }
124                         keys = fillMatrix(set);
125                         set.close();
126                 } catch (SQLException e) {
127                            e.printStackTrace();
128                 }
129                 return keys;
130         }
131
132         /**
133          * @param con : A valid (open) connection to an JDBC database
134          * @param schema : Schema of the table. Must be null if not given
135          * @param table : Name of the table.
136          * @return a StringMatrix with the info of the indexes on that table
137          */
138         public static StringMatrix getIndexInfo(Connection con, String schema, String table) {
139
140                 ResultSet set = null;
141                 DatabaseMetaData meta;
142                 StringMatrix keys = new StringMatrix();
143                 try {
144                         meta = con.getMetaData();
145                         set = meta.getIndexInfo(null, schema, table, false, false);
146                         keys = fillMatrix(set);
147                         set.close();
148                 } catch (SQLException e) {
149                         e.printStackTrace();
150                 }
151                 return keys;
152         }
153
154
155         /**
156          * 
157          * @param set
158          * @return a filled StringMatrix with the set results
159          * @throws SQLException
160          */
161         public static StringMatrix fillMatrix(ResultSet set) throws SQLException {
162                 int columnCount = set.getMetaData().getColumnCount();
163                 StringMatrix keys = new StringMatrix();
164                 for (int i = 1; i <= columnCount; i++) {
165                         keys.addHeader(set.getMetaData().getColumnName(i));
166                 }
167                 int row = 0;
168                 while (set.next()) {
169                         for (int i = 1; i <= columnCount; i++) {
170                                 keys.add(set.getString(i), row);
171                         }
172                         row++;
173                 }
174                 return keys;
175         }
176
177         /**
178          * @param con
179          * @param schema
180          * @param tableName
181          */
182         public static StringMatrix getBestRowId(Connection con, String schema, String table) 
183                 throws SQLException {
184
185                 ResultSet set = null;
186                 DatabaseMetaData meta = con.getMetaData();
187                 set = meta.getBestRowIdentifier(null, schema, table, DatabaseMetaData.bestRowSession, true);
188                 StringMatrix keys = fillMatrix(set);
189                 set.close();
190                 return keys;
191
192         }
193
194
195 }