Databases
StackOverflow2013
Postsdbo
POJava Swing & Postgres user authentication: Close old connection when new connection opened
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJava Swing & Postgres user authentication: Close old connection when new connection opened
    text
    copied!<p>I have a Java Swing application that accesses a Postgres database using a simple Singleton Pattern:</p> <pre><code>public class DatabaseConnection { private static final String uname = "*******"; private static final String pword = "*******"; private static final String url = "*******************************"; Connection connection; // load jdbc driver public DatabaseConnection(){ try{ Class.forName("org.postgresql.Driver"); establishConnection(); } catch (ClassNotFoundException ce) { System.out.println("Could not load jdbc Driver: "); ce.printStackTrace(); } } public Connection establishConnection() { // TODO Auto-generated method stub try{ connection = DriverManager.getConnection(url, uname, pword); } catch (SQLException e){ System.out.println("Could not connect to database: "); e.printStackTrace(); } return connection; } } public class SingletonConnection { private static DatabaseConnection con; public SingletonConnection(){} public static DatabaseConnection instance(){ assert con == null; con = new DatabaseConnection(); return con; } } </code></pre> <p>This is my user table created by Pgadmin3 (hence the ugly upper cases):</p> <pre><code>CREATE TABLE "user" ( id serial NOT NULL, "userRoleId" integer NOT NULL, "employeeId" bigint NOT NULL, "subjectId" bigint NOT NULL, username text NOT NULL, cryptpwd text NOT NULL, "userStatusId" integer NOT NULL, md5pwd text NOT NULL, CONSTRAINT pk_user PRIMARY KEY (id), CONSTRAINT "subjectId" FOREIGN KEY ("subjectId") REFERENCES subject (id) MATCH FULL ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT user_employee_id FOREIGN KEY ("employeeId") REFERENCES employee (id) MATCH FULL ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "user_userRole_id" FOREIGN KEY ("userRoleId") REFERENCES "userRole" (id) MATCH FULL ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "user_userStatus_id" FOREIGN KEY ("userStatusId") REFERENCES "userStatus" (id) MATCH FULL ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "unique_user_userName" UNIQUE (username) ) </code></pre> <p>Since this application will be run on many machines in a local network, I would like to have only a single connection instance per specific user. That is, if userA logs in from one machine, and userA logs in from another machine moments later, notifications should appear on both machines with the second log in having the option to continue with the connection - in which case, the existing connection is dropped/lost.</p> <p>I imagine I'd have to add a new column (logged_on boolean) in my user table ... in which case the second log in is handled by finding the value of logged_on and acting appropriately. My question is, how then will I be able to close the first connection? How can I maintain a maximum of one connection - per user - at database level?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload