How to restrict Hibernate to generate SQL query with columns only from root entity in Criteria API involving...












1















I have written hibernate code with criteria API involving multiple entities (having parent-child relationships & I am using createAlias() to join entities) expecting to fetch only root entity in the result.



However, when I debug the SQL generated by hibernate I observe that hibernate is fetching data (in select clause) for all the entities used in the join clause (achieved with createAlias()).



I don't think there is any need to fetch the columns for other entity other than the expected root entity since it may not be efficient and I am looking to avoid this?



I am using Hibernate version 3.6.9



Here is the scenario. There are 3 entities A, B & C having parent child relationship in following manner A (super parent) -> B (parent) -> C (child)



session.createCriteria(B.class, "b")
.createAlias("b.c", "c",Criteria.INNER_JOIN)
.createAlias("b.a", "a",Criteria.INNER_JOIN)
.add(Restrictions.in("c.prop1c", <<some value>>))
.add(Restrictions.in("a.prop1a", <<some value>>))
criteria.list()

now when hibernate generates the SQL it looks like
select
b.all columns of B
a.all columns of A
c.all columns of C
from B b
inner join A a on b.prop=a.prop
inner join C C on b.prop=c.prop
where
c.prop1c = <<some value>>
a.prop1a = <<some value>>


if you observe hibernate has generate query to fetch all columns of all entities (A, B & C) however as of now i don't need any entity other than root entity (B), data for entities like A & C should be fetched later on as per fetch strategies on their mappings to B.
I think there is no need to fetch all the data over the n/w traffic for this query, still hibernate is generating the query like this.



Is there a way I can guide hibernate to only fetch columns for entity B and not for A & C using criteria API?



select 
b.all columns of B
from B b
inner join A a on b.prop=a.prop
inner join C C on b.prop=c.prop
where
c.prop1c = <<some value>>
a.prop1a = <<some value>>









