Spring JPA long SQL String for NativeQuery

Question

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

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
        );

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?

UPDATE

I tried the triple double-quote but it gives:

String literal is not properly closed by a double-quote

If you just need the SQL for debugging, you can print the SQL in log from there you can copy the SQL without the "+" symbol. check this post stackoverflow.com/questions/30118683/… — seenukarthi
– seenukarthi, Commented Jun 7, 2020 at 4:52

Andronicus · Accepted Answer · 2020-06-07 04:40:02Z

5

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

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

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 " + 
    ...

edited Jun 7, 2020 at 4:40

answered Jun 7, 2020 at 3:58

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Henry Over a year ago

I can't, see my update, it gives the error "String literal is not properly closed by a double-quote".

Andronicus Over a year ago

@HenryLowCZ what version of java are you using?

Henry Over a year ago

My Java version is jdk1.8.0_211

Andronicus Over a year ago

@HenryLowCZ did you check the link I have given? It says, that this feature is available in java 13.

Henry Over a year ago

I've downloaded 13.0.2 from this link jdk.java.net/archive . And I've added the jdk to Environment Variable and added installed JREs in Eclipse > Windows Preferences. I've even reset the JRE System Library of my project, yet the error is still the same

|

Henry · Accepted Answer · 2020-06-07 10:16:48Z

0

I've tried @Andronicus's method, but it doesn't work out for me.

The way I solved it is by creating a new directory and file:
/src/main/resources/META-INF/orm.xml

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.0" xmlns="http://java.sun.com/xml/ns/persistence/orm"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm
    http://java.sun.com/xml/ns/persistence/orm_2_0.xsd ">

<named-native-query name="Student.findStudents" result- 
class="path/package_to_student_class">
    <query>
        SELECT
        *
        FROM
        student
        WHERE
        (
        `id` LIKE CONCAT('%', :keyword, '%') OR
        `name` LIKE CONCAT('%', :keyword, '%') OR
        `desc` LIKE CONCAT('%', :keyword, '%') OR
        `sex` LIKE CONCAT('%', :keyword, '%') OR
        )
    </query>
</named-native-query>
</entity-mappings>

answered Jun 7, 2020 at 10:16

Henry

64112 silver badges21 bronze badges

1 Comment

Jin Lim Over a year ago

can you give me more details? how did you load this sql from the application?

Collectives™ on Stack Overflow

Spring JPA long SQL String for NativeQuery

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related