java - Tomcat 8、Hibernate 5.2、MSSQL、HikariCP 标准搜索返回 Null

Question

目前我们正在对应用程序的 DB CRUD 功能进行性能审查和改进，其中一项审查是使用 Hikari 连接池和更高版本的 Hibernate 和 Hibernate-HikariCP 进行测试。

我们通过将 Hibernate 版本从 5.2.10 升级到 5.2.16 来运行这个，Hikari 版本是 3.1.0。整个练习使用 Tomcat 8 和 MSSQL 2016 进行了测试。

为简单起见，我们使用实体 User 对具有一种搜索条件的简单搜索进行了测试。

休眠 XML 配置：

<hibernate-configuration>
  <session-factory>
    <property name = "hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
    <property name="show_sql">true</property>  

    <!-- Hikari specific properties -->
    <property name="hibernate.connection.provider_class">com.zaxxer.hikari.hibernate.HikariConnectionProvider</property>
    <property name="hikari.maximumPoolSize">30</property>
    <property name="hikari.idleTimeout">300000</property>
    <property name="hikari.maxLifetime">1800000</property>

    <!-- Database connection properties -->
    <property name="hibernate.hikari.dataSourceClassName">com.microsoft.sqlserver.jdbc.SQLServerDataSource</property>

<property name="hibernate.connection.url">jdbc:sqlserver://192.168.0.102:1433;databaseName=migration1;user=sun;password=p@ssw0rd;</property>
        <property name = "hibernate.connection.username">sun</property>
        <property name = "hibernate.connection.password">p@ssw0rd</property>

    <mapping class="com.hib.model.Outlet"/>
    <mapping class="com.hib.model.Receipt"/>
    <mapping class="com.hib.model.Cashier"/>
    <mapping class="com.hib.model.Category"/>
    <mapping class="com.hib.model.Item"/>
    <mapping class="com.hib.model.PayMethodName"/>
    <mapping class="com.hib.model.Pos"/>
    <mapping class="com.hib.model.Purchase"/>
    <mapping class="com.hib.model.User"/>
  </session-factory>
</hibernate-configuration>

HibernateUtil 类非常简单，目前为：

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();
    }
}

用户实体，带注释，不包括 getter / setter：

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 && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }

        return true;
    }

    @Override
    public String toString() {
        return "com.example.dao.User[ id=" + id + " ]";
    }

搜索特定用户的功能在用户服务类的这个方法中：

@Override
    public List<User> getUserById(Integer id) {
        List<User> 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;
    }

测试的时候，如果使用原来的C3P0连接池，上面的搜索功能会返回传入的Id号（数值）对应的匹配记录的预期结果。

但是，当切换到 HikariCP 时，结果返回 null。但是，如果我们编写 NativeQuery 而不是 Criteria，它不会受到影响。

随后，我们尝试了三种替代方案，看看它是否有效，但有不同的错误：

选项 1： - 返回 IllegalArgumentException，实体用户未映射

TypedQuery<User> query = session.createQuery("select u from "+User.class.getSimpleName()+" u",User.class) ;
result = query.getResultList();

选项 2： - 返回 IllegalArgumentException，而不是实体用户。

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);
cq.select(root);
Query<User> q = session.createQuery(cq);
result = q.getResultList();

选项 3 - 返回映射异常，未知实体用户：

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(); 

由于 HikariCP 对其他 Native Queries 做了很多改进，我们认为如果我们仍然可以继续使用该 CP 来支持 C3P0 池会更好。但是，我们仍然无法确定最好使用 Service 类方法中的哪种方法。

我们感谢任何有关这方面的帮助的建议。谢谢。

标签： javasql-serverhibernatehikaricp