我正在做一个grails项目。我正在尝试执行以下查询
String CHECK_FOR_HIGH_TRADE_VOLUME_QUERY = "Update LocationTrade lt set lt.hasVeryHighVolume=true where lt.locationIndices=? AND lt.trade.volume>20000"; ... LocationTrade.executeUpdate(CHECK_FOR_HIGH_TRADE_VOLUME_QUERY, [indices]);
LocationTrade与Trade之间的关系是单向多对一的。因此,LocationTrade引用了Trade,但是Trade类没有引用LocationTrade列表。
执行时,出现以下异常。
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute update query; SQL [update location_trade cross join set has_very_high_volume=1 where location_indices_id=? and volume>20000]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute update query and Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'set has_very_high_volume=1 where location_indices_id=997 and volume>20000' at line 1
似乎生成的查询是错误的。应该有一个与贸易表的联接,但这是缺失的。我无法识别我在这里犯的错误。有人可以帮我吗?
这两个表的创建脚本(我去除了一些无用的列)
CREATE TABLE `location_trade` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `version` bigint(20) NOT NULL, `auto_status` varchar(255) DEFAULT NULL, `exclusion_reason_description` varchar(255) DEFAULT NULL, `exclusion_reason_id` bigint(20) DEFAULT NULL, `exclusion_reason_title` varchar(255) DEFAULT NULL, `location_indices_id` bigint(20) DEFAULT NULL, `manual_status` varchar(255) DEFAULT NULL, `trade_id` bigint(20) DEFAULT NULL, `absolute_price` decimal(19,6) DEFAULT NULL, `flag` varchar(255) DEFAULT NULL, `auto_exclusion_reason` varchar(255) DEFAULT NULL, `date_created` datetime DEFAULT NULL, `exclusion_reason_text` varchar(255) DEFAULT NULL, `last_updated` datetime DEFAULT NULL, `has_very_high_volume` bit(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK858985A90CAA966` (`location_indices_id`), KEY `FK858985AB5FA6A69` (`trade_id`), CONSTRAINT `FK858985A90CAA966` FOREIGN KEY (`location_indices_id`) REFERENCES `location_indices` (`id`), CONSTRAINT `FK858985AB5FA6A69` FOREIGN KEY (`trade_id`) REFERENCES `trade` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=25405 DEFAULT CHARSET=latin1; CREATE TABLE `trade` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `version` bigint(20) NOT NULL, `comments` varchar(1020) DEFAULT NULL, `end_date` datetime DEFAULT NULL, `price` decimal(19,6) DEFAULT NULL, `price_type` varchar(255) DEFAULT NULL, `source_id` bigint(20) DEFAULT NULL, `start_date` datetime DEFAULT NULL, `trade_date` datetime DEFAULT NULL, `trade_name` varchar(255) DEFAULT NULL, `volume` decimal(19,6) DEFAULT NULL, `volume_units` varchar(255) DEFAULT NULL, `date_created` datetime DEFAULT NULL, `last_updated` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK697F1642D085935` (`source_id`), CONSTRAINT `FK697F1642D085935` FOREIGN KEY (`source_id`) REFERENCES `job_source` (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=26567 DEFAULT CHARSET=latin1;
谢谢
在Hibernate文档说:
批量HQL查询中不能指定隐式或显式连接。子查询可以在where子句中使用,其中子查询本身可能包含联接。
lt.trade.volume是LocationTrade和Trade之间的隐式内部联接,因此该查询无效。您必须将其重写为以下内容:
lt.trade.volume
update LocationTrade lt set lt.hasVeryHighVolume=true where lt.locationIndices=? and lt.id in ( select lt2.id from LocationTrade lt2 where lt2.trade.volume > 20000)
否则,您将不得不使用SQL查询。