解决使用springboot+ jpa + @query原生sql查询，无法动态插入表名的问题

83 阅读 0 评论 55 点赞

我是靠谱客的博主想人陪豌豆，这篇文章主要介绍解决使用springboot+ jpa + @query原生sql查询，无法动态插入表名的问题，现在分享给大家，希望可以做个参考。

问题描述：

在使用springboot2.0 + jpa+ @query原生sql查询时，无法动态插入表名

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public interface BalanceTargetInfoRepositoryWithNativeQuery extends JpaRepository<BalanceTargetInfoBean, Long> {
/**
*
不能使用，问题：动态传入表名，会在拼接sql时带单引号，导致sql执行失败
* @param memberId
* @return
*/
@Query(nativeQuery = true,value = " SELECT " +
"id AS targetId, " +
"state AS state,n" +
"reduce AS reduce,n" +
"end_date AS endDate,n" +
"start_date AS startDate,n" +
"target_name AS targetName,n" +
"target_value AS targetValue,n" +
"complete_rate AS completeRate,n" +
"current_value AS currentValue,n" +
"DATEDIFF(end_date, CURRENT_DATE()) AS remainingDay,n" +
"notification AS notification FROM :tableNamen" +
"WHERE member_id = :memberIdn" +
"ORDER BY update_time DESC;")
List<Object[]> getTarget(@Param("tableName")String tableName, @Param("memberId")long memberId);
}

使用？1传递表名也是报下面的错误。会自动给表名添加单引号，导致sql无法执行。

复制代码

1
2
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''target_info_0'
WHERE member_id = 7387250

解决：使用实体管理器EntityManager

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@Component
public class BalanceTargetInfoRepository {
//注入的是实体管理器,执行持久化操作
@PersistenceContext
EntityManager entityManager;
public
List<Object[]> getTarget(String tableName, long memberId){
String sql=" SELECT " +
"id AS targetId, " +
"state AS state,n" +
"reduce AS reduce,n" +
"end_date AS endDate,n" +
"start_date AS startDate,n" +
"target_name AS targetName,n" +
"target_value AS targetValue,n" +
"complete_rate AS completeRate,n" +
"current_value AS currentValue,n" +
"DATEDIFF(end_date, CURRENT_DATE()) AS remainingDay,n" +
"notification AS notification FROM "+tableName+" WHERE member_id ="+memberId+" ORDER BY update_time DESC;";
List<Object[]> list = entityManager.createNativeQuery(sql).getResultList();
return list;
}
}