스프링 에러 : TooManyResultsException

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy18.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93)
at com.sun.proxy.$Proxy23.cartList(Unknown Source)
at com.sjb.controller.CartMapperTest.cartList(CartMapperTest.java:38)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:73)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:83)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)
Caused by: org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:80)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
... 37 more

 

상황 : 장바구니 리스트를 가져오는 쿼리문 테스트중 이와같은 에러발생

1. xml

<select id="cartList" resultType="com.sjb.model.CartListVO">
        select @rownum:=@rownum+1 as rownum, 
        sjb_cart.productId, memberId, cartStock, addDate , title, 
            authorID, (select authorName from sjb_author where sjb_book.authorID=sjb_author.authorID) as authorName, 
            publisher, cateCode, (select cateName from sjb_bcate where sjb_book.cateCode=sjb_bcate.cateCode) as cateName,
            bookCover, publeYear, likeStar, bookReply, bookPrice, discountRate, bookInfo, contents,
            introImage,regdate,boardRegdate,modDate,bookStock
        from (select @rownum:=0) as rownum, sjb_cart 
        join sjb_book
        on sjb_cart.productId = sjb_book.productID
        where sjb_cart.productId = #{productId};      
</select>

 

2. mapper.java

 

package com.sjb.mapper;
 
import java.util.List;
 
import com.sjb.model.CartVO;
import com.sjb.model.CartListVO;
 
public interface CartMapper {
 
 
    
    public void cartList(int productId) throws Exception;
    
    
}
 

 

3. test문

 

package com.sjb.controller;
 
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
 
import com.sjb.mapper.CartMapper;
import com.sjb.model.CartListVO;
import com.sjb.model.CartVO;
 
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
public class CartMapperTest {
    
    @Autowired
        private CartMapper cartmapper;
 
    @Test
    public void cartList() throws Exception{
        //CartListVO cart = new CartListVO();
        
        
        cartmapper.cartList(4);
        
        
        
        
    }
    
    
    
 
}
 

 

 

 

원인 : 쿼리문의 결과로 나와야 되는 반환값 갯수가 맞지 않아서

해결법 : 반환값 갯수를 일치시켜준다.

=> 쿼리의 결과가 장바구니에 담긴 상품들의 리스트를 보여 줘야 하므로 VO에 담은후 반환값을 LIST<VO>로 변환

2번 mapper 문에서 void 를 List<CartListVO>로 수정

 

 

 

 

수정된 mapper문

package com.sjb.mapper;
 
import java.util.List;
 
import com.sjb.model.CartVO;
import com.sjb.model.CartListVO;
 
public interface CartMapper {
 
 
    
    public List<CartListVO> cartList(int productId) throws Exception;
    
    
}
 

 

 

결과물 담은 VO

package com.sjb.model;
 
import java.util.List;
 
public class CartListVO {
 
    private int productId;
    
    private String memberId;
    
    private int cartStock;
    
    private String addDate;
    
    private String title;
    
    private int authorID;
    
    private String authorName;
        
    private String publisher;
    
    private String cateCode;
    
    private String cateName;
    
    private String bookCover;
    
    private List<BookCoverVO> bCover;
    
    private String publeYear;
    
    private int likeStar;
    
    private int bookReply;
    
    private int bookPrice;
    
    private int discountRate;
    
    private int sellprice;
    
    private String bookInfo;
    
    private String contents;
    
    private String introImage;
    
    private String regdate;
    
    private String boardRegdate;
    
    private String modDate;
    
    private int bookStock;
 
    public String getMemberId() {
        return memberId;
    }
 
    public void setMemberId(String memberId) {
        this.memberId = memberId;
    }
 
    public int getCartStock() {
        return cartStock;
    }
 
    public void setCartStock(int cartStock) {
        this.cartStock = cartStock;
    }
 
    public String getAddDate() {
        return addDate;
    }
 
    public void setAddDate(String addDate) {
        this.addDate = addDate;
    }
 
    public int getProductId() {
        return productId;
    }
 
    public void setProductId(int productId) {
        this.productId = productId;
    }
 
