JPA Join tables Query

Question

I am working on a school project, and I am having trouble with joining tables so I can display output in JSP file using JSTL. I will provide all necessary code. I know that I need to connect entities somehow, but I don't know how.

SQL:

CREATE TABLE IF NOT EXISTS `totelegram`.`contacts` (
`id` INT NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`phone_number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
UNIQUE INDEX `phone_number_UNIQUE` (`phone_number` ASC))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `totelegram`.`messages` (
`id_message` INT NOT NULL AUTO_INCREMENT,
`message` VARCHAR(2000) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT 
NULL,
`time` VARCHAR(45) NOT NULL,
`contacts_id` INT NOT NULL,
 PRIMARY KEY (`id_message`),
 UNIQUE INDEX `id_message_UNIQUE` (`id_message` ASC),
 INDEX `fk_messages_contacts_idx` (`contacts_id` ASC),
 CONSTRAINT `fk_messages_contacts`
 FOREIGN KEY (`contacts_id`)
 REFERENCES `totelegram`.`contacts` (`id`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
 ENGINE = InnoDB;

Contacts.java

@Entity(name = "contacts")
public class Contacts implements Serializable{

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private int id;

@javax.persistence.Column(name = "first_name")
private String firstName;

@javax.persistence.Column(name = "last_name")
private String lastName;

@javax.persistence.Column(name = "phone_number")
private String phoneNumber;

...getters/setters, constructor, toString...

Messages.java

@Entity(name = "messages")
public class Messages implements Serializable{

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@javax.persistence.Column(name = "id_message")
private int id;

private String message;

private String time;

@javax.persistence.Column(name = "contacts_id")
private int contactsId;

...getters/setters, constructor, toString...

MessagesRepository.java

public interface MessagesRepository extends JpaRepository<Messages, Integer> {

//custom query which will output this
//SELECT b.message, b.time, b.contacts_id, a.first_name, a.last_name FROM messages AS b INNER JOIN contacts as A ON (b.contacts_id=a.id) ORDER BY time ASC;
public List<Messages> findAll(); 

}

I hope I was clear. Thanks to everybody in advance.

score 0 · Accepted Answer · answered Mar 10 '19 at 14:14

0

As far as i understand, one contact can have N messages and you cannot have a Message without the Contact, right?

Since you have relations between classes, you have to use specific annotations in jpa, for example:

in the Message Class, you should use the @ManyToOne annotation, since you have Many Messages for One Contact. The JoinColumn will input the contacts_id in the Messages Table.

@ManyToOne @JoinColumn(name = "contacts_id") private Contacts contact;

in the Contacts Class, you should use @OneToMany annotation, since One Contact has Many Messages. The mappedBy makes a reference in contact at the Message Class.

@OneToMany(mappedBy = "contact") private List<Messages> messages = new ArrayList<>();

So far you made a Bidirectional reference between Contacts and Messages. Now in your service class, i would recommend you find the Messages through the Contacts, since you cannot have a message without the contact. Its a Repository principle.

Contacts con = repository.findOne(1); con.getMessages();

btw, sorry for the bad english.

answered Mar 10 '19 at 14:14

Guilherme Stella

153
1
8

Thank you for your help so far. I already found those ManyToOne and OneToMany annotations online, and with your explanation I am starting to understand them. Can you just explain that last part with Repository principle? Where should that code be putted? As I explained in commented SQL query I need to display "select * from messages" with aditional info of contacts first and last name.. When I do that in sql command prompt it works, but I am having problem implementing it in Java. – Vukašin Kosanović Mar 10 '19 at 14:46
**Repository is an abstraction of a collection of objects**, so, since you have a Contact and a list of its messages, then you can only implement a ContactsRepository just like you did above with Messages. ` public interface ContactsRepository extends JpaRepository {} ` Then in your service class, you can find a Contact and then get all messages ` Contacts con = contactsRepository.findOne(1); con.getMessages(); ` this link explains Repository [link](https://stackoverflow.com/questions/8550124/what-is-the-difference-between-dao-and-repository-patterns) – Guilherme Stella Mar 10 '19 at 14:56
Where exactly those two lines of code go? Inside ContactsRepository, inside method public List findOne()? Sorry to bother you, but that last part is really not logical to me. – Vukašin Kosanović Mar 10 '19 at 15:01
oh sorry, the last one should be in a service class, like: ContactsService. I didnt really understand JSP and JSTL, but from the JSP o should call a method (ie: Contacts getContactById(int id){}) in the ContactsService class that should provide the contact and messages data. – Guilherme Stella Mar 10 '19 at 17:27

JPA Join tables Query

1 Answers1