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

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>

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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related