StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHQL Bulk insert
text
Body
copied!<p>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 <a href="http://docs.jboss.org/hibernate/core/3.3/reference/en/html/batch.html#batch-direct" rel="nofollow">http://docs.jboss.org/hibernate/core/3.3/reference/en/html/batch.html#batch-direct</a> to create my query.</p> <pre><code>@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") </code></pre> <p>My Shema:</p> <pre><code>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) ); </code></pre> <p>Simplyfied my template:</p> <pre><code> 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) ) </code></pre> <p>I get the following error:</p> <pre><code>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) </code></pre> <p>Game is an entity which has a long identifier generated by a sequence. </p> <p>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?</p> <p>Thanks in advance for your time, best regards m</p> <p>PS: how i call the query:</p> <pre><code>Query query = em.createNamedQuery("Tile.bulkLoadLevel"); query.setParameter("game", game.getGameid()); int copyiedEntities = query.executeUpdate(); </code></pre> <p>Entities:</p> <pre><code>public class Tile implements Serializable{ @Id private int x; @Id private int y; @Id @ManyToOne @JoinColumn(name="gameid") private Game game; </code></pre> <p>PS: the cast doesn't work either. </p> <blockquote> <p>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</p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload