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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
