Hibernate / DB Metadata

Background

In the early stages of learning Hibernate, I wanted to get some metadata about my database.

But, was lost as to how to do so.

Outline

  1. Connect to Database
  2. Get Session
    • Cast Session to sessionImpl
    • Get Connection from sessionImpl
    • Get metadata from Connection
      • Access metadata
        • getURL
        • getDriverName
        • getDatabaseProductName
        • getDatabaseProductVersion
        • getUserName
        • getDefaultTransactionIsolation

Code

void getDBMetadata(Session session)
    throws Exception
    {

        SessionImpl sessionImpl;
        Connection conn;
        DatabaseMetaData dbMetadata;

        int iDefaultTransactionIsolation;
        String strDefaultTransactionIsolation = null;

        //get Session Implementation
        sessionImpl = (SessionImpl) session;

        //Get Session Implementation Connection
        conn = sessionImpl.connection();

        //Get Metadata
        dbMetadata = conn.getMetaData();

        System.out.println
        (
              "Database URL :- "
            + dbMetadata.getURL()
        );

        System.out.println
            (
                  "Driver Name :- "
                + dbMetadata.getDriverName()
            );

        System.out.println
        ("Database Product Name :- "
            + dbMetadata.getDatabaseProductName()
        );

        System.out.println
            (
                "Database Product Version :- "
                        + dbMetadata.getDatabaseProductVersion()
            );

        System.out.println
        (
            "Database Username:- "
                + dbMetadata.getUserName()
        );          

        iDefaultTransactionIsolation
        	= dbMetadata.getDefaultTransactionIsolation();

        strDefaultTransactionIsolation
        	= getDefaultTransactionIsolationAsString
        	  (
    			  iDefaultTransactionIsolation
			  );

        System.out.println
        (
            "Default Isolation Level ( as int ):- "
                    + dbMetadata.getDefaultTransactionIsolation()
        );  

        if (strDefaultTransactionIsolation != null)
        {

	        System.out.println
	        (
	            "Default Isolation Level ( as String ) :- "
	                    + strDefaultTransactionIsolation
	        );
        }

    }

 

    String getDefaultTransactionIsolationAsString(int value)
    {

    	String strValue = null;

    	switch(value)
    	{
    	   case java.sql.Connection.TRANSACTION_NONE:
    		  strValue = "None";
    	      break; 

    	   case java.sql.Connection.TRANSACTION_READ_COMMITTED :
    		  strValue = "Read Committed";
    	      break; 

    	   case java.sql.Connection.TRANSACTION_READ_UNCOMMITTED :
     		  strValue = "Read UnCommitted";
     	      break; 

    	   case java.sql.Connection.TRANSACTION_REPEATABLE_READ :
      		  strValue = "Repeatable Read";
      	      break; 

    	   case java.sql.Connection.TRANSACTION_SERIALIZABLE :
       		  strValue = "Serializable";
       	      break; 

    	   default :
    	      strValue = null;
    	}

    	return (strValue);

    }

Output

Output – Text


Database URL :- jdbc:sqlserver://localhost:1433;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustStoreType=JKS;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=sakila;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;
Driver Name :- Microsoft JDBC Driver 7.0 for SQL Server
Database Product Name :- Microsoft SQL Server
Database Product Version :- 14.00.3045
Database Username:- sakila
Default Isolation Level ( as int ):- 2
Default Isolation Level ( as String ) :- Read Committed

Output – Image

 

Source Code Control

GitHub

DanielAdeniji/HibernateDBMetadata
Link

 

References

  1. Oracle.com
    • java.sql
      • DatabaseMetaData
      • Connection
    • API
      • Constant Field Values
    • Sun Java System Application Server Platform Edition 8.2 Developer’s Guide
      • Using JDBC Transaction Isolation Levels
  2. jboss.org
    • org.hibernate
      • org.hibernate.impl

 

Eclipse – Data Source Explorer – SQL Server – Configuration ( Day 01 )

Background

Ran into a blocking issue while playing around with Eclipse.

The issue surfaced while trying to configure Hibernate to connect to SQL Server.

 

Data Source Explorer

I tried using the Hibernate Data Tools to connect to my back-end database; SQL Server in this case.

Finally gave up and wanted to see if I can reproduce the error using other data tools.

Process

