java.sql

Here are the examples of the java api class java.sql taken from open source projects.

1. DrillThroughTest#testDrillThrough2()

Project: mondrian
File: DrillThroughTest.java
public void testDrillThrough2() {
    Result result = executeQuery("WITH MEMBER [Measures].[Price] AS '[Measures].[Store Sales] / ([Measures].[Unit Sales], [Time].[Time].PrevMember)'\n" + "SELECT {[Measures].[Unit Sales], [Measures].[Price]} on columns,\n" + " {[Product].Children} on rows\n" + "from Sales");
    String sql = result.getCell(new int[] { 0, 0 }).getDrillThroughSQL(true);
    String nameExpStr = getNameExp(result, "Customers", "Name");
    String expectedSql = "select `store`.`store_country` as `Store Country`," + " `store`.`store_state` as `Store State`," + " `store`.`store_city` as `Store City`," + " `store`.`store_name` as `Store Name`," + " `store`.`store_sqft` as `Store Sqft`," + " `store`.`store_type` as `Store Type`," + " `time_by_day`.`the_year` as `Year`," + " `time_by_day`.`quarter` as `Quarter`," + " `time_by_day`.`month_of_year` as `Month`," + " `time_by_day`.`week_of_year` as `Week`," + " `time_by_day`.`day_of_month` as `Day`," + " `product_class`.`product_family` as `Product Family`," + " `product_class`.`product_department` as `Product Department`," + " `product_class`.`product_category` as `Product Category`," + " `product_class`.`product_subcategory` as `Product Subcategory`," + " `product`.`brand_name` as `Brand Name`," + " `product`.`product_name` as `Product Name`," + " `promotion`.`media_type` as `Media Type`," + " `promotion`.`promotion_name` as `Promotion Name`," + " `customer`.`country` as `Country`," + " `customer`.`state_province` as `State Province`," + " `customer`.`city` as `City`, " + nameExpStr + " as `Name`," + " `customer`.`customer_id` as `Name (Key)`," + " `customer`.`education` as `Education Level`," + " `customer`.`gender` as `Gender`," + " `customer`.`marital_status` as `Marital Status`," + " `customer`.`yearly_income` as `Yearly Income`," + " `sales_fact_1997`.`unit_sales` as `Unit Sales` " + "from `store` =as= `store`," + " `sales_fact_1997` =as= `sales_fact_1997`," + " `time_by_day` =as= `time_by_day`," + " `product_class` =as= `product_class`," + " `product` =as= `product`," + " `promotion` =as= `promotion`," + " `customer` =as= `customer` " + "where `sales_fact_1997`.`store_id` = `store`.`store_id`" + " and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `sales_fact_1997`.`product_id` = `product`.`product_id`" + " and `product`.`product_class_id` = `product_class`.`product_class_id`" + " and `product_class`.`product_family` = 'Drink'" + " and `sales_fact_1997`.`promotion_id` = `promotion`.`promotion_id`" + " and `sales_fact_1997`.`customer_id` = `customer`.`customer_id` " + "order by `store`.`store_country` ASC," + " `store`.`store_state` ASC," + " `store`.`store_city` ASC," + " `store`.`store_name` ASC," + " `store`.`store_sqft` ASC," + " `store`.`store_type` ASC," + " `time_by_day`.`the_year` ASC," + " `time_by_day`.`quarter` ASC," + " `time_by_day`.`month_of_year` ASC," + " `time_by_day`.`week_of_year` ASC," + " `time_by_day`.`day_of_month` ASC," + " `product_class`.`product_family` ASC," + " `product_class`.`product_department` ASC," + " `product_class`.`product_category` ASC," + " `product_class`.`product_subcategory` ASC," + " `product`.`brand_name` ASC," + " `product`.`product_name` ASC," + " `promotion`.`media_type` ASC," + " `promotion`.`promotion_name` ASC," + " `customer`.`country` ASC," + " `customer`.`state_province` ASC," + " `customer`.`city` ASC, " + nameExpStr + " ASC," + " `customer`.`customer_id` ASC," + " `customer`.`education` ASC," + " `customer`.`gender` ASC," + " `customer`.`marital_status` ASC," + " `customer`.`yearly_income` ASC";
    getTestContext().assertSqlEquals(expectedSql, sql, 7978);
    // Drillthrough SQL is null for cell based on calc member
    sql = result.getCell(new int[] { 1, 1 }).getDrillThroughSQL(true);
    assertNull(sql);
}

