用于NativeQuery的Spring JPA长SQL字符串 [英] Spring JPA long SQL String for NativeQuery

问题描述

在我的Spring JPA项目中，我有一个这样的回购文件:

In my Spring JPA Project, I have a repo file as such:

@Repository
public interface StudentRepository extends JpaRepository<Student, Integer>{

@Query(value = "select * from students", nativeQuery = true)
public List<Student> findStudents(
        @Param("keyword") String keyword
        );
}

有了这个，我仍然可以复制粘贴SQL并在我的数据库软件中运行.

但是当涉及到这样的大型查询时:

With this, I can still copy paste the SQL and run in my DB software.

But when it comes to large queries as such:

    @Query(value = "SELECT\n" + 
        "*\n" + 
        "FROM\n" + 
        "students\n" + 
        "WHERE\n" + 
        "(\n" + 
        "`id` LIKE CONCAT('%', :keyword, '%') OR\n" + 
        "`name` LIKE  CONCAT('%', :keyword, '%') OR\n" + 
        "`desc` LIKE  CONCAT('%', :keyword, '%') OR\n" + 
        "`sex` LIKE  CONCAT('%', :keyword, '%')\n" + 
        ")", nativeQuery = true)
public List<Student> findStudents(
        @Param("keyword") String keyword
        );

我不能真正直接复制粘贴并在DB软件中运行，我必须删除"+""\ n"字符. 我已经尝试过Java的""SQL_QUERY_STRING""，但它不允许这样做.

I can't really directly copy paste and run in the DB software, I have to remove the "+" "\n" characters. I've tried Java's """SQL_QUERY_STRING""" but it doesn't allow it.

是否有其他替代方法?

Are there any alternative approach to this?

更新

我尝试了三重双引号，但是它给出了:

I tried the triple double-quote but it gives:

字符串文字没有被双引号正确

String literal is not properly closed by a double-quote

推荐答案

JEP 355 进行了介绍多行字符串.您可以利用它们来简单地复制粘贴sql:

JEP 355 introduces multiline strings. You can leverage them to simply copy-paste the sql:

@Query(value = 
"""
    select * 
    from students
    ...
""", nativeQuery = true)

请注意，它是在Java 13中作为预览引入的.因此，您至少需要升级到Java 13并启用预览功能才能使用它.

Note, that it was introduced in java 13 as a preview. So you need to upgrade at least to java 13 and enable preview features to make use of it.

如果没有，则可以通过用空格替换换行符来提高可读性:

If not, you can improve readability by replacing newline characters with spaces:

@Query(value = "SELECT " + 
    "FROM " + 
    "students " + 
    ...

这篇关于用于NativeQuery的Spring JPA长SQL字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！