CODEWITHSUNDEEP
springspring-mvcjpaspring-data
William Callahan
William Callahan

Reputation: 639

Create a Dynamic Query in Spring

I have been using the standard JPA implementation for a while and had no problems creating dynamic queries resulting from algorithms to search for users in a database. However, we are moving to the Spring Framework (an older version - 3.2, unfortunately) and I simply cannot figure out how to create a dynamic query in Spring.

With javax.persistance.EntityManager, I could call createQuery and give it a string to work with. However, I have found that in Spring I can only use something like the following code where I define the query in an annotation.

@Repository
@SuppressWarnings("unused")
public interface PersonRepository extends JpaRepository<Person, Long>, CrudRepository<Person, Long> {

    @Override
    List<Person> findAll(Sort sort);

    @Override
    List<Person> findAll();

    @Query("SELECT p FROM Person p ORDER BY p.lname ASC, p.fname ASC, p.mname ASC")
    List<Person> findAllSort();

    @Query("SELECT p FROM Person p WHERE UPPER(p.userName) = UPPER(?1)")
    Person findPersonByUsername(String username);


}

Here is the simplest dynamic query example I could give you that I would like to replicate for Spring:

public List<Person> getPersons(List<Long> perIds) {
    List<Person> persons;
    String whereClause = "";
    for (int i = 0; i < perIds.size(); i++) {
        if (i != 0)
            whereClause += " OR ";
        whereClause += "p.perId = '" + perIds.get(i) + "'";
    }
    persons = em.createQuery("SELECT p FROM Person p WHERE " + whereClause).getResultList();
    return persons;
}

Maybe a better question here is to ask if it is possible or if I should just keep my implementation using the Entity Manager. That being said, would anyone recomend me to change my code from using the EntityManager over to using the Spring Framework?

Upvotes: 1

Views: 1723

Answers (4)

TanvirChowdhury
TanvirChowdhury

Reputation: 2445

Please consider the below example.May be you are looking something like this.Though your question is not pretty straight forward what do you want.Using criteria or specification you can achieve many cool things.

@Service 

Optional<Person> findByEmail(String email);


@NoRepositoryBean
public interface PersonRepositoryCustom {
    Optional<Person> findByEmail(String email);
}

@Repository
public class PersonRepositoryImpl extends QuerydslRepositorySupport implements PersonRepositoryCustom {
    public PersonRepositoryImpl() {
        super(Person.class);
    }

    @Override
    public Optional<Person> findByEmail(String email) {
        JPAQuery<Person> query = getQuerydsl()
                .createQuery()
                .from(QPerson.person)
                .where(QPerson.person.email.equalsIgnoreCase(email))
                .select(QPerson.person);
        return query.fetch().stream().findFirst();
    }
}

Upvotes: -1

DuncanKinnear
DuncanKinnear

Reputation: 4643

Why not use the IN query condition instead?

Spring should allow you to do:

@Query( "SELECT p FROM Person p WHERE p.perId in :ids" )
findPersonsInIdList(@Param("ids") List<Long> perIds);

Upvotes: 2

Serge Ballesta
Serge Ballesta

Reputation: 149165

Spring allows you to use @Repository, but does not force you to do so. Spring even offers a nice interfacing of JPA that separates the low level concerns (Datasource definition, and transaction management) from the high level ones (DAO, with declarative transactions).

There is a chapter in Spring Framework Reference Manual about JPA. You should also read the part about transaction management in previous chapters.

Upvotes: 1

Funzzy
Funzzy

Reputation: 178

I do not know if we can do what you request. But I have an alternative to your method.

public List<Person> getPersons(List<Long> perIds) {
    return em.createQuery(
        "SELECT p FROM Person p WHERE p.perId = " 
        + org.springframework.util.StringUtils.collectionToDelimitedString(perIds, " OR p.perId = ", "'", "'")
    ).getResultList();
}

Upvotes: 2

Related Questions