어플리케이션 실행시 인텔리제이 콘솔에 찍히는 sql statement가 1줄로 나오는 문제!

Question

2020-06-23 17:24:25.174 DEBUG 5276 --- [nio-8080-exec-5] org.hibernate.SQL : select order0_.order_id as order_id1_6_0_, member1_.member_id as member_i1_4_1_, delivery2_.delivery_id as delivery1_2_2_, order0_.delivery_id as delivery4_6_0_, order0_.member_id as member_i5_6_0_, order0_.order_date as order_da2_6_0_, order0_.status as status3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zipcode as zipcode4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zipcode as zipcode4_2_2_, delivery2_.status as status5_2_2_ from orders order0_ inner join member member1_ on order0_.member_id=member1_.member_id inner join delivery delivery2_ on order0_.delivery_id=delivery2_.delivery_id limit ?

2020-06-23 17:24:25.176 INFO 5276 --- [nio-8080-exec-5] p6spy : #1592900665176 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/shop

select order0_.order_id as order_id1_6_0_, member1_.member_id as member_i1_4_1_, delivery2_.delivery_id as delivery1_2_2_, order0_.delivery_id as delivery4_6_0_, order0_.member_id as member_i5_6_0_, order0_.order_date as order_da2_6_0_, order0_.status as status3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zipcode as zipcode4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zipcode as zipcode4_2_2_, delivery2_.status as status5_2_2_ from orders order0_ inner join member member1_ on order0_.member_id=member1_.member_id inner join delivery delivery2_ on order0_.delivery_id=delivery2_.delivery_id limit ?

select order0_.order_id as order_id1_6_0_, member1_.member_id as member_i1_4_1_, delivery2_.delivery_id as delivery1_2_2_, order0_.delivery_id as delivery4_6_0_, order0_.member_id as member_i5_6_0_, order0_.order_date as order_da2_6_0_, order0_.status as status3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zipcode as zipcode4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zipcode as zipcode4_2_2_, delivery2_.status as status5_2_2_ from orders order0_ inner join member member1_ on order0_.member_id=member1_.member_id inner join delivery delivery2_ on order0_.delivery_id=delivery2_.delivery_id limit 100;

2020-06-23 17:24:25.177 DEBUG 5276 --- [nio-8080-exec-5] org.hibernate.SQL : select orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.count as count2_5_0_, orderitems0_.item_id as item_id4_5_0_, orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_price as order_pr3_5_0_ from order_item orderitems0_ where orderitems0_.order_id in (?, ?)

2020-06-23 17:24:25.178 INFO 5276 --- [nio-8080-exec-5] p6spy : #1592900665178 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/shop

select orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.count as count2_5_0_, orderitems0_.item_id as item_id4_5_0_, orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_price as order_pr3_5_0_ from order_item orderitems0_ where orderitems0_.order_id in (?, ?)

select orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.count as count2_5_0_, orderitems0_.item_id as item_id4_5_0_, orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_price as order_pr3_5_0_ from order_item orderitems0_ where orderitems0_.order_id in (4, 11);

2020-06-23 17:24:25.179 DEBUG 5276 --- [nio-8080-exec-5] org.hibernate.SQL : select item0_.item_id as item_id2_3_0_, item0_.name as name3_3_0_, item0_.price as price4_3_0_, item0_.stock_quantity as stock_qu5_3_0_, item0_.author as author6_3_0_, item0_.isbn as isbn7_3_0_, item0_.actor as actor8_3_0_, item0_.director as director9_3_0_, item0_.artist as artist10_3_0_, item0_.etc as etc11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id in (?, ?, ?, ?)

2020-06-23 17:24:25.186 INFO 5276 --- [nio-8080-exec-5] p6spy : #1592900665186 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/shop

select item0_.item_id as item_id2_3_0_, item0_.name as name3_3_0_, item0_.price as price4_3_0_, item0_.stock_quantity as stock_qu5_3_0_, item0_.author as author6_3_0_, item0_.isbn as isbn7_3_0_, item0_.actor as actor8_3_0_, item0_.director as director9_3_0_, item0_.artist as artist10_3_0_, item0_.etc as etc11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id in (?, ?, ?, ?)

select item0_.item_id as item_id2_3_0_, item0_.name as name3_3_0_, item0_.price as price4_3_0_, item0_.stock_quantity as stock_qu5_3_0_, item0_.author as author6_3_0_, item0_.isbn as isbn7_3_0_, item0_.actor as actor8_3_0_, item0_.director as director9_3_0_, item0_.artist as artist10_3_0_, item0_.etc as etc11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id in (2, 3, 9, 10);

위와 같이 콘솔에 찍히는 sql 문이 정열되지 않게 보입니다.

윈도우에 설치된 인텔리제이는 무료버전을 사용하고 있고

설정된 application.yml은 아래와 같습니다.

