gengv

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

语法错误，可能是由于字符型当成数值型，或者相反
举例来说，如下面传入一个参数 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

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

1. 使用int类型的参数


然后添加DataSet


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



2. 使用String类型参数


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


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



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

admin

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

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

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


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

admin

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

gengv

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

gengv

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

顺利得到结果。

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