How to get database metadata from entity manager

Question

I have an app which is using hibernate and jpa. I need to find out which db it is connected to so that some native sql query i execute based on db say for eg. oracle and postgres. If I am using pure jdbc then it is straight forward to get the db metadata but dont know how to get from entity manager

Thanks

Answer 1

In Hibernate 4, you can get the database infos from the entity manager with that code:

org.hibernate.engine.spi.SessionImplementor sessionImp = 
     (org.hibernate.engine.spi.SessionImplementor) eManager.getDelegate();
DatabaseMetaData metadata = sessionImp.connection().getMetaData();
//do whatever you need with the metadata object...
metadata.getDatabaseProductName();

Cheers

Emmanuel

Answer 2

As a workaround, you can fetch the EntityManagerFactory properties to get the underlying database configuration, which is implementation specific.

• Hibernate : hibernate.connection.driver_class

• EclipseLink : eclipselink.target-database This property specifies the target database. In your case, it may have value as Oracle or PostgreSQL for respective database.

• General : javax.persistence.jdbc.driver

From this information, you can get the database currently connected.

EntityManagerFactory emf = entityManager.getEntityManagerFactory();     
Map<String, Object> emfProperties = emf.getProperties();

String driverClass = (String)emfProperties.get(PROPERTY);
//-- For PostgreSQL, it will have value "org.postgresql.Driver"

if(driverClass.lastIndexOf("postgresql") != -1)
    postGreSQL_DB = true;

---

Note : Not much clear on your application design, but it may happen that your application is connected to both the databases. If possible, you can try having separate EntityManager for each database, pointing to different persistence units in persistence.xml & can use it accordingly.

If this is not the case & only one of them is connected at a time, then you can simply verify it by entityManager.isOpen() or emf.isOpen().

Edit :

Connection connection = entityManager.unwrap(Connection.class);  
DatabaseMetaData metaData = connection.getMetaData();

Now from this, you can get database product name, driver etc.

Answer 3

Based on Emmanuel answer how to do it with Spring and hibernate:

 @Repository
 public class UserRepository {

    @PersistenceContext
    private EntityManager em;

    public void getMeta() {
        org.hibernate.engine.spi.SessionImplementor sessionImp = (org.hibernate.engine.spi.SessionImplementor) em.getDelegate();
        DatabaseMetaData metadata = null;
        try {
            metadata = sessionImp.connection().getMetaData();
            ResultSet res = metadata.getColumns(null, null, "USERS", "USERNAME");
            System.out.println("List of columns: ");
            while (res.next()) {
                System.out.println(
                        "  " + res.getString("TABLE_SCHEM")
                                + ", " + res.getString("TABLE_NAME")
                                + ", " + res.getString("COLUMN_NAME")
                                + ", " + res.getString("TYPE_NAME")
                                + ", " + res.getInt("COLUMN_SIZE")
                                + ", " + res.getInt("NULLABLE"));
            }
            res.close();
            System.out.println();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Answer 4

Use following line to get information related to your connection and datasource

entityManager.getEntityManagerFactory().getProperties().get("hibernate.connection.datasource");

Answer 5

if anybody else (like me) wants to try to get some jdbc information and you are using hibernate 4.x, you might try it that way:

import java.sql.Connection;
import java.sql.SQLException;
import org.hibernate.jdbc.Work;
public class ConnectionInfo implements Work {

public String dataBaseUrl;
public String dataBaseProductName;
public String driverName;

@Override
public void execute(Connection connection) throws SQLException {
    dataBaseUrl = connection.getMetaData().getURL();
    dataBaseProductName = connection.getMetaData().getDatabaseProductName();
    driverName = connection.getMetaData().getDriverName();
}

public String getDataBaseProductName() {
    return dataBaseProductName;
}

public void setDataBaseProductName(String dataBaseProductName) {
    this.dataBaseProductName = dataBaseProductName;
}

public String getDataBaseUrl() {
    return dataBaseUrl;
}

public void setDataBaseUrl(String dataBaseUrl) {
    this.dataBaseUrl = dataBaseUrl;
}

public String getDriverName() {
    return driverName;
}

public void setDriverName(String driverName) {
    this.driverName = driverName;
}
}

Now you can retrieve your information like that:

// -- snip
org.hibernate.ejb.EntityManagerImpl entityManagerImpl =  (org.hibernate.ejb.EntityManagerImpl) genericDBAccess.getEntityManager().getDelegate();
    Session session = entityManagerImpl.getSession();
    connectionInfo = new ConnectionInfo();
    session.doWork(connectionInfo);
// -- snap

Hope that helps! I drove crazy finding this information....

Answer 6

Base on this answer, here is Kotlin/Spring code to get an enum value depending on some known values for the databaseProductName:

@Component
class DbDetector {

    @Autowired
    private lateinit var dataSource: DataSource

    val dbType by lazy { detectDatabase() }

    private fun detectDatabase(): DbType {
        val rawName = dataSource.connection.use {
            it.metaData.databaseProductName
        }
        return when (rawName) {
            "MySQL" -> DbType.MY_SQL
            "Oracle" -> DbType.ORACLE
            "PostgreSQL" -> DbType.POSTGRES
            "Microsoft SQL Server" -> DbType.SQL_SERVER
            "HSQL Database Engine" -> DbType.HSQLDB
            "H2" -> DbType.H2
            else -> DbType.UNKNOWN
        }
    }
}

enum class DbType {
    MY_SQL, ORACLE, POSTGRES, SQL_SERVER, HSQLDB, H2, UNKNOWN
}

NB: Closing the Datasource & Connection Leaks

One critical thing that caused me pain and I wanted to point out explicitly was the closing of the database connection after use. Kotlin's use construct elegantly allows this:

val rawName = dataSource.connection.use {
                it.metaData.databaseProductName
            }

Below is previous, buggy code that I had which wasn't closing the connection afterwards. I've crossed it out to make it obvious that you shouldn't use it, because it might not obviously fail on first use and you may think it's OK:

val rawName = dataSource.connection.metaData.databaseProductName

For me, there were no apparent errors with this code for a while. But it was causing a subtle connection leak, which was only detected luckily when my connection pool maxed out during a test and a connection started to timeout. This answer then pointed me towards debugging the timeout with a leak detection setting in application.properties:

spring.datasource.hikari.leakDetectionThreshold=2000

Answer 7

In case you want yo get the dba name from the url

public String getDbName() throws SQLException {
    SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
    DatabaseMetaData metadata = sessionImp.connection().getMetaData(); 

    String dbName="";
    Pattern urlDbName = Pattern.compile("databaseName=([A-Za-z0-9\\-\\_]+)");
    Matcher m = urlDbName.matcher(metadata.getURL());
    while (m.find()) {
        dbName = m.group(1);
    }

    return dbName;   
}