This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.
Example: Table 1: item {partno (PK), description} Table 2: warehouse {warehouseid (PK), description} Table 3: item_warehouse {partno,warehouseid} both fields are PK and have foreign keys on their respective tables. Run "Entity Classes from Database" wizard. Expected behavior: 1. Class Item.java will have a @JoinTable annotation and a simple @ManyToMany annotation (without mappedBy) on field warehouseCollection 2. Class Warehouse will have a @ManyToMany(mappedBy = "warehouseCollection") annotation on field itemCollection Actual behavior: 1. Class Item.java has @ManyToMany(mappedBy = "itemCollection") annotation on field warehouseCollection 2. Class Warehouse.java has @JoinTable annotation and simple @ManyToMany annotation on field itemCollection This is the reverse of what it should be because the first field in item_warehouse is partno and is the "driving" entity for the item_warehouse table. P.S.: Eclipse IDE's DALI JPA Project generates these two entity classes as described in the "Expected behavior" section. I've done some digging and found the following in JavaPersistenceGenerator.java lines 1434-1501: role.getParent() which points to the ItemWarehouse reference has WarehouseID as its first Role and PartNo as its second role. That is *not* correct according to the table definition which has Partno first and WarehouseID second. The reason why this is important to me is because I maintain the PrimeFaces CRUD Generator and am testing for which entity is the "driver" and then generate a selection box for that relationship in the edit and create pages. But right now, the selection box gets generated on the Warehouse side of this relationship, an unnatural assumption and way of looking at it. Could somebody please find out why the parent role has a reverse order of the primary key fields? Thanks.
I just reversed the keys on the item_warehouse table to see if that makes any difference in this determination, but it didn't. So it looks as if the actual field order on the join-table has no effect of where the @JoinTable annotation goes. It is almost as if the alphabetical order is used in reverse.
will try now
Suppose there is nothing can be wrong with item and warehouse But it's nice to provide more details with all sql code, db used with db driver, any custom options in entities from db wizard etc, jdk version, I have next join table CREATE TABLE item_warehouse( partno INT not null, warehouseid INT not null, CONSTRAINT pk PRIMARY KEY (partno,warehouseid), FOREIGN KEY (partno) REFERENCES ITEM(partno), FOREIGN KEY (warehouseid) REFERENCES WAREHOUSE(warehouseid) ) and jdk7_045 and current dev build. and generate entities, for Item I have: @JoinTable(name = "ITEM_WAREHOUSE", joinColumns = { @JoinColumn(name = "PARTNO", referencedColumnName = "PARTNO")}, inverseJoinColumns = { @JoinColumn(name = "WAREHOUSEID", referencedColumnName = "WAREHOUSEID")}) @ManyToMany private Collection<Warehouse> warehouseCollection; nothing similar to "mappedBy" for Warehouse: @ManyToMany(mappedBy = "warehouseCollection") private Collection<Item> itemCollection; But also it's unclear why reverse is wrong as table are symmetric to me, I suppose it can be done either way. Please reopen with more details.
One more point, If the association is bidirectional, either side may be designated as the owning side, and if entities are designed from some tables, I suppose there is no good way to say if it's not bidirectional or what side is owning.
Regarding Comment #1: It looks to me as if this is processed alphabetically into some kind of list that is then processed by Java in reverse. I've seen this on another occasion where I willed a HashMap in a particular order, passed it on to another routine which then retrieved a Set from the HashMap into a Set<Attr,Val> type of variable, and that was processed in reverse order. So I think it's something that Java does. Regarding Comment #4: I guess there's no right or wrong here. It's true. Both entities are "master" entities of their own in a sense that they both can exists and not have any children associated. That said, I'm looking at this from a GUI perspective or even business perspective. You have an item that is associated with multiple warehouses. On an item maintenance page, you would then select warehouses from a selection list by highlighting them. You would never do that in reverse where, say, you had a warehouse maintenance screen and then select items that are in a warehouse. With a massive set of items, that would be a very looong list. So what I'm saying is that it's a thing of perspective, hence the partno coming before the warehouseid in the join table. It's the "driver" of that table.
I meant to say "filled", not "willed" in my previous post.
ok, as there is "no wrong" it may not be a defect, but I see a justification for column order based selection of promary entity, as it will alllow at least some control and also may be it was the meaning when join table was created. on another side, some sample sql scrip is till welcomed and all requested details, as it may help to reproduce not "natural" order and evaluate it.
So what kind of script are you asking me for? A MySQL dump that you can load into your MySQL instance?
dbschema create with Perisstence|Db schema may help. but it may be better to have sql script to create alll 3 tables, i.e. "create table" code.
Created attachment 143803 [details] Sample Database manymanydb Here is a database to test with.
Comment on attachment 143803 [details] Sample Database manymanydb Forget this attachment. I forgot to add foreign keys to the tables. Will upload a new one.
Created attachment 143806 [details] Sample Database manymanydb Here is a MySQL sample database (dump format) with two tables (item/warehouse) and a join table (item_warehouse) to resolve the many-to-many relationship.
Created attachment 143807 [details] Sample Web Project (Undesired Outcome) This is a sample project with PrimeFaces generated via PrimeFaces CRUD Generator 0.21 (patch2). The entities were generated out of the box with NetBeans 7.4. The project targets GlassFish 4 (as Java EE 7 Web) with CDI. You will need to have PrimeFaces 4.0 in set up your Libraries. Also, the database user for the data source is "nbdev" password "go-netbeans-go".
Created attachment 143808 [details] Sample Web Project (Desired Outcome) This is a sample project with PrimeFaces generated via PrimeFaces CRUD Generator 0.21 (patch2). The entities were modified in such a way that Item.java now has the @JoinTable annotation on the warehouseCollection field and mappedBy removed on the @ManyToMany annotation. Also notice that I had to reverse the JoinColumns. Likewise, the Warehouse.java was modified by adding mappedBy to the @ManyToMany annotation. The project targets GlassFish 4 (as Java EE 7 Web) with CDI. You will need to have PrimeFaces 4.0 in set up your Libraries. Also, the database user for the data source is "nbdev" password "go-netbeans-go".
So when you run the undesired outcome project, and go into "Maintenance"->"Item" and select a partno, then click "Edit", you will see that it doesn't have a selection list for warehouses, even though the table item_warehouse suggests that partno is leading and warehouse_id is the "dependent" field. When you go to the Warehouse maintenance screen, you see it has a selection list for items. This, in my opinion, is undesired. When you run the desired outcome project, you will notice the reverse is true. Item maintenance lets you choose warehouses. This would, to me, be a more natural way of looking at this relationship. The CRUD generator does look at the relationship as it generates the pages. This is handled inside the page template (e.g. Edit.xhtml) via this this code snipped: <#elseif entityDescriptor.relationshipMany> <#if entityDescriptor.relationshipOwner> <p:selectManyMenu>Yadayadayada</p:selectManyMenu> </#if> <#else> The important part here is relationShipOwner on the entityDescriptor, which I custom-modified based on original NetBeans code. That code looks specifically for the @JoinTable annotation of an entity field. That tells it to be the leading side of the many-to-many relationship. Hope this makes a bit more sense now. Feel free to explore the source code of the CRUD generator. Here is a pointer to the relevant java code: https://sourceforge.net/p/nbpfcrudgen/code/ci/master/tree/src/org/netbeans/modules/web/primefaces/crudgenerator/palette/items/FromEntityBase.java
Lines 693-699, isRelationshipOwner() handles the test for @JoinTable annotation.
thanks, will look next week
can't reproduce with derby, may be mysql specific, with derby, db module return keys in alphabetical order by fk name, i.e. "item_warehouse_ibfk_1" first and "item_warehouse_ibfk_2" second and it's used, fr mysql it may be different order or sample sql isn't the one to reproduce the issue. regarding column ordering, it's easy for 1+1 column, but there may be some cases with 2+2 mixes with smth like col1_fk1, col1_fk2, col2_fk1, col2+fk2. Also fk ordering may have sense, you can easily alter yor constraint and change you primary object if change fk1 to fk2 and fk2 to fk1 for example. And I prefer to keep this behaviour in this case.
I'm still evaluating, and I was wrong regarding fk oderring, after alter table I have fk2 first and fk1 as second, it may depend on order it's returned by jdbc driver.
I'm able to reproduce "reverse" order with mysql, I need to get feedback regarding fk keys order from db module before continue evaluation.
Personally, I think they should not be processed in alphabetical order at all, but in order of how they are defined in the primary key. If somebody had a field that started with a character close to the end of the alphabet, but it was the first key field, followed by a field that has a character at the beginning of the alphabet, that order should be honored, and not assumed alphabetical order. There is a reason why a database designed puts field xzy in front of field abc. It's just that. It's how the data is designed. Anyway, that's just my take on it.
Correction: I meant "designer", not "designed". (In reply to kwrobel from comment #21) > Personally, I think they should not be processed in alphabetical order at > all, but in order of how they are defined in the primary key. If somebody > had a field that started with a character close to the end of the alphabet, > but it was the first key field, followed by a field that has a character at > the beginning of the alphabet, that order should be honored, and not assumed > alphabetical order. There is a reason why a database designed puts field xzy > in front of field abc. It's just that. It's how the data is designed. > > Anyway, that's just my take on it.
in this case |I\m not taling about fields/column names but about fk constraint names. regarding order in pk, it's hard to get this order if there is pk(a,b,c) and fk1(a,c) and fk2(b), it may be rare but is possible case.
Oh so you're not actually looking at the join-table's key definition but the foreign keys of it?
Ahhhhh. I see it now! This is the CREATE TABLE item_warehouse as MySQL sees it right now: CREATE TABLE "item_warehouse" ( "partno" varchar(50) NOT NULL, "warehouse_id" smallint(6) NOT NULL, PRIMARY KEY ("partno","warehouse_id"), KEY "warehouse_id" ("warehouse_id"), CONSTRAINT "item_warehouse_ibfk_2" FOREIGN KEY ("warehouse_id") REFERENCES "warehouse" ("warehouse_id"), CONSTRAINT "item_warehouse_ibfk_1" FOREIGN KEY ("partno") REFERENCES "item" ("partno") Notice that the constraint names were auto-generated by MySQL because I didn't specify them specifically. And for some reason, MySQL see's the order in a different way. But I have to caution this approach because I could have added the warehouse constraint first just because I could have thought about it first and then added the partno constraint later. Fact is: for a join table like this, the Primary Key field order is the one that determines its natural order for an end user and the database designer as well. I didn't even think about the FK relationship, but yes, I see how for you guys, this is all you're looking at. Is there ANY way you can also take the primary key and ITS field order into account?
in my opinion, for 8.0 I need to make derby/mysql consistent(may need sort what I receive from nb db module, as it return differnt order for different vedors as I see) and also fk names apprach may have goood base behind, also as it's the way it was there for a long time, I suppose some people either don't mind or like this approach. regarding taking pk, from wizard point of view it may be third tab option, regarding nb jpa api point of view, it may be one more option in helper class but it may be considered as enhanment in this case. I suppose it shouldn't be hard from implementation point of view if to take into account it's join table and all fields are part of one of two fks. but ui on third tab may be a bit overloaded.
Maybe Many-To-Many is not a common scenario in the database world. Then again, it is part of E-R and thus should work, whether people use it or not. And, it's as if it's entirely broken, just not the natural order. So what I'm reading is that this is probably going to close unresolved. Right?
after some googling, I have no cewrtain opinion anything is broken or entirely broken as nobody say there is any owning side in many to many in general. in this case any approach can be used to determine it in entities generator, and I prefer fk based approach here. soem related discussions like http://stackoverflow.com/questions/3484325/jpa-which-side-should-be-the-owning-side-in-a-mn-relationship http://stackoverflow.com/questions/2749689/what-is-the-owning-side-in-an-orm-mapping say nothing about tables design also, as tables design do not rerquire to have owning side, it's jpa limitation. the only problem I see , it's better if it will be solid instead of randsom side for any database vendor on the same database.
one more point about design and nature of manytomany, in my opinion, I don't see much difference between tasks "what stores have this item" "what items have this store"
So this is now a philosophical topic and not a bug report anymore. I get it. and since the team seems to be a little defiant about whether there is an owning side to a M:N relationship, let me pull up the Java EE 6 Documentation for the @ManyToMany annotation. Quote: "Every many-to-many association has two sides, the owning side and the non-owning, or inverse, side. The join table is specified on the owning side." [http://docs.oracle.com/javaee/6/api/javax/persistence/ManyToMany.html] Um, so you're saying it doesn't matter which way is which on a many-to-many. Apparently the designers of the @ManyToMany annotation disagree. And so do I. I think it IS important which way this is resolved. But on to something more concrete: I also created this example item/warehouse inside a PostgreSQL database, which appears to create the foreign key constraints in the natural order in which the other had them (meaning: partno, warehouse). And the resulting classes from the Entity wizard do reflect that. The item class has the @JoinTable annotation, and the warehouse has the mappedBy attribute. This would indicate a deficiency of how MySQL, either the driver, or the system itself, returns the order of foreign key constraints. Again, I think it would be meritorious to not only look at the foreign keys and how they come back from the driver, but also to correlate that with a join table's primary key.
yes, in my opinion it's more philosophical rather the an issue. for now, I already push fk sorting patch, it will make mysql code the same as for derby and as I see postresql, also itsreatively simple rule any customer can control, instead of unknown rule before. regarding specification ,mentioned above citation is from spec also, 11.1.29 ManyToMany Annotation A ManyToMany annotation defines a many-valued association with many-to-many multiplicity. If the collection is defined using generics to specify the element type, the associated target entity class does not need to be specified; otherwise it must be specified. Every many-to-many association has two sides, the owning side and the non-owning, or inverse, side. If the association is bidirectional, either side may be designated as the owning side. If the relationship is bidirectional, the non-owning side must use the mappedBy element of the ManyToMany annotation to specify the relationship field or property of the owning side. The join table for the relationship, if not defaulted, is specified on the owning side. see "either side may be designated as the owning side". yes, you have to select one, but no one is more desired then another from specification point of view. also, if we are looking at defaults for manytomany, there are no rules about promary key fields order or table columns order but about join table naming and about fk keys naming also. also what db designers/db design rules/tutorials etc are you referencing?
Listen, I'm just an individual with an opinion on the matter. If you're saying that you're going to process the FKs in order of alphabet and not how it's returned from the DB, that's fine by me. It will only make a real difference with MySQL since that DB apparently returns them in a different order than others. And that IS a solution. I don't want to blow this topic up further. And you're right, a DB designer then has control via the FK's name to determine a certain order. Works for me. Thank you for at least moving this along.
If you are at least some way happy with fk solution, I'm going to close this as fixed, otherwise I can change it into enhancment but it means occational review from release to release and may be implemented next release(some option to use pk) or never depending on current priorities and time resources. http://hg.netbeans.org/web-main/rev/11a4551c5b1d reorder tables according to fk names if required
Yeah, if you can get this resolved by FK order the way you describe, I'm perfectly fine with that. Is that something you'd roll out for 8.0 or as a patch for 7.4?
ok, it's fixed in 8.0, I do not expect it to be in a patch as it isn't much severe issue in my opinion, also don't know when next patch can be expected if any can be expected, wait for integration message or a few days to find it integrated in daily build. it will not be in 8.0 beta build as a bit late for beta.
need to review my fix tomorrow, it seems I miss smth, and will update it
http://hg.netbeans.org/web-main/rev/0cf2503aa24f please try after second commit integration
Integrated into 'main-silver', will be available in build *201401160001* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress) Changeset: http://hg.netbeans.org/main-silver/rev/11a4551c5b1d User: Sergey B. Petrov <sj-nb@netbeans.org> Log: #237965 not a fix for initial issue, but now random(or whatever is returned by jdbc/db module) order of fk is changed to alphabetical, in attached sample it cause "correct" generation, but it's just a side effect