0

I want to build an Java program that connects with a mySQL database that consists of tables that has different many-to-many and one-to-many table connections.

The program is suposed to handle books with different authors and reviews. The authors and books is related through a many-to-many connection. (as an author can write different books and a book can have different authors). The books and reviews are connected with an one-to-many connection.

On the program side i have made an Album object that stores its authors and reviews in separate array lists. The idea is to iterate through the different albums and pick upp that album data and then its authors and reviews.

The problem is that a can't figure out the best way to do this. I suppose it would be best if this was made in a closed transaction the make it more stable.

With my limited SQL and java experience i can only see 3 different way to preform this task:

  1. Should i do this on the program side with a loop that loops through each album based on the row number and then gets the authors seperalty with a separate stored procedure and revives through the foreign keys with another stored procedure

  2. Or should i make this whole action as an stored procedure on the DB side?

  3. Or should i fetch all the tables in a big join procedure and then iterate trough this result set with som Java functions?

Or is it an better way to do this?

BR

codeer
  • 141
  • 1
  • 9
  • yes, there is JPA. It allows you to deal with only Java Objects and removes the need for writing any SQL or JDBC code. – Andy Guibert Jan 09 '16 at 20:06
  • Hi, could you please accept the most fitting answer? – Johannes Jander Jan 10 '16 at 11:19
  • Yes, an O/RM framework/library is the way to go. However, I don't recommend using Hibernate or EclipseLink for relatively small applications because those frameworks are huge. You can use a ORMLite (http://ormlite.com/) or EasyORM (https://github.com/IvanGH2/EasyORM/releases). Perhaps I should add that I've written the latter library. – dsp_user Mar 27 '16 at 15:35

2 Answers2

0

If I were you, I'd use an ORM for that. Java has the JPA2 standard which is implemented e.g. by Hibernate or Eclipselink and this allows you to easily read 1:n and n:m relationships between objects.

In most use-cases, you don't need to iterate, but start with a find query (e.g. user is searching for all books of a certain author). an ORM allows you to issue this query and get back a list of books, reviews are already a pre-filled list on each book.

Johannes Jander
  • 4,974
  • 2
  • 31
  • 46
0

I recommend you use an ORM (Object Relational Mapping) framework, that simplify the access to you relational data in MySQL, from Java Objects. See Hibernate

Alejandro Goñi
  • 532
  • 1
  • 3
  • 14