Hibernate: Using native SQL + addEntity()

Question

I have table A which have relation with table B as one-to-many. What I want to do is to do select with іщьу limit from table A and after join to selected results table B. So I have faced with a problem how do that in the right way bu the hibernate?

Typical Criteria.selectMaxResults it is not that I need, cause limit will accept after join and instead of getting 10 different rows from table A I will get, for example, just 1 row from table 'A' with joined 10 different rows from table B.

(1) So what is the best way to do that? Select just unique rows from A in one query and in the other query do select from A with join B?

In general, on the native SQL language, I expect to execute the next query:

String sQuery = SELECT a.*,b* FROM (SELECT * FROM parentTable WHERE c.id=777 LIMIT 0,10) AS a, b.* WHERE a.id=b.a_id;

So, according to hibernate tutorial and docs I've tried to use session.createSQLQuery(sQuery). My method looks in the next way:

@Override
@Transactional
public List<VocabularyWord> getWords(int vocId, int firstResult, int pageSize) {
    Session s = this.template.getSessionFactory().getCurrentSession();
    SQLQuery query = s.createSQLQuery("SELECT {vW.*}, m.* FROM (SELECT * FROM vocabulary_words AS vw WHERE vw.vocabulary_id=:id LIMIT :from,:size) AS vW, meaning AS m WHERE vW.id=m.vocabulary_words_id;");
    query.setInteger("id", vocId);
    query.setInteger("from", firstResult);
    query.setInteger("size", pageSize);
    query.addEntity("vW", VocabularyWord.class);
    query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    //  If unccoment the next word I get error: Column 'id1_1_1_' not found. But as I'have understood I don't need this.
    //    query.addEntity("m", Meaning.class);
    List l = query.list();
    return l;
}

And this method returns exactly what I want. BUT to return this result hibernate executes a huge amount of queries. (He try to do something like (1)?). Being exactly Hibernate executes 5 next queries:

Hibernate: 
    SELECT
        vW.id as id1_7_0_,
        vW.original_text as original2_7_0_,
        vW.transcription as transcri3_7_0_,
        vW.vocabulary_id as vocabula4_7_0_,
        m.* 
    FROM
        (SELECT
            * 
        FROM
            vocabulary_words AS vw 
        WHERE
            vw.vocabulary_id=? LIMIT ?,?) AS vW,
        meaning AS m 
    WHERE
        vW.id=m.vocabulary_words_id;
Hibernate: 
    select
        meaning0_.vocabulary_words_id as vocabula5_1_0_,
        meaning0_.id as id1_1_0_,
        meaning0_.id as id1_1_1_,
        meaning0_.definition as definiti2_1_1_,
        meaning0_.example as example3_1_1_,
        meaning0_.translation as translat4_1_1_,
        meaning0_.vocabulary_words_id as vocabula5_1_1_,
        meaning0_.w_type as w_type6_1_1_ 
    from
        meaning meaning0_ 
    where
        meaning0_.vocabulary_words_id=?
Hibernate: 
    select
        meaning0_.vocabulary_words_id as vocabula5_1_0_,
        meaning0_.id as id1_1_0_,
        meaning0_.id as id1_1_1_,
        meaning0_.definition as definiti2_1_1_,
        meaning0_.example as example3_1_1_,
        meaning0_.translation as translat4_1_1_,
        meaning0_.vocabulary_words_id as vocabula5_1_1_,
        meaning0_.w_type as w_type6_1_1_ 
    from
        meaning meaning0_ 
    where
        meaning0_.vocabulary_words_id=?
Hibernate: 
    select
        meaning0_.vocabulary_words_id as vocabula5_1_0_,
        meaning0_.id as id1_1_0_,
        meaning0_.id as id1_1_1_,
        meaning0_.definition as definiti2_1_1_,
        meaning0_.example as example3_1_1_,
        meaning0_.translation as translat4_1_1_,
        meaning0_.vocabulary_words_id as vocabula5_1_1_,
        meaning0_.w_type as w_type6_1_1_ 
    from
        meaning meaning0_ 
    where
        meaning0_.vocabulary_words_id=?
