apache calcite 进阶篇(一)

介绍

上一篇已经介绍过如何利用SQL解析查询出hello world,通过工厂模式实例化Scheam, schema包含表,表包含数据。没看过的可以去看https://blog.csdn.net/qq_27408211/article/details/86497097

用过工具的基本也都是这种形态。今天学习用不一样的方式也非常简单的代码实现复杂的SQL解析。

代码结构

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.calcite</groupId>
    <artifactId>calcite-test</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>calcite-test</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.calcite/calcite-core -->
        <dependency>
            <groupId>org.apache.calcite</groupId>
            <artifactId>calcite-core</artifactId>
            <version>1.18.0</version>
        </dependency>
    </dependencies>
</project>

讲解

相信大家都见过以下代码

这是一段连接jdbc数据的代码,需要在pom文件引入mysql驱动。换成calcite之后。变成

Client01.java

package com.calcite2;

import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.SchemaPlus;

import java.sql.*;

public class Client01 {

    /**
     * //        SchemaFactory factory = new ReflectiveSchema.Factory();
     * //        final ImmutableMap.Builder<String, Object> builder = ImmutableMap.builder();
     * //        builder.put("class","com.calcite2.FoodMart");
     * //        builder.put("staticMethod","instance");
     * //        Map<String,Object> operand = builder.build();
     * //        Schema schema = factory.create(rootSchema,"hr",operand);
     * //        rootSchema.add("hr",schema);
     * */
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.calcite.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:calcite:lex=JAVA");
        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
        final SchemaPlus rootSchema = calciteConnection.getRootSchema();
        rootSchema.add("hr", new ReflectiveSchema(new HrSchema()));
        calciteConnection.setSchema("hr");  // 设置默认Schema
        Statement statement = calciteConnection.createStatement();
        ResultSet resultSet = statement.executeQuery(
                "select d.deptno, min(e.empid) from hr.emps as e join hr.depts as d on e.deptno = d.deptno " +
                        "where e.deptno = 10 group by d.deptno having count(*) > 1");
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        int lineIndex = 1;
        while(resultSet.next()){
            System.out.println("行序号 => " + lineIndex);
            for (int i = 1; i < columnCount + 1; i++) {
                Object value = resultSet.getObject(i);
                System.out.println(String.format("\t列序号 => %s, 值 => %s, 类型 => %s", i,value,metaData.getColumnTypeName(i)));
            }
            lineIndex += 1;
        }
        resultSet.close();
        statement.close();
        connection.close();
    }
}

以上代码着重点

上面的代码最重要的就是 

new ReflectiveSchema(new HrSchema()) 这段代码可以看源代码是通过反射实例化Schema中的字段,设置hr数据库。
calciteConnection.setSchema("hr");  // 设置默认Schema,如果没有这句,每次写SQL都要指明hr.表名。

 

HrSchema.java

没错,就是模拟两个表的之前的关系。数据结构如图

接下来我们的任务是 查出部门编号为10并且至少有1个员工部门编号和最小员工ID

Employee.java

package com.calcite2;

public class Employee {
    public final int empid;
    public final int deptno;
    public final String name;
    public final float salary;
    public final Integer commission;

    public Employee(int empid, int deptno, String name, float salary, Integer commission) {
        this.empid = empid;
        this.deptno = deptno;
        this.name = name;
        this.salary = salary;
        this.commission = commission;
    }

    @Override public boolean equals(Object obj) {
        return obj == this
                || obj instanceof Employee
                && empid == ((Employee) obj).empid;
    }

    public String toString() {
        return "Employee [empid: " + empid + ", deptno: " + deptno
                + ", name: " + name + "]";
    }
}

Department.java

package com.calcite2;

import java.util.List;

public class Department {
    public final int deptno;
    public final String name;
    
    @org.apache.calcite.adapter.java.Array(component = Employee.class)
    public final List<Employee> employees;

    public Department(int deptno, String name, List<Employee> employees) {
        this.deptno = deptno;
        this.name = name;
        this.employees = employees;
    }

    @Override public boolean equals(Object obj) {
        return obj == this
                || obj instanceof Department
                && deptno == ((Department) obj).deptno;
    }

    public String toString() {
        return "Department [deptno: " + deptno + ", name: " + name
                + ", employees: " + employees + "]";
    }
}

一对多数据关系一定要加上@org.apache.calcite.adapter.java.Array(component = Employee.class) 这句,不然SQL会报空指针

 

如上代码就可以完成稍微复杂的关系查询了如:

select d.deptno, min(e.empid) 
    from 
        hr.emps as e 
        join 
        hr.depts as d 
    on e.deptno = d.deptno
where e.deptno = 10 
group by d.deptno 
having count(*) > 1

两个表关联查询结果:

展开阅读全文

没有更多推荐了,返回首页