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