0

---- Note: working with JavaFX and SQLite ----

Ok guys, first let me illustrate the scenario:

Tables relationship

So A has a one-to-many relation with B and C, and the last two have a many-to-many relation between them, as described above.

The sequence of record insertion is such that records in table A are always created first, and when a record in table B is created, at least one record exists in table A. Similarly, when a record in C is created, there will be at least one record in A and one in B.

Here's the question: it's possible that a record being created in C, references multiple records in B, so there will be multiple inserts in table B_C_XRef (dashed circle in picture above), at the same time.

In other words, this is what's happening when a record is created in table C:

  1. Insert record in C with foreign key from A
  2. Insert as many records in B_C_XRef as there are references to B

As an example, let's imagine that "C" is an "Orders" table, and "B" is a "Products" table. That way since an order can contain multiple products, how would I add all those records to B_C_XRef representing each order-product relationship?

Thank you in advance.

fduayer
  • 770
  • 1
  • 7
  • 23
  • What is `A` in your example? Is it even relevant? The way you describe the insertion procedure both B and C exist with an associated A before you want to insert to B_C_XRef. Also how do you determine which order contains which input? Usually you get this info from the front end and create your query based on this data... – fabian Mar 21 '18 at 13:49
  • @fabian yes, the front end takes care of that. A is not relevant here; I just want to know how to add multiple records in B_C_XRef, considering I already have the foreign keys from B and C that will make up the primary key in B_C_XRef. – fduayer Mar 21 '18 at 13:55
  • Probably this is a duplicate of this question: https://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database – fabian Mar 21 '18 at 14:05
  • I saw that post; i was hoping to find a more specific answer to my case, using Java and SQLite. – fduayer Mar 21 '18 at 14:44
  • The question is about SQLite and with jdbc all you can do is execute queries. If you want some more convenient way you probably need to use some API like hibernate. – fabian Mar 21 '18 at 15:16

0 Answers0