Question: I understand that you can embed
Oracle SQL inside the Java Persistence API (JPA), but I don't
understand how JPA can interface with Oracle. Can you explain
how to execute a SQL statement inside JPA and show an example of
executing an Oracle stored procedure with JPA?
Answer: The Java Persistence API (JPA)
allows you to execute Oracle SQL and gather the results in Java by
binding the results to Java parameters. The most complex
aspect of using native SQL queries within JPA is binding results.
Binding a result set is simple when the query returns a single
result of a simple type. In the case where the result is a
complex type you will have to create an entity to which you can map
the native query, or define a complex result set mapped to multiple
entities.
A simple example of using native SQL queries in JPA:
List<Customer> customers = (List<Customer>)em.createNativeQuery
("SELECT * FROM customers", jpqlexample.entities.Customer.class)
.getResultList();
Iterator i = customers.iterator();
Customer cust;
out.println("Customers: " + "<br/>");
while (i.hasNext()) {
cust = (Customer) i.next();
out.println(cust.getCust_name() +"<br/>");
}
A big disadvantage of using native SQL queries is that your Java
code becomes directly dependent on the database structure. If
you change the structure of the database, you will have to adjust
your queries and recompile/redeploy your application. You can,
however, take advantage of Oracle stored procedures to move complex
SQL statements to programs that are executed from within the
database, and then calling those stored programs instead of making
direct queries on the underlying tables. With this structure,
when you change the structure of the tables you must change the
stored procedure instead of changing the actual Java code.
An example of using a stored procedure called
sum_total:
String sup_name ="Tortuga Trading";
BigDecimal sum =(BigDecimal)em.createNativeQuery ("SELECT sum_total(?1) FROM DUAL")
.setParameter(1, sup_name)
.getSingleResult();
out.println("The total cost of the ordered products
supplied by Tortuga Trading: " + sum +"");
sum_total is defined in the following example:
CREATE OR REPLACE FUNCTION sum_total(supplier VARCHAR2) RETURN NUMBER AS sup_sum NUMBER;
BEGIN
SELECT
SUM(p.price*l.quantity)
INTO sup_sum
FROM orders o
JOIN orderlineitems l
ON o.pono=l.pono
JOIN products p
ON l.prod_id=p.prod_id
JOIN suppliers s
ON p.sup_id=s.sup_id
WHERE sup_name = supplier;
RETURN sup_sum;
END;
/
For more information on creating stored procedures in Oracle,
check out this
helpful article.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
