Blank FROM in JPQL?
I am using JPQL selection to access Oracle DB function: select FUNCTION('function_name', 'foo', 1234) from com_mycompany_bar obj
This works as expected and the function is indeed called. The point is, I really don't need a suggestion FROM
and would rather it be empty instead of having access to the object to match the syntax.
What's the best option I have here?
source to share
I see several possible answers:
There are two options in standard JPA, both with Oracle DUAL
"table"
- Map
DUAL
for the object and use it in the sentenceFROM
. This is all the more "JPQL", but it is more active:
In Java code:
@Entity(name = "Dual")
@Table(name = "SYS.DUAL")
public class Dual {
@Id
@Column(name = "DUMMY")
private String dummy;
}
In orm.xml:
<named-query name="yourQuery">
<query><![CDATA[
SELECT FUNCTION('function_name', 'foo', 1234) FROM Dual d
]]>
</query>
</named-query>
In the client code:
Query q = entityManager.createNamedQuery("yourQuery");
- Just use your own query that uses
DUAL
In orm.xml:
<named-native-query name="yourQuery">
<query><![CDATA[
SELECT function_name('foo', 1234) from sys.dual
]]>
</query>
</named-native-query>
In the client code:
Query q = entityManager.createNativeQuery("yourQuery");
As a last resort, you can use some JPA implementation extension for JPA and avoid DUAL
:
-
EclipseLink:
-
Hibernate:
source to share
In short: I don't think it is possible to skip the FROM clause of a JPQL query.
The JPQL grammar requires a FROM clause (see for example here or here ).
Stored procedures / functions are usually invoked using native queries (see createNativeQuery ). So this should work:
em.createNativeQuery("select function_name('foo', 1234)").getSingleResult()
UPDATE (from comments):
It is true that native JPA queries do not support named parameters. So you have to decide which of the two troubles is for you: either using JPQL with an unnecessary object reference, or using your own query with positional parameters instead of a name.
But keep in mind that FUNCTION is an extension of EclipseLink, so it won't work if you decide to change the provider.
source to share