sql抛出org.springframework.jdbc.badsqlgrammarexception(preparedstatementcallback;错误的sql语法

mjqavswn  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(440)

我在运行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”在代码中作为布尔值传递,但在sql表中它是数字(0,1)。我不确定这种转换是否有效。

f5emj3cl

f5emj3cl1#

不熟悉java库,但返回 ip_address, 以及 ?, 出现问题。

hgb9j2n6

hgb9j2n62#

请参见下面的示例以解决此问题

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setEscapeProcessing(true);//use this line
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeBatch();

确保注解行
另一个原因可能是raise,如果您使用特殊字符作为sql不支持的值,那么也检查一下

相关问题