controller 取不到 shoplist

Question

问题描述 :
在浏览器中直接输入 http://localhost:8080/O2O/frontend/listshops?parentId=12&pageSize=9&pageIndex=1, 取到的 shopList 为空.
问题背景 :
首先我已经将我的数据库中的数据该得和老师差不多了, 然后我测试了 ShopDao, 测试代码和结果如下 :

@Test
    public void testQueryShopListAndCount() {
        Shop shopCondition = new Shop();
        ShopCategory childCategory = new ShopCategory();
        ShopCategory parentCategory = new ShopCategory();
        parentCategory.setShopCategoryId(12L);
        childCategory.setParent(parentCategory);
        shopCondition.setShopCategory(childCategory);
        List<Shop> shopList = shopDao.queryShopList(shopCondition, 0, 6);
        System.out.println(shopList.size());
        System.out.println(shopList);
        int count = shopDao.queryShopCount(shopCondition);
        System.out.println(count);
    }

测试结果 :

JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@5ce8d869] will not be managed by Spring
==>  Preparing: SELECT s.shop_id, s.shop_name, s.shop_desc, s.shop_addr, s.phone, s.shop_img, s.priority, s.create_time, s.last_edit_time, s.enable_status, s.advice, a.area_id, a.area_name, sc.shop_category_id, sc.shop_category_name FROM tb_shop s, tb_area a, tb_shop_category sc WHERE s.shop_category_id in (select shop_category_id from tb_shop_category WHERE parent_id = ?) AND s.area_id = a.area_id AND s.shop_category_id = sc.shop_category_id ORDER BY s.priority DESC LIMIT ?, ?; 
Wed Feb 12 10:18:41 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
==> Parameters: 12(Long), 0(Integer), 6(Integer)
Wed Feb 12 10:18:41 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
<==    Columns: shop_id, shop_name, shop_desc, shop_addr, phone, shop_img, priority, create_time, last_edit_time, enable_status, advice, area_id, area_name, shop_category_id, shop_category_name
<==        Row: 28, 小黄人主题奶茶店, 不接受预定, 请直接来店里进行消费, 位于西苑2号, 13297044271, /upload/images/item/shop/1/实话.jpg, 50, null, null, 0, null, 2, 西苑, 22, 奶茶店
<==        Row: 29, 爆满奶茶店, 过来吃吃喝喝好, 东苑1号, 13141111619, /upload/images/item/shop/1/王尼玛.jpg, 40, null, null, 0, null, 1, 东苑, 22, 奶茶店
<==        Row: 30, 表哥大排档, 是兄弟就来吃, 西苑1号, 13141111619, /upload/images/item/shop/1/很好吃.jpg, 30, null, null, 0, null, 2, 西苑, 20, 大排档
<==        Row: 31, 伟哥大排档, 来吧, 东苑南路, 111112121, /upload/images/item/shop/1/很好吃.jpg, 20, null, null, 0, null, 1, 东苑, 20, 大排档
<==        Row: 32, 你是我的奶茶, 奶茶店点点点, 西苑路, 121212, /upload/images/item/shop/1/王尼玛.jpg, 10, null, null, 0, null, 2, 西苑, 22, 奶茶店
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ff8d125]
5
[com.imooc.o2o.entity.Shop@7776ab, com.imooc.o2o.entity.Shop@79179359, com.imooc.o2o.entity.Shop@dbd8e44, com.imooc.o2o.entity.Shop@51acdf2e, com.imooc.o2o.entity.Shop@6a55299e]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f1de2d6] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@79517588] will not be managed by Spring
==>  Preparing: SELECT count(1) FROM tb_shop s, tb_area a, tb_shop_category sc WHERE s.shop_category_id in (select shop_category_id from tb_shop_category WHERE parent_id = ?) AND s.area_id = a.area_id AND s.shop_category_id = sc.shop_category_id 
==> Parameters: 12(Long)
Wed Feb 12 10:18:41 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
<==    Columns: count(1)
<==        Row: 5
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f1de2d6]
5

这就说明我的 ShopDao 的开发是没有问题的, 然后对 ShopListController 中的 listShops 方法进行 debug, 同时对 ShopServiceImpl 中的 getShopList 方法中加入了输入 shopCondition 的 shopCategory 的 parent 的 Id 的方法和 rowIndex 以及 pageSize. 输出的也是没有问题的.
如下是 ShopServiceImpl 的 getShopList :

@Override
    public ShopExecution getShopList(Shop shopCondition, int pageIndex, int pageSize) {
        int rowIndex = PageCalculator.calculateRowIndex(pageIndex, pageSize);
        List<Shop> shopList = shopDao.queryShopList(shopCondition, rowIndex, pageSize);
        System.out.println(shopCondition.getShopCategory().getParent().getShopCategoryId());
        System.out.println(rowIndex + "-------" + pageSize);
        int count = shopDao.queryShopCount(shopCondition);
        ShopExecution se = new ShopExecution();
        if (shopList != null) {
            se.setShopList(shopList);
            se.setCount(count);
        } else {
            se.setState(ShopStateEnum.INNER_ERROR.getState());
        }
        return se;
    }

debug 的图示 :

控制台的输出 :

JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@6153b91d] will not be managed by Spring
==>  Preparing: SELECT s.shop_id, s.shop_name, s.shop_desc, s.shop_addr, s.phone, s.shop_img, s.priority, s.create_time, s.last_edit_time, s.enable_status, s.advice, a.area_id, a.area_name, sc.shop_category_id, sc.shop_category_name FROM tb_shop s, tb_area a, tb_shop_category sc WHERE s.shop_category_id in (select shop_category_id from tb_shop_category WHERE parent_id = ?) and s.enable_status = ? AND s.area_id = a.area_id AND s.shop_category_id = sc.shop_category_id ORDER BY s.priority DESC LIMIT ?, ?; 
Wed Feb 12 10:30:14 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Feb 12 10:30:14 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
==> Parameters: 12(Long), 1(Integer), 0(Integer), 9(Integer)
Wed Feb 12 10:30:14 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Feb 12 10:30:14 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Feb 12 10:30:14 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Feb 12 10:30:14 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Feb 12 10:30:14 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f68b63f]
12
0-------9
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70926a1f] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@4edd257f] will not be managed by Spring
==>  Preparing: SELECT count(1) FROM tb_shop s, tb_area a, tb_shop_category sc WHERE s.shop_category_id in (select shop_category_id from tb_shop_category WHERE parent_id = ?) and s.enable_status = ? AND s.area_id = a.area_id AND s.shop_category_id = sc.shop_category_id 
==> Parameters: 12(Long), 1(Integer)
<==    Columns: count(1)
<==        Row: 0
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70926a1f]

麻烦老师帮忙看看什么问题, 找了两天了

Answer 1

翔仔 2020-02-12 23:42:20

同学好，浏览器中输入http://localhost:8080/O2O/frontend/listshops?parentId=12&pageSize=9&pageIndex=1之后，能否来到Controller，如果可以的话，看看Controller往Service传的参数，和你先前验证Service的一致么，然后看看控制台的sql是啥，把Controller调用Service执行后产生的sql复制粘贴到mysql客户端里，传入控制台里显示的参数值执行一下，看看能否获取到结果，如果获取不到，再定位传给service的数据是不是不对，再看看controller接收到的数据是不是不对

• 提问者 姜岑 #1

  谢谢老师, 两个 SQL 语句对比, 在 Controller 语句中多的是 enable_status 的值为 1