Recently I had this error in my Spring Boot application,  during the connection to MSSQL Server:

ERROR org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl - Could not fetch the SequenceInformation from the database
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'INFORMATION_SCHEMA.SEQUENCES'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc4-4.0.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) ~[sqljdbc4-4.0.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792) ~[sqljdbc4-4.0.jar:?]
.....

After the investigation, I discovered that I had two issues in my configuration that caused this behavior.

My previous configuration in the application.properties was:

spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

What I found out is that I'm setting the wrong property, the right one should be:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

I also used this query to check the version of my SQL Server, and it turned out that I'm connecting to version 2008 instead of 2012. So, in the end, my connection dialect property was:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect

After I changed this, everything worked like a charm.

If you asked yourself how to persist list of String elements in Hibernate, there are few ways to do so, but the most common and easiest one is by using @ElementCollection annotation.

What you need to do is to annotate the list element with @ElementCollection and you'll notice that persistence provider will create a new table in a database where it will store your list. In this tutorial, we'll show how Hibernate behaves and what is created in the database when using @ElementCollection annotation.

Implementation

import java.util.ArrayList;
import java.util.List;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Shirt {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	Long id;

	@ElementCollection
	List<String> availableColors = new ArrayList<String>();

	public Long getId() {
		return id;
	}

	public List<String> getAvailableColors() {
		return availableColors;
	}

	public void setAvailableColors(List<String> availableColors) {
		this.availableColors = availableColors;
	}

}

Result in database

I'll create one object of Shirt type with available colors {Red, Blue} and persist it. The situation in the database would be as follows:

How to persist list of String elements in Hibernate

You'll notice that there's the separate table for field availableColors, and that Hibernate added shirt_ prefix to availableColors table. If you do select on table Shirt, you'll notice that there's nothing related to availableColors table there, just the Id field. 

How to persist list of String elements in Hibernate

But if you select from shirt_availablecolors, you'll notice that every String object in List is now separate row with reference to parent Shirt object by shirt_id. The bad side of this approach is that availableColors element has no id so it couldn't be addressed separately from other elements.

How to persist list of String elements in Hibernate

Introduction

If you ever wanted a simple way to query your database without writing complicated Hibernate queries - Criteria API is the right thing for you. In this tutorial, you'll learn how to use JPA Criteria API  and how to build CriteriaQuery on a simple example.

Implementation

First, we'll define entity class Item with fields id, name, code. This class represents our data table. We will create one more class called ItemQueryCriteria that will contain only query parameters we would like to use to query out data with JPA Criteria API.

@Entity
public class Item {

  private Long id;
  private String code;
  private String name;
  
  public Long getId() {
    return id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }
 
  public String getCode() {
    return code;
  }

  public void setCode(String code) {
    this.code = code;
  }
}

 Class ItemQueryCriteria will contain variables representing query parameters we would like to use to query the database. So, in this scenario, we'll use code and name as query parameters.

public class ItemQueryCriteria {

  private String code;
  private String name;

  public void setName(String name) {
    this.name = name;
  }
 
  public String getCode() {
    return code;
  }

  public void setCode(String code) {
    this.code = code;
  }
}

And finally, this is our repository method. In the following text, we'll go line by line.

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

@Repository
public class ItemsRepositoryCustomImpl {

 @PersistenceContext
 private EntityManager em;

 public List<Item> findByCriteria(ItemCriteria itemCriteria) {
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Item> cq = cb.createQuery(Item.class);
  Root<Item> item = cq.from(Item.class);
  List<Predicate> predicates = new ArrayList<Predicate>();
  // checking if parameter name is provided, if yes, adding new predicate
  if (itemCriteria.getName() != null) {
	predicates.add(cb.like(item.get("name"), "%" + itemCriteria.getName() + "%"));
  }
  // checking if parameter code is provided, if yes, adding new predicate
  if (itemCriteria.getCode() != null) {
	predicates.add(cb.equal(item.get("code"), itemCriteria.getCode()));
     }
  cq.where(predicates.toArray(new Predicate[] {}));
  TypedQuery<Item> query = em.createQuery(cq);
  List<Item> items = query.getResultList();
  return items;
 }
}

Here we initialize CriteriaBuilder and CriteriaQuery, binding it to the type we would like to query. Here it's Item, so we'll pass it in constructor od CriteriaQuery and to Root. 

  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Item> cq = cb.createQuery(Item.class);
  Root<Item> item = cq.from(Item.class);

The most important part is creating a list of Predicates. CriteriaQuery accepts array for configuring where clause, so it's important to build an array of predicates. Predicate represents one single where clause. In this example, we used LIKE - for name and EQUAL for code. Sometimes it could happen that ItemQueryCriteria does not contain all params, so we should assure that we didn't provide nulls to the predicate.

After that, we pass our predicate list to CriteriaQuery, converting it to an array with predicates.toArray(new Predicate[]{}).

  List<Predicate> predicates = new ArrayList<Predicate>();
  // checking if parameter name is provided, if yes, adding new predicate
  if (itemCriteria.getName() != null) {
	predicates.add(cb.like(item.get("name"), "%" + itemCriteria.getName() + "%"));
  }
  // checking if parameter code is provided, if yes, adding new predicate
  if (itemCriteria.getCode() != null) {
	predicates.add(cb.equal(item.get("code"), itemCriteria.getCode()));
     }
  cq.where(predicates.toArray(new Predicate[] {}));

In the end, we call the EntityManager to create typed query based on our CriteriaQuery.

  TypedQuery<Item> query = em.createQuery(cq);
  List<Item> items = query.getResultList();