Question 196569 in Mysql Question posted in
The official MySQL documentation can be found here.

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

    @Query(value = " with cte as (n" +
            "    select id, 1 as quantityn" +
            "    from productn" +
            "    where id = :productIdn" +
            "    union alln" +
            "    select distinct combo_id as id,n" +
            "                    quantityn" +
            "    from combon" +
            "    where product_id = :productId),n" +
            "     result as (n" +
            "         select o.product_id, o.quantity * c.quantity as 'quantity'n" +
            "         from order_product on" +
            "                  inner join cte cn" +
            "                             on o.product_id = c.idn" +
            "     )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?

Tags:

2

Answers


  1. 0

    You may do the following:

    1. Add a public static void main() method to your current class.
    2. Cut and paste the query string as String value = " with cte as ..."
    3. 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.

    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search