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

最新推荐文章于 2023-12-25 19:51:13 发布

石头成说

最新推荐文章于 2023-12-25 19:51:13 发布

阅读量1.1w

点赞数 4

CC 4.0 BY-SA版权

分类专栏： spring boot 文章标签： springboot jpa

本文链接：https://blog.youkuaiyun.com/qq_27127145/article/details/88534951

spring boot 专栏收录该内容

8 篇文章

订阅专栏

本文探讨了在SpringBoot结合JPA中，使用@Query注解进行原生SQL查询时，如何解决动态传入表名的问题。通过展示具体的代码示例，指出直接在@Query中动态插入表名会导致SQL语法错误，并提出了解决方案：利用EntityManager手动构造并执行SQL语句。

问题描述：

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

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 :tableName\n" +
            "WHERE member_id = :memberId\n" +
            "ORDER BY update_time DESC;")
    List<Object[]> getTarget(@Param("tableName")String tableName, @Param("memberId")long memberId);

}

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

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

@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;
    }
}