Java/JDBC – SQL Server – “Integrated Security”

Background

Wanted to go over the steps to take when one needs to connect to a Microsoft SQL Server Instance using Integrated Security.

Outline

  1. Java
    • JDBC
      • JDBC Connecting String
    • Class Path
      • Includes SQL Server JDBC Driver
  2.  System
    • System Path
      • Add [jdbc-sql]\enu\auth\[platform]\

Tasks

Java

JDBC

JDBC Connecting String

Outline
  1. JDBC URL
    • IntegratedSecurity=True
    • authenticationScheme=NativeAuthentication
Syntax

jdbc:sqlserver://{server-name}:{port};IntegratedSecurity=True;authenticationScheme=NativeAuthentication;databaseName={database};

Sample

jdbc:sqlserver://localhost:1433;IntegratedSecurity=True;authenticationScheme=NativeAuthentication;databaseName=sakila;

java Class Path

SQL Server JDBC Driver

Please include your jdbc driver as part of your classpath when you launch your app.

In our case, %_sqlServerJDBCPath%\ sqljdbc_7.0\enu\mssql-jdbc-7.0.0.jre8.jar

 

System Path

Enhance your system path to include the sqljdbc_[version]\enu\auth\x64\ folder.

We are on SQL JDBC Version 7 and our platform is 64-bit.

Our relative path is sqljdbc_7.0\enu\auth\x64.

Code

Sample


set "_libSQLServerAuth_Folder=C:\downloads\Microsoft\SQLServer\jdbc\v7.0.0.0\extract\sqljdbc_7.0\enu\auth\x64\"

set path=%path%;%_libSQLServerAuth_Folder%

Trouble Shooting

Here are a couple of areas that you may stumble upon.

WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path

Error

Error Image

Error Text


WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path: [C:\Program Files\Java\jdk-11.0.3\bin,
[WARN ] 2019-06-10 12:57:02.390 [main] JdbcEnvironmentInitiator - HHH000342: Could not obtain connection to query metadata : null
[INFO ] 2019-06-10 12:57:02.424 [main] Dialect - HHH000400: Using dialect: org.hibernate.dialect.SQLServerDialect
[WARN ] 2019-06-10 12:57:02.502 [main] SqlExceptionHelper - SQL Error: 0, SQLState: 08S01
[ERROR] 2019-06-10 12:57:02.509 [main] SqlExceptionHelper - This driver is not configured for integrated authentication.
Exception :- Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]

Error Explanation

  1. WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path

Resolution

  1. java.library.path
    • WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
  2. Please include the folder where sqljdbc_auth is located in your system path.

 

Screen Shots

Hibernate

Working

Image -01

References

  1. Microsoft
    • Docs
      • Docs / SQL / Connect / JDBC / Programming Guide for JDBC SQL Driver / Guide articles
        • Building the Connection URL
          Link
    • CSS SQL Server Engineers
      • Adam W. Saxton
        • JDBC: This driver is not configured for integrated authentication
          Link

 

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

 

Hibernate/Annotation – Error – “Schema-validation: missing column”

Background

Adding more functionality to a POJO ( Plain Old Java Object) class that relies on Annotations.

Error

But, here ended up with an error!

Error Message


Schema-validation: missing column [bResetDependantEntityCountryIDOnDelete] in table [dbo.country]
Failed to create sessionFactory object.org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [bResetDependantEntityCountryIDOnDelete] in table [dbo.country]
Exception in thread "main" java.lang.ExceptionInInitializerError
        at sakila.helloDB.dbWork(helloDB.java:166)
        at sakila.helloDB.run(helloDB.java:55)
        at sakila.helloDB.main(helloDB.java:42)
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [bResetDependantEntityCountryIDOnDelete] in table [dbo.country]
        at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateTable(AbstractSchemaValidator.java:136)
        at org.hibernate.tool.schema.internal.GroupedSchemaValidatorImpl.validateTables(GroupedSchemaValidatorImpl.java:42)
        at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:89)
        at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:68)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:192)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
        at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:309)
        at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
        at sakila.helloDB.getDBSession(helloDB.java:82)
        at sakila.helloDB.dbWork(helloDB.java:116)
        ... 2 more

