JPA 使用SQL语句查询数据的两种方式（@Query注解、EntityManager类）

旧城等待， 2022-09-11 03:11 419阅读 0赞

## 1 Maven配置 ##

<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-data-jpa</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<optional>true</optional>
    		</dependency>
    		
    		<dependency>
    			<groupId>cn.hutool</groupId>
    			<artifactId>hutool-all</artifactId>
    			<version>5.5.1</version>
    		</dependency>

## 2 使用@Query注解查询数据 ##

### 2.1 UserRepository ###

package com.dao;
    
    import com.entity.UserEntity;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /**
     * 用户Dao层
     */
    @Repository
    public interface UserRepository extends JpaRepository<UserEntity, String>, JpaSpecificationExecutor<UserEntity>, UserCustomRepository {
        /**
         * 获取用户信息
         * nativeQuery = true:使用sql查询
         * nativeQuery = false:使用jpql查询，默认就是false
         */
        @Query(value = "select * from users where username=? ", nativeQuery = true)
        List<UserEntity> getUserListByQuery(String username);
    }

## 3 使用EntityManager类查询数据 ##

### 3.1 UserCustomRepository ###

package com.dao;
    
    import com.entity.UserEntity;
    
    import java.util.List;
    
    public interface UserCustomRepository {
        /**
         * 获取用户信息
         *
         * @param username 用户名（非必填，默认全部）
         * @return
         */
        List<UserEntity> getUserListByEntityManager(String username);
    }

### 3.2 UserCustomRepositoryImpl ###

package com.dao.impl;
    
    import cn.hutool.core.util.StrUtil;
    import com.dao.UserCustomRepository;
    import com.entity.UserEntity;
    
    import javax.persistence.EntityManager;
    import javax.persistence.PersistenceContext;
    import javax.persistence.Query;
    import java.util.List;
    
    public class UserCustomRepositoryImpl implements UserCustomRepository {
        @PersistenceContext
        private EntityManager entityManager;
    
        /**
         * 获取用户信息
         *
         * @param username 用户名（非必填，默认全部）
         * @return
         */
        @Override
        public List<UserEntity> getUserListByEntityManager(String username) {
            String sql = "select * from users where 1=1 ";
            if (StrUtil.isNotBlank(username)) {
                sql += " and username like '%" + username + "%'";
            }
            Query query = entityManager.createNativeQuery(sql, UserEntity.class);
            List<UserEntity> list = query.getResultList();
            return list;
        }
    }