I've wrote HQL query like this:
SELECT a FROM A a LEFT JOIN a.b where ...
Hibernate generate sql query like this:
SELECT a FROM A a LEFT JOIN a.b where a.b_id = b.id(+)
But when I write something like this:
SELECT a FROM where a.b.id > 5
It generate SQL:
SELECT a.* FROM A b cross join B b where b.id > 5
So when I combine this approaches I recieve Oracle ERROR:
SQL Error: 25156, SQLState: 99999
ORA-25156: old style outer join (+) cannot be used with ANSI joins
So is there way to say to Hibernate that I want receive only one type of queries (old style or new) ?
UPDATE: By combining I mean HQL query like this:
SELECT alarm FROM Alarm as a LEFT JOIN alarm.madeBy as user where user.name = 'George' and a.source.name = 'UFO'
So here I specify that Alarm should be connected with User with LEFT JOIN, and not specify how connect Alarm with Source, so Hibernate will connect it with Cross Join.
And SQL Query will be like this:
FROM Alarms a, Users u cross join Sources s where a.user_id = u.user_id(+) and a.source_id = s.source_id and u.name = 'George' and s.name = 'UFO'
For more understanding I will add small example of Alarm Entity:
@Entity
@Table(name = 'Alarms')
public class Alarm {
@Id
private BigDecimial id;
@ManyToOne
@JoinColumn(name = "user_id")
private User madeBy;
@ManyToOne
@JoinColumn(name = "source_id")
private Source source;
}
A LEFT OUTER JOIN (or simply LEFT JOIN) selects all records from the left side table even if there are no matching records in right side table.
A "fetch" join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections.
Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. HQL queries are translated by Hibernate into conventional SQL queries, which in turns perform action on database.
The Left Join is a keyword in SQL, which returns all data from the left-hand side table and matching records from the right-hand side table.
So the solving of this problem is set hibernate's Dialect to org.hibernate.dialect.Oracle9Dialect
<property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With