Java TableNode.query方法代码示例

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_合并_OR条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("ID > 1 OR ID < 3");
    table.build();
    FilterPreProcessor.optimize(table, true);
    Assert.assertEquals(table.getWhereFilter().toString(), "1");

    table.query("ID > 1 OR ID > 3");
    table.build();
    FilterPreProcessor.optimize(table, true);
    Assert.assertEquals(table.getWhereFilter().toString(), "TABLE1.ID > 1");

    table.query("ID > 1 OR ID = 5");
    table.build();
    FilterPreProcessor.optimize(table, true);
    Assert.assertEquals(table.getWhereFilter().toString(), "TABLE1.ID > 1");
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_单表查询_复杂条件展开() {
    TableNode table = new TableNode("TABLE1");
    table.query("SCHOOL=1 AND (ID=4 OR ID=3)");
    extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, true);
    extraCmd.put(ConnectionProperties.EXPAND_OR, true);
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, false);
    extraCmd.put(ConnectionProperties.EXPAND_OR, false);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertTrue(((IMerge) qc).isUnion());// 是union查询
    Assert.assertTrue(((IMerge) qc).getSubNodes().get(0) instanceof IQuery);
    Assert.assertTrue(((IMerge) qc).getSubNodes().get(1) instanceof IQuery);
    IQuery query1 = (IQuery) ((IMerge) qc).getSubNodes().get(0);
    Assert.assertEquals("TABLE1.ID = 4", query1.getKeyFilter().toString());
    Assert.assertEquals("TABLE1.SCHOOL = 1", query1.getValueFilter().toString());
    IQuery query2 = (IQuery) ((IMerge) qc).getSubNodes().get(1);
    Assert.assertEquals("TABLE1.ID = 3", query2.getKeyFilter().toString());
    Assert.assertEquals("TABLE1.SCHOOL = 1", query2.getValueFilter().toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
/**
 * 虽然C1，C2上都存在单索引，但是C1，C2还是组合索引，这种情况下优先选择组合索引
 * 
 * @throws QueryException
 */
@Test
public void testChooseIndex选择组合索引() throws QueryException {
    TableNode table = new TableNode("TABLE9");
    QueryTreeNode qn = table.query("C1>10&&C2=3");
    qn.build();

    IndexMeta index = IndexChooser.findBestIndex(table.getTableMeta(),
        table.getColumnsRefered(),
        toDNFFilter(table.getWhereFilter()),
        table.getTableName(),
        extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE9._C1_C2");
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void testWhere_字段多级子查询() throws QueryException, SqlParserException {
    String subSql = "SELECT B.* FROM TABLE2 B WHERE B.ID=1 GROUP BY SCHOOL HAVING COUNT(*) > 1 ORDER BY ID DESC LIMIT 1";
    String sql = "SELECT NAME FROM TABLE1 WHERE NAME=(SELECT C.NAME FROM (" + subSql + ") C )";
    QueryTreeNode qn = query(sql);
    qn.build();

    TableNode table1 = new TableNode("TABLE1");
    QueryTreeNode qnExpected = table1.select("NAME");

    TableNode table2 = new TableNode("TABLE2");
    table2.alias("C").setSubAlias("B").select("*").query("B.ID=1");
    table2.groupBy("SCHOOL");
    table2.having("COUNT(*) > 1");
    table2.orderBy("ID", false);
    table2.limit(0, 1);
    table2.setSubQuery(true);

    QueryNode subQuery = new QueryNode(table2);
    subQuery.select("C.NAME");

    IColumn column = ASTNodeFactory.getInstance().createColumn().setColumnName("NAME");
    IBooleanFilter filter = ASTNodeFactory.getInstance()
        .createBooleanFilter()
        .setColumn(column)
        .setValue(subQuery)
        .setOperation(OPERATION.EQ);
    table1.query(filter);
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_等于子查询_存在多条件() {
    TableNode table1 = new TableNode("TABLE1");
    table1.query("ID = (SELECT ID FROM TABLE2 WHERE NAME = 'HELLO' AND SCHOOL = 'HELLO' ) AND NAME = 3 AND SCHOOL IN ('A','B')");
    table1.build();

    QueryTreeNode qn = SubQueryPreProcessor.optimize(table1);
    Assert.assertTrue(qn instanceof JoinNode);
    Assert.assertEquals(null, table1.getWhereFilter());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void testQuery_OrExpression() throws SqlParserException {
    String sql = "SELECT * FROM TABLE1 WHERE NAME = 2323 OR ID=1";
    QueryTreeNode qn = query(sql);
    qn.build();

    TableNode table1 = new TableNode("TABLE1");
    QueryTreeNode qnExpected = table1.query("NAME=2323 || ID=1");
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_存在OR条件_有字段无索引_构建全表扫描() {
    TableNode table1 = new TableNode("TABLE10");
    table1.query("ID = 1 OR C1 = 2 OR C2 = 1");
    table1.build();
    QueryTreeNode qn = optimize(table1, true, true, false);
    Assert.assertTrue(qn instanceof TableNode);
    // Assert.assertTrue(((TableNode) qn).isFullTableScan());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void testDelete_正常() throws SqlParserException {
    String sql = "DELETE FROM TABLE1 WHERE ID>=5 AND ID<=5";
    DeleteNode dn = delete(sql);
    dn.build();

    TableNode table1 = new TableNode("TABLE1");
    DeleteNode dnExpected = table1.delete();
    table1.query("ID>=5 AND ID<=5");
    dnExpected.build();

    assertEquals(dn, dnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_主键索引条件_特殊条件不走索引() {
    TableNode table = new TableNode("TABLE1");
    table.query("ID != 1 AND ID IS NULL AND ID IS NOT NULL AND ID LIKE '%A' AND ID = 2");
    build(table);

    Map<FilterType, IFilter> result = FilterSpliter.splitByIndex(FilterUtils.toDNFNode(table.getWhereFilter()),
        table);
    Assert.assertEquals("TABLE1.ID = 2", result.get(FilterType.IndexQueryKeyFilter).toString());
    Assert.assertEquals("(TABLE1.ID != 1 AND TABLE1.ID IS NULL AND TABLE1.ID IS NOT NULL AND TABLE1.ID LIKE %A)",
        result.get(FilterType.IndexQueryValueFilter).toString());
    Assert.assertEquals(null, result.get(FilterType.ResultFilter));
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void testWhere_字段子查询() throws SqlParserException {
    String sql = "SELECT NAME FROM TABLE1 WHERE NAME=(SELECT NAME FROM TABLE2 B WHERE B.ID=1)";
    QueryTreeNode qn = query(sql);
    qn.build();

    TableNode table1 = new TableNode("TABLE1");
    QueryTreeNode qnExpected = table1.select("NAME");

    TableNode table2 = new TableNode("TABLE2");
    table2.alias("B").select("NAME").query("B.ID=1");
    table1.query("NAME=(SELECT NAME FROM TABLE2 B WHERE B.ID=1)");
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_单表查询_value条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("NAME = 1");

    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(QUERY_CONCURRENCY.CONCURRENT, ((IMerge) qc).getQueryConcurrency());// 并行
    IDataNodeExecutor dne = ((IMerge) qc).getSubNode().get(0);
    Assert.assertTrue(dne instanceof IJoin);
    IJoin join = (IJoin) dne;
    IQuery left = (IQuery) join.getLeftNode();
    Assert.assertEquals("TABLE1._NAME.NAME = 1", left.getKeyFilter().toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void testQuery_复杂条件() throws SqlParserException, QueryException {
    String sql = "SELECT * FROM TABLE1 WHERE (SCHOOL=1 OR NAME=2) AND (ID=1)";
    QueryTreeNode qn = query(sql);
    qn.build();

    TableNode table1 = new TableNode("TABLE1");
    QueryTreeNode qnExpected = table1.query("(SCHOOL=1 || NAME=2) && (ID=1)");
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_主键索引条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("ID = 1");
    build(table);

    Map<FilterType, IFilter> result = FilterSpliter.splitByIndex(FilterUtils.toDNFNode(table.getWhereFilter()),
        table);
    Assert.assertEquals("TABLE1.ID = 1", result.get(FilterType.IndexQueryKeyFilter).toString());
    Assert.assertEquals(null, result.get(FilterType.IndexQueryValueFilter));
    Assert.assertEquals(null, result.get(FilterType.ResultFilter));
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_存在OR条件_所有字段均有索引_构建IndexMerge() {
    TableNode table1 = new TableNode("TABLE10");
    table1.query("ID = 1 OR C1 = 2");
    table1.build();
    QueryTreeNode qn = optimize(table1, true, true, true);
    Assert.assertTrue(qn instanceof MergeNode);
    Assert.assertTrue(qn.getChildren().get(0) instanceof TableNode);
    Assert.assertTrue(qn.getChildren().get(1) instanceof JoinNode);
}