CODEWITHSUNDEEP
javasqlhibernatejpa
leopold
leopold

Reputation: 88

hibernate composite key hql insert bulk

I have tables with composite keys in existing database. When I want to use bulk-insert in HQL like "INSERT INTO entityName (id.key1 id.key2, property1, property, ...) SELECT prop1, prop2, prop3, prop3 FROM entityName2" I get error: Caused by: org.hibernate.QueryException: could not resolve property ...

If my entity for the "insert into"-statement has only one id-column everything works like a charm.

I use hibernate-entitymanager version 4.3.6.

First the entity for the insert-clause:

package de.kbv.rms;
// Generated 30.07.2014 10:31:56 by Hibernate Tools 4.3.1


import java.math.BigDecimal;
import java.util.Date;
import javax.persistence.AttributeOverride;
import javax.persistence.AttributeOverrides;
import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * Dm1Dap generated by hbm2java
 */
@Entity
@Table(name="DM1_DAP"
)
public class Dm1Dap  implements java.io.Serializable {

     @EmbeddedId
    @AttributeOverrides( {
        @AttributeOverride(name="dmpFallNr", column=@Column(name="DMP_FALL_NR", nullable=false, length=10) ), 
        @AttributeOverride(name="anr", column=@Column(name="ANR", nullable=false, length=14) ), 
        @AttributeOverride(name="versNr", column=@Column(name="VERS_NR", nullable=false, length=21) ), 
        @AttributeOverride(name="kassenNr", column=@Column(name="KASSEN_NR", nullable=false, length=10) ) } )
    private Dm1DapId id;

    // ... columns with getters and setters.

the composite-key for the DAP-Entity:

package de.kbv.rms;
// Generated 30.07.2014 10:31:56 by Hibernate Tools 4.3.1


import javax.persistence.Column;
import javax.persistence.Embeddable;

/**
 * Dm1DapId generated by hbm2java
 */
@Embeddable
public class Dm1DapId  implements java.io.Serializable {



    @Column(name="DMP_FALL_NR", nullable=false, length=10)
    private String dmpFallNr;

    @Column(name="ANR", nullable=false, length=14)
    private String anr;

    @Column(name="VERS_NR", nullable=false, length=21)
    private String versNr;

    @Column(name="KASSEN_NR", nullable=false, length=10)
    private String kassenNr;

    // columns with getters and setters
     ...
    // equals and hash-Methods 
     ...

my entity for the select-clause. I tested select-clause separately and it works without problems. 

package de.kbv.rms;
// Generated 30.07.2014 10:31:56 by Hibernate Tools 4.3.1

import java.math.BigDecimal;
import java.util.Date;
import javax.persistence.AttributeOverride;
import javax.persistence.AttributeOverrides;
import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * Dm1F generated by hbm2java
 */
@Entity
@Table(name="DM1_F"
)
public class Dm1F  implements java.io.Serializable {



     @EmbeddedId
    @AttributeOverrides( {
        @AttributeOverride(name="DmpFallNr", column=@Column(name="F_DMP_FALL_NR", nullable=false, length=10) ), 
        @AttributeOverride(name="Anr", column=@Column(name="F_ANR", nullable=false, length=14) ), 
        @AttributeOverride(name="DokuDatum", column=@Column(name="F_DOKU_DATUM", nullable=false, length=7) ) } )
    private Dm1FId id;

// ... columns with getters and setters.

my composite-key for the Dm1F-Entity:

package de.kbv.rms;
// Generated 30.07.2014 10:31:56 by Hibernate Tools 4.3.1

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * Dm1FId generated by hbm2java
 */
@Embeddable
public class Dm1FId  implements java.io.Serializable {

    @Column(name="F_DMP_FALL_NR", nullable=false, length=10)
    private String DmpFallNr;

    @Column(name="F_ANR", nullable=false, length=14)
    private String Anr;

    @Temporal (TemporalType.DATE)
    @Column(name="F_DOKU_DATUM", nullable=false, length=7)
    private Date DokuDatum;

    // columns with getters and setters
    ...
    // equals and hash-Methods 
    ...

my JUNIT-Test:

package dao.test;

import java.util.concurrent.TimeUnit;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;
import javax.persistence.Query;

import org.junit.Test;

public class RmsSimpleTest {

    public static EntityManager entityManager = Persistence.createEntityManagerFactory("devsample")
            .createEntityManager();

