- 쿼리 메소드를 이용할 때 가장 많이 사용하는 문법 : find
find + (엔티티 이름) + By + 변수
결과)
Hibernate:
select
item0_.item_id as item_id1_0_,
item0_.item_detail as item_det2_0_,
item0_.item_nm as item_nm3_0_,
item0_.item_sell_status as item_sel4_0_,
item0_.price as price5_0_,
item0_.reg_time as reg_time6_0_,
item0_.stock_number as stock_nu7_0_,
item0_.update_time as update_t8_0_
from
item item0_
where
item0_.item_nm=?
2022-01-18 20:06:21.021 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [테스트 상품1]
2022-01-18 20:06:21.022 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([item_id1_0_] : [BIGINT]) - [2]
2022-01-18 20:06:21.024 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([item_det2_0_] : [CLOB]) - [테스트 상품 상세 설명1]
2022-01-18 20:06:21.025 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([item_nm3_0_] : [VARCHAR]) - [테스트 상품1]
2022-01-18 20:06:21.025 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([item_sel4_0_] : [VARCHAR]) - [SELL]
2022-01-18 20:06:21.025 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([price5_0_] : [INTEGER]) - [1000]
2022-01-18 20:06:21.026 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([reg_time6_0_] : [TIMESTAMP]) - [2022-01-18T20:06:20.928165]
2022-01-18 20:06:21.026 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([stock_nu7_0_] : [INTEGER]) - [100]
2022-01-18 20:06:21.026 TRACE 22240 --- [ main] o.h.type.descriptor.sql.BasicExtractor : extracted value ([update_t8_0_] : [TIMESTAMP]) - [2022-01-18T20:06:20.928165]
Item(id=2, itemNm=테스트 상품1, price=1000, stockNumber=100, itemDetail=테스트 상품 상세 설명1, itemSellStatus=SELL, regTime=2022-01-18T20:06:20.928165, updateTime=2022-01-18T20:06:20.928165)
2022-01-18 20:06:21.047 INFO 22240 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-01-18 20:06:21.048 INFO 22240 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2022-01-18 20:06:21.050 INFO 22240 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
Disconnected from the target VM, address: '127.0.0.1:61817', transport: 'socket'
Process finished with exit code 0
JPQL snippet
Spring Data JPA - Reference Documentation
Spring Data JPA - Reference Documentation
Example 109. Using @Transactional at query methods @Transactional(readOnly = true) interface UserRepository extends JpaRepository { List findByLastname(String lastname); @Modifying @Transactional @Query("delete from User u where u.active = false") void del
docs.spring.io
Table 3. Supported keywords inside method namesKeywordSampleJPQL snippet
| Distinct | findDistinctByLastnameAndFirstname | select distinct … where x.lastname = ?1 and x.firstname = ?2 |
| And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
| Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
| Is, Equals | findByFirstname,findByFirstnameIs,findByFirstnameEquals | … where x.firstname = ?1 |
| Between | findByStartDateBetween | … where x.startDate between ?1 and ?2 |
| LessThan | findByAgeLessThan | … where x.age < ?1 |
| LessThanEqual | findByAgeLessThanEqual | … where x.age <= ?1 |
| GreaterThan | findByAgeGreaterThan | … where x.age > ?1 |
| GreaterThanEqual | findByAgeGreaterThanEqual | … where x.age >= ?1 |
| After | findByStartDateAfter | … where x.startDate > ?1 |
| Before | findByStartDateBefore | … where x.startDate < ?1 |
| IsNull, Null | findByAge(Is)Null | … where x.age is null |
| IsNotNull, NotNull | findByAge(Is)NotNull | … where x.age not null |
| Like | findByFirstnameLike | … where x.firstname like ?1 |
| NotLike | findByFirstnameNotLike | … where x.firstname not like ?1 |
| StartingWith | findByFirstnameStartingWith | … where x.firstname like ?1 (parameter bound with appended %) |
| EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1 (parameter bound with prepended %) |
| Containing | findByFirstnameContaining | … where x.firstname like ?1 (parameter bound wrapped in %) |
| OrderBy | findByAgeOrderByLastnameDesc | … where x.age = ?1 order by x.lastname desc |
| Not | findByLastnameNot | … where x.lastname <> ?1 |
| In | findByAgeIn(Collection<Age> ages) | … where x.age in ?1 |
| NotIn | findByAgeNotIn(Collection<Age> ages) | … where x.age not in ?1 |
| True | findByActiveTrue() | … where x.active = true |
| False | findByActiveFalse() | … where x.active = false |
| IgnoreCase | findByFirstnameIgnoreCase | … where UPPER(x.firstname) = UPPER(?1) |
@Query
@Query 어노테이션을 이용하면 SQL과 유사한 JPQL 이라는 객체지향 쿼리 언어를 통해 복잡한 쿼리도 처리가 가능하다.
* 테이블이 아닌 객체를 대상으로 검색하는 객체지향 쿼리
* JPQL은 SQL을 추상화해서 사용하기 때문에 특정 데이터베이스 SQL에 의존하지 않는다.
public interface ItemRepository extends JpaRepository<Item, Long> {
List<Item> findByItemNm(String itemNm);
@Query("select i from Item i where i.itemDetail like %:itemDetail% order by i.price desc")
List<Item> findByItemDetail(@Param("itemDetail") String itemDetail);
}package com.shop.repository;
import com.shop.constant.ItemSellStatus;
import com.shop.entity.Item;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;
import java.time.LocalDateTime;
import java.util.List;
@SpringBootTest
@TestPropertySource(locations = "classpath:application-test.properties")
public class ItemRepositoryTest {
@Autowired
ItemRepository itemRepository;
// @Test
// @DisplayName("상품 저장 테스트")
public void createItemTest(){
for(int i=0; i<=10; i++) {
Item item = new Item();
item.setItemNm("테스트 상품"+i);
item.setPrice(10000+i);
item.setItemDetail("테스트 상품 상세 설명"+i);
item.setItemSellStatus(ItemSellStatus.SELL);
item.setStockNumber(100);
item.setRegTime(LocalDateTime.now());
item.setUpdateTime(LocalDateTime.now());
Item saveItem = itemRepository.save(item);
}
}
@Test
@DisplayName("상품명 조회 테스트")
public void findByItemNmTest(){
this.createItemTest();
List<Item> itemList = itemRepository.findByItemNm("테스트 상품1");
for(Item item : itemList){
System.out.println(item.toString());
}
}
@Test
@DisplayName("@Query Product Select Test")
public void findByItemDetailTest(){
this.createItemTest();
List<Item> itemList = itemRepository.findByItemDetail("테스트 상품 상세 설명");
for(Item item : itemList){
System.out.println(item.toString());
}
}
}
findByItemDetailTest 결과)
Item(id=11, itemNm=테스트 상품10, price=10010, stockNumber=100, itemDetail=테스트 상품 상세 설명10, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.918605, updateTime=2022-01-20T17:36:13.918605)
Item(id=10, itemNm=테스트 상품9, price=10009, stockNumber=100, itemDetail=테스트 상품 상세 설명9, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.917605, updateTime=2022-01-20T17:36:13.917605)
Item(id=9, itemNm=테스트 상품8, price=10008, stockNumber=100, itemDetail=테스트 상품 상세 설명8, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.916604, updateTime=2022-01-20T17:36:13.916604)
Item(id=8, itemNm=테스트 상품7, price=10007, stockNumber=100, itemDetail=테스트 상품 상세 설명7, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.915603, updateTime=2022-01-20T17:36:13.915603)
Item(id=7, itemNm=테스트 상품6, price=10006, stockNumber=100, itemDetail=테스트 상품 상세 설명6, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.914602, updateTime=2022-01-20T17:36:13.914602)
Item(id=6, itemNm=테스트 상품5, price=10005, stockNumber=100, itemDetail=테스트 상품 상세 설명5, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.913601, updateTime=2022-01-20T17:36:13.913601)
Item(id=5, itemNm=테스트 상품4, price=10004, stockNumber=100, itemDetail=테스트 상품 상세 설명4, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.911599, updateTime=2022-01-20T17:36:13.911599)
Item(id=4, itemNm=테스트 상품3, price=10003, stockNumber=100, itemDetail=테스트 상품 상세 설명3, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.909597, updateTime=2022-01-20T17:36:13.909597)
Item(id=3, itemNm=테스트 상품2, price=10002, stockNumber=100, itemDetail=테스트 상품 상세 설명2, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.908596, updateTime=2022-01-20T17:36:13.908596)
Item(id=2, itemNm=테스트 상품1, price=10001, stockNumber=100, itemDetail=테스트 상품 상세 설명1, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.906594, updateTime=2022-01-20T17:36:13.906594)
Item(id=1, itemNm=테스트 상품0, price=10000, stockNumber=100, itemDetail=테스트 상품 상세 설명0, itemSellStatus=SELL, regTime=2022-01-20T17:36:13.861551, updateTime=2022-01-20T17:36:13.861551)
nativeQuery 옵션을 주어서 특정 데이터베이스 종속될수있게 처리하는방법
@Query(value = "select * from Item i where i.itemDetail like %:itemDetail% order by i.price desc", nativeQuery = true)
List<Item> findByItemDetailByNative(@Param("itemDetail") String itemDetail);@Test
@DisplayName("@Query Product Select Test - Native")
public void findByItemDetailByNativeTest(){
this.createItemTest();
List<Item> itemList = itemRepository.findByItemDetailByNative("테스트 상품 상세 설명");
for(Item item : itemList){
System.out.println(item.toString());
}
}
findByItemDetailNative 에러)
Hibernate:
select
*
from
Item i
where
i.itemDetail like ?
order by
i.price desc
2022-01-20 20:57:45.939 WARN 24084 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42122, SQLState: 42S22
2022-01-20 20:57:45.939 ERROR 24084 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Column "I.ITEMDETAIL" not found; SQL statement:
select * from Item i where i.itemDetail like ? order by i.price desc [42122-200]
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select * from Item i where i.itemDetail like ? order by i.price desc]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
at jdk.proxy2/jdk.proxy2.$Proxy113.findByItemDetailByNative(Unknown Source)
at com.shop.repository.ItemRepositoryTest.findByItemDetailByNativeTest(ItemRepositoryTest.java:61)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2122)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2059)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
at org.hibernate.loader.Loader.doQuery(Loader.java:956)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
at org.hibernate.loader.Loader.doList(Loader.java:2868)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
at org.hibernate.loader.Loader.list(Loader.java:2677)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2181)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1204)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
at org.hibernate.query.Query.getResultList(Query.java:165)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:406)
at jdk.proxy2/jdk.proxy2.$Proxy130.getResultList(Unknown Source)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:128)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
... 77 more
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "I.ITEMDETAIL" not found; SQL statement:
select * from Item i where i.itemDetail like ? order by i.price desc [42122-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:163)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:145)
at org.h2.expression.condition.CompareLike.optimize(CompareLike.java:102)
at org.h2.command.dml.Select.prepare(Select.java:1209)
at org.h2.command.Parser.prepareCommand(Parser.java:744)
at org.h2.engine.Session.prepareLocal(Session.java:657)
at org.h2.engine.Session.prepareCommand(Session.java:595)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:352)
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
... 115 more
이유 : SQL 문법으로 바꿔준다.
참고 사이트 : [JPA] @Query, 직접 쿼리 작성 (tistory.com)
해결 :
@Query(value = "select * from Item i where i.item_detail like CONCAT('%',:itemDetail,'%') order by i.price desc", nativeQuery = true)
List<Item> findByItemDetailByNative(@Param("itemDetail") String itemDetail);Item(id=11, itemNm=테스트 상품10, price=10010, stockNumber=100, itemDetail=테스트 상품 상세 설명10, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.452036, updateTime=2022-01-20T20:53:42.452036)
Item(id=10, itemNm=테스트 상품9, price=10009, stockNumber=100, itemDetail=테스트 상품 상세 설명9, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.451035, updateTime=2022-01-20T20:53:42.451035)
Item(id=9, itemNm=테스트 상품8, price=10008, stockNumber=100, itemDetail=테스트 상품 상세 설명8, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.450033, updateTime=2022-01-20T20:53:42.450033)
Item(id=8, itemNm=테스트 상품7, price=10007, stockNumber=100, itemDetail=테스트 상품 상세 설명7, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.449033, updateTime=2022-01-20T20:53:42.449033)
Item(id=7, itemNm=테스트 상품6, price=10006, stockNumber=100, itemDetail=테스트 상품 상세 설명6, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.448031, updateTime=2022-01-20T20:53:42.448031)
Item(id=6, itemNm=테스트 상품5, price=10005, stockNumber=100, itemDetail=테스트 상품 상세 설명5, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.447031, updateTime=2022-01-20T20:53:42.447031)
Item(id=5, itemNm=테스트 상품4, price=10004, stockNumber=100, itemDetail=테스트 상품 상세 설명4, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.445029, updateTime=2022-01-20T20:53:42.445029)
Item(id=4, itemNm=테스트 상품3, price=10003, stockNumber=100, itemDetail=테스트 상품 상세 설명3, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.443027, updateTime=2022-01-20T20:53:42.443027)
Item(id=3, itemNm=테스트 상품2, price=10002, stockNumber=100, itemDetail=테스트 상품 상세 설명2, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.442026, updateTime=2022-01-20T20:53:42.442026)
Item(id=2, itemNm=테스트 상품1, price=10001, stockNumber=100, itemDetail=테스트 상품 상세 설명1, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.440024, updateTime=2022-01-20T20:53:42.440024)
Item(id=1, itemNm=테스트 상품0, price=10000, stockNumber=100, itemDetail=테스트 상품 상세 설명0, itemSellStatus=SELL, regTime=2022-01-20T20:53:42.377302, updateTime=2022-01-20T20:53:42.377302)
call next value for hibernate_sequence 에러는 아래 참고
[Spring JPA] data.sql 동작방식 변경, hibernate_sequence not found 해결법 (tistory.com)
스프링 부트 2.5 업데이트 : hibernate, data.sql 관련 변동사항 (velog.io)
공식 : Spring Boot 2.5 Release Notes · spring-projects/spring-boot Wiki (github.com)
반응형
'2022 > JPA입문(完)' 카테고리의 다른 글
| 스프링 시큐리티 - 1 (0) | 2022.01.23 |
|---|---|
| Thymeleaf - 1 (0) | 2022.01.21 |
| Spring Data JPA - 2 (0) | 2022.01.20 |
| JPA - 1 (0) | 2022.01.10 |
| 개발환경셋팅 (0) | 2022.01.10 |
