Grid SQL Query

Hi,

i wonder is there any methods for nativequery to map to grid?

my query methods is based on query of a few sql tables:

 @Override
    public List getRecentJob(int startingPoint, int maxResult) {
        try {
            Query q = em.createNativeQuery("Select b.jobNo,  ItemCode, JobOrderDate ,DelieveryDate, jobTitle, "
                    + "CoyName, FirstName,DeliveryQty,  OpenSizeWidth, OpenSizeLength,"
                    + "FrontColour,backColour From ( Select jobNo, JobOrderDate , DelieveryDate, "
                    + "jobTitle,ItemCode, FirstName,CoyName,DeliveryQty From ( Select jobNo, "
                    + "DATE_FORMAT( date , '%Y-%m-%d' ) As JobOrderDate, DATE_FORMAT( DeliveryDate , '%Y-%m-%d' ) As DelieveryDate, jobTitle, ItemCode, "
                    + "CoyName, job.DesignerICSTAFFID, DeliveryQty from job join "
                    + "ClientCompany on job.CompanyRef = ClientCompany.CompanyRef) "
                    + "As A join Staff on Staff.StaffID = A.DesignerICSTAFFID) As b "
                    + "join Part on B.jobNo = Part.jobNo Where PartID = 1 Order By JobNo Desc");
            if (startingPoint >= 0) {
                System.out.println("StartingPoint is not null");
                q.setMaxResults(maxResult);
                q.setFirstResult(startingPoint);
            } else {
                return null;
            }
            List result = q.getResultList();
            System.out.println("result: " + result.size());
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

I recommend you to create entity type for your query result, so that you get typed list as a result. Then it is much easier to use it. For example you can give that list then directly to Grid.setItems(result), since list is collection.

Check e.g. discussion in StackOverflow


https://stackoverflow.com/questions/13700565/jpa-query-getresultlist-use-in-a-generic-way

Hi Tatu,
thanks for this. i created a entiy class with the example given.

as i create this methods in a another ejb so that another appclient module can access this. however i find that the values of each entity exist during query but it is not transfer over to the webapp. any idea what have i miss out?

also the put methods does not seem to be required or access?

package Model;

import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;

/**
 *
 * @author weilianghome
 */
@Entity
public class RecentJobs {

    @Id
    private Integer jobNo;
    private String itemcode;
    private Date date;
    private Date deliveryDate;
    private String jobTitle;

    private String coyName;

    private String firstName;

    private int deliveryQty;

    private double openSizeWidth;

    private double openSizeLength;

    private int frontColour;
    private int backColour;

    public RecentJobs() {
    }

    /**
     * Generic put method to map JPA native Query to this object.
     *
     * @param column
     * @param value
     */
    public void put(Object column, Object value) {

        switch (((String) column)) {
            case "jobNo": {
                setJobNo((Integer) value);
                System.out.println("jobNo");
            }
            default:
                System.out.println("invalid case: " + ((String) column));
        }
    }

    public Integer getJobNo() {
        return jobNo;
    }

    public void setJobNo(Integer jobNo) {
        this.jobNo = jobNo;
    }

    public String getItemcode() {
        return itemcode;
    }

    public void setItemcode(String itemcode) {
        this.itemcode = itemcode;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Date getDeliveryDate() {
        return deliveryDate;
    }

    public void setDeliveryDate(Date deliveryDate) {
        this.deliveryDate = deliveryDate;
    }

    public String getJobTitle() {
        return jobTitle;
    }

    public void setJobTitle(String jobTitle) {
        this.jobTitle = jobTitle;
    }

    public String getCoyName() {
        return coyName;
    }

    public void setCoyName(String coyName) {
        this.coyName = coyName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public int getDeliveryQty() {
        return deliveryQty;
    }

    public void setDeliveryQty(int deliveryQty) {
        this.deliveryQty = deliveryQty;
    }

    public double getOpenSizeWidth() {
        return openSizeWidth;
    }

    public void setOpenSizeWidth(double openSizeWidth) {
        this.openSizeWidth = openSizeWidth;
    }

    public double getOpenSizeLength() {
        return openSizeLength;
    }

    public void setOpenSizeLength(double openSizeLength) {
        this.openSizeLength = openSizeLength;
    }

    public int getFrontColour() {
        return frontColour;
    }

    public void setFrontColour(int frontColour) {
        this.frontColour = frontColour;
    }

    public int getBackColour() {
        return backColour;
    }

    public void setBackColour(int backColour) {
        this.backColour = backColour;
    }

}


 

I think comments by Denis in this other discussion apply to this case as well.


https://vaadin.com/forum/#!/thread/16972648

Generally, if you are using Vaadin CDI add-on, you need to have that in same war module with the beans you are injecting etc.

The cross war referencing of beans is something that I would try to avoid.

But at quick glance, you seem to miss @Remote annotation in your bean. That for starters. You may encounter other things to be adjusted too.


https://stackoverflow.com/questions/19999916/calling-ejb-from-a-war-inside-the-same-ear