2

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

enter image description here

Andronicus
  • 25,419
  • 17
  • 47
  • 88
Henry
  • 631
  • 10
  • 21
  • 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 https://stackoverflow.com/questions/30118683/how-to-log-sql-statements-in-spring-boot – seenukarthi Jun 07 '20 at 04:52

2 Answers2

3

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 " + 
    ...
Andronicus
  • 25,419
  • 17
  • 47
  • 88
  • I can't, see my update, it gives the error "String literal is not properly closed by a double-quote". – Henry Jun 07 '20 at 04:18
  • @HenryLowCZ what version of java are you using? – Andronicus Jun 07 '20 at 04:30
  • My Java version is jdk1.8.0_211 – Henry Jun 07 '20 at 04:35
  • @HenryLowCZ did you check the link I have given? It says, that this feature is available in java 13. – Andronicus Jun 07 '20 at 04:39
  • I've downloaded 13.0.2 from this link https://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 Jun 07 '20 at 08:34
  • @HenryLowCZ as stated in the answer "enable preview features", https://www.baeldung.com/java-preview-features – Andronicus Jun 07 '20 at 09:03
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>
Henry
  • 631
  • 10
  • 21