jdbcdatasetschemareader.java

ADO.NET在Java中的使用 ADO.NET最大的特性在于对断开数据库连接方式的全方位支持

                int dotIdx = name.indexOf(".");
                
                if (dotIdx != -1) {
                    String table = name.substring(0, dotIdx);
                    String col = name.substring(dotIdx + 1);
                    
                    // If nothing has been set before (never seen this table)
                    // then setup the column set
                    if (!tables.containsKey(table)) {
                        tables.put(table, new HashSet<String>());
                    }
                    
                    // If the value has been set before it may have been
                    // explictly null to indicate all columns to be included.
                    if (tables.get(table) != null) {
                        tables.get(table).add(col);
                    }
                } else {
                    tables.put(name, null);
                }
            }
            
            // Create all of the DataTables by building simple SELECT statements
            // to retrieve information for one table
            for (String tableName : tables.keySet()) {
                if ( dataSet.getTable(tableName) != null ) {
                    System.out.println("SKIPPING TABLE " + tableName + " ALREADY IN DATASET");
                    continue;
                }
                Set<String> cols = tables.get(tableName);
                
                // check to see whether the entire table is to be included, or
                // only some of the table
                List colNames = ( cols == null ? null : new ArrayList(cols));
                String tableSelectCmd = getTableSelectNoRowsCommand(tableName, colNames); //"SELECT * FROM " + tableName + " WHERE 1 = 0";
                DataTable dataTable = readDataTable(dataSet, tableName, tableSelectCmd);
                
                SQLDataProvider dataProvider = new SQLDataProvider(tableName);
                dataProvider.setConnection(jdbcConn);
                dataTable.setDataProvider(dataProvider);
                
                loadedTables.add(tableName);
            }
        } catch (SchemaReaderException e) {
            throw e;
        } catch (Exception e) {
            throw new SchemaReaderException("Could not read schemas to create DataTables", e);
        } finally {
            jdbcConn.setConnected(connectState);
        }
        return loadedTables;
    }
    
    /**
     * Adds all {@link org.jdesktop.databuffer.DataRelation} for the named {@link org.jdesktop.databuffer.DataTable}
     * to the specified {@link org.jdesktop.databuffer.DataSet}, as read from the schema which this reader
     * is reading from. All <CODE>DataRelations</CODE> for each table are dropped from the <CODE>DataSet</CODE>
     * and re-added.
     *
     * @return A <CODE>List</CODE> of the <CODE>DataRelation</CODE> names loaded from the schema.
     *
     * @param dataSet The <CODE>DataSet</CODE> to which to add the relations.
     *
     * @param tableNames One or more table names for which to read relations from the schema. Only those relations
     * for columns in the DataTable (as currently loaded) are added.
     *
     * @throws org.jdesktop.databuffer.io.schema.SchemaReaderException If an error occurred while loading the schema.
     */
    public List<String> addRelations(DataSet dataSet, String... tableNames) throws SchemaReaderException {
        List<String> loadedRelations = new ArrayList<String>();
        
        boolean connectState = jdbcConn.isConnected();
        if ( !connectState ) jdbcConn.setConnected(true);
        Connection conn = jdbcConn.getConnection();
        
        // relations
        // This list is used to search for datatables using a case-insensitive
        // comparison to avoid problems of databases changing the casing when
        // returning relation metadata (i.e. MySQL)
        try {
            List<DataTable> dataTables = dataSet.getTables();
            
            //for (DataTable dataTable : dataTables) {
            for ( String tableName : tableNames) {
                DataTable dataTable = dataSet.getTable(tableName);
                
                DatabaseMetaData md = conn.getMetaData();
                
                // Uses the current catalog, as noted in the javadoc
                ResultSet rs = md.getImportedKeys(conn.getCatalog(), null, dataTable.getName());
                
                while (rs.next()) {
                    // TODO for now, DataRelations only support a single column,
                    // so if the sequence is > 1, skip it
                    if (rs.getInt("KEY_SEQ") > 1) {
                        continue;
                    }
                    
                    // search for table in DataSet, case-insensitive
                    DataTable parentTable = null;
                    String pkTableName = rs.getString("PKTABLE_NAME");
                    
                    for (DataTable t : dataTables) {
                        if (t.getName().equalsIgnoreCase(pkTableName)) {
                            parentTable = t;
                            break;
                        }
                    }
                    
                    // May occur if tableName was not initially selected
                    if (parentTable == null) {
                        continue;
                    }
                    
                    DataColumn parentColumn = parentTable.getColumn(rs.getString("PKCOLUMN_NAME"));
                    DataColumn childColumn = dataTable.getColumn(rs.getString("FKCOLUMN_NAME"));
                    if (parentColumn != null && childColumn != null && parentColumn != childColumn) {
                        String name = rs.getString("FK_NAME");
                        DataRelation rel = dataSet.createRelation(rs.getString("FK_NAME"), parentColumn, childColumn);
                        loadedRelations.add(rel.getName());
                    }
                }
            }
            
        } catch (Exception e) {
            throw new SchemaReaderException("Could not create data relations", e);
        } finally {
            jdbcConn.setConnected(connectState);
        }
        return loadedRelations;
    }
    
    /**
     *
     * Returns the SELECT statement used to query the given table, without rows being returned; this may vary
     * between databases. The default is to select "where 1 = 0". You may override this in a subclass to provide
     * a database-specific SELECT command, e.g. "SELECT * FROM <table> LIMIT 0".
     *
     * @param tableName The table from which to select.
     *
     * @param columnNames The columns to select for that table, null means all columns.
     *
     * @return A SELECT statement with no parameters, selecting from the table for the columns specified,
     * and returning no rows.
     */
    protected String getTableSelectNoRowsCommand(String tableName, List<String> columnNames) {
        String comma = ", \n";
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT ");
        
        if ( columnNames == null ) {
            builder.append("*\n");
        } else {
            for ( String col : columnNames ) {
                builder.append(col + comma);
            }
            builder.delete(builder.length() - comma.length(), builder.length() - 1);
        }
        
        builder.append("FROM " + tableName + "\n");
        builder.append("WHERE 1 = 0");
        
        return builder.toString();
    }
    
    /**
     * Adds a single named <CODE>DataTable</CODE> to the <CODE>DataSet</CODE>.
     *
     * @return The new <CODE>DataTable</CODE> added to the <CODE>DataSet</CODE>.
     *
     * @param dataSet The <CODE>DataSet</CODE> to add to.
     *
     * @param tableName The table name to read from the schema.
     *
     * @param tableSelectCmd The SELECT command to retrieve from the table, with no rows.
     *
     * @throws org.jdesktop.databuffer.io.schema.SchemaReaderException If an error occurred while loading the schema.
     */
    private DataTable readDataTable(DataSet dataSet, String tableName, String tableSelectCmd) throws SchemaReaderException {
        boolean connectState = jdbcConn.isConnected();
        if ( !connectState ) jdbcConn.setConnected(true);
        Connection conn = jdbcConn.getConnection();
        DataTable dataTable = null;
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            Statement stmt = conn.createStatement();
            ResultSetMetaData colRSMD = stmt.executeQuery(tableSelectCmd).getMetaData();
            dataTable = dataSet.createTable(tableName);
            
            int colCnt = colRSMD.getColumnCount();
            String catalog = colRSMD.getCatalogName(1);
            String schema = colRSMD.getSchemaName(1);;
            for ( int i=1; i <= colCnt; i++ ) {
                String colName = colRSMD.getColumnName(i);
                DataColumn newColumn =
                    dataTable.createColumn(DataSetIOUtility.getType(colRSMD.getColumnType(i)),
                                           colName);
                
                newColumn.setRequired(colRSMD.isNullable(i) == ResultSetMetaData.columnNullable);
                newColumn.setReadOnly(colRSMD.isReadOnly(i));
                
                String columnPattern = "%";
                ResultSet colRS = dbMetaData.getColumns(catalog, schema, tableName, columnPattern );
                if ( !colRS.next()) continue;
                
                newColumn.setDefaultValue(colRS.getObject("COLUMN_DEF"));
                colRS.close();
            }
            
            ResultSet rs = dbMetaData.getPrimaryKeys(catalog, schema, tableName);
            while (rs.next()) {
                DataColumn col = dataTable.getColumn(rs.getString("COLUMN_NAME"));
                
                // Happens if a key column is not SELECTed
                if (col != null) {
                    col.setKeyColumn(true);
                }
            }
            SQLDataProvider dataProvider = new SQLDataProvider(tableName);
            dataProvider.setConnection(jdbcConn);
            dataTable.setDataProvider(dataProvider);
        } catch (Exception e) {
            throw new SchemaReaderException("Could not create DataTable for table named " + tableName, e);
        } finally {
            jdbcConn.setConnected(connectState);
        }
        return dataTable;
    }    
}