Hibernate native SQL query Multiple table Association, SQL statement to pay attention to the problem

Hibernate native SQL query Multiple table Association, SQL statement to pay attention to the problem

@for &ever 2009-9-4

System environment:

MySQL5.1

Hibernate3.3

The assumption is as follows:

The entity classes Question and answer correspond to the data table Question and answer respectively.

and the fields of table question and answer are mostly the same, and the number of fields is the same.

Perform the following actions:

1>

Using hibernate with native SQL queries,

Query q = session.createsqlquery (sql). Addentity (Question.class). addentity (Answer.class);

The SQL executed by Createsqlquery is the following statement:

Select b.*, a.* from question B left joins answer a on a.id = B.ansid

After the Addentity entities question and answer above, the query can proceed, but the bean's note value is incorrect.

The specific phenomenon is, question and answer two entities query out, data confusion.

This behavior is described in the native SQL query chapter in Hibernate's official documentation and is addressed in a specific way.

2>

Then, according to the Hibernate document, modify the SQL statement as follows:

Select {b.*}, {a.*} from question {b} left join answer {a} on {a}.id = {B}.ansid

After executing the query, the error:

Hibernate:select {b.*}, {a.*} from question {b} left join answer {a} on {a}.id = {B}.ansid

2009-09-04 20:03:53,625 WARN [Org.hibernate.util.JDBCExceptionReporter]-<sql error:1064, sqlstate:42000>

2009-09-04 20:03:53,625 Error [Org.hibernate.util.JDBCExceptionReporter]-<you has an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' from question left Joi n answer on. id =. Ansid ' in line 1>

Exception in thread ' main ' org.hibernate.exception.SQLGrammarException:could not execute query

At Org.hibernate.exception.SQLStateConverter.convert (sqlstateconverter.java:90)

At Org.hibernate.exception.JDBCExceptionHelper.convert (jdbcexceptionhelper.java:66)

At Org.hibernate.loader.Loader.doList (loader.java:2231)

At Org.hibernate.loader.Loader.listIgnoreQueryCache (loader.java:2125)

3>

Then modify the SQL statement to:

Select {bbbb}.*, {aaaa}.* from question {bbbb} ' left join answer {AAAA} ' on {aaaa}.id={bbbb}.ansid

Query, error is as follows:

2009-09-04 19:14:59,140 INFO [Org.hibernate.type.IntegerType]-<could not read column value from result set:id10_0_; Column ' id10_0_ ' not found.>

2009-09-04 19:14:59,140 WARN [Org.hibernate.util.JDBCExceptionReporter]-<sql error:0, sqlstate:s0022>

2009-09-04 19:14:59,140 ERROR [org.hibernate.util.JDBCExceptionReporter]-<column ' id10_0_ ' not found.>

Exception in thread ' main ' org.hibernate.exception.SQLGrammarException:could not execute query

At Org.hibernate.exception.SQLStateConverter.convert (sqlstateconverter.java:90)

At Org.hibernate.exception.JDBCExceptionHelper.convert (jdbcexceptionhelper.java:66)

4>

The final modification is:

Select {bbbb.*}, {aaaa.*} from question {bbbb} left join answer {AAAA} on {Aaaa}.id={bbbb}.ansid

The query results are normal.

Summarize:

1 , using Hibernate Native SQL queries, when multiple tables are associated, or when multiple table fields are returned, it is a good idea to add aliases and note the spelling of the added aliases;

2 , aliases alias the name is not too short, as in the example above, a--and AAAA, B--and BBBB , the query on everything is normal;

3 , when multiple tables are associated with native SQL when querying, remember to addentity for each entity to be returned .