2. DrillThroughTest#testDrillThrough()

Project: mondrian
File: DrillThroughTest.java
public void testDrillThrough() {
    Result result = executeQuery("WITH MEMBER [Measures].[Price] AS '[Measures].[Store Sales] / ([Measures].[Store Sales], [Time].[Time].PrevMember)'\n" + "SELECT {[Measures].[Unit Sales], [Measures].[Price]} on columns,\n" + " {[Product].Children} on rows\n" + "from Sales");
    final Cell cell = result.getCell(new int[] { 0, 0 });
    assertTrue(cell.canDrillThrough());
    String sql = cell.getDrillThroughSQL(false);
    String expectedSql = "select `time_by_day`.`the_year` as `Year`," + " `product_class`.`product_family` as `Product Family`," + " `sales_fact_1997`.`unit_sales` as `Unit Sales` " + "from `time_by_day` =as= `time_by_day`," + " `sales_fact_1997` =as= `sales_fact_1997`," + " `product_class` =as= `product_class`," + " `product` =as= `product` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `sales_fact_1997`.`product_id` = `product`.`product_id`" + " and `product`.`product_class_id` = `product_class`.`product_class_id`" + " and `product_class`.`product_family` = 'Drink' " + "order by `time_by_day`.`the_year` ASC," + " `product_class`.`product_family` ASC";
    getTestContext().assertSqlEquals(expectedSql, sql, 7978);
    // Cannot drill through a calc member.
    final Cell calcCell = result.getCell(new int[] { 1, 1 });
    assertFalse(calcCell.canDrillThrough());
    sql = calcCell.getDrillThroughSQL(false);
    assertNull(sql);
}

3. DrillThroughTest#testTrivialCalcMemberDrillThrough()