    @Test
    public void testQuery() {
        long startTime = System.nanoTime();

        Query query = entityManager
                .createQuery("INSERT INTO  Dm1Dap (id.dmpFallNr, id.anr, id.versNr, id.kassenNr) SELECT f.id.DmpFallNr, f.id.Anr, f.VersNr, f.KassenNr FROM Dm1F f");

        query.executeUpdate();

        long estimatedTime = java.lang.System.nanoTime() - startTime;
        System.out.println(" Time: "
                + String.format("%d milsec", TimeUnit.NANOSECONDS.toMillis(estimatedTime)));
    }
}

stacktrace snippet:

java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property:  of: de.kbv.rms.Dm1Dap [INSERT INTO  Dm1Dap (id.dmpFallNr, id.anr, id.versNr, id.kassenNr) SELECT f.id.DmpFallNr, f.id.Anr, f.VersNr, f.KassenNr FROM de.kbv.rms.Dm1F f]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)

DEBUG-Snippet:

4016 [main] DEBUG org.hibernate.hql.internal.ast.QueryTranslatorImpl  - parse() - HQL: INSERT INTO  Dm1Dap (id.dmpFallNr, id.anr, id.versNr, id.kassenNr) SELECT f.id.DmpFallNr, f.id.Anr, f.VersNr, f.KassenNr FROM de.kbv.rms.Dm1F f
4038 [main] DEBUG org.hibernate.hql.internal.ast.QueryTranslatorImpl  - --- HQL AST ---
 \-[INSERT] Node: 'INSERT'
    +-[INTO] Node: 'INTO'
    |  +-[IDENT] Node: 'Dm1Dap'
    |  \-[RANGE] Node: 'column-spec'
    |     +-[DOT] Node: '.'
    |     |  +-[IDENT] Node: 'id'
    |     |  \-[IDENT] Node: 'dmpFallNr'
    |     +-[DOT] Node: '.'
    |     |  +-[IDENT] Node: 'id'
    |     |  \-[IDENT] Node: 'anr'
    |     +-[DOT] Node: '.'
    |     |  +-[IDENT] Node: 'id'
    |     |  \-[IDENT] Node: 'versNr'
    |     \-[DOT] Node: '.'
    |        +-[IDENT] Node: 'id'
    |        \-[IDENT] Node: 'kassenNr'
    \-[QUERY] Node: 'query'
       \-[SELECT_FROM] Node: 'SELECT_FROM'
          +-[FROM] Node: 'FROM'
          |  \-[RANGE] Node: 'RANGE'
          |     +-[DOT] Node: '.'
          |     |  +-[DOT] Node: '.'
          |     |  |  +-[DOT] Node: '.'
          |     |  |  |  +-[IDENT] Node: 'de'
          |     |  |  |  \-[IDENT] Node: 'kbv'
          |     |  |  \-[IDENT] Node: 'rms'
          |     |  \-[IDENT] Node: 'Dm1F'
          |     \-[ALIAS] Node: 'f'
          \-[SELECT] Node: 'SELECT'
             +-[DOT] Node: '.'
             |  +-[DOT] Node: '.'
             |  |  +-[IDENT] Node: 'f'
             |  |  \-[IDENT] Node: 'id'
             |  \-[IDENT] Node: 'DmpFallNr'
             +-[DOT] Node: '.'
             |  +-[DOT] Node: '.'
             |  |  +-[IDENT] Node: 'f'
             |  |  \-[IDENT] Node: 'id'
             |  \-[IDENT] Node: 'Anr'
             +-[DOT] Node: '.'
             |  +-[IDENT] Node: 'f'
             |  \-[IDENT] Node: 'VersNr'
             \-[DOT] Node: '.'
                +-[IDENT] Node: 'f'
                \-[IDENT] Node: 'KassenNr'

4038 [main] DEBUG org.hibernate.hql.internal.ast.ErrorCounter  - throwQueryException() : no errors
4065 [main] DEBUG org.hibernate.hql.internal.antlr.HqlSqlBaseWalker  - insert << begin [level=1, statement=insert]
4071 [main] ERROR org.hibernate.hql.internal.ast.ErrorCounter  - <AST>:1:24: unexpected AST node: .
4071 [main] ERROR org.hibernate.hql.internal.ast.ErrorCounter  - <AST>:1:24: unexpected AST node: .
<AST>:1:24: unexpected AST node: .
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.insertablePropertySpec(HqlSqlBaseWalker.java:986)

Is there any way to use bulk-insert and composite-keys in hibernate?

Upvotes: 3

Views: 1970

Answers (1)

xhaggi
xhaggi

Reputation: 11

As a workaround you should add the composite-id properties directly to the entity and set them to insertable = false and updateable = false. There is no need to add getter/setter for that properties.

@Column(name = "F_DMP_FALL_NR", insertable = false, updateable = false)
private String DmpFallNr;

The insert into select bulk operation do not check this options. Now you can remove the id prefix and use the properties directly in your HQL query.

INSERT INTO Dm1Dap (idmpFallNr, anr, versNr, kassenNr) SELECT f.id.DmpFallNr, f.id.Anr, f.VersNr, f.KassenNr FROM Dm1F f

Upvotes: 1

Related Questions