Skip to content

executedSqlViaConsole

Jump to bottom
mybloom edited this page May 17, 2022 · 1 revision

콘솔을 통해 실행 SQL 확인하기

콘솔에서 확인할 수 있는 쿼리

  • HQL AST : [QUERY] Node: 'query'
  • Tables referenced from query nodes(쿼리노드로 참조된 테이블) : QueryNode
  • SQL AST (Abstract Syntax Trees)
  • HQL
  • SQL

SidedishRepository.findAllBySubCategoryId(categoryId) 쿼리 확인

fetch join 사용

메서드 코드

public List<Sidedish> findAllBySubCategoryId(Long categoryId) {
        String jpql = "select s "
        + "from Sidedish s "
        + "join fetch s.sidedishCategories sc "
        + "where sc.categoryId = :categoryId";

		return entityManager.createQuery(jpql, Sidedish.class)
			.setParameter("categoryId" , categoryId)
			.getResultList();
	}

1 HQL AST

 \-[QUERY] Node: 'query'
    +-[SELECT_FROM] Node: 'SELECT_FROM'
    |  +-[FROM] Node: 'from'
    |  |  +-[RANGE] Node: 'RANGE'
    |  |  |  +-[DOT] Node: '.'
    |  |  |  |  +-[DOT] Node: '.'
    |  |  |  |  |  +-[DOT] Node: '.'
    |  |  |  |  |  |  +-[DOT] Node: '.'
    |  |  |  |  |  |  |  +-[IDENT] Node: 'com'
    |  |  |  |  |  |  |  \-[IDENT] Node: 'project'
    |  |  |  |  |  |  \-[IDENT] Node: 'sidedish_jpa'
    |  |  |  |  |  \-[IDENT] Node: 'sidedish'
    |  |  |  |  \-[IDENT] Node: 'Sidedish'
    |  |  |  \-[ALIAS] Node: 's'
    |  |  \-[JOIN] Node: 'join'
    |  |     +-[FETCH] Node: 'fetch'
    |  |     +-[DOT] Node: '.'
    |  |     |  +-[IDENT] Node: 's'
    |  |     |  \-[IDENT] Node: 'sidedishCategories'
    |  |     \-[ALIAS] Node: 'sc'
    |  \-[SELECT] Node: 'select'
    |     \-[IDENT] Node: 's'
    \-[WHERE] Node: 'where'
       \-[EQ] Node: '='
          +-[DOT] Node: '.'
          |  +-[IDENT] Node: 'sc'
          |  \-[IDENT] Node: 'categoryId'
          \-[COLON] Node: ':'
             \-[IDENT] Node: 'categoryId'
  • join tree
12:22:54.661 [DEBUG] [Test worker] [o.h.hql.internal.ast.HqlSqlWalker] - createFromJoinElement() : -- join tree --
 \-[JOIN_FRAGMENT] FromElement: 'sidedish_category sidedishca1_' FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=sc,role=com.project.sidedish_jpa.sidedish.Sidedish.sidedishCategories,tableName=sidedish_category,tableAlias=sidedishca1_,origin=sidedish sidedish0_,columns={sidedish0_.sidedish_id,className=com.project.sidedish_jpa.sidedish.SidedishCategory}}
  • processQuery() :
SELECT ( {select clause} sidedish0_.sidedish_id ) 
( fromclause{level=1} (sidedish sidedish0_ sidedish_category sidedishca1_ ) ) 
( WHERE (               = ( sidedishca1_.category_id sidedishca1_.sidedish_category_id categoryid ) ? ) )
  • Tables referenced from query nodes :
 \-QueryNode
    +-SelectClause
    | referencedTables(entity Sidedish): [sidedish]
    | referencedTables(entity SidedishCategory): [sidedish_category]
    |  +-IdentNode
    |  | persister: SingleTableEntityPersister(com.project.sidedish_jpa.sidedish.Sidedish)
    |  | originalText: s
    |  +-SelectExpressionImpl
    |  | persister: SingleTableEntityPersister(com.project.sidedish_jpa.sidedish.SidedishCategory)
    |  +-SqlFragment
    |  +-SqlFragment
    |  \-SqlFragment
    +-FromClause
    |  \-FromElement
    |     \-FromElement
    \-SqlNode
       \-BinaryLogicOperatorNode
          +-DotNode
          | persister: SingleTableEntityPersister(com.project.sidedish_jpa.sidedish.SidedishCategory)
          | path: sc.categoryId
          |  +-IdentNode
          |  | persister: SingleTableEntityPersister(com.project.sidedish_jpa.sidedish.SidedishCategory)
          |  | originalText: sc
          |  \-IdentNode
          |    persister: null
          |    originalText: categoryId
          \-ParameterNode