Project: mondrian
File: DrillThroughTest.java
// ~ Tests ================================================================
public void testTrivialCalcMemberDrillThrough() {
    Result result = executeQuery("WITH MEMBER [Measures].[Formatted Unit Sales]" + " AS '[Measures].[Unit Sales]', FORMAT_STRING='$#,###.000'\n" + "MEMBER [Measures].[Twice Unit Sales]" + " AS '[Measures].[Unit Sales] * 2'\n" + "MEMBER [Measures].[Twice Unit Sales Plus Store Sales] " + " AS '[Measures].[Twice Unit Sales] + [Measures].[Store Sales]'," + "  FOMRAT_STRING='#'\n" + "MEMBER [Measures].[Foo] " + " AS '[Measures].[Unit Sales] + ([Measures].[Unit Sales], [Time].[Time].PrevMember)'\n" + "MEMBER [Measures].[Unit Sales Percentage] " + " AS '[Measures].[Unit Sales] / [Measures].[Twice Unit Sales]'\n" + "SELECT {[Measures].[Unit Sales],\n" + "  [Measures].[Formatted Unit Sales],\n" + "  [Measures].[Twice Unit Sales],\n" + "  [Measures].[Twice Unit Sales Plus Store Sales],\n" + "  [Measures].[Foo],\n" + "  [Measures].[Unit Sales Percentage]} on columns,\n" + " {[Product].Children} on rows\n" + "from Sales");
    // can drill through [Formatted Unit Sales]
    final Cell cell = result.getCell(new int[] { 0, 0 });
    assertTrue(cell.canDrillThrough());
    // can drill through [Unit Sales]
    assertTrue(result.getCell(new int[] { 1, 0 }).canDrillThrough());
    // can drill through [Twice Unit Sales]
    assertTrue(result.getCell(new int[] { 2, 0 }).canDrillThrough());
    // can drill through [Twice Unit Sales Plus Store Sales]
    assertTrue(result.getCell(new int[] { 3, 0 }).canDrillThrough());
    // can not drill through [Foo]
    assertFalse(result.getCell(new int[] { 4, 0 }).canDrillThrough());
    // can drill through [Unit Sales Percentage]
    assertTrue(result.getCell(new int[] { 5, 0 }).canDrillThrough());
    assertNotNull(result.getCell(new int[] { 5, 0 }).getDrillThroughSQL(false));
    String sql = cell.getDrillThroughSQL(false);
    String expectedSql = "select `time_by_day`.`the_year` as `Year`," + " `product_class`.`product_family` as `Product Family`," + " `sales_fact_1997`.`unit_sales` as `Unit Sales` " + "from `time_by_day` =as= `time_by_day`," + " `sales_fact_1997` =as= `sales_fact_1997`," + " `product_class` =as= `product_class`," + " `product` =as= `product` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `sales_fact_1997`.`product_id` = `product`.`product_id`" + " and `product`.`product_class_id` = `product_class`.`product_class_id`" + " and `product_class`.`product_family` = 'Drink' " + "order by `time_by_day`.`the_year` ASC," + " `product_class`.`product_family` ASC";
    getTestContext().assertSqlEquals(expectedSql, sql, 7978);
    // Can drill through a trivial calc member.
    final Cell calcCell = result.getCell(new int[] { 1, 0 });
    assertTrue(calcCell.canDrillThrough());
    sql = calcCell.getDrillThroughSQL(false);
    assertNotNull(sql);
    expectedSql = "select `time_by_day`.`the_year` as `Year`," + " `product_class`.`product_family` as `Product Family`," + " `sales_fact_1997`.`unit_sales` as `Unit Sales` " + "from `time_by_day` =as= `time_by_day`," + " `sales_fact_1997` =as= `sales_fact_1997`," + " `product_class` =as= `product_class`," + " `product` =as= `product` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `sales_fact_1997`.`product_id` = `product`.`product_id`" + " and `product`.`product_class_id` = `product_class`.`product_class_id`" + " and `product_class`.`product_family` = 'Drink' " + "order by `time_by_day`.`the_year` ASC," + " `product_class`.`product_family` ASC";
    getTestContext().assertSqlEquals(expectedSql, sql, 7978);
    assertEquals(calcCell.getDrillThroughCount(), 7978);
}

4. BasicQueryTest#_testCubeWhichUsesSameSharedDimTwice()

Project: mondrian
File: BasicQueryTest.java
/**
     * This test modifies the Sales cube to contain both the regular usage
     * of the [Store] shared dimension, and another usage called [Other Store]
     * which is connected to the [Unit Sales] column
     */