    public String getTitle() {
        return title;
    }
 
    public void setTitle(String title) {
        this.title = title;
    }
 
    public int getAuthorID() {
        return authorID;
    }
 
    public void setAuthorID(int authorID) {
        this.authorID = authorID;
    }
 
    public String getAuthorName() {
        return authorName;
    }
 
    public void setAuthorName(String authorName) {
        this.authorName = authorName;
    }
 
    public String getPublisher() {
        return publisher;
    }
 
    public void setPublisher(String publisher) {
        this.publisher = publisher;
    }
 
    public String getCateCode() {
        return cateCode;
    }
 
    public void setCateCode(String cateCode) {
        this.cateCode = cateCode;
    }
 
    public String getCateName() {
        return cateName;
    }
 
    public void setCateName(String cateName) {
        this.cateName = cateName;
    }
 
    public String getBookCover() {
        return bookCover;
    }
 
    public void setBookCover(String bookCover) {
        this.bookCover = bookCover;
    }
 
    public List<BookCoverVO> getbCover() {
        return bCover;
    }
 
    public void setbCover(List<BookCoverVO> bCover) {
        this.bCover = bCover;
    }
 
    public String getPubleYear() {
        return publeYear;
    }
 
    public void setPubleYear(String publeYear) {
        this.publeYear = publeYear;
    }
 
    public int getLikeStar() {
        return likeStar;
    }
 
    public void setLikeStar(int likeStar) {
        this.likeStar = likeStar;
    }
 
    public int getBookReply() {
        return bookReply;
    }
 
    public void setBookReply(int bookReply) {
        this.bookReply = bookReply;
    }
 
    public int getBookPrice() {
        return bookPrice;
    }
 
    public void setBookPrice(int bookPrice) {
        this.bookPrice = bookPrice;
    }
 
    public int getDiscountRate() {
        return discountRate;
    }
 
    public void setDiscountRate(int discountRate) {
        this.discountRate = discountRate;
    }
 
    public int getSellprice() {
        return sellprice;
    }
 
    public void setSellprice(int sellprice) {
        this.sellprice = sellprice;
    }
 
    public String getBookInfo() {
        return bookInfo;
    }
 
    public void setBookInfo(String bookInfo) {
        this.bookInfo = bookInfo;
    }
 
    public String getContents() {
        return contents;
    }
 
    public void setContents(String contents) {
        this.contents = contents;
    }
 
    public String getIntroImage() {
        return introImage;
    }
 
    public void setIntroImage(String introImage) {
        this.introImage = introImage;
    }
 
    public String getRegdate() {
        return regdate;
    }
 
    public void setRegdate(String regdate) {
        this.regdate = regdate;
    }
 
    public String getBoardRegdate() {
        return boardRegdate;
    }
 
    public void setBoardRegdate(String boardRegdate) {
        this.boardRegdate = boardRegdate;
    }
 
    public String getModDate() {
        return modDate;
    }
 
    public void setModDate(String modDate) {
        this.modDate = modDate;
    }
 
    public int getBookStock() {
        return bookStock;
    }
 
    public void setBookStock(int bookStock) {
        this.bookStock = bookStock;
    }
 
    @Override
    public String toString() {
        return "cartListVO [memberId=" + memberId + ", cartStock=" + cartStock + ", addDate=" + addDate + ", productId="
                + productId + ", title=" + title + ", authorID=" + authorID + ", authorName=" + authorName
                + ", publisher=" + publisher + ", cateCode=" + cateCode + ", cateName=" + cateName + ", bookCover="
                + bookCover + ", bCover=" + bCover + ", publeYear=" + publeYear + ", likeStar=" + likeStar
                + ", bookReply=" + bookReply + ", bookPrice=" + bookPrice + ", discountRate=" + discountRate
                + ", sellprice=" + sellprice + ", bookInfo=" + bookInfo + ", contents=" + contents + ", introImage="
                + introImage + ", regdate=" + regdate + ", boardRegdate=" + boardRegdate + ", modDate=" + modDate
                + ", bookStock=" + bookStock + "]";
    }
    
    
    
    
}
 

 

결과