SQL AST

  • 여기에서는 not a fetch join 으로 확인
  • 확인 : 짐작해 보건데 실제 작동 쿼리이기 때문일 것 같다.
 \-[SELECT] QueryNode: 'SELECT'  querySpaces (sidedish_category,sidedish)
    +-[SELECT_CLAUSE] SelectClause: '{select clause}'
    |  +-[ALIAS_REF] IdentNode: 'sidedish0_.sidedish_id as sidedish1_1_0_' {alias=s, className=com.project.sidedish_jpa.sidedish.Sidedish, tableAlias=sidedish0_}
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'sidedishca1_.sidedish_category_id as sidedish1_2_1_' {FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=sc,role=com.project.sidedish_jpa.sidedish.Sidedish.sidedishCategories,tableName=sidedish_category,tableAlias=sidedishca1_,origin=sidedish sidedish0_,columns={sidedish0_.sidedish_id,className=com.project.sidedish_jpa.sidedish.SidedishCategory}}}
    |  +-[SQL_TOKEN] SqlFragment: 'sidedish0_.category_id as category2_1_0_, sidedish0_.description as descript3_1_0_, sidedish0_.name as name4_1_0_, sidedish0_.price as price5_1_0_, sidedish0_.stock_quantity as stock_qu6_1_0_'
    |  +-[SQL_TOKEN] SqlFragment: 'sidedishca1_.created_by as created_2_2_1_, sidedishca1_.created_date as created_3_2_1_, sidedishca1_.modified_by as modified4_2_1_, sidedishca1_.modified_date as modified5_2_1_, sidedishca1_.category_id as category6_2_1_, sidedishca1_.sidedish_id as sidedish7_2_1_'
    |  \-[SQL_TOKEN] SqlFragment: 'sidedishca1_.sidedish_id as sidedish7_2_0__, sidedishca1_.sidedish_category_id as sidedish1_2_0__'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[sc, s], fromElementByTableAlias=[sidedish0_, sidedishca1_], fromElementsByPath=[s.sidedishCategories], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'sidedish sidedish0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=s,role=null,tableName=sidedish,tableAlias=sidedish0_,origin=null,columns={,className=com.project.sidedish_jpa.sidedish.Sidedish}}
    |     \-[JOIN_FRAGMENT] FromElement: 'inner join sidedish_category sidedishca1_ on sidedish0_.sidedish_id=sidedishca1_.sidedish_id' FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=sc,role=com.project.sidedish_jpa.sidedish.Sidedish.sidedishCategories,tableName=sidedish_category,tableAlias=sidedishca1_,origin=sidedish sidedish0_,columns={sidedish0_.sidedish_id,className=com.project.sidedish_jpa.sidedish.SidedishCategory}}
    \-[WHERE] SqlNode: 'where'
Ident       \-[EQ] BinaryLogicOperatorNode: '='
          +-[DOT] DotNode: 'sidedishca1_.category_id' {propertyName=categoryId,dereferenceType=PRIMITIVE,getPropertyPath=categoryId,path=sc.categoryId,tableAlias=sidedishca1_,className=com.project.sidedish_jpa.sidedish.SidedishCategory,classAlias=sc}
          |  +-[ALIAS_REF] IdentNode: 'sidedishca1_.sidedish_category_id' {alias=sc, className=com.project.sidedish_jpa.sidedish.SidedishCategory, tableAlias=sidedishca1_}
          |  \-[IDENT] IdentNode: 'categoryId' {originalText=categoryId}
          \-[NAMED_PARAM] ParameterNode: '?' {name=categoryId, expectedType=org.hibernate.type.LongType@401317a0}

SQL

SELECT s
FROM   com.project.sidedish_jpa.sidedish.sidedish s
JOIN FETCH s.sidedishcategories sc
WHERE sc.categoryid = :categoryId

HSQL

SELECT sidedish0_.sidedish_id            AS sidedish1_1_0_,
       sidedishca1_.sidedish_category_id AS sidedish1_2_1_,
       sidedish0_.category_id            AS category2_1_0_,
       sidedish0_.description            AS descript3_1_0_,
       sidedish0_.NAME                   AS name4_1_0_,
       sidedish0_.price                  AS price5_1_0_,
       sidedish0_.stock_quantity         AS stock_qu6_1_0_,
       sidedishca1_.created_by           AS created_2_2_1_,
       sidedishca1_.created_date         AS created_3_2_1_,
       sidedishca1_.modified_by          AS modified4_2_1_,
       sidedishca1_.modified_date        AS modified5_2_1_,
       sidedishca1_.category_id          AS category6_2_1_,
       sidedishca1_.sidedish_id          AS sidedish7_2_1_,
       sidedishca1_.sidedish_id          AS sidedish7_2_0__,
       sidedishca1_.sidedish_category_id AS sidedish1_2_0__
FROM   sidedish sidedish0_
       INNER JOIN sidedish_category sidedishca1_
               ON sidedish0_.sidedish_id = sidedishca1_.sidedish_id
WHERE  sidedishca1_.category_id = ?

join 사용

HQL AST

  • not a fetch join 확인

  • join tree

