How to get pure String value from SQL format in IntelliJ?

Question

    @Query(value = " with cte as (\n" +
            "    select id, 1 as quantity\n" +
            "    from product\n" +
            "    where id = :productId\n" +
            "    union all\n" +
            "    select distinct combo_id as id,\n" +
            "                    quantity\n" +
            "    from combo\n" +
            "    where product_id = :productId),\n" +
            "     result as (\n" +
            "         select o.product_id, o.quantity * c.quantity as 'quantity'\n" +
            "         from order_product o\n" +
            "                  inner join cte c\n" +
            "                             on o.product_id = c.id\n" +
            "     )\n" +
            "select sum(quantity)\n" +
            "from result", nativeQuery = true)
    Object method(@Param("productId") String productId);

I am using Spring Jpa to write the SQL query, the IntelliJ supports me to reformat SQL easy to read. However, when I try to copy this SQL to run in the database, it has a lot of redundant characters I need to remove such as + " \n. How can I do for copying only the value of this query?

it is automatically formatted by IntelliJ, \n means new line — Hoang Phan, Jun 29 '23 at 04:52
@HoangPhan When you cut and paste your CTE + query into IntelliJ, IntelliJ added that formatting. You need to remove it to get back to the original SQL code. Printing the string is one easy way to do that. — Tim Biegeleisen, Jun 29 '23 at 04:59
Seems like you can take advantage of this IntelliJ feature: https://stackoverflow.com/questions/31699115/intellij-idea-reverse-paste-into-string-aka-copy-from-string — Vlad Ded, Jun 29 '23 at 06:58

score 0 · Answer 1 · answered Jun 29 '23 at 04:34

0

You may do the following:

Add a public static void main() method to your current class.
Cut and paste the query string as String value = " with cte as ..."
Add a println() in the main() method

Then run your main() method and you should be able to cut and paste that output into your SQL tool.

answered Jun 29 '23 at 04:34

Tim Biegeleisen

502,043
27
286
360

I already thought this way, however, it is better if there is a way that is supported by IntelliJ – Hoang Phan Jun 29 '23 at 04:38
If you are using Java 17 and higher, you can use the multi line syntax: `@Query(value = """easy-to-read-multiline-string-without-concatenation""")` – meridbt Jun 29 '23 at 05:12

score 0 · Accepted Answer · answered Jun 29 '23 at 08:39

Type Alt+Enter on the query string and invoke Copy string concatenation text to the clipboard.

Also if you are working on Java 15 or higher, consider converting the string concatenation to a Text Block. (IntelliJ IDEA has a "Text block can be used" inspection for that). Text blocks are easier to copy and paste and easier to read as well:

    @Query(value = """
         with cte as (
            select id, 1 as quantity
            from product
            where id = :productId
            union all
            select distinct combo_id as id,
                            quantity
            from combo
            where product_id = :productId),
             result as (
                 select o.product_id, o.quantity * c.quantity as 'quantity'
                 from order_product o
                          inner join cte c
                                     on o.product_id = c.id
             )
        select sum(quantity)
        from result""", nativeQuery = true)
    Object method(@Param("productId") String productId);

How to get pure String value from SQL format in IntelliJ?

2 Answers2