Spring Data JPA - exception when using findAll with MySQL

Question

I have an entity, and the DAO with interface JpaRepository<MyEntity, Long>. Using EclipseLink.

I'm using the following method from the DAO:

Iterable<MyEntity> findAll(Iterable<Long> ids);

in this way:

List<Long> listOfIds = Arrays.asList(new Long[] {1,2,3});
Iterable<MyEntity> entities = dao.findAll(listOfIds);

I've got the MySQL exception:

java.sql.SQLException: Operand should contain 1 column(s)

The SQL query that is executed in the database has the following syntax:

SELECT id, creation_date, column1, column2 FROM my_entity WHERE (id IN ((1,2,3)))

The problem is in the last braces - there are too many of them. The working query is:

SELECT id, creation_date, column1, column2 FROM my_entity WHERE (id IN (1,2,3))

Any reason why the Spring Data adds these unnecessary braces? Any way to fix it?

Please post MyEntity class code + the declaration of listOfIds — Nir Alfasi
– Nir Alfasi, Commented Sep 27, 2013 at 18:05
Provided example declaration of listOfIds in edit. The entity is a POJO annotated with @Entity. — fracz
– fracz, Commented Sep 27, 2013 at 18:14
@MatthiasHerlitzius sorry but I don't know what you are asking about — fracz
– fracz, Commented Sep 27, 2013 at 18:21
Then your problem might be related to a bug. See this link and further links to bug reports at the end: forum.spring.io/forum/spring-projects/data/… — Matthias Herlitzius
– Matthias Herlitzius, Commented Sep 27, 2013 at 18:22

fracz · Accepted Answer · 2013-09-27 18:27:08Z

3

FOUND WORKAROUND

First of all, your DAO must also implement the JpaSpecificationExecutor<MyEntity>. Then, create a Specification-factory class like this:

public final class MyEntitySpecifications {
    public static Specification<MyEntity> idsIn(final Collection<Long> ids) {
        return new Specification<MyEntity>() {
            @Override
            public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                return root.get("id").in(ids);
            }
        };
    }
}

and use your DAO like this:

Iterable<MyEntity> entities = dao.findAll(MyEntitySpecifications.idsIn(listOfIds));

Produced query is now as expected.

answered Sep 27, 2013 at 18:27

$fracz's user avatar$

fracz

21.4k18 gold badges109 silver badges154 bronze badges

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

Collectives™ on Stack Overflow

Spring Data JPA - exception when using findAll with MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related