title: Tomcat 8, Hibernate 5.2, MSSQL, HikariCP Criteria Search Returns Null
body: <p>Currently we are doing a performance review and improvement of the DB CRUD function of the application and one of the reviews was using Hikari connection pool and a later version of Hibernate and Hibernate-HikariCP for our tests. </p>
  
  <p>We are running this by upgrading the Hibernate version from 5.2.10 to 5.2.16, with Hikari at version 3.1.0. This entire exercise was tested with Tomcat 8 and MSSQL 2016.</p>
  
  <p>For simplicity purposes, we tested on one simple search with one search condition using the entity User. </p>
  
  <p><strong>Hibernate XML Configuration:</strong></p>
  
  <pre><code>&lt;hibernate-configuration&gt;
    &lt;session-factory&gt;
      &lt;property name = "hibernate.dialect"&gt;org.hibernate.dialect.SQLServerDialect&lt;/property&gt;
      &lt;property name="show_sql"&gt;true&lt;/property&gt;  
  
      &lt;!-- Hikari specific properties --&gt;
      &lt;property name="hibernate.connection.provider_class"&gt;com.zaxxer.hikari.hibernate.HikariConnectionProvider&lt;/property&gt;
      &lt;property name="hikari.maximumPoolSize"&gt;30&lt;/property&gt;
      &lt;property name="hikari.idleTimeout"&gt;300000&lt;/property&gt;
      &lt;property name="hikari.maxLifetime"&gt;1800000&lt;/property&gt;
  
      &lt;!-- Database connection properties --&gt;
      &lt;property name="hibernate.hikari.dataSourceClassName"&gt;com.microsoft.sqlserver.jdbc.SQLServerDataSource&lt;/property&gt;
  
  &lt;property name="hibernate.connection.url"&gt;jdbc:sqlserver://192.168.0.102:1433;databaseName=migration1;user=sun;password=p@ssw0rd;&lt;/property&gt;
          &lt;property name = "hibernate.connection.username"&gt;sun&lt;/property&gt;
          &lt;property name = "hibernate.connection.password"&gt;p@ssw0rd&lt;/property&gt;
  
      &lt;mapping class="com.hib.model.Outlet"/&gt;
      &lt;mapping class="com.hib.model.Receipt"/&gt;
      &lt;mapping class="com.hib.model.Cashier"/&gt;
      &lt;mapping class="com.hib.model.Category"/&gt;
      &lt;mapping class="com.hib.model.Item"/&gt;
      &lt;mapping class="com.hib.model.PayMethodName"/&gt;
      &lt;mapping class="com.hib.model.Pos"/&gt;
      &lt;mapping class="com.hib.model.Purchase"/&gt;
      &lt;mapping class="com.hib.model.User"/&gt;
    &lt;/session-factory&gt;
  &lt;/hibernate-configuration&gt;
  </code></pre>
  
  <p>The HibernateUtil class is quite simple, currently as:</p>
  
  <pre><code>public class HibernateUtil {
  
      private static final SessionFactory sessionFactory;
  
      static {
  
          try {
              // Create the SessionFactory from standard (hibernate.cfg.xml) 
              // config file.
              Configuration configuration = new Configuration().configure();
              StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                      .applySettings(configuration.getProperties());
              sessionFactory = new Configuration().configure().buildSessionFactory(builder.build());
  
  
          } catch (Throwable th) {
              System.err.println("Initial SessionFactory creation failed " + th);
              throw new ExceptionInInitializerError(th);
          }
  }
  
      public static SessionFactory getSessionFactory() {
          return sessionFactory;
      }
  
      public static void shutdown() {
          // Close caches and connection pools
          getSessionFactory().close();
      }
  }
  </code></pre>
  
  <p>The User Entity, annotated, excluding the getter / setters:</p>
  
  <pre><code>import java.io.Serializable;
  import java.util.Date;
  import javax.persistence.Basic;
  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.GeneratedValue;
  import javax.persistence.Id;
  import javax.persistence.Lob;
  import javax.persistence.Table;
  import javax.persistence.Temporal;
  import javax.persistence.TemporalType;
  import javax.persistence.Version;
  import javax.validation.constraints.NotNull;
  import javax.xml.bind.annotation.XmlRootElement;
  import org.hibernate.annotations.CreationTimestamp;
  import org.hibernate.annotations.GenericGenerator;
  import org.hibernate.annotations.UpdateTimestamp;
  @Entity
  @Table(name = "User")
  @XmlRootElement
  public class User implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      @Id
      @Basic(optional = false)
      @Column(name = "id")
      @NotNull
      @GeneratedValue(generator="`id`")
      @GenericGenerator(name="`id`", strategy = "increment")
      private Integer id;
  
      @NotNull
      @Column(name = "FullName")
      private String fullName;
  
      @Column(name = "NickName")
      private String nickName;
  
      @Column(name = "Tel")
      private String tel;
  
      @Column(name = "Rank")
      private String rank;
  
      @NotNull
      @Column(name = "Email")
      private String email;
  
      @NotNull
      @Column(name = "Password")
      private String password;
  
      @Lob
      @Column(name = "PhotoUpload")
      private String photoUpload;
  
      @Version
      @Column(name = "CreateDate")
      @CreationTimestamp
      @Temporal(TemporalType.TIMESTAMP)
      private Date createDate;
  
      @Version
      @Column(name = "EditDate")
      @UpdateTimestamp
      @Temporal(TemporalType.TIMESTAMP)
      private Date editDate;
  
      @Column(name = "CreateBy")
      private String createBy;
  
      @Column(name = "ActiveInd")
      private String activeInd;
  
      @Column(name = "LastLogin")
      @Temporal(TemporalType.TIMESTAMP)
      private Date lastLogin;
  
      @Column(name = "tenant_id")
      private String tenantId;
  
      @NotNull
      @Column(name = "group_id")
      private String groupId;
  
      public User() {
      }
  
      public User(Integer id, String fullName,String nickName,String tel,String rank,String email,String password,String photoUpload,Date createDate,Date editDate,String createBy,String activeInd,Date lastLogin, String tenantId, String groupId) {
          this.id = id;
          this.fullName = fullName;
          this.nickName = nickName;
          this.tel =tel;
          this.rank=rank;
          this.email =email;
          this.password = password;
          this.photoUpload = photoUpload;
          this.createDate =createDate;
          this.editDate =editDate;
          this.createBy =createBy;
          this.activeInd = activeInd;
          this.lastLogin=lastLogin;
          this.tenantId= tenantId;
          this.groupId =groupId;
      }
  
  @Override
      public boolean equals(Object object) {
          // TODO: Warning - this method won't work in the case the id fields are not set
          if (!(object instanceof User)) {
              return false;
          }
          User other = (User) object;
          if ((this.id == null &amp;&amp; other.id != null) || (this.id != null &amp;&amp; !this.id.equals(other.id))) {
              return false;
          }
  
          return true;
      }
  
      @Override
      public String toString() {
          return "com.example.dao.User[ id=" + id + " ]";
      }
  </code></pre>
  
  <p>The function of searching the particular Users is in this method of the User Service class:</p>
  
  <pre><code>@Override
      public List&lt;User&gt; getUserById(Integer id) {
          List&lt;User&gt; result = null;
          Session session = getSession();
  
          try {
  
          Criteria criteria = getSession().createCriteria(User.class);
          Criteria criteria = getSession().(User.class);
          criteria.add(Restrictions.eq("id", id));
          criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
          result = query.getResultList();
  
          }catch (HibernateException e){
              userLogger.error("Error found: " + e);
          }finally {
              if (session != null)
                  session.close(); 
          }
          return result;
      }
  </code></pre>
  
  <p>When tested, if the original C3P0 connection pool is used, the search function above will return the expected result of the matching record corresponding to the Id number passed in (numeric value).</p>
  
  <p>However, when switched to HikariCP, the result returns null. However, if we write a NativeQuery instead of Criteria, it does not get affected.</p>
  
  <p>Subsequently, we tried three alternatives to see if it works but with different errors:</p>
  
  <p><strong>Option 1: - returns an IllegalArgumentException, entity User not mapped</strong></p>
  
  <pre><code>TypedQuery&lt;User&gt; query = session.createQuery("select u from "+User.class.getSimpleName()+" u",User.class) ;
  result = query.getResultList();
  </code></pre>
  
  <p><strong>Option 2: - returns an IllegalArgumentException, not an entity User.</strong></p>
  
  <pre><code>CriteriaBuilder cb = session.getCriteriaBuilder();
  CriteriaQuery&lt;User&gt; cq = cb.createQuery(User.class);
  Root&lt;User&gt; root = cq.from(User.class);
  cq.select(root);
  Query&lt;User&gt; q = session.createQuery(cq);
  result = q.getResultList();
  </code></pre>
  
  <p><strong>Option 3 - returns a mapping exception, unknown entity User:</strong></p>
  
  <pre><code>String sql = "SELECT * FROM USER_LOG WHERE id = :number_id";
              SQLQuery query = session.createSQLQuery(sql);
              query.addEntity(User.class);
              query.setParameter("number_id", id);
              result = query.list(); 
  </code></pre>
  
  <p>As HikariCP has done a lot of improvements for the other Native Queries, we felt it is better if we can still keep on using that CP in favor of the C3P0 pool. However, we are still stuck as in unsure to which approach in the Service class method is best used.</p>
  
  <p>We appreciate any advise on help on this. Thanks.</p>