11:50:59.124 [DEBUG] [Test worker] [o.h.hql.internal.ast.HqlSqlWalker] - createFromJoinElement() : -- join tree --
\-[JOIN_FRAGMENT] FromElement: 'sidedish_category sidedishca1_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=sc,role=com.project.sidedish_jpa.sidedish.Sidedish.sidedishCategories,tableName=sidedish_category,tableAlias=sidedishca1_,origin=sidedish sidedish0_,columns={sidedish0_.sidedish_id,className=com.project.sidedish_jpa.sidedish.SidedishCategory}}

SQL AST

11:50:59.150 [DEBUG] [Test worker] [o.h.h.i.ast.QueryTranslatorImpl] - --- SQL AST ---
 \-[SELECT] QueryNode: 'SELECT'  querySpaces (sidedish_category,sidedish)
    +-[SELECT_CLAUSE] SelectClause: '{select clause}'
    |  +-[ALIAS_REF] IdentNode: 'sidedish0_.sidedish_id as sidedish1_1_' {alias=s, className=com.project.sidedish_jpa.sidedish.Sidedish, tableAlias=sidedish0_}
    |  \-[SQL_TOKEN] SqlFragment: 'sidedish0_.category_id as category2_1_, sidedish0_.description as descript3_1_, sidedish0_.name as name4_1_, sidedish0_.price as price5_1_, sidedish0_.stock_quantity as stock_qu6_1_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[sc, s], fromElementByTableAlias=[sidedish0_, sidedishca1_], fromElementsByPath=[s.sidedishCategories], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'sidedish sidedish0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=s,role=null,tableName=sidedish,tableAlias=sidedish0_,origin=null,columns={,className=com.project.sidedish_jpa.sidedish.Sidedish}}
    |     \-[JOIN_FRAGMENT] FromElement: 'inner join sidedish_category sidedishca1_ on sidedish0_.sidedish_id=sidedishca1_.sidedish_id' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=sc,role=com.project.sidedish_jpa.sidedish.Sidedish.sidedishCategories,tableName=sidedish_category,tableAlias=sidedishca1_,origin=sidedish sidedish0_,columns={sidedish0_.sidedish_id,className=com.project.sidedish_jpa.sidedish.SidedishCategory}}
    \-[WHERE] SqlNode: 'where'
       \-[EQ] BinaryLogicOperatorNode: '='
          +-[DOT] DotNode: 'sidedishca1_.category_id' {propertyName=categoryId,dereferenceType=PRIMITIVE,getPropertyPath=categoryId,path=sc.categoryId,tableAlias=sidedishca1_,className=com.project.sidedish_jpa.sidedish.SidedishCategory,classAlias=sc}
          |  +-[ALIAS_REF] IdentNode: 'sidedishca1_.sidedish_category_id' {alias=sc, className=com.project.sidedish_jpa.sidedish.SidedishCategory, tableAlias=sidedishca1_}
          |  \-[IDENT] IdentNode: 'categoryId' {originalText=categoryId}
          \-[NAMED_PARAM] ParameterNode: '?' {name=categoryId, expectedType=org.hibernate.type.LongType@401317a0}

HQL

SELECT s
FROM   com.project.sidedish_jpa.sidedish.sidedish s
JOIN   s.sidedishcategories sc
where  sc.categoryid = :categoryId

SQL

SELECT sidedish0_.sidedish_id    AS sidedish1_1_,
       sidedish0_.category_id    AS category2_1_,
       sidedish0_.description    AS descript3_1_,
       sidedish0_.NAME           AS name4_1_,
       sidedish0_.price          AS price5_1_,
       sidedish0_.stock_quantity AS stock_qu6_1_
FROM   sidedish sidedish0_
       INNER JOIN sidedish_category sidedishca1_
        ON sidedish0_.sidedish_id = sidedishca1_.sidedish_id
WHERE  sidedishca1_.category_id = ?

연관관계 컬렉션 로딩 시 발생 쿼리 : Loading collection: [com.project.sidedish_jpa.sidedish.Sidedish.sidedishCategories#1]

  • [org.hibernate.SQL]
    select
        sidedishca0_.sidedish_id as sidedish7_2_0_,
        sidedishca0_.sidedish_category_id as sidedish1_2_0_,
        sidedishca0_.sidedish_category_id as sidedish1_2_1_,
        sidedishca0_.created_by as created_2_2_1_,
        sidedishca0_.created_date as created_3_2_1_,
        sidedishca0_.modified_by as modified4_2_1_,
        sidedishca0_.modified_date as modified5_2_1_,
        sidedishca0_.category_id as category6_2_1_,
        sidedishca0_.sidedish_id as sidedish7_2_1_ 
    from
        sidedish_category sidedishca0_ 
    where
        sidedishca0_.sidedish_id=?

궁금한 키워드

  • AST : Abstract Syntax Trees
  • Query Node
  • AST 에서 나오는 단어들
    • SingleTableEntityPersister 등

궁금한 키워드에 대해 찾아본 사이트

Clone this wiki locally