create schema test2;
use test2;
create table student2
(
    pk_id varchar(32)  not null
        primary key,
    name  varchar(128) null
);
insert into test2.student2 (pk_id, name)
values ('2', 'lu');
insert into test2.student2 (pk_id, name)
values ('1', 'wang');
commit;

select *
from test.student
         left join test2.student2
                   on test.student.pk_id = test2.student2.pk_id

/**
 * test connect mysql by calcite
 *
 * to run this test normally please run init2.sql on your mysql server first
 *
 * schema: test & test2
 * table: test.student & test2.student2
 */
public class Test02 {
    // two schema query for mysql
    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);
        // code for mysql datasource2
        MysqlDataSource dataSource2 = new MysqlDataSource();
        dataSource2.setUrl("jdbc:mysql://aaa.club/test2");
        dataSource2.setUser("root");
        dataSource2.setPassword("123456");
        // mysql schema, the sub schema for rootSchema, "test2" is a schema in mysql
        Schema schema2 = JdbcSchema.create(rootSchema, "test2", dataSource2, null, "test2");
        rootSchema.add("test2", schema2);
        // run sql query
        Statement statement = calciteConnection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from test.student " +
                "left join test2.student2 on test.student.pk_id = test2.student2.pk_id");
        while (resultSet.next()) {
            System.out.println(resultSet.getObject(1) + "__" + resultSet.getObject(2));
        }
        statement.close();
        connection.close();
    }
}