HQL批量插入

Question

我在hibernate中使用postgresql，我想将数据从模板表大容量插入到另一个模板表中。如何在本地查询中这样做对我来说是很清楚的，但在HQL中，我并不真正知道如何达到我的预期结果。我使用http://docs.jboss.org/hibernate/core/3.3/reference/en/html/batch.html#batch-direct的语法来创建我的查询。

@NamedQuery(name="Tile.bulkLoadLevel", query="INSERT INTO Tile (x, y, game, tileOverlay, startTile, blockWalkable, sightBlocking)" +
        " SELECT t.x, t.y, :game as game, t.tileOverlay, t.startTile, t.blockWalkable, t.sightBlocking from TemplateQuestTile t")

我的Shema：

CREATE TABLE tile
(
   x integer NOT NULL,
   y integer NOT NULL,
   blockwalkable boolean NOT NULL,
   sightblocking boolean NOT NULL,
   starttile boolean NOT NULL,
   imagepath character varying(255) NOT NULL,
   gameid bigint NOT NULL,
   CONSTRAINT tile_pkey PRIMARY KEY (gameid, x, y)
 );

简化我的模板：

   CREATE TABLE templatequesttile
   (
     x integer NOT NULL,
     y integer NOT NULL,
     blockwalkable boolean NOT NULL,
     sightblocking boolean NOT NULL,
     starttile boolean NOT NULL,
     imagepath character varying(255) NOT NULL,
     questname character varying(255) NOT NULL,
     CONSTRAINT templatequesttile_pkey PRIMARY KEY (questname, questseries, x, y)
   )

我得到以下错误：

ERROR (SessionFactoryImpl.java:435) - Error in named query: Tile.bulkLoad
org.hibernate.QueryException: number of select types did not match those for insert [INSERT INTO Tile (x, y,    game, tileOverlay, startTile, blockWalkable, sightBlocking) SELECT t.x, t.y, :game, t.tileOverlay, t.startTile, t.blockWalkable, t.sightBlocking from net.hq.model.TemplateQuestTile t]
at org.hibernate.hql.ast.tree.IntoClause.validateTypes(IntoClause.java:115)
at org.hibernate.hql.ast.tree.InsertStatement.validate(InsertStatement.java:57)
at org.hibernate.hql.ast.HqlSqlWalker.postProcessInsert(HqlSqlWalker.java:715)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.insertStatement(HqlSqlBaseWalker.java:519)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)

游戏是一个由序列生成的长标识符的实体。

如您所见，游戏不在我的模板表中，所以我需要强制将游戏id输入到我的查询中。有人知道该怎么做吗？

提前谢谢你的时间，向我问好

PS:如何调用查询：

Query query = em.createNamedQuery("Tile.bulkLoadLevel");
query.setParameter("game", game.getGameid());
int copyiedEntities = query.executeUpdate();

实体：

public class Tile implements Serializable{

@Id
private int x;
@Id
private int y;
@Id
@ManyToOne
@JoinColumn(name="gameid")
private Game game;

PS:演员也不起作用。

net.hq.process.db.PersistenceTest.setUp(PersistenceTest.java:58) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native

java.lang.ExceptionInInitializerError ) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java44)在org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:27) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31) at org.junit.runners.ParentRunner.run(ParentRunner.java:236) atorg.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run( org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) )由: java.lang.NullPointerException at java.lang.Class.forName0(原生方法) at java.lang.Class.forName(Class.java:169) at org.hibernate.util.ReflectHelper.classForName(ReflectHelper.java:192) at org.hibernate.type.TypeFactory.heuristicType(TypeFactory.java:279) at org.hibernate.type.TypeFactory.heuristicType(TypeFactory.java:264) at org.hibernate.hql.ast.util.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:400) at org.hibernate.hql.ast.util.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:392) at org.hibernate.hql.ast.tree.MethodNode.dialectFunction(MethodNode.java:103) at org.hibernate.hql.ast.tree.MethodNode.resolve(MethodNode.java:78) at org.hibernate.hql.ast.HqlSqlWalker.processFunction(HqlSqlWalker.java:979) at org.hibernate.hql.antlr.HqlSqlBaseWalker.functionCall(HqlSqlBaseWalker.java:2529) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2129) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:1983) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1515) at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:586)在org.hibernate.hql.antlr.HqlSqlBaseWalker.insertStatement(HqlSqlBaseWalker.java:510) at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261) at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:254) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:101) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:80) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:98) at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:562) at org.hibernate.impl.SessionFactoryImpl.(SessionFactoryImpl.java:424) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1385) atorg.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:954) at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:891) at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:57) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:48) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:32) at net.hq.util.Db.(Db.java:7) . 17

Answer 1

我认为你不能用HQL来表达这个查询。Hibernate文档描述了INSERT ... SELECT ...查询的几个限制，特别是

select_statement可以是任何有效的HQL查询，但必须说明返回类型必须与insert所期望的类型匹配。目前，这是在查询编译期间检查的，而不是允许将检查降到数据库。

由于您不能在查询中表示:game有一种类型的Game，所以该查询的编译永远不会成功。

尝试使用本机SQL查询。

Answer 2

试着：

插入Tile (x，y，startTile，blockWalkable，sightBlocking)选择t.x，t.y，cast(游戏作为游戏)，t.tileOverlay，t.startTile，t.blockWalkable，t.sightBlocking来自TemplateQuestTile t")

和

query.setEntity(游戏，游戏)；

我假设你还有一个名为Tile和x，y，tileOverlay的类.是那个类的属性。在hibernate参考文档中：“INSERT语句的伪语法是: INSERT INSERT EntityName properties_list select_statement”。

关于cast函数，“cast(. as .)，其中第二个参数是Hibernate类型的名称”，所以它应该可以工作。

我没有在实体中尝试它，但是它在简单类型(字节、整数.)中工作得很好。

Answer 3

我从jorgegm上面的评论中想出了如何做到这一点，我希望这是他的实际答案，因为他最初的回答给出了NPE。

您希望像这样创建查询：

INSERT INTO Tile (x, y, game, tileOverlay, startTile, blockWalkable, sightBlocking)
SELECT t.x, t.y, g, t.tileOverlay, t.startTile, t.blockWalkable, t.sightBlocking 
  FROM TemplateQuestTile t,
       Game g
 WHERE g.id = :gameId

然后，打电话

query.setParameter("gameId", game.getId());

我使用Hibernate 3.6做了这件事，它的工作就像一种魅力。