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 }