Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Filtering OneToMany and ManyToOne relationship mappings

I'll create some example sql. Anyway, that first selection _expression_ i created worked pretty well.

Let me ask you something else. How do i add an Order by _expression_ into a DatabaseMapping selection query? @OrderBy just doesn't work out for me

Thank you


2013/6/12 Tom Ware <tom.ware@xxxxxxxxxx>
Hi Luciano,

  I am confused about what you are trying to achieve.  Lets take a step back and try go get something working for one of the mappings where you are seeing this issue.  When we do that, hopefully we can generalize the solution to the other ones.  Can you help me simplify your example to help me understand it?  I know you have provided some of this information already, but I am having trouble piecing all together coherently.

1. What classes are required to see the problem you are trying to solve?  How are they related to each other?  Please provide all annotations that define the Entities and their relationships to one another.

2. From the information provided above, which one mapping do you want to behave differently than the default?  Specifically, how do you want it to behave differntly?  Provide sample SQL for how that relationship is currently retrieved and sample SQL for how you want it to be retreived.  Provide similar SQL examples for any changes you are hoping to see in INSERT and UPDATES to that relationship (or if you don't expect to see changes, tell me that)

Thanks,
Tom


On 12/06/2013 10:04 AM, Luciano Santos wrote:
Hi Tom.

Yes, cascade works, but the CO_NOTIFICATION column no HARMPARAMETER table isn't
updated with ManyToOne and OneToOne relationships, because they claim the source
table has a local FK to the target table, so the referencedColumnName isnt
touched. Although the OneToMany relationship knows it needs to update the target
join table on cascade so the reference isn't lost. The big problem is that i am
trying to create a ManyToOne relationship that is actually mapped as a OneToMany
behavior, but my selectionCriteria ensure there will be only one row there for
these cases you know.

No sure about your _expression_, because the TP_PARAMETER isn't on HARMPARAMETER
table, but it is on PARAMETER:

TABLE HARMNOTIFICATION (
   CO_SEQ_NOTIFICATION
   ...
)

TABLE HARMPARAMETER (
   CO_SEQ_HARMPARAMETER
   CO_NOTIFICATION
   CO_PARAMETER
   ...
)

TABLE PARAMETER (
   CO_SEQ_PARAMETER
   TP_PARAMETER
)

So, when i have a relationship like these on Notification class:

@OneToMany
@JoinColumn(name="CO_NOTIFICATION")
@HamTypeJoin("SPECIAL")
private Set<HarmParameter> specials;

@OneToMany
@JoinColumn(name="CO_NOTIFICATION")
@HamTypeJoin("ORDINAL")
private Set<HarmParameter> ordinals;

I expect to Join the PARAMETER table from HARMPARAMETER on the selection
criteria so i can access its TP_PARAMETER.

SELECT HP.*
FROM HARMPARAMETER HP
         , PARAMETER P
WHERE HP.CO_PARAMETER =P.CO_PARAMETER
AND P.TP_PARAMETER = 'SPECIAL'; -- 'ORDINAL'

Not sure, but it seems your _expression_ builder.getField("TP_PARAMETER") is
hoping to have this column on HARMPARAMETER table, which is not.

Thanks

Luciano Greiner

2013/6/12 Tom Ware <tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>


    I think we are talking about two separate issues here.

    1. You are having an issue with cascading of relationships.  Does cascading
    work without your custom annotations?  Please provide a complete example of
    the problem including the Entities involved, the relevant mappings, the code
    the you are executing and the log from the execution with
    eclipselink.logging.level=__FINEST


    2. Your customizer is not behaving as you expect.  We should be able to
    address this as well, but until your cascading is working, I think adding
    the customizer, will just make solving that issue harder.  However... I
    think your customizer code needs to be more like this:


    HarmTypeJoin joinMapping = getMappingAnnotation(mapping,
    HarmTypeJoin.class); // Get the annotation from the mapping Field
    if(joinMapping != null) {
       ExpressionBuilder builder = selectionQuery.__getExpressionBuilder();
       _expression_ selectionCriteria =
    foreignReferenceMapping.__buildSelectionCriteria();
       _expression_ extraExpression =
    builder.getField("TP___PARAMETRO").equal(joinMapping.__value());
       parameter.getField("TP___PARAMETRO").equal(joinMapping.__value());
       mapping.setSelectionCriteria(__selectionCriteria.and(__extraExpression));

    }

    -Tom


    On 11/06/2013 1:53 PM, Luciano Santos wrote:

        Hi.

        The HarmParameter class is like the OrderItem, but between
        HarmNotification and
        Parameter

        In HarmNotification class i might have a lot of references to HarmParameter,
        some of them are scalar (Basic) or a Set.

        So i can load a HarmNotification i need to classify which HarmParameter
        is goes
        for each of its refereces, because all are stored on HarmParameter table
        with
        the FK set to the Notification. The way i select which HarmParameter
        goes for
        each of its references is by restricting the associated ParameterType:

        HarmNotification * <-> * HarmParameter -> Parameter (type)

        So in my Customizer i did:

        for each ForeignReferenceMapping mapping
        HarmTypeJoin joinMapping = getMappingAnnotation(mapping,
        HarmTypeJoin.class); //
        Get the annotation from the mapping Field
        if(joinMapping != null) {
            ExpressionBuilder builder = new
        ExpressionBuilder(__HarmParameter.class);

            _expression_ parameter = builder.get("parameter");
            parameter.getField("TP___PARAMETRO").equal(joinMapping.__value());
            mapping.setSelectionCriteria(__builder);

        }

        So in the mapping i have:

        @OneToOne(cascade=ALL)
        @PrimaryKeyJoinColumn(__referencedColumnName="CO___NOTIFICATION")
        //@JoinColumn(name="CO_SEQ___PARAMETER",
        referencedColumnName="CO___NOTIFICATION"

        insertable=false, updatable=false)
        @HarmTypeJoin("SPECIAL")
        private HarmNotification special;

        @OneToMany(cascade=ALL)
        @JoinColumn(name=""CO___NOTIFICATION")

        @HarmTypeJoin("STANDARD")
        private Set<HarmNotification> others;

        So what i expect is:
        SELECT HP.* FROM HARMPARAMETER HP, PARAMETER P WHERE HP.CO_PARAMETER =
        P.CO_PARAMETER AND P.TP_PARAMETER = 'SPECIAL';

        I couldn't even test it yet because i am struggling trying to persist the
        OneToOne or ManyToOne relationships. The CO_NOTIFICATION column on
        HARMPARAMETER
        table doesn't get updated on cascade like on OneToMapy, neither when i
        set the
        relation as bi-directional.

        What do you think?

        Thanks


        2013/6/11 Tom Ware <tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>
        <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>>



             Please explain exactly what your HarmTypeJoin annotation does.  I
        am a bit
             confused by the fact that you say the attribute is updated with the
             OneToManyMapping, but not for the ManyToOne.  Please give more of an
             explanation.

             The easiest way to have information written to the database is to
        map it.
               You could, for instance, add a Basic Mapping for the column as well.

             -Tom


             On 11/06/2013 11:41 AM, Luciano Santos wrote:

                 Hi Tom,

                 Based on your suggestions i have created my own annotation to
        declare the
                 foreign key filter i need to apply to that case, so i have
        something
                 like this:

                 ManyToOne(cascade=ALL)
                 @JoinColumn(name="CO_SEQ_____NOTIFICATION",
                 referencedColumnName="CO_____NOTIFICATION",
                 insertable=false, updatable=false)
                 @HarmTypeJoin(value=____LABORATORY_INFO)


                 private HarmParameter information;

                 @OneToMany(cascade=ALL)
                 @JoinColumn(name = "CO_NOTIFICATION")
                 @HarmTypeJoin(value=____LABORATORY_EXAM)


                 private Set<HarmParameter> exams;


                 So this awkward brings either a collection or a single value,
        depending
                 on the
                 criteria i add to the query based on the Customizer i applied.

                 My problem now, is how to force a ManyToOne relationship to
        update the
                 referencedColumnName on persist. Is there a way? Of course this is
                 automatic for
                 the OneToMany mapping.

                 Thanks


                 2013/6/4 Tom Ware <tom.ware@xxxxxxxxxx
        <mailto:tom.ware@xxxxxxxxxx> <mailto:tom.ware@xxxxxxxxxx
        <mailto:tom.ware@xxxxxxxxxx>>
                 <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>
        <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>>>



                      Comments inline:


                      On 03/06/2013 7:33 PM, Luciano Santos wrote:

                          Hello Tom.

                          Thanks for the response. I got how it works. Perfect
                 flexibility to be
                          able to
                          express the additional criterion as JPA-QL or SQL
        Expressions.


                      Please feel free to enter an enhancement request for this
        item and
                 vote for
                      it.  With enough votes we would consider implementing more
        direct JPA
                      configuration for this feature.



                          I'll create a base DescriptionCustomizer
        implementation and
                 reuse it for the
                          occurrences. Although i am thinking a good way to
        spread this
                 into code,
                          because
                          i have about 45 occurences of scalar references to
        UserRole
                 (Not really
                          UserRole, was just an example), and other 50 to Sets of
                 UserRole. So i am
                          thinking of creating a custom annotation such as
                          @UserRoleFilter(holeType="______STANDARD") and add

        into mapping.



                          Will i be able to access the declaring Column from the
                 DescriptionCustomizer
                          somehow so i can grab the annotation data?


                      It is not clear to me what you mean when you say declaring
        column
                 but...

                      A DescriptorCustomizer has access to a ClassDescriptor
        which has
                 all the
                      information about how a particular Entity is mapped,
        including, not
                 only,
                      all the information about all the mappings, but also
        information
                 about the
                      table it is mapped too, caching etc...  If the information
        is used
                 by JPA it
                      is there. If you can explain more precisely what you are
        looking
                 for, I can
                      give you a pointer.




                          If you think it's a good approach, would i be too
        pretentious about
                          creating a
                          processor for this custom annotation so we don't need
        to create
                 a bunch of
                          DescriptorCustomizer extensions and have to declare them
                 individually on the
                          classes containing UserRole relationship occurences?


                      The challenge here is getting the DescriptorCustomizer applied
                 before the
                      persistence unit is initialized.  In a non-Java-EE
        environment that
                 should
                      be fairly easy to manage because you have control over
        when the
                      PersistenceUnit is accessed.  In Java EE, it will be more of a
                 challenge
                      since the container will typically initialize the
        persistence units
                 as they
                      are deployed.

                      -Tom


                          Thanks

                          Luciano G Santos



                          2013/6/3 Tom Ware <tom.ware@xxxxxxxxxx
        <mailto:tom.ware@xxxxxxxxxx>
                 <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>
        <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>
                 <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>>
                          <mailto:tom.ware@xxxxxxxxxx
        <mailto:tom.ware@xxxxxxxxxx> <mailto:tom.ware@xxxxxxxxxx
        <mailto:tom.ware@xxxxxxxxxx>>
                 <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>
        <mailto:tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>>>__>




                               Hi Luciano,

                                  Filtering relationships in EclipseLink
        requires the use
                 of our
                          native
                               API. The way you do it is to create a
        DescriptorCustomizer

        http://www.eclipse.org/________eclipselink/documentation/2.4/________jpa/extensions/a_____customizer.____htm#CHDCCDGC
        <http://www.eclipse.org/______eclipselink/documentation/2.4/______jpa/extensions/a___customizer.____htm#CHDCCDGC>


        <http://www.eclipse.org/______eclipselink/documentation/2.4/______jpa/extensions/a___customizer.____htm#CHDCCDGC
        <http://www.eclipse.org/____eclipselink/documentation/2.4/____jpa/extensions/a_customizer.____htm#CHDCCDGC>>



        <http://www.eclipse.org/______eclipselink/documentation/2.4/______jpa/extensions/a___customizer.____htm#CHDCCDGC
        <http://www.eclipse.org/____eclipselink/documentation/2.4/____jpa/extensions/a_customizer.____htm#CHDCCDGC>

        <http://www.eclipse.org/____eclipselink/documentation/2.4/____jpa/extensions/a_customizer.____htm#CHDCCDGC
        <http://www.eclipse.org/__eclipselink/documentation/2.4/__jpa/extensions/a_customizer.__htm#CHDCCDGC>>>





        <http://www.eclipse.org/______eclipselink/documentation/2.4/______jpa/extensions/a___customizer.____htm#CHDCCDGC
        <http://www.eclipse.org/____eclipselink/documentation/2.4/____jpa/extensions/a_customizer.____htm#CHDCCDGC>

        <http://www.eclipse.org/____eclipselink/documentation/2.4/____jpa/extensions/a_customizer.____htm#CHDCCDGC
        <http://www.eclipse.org/__eclipselink/documentation/2.4/__jpa/extensions/a_customizer.__htm#CHDCCDGC>>


        <http://www.eclipse.org/____eclipselink/documentation/2.4/____jpa/extensions/a_customizer.____htm#CHDCCDGC
        <http://www.eclipse.org/__eclipselink/documentation/2.4/__jpa/extensions/a_customizer.__htm#CHDCCDGC>

        <http://www.eclipse.org/__eclipselink/documentation/2.4/__jpa/extensions/a_customizer.__htm#CHDCCDGC
        <http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_customizer.htm#CHDCCDGC>>>>

                                  In the DescriptorCustomizer, you will have to
        access
                 the mapping
                          you want
                               to change.  Then you will have to alter the selection
                 criteria for the
                               mapping. Here is some rough code from our test
        framework:

                                  OneToOneMapping _oneToOneMapping_ =



        (OneToOneMapping)descriptor.__________getMappingForAttributeName("________computer");
                                  _expression_ exp =
                 oneToOneMapping.________buildSelectionCriteria();




                                  ExpressionBuilder builder = exp.getBuilder();
                                  _expression_ addedExpression =

        builder.getField("MAP_HRW.EMP_________LNAME").equal("Louis");



        oneToOneMapping.________setSelectionCriteria(exp.and(________addedExpression));





                                  Hopefully that will get you started.  If you have
                 problems, we
                          can help
                               you on this list.

                               -Tom


                               On 03/06/2013 11:49 AM, Luciano Santos wrote:

                                   Hello.

                                   I am about to change our project's JPA
        implementation from
                          Hibernate to
                                   EclipseLink.
                                   There is a tough relationship i am struggling to
                 accomplish.

                                   The case is similar as this (as much it might
        seems to
                 make no
                          much sense):

                                   @Entity @Table(name="tb_role")
                                   class Role {
                                       @Id
                                       private Long id;

                                       @Enumerated(STRING)
                                       private ProductType productType; //
        STANDARD, SPECIAL
                                       ...
                                   }

                                   @Entity @Table(name="rl_user_role")
                                   class UserRole {
                                         ...
                                        @ManyToOne
                                        private Role role;

                                        private String comments;
                                        ...
                                   }

                                   @Entity @Table(name="tb_user")
                                   class User {
                                          @Id
                                          private Long id;

                                          @ManyToOne
                                          // NEED to filter for SPECIAL (property or
                 column value
                          on Role)
                                          // DO NOT HAVE A LOCAL FK column,
        should use
                 USER_ID on
                          rl_user_role
                                          private UserRole claimForOthersRole;

                                          @OneToMany
                                          // NEED to filter for STANDARD
        (property or
                 column value
                          on Role)
                                          @JoinColumn(name = "user_id")

                                          private Set<UserRole> roles;

                                   }


                                   Do you guys have any clue on how could i
        accomplish
                 this? I was
                          trying
                                   to use
                                   Hibernate's @Where annotation, but i could
        just filter
                 using
                          columns on
                                   "rl_user_role" (Couldn't be nested).

                                   Thank you

                                   Luciano Santos



        _______________________________________________________



                                   eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
        <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>
                 <mailto:eclipselink-users@
        <mailto:eclipselink-users@>__ec__lipse.org <http://eclipse.org>
                 <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>>
                          <mailto:eclipselink-users@ <mailto:eclipselink-users@>
                 <mailto:eclipselink-users@
        <mailto:eclipselink-users@>>__e__c__lipse.org <http://ec__lipse.org>
        <http://eclipse.org>

                          <mailto:eclipselink-users@
        <mailto:eclipselink-users@>__ec__lipse.org <http://eclipse.org>
                 <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>>>
        https://dev.eclipse.org/________mailman/listinfo/eclipselink-________users
        <https://dev.eclipse.org/______mailman/listinfo/eclipselink-______users>         _______________________________________________________



                               eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
        <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>
                 <mailto:eclipselink-users@
        <mailto:eclipselink-users@>__ec__lipse.org <http://eclipse.org>
                 <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>>
                          <mailto:eclipselink-users@ <mailto:eclipselink-users@>
                 <mailto:eclipselink-users@
        <mailto:eclipselink-users@>>__e__c__lipse.org <http://ec__lipse.org>
        <http://eclipse.org>

                          <mailto:eclipselink-users@
        <mailto:eclipselink-users@>__ec__lipse.org <http://eclipse.org>
                 <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>>>
        https://dev.eclipse.org/________mailman/listinfo/eclipselink-________users
        <https://dev.eclipse.org/______mailman/listinfo/eclipselink-______users>


        <https://dev.eclipse.org/______mailman/listinfo/eclipselink-______users
        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users>>



        <https://dev.eclipse.org/______mailman/listinfo/eclipselink-______users
        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users>

        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>>>




        <https://dev.eclipse.org/______mailman/listinfo/eclipselink-______users
        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users>

        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>>

          <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>
                 <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
        <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>>>>




                          _____________________________________________________
                          eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
        <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>
                 <mailto:eclipselink-users@
        <mailto:eclipselink-users@>__ec__lipse.org <http://eclipse.org>
                 <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>>
        https://dev.eclipse.org/______mailman/listinfo/eclipselink-______users
        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users>

        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>>

          <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>
                 <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
        <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>>>

                      _____________________________________________________
                      eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
        <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>
                 <mailto:eclipselink-users@
        <mailto:eclipselink-users@>__ec__lipse.org <http://eclipse.org>
                 <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>>
        https://dev.eclipse.org/______mailman/listinfo/eclipselink-______users
        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users>

        <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>>

          <https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>
                 <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
        <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>>>




                 ___________________________________________________
                 eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
        <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>
        https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>
                 <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
        <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>>

             ___________________________________________________
             eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
        <mailto:eclipselink-users@__eclipse.org
        <mailto:eclipselink-users@eclipse.org>>
        https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users
        <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>
             <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
        <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>>




        _________________________________________________
        eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
        https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
        <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>

    _________________________________________________
    eclipselink-users mailing list
    eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
    https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
    <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>




_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


Back to the top