001: /*
002: * Copyright 2006-2007 Pentaho Corporation. All rights reserved.
003: * This software was developed by Pentaho Corporation and is provided under the terms
004: * of the Mozilla Public License, Version 1.1, or any later version. You may not use
005: * this file except in compliance with the license. If you need a copy of the license,
006: * please go to http://www.mozilla.org/MPL/MPL-1.1.txt.
007: *
008: * Software distributed under the Mozilla Public License is distributed on an "AS IS"
009: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
010: * the license for the specific language governing your rights and limitations.
011: *
012: * Additional Contributor(s): Martin Schmid gridvision engineering GmbH
013: */
014: package org.pentaho.reportdesigner.crm.report.datasetplugin.composer;
015:
016: import org.jetbrains.annotations.NotNull;
017: import org.pentaho.reportdesigner.crm.report.components.ProgressListener;
018: import org.pentaho.reportdesigner.crm.report.datasetplugin.jdbc.ResultSetPrinter;
019: import org.pentaho.reportdesigner.crm.report.datasetplugin.sampledb.SampleDB;
020:
021: import java.sql.Connection;
022: import java.sql.DriverManager;
023: import java.sql.ResultSet;
024: import java.util.ArrayList;
025:
026: /**
027: * User: Martin
028: * Date: 06.03.2006
029: * Time: 09:30:31
030: */
031: @SuppressWarnings({"ALL"})
032: public class QueryComposeTester {
033: public static void main(String[] args) throws Exception {
034: SampleDB.initSampleDB();
035:
036: Connection c = DriverManager.getConnection(
037: "jdbc:hsqldb:mem:sample", "sa", "");
038:
039: ResultSet catalogs = c.getMetaData().getCatalogs();
040: ResultSetPrinter.printResultSet(catalogs);
041:
042: ResultSet schemas = c.getMetaData().getSchemas();
043: ResultSetPrinter.printResultSet(schemas);
044:
045: JDBCGraph graph = JDBCAnalyzer.buildGraph("", "PUBLIC", c,
046: new ProgressListener() {
047: public void taskStarted(@NotNull
048: String task) {
049: System.out.println("task = " + task);//NON-NLS
050: }
051: });
052: System.out.println("graph = " + graph);
053:
054: //e.g build query with
055: //CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME, PRODUCTS.PRODUCT_NAME, PRODUCTS.PRODUCT_DESCRIPTION, PRODUCTS.PRICE
056: /*
057: SELECT
058: CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME,
059: PRODUCTS.PRODUCT_NAME, PRODUCTS.PRODUCT_DESCRIPTION, PRODUCTS.PRICE
060: FROM CUSTOMERS
061: JOIN ORDERS ON CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID
062: JOIN ORDER_ITEMS ON ORDER_ITEMS.ORDER_ID=ORDERS.ORDER_ID
063: JOIN PRODUCTS ON ORDER_ITEMS.PRODUCT_ID=PRODUCTS.PRODUCT_ID
064: ORDER BY
065: CUSTOMERS.FIRST_NAME, PRODUCTS.PRODUCT_NAME
066: */
067:
068: JDBCColumnInfo firstName = graph.getVertexForTable("CUSTOMERS")
069: .getTableInfo().getColumnInfos().get("FIRST_NAME");
070: JDBCColumnInfo lastName = graph.getVertexForTable("CUSTOMERS")
071: .getTableInfo().getColumnInfos().get("LAST_NAME");
072: JDBCColumnInfo productName = graph
073: .getVertexForTable("PRODUCTS").getTableInfo()
074: .getColumnInfos().get("PRODUCT_NAME");
075: JDBCColumnInfo productDescription = graph.getVertexForTable(
076: "PRODUCTS").getTableInfo().getColumnInfos().get(
077: "PRODUCT_DESCRIPTION");
078: JDBCColumnInfo price = graph.getVertexForTable("PRODUCTS")
079: .getTableInfo().getColumnInfos().get("PRICE");
080:
081: ArrayList<QueryComposerColumn> queryComposerColumns = new ArrayList<QueryComposerColumn>();
082: queryComposerColumns.add(new QueryComposerColumn(firstName,
083: true, OrderDirection.ASCENDING, null, null, null));
084: queryComposerColumns.add(new QueryComposerColumn(lastName,
085: true, OrderDirection.ASCENDING, null, null, null));
086: queryComposerColumns.add(new QueryComposerColumn(productName,
087: true, null, null, null, null));
088: queryComposerColumns.add(new QueryComposerColumn(
089: productDescription, true, null, null, null, null));
090: queryComposerColumns.add(new QueryComposerColumn(price, true,
091: null, null, null, null));
092:
093: String query = QueryComposer.getQuery(graph,
094: queryComposerColumns);
095: System.out.println("query = \n" + query);
096:
097: /*
098: SELECT
099: aufwand.gueltig_von,
100: aufwand.gueltig_bis,
101: projekt.titel,
102: task.beschreibung
103: FROM
104: aufwand, projekt, task
105: WHERE
106: aufwand.gueltig_von >= '02.01.2006 00:00' AND
107: aufwand.gueltig_bis < '03.01.2006 00:00' AND
108: task.benutzer_oid = 9991 AND
109: task.task_oid = aufwand.task_oid AND
110: projekt.projekt_oid=task.projekt_oid
111: ORDER BY
112: projekt.titel, task.beschreibung, aufwand.gueltig_von
113: */
114: }
115: }
|