自定义 SQL 函数,并执行 SQL 查询。
准备如上的表,自定义函数查询返回 age 字段的平方。
相关准备工作在上一篇:https://snailgary.org/9c2d6b5842fa90afb1960b2cf303d6fe
自定义函数类
public static class MySquareFunction {
public int eval(int a) {
return a*a;
}
}
连接配置
// Build our connection
Connection connection = DriverManager.getConnection("jdbc:calcite:");
// Unwrap our connection using the CalciteConnection
CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
calciteConnection.getProperties().setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");
// Get a pointer to our root schema for our Calcite Connection
SchemaPlus rootSchema = calciteConnection.getRootSchema();
// Instantiate a data source, this can be autowired in using Spring as well
DataSource mysqlDataSource = JdbcSchema.dataSource(
"jdbc:mysql://localhost:3306/local_test",
"com.mysql.cj.jdbc.Driver", // Change this if you want to use something like MySQL, Oracle, etc.
"root", // username
"123456" // password
);
// Attach our Postgres Jdbc Datasource to our Root Schema
//rootSchema.add(POSTGRESQL_SCHEMA, JdbcSchema.create(rootSchema, POSTGRESQL_SCHEMA, postgresDataSource, null, null));
// Attach our MySQL Jdbc Datasource to our Root Schema
rootSchema.add(MYSQL_SCHEMA, JdbcSchema.create(rootSchema, MYSQL_SCHEMA, mysqlDataSource, null, null));
注册函数类
SchemaPlus post = rootSchema.add("custom_function", new AbstractSchema());
post.add("my_function", ScalarFunctionImpl.create(MySquareFunction.class,"eval"));
将自定义函数注册到 custom_function 空间中,使用 custom_function.functionName 调用。
编写 SQL
String customSql = "select custom_function.my_function(\"age\") as age2 from mysql.\"human\" h";
执行 SQL
Statement statement1 = connection.createStatement();
ResultSet resultSet1 = statement1.executeQuery(customSql);
DBTablePrinter.printResultSet(resultSet1);
输出结果如下:
+------+
| AGE2 |
+------+
| 324 |
+------+
| 324 |
+------+
| 324 |
+------+
| 324 |
+------+
巧了,都是 18 岁!