public void _testCubeWhichUsesSameSharedDimTwice() {
    // Create a second usage of the "Store" shared dimension called "Other
    // Store". Attach it to the "unit_sales" column (which has values [1,
    // 6] whereas store has values [1, 24].
    TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<DimensionUsage name=\"Other Store\" source=\"Store\" foreignKey=\"unit_sales\" />");
    Axis axis = testContext.executeAxis("[Other Store].members");
    assertEquals(63, axis.getPositions().size());
    axis = testContext.executeAxis("[Store].members");
    assertEquals(63, axis.getPositions().size());
    final String q1 = "select {[Measures].[Unit Sales]} on columns,\n" + " NON EMPTY {[Other Store].members} on rows\n" + "from [Sales]";
    testContext.assertQueryReturns(q1, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Other Store].[All Other Stores]}\n" + "{[Other Store].[Mexico]}\n" + "{[Other Store].[USA]}\n" + "{[Other Store].[Mexico].[Guerrero]}\n" + "{[Other Store].[Mexico].[Jalisco]}\n" + "{[Other Store].[Mexico].[Zacatecas]}\n" + "{[Other Store].[USA].[CA]}\n" + "{[Other Store].[USA].[WA]}\n" + "{[Other Store].[Mexico].[Guerrero].[Acapulco]}\n" + "{[Other Store].[Mexico].[Jalisco].[Guadalajara]}\n" + "{[Other Store].[Mexico].[Zacatecas].[Camacho]}\n" + "{[Other Store].[USA].[CA].[Beverly Hills]}\n" + "{[Other Store].[USA].[WA].[Bellingham]}\n" + "{[Other Store].[USA].[WA].[Bremerton]}\n" + "{[Other Store].[Mexico].[Guerrero].[Acapulco].[Store 1]}\n" + "{[Other Store].[Mexico].[Jalisco].[Guadalajara].[Store 5]}\n" + "{[Other Store].[Mexico].[Zacatecas].[Camacho].[Store 4]}\n" + "{[Other Store].[USA].[CA].[Beverly Hills].[Store 6]}\n" + "{[Other Store].[USA].[WA].[Bellingham].[Store 2]}\n" + "{[Other Store].[USA].[WA].[Bremerton].[Store 3]}\n" + "Row #0: 266,773\n" + "Row #1: 110,822\n" + "Row #2: 155,951\n" + "Row #3: 1,827\n" + "Row #4: 14,915\n" + "Row #5: 94,080\n" + "Row #6: 222\n" + "Row #7: 155,729\n" + "Row #8: 1,827\n" + "Row #9: 14,915\n" + "Row #10: 94,080\n" + "Row #11: 222\n" + "Row #12: 39,362\n" + "Row #13: 116,367\n" + "Row #14: 1,827\n" + "Row #15: 14,915\n" + "Row #16: 94,080\n" + "Row #17: 222\n" + "Row #18: 39,362\n" + "Row #19: 116,367\n");
    final String q2 = "select {[Measures].[Unit Sales]} on columns,\n" + " CrossJoin(\n" + "  {[Store].[USA], [Store].[USA].[CA], [Store].[USA].[OR].[Portland]}, \n" + "  {[Other Store].[USA], [Other Store].[USA].[CA], [Other Store].[USA].[OR].[Portland]}) on rows\n" + "from [Sales]";
    testContext.assertQueryReturns(q2, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Store].[USA], [Other Store].[USA]}\n" + "{[Store].[USA], [Other Store].[USA].[CA]}\n" + "{[Store].[USA], [Other Store].[USA].[OR].[Portland]}\n" + "{[Store].[USA].[CA], [Other Store].[USA]}\n" + "{[Store].[USA].[CA], [Other Store].[USA].[CA]}\n" + "{[Store].[USA].[CA], [Other Store].[USA].[OR].[Portland]}\n" + "{[Store].[USA].[OR].[Portland], [Other Store].[USA]}\n" + "{[Store].[USA].[OR].[Portland], [Other Store].[USA].[CA]}\n" + "{[Store].[USA].[OR].[Portland], [Other Store].[USA].[OR].[Portland]}\n" + "Row #0: 155,951\n" + "Row #1: 222\n" + "Row #2: \n" + "Row #3: 43,730\n" + "Row #4: 66\n" + "Row #5: \n" + "Row #6: 15,134\n" + "Row #7: 24\n" + "Row #8: \n");
    Result result = executeQuery(q2);
    final Cell cell = result.getCell(new int[] { 0, 0 });
    String sql = cell.getDrillThroughSQL(false);
    // the following replacement is for databases in ANSI mode
    //  using '"' to quote identifiers
    sql = sql.replace('"', '`');
    String tableQualifier = "as ";
    final Dialect dialect = getTestContext().getDialect();
    if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.ORACLE) {
        // " + tableQualifier + "
        tableQualifier = "";
    }
    assertEquals("select `store`.`store_country` as `Store Country`," + " `time_by_day`.`the_year` as `Year`," + " `store_1`.`store_country` as `x0`," + " `sales_fact_1997`.`unit_sales` as `Unit Sales` " + "from `store` " + tableQualifier + "`store`," + " `sales_fact_1997` " + tableQualifier + "`sales_fact_1997`," + " `time_by_day` " + tableQualifier + "`time_by_day`," + " `store` " + tableQualifier + "`store_1` " + "where `sales_fact_1997`.`store_id` = `store`.`store_id`" + " and `store`.`store_country` = 'USA'" + " and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `sales_fact_1997`.`unit_sales` = `store_1`.`store_id`" + " and `store_1`.`store_country` = 'USA'", sql);
}