Ebean many to many relationship with join fails

Question

I'm having Some Gins and I'm having some Tonics that is a many to many relation. Now I'm also having a table gin2tonic. That just has 2 keys that both foreign to the id's from tonic and gin.

I want to retrieve all Gins with the matching tonics. My db create statements are like this:

CREATE TABLE `tonic` (
  `idTonic` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `type` varchar(360) DEFAULT NULL,
  `ingredient` varchar(1440) DEFAULT NULL,
  `origin` varchar(1440) DEFAULT NULL,
  `picture_link` varchar(360) DEFAULT NULL,
  `aroma` varchar(360) DEFAULT NULL,
  PRIMARY KEY (`idTonic`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;


CREATE TABLE `gin` (
  `idGin` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `picture_link` varchar(360) DEFAULT NULL,
  `origin` varchar(1440) DEFAULT NULL,
  `ingredient` varchar(1440) DEFAULT NULL,
  `aroma` varchar(360) DEFAULT NULL,
  `alc_percentage` double DEFAULT NULL,
  `type` varchar(360) DEFAULT NULL,
  PRIMARY KEY (`idGin`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `gin2tonic` (
  `id_gin` int(11) DEFAULT NULL,
  `id_tonic` int(11) DEFAULT NULL,
  KEY `idGin_idx` (`id_gin`),
  KEY `idTonic_idx` (`id_tonic`),
  CONSTRAINT `fk_gin2tonic_idGin` FOREIGN KEY (`id_gin`) REFERENCES `gin` (`idGin`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gin2tonic_idTonic` FOREIGN KEY (`id_tonic`) REFERENCES `tonic` (`idTonic`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In my java classes I thought I could do this like this:

@Entity
public class Gin extends Model {
    @Id
    @Column(name="idGin")
    private Integer idGin;
    private String aroma;
    ...// some other vars from the database not important

    @ManyToMany
    @JoinTable(name="gin2tonic")
    private List<Tonic> tonics;

        public static Finder<Integer, Gin> find = new Finder<>(
            Integer.class, Gin.class
    );
}


@Entity
public class Tonic extends Model {
    @Id
    @Column(name="idTonic")
    private Integer idTonic;

    private String aroma;

    // some other vars from the database not important


    @ManyToMany(mappedBy = "tonics")
    public List<Tonic> tonics;

    public static Finder<Integer, Tonic> find = new Finder<>(
            Integer.class, Tonic.class
    );

}

Then I execute this like:

 List<Gin> gins = Gin.find.all();

I'm getting as error this:

Can someone help me out please?

EDIT:

Thanks to singhakash the error is solved but, I'm now getting BeanList deferred when I want to print out the list like this:

List<Gin> gins = Gin.find.all();
for(Gin x : gins){
    System.out.println("idGin: " + x.getIdGin());
    System.out.println("Tonics: "+ x.getTonics());
    System.out.println("---------------------");
}

EDIT:

I know that it works with lazy loading when I do x.getTonics.get(0) it gives me this error:

As far as I know, is the query wrong, because he doesn't know that the columns in gin2tonic are id_gin instead of idGin ( correct me when I'm wrong )

Answer 1

You have same type of list variable in both entity having Many-To-Many relation .Change List type to Gin in Tonic class .Do

@Entity
public class Gin extends Model {
    @Id
    @Column(name="idGin")
    private Integer idGin;
    private String aroma;
    ...// some other vars from the database not important

    @ManyToMany
    @JoinTable(name="gin2tonic")
    private List<Tonic> tonics;

        public static Finder<Integer, Gin> find = new Finder<>(
            Integer.class, Gin.class
    );
}


@Entity
public class Tonic extends Model {
    @Id
    @Column(name="idTonic")
    private Integer idTonic;

    private String aroma;

    // some other vars from the database not important


    @ManyToMany(mappedBy = "tonics")
    public List<Gin> gins;                    //changed to Gin type

    public static Finder<Integer, Tonic> find = new Finder<>(
            Integer.class, Tonic.class
    );

}