Hibernate: 
    select
        meaning0_.vocabulary_words_id as vocabula5_1_0_,
        meaning0_.id as id1_1_0_,
        meaning0_.id as id1_1_1_,
        meaning0_.definition as definiti2_1_1_,
        meaning0_.example as example3_1_1_,
        meaning0_.translation as translat4_1_1_,
        meaning0_.vocabulary_words_id as vocabula5_1_1_,
        meaning0_.w_type as w_type6_1_1_ 
    from
        meaning meaning0_ 
    where
        meaning0_.vocabulary_words_id=?

My VocabularyWord entity:

public class VocabularyWord implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", updatable = false, nullable = false)
    private int id;
    @Column(name = "vocabulary_id", updatable = false, nullable = false)
    private int vocId;
    @Column(name = "original_text", nullable = false)
    private String originalText;
    @Column(name="transcription", nullable = true)
    private String transcription;
    @OneToMany (mappedBy = "vocWord", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private Set<Meaning> meaning;

    // + getters and setters
}

My Meaning entity:

public class Meaning implements Serializable {
    @Id
    @Column(name = "id", updatable = false, nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "vocabulary_words_id", nullable = false, updatable = false)
    private VocabularyWord vocWord;
    @JsonIgnore
    @Column(name = "vocabulary_words_id", updatable = false, insertable = false)
    private int vocWordId;
    @Column(name = "w_type", nullable = true, unique = false)
    private String wType;
    @Column(name = "example", nullable = true, unique = false)
    private String example;
    @Column(name = "definition", nullable = true, unique = false)
    private String definition;
    @Column(name = "translation", nullable = true, unique = false)
    private String translation;
    //+ getters and setters
}

How can I fix this? Or, probably, have I choose the other way to realize that query?

I will appreciate any help, propositions, ideas and related links. Thanks in advance.

Nickolas · Accepted Answer · 2016-06-13 20:09:29Z

I have resolved my problem by changing my query and adding .addJoin() and .addRootEntity(). So now my method getWords looks next:

@Override
@Transactional
public List<VocabularyWord> getWords(int vocId, int firstResult, int pageSize) {
Session s = this.template.getSessionFactory().getCurrentSession();
SQLQuery query = s.createSQLQuery("SELECT {vW.*}, {m.*} FROM (SELECT * FROM vocabulary_words AS vw WHERE vw.vocabulary_id=:id LIMIT :from,:size) AS vW LEFT JOIN meaning AS m ON (m.vocabulary_words_id = vW.id);");
query.addEntity("vW", VocabularyWord.class)
.addJoin("m", "vW.meaning")
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.setInteger("id", vocId)
.setInteger("from", firstResult)
.setInteger("size", pageSize);
query.addRoot("vW", VocabularyWord.class);
List l = query.list();
return l;
}

This method executes exactly one query:

Hibernate: 
    SELECT
        vW.id as id1_7_0_,
        vW.original_text as original2_7_0_,
        vW.transcription as transcri3_7_0_,
        vW.vocabulary_id as vocabula4_7_0_,
        m.vocabulary_words_id as vocabula6_1_0__,
        m.id as id1_1_0__,
        m.id as id1_1_1_,
        m.definition as definiti2_1_1_,
        m.example as example3_1_1_,
        m.translation as translat4_1_1_,
        m.vocabulary_words_id as vocabula6_1_1_,
        m.w_type as w_type5_1_1_ 
    FROM
        (SELECT
            * 
        FROM
            vocabulary_words AS vw 
        WHERE
            vw.vocabulary_id=? LIMIT ?,?) AS vW 
    LEFT JOIN
        meaning AS m 
            ON (
                m.vocabulary_words_id = vW.id
            );

Collectives™ on Stack Overflow

Hibernate: Using native SQL + addEntity()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related