jfinal 执行sql语句获取的数据与实际数据有差异

佰亿互联 发布于 2014/06/13 11:41
阅读 1K+
收藏 1

以下mysql 语句执行是没有问题的,但是在jfinal中执行,其中int_out.int_quantity, int_out.out_quantity获取的值有异常


SELECT int_out.sn, int_out.operation_date, item.id, item.code, item.name, item.url, item.image_url, item.price, int_out.int_quantity, int_out.out_quantity

FROM ((SELECT sn, operation_date, item_id, quantity int_quantity, @out_quantity out_quantity FROM t_storage_item LEFT JOIN t_storage ON t_storage_item.storage_id=t_storage.id) UNION ALL (SELECT sn, operation_date, item_id, @int_quantity int_quantity, quantity out_quantity FROM t_outbound_item LEFT JOIN t_outbound ON t_outbound_item.outbound_id=t_outbound.id)) int_out 

 LEFT JOIN (SELECT item.id, item.code, item_language.name, item.url, image.url image_url, item.market_price price FROM t_item item LEFT JOIN t_item_language item_language ON item.item_language_id=item_language.id LEFT JOIN t_image image ON item.image_id=image.id) item ON int_out.item_id=item.id 
 
 WHERE 1=1 AND int_out.item_id=2209 ORDER BY int_out.operation_date DESC

获取数据结果示例:

"id":2209,"operation_date":"2014-06-13","sn":"STO1406101746090158946","price":100.0,"image_url":"\/UPLOAD\/2927\/20140401\/1759318591354.jpg","name":"Donod D611 QVGA LCM Phone Coolsandchipset Dual sims dual standby","int_quantity":{},"code":"CP20140402020S","out_quantity":null,"url":"donod-d611-qvga-lcm-phone-coolsand-chipset-dual-sims-dual-standby"

其中int_quantity本应该是1,但是现在是{},求助。

@JFinal












加载中
1
佰亿互联
佰亿互联

@JFinal , @糊搞  问题已经解决了,还是在sql上的用户变量上,需要定义用户变量类型,像我这个就需要改成:

SELECT int_out.sn,
       int_out.operation_date,
       item.id,
       item.code,
       item.name,
       item.url,
       item.image_url,
       item.price,
       int_out.int_quantity,
       int_out.out_quantity
FROM (
        (SELECT sn,
                operation_date,
                item_id,
                quantity int_quantity,
                @out_quantity:=0 out_quantity
         FROM t_storage_item
         LEFT JOIN t_storage ON t_storage_item.storage_id=t_storage.id)
      UNION ALL
        (SELECT sn,
                operation_date,
                item_id,
                @int_quantity:=0 int_quantity,
                quantity out_quantity
         FROM t_outbound_item
         LEFT JOIN t_outbound ON t_outbound_item.outbound_id=t_outbound.id)) int_out
LEFT JOIN
  (SELECT item.id,
          item.code,
          item_language.name,
          item.url,
          image.url image_url,
          item.market_price price
   FROM t_item item
   LEFT JOIN t_item_language item_language ON item.item_language_id=item_language.id
   LEFT JOIN t_image image ON item.image_id=image.id) item ON int_out.item_id=item.id
WHERE 1=1
  AND int_out.item_id=?
ORDER BY int_out.operation_date DESC LIMIT 0,10



谢谢两位!

0
糊搞

一定是你在JFinal中执行时的SQL和在DB中操作SQL不一致引起的,参照下面的代码将JFinal执行时的SQL显示出来,然后作对比:

// 配置ActiveRecord插件
ActiveRecordPlugin arp = new ActiveRecordPlugin(c3p0Plugin);
arp.setShowSql(true); // 显示操作SQL



佰亿互联
佰亿互联
是一样的,sql是没有问题的
0
JFinal
JFinal
仔细核对一下是否是完全一样的sql。JFinal 拿到sql以后会直接扔给JDBC 来处理,所以几乎不会出错
佰亿互联
佰亿互联
回复 @JFinal : 错误的数据字段,是一个用户变量,您看下是不是这个原因
佰亿互联
佰亿互联
回复 @JFinal : 已经换上最新的驱动了,还是错误。
JFinal
JFinal
回复 @Adam.ZLQ : 在 mysql 之下对 int 型数据使用 getObject 从来没有出过问题,试试换个 jdbc 驱动包
佰亿互联
佰亿互联
回复 @JFinal : 是mysql
JFinal
JFinal
回复 @Adam.ZLQ : 这个太奇怪了,你用的是 mysql 吗? 换上最新的 jdbc 驱动试试
下一页
0
佰亿互联
佰亿互联