Error Image

SchemaValidationMissingColumn.20190516.0828PM.PNG

Code

Outline

  1. “Decorate” variable declaration with the keyword @Transient

Code


    private Boolean bResetDependantEntityCountryIDOnDelete = true;

    public Boolean getResetDependantEntityCountryIDOnDeleteFlag()
    {
        return bResetDependantEntityCountryIDOnDelete;
    }

    public void setResetDependantEntityCountryIDOnDeleteFlag(Boolean value)
    {
    	bResetDependantEntityCountryIDOnDelete = value;
    }

Revision


    //object.org.hibernate.tool.schema.spi.SchemaManagementException:
    //Schema-validation: missing column
    @Transient
    private Boolean bResetDependantEntityCountryIDOnDelete = true;

    public Boolean getResetDependantEntityCountryIDOnDeleteFlag()
    {
        return bResetDependantEntityCountryIDOnDelete;
    }

    public void setResetDependantEntityCountryIDOnDeleteFlag(Boolean value)
    {
    	bResetDependantEntityCountryIDOnDelete = value;
    }

Hibernate – Error Message – “Unknown entity”

Background

Learning new tooling is not easy for me.

Error

Image

unknownEntity.20190509.0942AM

Textual

Unknown entity: sakila.Country

Trouble Shooting

Metadata

Entities Registered

Outline

  1. Start a Session or use an existing one
  2. Get Session’s Transaction handle
  3. Start a new transaction
  4. Get Session’s metamodel
  5. Get Metamodel entity list
  6. Iterate Metamodel entity list
  7. Commit Transaction

Code

/*
     * This function lists the entities declared in our session
     * It principally relies on session.getMetaModel.getEntities
     */
    private void entityList
    (
		  Session session
	)
    { 

    	String strEntity;
    	int i;
    	int iNumberofElements;
    	Transaction transaction;
    	Metamodel objEntityManager;
        Set objEntityTypeList;

    	try
        {

        	System.out.println("Retrieve entities ...."); 

        	//Get Session's Transaction
        	transaction
        		= session.getTransaction();

        	//Start Transaction
            transaction.begin();

            //Get MetaModel
            objEntityManager
            	= session.getMetamodel();

            //Get Entities
            objEntityTypeList
            	= objEntityManager.getEntities();

            //get Number of Entities
            iNumberofElements
            	= objEntityTypeList.size();

        	System.out.println
        		(
					CHAR_TAB
					+ "Number of elements is "
					+ iNumberofElements
				); 

        	//reset i
            i = 0;

            //Iterate through collection
            for (javax.persistence.metamodel.EntityType objEntityType:  objEntityTypeList)
            {

            	//increment
            	i = i +1;

            	//Get Entity
            	strEntity = objEntityType.toString();

            	//Display Entity
            	System.out.println
            	(
    				CHAR_TAB + CHAR_TAB
    				+ i
    				+ ") "
            	    + strEntity
        	    ); 

            }

        	System.out.println("Entities Retrieved"); 

        	//Close Transaction
            transaction.commit();

        }

    	catch (Exception ex)
    	{

    		System.out.println(ex.getMessage()); 

            if (session.getTransaction().isActive())
            {
                session.getTransaction().rollback();
            }

        } //Iterate through collection catch

	} //entityList<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Similar Errors

@OneToOne or @ManyToOne on [entity] references an unknown entity

Image

@OneToOne or @ManyToOne on sakila.City.country references an unknown entity.

Text


Failed to create sessionFactory object.org.hibernate.AnnotationException:
@OneToOne or @ManyToOne on sakila.City.country references an unknown entity: sakila.Country

