JPA 实现SQL和HQL语句查询（createQuery()、createNativeQuery()）

我会带着你远行 2023-01-11 03:48 265阅读 0赞

### 1 共通代码 ###

import javax.persistence.EntityManager;
    import javax.persistence.PersistenceContext;
    import javax.persistence.Query;
    import org.hibernate.query.internal.NativeQueryImpl;
    import org.hibernate.transform.Transformers;
    public class UserDao{
        @PersistenceContext
        private EntityManager entityManager;
    }

## 2 createQuery() ##

使用HQL语句实现查询，以hibernate生成的Bean为对象装入list返回 。

EntityManager entityManager;
    String hql="Select * From User";
    List<User> userList=entityManager.createQuery(hql);

## 3 createNativeQuery() ##

使用SQL语句实现查询。

### 3.1 查询List ###

/**
         * 得到用户信息
         */
        public List<SysUserDto> getUserList(String username, String jobNumer) {
            String sql="select t1.user_id userId,t1.user_name userName,t1.postid postId,t1.jobnumer jobNumer,t1.dept_id deptId,t4.dept_name deptName " +
                    " from sys_user t1" ;
            List<Object> params = new ArrayList<>();
            if(com.pit.common.utils.StringUtils.isNotBlank(username)){
                sql+=" and t1.user_name like ?";
                params.add("%"+username+"%");
            }
            if(com.pit.common.utils.StringUtils.isNotBlank(jobNumer)){
                sql+=" and t1.jonumer like ?";
                params.add("%"+jobNumer+"%");
            }
            Query contentQuery = entityManager.createNativeQuery(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                contentQuery.setParameter(i + 1, params.get(i));
            }
            contentQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(SysUserDto.class));
            List<SysUserDto> results = contentQuery.getResultList();
            return results;
        }

### 3.2 查询数量 ###

/**
         * 得到用户信息数量
         */
        public int getUserListCount( String username, String jobNumer) {
            String sql="select count(*) " +
                    " from sys_user t1" ;
            List<Object> params = new ArrayList<>();
            if(com.pit.common.utils.StringUtils.isNotBlank(username)){
                sql+=" and t1.user_name like ?";
                params.add("%"+username+"%");
            }
            if(com.pit.common.utils.StringUtils.isNotBlank(jobNumer)){
                sql+=" and t1.jobnumer like ?";
                params.add("%"+jobNumer+"%");
            }
            Query contentQuery = entityManager.createNativeQuery(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                contentQuery.setParameter(i + 1, params.get(i));
            }
            Object result = contentQuery.getSingleResult();
            int count = 0;
            if (null != result  && !"".equals(result.toString())) {
                count = Integer.parseInt(result.toString());
            }
            return count;
        }