JPA subselect at MANYTOMANY

Question

I am developing a system with java+jpa+mysql but now I have a doubt that I have not been able to find the answer... Given the tables and entities below, I would like to mount a jpql to perform the following native SQL query:

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 error on 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 assemble 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`)
) 

Answer 1

I would need the entire stack trace to tell you exactly why the error was made, but I would say that 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 won’t work out.

Correcting the query, you could do 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 extra JOIN (compared to equivalent native query).