Using JooQ with SQL Server, getTables() method is returning all tables across all DB's on server

user_14023860 0
Viewed 163

When using the DSLContext.meta().getTables() method in Jooq to return a list of tables on a DB, it works perfectly on postgres and H2 servers. However, when the same call is made on a SQL Server database it ends up returning every table on ALL of the db's on the server, not just the datasource db.

So say in this example where there are 2 db's on the server, DB1 has 2 tables, and DB2 has 3 tables. If I wanted to just return the 2 tables in DB1 using DSLContext.meta().getTables(), it correctly returns these 2 tables on postgres and H2, however when on SQL Server it returns all 5 with the same code, only difference is the datasource.

Is there a way to return just the tables for a specific db when using Jooq with SQL Server? And is behavior a bug, or intentional and I should be doing something different for SQL Server dbs?

Here is the method that is using it if interested:

private boolean isTableExists(String table_name){
        boolean isTable = false;
        List<Table<?>> tables = dsl.meta().getTables(); //this is the problem line, returning the wrong number of tables on SQL Server
        for (Table<?> table : tables){
            if (table.getName().equals(table_name)){
                isTable = true;
                break;
            }
        }
        return isTable;
    }

Notes: I am not using any generated code, and I am using a Trial version of jooq 3.14.4. I've only been using Jooq for about 2 weeks.

jooq sql-server java
1 Answers

jOOQ's DSLContext.meta() API produces the tables for all the catalogs and all the schemas as per jOOQ 3.13. Hence, you'll have to add some logic to skip the undesired dbs, e.g. like this:

private boolean isTableExists(String tableName) {
    return dsl.meta()
       .getCatalogs()
       .stream()
       .filter(c -> c.getName().equals("databaseName"))
       .flatMap(c -> c.getSchemas().stream())
       .filter(s -> s.getName().equals("schemaName"))
       .flatMap(s -> s.getTables().stream())
       .anyMatch(t -> t.getName().equals(tableName));
}

In jOOQ 3.14 (see #10204), there will be additional API to filter out undesired objects prior to accessing meta information. This has the same effect, but is probably faster, e.g.

private boolean isTableExists(String tableName) {
    return !dsl.meta()
       .filterCatalogs(c -> c.getName().equals("databaseName"))
       .filterSchemas(s -> s.getName().equals("schemaName"))
       .filterTables(t -> t.getName().equals(tableName))
       .getTables()
       .isEmpty();
}
user_5217990
Related