sqlmembersource.java

来自「数据仓库展示程序」· Java 代码 · 共 1,093 行 · 第 1/3 页

                        member.setProperty(property.getName(),
                                        resultSet.getObject(column + 1));
                        column++;
                    }
                }
            }

            return RolapUtil.toArray(list);
        } catch (SQLException e) {
            throw Util.newInternal(e,
                    "while building member cache; sql=[" + sql + "]");
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.getStatement().close();
                    resultSet.close();
                }
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    /**
     * Adds <code>member</code> just before the first element in
     * <code>list</code> which has the same parent.
     */
    private void addAsOldestSibling(List list, RolapMember member) {
        int i = list.size();
        while (--i >= 0) {
            RolapMember sibling = (RolapMember) list.get(i);
            if (sibling.getParentMember() != member.getParentMember()) {
                break;
            }
        }
        list.add(i + 1, member);
    }

    private String makeKeysSql(Connection jdbcConnection) {
        SqlQuery sqlQuery = newQuery(jdbcConnection,
                "while generating query to retrieve members of " + hierarchy);
        RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
        for (int i = 0; i < levels.length; i++) {
            RolapLevel level = levels[i];
            if (level.isAll()) {
                continue;
            }
            MondrianDef.Expression exp = level.getKeyExp();
            hierarchy.addToFrom(sqlQuery, exp);
            String expString = exp.getExpression(sqlQuery);
            sqlQuery.addSelect(expString);
            sqlQuery.addGroupBy(expString);
            exp = level.getOrdinalExp();
            hierarchy.addToFrom(sqlQuery, exp);
            expString = exp.getExpression(sqlQuery);
            sqlQuery.addOrderBy(expString);
            sqlQuery.addGroupBy(expString);

            RolapProperty[] properties = level.getRolapProperties();
            for (int j = 0; j < properties.length; j++) {
                RolapProperty property = properties[j];
                exp = property.getExp();
                hierarchy.addToFrom(sqlQuery, exp);
                expString = exp.getExpression(sqlQuery);
                sqlQuery.addSelect(expString);
                sqlQuery.addGroupBy(expString);
            }
        }
        return sqlQuery.toString();
    }

    /**
     * Generates the SQL statement to access members of <code>level</code>. For
     * example, <blockquote>
     * <pre>SELECT "country", "state_province", "city"
     * FROM "customer"
     * GROUP BY "country", "state_province", "city", "init", "bar"</pre>
     * </blockquote> accesses the "City" level of the "Customers"
     * hierarchy. Note that:<ul>
     *
     * <li><code>"country", "state_province"</code> are the parent keys;</li>
     *
     * <li><code>"city"</code> is the level key;</li>
     *
     * <li><code>"init", "bar"</code> are member properties.</li>
     * </ul>
     *
     * @pre !level.isAll()
     **/
    String makeLevelSql(RolapLevel level, Connection jdbcConnection) {
        Util.assertPrecondition(!level.isAll());
        SqlQuery sqlQuery = newQuery(jdbcConnection,
                "while generating query to retrieve members of level " + level);
        RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
        int levelDepth = level.getDepth();
        for (int i = 0; i <= levelDepth; i++) {
            RolapLevel level2 = levels[i];
            if (level2.isAll()) {
                continue;
            }
            hierarchy.addToFrom(sqlQuery, level2.getKeyExp());
            String keySql = level2.getKeyExp().getExpression(sqlQuery);
            sqlQuery.addSelect(keySql);
            sqlQuery.addGroupBy(keySql);
            hierarchy.addToFrom(sqlQuery, level2.getOrdinalExp());

            if (level2.hasCaptionColumn()) {
                MondrianDef.Expression captionExp = level2.getCaptionExp();
                hierarchy.addToFrom(sqlQuery, captionExp);
                String captionSql = captionExp.getExpression(sqlQuery);
                sqlQuery.addSelect(captionSql);
                sqlQuery.addGroupBy(captionSql);
            }

            String ordinalSql = level2.getOrdinalExp().getExpression(sqlQuery);
            sqlQuery.addGroupBy(ordinalSql);
            sqlQuery.addOrderBy(ordinalSql);
            RolapProperty[] properties = level2.getRolapProperties();
            for (int j = 0; j < properties.length; j++) {
                RolapProperty property = properties[j];
                String propSql = property.getExp().getExpression(sqlQuery);
                sqlQuery.addSelect(propSql);
                sqlQuery.addGroupBy(propSql);
            }
        }
        return sqlQuery.toString();
    }

    // implement MemberReader
    public List getMembersInLevel(RolapLevel level,
                                  int startOrdinal,
                                  int endOrdinal) {
        if (level.isAll()) {
            final String allMemberName = hierarchy.getAllMemberName();
            Object key = cache.makeKey(null, allMemberName);
            RolapMember root = cache.getMember(key);
            if (root == null) {
                root = new RolapMember(null, level, null, allMemberName,
                        Member.ALL_MEMBER_TYPE);
                root.setOrdinal(lastOrdinal++);
                cache.putMember(key, root);
                if (hierarchy.xmlHierarchy != null &&
                    hierarchy.xmlHierarchy.allMemberCaption != null &&
                    hierarchy.xmlHierarchy.allMemberCaption.length() > 0) {
                    root.setCaption(hierarchy.xmlHierarchy.allMemberCaption );
                }

            }
            List list = new ArrayList(1);
            list.add(root);
            return list;
        }
        final RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
        Connection jdbcConnection;
        try {
            jdbcConnection = dataSource.getConnection();
        } catch (SQLException e) {
            throw Util.newInternal(
                e, "Error while creating connection from data source");
        }
        try {
            return getMembersInLevel(level, jdbcConnection, levels);
        } finally {
            try {
                jdbcConnection.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    private List getMembersInLevel(RolapLevel level,
                                   Connection jdbcConnection,
                                   final RolapLevel[] levels) {
        String sql = makeLevelSql(level, jdbcConnection);
        ResultSet resultSet = null;
        try {
            resultSet = RolapUtil.executeQuery(jdbcConnection,
                                          sql,
                                          "SqlMemberSource.getMembersInLevel");
            List list = new ArrayList();
            final int levelDepth = level.getDepth();
            RolapMember allMember = null;
            if (hierarchy.hasAll()) {
                final List rootMembers = getRootMembers();
                Util.assertTrue(rootMembers.size() == 1);
                allMember = (RolapMember) rootMembers.get(0);
            }
            boolean parentChild = level.getParentExp() != null;

            // members[i] is the current member of level#i, and siblings[i]
            // is the current member of level#i plus its siblings
            RolapMember[] members = new RolapMember[levels.length];
            List[] siblings = new ArrayList[levels.length + 1];

            int limit = MondrianProperties.instance().ResultLimit.get();
            int nFetch = 0;

            while (resultSet.next()) {

                if (limit > 0 && limit < ++nFetch) {
                    // result limit exceeded, throw an exception
                    throw MondrianResource.instance().
                               MemberFetchLimitExceeded.ex(new Long(limit));
                }

                int column = 0;
                RolapMember member = null;
                for (int i = 0; i <= levelDepth; i++) {
                    RolapLevel childLevel = levels[i];
                    if (childLevel.isAll()) {
                        member = allMember;
                        continue;
                    }
                    Object value = resultSet.getObject(++column);
                    if (value == null) {
                        value = RolapUtil.sqlNullValue;
                    }
                    Object captionValue;
                    if (childLevel.hasCaptionColumn()) {
                        captionValue = resultSet.getObject(++column);
                    } else {
                        captionValue = null;
                    }
                    RolapMember parentMember = member;
                    Object key = cache.makeKey(parentMember, value);
                    member = cache.getMember(key);
                    if (member == null) {
                        member = makeMember(
                                parentMember, childLevel, value, captionValue,
                                parentChild, resultSet, key, column);
                    }
                    column += childLevel.getProperties().length;
                    if (member != members[i]) {
                        // Flush list we've been building.
                        List children = siblings[i + 1];
                        if (children != null) {
                            cache.putChildren(members[i], children);
                        }
                        // Start a new list, if the cache needs one. (We don't
                        // synchronize, so it's possible that the cache will
                        // have one by the time we complete it.)
                        if (i < levelDepth &&
                                !cache.hasChildren(member)) {
                            siblings[i + 1] = new ArrayList();
                        } else {
                            siblings[i + 1] = null; // don't bother building up a list
                        }
                        // Record new current member of this level.
                        members[i] = member;
                        // If we're building a list of siblings at this level,
                        // we haven't seen this one before, so add it.
                        if (siblings[i] != null) {
                            if (value == RolapUtil.sqlNullValue) {
                                addAsOldestSibling(siblings[i], member);
                            } else {
                                siblings[i].add(member);
                            }
                        }
                    }
                }
                list.add(member);
            }
            for (int i = 0; i < members.length; i++) {
                RolapMember member = members[i];
                final List children = siblings[i + 1];
                if (member != null && children != null) {
                    cache.putChildren(member, children);
                }
            }
            return list;
        } catch (Throwable e) {
            throw Util.newInternal(e,
                    "while populating member cache with members for level '" +
                    level.getUniqueName() + "'; sql=[" + sql + "]");
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.getStatement().close();
                    resultSet.close();
                }
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    // implement MemberSource
    public List getRootMembers() {
        return getMembersInLevel(
                (RolapLevel) hierarchy.getLevels()[0],
                0,
                Integer.MAX_VALUE);
    }

    /**
     * Generates the SQL statement to access the children of
     * <code>member</code>. For example, <blockquote>
     *
     * <pre>SELECT "city"
     * FROM "customer"
     * WHERE "country" = 'USA'
     * AND "state_province" = 'BC'
     * GROUP BY "city"</pre>
     * </blockquote> retrieves the children of the member
     * <code>[Canada].[BC]</code>. See also {@link #makeLevelSql}.
     **/
    String makeChildMemberSql(RolapMember member, Connection jdbcConnection) {
        SqlQuery sqlQuery = newQuery(jdbcConnection,
                "while generating query to retrieve children of member "
                + member);
        for (RolapMember m = member; m != null; m = (RolapMember)
                 m.getParentMember()) {
            RolapLevel level = (RolapLevel) m.getLevel();
            if (level.isAll()) {
                continue;
            }
            hierarchy.addToFrom(sqlQuery, level.getKeyExp());
            String q = level.getKeyExp().getExpression(sqlQuery);
            sqlQuery.addWhere(q, " = ", m.quoteKeyForSql());
            if (level.isUnique()) {
                break; // no further qualification needed
            }
        }

        RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
        RolapLevel level = levels[member.getLevel().getDepth() + 1];
        hierarchy.addToFrom(sqlQuery, level.getKeyExp());
        String q = level.getKeyExp().getExpression(sqlQuery);
        sqlQuery.addSelect(q);
        sqlQuery.addGroupBy(q);

        if (level.hasCaptionColumn()){
            MondrianDef.Expression captionExp = level.getCaptionExp();
            hierarchy.addToFrom(sqlQuery, captionExp);
            String captionSql = captionExp.getExpression(sqlQuery);
            sqlQuery.addSelect(captionSql);
            sqlQuery.addGroupBy(captionSql);
        }

        hierarchy.addToFrom(sqlQuery, level.getOrdinalExp());
        String orderBy = level.getOrdinalExp().getExpression(sqlQuery);
        sqlQuery.addOrderBy(orderBy);
        if (!orderBy.equals(q)) {
            sqlQuery.addGroupBy(orderBy);
        }

        RolapProperty[] properties = level.getRolapProperties();
        for (int j = 0; j < properties.length; j++) {
            RolapProperty property = properties[j];
            final MondrianDef.Expression exp = property.getExp();
            hierarchy.addToFrom(sqlQuery, exp);
            final String s = exp.getExpression(sqlQuery);
            sqlQuery.addSelect(s);
            sqlQuery.addGroupBy(s);
        }
        return sqlQuery.toString();
    }

    public void getMemberChildren(List parentMembers, List children) {
        for (int i = 0; i < parentMembers.size(); i++) {
            getMemberChildren((RolapMember) parentMembers.get(i), children);
        }
    }