JPA subselect in MANYTOMANY

Question

JPA subselect in MANYTOMANY

Navigation

#1 by (3 votes)

4

I'm developing a system with java + jpa + mysql but now I have a question that I still can not find the answer ... Given the tables and entities below, I would like to set up a jpql to perform the following query in native SQL:

SELECT * FROM politico po
where po.idpolitico not in ( SELECT mu.idpolitico FROM info_muni_politico mu  )

I tried to mount the following JPQL but the error in the execution:

StringBuilder sql = new StringBuilder("SELECT p FROM Politico p ");
        sql.append("WHERE p.id  NOT IN ( ");
        sql.append("SELECT p2.politicos FROM InfoMunicipio p2 )");
        Query q = em.createQuery(sql.toString());
        List<Politico> resultList = q.getResultList();

Error:

10: 05: 49,637 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-20) SQL Error: 1064, SQLState: 42000

Does anyone know how to mount this jpql?

Mapped Entities

@Entity
@Table(name="info_municipio")
public class InfoMunicipio {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="idinfo_municipio")
    private Integer id;
    @Column(name="num_habitantes")
    private Integer numeroHabitantes;
    @ManyToMany(fetch=FetchType.LAZY)
    @JoinTable( name="info_muni_politico", joinColumns=@JoinColumn(name="idinfo_municipio"), inverseJoinColumns=@JoinColumn(name="idpolitico") )
    private List<Politico> politicos;

    //getters e setters 
}




@Entity
@Table(name="politico")
public class Politico {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="idpolitico")
    private Integer id;
    @Column(name="nome")
    private String nome;

    //getters e setters
}

Bank Tables

CREATE TABLE 'info_municipio' (
  'idinfo_municipio' int(11) NOT NULL AUTO_INCREMENT,
  'num_habitantes' int(10) NOT NULL,
  PRIMARY KEY ('idinfo_municipio')
)

CREATE TABLE 'info_muni_politico' (
  'idinfo_municipio' int(11) NOT NULL,
  'idpolitico' int(11) NOT NULL,
  PRIMARY KEY ('idinfo_municipio','idpolitico'),
  CONSTRAINT 'muni_info_muni_fk' FOREIGN KEY ('idinfo_municipio') REFERENCES 'info_municipio' ('idinfo_municipio') ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT 'politico_info_muni_fk' FOREIGN KEY ('idpolitico') REFERENCES 'politico' ('idpolitico') ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE 'politico' (
  'idpolitico' int(11) NOT NULL AUTO_INCREMENT,
  'nome' varchar(200) NOT NULL,
  PRIMARY KEY ('idpolitico')
)

mysql java jpa jpa-2.0 jpql

asked by anonymous 24.09.2015 / 20:20

1 answer

Star Rating - javascript Select folder / directory in an input

score 3 · Accepted Answer

You would need the entire trace stack to tell you the exact reason for the error, but your JPQL query has a problem.

In your JPQL query, you are wanting to compare an ID ( p.id NOT IN ) with a list ( SELECT p2.politicos ). This will not work.

Correcting the query, you could do it this way:

StringBuilder sql = new StringBuilder("SELECT p FROM Politico p ");
        sql.append("WHERE p.id NOT IN ( ");
        sql.append("SELECT p2.id FROM InfoMunicipio infoMun )");
        sql.append("JOIN infoMun.politicos p2 )");
        Query q = em.createQuery(sql.toString());
        List<Politico> resultList = q.getResultList();

Unfortunately, it will require an additional JOIN (if compared to equivalent native query).