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?

i think the best option which might support that out of the box in h2 database is [Pluggable or User-Defined Tables](https://www.h2database.com/html/features.html#pluggable_tables) .. — Raymond Nijland, Sep 27 '19 at 09:34

Evgenij Ryazanov · Accepted Answer · 2019-09-27T09:45:06.750

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;

edited Sep 27 '19 at 09:45

answered Sep 27 '19 at 09:36

Evgenij Ryazanov

6,960
2
10
18

that might indeed be a better option then mine suggestion in the comment +1.. Assuming h2 database can do query plan optimisations on external RDMS aswell – Raymond Nijland Sep 27 '19 at 09:46
If you declare a linked table using a source table (not a query) H2 tries to read its indexes from the source database and use them when possible. However, you shouldn't expect that the best plan will be always chosen. I can't say whether a couple of plain linked tables or a linked table with a query will be faster for your use case. If performance is critical for you, you should compare it by yourself with your real data. – Evgenij Ryazanov Sep 27 '19 at 10:37
I meant if you have a use case similar to that one from the question when multiple tables from another database are joined together and finally they are joined with some own table(s) of H2. Such query will be performed by two databases together and obliviously it can't be optimized in the same way as a normal query with own tables only. – Evgenij Ryazanov Sep 27 '19 at 10:48
i am not the topicstarter.. but the optimizer could check which values are existing local and request those remotely and merge the sets together (local/remote merge join algorthm).. But we are moving to much off topic in RDMS optimisation thoery now.. – Raymond Nijland Sep 27 '19 at 11:22
@Evgenij Ryazanov when I'm trying to create another linked table and then doing a ```SELECT``` on this linked table it comes up an error: ```org.h2.jdbc.JdbcSQLException: Column "COD" not found; SQL statement: ``` how can I fix this error? – jumpy Sep 28 '19 at 20:26
Try to execute `SELECT * FROM tableName` and check the actual names of the columns. For example, you may need to specify `"cod"` (in double quotes and in lower case) instead of unquoted `cod` if names are in lower case that is possible if you use some old version of H2. – Evgenij Ryazanov Sep 29 '19 at 03:31
I see another problem in your other question (https://stackoverflow.com/questions/58145540/h2-error-accessing-linked-table-with-sql-statement-select-from-null-t). Let's continue this discussion in that question. – Evgenij Ryazanov Sep 29 '19 at 04:02

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

1 Answers1