Unable to pass positional parameters into HQL query

Question

Whilst trying to create an API for my app I tried to make a query that takes a value passed in and then returns the response from the db.

@Query(value = "SELECT u FROM User u WHERE u.userID = ?")
User getUserById(String id);

I have created queries this way in other projects but cannot figure out why I get the following error on this project

JDBC style parameters (?) are not supported for JPA queries.

score 12 · Accepted Answer · answered Apr 16 '18 at 20:03

12

have you tried this :

@Query(value = "SELECT u FROM User u WHERE u.userID  = :id")
User getUserById(String id);

answered Apr 16 '18 at 20:03

Elarbi Mohamed Aymen

1,617
2
14
26

This works... is there any documentation I can read to see why this works? – Richard Payne Apr 16 '18 at 20:04
you can read more from this : https://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-creating-database-queries-with-the-query-annotation/ – Elarbi Mohamed Aymen Apr 16 '18 at 20:04
Is it necessary to annotate id parameter with @Param ? User getUserById(@Param String id); – Orçun Çolak Apr 17 '18 at 06:22
@OrçunÇolak https://stackoverflow.com/questions/16138217/how-exactly-does-param-work-java – Elarbi Mohamed Aymen Apr 17 '18 at 09:07

score 3 · Answer 2 · answered Jul 23 '19 at 19:10

Encounter the same problem in HQL, in XML approach with positional Parameter

Exception: Legacy-style query parameters (?) are no longer supported

HQL Query

String hql = "from Customer cust where cust.city=?";

Solution: Change the hql query as below

String hql = "from Customer cust where cust.city=?0";

And here we go!!

score 1 · Answer 3 · answered Aug 05 '18 at 09:12

Legacy-style query parameters (`?`) are no longer supported;

This is because hibernate does not provide support to placeholders (?) , you need to change your code to following which uses named parameters in hibernate.

It works.

@Query(value = "SELECT u FROM User u WHERE u.userID = :inputUserId")
User getUserById(String inputUserId);

:inputUserId // named parameter

Following is the reason to avoid placeholders (?).

Deprecate the Hibernate-specific (JDBC-style) positional parameters in favor of the JPA-style.

score 1 · Answer 4 · answered Nov 16 '19 at 14:02

1

String hql = "from Customer cust where cust.city= :city";
Query<Customer> query = session.createQuery(hql,Customer.Class);
query.setParameter("city",id);//here id is your value

answered Nov 16 '19 at 14:02

Manjunath Reddy R

31
3

score 1 · Answer 5 · answered Nov 22 '20 at 17:46

I have presented 2 example on how to write parameterized query in hibernate.

package demo;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import demo.entity.Student;

public class UpdateDemo {

    public static void main(String[] args) {

        // create session factory
        SessionFactory factory = new Configuration().configure("hibernate.cfg.xml").addAnnotatedClass(Student.class)
                .buildSessionFactory();

        // create session
        Session session = factory.getCurrentSession();

        try {

            // start a transaction
            session.beginTransaction();

            // query student table
            List<Student> theStudents = session.createQuery("from Student").getResultList();

            // Example1: Query with parameter
            String sql = "from Student s where s.firstName=?0 and s.lastName=?1";
            theStudents = session.createQuery(sql).setParameter(0, "Norman").setParameter(1, "Brandon").list();
            displayResult(theStudents);

            //Example 2: Query with Parameter
            String sql1 = "from Student s where s.firstName=:fname and s.lastName=:lname";
            Query<Student> query = session.createQuery(sql1);
            query.setParameter("fname", "John");
            query.setParameter("lname", "Doe");
            theStudents = query.list();
            displayResult(theStudents);

            // commit transaction
            session.getTransaction().commit();

        } finally {
            factory.close();
        }
    }

    private static void displayResult(List<Student> theStudents) {
        for (Student tempStudent : theStudents) {
            System.out.println(tempStudent);
        }
    }

}

score 0 · Answer 6 · answered Sep 19 '21 at 08:11

0

You can also add nativeQuery = true after the query

@Query(value = "SELECT u FROM User u WHERE u.userID = ?", nativeQuery = true)

User getUserById(String id);

answered Sep 19 '21 at 08:11

score 0 · Answer 7 · answered Oct 27 '21 at 15:05

0

You can use ?1 since you have one input parameter in getUserById():

@Query(value = "SELECT u FROM User u WHERE u.userID = ?1")
User getUserById(String id);

answered Oct 27 '21 at 15:05

Grygorii

162
2
6

Unable to pass positional parameters into HQL query

7 Answers7