CODEWITHSUNDEEP
javaoracle-databasehibernate
user182944
user182944

Reputation: 8067

Generated SQL query syntax with Oracle are old

I am using Hibernate 3.2.5. When I use a JOIN I can see that the sql query generated by the Hibernate are the syntax prior to the Oracle 9i release. The queries are having (+) symbols to denote the LEFT OUTER JOIN rather than using LEFT OUTER JOIN and so on.

For this criteria:

Criteria criteria = session.createCriteria(Dept.class).createAlias("empMap","id",CriteriaSpecification.LEFT_JOIN).add(Restrictions.eq("deptId", new Integer(6)));

This is the SQL query generated:

select this_.DEPT_ID as DEPT1_1_1_, this_.DEPT_NAME as DEPT2_1_1_, id1_.DEPT_ID as DEPT2_3_, id1_.EMP_ID as EMP1_3_, id1_.EMP_ID as formula0_3_, id1_.EMP_ID as EMP1_0_0_, id1_.DEPT_ID as DEPT2_0_0_, id1_.EMP_NAME as EMP3_0_0_, id1_.AGE as AGE0_0_, id1_.SEX as SEX0_0_ from dept this_, emp id1_ where this_.DEPT_ID=id1_.DEPT_ID(+) and this_.DEPT_ID=?

My Queston is:

1) Why the queries are having old syntax? If I use Hibernate 4.0 or latest one then will it generate the new syntax SQL queries?

2) How to perform Outer Join in this case? I can see options for LEFT JOIN and FULL JOIN but can't see anything for RIGHT JOIN

EDIT

Q. 2 It was a typo mistake, its RIGHT JOIN

Please let me know about these.

Regards,

Upvotes: 0

Views: 531

Answers (2)

Tomasz Żuk
Tomasz Żuk

Reputation: 1288

Ad 2) If you have "left join" and "full join" (and, as I guess, "right join") options, then you can perform outer joins. These are the three existing types of an outer join. There is no such thing as a "pure" outer join.

Upvotes: 0

Tom Anderson
Tom Anderson

Reputation: 47243

If you look at the current Hibernate source code, then you'll see that there are a number of different SQL dialects for Oracle:

  • 8 - deprecated in favour of 8i
  • 8i - generates the (+) syntax
  • 9 - deprecated in favour of either 9i or 10g
  • 9i - generates the (+) syntax, with the comment Specifies to not use "ANSI join syntax" because 9i does not seem to properly handle it in all cases
  • 10g - generates ANSI joins

So, the only way to get ANSI join syntax is to use the 10g dialect. I don't know if that existed in 3.2.5, or if it did, whether the version of it there generates ANSI joins, but it's the only thing you can try.

Upvotes: 3

Related Questions