Outline

  1. Perspective
    • Change to Data Development Perspective
  2. Driver Templates
    • Does Not Work
      • Microsoft SQL Server 2016 JDBC Driver
        • Specify a Driver Template and Definition Name
          • Driver Name :- Microsoft SQL Server 2016 JDBC Driver
          • Driver Type :- Microsoft SQL Server 2016 JDBC Driver
        • Tab :- JAR List
          • Removed
            • Removed sqljdbc.jar
          • Added
            • Added mssql-jdbc-7.0.0.jre10.jar
        • Tab :- Properties
          • Filled out properties tab
        • Tested Connection
          • Errors Out
            • java.lang.UnsupportedClassVersionError: com/microsoft/sqlserver/jdbc/SQLServerDriver has been compiled by a more recent version of the Java Runtime (class file version 54.0), this version of the Java Runtime only recognizes class file versions up to 52.0
    • Works
      • Microsoft SQL Server 2008 JDBC Driver
        • Specify a Driver Template and Definition Name
          • Driver Name :- Microsoft SQL Server 2008 JDBC Driver
          • Driver Type :- Microsoft SQL Server 2008 JDBC Driver
        • Tab :- JAR List
          • Removed
            • Removed sqljdbc.jar
          • Added
            • Added mssql-jdbc-7.0.0.jre8.jar
        • Tab :- Properties
          • Filled out properties tab
        • Tested Connection
          • Ping Successful
  3. Data Explorer
    • Review Data Explorer

Perspective

Database Development

Image

perspective.OpenPerspective.01.20190505.1033PM.PNG

Connection Profile

Driver Templates

Driver Templates – v 2016

Image
Image – Connection Profile

connectionProfile.01.20190505.1052PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Name/Type

DriverTemplateAndDriverName.Tab.NameAndType.01.20190505.1059PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 01

DriverTemplateAndDriverName.Tab.JARList.01.20190505.1100PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 02

Removed sqljdbc.jar

DriverTemplateAndDriverName.Tab.JARList.02.20190505.1101PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 04

Added mssql-jdbc-7.0.0.jre10.jar

DriverTemplateAndDriverName.Tab.JARList.03.20190505.1121PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 01

Review Properties Tab

DriverTemplateAndDriverName.Tab.Properties.01.20190505.1104PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 02

Completed Properties Tab

DriverTemplateAndDriverName.Tab.Properties.02.20190505.1105PM.PNG

Image – Specify a Driver and Connection Details

Review Driver and Connection Details ….

connectionProfile.jre10.01.SpecifyADriverAndConnectionDetails.02.20190505.1118PM.PNG

Image – Ping failed!

Ping failed.

pingFailed.20190505.1120PM.PNG

Ping failed.

java.lang.UnsupportedClassVersionError: com/microsoft/sqlserver/jdbc/SQLServerDriver has been compiled by a more recent version of the Java Runtime (class file version 54.0), this version of the Java Runtime only recognizes class file versions up to 52.0
	at java.lang.ClassLoader.defineClass1(Native Method)
	at java.lang.ClassLoader.defineClass(Unknown Source)
	at java.security.SecureClassLoader.defineClass(Unknown Source)
	at java.net.URLClassLoader.defineClass(Unknown Source)
	at java.net.URLClassLoader.access$100(Unknown Source)
	at java.net.URLClassLoader$1.run(Unknown Source)
	at java.net.URLClassLoader$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at java.net.FactoryURLClassLoader.loadClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:327)
	at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
	at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
	at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
	at org.eclipse.datatools.enablement.msft.internal.sqlserver.connection.JDBCSQLServerConnectionFactory.createConnection(JDBCSQLServerConnectionFactory.java:27)
	at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
	at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
	at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
	at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Driver Templates – v 2008

Image
Image – Connection Profile

connectionProfile.01.20190505.1052PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Name/Type

Please choose “Microsoft SQL Server 2008 JDBC Driver

ConnectionProfile.SpecifyADriverAndDefinitionName.Tab.NameAndType.01.20190505.1112PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 01

DriverTemplateAndDriverName.Tab.JARList.01.20190505.1100PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 02

Removed sqljdbc.jar

DriverTemplateAndDriverName.Tab.JARList.02.20190505.1101PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 03

Using the OS “Select the file” functionality reach for mssql-jdbc-7.0.0.0.jre8.jarpingSucceedded.20190505.1124PM.PNG.

DriverTemplateAndDriverName.Tab.JARList.SelectTheFile.03.20190505.1122PM.PNG

 

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 04

Added mssql-jdbc-7.0.0.jre8.jar

DriverTemplateAndDriverName.Tab.JARList.04.20190505.1123PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 01

Review Properties Tab

DriverTemplateAndDriverName.Tab.Properties.01.20190505.1104PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 02

Completed Properties Tab

DriverTemplateAndDriverName.Tab.Properties.02.20190505.1105PM.PNG

Image – Specify a Driver and Connection Details

Review Driver and Connection Details ….

connectionProfile.jre10.01.SpecifyADriverAndConnectionDetails.02.20190505.1118PM.PNG

Image – Test Connection

Tested Connection

pingSucceedded.20190505.1124PM.PNG

 

Issues

