使用Spring Data JPA 获取数据库数据

使用Spring Data JPA 获取数据库数据

假若我们需要获取某一用户数据，则一般可以使用sql如下：

select uid, uname, sex from user where sex = '男';

但是如果现在使用 Spring Data JPA ，那么该sql又该如何实现？

1. 构建bean继承org.springframework.data.repository.repository的子接口

1. 创建 User表的实体类

   package com.demo.bean;
   
   import lombok.AllArgsConstructor;
   import lombok.Getter;
   import lombok.Setter;
   import lombok.ToString;
   
   import javax.persistence.*;
   import java.sql.Date;
   
   @Getter
   @Setter
   @AllArgsConstructor
   @Entity
   @ToString
   public class User{
      	@Id
       @GeneratedValue(strategy = GenerationType.AUTO)
       private long uid;
   	private String uname;
   	private String sex;
   	
   	public User(){};
   }
   

2. 创建接口并实现JpaRepository(该接口为repository的子接口)

   package com.demo.repostory;
   
   import org.springframework.data.jpa.repository.JpaRepository;
   import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
   import com.demo.bean.User;
   import java.sql.Date;
   import java.util.List;
   
   public interface UserRepository extends JpaRepository<User, Long> {
   	List<User> findAllBySex(String Sex);
   }
   

3. 创建service类

   package com.demo.service;
   
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.stereotype.Service;
   import com.demo.bean.User;
   import com.demo.repostory.UserRepository;
   
   @Service
   public class UserService {
   
   	private UserRepository userRepository;
   	
   	/**
   	* 建议使用构造器或setter进行依赖注入
   	*/
   	public UserService(UserRepository userRepository ) {
   		this.userRepository = userRepository;
   	}
   	
   	public List<User> findAllBySex(String sex) {
   		return userRepository.findAllBySex(sex);
   	}
   }
   

4. 使用测试类测试

   package com.demo.jpademo;
   
   import org.junit.jupiter.api.Test;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.test.context.SpringBootTest;
   import com.demo.bean.User;
   import com.demo.service.UserService ;
   
   import java.util.List;
   
   @SpringBootTest
   class JpaDemoApplicationTests {
   
       @Autowired
       UserService service;
   
       @Test
       void contextLoads() {
           List<User> list = service.findAllBySex(“男”);
           list.forEach(u -> System.out.println("姓名：" + u.getUname));
       }
   
   }
   

2. 使用@Query + JPQL/SQL

1. 修改上述案例中的JpaRepository

   package com.demo.repostory;
   
   import org.springframework.data.jpa.repository.JpaRepository;
   import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
   import org.springframework.data.jpa.repository.Modifying;
   import org.springframework.data.jpa.repository.Query;
   import com.demo.bean.User;
   import javax.transaction.Transactional;
   import java.sql.Date;
   import java.util.List;
   
   public interface UserRepository extends JpaRepository<User, Long> {
      	/**
      	* 如果涉及到了update、delete或insert, 则需要添加@Modifying 和 @Transactional。
      	* nativeQuery为false（默认）时使用的是JPQL，如果使用JPQL，则下面的sql需要改为：
      	* <code>select u.uid, u.uname, u.sex from #{#entityName} where u.sex = ?1</code>
      	*/
      	@Transactional
       @Modifying
       @Query(value = "select uid, uname, sex from user where sex = ?1", nativeQuery = true)
       List<User> findAllBySex(String sex);
   }
   

3. 在service中使用SQL/JPQL

如果在service中使用sql，则可以不使用User和UserRepository 。

package com.demo.service;
	
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import java.util.List;

@Service
public class UserService {
	
	@PersistenceContext
    private EntityManager entityManager;
	
	public List findAllBySex(String uname) {
		String sql = "select uid, uname, sex from user where sex = :sex";
		Query query = entityManager.createNativeQuery(sql)
                .setParameter("sex", sex);
        List list = query.getResultList();
        return list;
	}
}

参考资料

---

1. https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.repositories
2. Spring Data JPA 常用的几种Repository接口
3. JPQL Language Reference

本文地址：https://blog.csdn.net/Yinbin_/article/details/107370825