Here are the examples of the java api class java.sql taken from open source projects.
1. DrillThroughTest#testDrillThrough2()
Project: mondrian
File: DrillThroughTest.java
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
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
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
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); }