List all tables in an specific PostgreSQL database using java code

Question

actually i have googled a bit and i need corresponding SELECT command to following PostgreSQL shell command :

\dt schemaname.*

i managed to get all databases with following code :

            Statement statement = (Statement) connection.createStatement();
            ResultSet rs = statement
                    .executeQuery("SELECT datname FROM pg_database");
            while (rs.next()) {
                System.out.println("DB Name : " + rs.getString(1));
           //i need another while here to list tables 
           //inside the selected database
}

i tried following statement, but no luck :

statement.executeQuery("SELECT table_schema,table_name FROM "
                                + rs.getString(1)
                                + " ORDER BY table_schema,table_name");

this is the error i am getting :

org.postgresql.util.PSQLException: ERROR: relation "template1" does not exist
  Position: 37
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    at com.isiran.rayten.rg.db.bare.wrapper.PGWrap.main(PGWrap.java:64)

Answer 1

Use the DatabaseMetaData object to query information, eg getTables(...):

DatabaseMetaData dbmd = connection.getMetaData();
try (ResultSet tables = dbmd.getTables(null, null, "%", new String[] { "TABLE" })) {
    while (tables.next()) {
        System.out.println(tables.getString("TABLE_NAME"));
    }
}

This will return all tables in the database, you may need to specify values for catalog and/or schemaPattern to get a more specific result.

Answer 2

If you use psql -E, it'll echo the actual queries that get run when you type commands such as \dt:

denis=# \dt public.*
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
      AND n.nspname !~ '^pg_toast'
  AND n.nspname ~ '^(public)$'
ORDER BY 1,2;
**************************

These queries can then be simplified or amended per your specific use-case.

Answer 3

To list all tables from database you have to read table pg_catalog.pg_tables But unfortunately you have to be logged-in in database. So in place where you wrote comments

//i need another while here to list tables 
//inside the selected database

Before loop for tables you need to log-in in this database.