下面是打印出来的,基本是一样的:

SELECT int_out.sn, int_out.operation_date, item.id, item.code, item.name, item.url, item.image_url, item.price, int_out.int_quantity, int_out.out_quantity  FROM ((SELECT sn, operation_date, item_id, quantity int_quantity, @out_quantity out_quantity FROM t_storage_item LEFT JOIN t_storage ON t_storage_item.storage_id=t_storage.id) UNION ALL (SELECT sn, operation_date, item_id, @int_quantity int_quantity, quantity out_quantity FROM t_outbound_item LEFT JOIN t_outbound ON t_outbound_item.outbound_id=t_outbound.id)) int_out  LEFT JOIN (SELECT item.id, item.code, item_language.name, item.url, image.url image_url, item.market_price price FROM t_item item LEFT JOIN t_item_language item_language ON item.item_language_id=item_language.id LEFT JOIN t_image image ON item.image_id=image.id) item ON int_out.item_id=item.id  WHERE 1=1  AND int_out.item_id=?  ORDER BY int_out.operation_date DESC  limit 0, 10



0
JFinal
JFinal

引用来自“Adam.ZLQ”的评论

下面是打印出来的,基本是一样的:

SELECT int_out.sn, int_out.operation_date, item.id, item.code, item.name, item.url, item.image_url, item.price, int_out.int_quantity, int_out.out_quantity  FROM ((SELECT sn, operation_date, item_id, quantity int_quantity, @out_quantity out_quantity FROM t_storage_item LEFT JOIN t_storage ON t_storage_item.storage_id=t_storage.id) UNION ALL (SELECT sn, operation_date, item_id, @int_quantity int_quantity, quantity out_quantity FROM t_outbound_item LEFT JOIN t_outbound ON t_outbound_item.outbound_id=t_outbound.id)) int_out  LEFT JOIN (SELECT item.id, item.code, item_language.name, item.url, image.url image_url, item.market_price price FROM t_item item LEFT JOIN t_item_language item_language ON item.item_language_id=item_language.id LEFT JOIN t_image image ON item.image_id=image.id) item ON int_out.item_id=item.id  WHERE 1=1  AND int_out.item_id=?  ORDER BY int_out.operation_date DESC  limit 0, 10



使用 DbKit.getConfig().getConnection() 得到 Connection 对象后,手动用 JDBC  执行下该 sql ,看看执行结果如何?
0
糊搞

两个SQL基本一致,差别只在于limit关键字,你拿有Limit这句在DB中试试

SELECT int_out.sn,
       int_out.operation_date,
       item.id,
       item.code,
       item.name,
       item.url,
       item.image_url,
       item.price,
       int_out.int_quantity,
       int_out.out_quantity
FROM (
        (SELECT sn,
                operation_date,
                item_id,
                quantity int_quantity,
                @out_quantity out_quantity
         FROM t_storage_item
         LEFT JOIN t_storage ON t_storage_item.storage_id=t_storage.id)
      UNION ALL
        (SELECT sn,
                operation_date,
                item_id,
                @int_quantity int_quantity,
                quantity out_quantity
         FROM t_outbound_item
         LEFT JOIN t_outbound ON t_outbound_item.outbound_id=t_outbound.id)) int_out
LEFT JOIN
  (SELECT item.id,
          item.code,
          item_language.name,
          item.url,
          image.url image_url,
          item.market_price price
   FROM t_item item
   LEFT JOIN t_item_language item_language ON item.item_language_id=item_language.id
   LEFT JOIN t_image image ON item.image_id=image.id) item ON int_out.item_id=item.id
WHERE 1=1
  AND int_out.item_id=?
ORDER BY int_out.operation_date DESC LIMIT 0,10



返回顶部
顶部