I'm trying to drop a DB. Before dropping the DB I just want to check if there are any clients connected to that particular DB.
If No, I can drop it.
If Yes, I want to give the user some useful information.
I know this query, so I can see the number connections made to that DB.
select count(*) from pg_stat_activity
How can I do this in Java?
I would not bother checking pg_stat_activity, since even if you did, someone could access the database right afterward before you initiate the DROP DATABASE command.
Rather, I would recommend you just issue the DROP DATABASE command using whatever method you're using the interact with Postgres (JDBC, MyBatis, etc.) and surround it with a try/catch block. The DROP DATABASE command will fail if there are users connected to it, so you can simply execute the command and then handle the exception -- perhaps sleep for a few seconds and try again, or something to that effect.
However, if you wanted to force any active users off of the database, pg_stat_activity would come into play and you could terminate their backend connections, as outlined here: How to drop a PostgreSQL database if there are active connections to it? and then issue the DROP DATABASE command.
Edit in response to comment from OP:
Like I said, since the DROP command would fail if there are connections to the DB, I wouldn't bother checking first, but if you really want to see what the connections are (except for the connection you're using the check), you can use a similar query to what was in the link above, just with the pg_terminate_backend call.
SELECT *
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
AND pid <> pg_backend_pid();
