Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to connect to PostgreSQL server to query the database names list

I can use jdbc:postgresql://host:port/database to connect to a database in postgresql server using jdbc driver.

But I wanted to connect to the postgresql server and find the database list there. when I used jdbc:postgresql://localhost:5432, I got an exception called

java.sql.SQLException: No suitable driver found for jdbc:postgresql://localhost:5432

is there any other driver or any method to get a connection to the server without knowing the database names in server and query the database list there?

like image 261
lakshman Avatar asked May 07 '14 15:05

lakshman


People also ask

How do I find the database name in PostgreSQL?

Step 1: Log in to the server using the SQL Shell (psql) app. Step 2: Run the following query: SELECT datname FROM pg_database; psql runs the query against the server and displays a list of existing databases in the output.

How do I see all postgres databases?

Summary. Use \l or \l+ in psql to show all databases in the current PostgreSQL server. Use the SELECT statement to query data from the pg_database to get all databases.


1 Answers

Ok. I have figured it out my self. I can use this string to connect to the server with jdbc driver.

jdbc:postgresql://localhost:5432/?

and can use this code snippet to get the database list

private void listDownAllDatabases() {
        try {
            PreparedStatement ps = connection
                    .prepareStatement("SELECT datname FROM pg_database WHERE datistemplate = false;");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

References: I used this dba stackexchange answer to get all the database list

like image 144
lakshman Avatar answered Oct 24 '22 16:10

lakshman