java - Sql 抛出 org.springframework.jdbc.BadSqlGrammarException(PreparedStatementCallback; 错误的 SQL 语法

Question

我在运行 SQL 查询时遇到问题。我有多个这样的查询，但只有这个不起作用。这是我的错误:

06/16/20 19:54:05.808 INFO  Camel (camel-1) thread #25 - JmsConsumer[AUDIT_EVENT] com.jcabi.log.Logger - #handle('StudyDispatchEvent{workstationId=151611, accelera..1053..Context='null', eventType=STUDY_DISPATCH}'): thrown org.springframework.jdbc.BadSqlGrammarException(PreparedStatementCallback; bad SQL grammar [ INSERT INTO smr_dicom_push_events (event_id, study_id, accel_name, accel_recv_ch_name, ip_address, port, ae_title, workstation_log_id, from_rule) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?); ]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00911: invalid character ) out of org.springframework.jdbc.support.SQLExceptionSubclassTranslator#doTranslate[93] in 10ms
06/16/20 19:54:05.837 ERROR Camel (camel-1) thread #25 - JmsConsumer[AUDIT_EVENT] org.apache.camel.util.CamelLogger - Failed delivery for (MessageId: ID:43d1a8f8-ca9a-4fa7-b50f-89fbb07d7e3d:5:1:1-9 on ExchangeId: ID-psh-app-audit-event-handler-6889655f7d-nzpd4-1592329533949-0-34). Exhausted after delivery attempt: 1 caught: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ INSERT INTO smr_dicom_push_events (event_id, study_id, accel_name, accel_recv_ch_name, ip_address, port, ae_title, workstation_log_id, from_rule) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?); ]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00911: invalid character . Processed by failure processor: FatalFallbackErrorHandler[Channel[DelegateSync[com.nuance.powershare.audit.event.handler.service.gateway.AuditEventMessageGateway$$Lambda$613/2123960023@756d127a]]] Message History --------------------------------------------------------------------------------------------------------------------------------------- RouteId ProcessorId Processor Elapsed (ms) [route1 ] [route1 ] [amqp://AUDIT_EVENT ] [ 44] [route1 ] [bean2 ] [bean[com.nuance.diag.common.messaging.service.adapter.DataCompressionServiceAd] [ 1] [route1 ] [bean3 ] [bean[com.nuance.diag.common.messaging.service.adapter.DataMapperServiceAdapter] [ 4] [route1 ] [bean4 ] [bean[com.nuance.powershare.audit.event.handler.service.adapter.AuditEventHandl] [ 38] [route1 ] [process1 ] [Processor@0x756d127a ] [ 21] Exchange --------------------------------------------------------------------------------------------------------------------------------------- Exchange[ Id ID-psh-app-audit-event-handler-6889655f7d-nzpd4-1592329533949-0-34 ExchangePattern InOnly Headers {breadcrumbId=ID-psh-app-audit-event-handler-6889655f7d-nzpd4-1592329533949-0-34, ContentType=application/json, DataType=STUDY_DISPATCH, JMSCorrelationID=null, JMSCorrelationIDAsBytes=null, JMSDeliveryMode=1, JMSDestination=AUDIT_EVENT, JMSExpiration=0, JMSMessageID=ID:43d1a8f8-ca9a-4fa7-b50f-89fbb07d7e3d:5:1:1-9, JMSPriority=4, JMSRedelivered=false, JMSReplyTo=null, JMSTimestamp=1592337245787, JMSType=null, JMSXDeliveryCount=1, JMSXGroupID=null, JMSXUserID=null} BodyType com.nuance.powershare.audit.event.model.StudyDispatchEvent Body StudyDispatchEvent{workstationId=151611, acceleratorName='3021AzDev', receivingChannelName='3021AzDev', ipAddress='10.0.0.34', port=6666, aeTitle='sendtopacs', isFromRule=true, priority=20, action=enqueue} StudyEvent{study=AuditStudy{id=14255648, studyUid='2.25.216144697767951623307700654550131549107', ibId=120912, studyDate=null, creationDate=null, patient=AuditPatient{id=319213}, accessionNumber='an123456', institutionName='Automation,Inc', modality='US', bodyPartsExamined='all Parts', studyDescription='This is a test study', referringPhysicianName='Dr Anderson'}} AuditEvent{actionAccount=AuditAccount{id=116233, accountName='API AutoPush Owner Account', user=AuditUser{id=125716, firstName='Owner', lastName='AutoPush', email='nuancepshapi+AutoPushOwner@gmail.com'}, repositoryId=null, ibId=null}, ownerAccount=AuditAccount{id=116233, accountName='API AutoPush Owner Account', user=AuditUser{id=125716, firstName='Owner', lastName='AutoPush', email='nuancepshapi+AutoPushOwner@gmail.com'}, repositoryId=null, ibId=null}, recipientAccount=null, custodianAccount=null, impersonatorAccount=null, requestContext='null', eventType=STUDY_DISPATCH} ] Stacktrace ---------------------------------------------------------------------------------------------------------------------------------------
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [

            INSERT INTO smr_dicom_push_events (event_id, study_id, accel_name, accel_recv_ch_name, ip_address,
            port, ae_title, workstation_log_id, from_rule)
            VALUES (?, ?, ?, ?, ?,
            ?, ?, ?, ?);

    ]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
...
...

我不确定为什么会发生这种情况。我知道所有的值都是有效的。我可以猜测的一件事是，在 sql 查询的末尾有分号。但我不知道。我做错了什么？

编辑:我可以猜测的另一个问题是“from_rule”在代码中作为 boolean 值传递，但在sql表中它是NUMBER(0,1)。我不确定这种转换是否有效。

Answer 1

不熟悉 java 库，但在 ip_address, 和 ?, 处返回似乎有问题。