@OneToOne or @ManyToOne on sakila.City.country references an unknown entity: sakila.Country

Summary

Was able to confirm that the entities were not registering correctly.

And, moved on from there and sought help from a real Java developer.

Hibernate – SQL Server – Exercise – 01

Background

Wanted to share source code for a sample helloDB Script that I am trying to use as I self teach Hibernate.

Outline

  1. Database File
    • DDL
      • Table
        • dbo.Country
  2. Java Source File
    • helloDB.java
  3. Hibernate Configuration File
    • SQLServerSavila.cfg.xml
  4. Entity
    • Country
      • Country.java ( POJO File )
      • Country.hbm.xml ( XML )

Source Code

Database

DDL

Table

Country
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [sakila]
GO

/*
    drop table [dbo].[country];
*/

/*
    truncate table [dbo].[country];
*/

/*
    select *
    from [dbo].[country];
*/
IF NOT EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[country]')
    AND type in (N'U')
)
BEGIN

    CREATE TABLE [dbo].[country]
    (
        [country_id] [smallint] NOT NULL,
        [country] [varchar](50) NOT NULL,
        [last_update] [datetime] NULL

        , constraint [dbo.PK_Country]
          primary key
        (
            [country_id]
        )
    )
    ON [PRIMARY]

END
GO

Source Code

helloDB.java

package sakila;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.Transaction;

import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;
import java.util.Set;

public class helloDB
{

	Configuration objConfiguration = null;

	static String CONFIGURATION_FILE_SQLSERVER = "SQLServerSakila.cfg.xml";

	SessionFactory factoryMSSQL = null;

	static String CHAR_TAB = "\t";

	public static void main(String[] args)
	{

		// TODO Auto-generated method stub
		run();

	}

	public static void run()
	{

			helloDB objHelloDB = null;

			objHelloDB = new helloDB();

			try
			{
				objHelloDB.dbWork();
			}
			catch (Exception ex)
			{

				System.out.println(ex.getMessage());

			}

			objHelloDB =null;

	}

    private Session getDBSession()
    {
    	Session session = null;

    	Configuration cfg= new Configuration();

    	cfg.configure(CONFIGURATION_FILE_SQLSERVER);

    	factoryMSSQL
			= cfg.buildSessionFactory();

    	session = factoryMSSQL.openSession(); 

    	//session = factoryMSSQL.getCurrentSession(); 

    	return session;

    }

    void dbWork()
	//throws Exception
    {

    	try
    	{

    		Session session = null;

    		session = getDBSession();

	    	entityList(session);

	    	countryAddList(session);

	    	countryList(session);

	    	session.close();

	    	factoryMSSQL.close();

    	}
		catch (Exception ex)
		{

			System.out.println(ex.getMessage());

			System.err.println("Failed to create sessionFactory object." + ex);

			throw new ExceptionInInitializerError(ex);
		}

    }

    // list all objects
    private void countryList(Session session)
    {

    	List lists;
    	Country       objCountry;

    	String strLog;

		System.out.println("List Countries");

		System.out.println("==============");

    	lists = session.createQuery("from Country").list(); 

    	System.out.println(lists.size()); 

    	for (int i = 0; i < lists.size(); i++)
    	{ 

			objCountry = lists.get(i);

			strLog = CHAR_TAB
						+ objCountry.getCountry()
						+ " "
						+ "("
						+ objCountry.getCountryID()
						+ ")";

			System.out.println(strLog);

    	}	

    }

    private void countryAddList
    (
		Session session
	)
	throws Exception
    {

    	countryAdd(session, 1, "United States of America");
    	countryAdd(session, 33, "France");
    	countryAdd(session, 44, "United Kingdom");
    	countryAdd(session, 55, "Brazil");
    	countryAdd(session, 61, "Australia");

    }

