In this month's Oracle Magazine, Frank Nimphius wrote a very good article about an Oracle ADF Faces dashboard application to support persistent user personalization.
You can read this entire article clicking here. The idea in this article is to extend the dashboard application. My idea here is to create a similar dashboard application, but instead ADF BC model layer, I'm intending to use EJB3.0.
There are just a one small trick here and I'll show you. I'm using the HR usual oracle schema.
The steps are:
1. Create a ADF Fusion Application with EJB as a layer model
2. Generate the entities from table (I'm using Department and Employees only)
3. Create a new Session Bean. I called it: HRSessionEJB
4. Create a new method like that:
public List getAllDepartmentsHavingEmployees(){
JpaEntityManager jpaEntityManager =
(JpaEntityManager)em.getDelegate();
Query query =
jpaEntityManager.createNamedQuery("Departments.allDepartmentsHavingEmployees");
JavaBeanResult.setQueryResultClass(query, AggregatedDepartment.class);
return query.getResultList();
}
5. In the Departments entity, create a new native query annotation:
@Entity
@NamedQueries( { @NamedQuery(name = "Departments.findAll", query = "select o from Departments o")
})
@NamedNativeQueries({
@NamedNativeQuery(name="Departments.allDepartmentsHavingEmployees", query = "select e.department_id, d.department_name , sum(e.salary), avg(e.salary) , max(e.salary), min(e.salary) from departments d , employees e where d.department_id = e.department_id group by e.department_id, d.department_name")})
public class Departments implements Serializable {...}
6. Create a new POJO called AggregatedDepartment:
package oramag.sample.dashboard.model;
import java.io.Serializable;
import java.math.BigDecimal;
public class AggregatedDepartment implements Serializable{
@SuppressWarnings("compatibility:5167698678781240729")
private static final long serialVersionUID = 1L;
private BigDecimal departmentId;
private String departmentName;
private BigDecimal sum;
private BigDecimal avg;
private BigDecimal max;
private BigDecimal min;
public AggregatedDepartment() {
super();
}
public AggregatedDepartment(BigDecimal departmentId, String departmentName, BigDecimal sum, BigDecimal avg,
BigDecimal max, BigDecimal min) {
super();
this.departmentId = departmentId;
this.departmentName = departmentName;
this.sum = sum;
this.avg = avg;
this.max = max;
this.min = min;
}
public void setDepartmentId(BigDecimal departmentId) {
this.departmentId = departmentId;
}
public BigDecimal getDepartmentId() {
return departmentId;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
public String getDepartmentName() {
return departmentName;
}
public void setSum(BigDecimal sum) {
this.sum = sum;
}
public BigDecimal getSum() {
return sum;
}
public void setAvg(BigDecimal avg) {
this.avg = avg;
}
public BigDecimal getAvg() {
return avg;
}
public void setMax(BigDecimal max) {
this.max = max;
}
public BigDecimal getMax() {
return max;
}
public void setMin(BigDecimal min) {
this.min = min;
}
public BigDecimal getMin() {
return min;
}
}
7. Create the util java class called JavaBeanResult. The function of this class is to configure a native SQL query to return POJOs in a single line of code using the utility class. Credits: http://onpersistence.blogspot.com.br/2010/07/eclipselink-jpa-native-constructor.html
package oramag.sample.dashboard.model.util;
/*******************************************************************************
* Copyright (c) 2010 Oracle. All rights reserved.
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
* which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* @author shsmith
******************************************************************************/
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Query;
import org.eclipse.persistence.exceptions.ConversionException;
import org.eclipse.persistence.internal.helper.ConversionManager;
import org.eclipse.persistence.internal.sessions.AbstractRecord;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.queries.DatabaseQuery;
import org.eclipse.persistence.queries.QueryRedirector;
import org.eclipse.persistence.sessions.Record;
import org.eclipse.persistence.sessions.Session;
/***
* This class is a simple query redirector that intercepts the result of a
* native query and builds an instance of the specified JavaBean class from each
* result row. The order of the selected columns musts match the JavaBean class
* constructor arguments order.
*
* To configure a JavaBeanResult on a native SQL query use:
* JavaBeanResult.setQueryResultClass(query, SomeBeanClass.class);
* where query is either a JPA SQL Query or native EclipseLink DatabaseQuery.
*
* @author shsmith
*
*/
public final class JavaBeanResult implements QueryRedirector {
private static final long serialVersionUID = 3025874987115503731L;
protected Class resultClass;
public static void setQueryResultClass(Query query, Class resultClass) {
JavaBeanResult javaBeanResult = new JavaBeanResult(resultClass);
DatabaseQuery databaseQuery = JpaHelper.getDatabaseQuery(query);
databaseQuery.setRedirector(javaBeanResult);
}
public static void setQueryResultClass(DatabaseQuery query,
Class resultClass) {
JavaBeanResult javaBeanResult = new JavaBeanResult(resultClass);
query.setRedirector(javaBeanResult);
}
protected JavaBeanResult(Class resultClass) {
this.resultClass = resultClass;
}
@SuppressWarnings("unchecked")
public Object invokeQuery(DatabaseQuery query, Record arguments,
Session session) {
List results = new ArrayList();
try {
Constructor[] constructors =
resultClass.getDeclaredConstructors();
Constructor javaBeanClassConstructor = null;
// (Constructor) resultClass.getDeclaredConstructors()[0];
Class[] constructorParameterTypes = null;
// javaBeanClassConstructor.getParameterTypes();
List rows = (List) query.execute(
(AbstractSession) session, (AbstractRecord) arguments);
for (Object[] columns : rows) {
boolean found = false;
for (Constructor constructor : constructors) {
javaBeanClassConstructor = constructor;
constructorParameterTypes = javaBeanClassConstructor.getParameterTypes();
if (columns.length == constructorParameterTypes.length) {
found = true;
break;
}
// if (columns.length != constructorParameterTypes.length) {
// throw new ColumnParameterNumberMismatchException(
// resultClass);
// }
}
if (!found)
throw new ColumnParameterNumberMismatchException(
resultClass);
Object[] constructorArgs = new Object[constructorParameterTypes.length];
for (int j = 0; j < columns.length; j++) {
Object columnValue = columns[j];
Class parameterType = constructorParameterTypes[j];
// convert the column value to the correct type--if possible
constructorArgs[j] = ConversionManager.getDefaultManager()
.convertObject(columnValue, parameterType);
}
results.add(javaBeanClassConstructor.newInstance(constructorArgs));
}
} catch (ConversionException e) {
throw new ColumnParameterMismatchException(e);
} catch (IllegalArgumentException e) {
throw new ColumnParameterMismatchException(e);
} catch (InstantiationException e) {
throw new ColumnParameterMismatchException(e);
} catch (IllegalAccessException e) {
throw new ColumnParameterMismatchException(e);
} catch (InvocationTargetException e) {
throw new ColumnParameterMismatchException(e);
}
return results;
}
public final class ColumnParameterMismatchException extends
RuntimeException {
private static final long serialVersionUID = 4752000720859502868L;
public ColumnParameterMismatchException(Throwable t) {
super(
"Exception while processing query results-ensure column order matches constructor parameter order",
t);
}
}
public final class ColumnParameterNumberMismatchException extends
RuntimeException {
private static final long serialVersionUID = 1776794744797667755L;
public ColumnParameterNumberMismatchException(Class clazz) {
super(
"Number of selected columns does not match number of constructor arguments for: "
+ clazz.getName());
}
}
}
8. Create the DataControl and a jsf or jspx page
9. Drag allDepartmentsHavingEmployees from DataControl and drop in your page
10. Choose Graph > Type: Bar (Normal) > any layout
11. In the wizard screen, Bars label, adds: sum, avg, max, min. In the X Axis label, adds: departmentName, and click in OK button
12. Run the page, the result is showed below:
You can download the workspace here . It was using the latest jdeveloper version 11.1.2.2.
