Non-Entity Class and Returning Query Results to User

[Sean]

In my OpenXava project, I have an entity class named Applicant. This class is mapped to a table named applicant and works fine in the application. If I create a new non-entity class, I am able to create a query referencing the Applicant entity and return the results as a List to the user. The below query works fine returning results in my new module:

public List<Applicant> getApplicants() {
    EntityManagerFactory f= Persistence.createEntityManagerFactory("junit");
    EntityManager manager = f.createEntityManager();
    manager.getTransaction().begin();

    Query query = manager.createQuery("select a from Applicant a");
    List<Applicant> results = query.getResultList();

    manager.close();

    return results;     
}

What I need to do is create a query that joins more than one table (in my example, Applicant and Appreport, which is defined in a separate entity class) and returns results in a list to the user. I have created a non-entity class that I use to pass the results of my query to:

public class AppListData implements Serializable {
    public String lName;
    public String fName;
    public String rptCategory;
    public String rptType;

    public AppListData(String lName,String fName,String rptCategory,String rptType) {
        this.lName = lName;
        this.fName = fName;
        this.rptCategory = rptCategory;
        this.rptType = rptType;
    }
}

I am having trouble getting this to work. I have attempted the below and there are no errors returned, but also no results on screen (and I know the query returns at least one record):

public List<AppListData> getApps() {
    EntityManagerFactory f= Persistence.createEntityManagerFactory("junit");
    EntityManager manager = f.createEntityManager();
    manager.getTransaction().begin();

    TypedQuery<AppListData> query = manager.createQuery("SELECT NEW org.openxava.realitybgi.model.AppListData(" +
                    "a.lname,a.fname,r.rptCategory,r.rptType) from Applicant a, Appreport r where r.applicant.appid = a.appid",AppListData.class);
    List<AppListData> results = query.getResultList();
    manager.close();

    return results;     
}

Can someone help with how to display the results on screen (I don't need to persist these results, just display them to the user)?

Thanks,
Sean

[Javier Paniza]

Hi Sean,

OpenXava only recognizes entities as elements for collections, so AppListData is not recognized, or at least it's a case that we never have tried.

Try to solve your case in this way:

@ListProperties("applicant.lname, aplicant.fname, prtCatogory, rptType")
public List<Appreport> getApplicants() {
    EntityManagerFactory f= Persistence.createEntityManagerFactory("junit");
    EntityManager manager = f.createEntityManager();
    manager.getTransaction().begin();

    Query query = manager.createQuery("select a from Appreport r");
    List<Applicant> results = query.getResultList();

    manager.close();

    return results;     
}

Don't think in data scattered in tables, but in objects with references and collections. Usually you can obtain all the data you need from the correct root object.

---

Help others in this forum as I help you.

[Sean]

Thank you, Javier. Your suggestion works well. What I have now done is add a Transient DescriptionsList that I would like to use to allow the user to filter the List based on their selection from the DescriptionsList combo box. Is this possible?

I've tried to add a parameter to the createQuery statement in getApplicants, but while I can successfully parameterize the query by providing the actual category id value (example: query.setParameter(1,1)), I am not able to refresh the List display with the only those records related to the combo box selection. I have researched using @OnChange for the DescriptionsList, but have not been successful with that.

Here is my updated code:

private Category categories;
@Transient
@ManyToOne(fetch=FetchType.LAZY) 
@DescriptionsList(
        descriptionProperties="id",
        orderByKey=true)    
public Category getCategories() {       
    return categories;
}   
public void setCategories(Category categories) {
    this.categories = categories;
}

@ListProperties("applicant.lname, aplicant.fname, rptCatogory, rptType")
public List<Appreport> getApplicants() {
    EntityManagerFactory f= Persistence.createEntityManagerFactory("junit");
    EntityManager manager = f.createEntityManager();
    manager.getTransaction().begin();

    Query query = manager.createQuery("select r from Appreport r where r.category.id = ?1");
    query.setParameter(1,categories.getId());      
    List<Appreport> results = query.getResultList();

    manager.close();

    return results;     
}

[Javier Paniza]

Hi Sean,

the problem is that the combo with the value is in the view layer (because the data is in the View object until you save it, and this case you do not save) and your method is in the model layer and cannot (or better you should not) to access view data from your model, though this is a special case of model, it's a model just for create a view.

That is, it's not easy to access the combo value from your entity method, however I think that you can solve you case easily defining you collection in this way:

@OneToMany
@ListProperties("applicant.lname, aplicant.fname, rptCatogory, rptType")
private Collection<Appreport> applicants;

// Getters and setter for applicants

And the create a @OnChange action with a code like this:

String category = getView().getValue("catogery.id");
 getView().getSubview("application").getCollectionTab().setBaseCondition("r.category.id = " + category);

Try this or something inspired on this. Tell me if it works for you.

---

Help others in this forum as I help you.