    private void countryAdd
    (
		  Session session
		, int countryID
		, String country
	)
	throws Exception
    {
        try
        {

        	Transaction transaction = session.getTransaction();

            transaction.begin();

            Country objCountry = new Country();

            objCountry.setCountryID(countryID);
            objCountry.setCountry(country);
            objCountry.settsUpdate(new Timestamp(Calendar.getInstance().getTime().getTime()));

            session.persist(objCountry);

            transaction.commit();

        }

    	catch (Exception ex)
    	{

			System.err.println("Failed to Add country " + country);

			System.out.println(ex.getMessage());

            if (session.getTransaction().isActive())
            {
                session.getTransaction().rollback();
            }

        }

	}

    private void entityList
    (
		  Session session
	)
	//throws Exception
    { 

    	String strEntity;
    	int iNumberofElements;
    	Set objEntityTypeList;

    	try
        {

            objEntityTypeList = session.getMetamodel().getEntities();

            iNumberofElements = objEntityTypeList.size();

        	System.out.println("Retrieve entities ...."); 

        	System.out.println(CHAR_TAB + "Number of elements is " + iNumberofElements); 

        	Transaction transaction = session.getTransaction();

            transaction.begin();

            for (javax.persistence.metamodel.EntityType objEntityType:  session.getMetamodel().getEntities())
            {

            	strEntity = objEntityType.toString();

            	System.out.println(CHAR_TAB + CHAR_TAB + "entity name "+strEntity); 

            }

        	System.out.println("Entities Retrieved"); 

            transaction.commit();

        }

    	catch (Exception ex)
    	{

    		System.out.println(ex.getMessage()); 

            if (session.getTransaction().isActive()) {
                session.getTransaction().rollback();
            }

            //throw ex;
        }

	}

}

Hibernate Configuration File

SQLServerSavila.cfg.xml





 

  org.hibernate.dialect.SQLServerDialect

  com.microsoft.sqlserver.jdbc.SQLServerDriver

  jdbc:sqlserver://localhost:1433;databaseName=sakila;

  sakila

  VecRadrI3h

  <!-- JDBC connection pool (use the built-in) -->
  
  	1
  

  thread

  <!-- Disable the second-level cache  -->
  
  	false
  

  <!-- Echo all executed SQL to stdout -->
  true

  <!-- Drop and re-create the database schema on startup -->
  <!-- validate schema on startup -->

  validate

  dbo

  

  &lt;!--
  	 
	--&gt;

 


Entity

Country

Country.java ( POJO File )

package sakila;

/*
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
*/

import java.sql.Timestamp;

public class Country
{

	private int CountryID;
	private String Country;
	private Timestamp tsUpdate;

	public Country()
	{

	}

	public Timestamp gettsUpdate()
	{
		return tsUpdate;
	}

	public void settsUpdate(Timestamp tsUpdate)
	{
		this.tsUpdate = tsUpdate;
	}

	public int getCountryID()
	{
		return CountryID;
	}

	public void setCountryID(int countryID)
	{
		CountryID = countryID;
	}

	public String getCountry()
	{
		return Country;
	}

	public void setCountry(String country)
	{
		Country = country;
	}

}

Country.hbm.xml ( HBM.XML File )





 

  

  

  

 


Source Code Control

GitHub

DanielAdeniji/javaHibernateSQLServerSakila
Link

Eclipse/Hibernate – Installation – Day/01

 

Outline

  1. Download & Install Eclipse
  2. JDBC Driver
    • Microsoft JDBC Driver
      • Download Microsoft JDBC Driver
  3. Configure Database
    • Microsoft SQL Server
      • Provision Database
        • WideWorldImporters ( Sample Database )
          • Restore Database
      • Provision Principal
        • Create SQL Server Principal
        • Grant Principal access to database
        • Grant principal access to database objects
  4. Configure Eclipse
    • Install Components
      • JBoss Tools
        • Hibernate

Tasks

Download & Install Eclipse

Please peruse this post per installing Eclipse :-

