Spring JPA @Query - Trying to use 1 Param for multiple SQL arguments; getting error message about SQL Syntax

Question

I am using the @Query annotation in Spring JPA to write a custom query. I am having success when doing this for just 1 or 2 small strings -- however, I am trying to create 1 Param called "args" which could have several constraints/arguments.

For example, the "args" parameter is being passed as "title like '%iphone%'" (title%20like%20%27%25iphone%25%27);

The "addArgs" is additonal constraints like " and title like '%12%'" (and%20title%20like%20%27%2512%25%27 )

    @Query(
            value = "select :id as Id, :session as sessionId, :secondary as secondary, :primary as returnedData from :table where :args :addargs order by :order ;",
            nativeQuery = true
    )
    List<TypeAhead> showMe(
            @Param("id") String id,
            @Param("session") String session,
            @Param("primary") String primary,
            @Param("secondary") String secondary,
            @Param("table") String table,
            @Param("args") String args,
            @Param("addArgs") String addArgs,
            @Param("order") String order
            );

When I do this, I am getting the error message about SQL Syntax. I tried to log the SQL to the console; the System.out.println that I have written myself works fine when I paste into MySQL workbench, but doesn't work when using the :args parameter.

Below is how these Params are being logged by JPA:

2021-04-13 01:34:05.189 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [product_id]
2021-04-13 01:34:05.189 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [666]
2021-04-13 01:34:05.189 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [price]
2021-04-13 01:34:05.189 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [title]
2021-04-13 01:34:05.190 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [VARCHAR] - [products]
2021-04-13 01:34:05.190 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [VARCHAR] - [title like '%iphone%']
2021-04-13 01:34:05.190 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [7] as [VARCHAR] - [and title like '%12%']
2021-04-13 01:34:05.190 TRACE 6576 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [8] as [VARCHAR] - [title asc]

Below is the query printed to the console using System.out.println - when I copy & paste this into MYSQL it's working fine.

select product_id as Id, 666 as session_id, price as secondary, title as returned_data  from products where title like '%iphone%' and title like '%12%' order by title asc;

When I paste this into MySQL to test the query it comes back as expected & looks good. However I am getting errors when running the Java app.

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''products' where title like 'title like \'%iphone%\'' 'and title like \'%12%\'' ' at line 1

I am new to this so this may be a clunky way of doing this. Can someone help me understand what's going wrong, or why this isn't working as expected? Thanks in advance!

EDIT/UPDATE:

What is needed is to use EntityManager to dynamically build the query. https://www.baeldung.com/hibernate-entitymanager

Jignesh M. Khatri · Accepted Answer · 2021-04-13 06:23:59Z

1

There is syntax error in your query.

To create fully dynamic queries, need to create custom repository, and build dynamic query there, and execute it.

Below is the minimal code:

@Repository
public interface CustomRepository {
    public List<ReturnObj> customQueryMethod(Long param);
}

@Repository
public class CustomRepositoryImpl implements CustomRepository {
    
    @Autowired
    private EntityManager entityManager;
    
    public List<ReturnObj> customQueryMethod(Long param)  {
        // build sql query here
        Query query = entityManager.createNativeQuery(sql, Tuple.class);
        List<Tuple> data = query.getResultList();
        // do operations on Tuple data and return
    }
}

@Repository
public interface MainRepository extends JpaRepository<MainEntity, Long>, CustomRepository {}

Now wherever you want to use this custom repository method, just autowire the MainRepository, and call the customQueryMethod there.

edited Apr 13, 2021 at 6:23

answered Apr 13, 2021 at 5:46

Jignesh M. Khatri

1,6561 gold badge16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Derek Over a year ago

My apologies - I had already started trying to debug for a while. What I am actually trying is like this:

value = "select :id as Id, :session as sessionId, :secondary as secondary, :primary as returnedData from :table where :args :addargs order by :order ;",

where :args encapsulates everything which would follow "WHERE" ... I will adjust my question above to reflect this.

Jignesh M. Khatri Over a year ago

Oh okay. You want to build the query dynamically. Then I guess that would not be possible in this way. You need to create custom repository, and build query dynamically there and execute it with EntityManager.

Jignesh M. Khatri Over a year ago

If you want to make :addArgs optional, than you can achieve that. Let me update my answer accordingly.

Jignesh M. Khatri Over a year ago

Check my updated answer. Pass :addArgs param as null if not required, and it will be handled in the query.

Jignesh M. Khatri Over a year ago

What JPA does is build and cache all the queries while starting the application. It increases the performance. During execution, it only need to pass the parameters to the query. So you cannot create dynamic queries like this. Only way to create dynamic queries is to use custom repository, and EntityManager.

|

Collectives™ on Stack Overflow

Spring JPA @Query - Trying to use 1 Param for multiple SQL arguments; getting error message about SQL Syntax

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related