数据库对象视图、存储过程与Spring Data JPA中的联合使用

1、JPA访问视图

由于视图是一个虚拟的表，因此视图的访问与一般表的访问差不多，数据库中的数据如下图所示：

具体实现代码如下：
定义视图：

create view view_users 
as
select*from user

model类：

@Entity
@Table(name="view_users")
@Data
public class User implements Serializable {
	private static final long serialVersionUID = 1L;
	@Id
	private String id;
	private String name;
	private String password;
	private String sex;
}

Repository接口：

public interface UserRepository extends JpaRepository<User, String> {
	@Query(value="select*from view_users",nativeQuery=true)
	List<User> findUserByUserView();
}

Service接口及实现：

public interface IUserService {
	public List<User> findUsers();
}

@Service(value="userService_entityManager")
public class UserServiceImpl implements IUserService {

	@Autowired
	private UserRepository userRepository;
 
	@Override
	public List<User> findUsers() {
		List<User> users=userRepository.findUserByUserView();
		return users;
	}
}

controller类：

@RestController
public class UserController {
	@Autowired
	@Qualifier(value="userService_entityManager")
	private IUserService userService;

	@RequestMapping(value="/users",method=RequestMethod.GET)
	public ResponseEntity<List<User>> findUsers(){
		return ResponseEntity.ok(userService.findUsers());
	}
}

执行结果：

[{"id":"001","name":"rhine","password":"28e5ea71eb6600afb02132dcf27b8e75","sex":null},{"id":"002","name":"xixi","password":"xxxx","sex":null}]

2、JPA访问存储过程

2.1、返回结果集的存储过程

定义存储过程:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_users`()
BEGIN
	SELECT *FROM user;
END

model类：

@Data
@Entity
@NamedStoredProcedureQuery(
			name = "user.list", 
			procedureName = "proc_users",
			resultClasses= {UserProcParam.class})
public class UserProcParam{
	@Id
	private String id;
	@Column(name="name")  // 
	private String userName;
	private String password;
	private String sex;
}

由于我们的实体类中的字段有与数据库中的字段不一致的情况，可以有两种方式来解决：
第一种：使用@Column(name="【数据库中的字段名】")
第二种：使用@SqlResultSetMapping注解，具体实现如下：

@Data
@Entity
@NamedStoredProcedureQuery(
			name = "user.list", 
			procedureName = "proc_users",
			resultSetMappings= "userMapping"
	)
@SqlResultSetMapping(
		name="userMapping",
		entities= {@EntityResult(entityClass=UserProcParam.class,fields= {
				@FieldResult(name="id",column="id"),
				@FieldResult(name="userName",column="name"),
				@FieldResult(name="password",column="password"),
				@FieldResult(name="sex",column="sex"),
				})}
)
public class UserProcParam{
	@Id
	private String id;
	private String userName;
	private String password;
	private String sex;
}

Service层接口及实现类：

public interface IUserService {	
	public List<UserProcParam> findUsersByProc();
}

@Service(value="userService_entityManager")
public class UserServiceImpl implements IUserService {
    @PersistenceContext
    private EntityManager entityManager;

	@Override
	public List<UserProcParam> findUsersByProc() {
		StoredProcedureQuery store = this.entityManager.createNamedStoredProcedureQuery("user.list");
		List<UserProcParam> list=store.getResultList();
		return list;
	}

controller层的实现类这里就省略了。

2.2、返回单个数值的存储过程

定义存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(OUT num INT)
BEGIN
	DECLARE number int;
	SELECT COUNT(*) INTO number FROM user;
	SET num=number;
END

model类：

@Data
@Entity
@NamedStoredProcedureQuery(name="user.count",procedureName="proc_users_total",parameters= {
		@StoredProcedureParameter(mode=ParameterMode.OUT, name="num", type=Integer.class)
	})
public class UserProcParam{
	@Id
	private String id;
	@Column(name="name")
	private String userName;
	private String password;
	private String sex;
}

service层接口及实现类：

public interface IUserService {
	int findUsersCountByProc();
}

@Service(value="userService_entityManager")
public class UserServiceImpl implements IUserService {
    @PersistenceContext
    private EntityManager entityManager;
   
	@Override
	public int findUsersCountByProc() {
		StoredProcedureQuery store = this.entityManager.createNamedStoredProcedureQuery("user.count");
		int count=(int) store.getOutputParameterValue("num");
		return count;
	}
}

controller层省略。