spring:
  datasource:
    url: jdbc:h2:tcp://localhost/~/shop
    username: sa
    password:
    driver-class-name: org.h2.Driver

  jpa:
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
#        show-sql: true
        format-sql: true
        default_batch_fetch_size: 100



logging:
  level:
    org.hibernate.SQL: debug
#    org.hibernate.type: trace

콘솔에 sql문이 정렬되게 하는 방법은 무엇인가요?

Answer

감사합니다. 잘 작동합니다.^^

Answer

datasource 가 1개인 경우 yml 파일에만 지정하면 format_sql 도 잘되고 default_batch_fetch_size 도 잘 되는데요. 멀티 datasource 를 사용중이라 JPAConfig 클래스를 만들어서 사용하면 default_batch_fetch_size 프로퍼티는 아래 properties 에 값을 넣으면 되는데요. format_sql 은 안먹네요 ????

-> yml 파일에 등록해도 안되서 default_batch_size 는 아래 속성값에 추가하니까 되었는데요. format_sql 은 안되네요..

package kr.co.korbit.demo;


:::

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "jpaEntityManagerFactory",
        transactionManagerRef = "jpaTransactionManager",
        basePackages = {"kr.co.korbit.demo.jpa.repository", "kr.co.korbit.demo.jpa.repository.custom", "kr.co.korbit.demo.jpa.repository.impl", "kr.co.korbit.demo.jpa.repository.querydsl"})
public class JpaConfig {

    final String  dialect = "org.hibernate.dialect.H2Dialect" ;
    String ddlAuto = "create" ;
    String showSql = "false" ;
    String formatSql = "true" ;
    String useNewIdGeneratorMappings = "false" ;
    String implicitStrategy = "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy" ;
    String physicalStrategy = "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy" ;

    @Autowired(required = false)
    private PersistenceUnitManager persistenceUnitManager;

    @Bean(name = "jpaDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.demo")
    @Primary
    public DataSource jpaDataSource() {

        HikariDataSource dataSource =  DataSourceBuilder.create().type(HikariDataSource.class).build() ;
        return dataSource ;
    }


    @Bean(name = "jpaEntityManagerFactoryBuilder")
    @Primary
    public EntityManagerFactoryBuilder jpaEntityManagerFactoryBuilder(){
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setShowSql(true);
        adapter.setPrepareConnection(true);
        adapter.setDatabase(Database.H2);
        adapter.setDatabasePlatform(dialect);
        adapter.setGenerateDdl(false);

        HashMap<String, String> properties = new HashMap<String, String>() ;
        properties.put("hibernate.ddl-auto", ddlAuto);
        properties.put("format_sql", "true");
        properties.put("default_batch_size", "300");
        properties.put("hibernate.naming.implicit-strategy", implicitStrategy);
        properties.put("hibernate.naming.physical-strategy", physicalStrategy);
        properties.put("hibernate.use-new-id-generator-mappings", useNewIdGeneratorMappings);

        EntityManagerFactoryBuilder builder = new EntityManagerFactoryBuilder(
                adapter, properties , this.persistenceUnitManager);
        return builder;
    }

    @Primary
    @Bean(name = "jpaEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean jpaEntityManagerFactory(
            @Qualifier("jpaEntityManagerFactoryBuilder") EntityManagerFactoryBuilder builder,
            @Qualifier("jpaDataSource") DataSource jpaDataSource) {
        LocalContainerEntityManagerFactoryBean factory = builder
                .dataSource(jpaDataSource)
                .packages("kr.co.korbit.demo.jpa.model")
                .persistenceUnit("demo")
                .build();

        factory.setPackagesToScan("kr.co.korbit.demo.jpa.model") ;
        return factory ;
    }


    @Bean(name = "jpaTransactionManager")
    @Primary
    public PlatformTransactionManager jpaTransactionManager(
            @Qualifier("jpaEntityManagerFactory") EntityManagerFactory jpaEntityManagerFactory) {
        return new JpaTransactionManager(jpaEntityManagerFactory);
    }

    @Bean(name = "jpaQueryFactory")
    @Primary
    public JPAQueryFactory jpaQueryFactory(@Qualifier("jpaEntityManagerFactory") EntityManagerFactory jpaEntityManagerFactory) {

        return new JPAQueryFactory(jpaEntityManagerFactory.createEntityManager()) ;
    }
}

Answer

안녕하세요. 개발자님^^ format-sql -> format_sql 로 변경하시면 됩니다^^

개발자

어플리케이션 실행시 인텔리제이 콘솔에 찍히는 sql statement가 1줄로 나오는 문제!

이 글과 비슷한 Q&A

react 와 같은 별개의 프론트가 있는 경우 csrf

"MVC 패턴 - 적용" 강의에서 질문 있습니다.

Spring Boot에서 jsp 연동 오류

드롭다운 에러 Uncaught TypeError: i.createPopper is not a function