How to get UserName from java.sql.Connection?

Question

I have a data source for SQLServer created in Weblogic with username 'sa'.

In code I am using following to get user name.

Context ctx = new InitialContext(prop);
Object obj = ctx.lookup("sqlserver1");
System.out.println("Data Source Found….");
DataSource ds = (DataSource) obj;
Connection conn = ds.getConnection();
DatabaseMetaData mtdt = conn.getMetaData();
// Get UserName
System.out.println("User name: " + mtdt.getUserName());

But above code always returns 'dbo' as the username. I expected the username to be 'sa'. If the DB is Oracle it works fine. Is there a generic way for me to get user name for all different types of database.

Answer 1

The correct method should be DatabaseMetaData.getUserName(), but as you demonstrate not all databases implement that correctly. Another way would be to use the JDBC function escape USER() eg (eg SELECT {fn USER()} FROM DUAL), but not all drivers implement all JDBC escapes, and it could just be that this returns the same as the DatabaseMetaData. You could also try the SQL standard defined CURRENT_USER (or USER) in a query, but there you have two problems: 1) some databases require you to select from a table (eg DUAL in Oracle, some don't) and 2) not all databases implement all parts of the SQL standards, so the CURRENT_USER or USER context variable might be absent.

But as you have the DataSource object, you could try to get the user property from the datasource (it is defined in table 9.1 of the JDBC 4.1 specification, although the property names described there are not all required).

So for example:

Method getter = new PropertyDescriptor("user", ds.getClass()).getReadMethod();
String value = (String) getter.invoke(ds);

This assume that 1) the DataSource ds has a getUser() and 2) that it is actually set (for example with SQL Server integrated security the datasource doesn't need to know about a user).

Answer 2

FWIW, Java 1.7 provides the Connection.getSchema() method. I don't know how widely 1.7 has been adopted at this point however, so this might just be something to keep in mind for future reference.