share|improve this question





























    1















    I have written hibernate code with criteria API involving multiple entities (having parent-child relationships & I am using createAlias() to join entities) expecting to fetch only root entity in the result.



    However, when I debug the SQL generated by hibernate I observe that hibernate is fetching data (in select clause) for all the entities used in the join clause (achieved with createAlias()).



    I don't think there is any need to fetch the columns for other entity other than the expected root entity since it may not be efficient and I am looking to avoid this?



    I am using Hibernate version 3.6.9



    Here is the scenario. There are 3 entities A, B & C having parent child relationship in following manner A (super parent) -> B (parent) -> C (child)



    session.createCriteria(B.class, "b")
    .createAlias("b.c", "c",Criteria.INNER_JOIN)
    .createAlias("b.a", "a",Criteria.INNER_JOIN)
    .add(Restrictions.in("c.prop1c", <<some value>>))
    .add(Restrictions.in("a.prop1a", <<some value>>))
    criteria.list()

    now when hibernate generates the SQL it looks like
    select
    b.all columns of B
    a.all columns of A
    c.all columns of C
    from B b
    inner join A a on b.prop=a.prop
    inner join C C on b.prop=c.prop
    where
    c.prop1c = <<some value>>
    a.prop1a = <<some value>>


    if you observe hibernate has generate query to fetch all columns of all entities (A, B & C) however as of now i don't need any entity other than root entity (B), data for entities like A & C should be fetched later on as per fetch strategies on their mappings to B.
    I think there is no need to fetch all the data over the n/w traffic for this query, still hibernate is generating the query like this.



    Is there a way I can guide hibernate to only fetch columns for entity B and not for A & C using criteria API?



    select 
    b.all columns of B
    from B b
    inner join A a on b.prop=a.prop
    inner join C C on b.prop=c.prop
    where
    c.prop1c = <<some value>>
    a.prop1a = <<some value>>









    share|improve this question



























      1












      1








      1








      I have written hibernate code with criteria API involving multiple entities (having parent-child relationships & I am using createAlias() to join entities) expecting to fetch only root entity in the result.



      However, when I debug the SQL generated by hibernate I observe that hibernate is fetching data (in select clause) for all the entities used in the join clause (achieved with createAlias()).



      I don't think there is any need to fetch the columns for other entity other than the expected root entity since it may not be efficient and I am looking to avoid this?



      I am using Hibernate version 3.6.9



      Here is the scenario. There are 3 entities A, B & C having parent child relationship in following manner A (super parent) -> B (parent) -> C (child)



      session.createCriteria(B.class, "b")
      .createAlias("b.c", "c",Criteria.INNER_JOIN)
      .createAlias("b.a", "a",Criteria.INNER_JOIN)
      .add(Restrictions.in("c.prop1c", <<some value>>))
      .add(Restrictions.in("a.prop1a", <<some value>>))
      criteria.list()

      now when hibernate generates the SQL it looks like
      select
      b.all columns of B
      a.all columns of A
      c.all columns of C
      from B b
      inner join A a on b.prop=a.prop
      inner join C C on b.prop=c.prop
      where
      c.prop1c = <<some value>>
      a.prop1a = <<some value>>


      if you observe hibernate has generate query to fetch all columns of all entities (A, B & C) however as of now i don't need any entity other than root entity (B), data for entities like A & C should be fetched later on as per fetch strategies on their mappings to B.
      I think there is no need to fetch all the data over the n/w traffic for this query, still hibernate is generating the query like this.



      Is there a way I can guide hibernate to only fetch columns for entity B and not for A & C using criteria API?



      select 
      b.all columns of B
      from B b
      inner join A a on b.prop=a.prop
      inner join C C on b.prop=c.prop
      where
      c.prop1c = <<some value>>
      a.prop1a = <<some value>>









      share|improve this question
















      I have written hibernate code with criteria API involving multiple entities (having parent-child relationships & I am using createAlias() to join entities) expecting to fetch only root entity in the result.



      However, when I debug the SQL generated by hibernate I observe that hibernate is fetching data (in select clause) for all the entities used in the join clause (achieved with createAlias()).



      I don't think there is any need to fetch the columns for other entity other than the expected root entity since it may not be efficient and I am looking to avoid this?



      I am using Hibernate version 3.6.9



      Here is the scenario. There are 3 entities A, B & C having parent child relationship in following manner A (super parent) -> B (parent) -> C (child)



      session.createCriteria(B.class, "b")
      .createAlias("b.c", "c",Criteria.INNER_JOIN)
      .createAlias("b.a", "a",Criteria.INNER_JOIN)
      .add(Restrictions.in("c.prop1c", <<some value>>))
      .add(Restrictions.in("a.prop1a", <<some value>>))
      criteria.list()

      now when hibernate generates the SQL it looks like
      select
      b.all columns of B
      a.all columns of A
      c.all columns of C
      from B b
      inner join A a on b.prop=a.prop
      inner join C C on b.prop=c.prop
      where
      c.prop1c = <<some value>>
      a.prop1a = <<some value>>


      if you observe hibernate has generate query to fetch all columns of all entities (A, B & C) however as of now i don't need any entity other than root entity (B), data for entities like A & C should be fetched later on as per fetch strategies on their mappings to B.
      I think there is no need to fetch all the data over the n/w traffic for this query, still hibernate is generating the query like this.



      Is there a way I can guide hibernate to only fetch columns for entity B and not for A & C using criteria API?



      select 
      b.all columns of B
      from B b
      inner join A a on b.prop=a.prop
      inner join C C on b.prop=c.prop
      where
      c.prop1c = <<some value>>
      a.prop1a = <<some value>>






      java hibernate join hibernate-criteria criteria-api






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 7 at 14:54









      Vlad Mihalcea

      58.7k13165471




      58.7k13165471










      asked Jan 1 at 8:12









      MayurMayur

      113111




      113111
























          1 Answer
          1






          active

          oldest

          votes


















          0














          The legacy Criteria is deprecated. You can use the JPA Criteria which supports this via the JOIN directive as opposed to FETCH.



          Or, use JPQL:



          select b
          from B b
          inner join b.a. a
          inner join b.a. c
          where
          c.prop1c = :prop1c
          a.prop1a = :prop1a





          share|improve this answer
























          • Hi Vlad thanks for replying to the query .my questions was more towards on how I can restrict the hibernate query all the alias in the criteria since I don't need them while querying . Are you suggesting that join directive will resolve this issue wherein it will generate a sql query only for root entity .

            – Mayur
            Jan 2 at 4:57











          • Also I will try HQL as an alternative to criteria however that defies the reason why I wanted to use criteria for dynamic querying .

            – Mayur
            Jan 2 at 4:58











          • You can also use Criteria projections to achieve the same goal.

            – Vlad Mihalcea
            Jan 2 at 6:09











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993982%2fhow-to-restrict-hibernate-to-generate-sql-query-with-columns-only-from-root-enti%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          0














          The legacy Criteria is deprecated. You can use the JPA Criteria which supports this via the JOIN directive as opposed to FETCH.



          Or, use JPQL:



          select b
          from B b
          inner join b.a. a
          inner join b.a. c
          where
          c.prop1c = :prop1c
          a.prop1a = :prop1a





          share|improve this answer
























          • Hi Vlad thanks for replying to the query .my questions was more towards on how I can restrict the hibernate query all the alias in the criteria since I don't need them while querying . Are you suggesting that join directive will resolve this issue wherein it will generate a sql query only for root entity .

            – Mayur
            Jan 2 at 4:57











          • Also I will try HQL as an alternative to criteria however that defies the reason why I wanted to use criteria for dynamic querying .

            – Mayur
            Jan 2 at 4:58











          • You can also use Criteria projections to achieve the same goal.

            – Vlad Mihalcea
            Jan 2 at 6:09
















          0














          The legacy Criteria is deprecated. You can use the JPA Criteria which supports this via the JOIN directive as opposed to FETCH.



          Or, use JPQL:



          select b
          from B b
          inner join b.a. a
          inner join b.a. c
          where
          c.prop1c = :prop1c
          a.prop1a = :prop1a





          share|improve this answer
























          • Hi Vlad thanks for replying to the query .my questions was more towards on how I can restrict the hibernate query all the alias in the criteria since I don't need them while querying . Are you suggesting that join directive will resolve this issue wherein it will generate a sql query only for root entity .

            – Mayur
            Jan 2 at 4:57











          • Also I will try HQL as an alternative to criteria however that defies the reason why I wanted to use criteria for dynamic querying .

            – Mayur
            Jan 2 at 4:58











          • You can also use Criteria projections to achieve the same goal.

            – Vlad Mihalcea
            Jan 2 at 6:09














          0












          0








          0







          The legacy Criteria is deprecated. You can use the JPA Criteria which supports this via the JOIN directive as opposed to FETCH.



          Or, use JPQL:



          select b
          from B b
          inner join b.a. a
          inner join b.a. c
          where
          c.prop1c = :prop1c
          a.prop1a = :prop1a





          share|improve this answer













          The legacy Criteria is deprecated. You can use the JPA Criteria which supports this via the JOIN directive as opposed to FETCH.



          Or, use JPQL:



          select b
          from B b
          inner join b.a. a
          inner join b.a. c
          where
          c.prop1c = :prop1c
          a.prop1a = :prop1a






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Jan 1 at 20:34









          Vlad MihalceaVlad Mihalcea

          58.7k13165471




          58.7k13165471













          • Hi Vlad thanks for replying to the query .my questions was more towards on how I can restrict the hibernate query all the alias in the criteria since I don't need them while querying . Are you suggesting that join directive will resolve this issue wherein it will generate a sql query only for root entity .

            – Mayur
            Jan 2 at 4:57











          • Also I will try HQL as an alternative to criteria however that defies the reason why I wanted to use criteria for dynamic querying .

            – Mayur
            Jan 2 at 4:58











          • You can also use Criteria projections to achieve the same goal.

            – Vlad Mihalcea
            Jan 2 at 6:09



















          • Hi Vlad thanks for replying to the query .my questions was more towards on how I can restrict the hibernate query all the alias in the criteria since I don't need them while querying . Are you suggesting that join directive will resolve this issue wherein it will generate a sql query only for root entity .

            – Mayur
            Jan 2 at 4:57











          • Also I will try HQL as an alternative to criteria however that defies the reason why I wanted to use criteria for dynamic querying .

            – Mayur
            Jan 2 at 4:58











          • You can also use Criteria projections to achieve the same goal.

            – Vlad Mihalcea
            Jan 2 at 6:09

















          Hi Vlad thanks for replying to the query .my questions was more towards on how I can restrict the hibernate query all the alias in the criteria since I don't need them while querying . Are you suggesting that join directive will resolve this issue wherein it will generate a sql query only for root entity .

          – Mayur
          Jan 2 at 4:57





          Hi Vlad thanks for replying to the query .my questions was more towards on how I can restrict the hibernate query all the alias in the criteria since I don't need them while querying . Are you suggesting that join directive will resolve this issue wherein it will generate a sql query only for root entity .

          – Mayur
          Jan 2 at 4:57













          Also I will try HQL as an alternative to criteria however that defies the reason why I wanted to use criteria for dynamic querying .

          – Mayur
          Jan 2 at 4:58





          Also I will try HQL as an alternative to criteria however that defies the reason why I wanted to use criteria for dynamic querying .

          – Mayur
          Jan 2 at 4:58













          You can also use Criteria projections to achieve the same goal.

          – Vlad Mihalcea
          Jan 2 at 6:09





          You can also use Criteria projections to achieve the same goal.

          – Vlad Mihalcea
          Jan 2 at 6:09




















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993982%2fhow-to-restrict-hibernate-to-generate-sql-query-with-columns-only-from-root-enti%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          'app-layout' is not a known element: how to share Component with different Modules

          android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

          WPF add header to Image with URL pettitions [duplicate]