HQL Bulk insert

Question

I am using postgresql with hibernate and i would like to bulk insert data from a template table to another. How to do that in a native Query is clear to me, but in HQL i doesn't really know how to reach my expected result. I used the Syntax from http://docs.jboss.org/hibernate/core/3.3/reference/en/html/batch.html#batch-direct to create my query.

@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")

My 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)
 );

Simplyfied my template:

   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)
   )

I get the following error:

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)

Game is an entity which has a long identifier generated by a sequence.

As you can see game is not in my template table so i would need to force the game id into my query. Does anyone know how this has to be done?

Thanks in advance for your time, best regards m

PS: how i call the query:

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

Entities:

public class Tile implements Serializable{

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

PS: the cast doesn't work either.

java.lang.ExceptionInInitializerError at net.hq.process.db.PersistenceTest.setUp(PersistenceTest.java:58) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at 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.java:44) at 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) at org.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(RemoteTestRunner.java:390) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) Caused by: java.lang.NullPointerException at java.lang.Class.forName0(Native Method) 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) at 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) at org.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 more

Answer 1

I figured out how to do this from jorgegm's comment above, which I wish was his actual answer since his original answer gives an NPE.

You want to create your query like so:

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

Then, call

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

I did this using Hibernate 3.6 and it works like a charm.

Answer 2

Try with:

INSERT INTO Tile (x, y, game, tileOverlay, startTile, blockWalkable, sightBlocking) SELECT t.x, t.y, cast(:game as Game), t.tileOverlay, t.startTile, t.blockWalkable, t.sightBlocking from TemplateQuestTile t")

and

query.setEntity("game", game);

I'm assuming that you also have a class named Tile and x, y, tileOverlay... are properties of that class. From the hibernate reference docs: "The pseudo-syntax for INSERT statements is: INSERT INTO EntityName properties_list select_statement".

Regarding the cast function, "cast(... as ...), where the second argument is the name of a Hibernate type", so it should work.

I didn´t try it with entities but it worked fine with simple types (byte, integer...).

Answer 3

I think you can't express this query in HQL. Hibernate documentation describes several limitations of INSERT ... SELECT ... queries, especially

select_statement can be any valid HQL select query, with the caveat that the return types must match the types expected by the insert. Currently, this is checked during query compilation rather than allowing the check to relegate to the database.

Since you can't express in the query the fact that the :game has a type of Game, compilation of this query never succeeds.

Try to use native SQL query instead.