Doing JOIN between H2 table and ResultSet (select from MySQL table)

Question

I have to database, a MySQL database and a H2 database embedded in my Java application.

I want to CREATE TABLE on H2 database joining a H2 table and a ResultSet, that is a SELECT from MySQL database.

I want to perform this query on H2 database.

CREATE TABLE TableName AS
  SELECT l.*, m.Act FROM temp AS l JOIN
    (SELECT p.DES_PR AS Act, c.DES_COM AS com FROM
      table_com AS c JOIN table_pr AS p
         ON c.COD_PR = p.COD_PR) AS m
    ON l.sel = m.com
ORDER BY Cod;

All the table except temp is on MySQL database. temp table is on H2 database.

How can I create TableName on H2 database?

Answer 1

You need to create a LINKED TABLE for each MySQL table that you want to use in H2.

CREATE LINKED TABLE tableName('', 'jdbc:mysql://.......', 'userName', 'password', 'tableNameInMySQL');

See the complete syntax in the documentation: https://h2database.com/html/commands.html#create_linked_table

Then you will be able to use the tableName as a table in H2 in your query in AS clause of CREATE TABLE and in all other places where you need them.

Finally you can drop all these linked tables when they will be no longer in use.

For better performance you can also try to create a single linked table using a query with joins between MySQL tables as described in documentation, queries must be enclosed in parentheses. Something like

CREATE LINKED TABLE m('', 'jdbc:mysql://.......', 'userName', 'password',
'(SELECT p.DES_PR AS Act, c.DES_COM AS com FROM
      table_com AS c JOIN table_pr AS p
         ON c.COD_PR = p.COD_PR)');

and use it in your query

CREATE TABLE TableName AS
  SELECT l.*, m.Act FROM temp AS l JOIN
    m
    ON l.sel = m.com
ORDER BY Cod;