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

Question

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?

http://stackoverflow.com/questions/16696688/no-suitable-driver-found-for-jdbcpostgresql-192-168-1-85432-nexentasearch — Jay, May 07 '14 at 15:49
@Jay:no. that's not helpful.please read the question carefully — lakshman, May 07 '14 at 15:51
Seems that's not possible for security reasons. Check this http://postgresql.1045698.n5.nabble.com/I-can-t-get-the-database-s-list-td2175993.html — Jay, May 07 '14 at 16:02
You must provide a slash `/` after the `//host:port`. `jdbc:postgresql://localhost:5432/` - https://jdbc.postgresql.org/documentation/80/connect.html — Thomas Taylor, Jun 30 '20 at 15:28

score 17 · Accepted Answer · edited Apr 13 '17 at 12:42

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

One important detail, is that user and password must not be specified. Otherwise it can't connect. — david.perez, Sep 27 '16 at 13:31

score 0 · Answer 2 · answered May 07 '14 at 15:52

0

AFAIK, that is beyond the capabilities of JDBC. I suggest you reconsider your approach to the bigger problem. Environment variables or a PROPERTIES file might be a solution.

answered May 07 '14 at 15:52

Steve11235

2,849
1
17
18

can you please more elaborate what this mean _Environment variables or a PROPERTIES file might be a solution._? – lakshman May 07 '14 at 15:54
Often, the reason the application doesn't know the database is because the application runs in different environment, such as Dev, Test, UAT, Prod. This generally involves a different server as well as the database, but the idea is the same. The idea is to describe the database (or server) external to the application so the value can be set, generally by administrators. – Steve11235 May 07 '14 at 19:32
1

It's not beyond the capabilities of jdbc.please read my answer.I have tested it and it works properly. – lakshman May 08 '14 at 02:51
That's interesting. Obviously a capability built into PostgreSQL. Looks like MySql supports something similar. – Steve11235 May 08 '14 at 12:30

score 0 · Answer 3 · answered Nov 21 '20 at 10:21

import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Dev Parzival
 */
public class TestingDatabase {
    static String driver="org.postgresql.Driver";
    static String jdbc_url="jdbc:postgresql://localhost:5432/";
    static String username="username",password="password";
    static PrintStream out;
    static{
        out=System.out;
    }
    public static void main(String $[]){
        //SELECT datname FROM pg_database
        try{
            Class.forName(driver);
            Connection connection= DriverManager.getConnection(jdbc_url,username,password);
            Statement statement=connection.createStatement();
            ResultSet result=statement.executeQuery("SELECT datname FROM pg_database");
            while(result.next()){
                System.out.println(result.getString(1));
            }
            connection.close();
        }catch(Exception ex){
            ex.printStackTrace();
        }
    }
}

Hope this might help someone somehow

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

3 Answers3