Java Runtime Compatibility ( JRE )

Hibernate

Compatibility Matrix

Hibernate Compatibility Matrix is available here.

Image

compatibilityMatrix.01.20190506.1250AM

Explanation
  1. Hibernate ORM
    • v6.0
      • Java 8 or 11
    • v5.4
      • Java 8 or 11

Microsoft JDBC Driver

mssql-jdbc-7.0.0.jre10.jar

Naming Convention

The name, mssql-jdbc-7.0.0.jre10.jar, is telling :-

  1. mssql
  2. jdbc
  3. 7.0.0
    • Version of MSSQL/JDBC is 7
  4. jre10
    • Requires Java Runtime 10
  5. jar
    • Jar File
Implication
  1. mssql-jdbc-7.0.0.jre10.jar requires Java JRE 10
    • Hibernate does not support JRE 10
      • It appears we have an incompatibility

mssql-jdbc-7.0.0.jre8.jar

Naming Convention

The name, mssql-jdbc-7.0.0.jre8.jar, is telling :-

  1. mssql
  2. jdbc
  3. 7.0.0
    • Version of MSSQL/JDBC is 7
  4. jre8
    • Requires Java Runtime 8
  5. jar
    • Jar File
Implication
  1. mssql-jdbc-7.0.0.jre8.jar requires Java JRE 8
    • Hibernate does support JRE 8
      • It appears we are good with compatibility

 

 

Data Explorer

Images

Image – Data Explorer – 01

DataSourceExplorer.02.20190505.1126PM.PNG

Dedicate

Always a dedicated piece.

This time it is Kenji Hasunuma.

  1. Kenji Hasunuma
    • How to connect to SQL Server 2016 by Eclipse DTP (en)
      Link

DBeaver – Database Connection Configuration – IBM – DB2 / UDB JDBC Driver

Background

As always coming back to DBeaver.

This time to connect to a UDB Instance running on Linux.

BTW, UDB means Universal Database.

UDB JDBC Driver

Artifacts

DB2 JDBC Driver Versions and Downloads

The URL for DB2 JDBC Drivers is Link.

Here is a current snapshot.

artifacts_db2drivers_20180816_1236PM

v11.1 FP0 ( GA )

We chose the v11 FP0 ( GA ) which is a baseline install; specifically 4.21.29.

Others might choose v11 M3 FP3; which includes the latest patches.

The URL is Link.

Here is a screen shot:

artifacts_db2drivers_v11_20180816_0110PM

IBM Data Server Driver for JDBC and SQLJ ( JCC Driver )

As all we need is the JDBC Driver, we chose “IBM Data Server Driver for JDBC and SQLJ (JCC Driver)“.

One needs membership account to actually download, so please login in with one if you have already registered.  Else, please create one, as it is free.

Download

In short, we downloaded “IBM Data Server Driver for JDBC and SQLJ (JCC Driver)“.

Extracted

Image

zip_extracted_20180816_0216PM.PNG

Textual

  1. db2jcc.jar
    • JDBC v3.0
  2. db2jcc4.jar
    • JDBC 4.0

Preparation

Network

Port Number

Let us determine the Network Port Number that our DB/2 Instance is listening on.

db2 get dbm cfg

Syntax


db2 get dbm cfg

Sample


db2 get dbm cfg | grep SVCE

Output

db2getdbmcfg_20180816_0230PM.PNG

Databases

Outline

Let us determine the list of databases that are available on our UDB Instance.

db2 list database directory

Syntax


 db2 list database directory

Sample


 db2 list database directory | grep "Database name"

Output

listDatabases_20180816_0242PM.PNG

 

DBeaver

Database

Connections

Steps

Select new connection type
Image

SelectNewConnectionType_20180816_0644AM.PNG

Textual
  1. DB2
    • DB2 LUW
Driver settings – Download driver files
Image

downloadDriverFiles_20180816_0647AM.PNG

Textual
  1. Click on the “Add JARs” button
Driver settings – Edit Driver – Add File – Post
Image

EditDriver_AddFile_After_20180816_0647AM.PNG

Textual
  1. JAR file “db2jcc4.jar” added
Create new connection – DB2 Connection Settings – Tab – General
Image

EditDriver_Tab_Database_20180816_0709AM ( Edited ).PNG

Textual
  1. Host
  2. Port
  3. Database
  4. username
  5. Password
Create new connection – DB2 Connection Settings – finish connection creation
Image

finishConnection_20180816_0719AM ( Edited ).PNG

 

References

  1. IBM
    • Home > DB2 for Linux UNIX and Windows 9.5.0 > Database administration > Administrative interfaces > Administrative SQL routines and views > Supported routines and views > Snapshot routines and views
      • APPLICATIONS administrative view – Retrieve connected database application information
        Link