CODEWITHSUNDEEP
javasqlpostgresqlconnection
lakshman
lakshman

Reputation: 2741

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?

Upvotes: 11

Views: 7699

Answers (3)

user8234870
user8234870

Reputation: 

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

Upvotes: 0

lakshman
lakshman

Reputation: 2741

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

Upvotes: 17

Steve11235
Steve11235

Reputation: 2923

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.

Upvotes: 0

Related Questions