Eclipse IDE – Installation On MS Windows
Link

JDBC Drivers

Our database back-end is Microsoft SQL Server and so we will reach for Microsoft SQL Server JDBC Drivers.

Microsoft

SQL Server

JDBC Driver
Links
  1. Microsoft JDBC Driver for SQL Server
    Link
  2. Download Microsoft JDBC Driver for SQL Server
    Link
Available

Here are all the drivers available as of 2019-May-6th :-drivers.microsoft.jdbc.20190506.1231PM

We are on 7.0.

And, we will cover that in this post!

Database

Again, our database back-end is Microsoft SQL Server.  There are a few sample databases on that platform; the latest official is WorldWideImporters.

Microsoft

SQL Server

Worldwide Importers
Links
  1. SQL Samples
    Link
  2. Wide World Importers
    • Wide World Importers sample database v1.0
      Link
Brochure

Brochure

microsoft.sample.20190506.1246PM

Choice

We can go with traditional database backup files ( bak ).

Or with bacpac.

We chose :-

SQL Server 2016 SP1 (or later) Any Edition aside from LocalDB; SQL Server 2016 RTM (or later) Evaluation/Developer/Enterprise Edition
WideWorldImporters-Full.bak

Restore database

Launched SQL Server Management Studio and restored the backup file


USE [master]
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\'
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\'
go

RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\downloads\Microsoft\SQLServer\Sample\WideWorldImporters\WideWorldImporters-Full.bak'
WITH
       FILE = 1
    ,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters.mdf'
    ,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_UserData.ndf'
    ,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\WideWorldImporters.ldf'
    ,  MOVE N'WWI_InMemory_Data_1' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_InMemory_Data_1'
    ,  NOUNLOAD
    ,  STATS = 5
    ,  REPLACE

GO
Create Database Account

Let us create SQL Server Account


use [WideWorldImporters]
go

/*
    SQL Server Principal - helloDB
*/
if suser_id('helloDB') is null
begin

    create login [helloDB]
    with password = 'Nu3Hu5RuSt'
       , DEFAULT_DATABASE = [WideWorldImporters]
       , CHECK_EXPIRATION = OFF
       , CHECK_POLICY = OFF

end
go

/*
    SQL Server Principal - helloDB
*/
if user_id('helloDB') is null
begin

    create user [helloDB]
    from login [helloDB];

end
go

grant select, insert, update, delete on schema::[Application] to [helloDB]
go

grant select, insert, update, delete on schema::[Purchasing] to [helloDB]
go

grant select, insert, update, delete on schema::[Sales] to [helloDB]
go

grant select, insert, update, delete on schema::[Warehouse] to [helloDB]
go

jboss.org

Available Software

In the Work With entry box, please enter :-

http://download.jboss.org/jbosstools/photon/development/updates

Once entered please click the “Add…” button

install.AvailableSoftware.02.WorkWith.20190505.0225PM.PNG

Add Repository

Please give a Name for the Repository we are adding.

In our case we chose jboss.

install.AvailableSoftware.03.WorkWith.20190505.0225PM.PNG

 

Check the items that you wish to install

Using Web Services, jboss.org is reached.

Available Items are returned and displayed.

install.AvailableSoftware.05.AddRepository.20190505.0228PM.PNG

Item – jBoss Data Services Development

We selected the following items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.06.AddRepository.20190505.0229PM.PNG

Item – jBoss Data Services Development – Install Details

Please review the selected items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.07.AddRepository.20190505.0230PM.PNG

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

Item – Installing Software

 

Item – Security Warning

Security Warning – The authenticity or validity of the software cannot be established

Even Hibernate.org is shipping software without SSL Certificates?

 

It is good, please click “Install anyway”.

Restart

Please restart Eclipse ID to effect the changes.

Restarted

Upon restart, we are asked to opt in for anonymous usage statistics.

Summary

Enough shenanigans for Day 01.

 

 

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