Get data from multiple tables based on entities in

Question

I have three entities like below:

Comments:

@Entity
@Table(name = "comments")
public class CommentBean implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "commentId")
    private long commentId;

    @Column(name = "topicId")
    private String topicId;
}

Topics:

@Entity
@Table(name = "topics")
public class TopicBean implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "topicId")
    private String topicId;
    @Column(name = "title")
    private String title;
    @Column(name = "details")
    private String details;
    @Column(name = "username")
    private String username;
    @Column(name = "userImage")
    private String userImage;
    @Column(name = "dayPosted")
    private String dayPosted;
}

Likes:

@Entity
@Table(name = "comment_likes")
public class CommentLikes implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "commentLikes")
    private String commentLikes;

    @Column(name = "commentId")
    private long commentId;
}

I want to get all data from three tables/entities on request:

@RequestMapping(path = "/get_data_on_login", method = RequestMethod.GET)
public ResponseEntity get_data_on_login()) throws Exception {

}

How do I achieve this? I have seen examples using @query and some examples using searchByAnd... but Its more confusing which approach to follow.

Update:

Tried using @JoinColumn to map tables to get the data using bean.findAll() however, I am getting this error:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1589) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:554) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081) ~[spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856) ~[spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) ~[spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) ~[spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at seconds47.Application.main(Application.java:24) [classes/:?]
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:954) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:882) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1648) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1585) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    ... 16 more
Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: seconds47.beans.CommentBean column: topicId (should be mapped with insert="false" update="false")
    at org.hibernate.mapping.PersistentClass.checkColumnDuplication(PersistentClass.java:830) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.mapping.PersistentClass.checkPropertyColumnDuplication(PersistentClass.java:848) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.mapping.PersistentClass.checkColumnDuplication(PersistentClass.java:870) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.mapping.PersistentClass.validate(PersistentClass.java:605) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.mapping.RootClass.validate(RootClass.java:265) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.boot.internal.MetadataImpl.validate(MetadataImpl.java:329) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:443) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:879) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1648) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1585) ~[spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    ... 16 more

Update code:

Topic Bean:

@Entity
@Table(name = "topics")
public class TopicBean implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "topicId")
    private String topicId;
    @Column(name = "title")
    private String title;
    @Column(name = "details")
    private String details;
    @Column(name = "username")
    private String username;
    @Column(name = "userImage")
    private String userImage;
    @Column(name = "dayPosted")
    private String dayPosted;

    @OneToMany(mappedBy="topicBean")
    private List<CommentBean> commentBeans;
}

commentBean:

@Entity
@Table(name = "comments")
public class CommentBean implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "commentId")
    private long commentId;

    @Column(name = "topicId")
    private String topicId;

    @Column(name = "comments")
    private String comments;

    @Column(name = "commentDate")
    private String commentDate;

    @Column(name = "userImage")
    private String userImage;

    @Column(name = "username")
    private String username;

    @ManyToOne
    @JoinColumn(name="topicId")
    private TopicBean topicBean;
}

Repositories:

comment Likes Repo:

@Repository
public interface CommentLikeRepository extends JpaRepository<CommentLikes, Long>{

    CommentLikes findByCommentId(long commentId);
}

comment reply Repo:

@Repository
public interface CommentReplyRepository extends JpaRepository<CommentReply, Long> {

    CommentReply findByReplyId(String replyId);

    @Transactional
    Long deleteByReplyId(String replyId);

}

comment Repo:

@Repository
public interface CommentRepository extends JpaRepository<CommentBean, Long>{
    List<CommentBean> findByTopicId(String topicId);
    CommentBean findByCommentId(long commentId);

    @Transactional
    Long deleteByCommentId(long deleteId);
}

Topic Repo:

@Repository
public interface TopicRepository extends JpaRepository<TopicBean, Long> {
    TopicBean findByTopicId(String topicId);

    @Transactional
    Long deleteByTopicId(String topicId);

    List<TopicBean> findByUsername(String username);
}

user info repo:

@Repository
public interface UserInfoRepository extends JpaRepository<UserInfo, Long>{

    UserInfo findByUsername(String username);

    UserInfo findRoleByUsername(String username);
}

Answer 1

Since your tags include spring-boot and spring-jpa.
I assume you can use spring data repositories

The entities posted are not associated in any way. Therefore, the entities must be associated in order to retrieve all the data with one repository The modified version of entities look like this:

comments table

@Entity
@Table(name = "comments")
public class CommentBean implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "commentId")
    private long commentId;

    @ManyToOne
    @JoinColumn(name="topicId")
    private TopicBean topicBean;

    @OneToMany(mappedBy="commentBean")
    private List<CommentLikes> commentLikesList;
}

topics table

@Entity
@Table(name = "topics")
public class TopicBean implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "topicId")
    private String topicId;
    @Column(name = "title")
    private String title;
    @Column(name = "details")
    private String details;
    @Column(name = "username")
    private String username;
    @Column(name = "userImage")
    private String userImage;
    @Column(name = "dayPosted")
    private String dayPosted;

    @OneToMany(mappedBy="topicBean")
    private List<CommentBean> commentBeans;
}

comment_likes table

@Entity
@Table(name = "comment_likes")
public class CommentLikes implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "commentLikes")
    private String commentLikes;

    @ManyToOne
    @JoinColumn(name="commentId")
    private CommentBean commentBean;
}  

Note that the above rewrite is based on the most sensible guess I can make according to the original entities.

Now the three entities are properly associated. You can just use TopicBean repository to retrieve all the TopicBeans and the other two will be retrieved as well.
topicBeanRepo.findAll()