杰表技术论坛 - Powered by Discuz! Board

标题: 关于在jdbc数据集中使用参数的问题？ [打印本页]

作者: gengv 时间: 2010-1-18 21:40 标题: 关于在jdbc数据集中使用参数的问题？

JOR设计的确实很不错，我用它做了行列并行层级不等的交叉表，很好使。

不过，我想在JDBC数据集中使用参数来作为where的条件。我照着使用手册，在select语句中使用了${参数名}，但是提示MySQL语法错误，看来在将语句传递给MySQL之前，似乎并没有解析脚本内容啊？

错误信息：
sql error.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027)
      at jatools.data.reader.sql.SqlReader.readStart(SqlReader.java:200)
      at jatools.designer.data.DatasetTable.setDataSet(DatasetTable.java:82)
      at jatools.designer.data.DatasetPreviewer$1.windowOpened(DatasetPreviewer.java:82)
      at java.awt.AWTEventMulticaster.windowOpened(Unknown Source)
      at java.awt.Window.processWindowEvent(Unknown Source)
      at javax.swing.JDialog.processWindowEvent(Unknown Source)
      at java.awt.Window.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.Dialog$1.run(Unknown Source)
      at java.awt.Dialog$3.run(Unknown Source)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.awt.Dialog.show(Unknown Source)
      at jatools.designer.data.DatasetPreviewer.show(DatasetPreviewer.java:201)
      at jatools.designer.SqlReaderDialog.preview(SqlReaderDialog.java:92)
      at jatools.designer.SqlReaderDialog.access$0(SqlReaderDialog.java:87)
      at jatools.designer.SqlReaderDialog$1.actionPerformed(SqlReaderDialog.java:143)
      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
      at java.awt.Component.processMouseEvent(Unknown Source)
      at javax.swing.JComponent.processMouseEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.Dialog$1.run(Unknown Source)
      at java.awt.Dialog$3.run(Unknown Source)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.awt.Dialog.show(Unknown Source)
      at jatools.designer.data.jdbc.SqlReaderFactory.edit(SqlReaderFactory.java:60)
      at jatools.designer.variable.action.DataSourceAction.modifyAction(DataSourceAction.java:163)
      at jatools.designer.variable.action.DataSourceAction.actionPerformed(DataSourceAction.java:81)
      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
      at javax.swing.AbstractButton.doClick(Unknown Source)
      at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source)
      at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(Unknown Source)
      at java.awt.Component.processMouseEvent(Unknown Source)
      at javax.swing.JComponent.processMouseEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

还请各位指点迷津。谢谢！

作者: admin 时间: 2010-1-21 19:59

语法错误，可能是由于字符型当成数值型，或者相反
举例来说，如下面传入一个参数 myid ，且值为 "123"

在数据库表里, 你的 id 是 integer 类型, 则

select * from xx where id=${myid} -> ... where id=123 正确
select * from xx where id='${myid}' -> ... where id='123' 错误

如果你的id是 varchar，则, :
select * from xx where id='${myid}' -> where id='123' 正确
select * from xx where id=${myid} -> where id=123 错误

请仔细体会.

作者: admin 时间: 2010-1-22 17:14

谢谢管理员的答复，但是我按照您的提示，仍然没有得出想要的结果。为了便于说明，也便于您测试，我用JOR自带的demo数据库做了点测试：

1. 使用int类型的参数

然后添加DataSet

点击预览后，得到如下警告，貌似参数没有被解析出来？

2. 使用String类型参数

如果在SQL直接查询，可以得到正常结果

但如果像管理员在上贴中说的那样使用参数查询，虽然不会报错，但也得不到任何结果

还请管理员帮忙测试一下。谢谢！

作者: admin 时间: 2010-1-22 17:19

这里需要说明的是，设计时是不会解析到参数默认值的，换句话说，你在jdbc设计窗口的预览下，是取不到你定义的paramYear，如果你想设计时，预览到数据，请这样:

... where year=${ has(paramYear) ? paramYear : 2002 }

has函数用于判断某个变量是否存在。这里用到脚本的三元运算.

而在web服务器下，你的paramYear总是存在的

作者: admin 时间: 2010-1-22 17:20

请各位，不要在短消息里，提问，如果没有特别要保密的东西，因为你的问题，也可能帮助到其他朋友。

作者: gengv 时间: 2010-1-22 19:32

不好意思，今天白天论坛无法发贴回复，不得已才给管理员发的站内消息。

作者: gengv 时间: 2010-1-22 19:51

谢谢管理员的耐心解答，我试着对String类型的参数也尝试一下：
SELECT sales.dq,sales.dw,sales.name,sales.year,sales.quarter,sales.month,sales.month_num FROM sales where Quarter='${ has(paramQuarter) ? paramQuarter: "第一季度" }'

顺利得到结果。

如果还有什么疑问回头再像管理员请教。

欢迎光临杰表技术论坛 (http://jatools.com/)