架构小白到砖家-10-【数据存储问题】-JpaRepository支持原生sql操作

俗话说金无足赤，人无完人，jpa作为一个存储层技术方案肯定也有不完美的地方，在多表模型和特殊数据库操作方面，还是需要传统sql来进行处理。那么咱们就继续讨论如何通过jpa来实现原生sql的支持？

jpa已经提供了JpaRepository的默认实现类SimpleJpaRepository，咱们现在想自定义方法来扩展JpaRepository，就只能写一个自定义BaseRepository接口继承它，然后写一个自定义实现类BaseRepositoryImpl继承SimpleJpaRepository。

咱们就实现几个基础的原生sql方法就可以了，基本上能满足绝大多数多数需求。

其实JPA扩展方法的实现比较简单，创建JPA的Query，然后转化成ORM的SQLQuery就可以了。当然不同ORM的对象不一样，这里用的是hibernate的方案就是SQLQuery。下面直接将关键方法源码贴出来。

sqlFindMap

	public List<Map> sqlFindMap(String sql, List<Object> queryParams) {
		Query query = getEntityManager().createNativeQuery(sql); 
		SQLQuery sqlQuery = query.unwrap(SQLQuery.class);
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if(queryParams!=null&&queryParams.size()!=0){
        	 for(int index = 0;index<queryParams.size();index++){
        		 sqlQuery.setParameter(index, queryParams.get(index));
        	 }
        }

		return sqlQuery.list();
	}

findAll方法支撑对象返回结果方法

	private <T> List<T> findAll(String sql, Class<T> clazz, int page,int pageSize, List<Object> queryParams) {
		Query query = getEntityManager().createNativeQuery(sql); 
		SQLQuery sqlQuery = query.unwrap(SQLQuery.class);
		sqlQuery.setResultTransformer(Transformers.aliasToBean(clazz));
		if(queryParams!=null&&queryParams.size()!=0){
			for(int index = 0;index<queryParams.size();index++){
				sqlQuery.setParameter(index, queryParams.get(index));
			}
		}
		// 指定返回字段，根据返回Bean的字段来设置
		Field[] fields = clazz.getDeclaredFields();
		String sqlTmp = sql.toLowerCase();
		int index = sqlTmp.indexOf("from");
		for (Field field : fields) {
			if (sql.substring(0, index).contains(field.getName())) {
				// long类型必须设置类型，不然会转换异常
				if (field.getType() == long.class || field.getType() == Long.class) {
					sqlQuery.addScalar(field.getName(), StandardBasicTypes.LONG);
				} else if (field.getType() == int.class || field.getType() == Integer.class) {
					sqlQuery.addScalar(field.getName(), StandardBasicTypes.INTEGER);
				} else {
					sqlQuery.addScalar(field.getName());
				}
			}
		}
		
		// 分页设置
		if (page >= 0) {
			sqlQuery.setFirstResult((page) * pageSize);			
			sqlQuery.setMaxResults(pageSize);
		}		
		
		// 返回查询结果
		return sqlQuery.list();
	}

sqlExcute

	@Transactional
	@Modifying
	public int sqlExcute(String sql, List<Object> queryParams) {
		Query query = getEntityManager().createNativeQuery(sql); 
		SQLQuery sqlQuery = query.unwrap(SQLQuery.class);
		if(queryParams!=null&&queryParams.size()!=0){
        	 for(int index = 0;index<queryParams.size();index++){
        		 sqlQuery.setParameter(index, queryParams.get(index));
        	 }
	     }
		return sqlQuery.executeUpdate();
	}

这样咱们的JPA扩展就完成了，需要特别注意的问题，sqlExcute方法是执行update、delete等非查询事务，但是SimpleJpaRepository默认是只读事务，所以需要添加@Modifying告诉spring咱们是修改操作，并且@Transactional说明需要添加事务控制。

但是想让UserRepository使用扩展的自定义方法，还需要做两件事情，让spring把咱们的BaseRepository作为jpa默认的实现类，这样才能真正的实现jpa扩展。第一，要BaseRepository添加@NoRepositoryBean注解；第二，要在Application启动时，初始化BaseRepository的实现类。

这样咱们就可以使用自定义方法了，添加单元测试案例。

/**
	 * 测试<Specification>SQL动态查询
	 */
	@SuppressWarnings("rawtypes")
	@Test
	public void test_Jpa_Specification_SQL() {
		
		//返回结果为Map
		String sql = "SELECT u.id as 'id',u.real_name as 'realName' FROM P_USER u WHERE u.available = ?";
		List<Object> queryParams = new ArrayList<Object>();
		queryParams.add(true);
		List<Map> results = userRepository.sqlFindMap(sql, queryParams);
		Assert.assertNotNull(results);			
		for(Map u : results){
			System.out.println(u+", ");
		}
		System.out.println("===========================");
		
		//返回结果为Object
		List<User> users = userRepository.sqlFindAll(sql, User.class,queryParams);
		Assert.assertNotNull(users);			
		for(User u : users){
			System.out.print(u.getId()+"/"+u.getRealName()+", ");
		}
		System.out.println();
		System.out.println("===========================");
		
		//SQL查询分页数据		
		int index = 0; 
		int pageSize = 3;
		Page<User> page = userRepository.sqlFindPage(sql, User.class,index,pageSize,queryParams);
		Assert.assertNotNull(page);			
		System.out.println("总条数："+page.getTotalElements());
		System.out.println("总页数："+page.getTotalPages());
		System.out.println("当前页数："+page.getNumber());
		System.out.println("当前页记录数："+page.getNumberOfElements());
		System.out.print("当前页数据内容：");
		for(User u : page.getContent()){
			System.out.print(u.getId()+"/"+u.getRealName()+", ");
		}
		System.out.println();
		System.out.println("===========================");
		
		//数据操作
		long id = 84L;
		String sql2 = "DELETE FROM P_USER  WHERE id = ? ";
		queryParams = new ArrayList<Object>();
		queryParams.add(id);
		int result = userRepository.sqlExcute(sql2, queryParams);
		Assert.assertSame(result, 1);
		
	}

回顾总结，jpa可以优雅的解决单表查询和操作问题，简单固定查询条件使用JpaRepository接口方法，动态查询使用JpaSpecificationExecutor接口方法，但是多表问题还是需要使用原生sql来处理，需要扩展jpa默认的JpaRepository接口。JPQL语言建议直接放弃，为了小概率的数据库切换事件，增加额外的学习成本和未知问题的风险，实在不是一个明智的选择。