calcite入门01-连接mysql
apache calcite 可以连接多种数据源,mysql,redis,elasticsearch等等,并且封装了jdbc协议进行查询。另外它也易于扩展,可以查询自定义的数据源。
MAVEN依赖
<!--calcite核心包-->
<dependency>
<groupId>org.apache.calcite</groupId>
<artifactId>calcite-core</artifactId>
<version>1.22.0</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.10.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.79</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>24.1-jre</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.26</version>
</dependency>
准备数据源
首先准备一个可以运行的Mysql服务器,其次,创建schema以及table,当然,也插入一些数据,这边准备了init.sql
create schema test;
use test;
create table student
(
pk_id varchar(32) not null
primary key,
name varchar(128) null
);
insert into test.student (pk_id, name)
values ('1', 'wang');
commit;
DEMO代码
/**
* test connect mysql by calcite
*
* to run this test normally please run init.sql on your mysql server first
*
* schema: test
* table: test.student
*/
public class Test01 {
// single schema query
public static void main(String[] args) throws Exception {
// check driver exist
Class.forName("org.apache.calcite.jdbc.Driver");
Class.forName("com.mysql.jdbc.Driver");
// the properties for calcite connection
Properties info = new Properties();
info.setProperty("lex", "JAVA");
info.setProperty("remarks","true");
// SqlParserImpl can analysis sql dialect for sql parse
info.setProperty("parserFactory","org.apache.calcite.sql.parser.impl.SqlParserImpl#FACTORY");
// create calcite connection and schema
Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
System.out.println(calciteConnection.getProperties());
SchemaPlus rootSchema = calciteConnection.getRootSchema();
// code for mysql datasource
MysqlDataSource dataSource = new MysqlDataSource();
// please change host and port maybe like "jdbc:mysql://127.0.0.1:3306/test"
dataSource.setUrl("jdbc:mysql://aaa.club/test");
dataSource.setUser("root");
dataSource.setPassword("123456");
// mysql schema, the sub schema for rootSchema, "test" is a schema in mysql
Schema schema = JdbcSchema.create(rootSchema, "test", dataSource, null, "test");
rootSchema.add("test", schema);
// run sql query
Statement statement = calciteConnection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from test.student");
while (resultSet.next()) {
System.out.println(resultSet.getObject(1) + "__" + resultSet.getObject(2));
}
statement.close();
connection.close();
}
}
- 保证驱动包存在
- 创建calcite的连接对象connection以及rootSchema
- 创建mysql的datasource
- 创建mysql的schema
- 将mysql的schema添加到rootSchema中
- 执行SQL
- 打印SQL结果