Monday, September 12, 2011

Handling Scalar values(aggregation function) From JPA layer




---------in EJB file------------

public List<CountryRegCount> getReg()
    {
    List<CountryRegCount> lst=new ArrayList<CountryRegCount>();  
     
      Iterator<Object[]> results=em.createQuery("select count(c) ,c.regionId from Countries c GROUP BY c.regionId").getResultList().iterator();
     
         
          while ( results.hasNext() ) {
    Object[] row = results.next();
   
    Long count = (Long) row[0];
   
    int  count_i=count.intValue();
   
    Regions regId = (Regions) row[1];
    BigDecimal  regId_i=regId.getRegionId();
   
    lst.add(new CountryRegCount(count_i, regId.getRegionName()));
   
        }
       
       return lst;
    }


--------------------------------------------------------------------------------------------------------
Entity file in JPA layer


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.jpa;

import java.io.Serializable;
import java.util.List;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;

/**
 *
 * @author Sajjad
 */
@Entity
@Table(name = "COUNTRIES")
@XmlRootElement
@NamedQueries({
   
    @NamedQuery(name = "Countries.findAll", query = "SELECT c FROM Countries c"),
    @NamedQuery(name = "Countries.findAll", query = "SELECT c FROM Countries c"),
    @NamedQuery(name = "Countries.findByCountryId", query = "SELECT c FROM Countries c WHERE c.countryId = :countryId"),
    @NamedQuery(name = "Countries.findByCountryName", query = "SELECT c FROM Countries c WHERE c.countryName = :countryName")})
public class Countries implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 2)
    @Column(name = "COUNTRY_ID")
    private String countryId;
    @Size(max = 40)
    @Column(name = "COUNTRY_NAME")
    private String countryName;
    @JoinColumn(name = "REGION_ID", referencedColumnName = "REGION_ID")
    @ManyToOne
    private Regions regionId;
    @OneToMany(mappedBy = "countryId")
    private List<Locations> locationsList;

    public Countries() {
    }

    public Countries(String countryId) {
        this.countryId = countryId;
    }

    public String getCountryId() {
        return countryId;
    }

    public void setCountryId(String countryId) {
        this.countryId = countryId;
    }

    public String getCountryName() {
        return countryName;
    }

    public void setCountryName(String countryName) {
        this.countryName = countryName;
    }

    public Regions getRegionId() {
        return regionId;
    }

    public void setRegionId(Regions regionId) {
        this.regionId = regionId;
    }

    @XmlTransient
    public List<Locations> getLocationsList() {
        return locationsList;
    }

    public void setLocationsList(List<Locations> locationsList) {
        this.locationsList = locationsList;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (countryId != null ? countryId.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Countries)) {
            return false;
        }
        Countries other = (Countries) object;
        if ((this.countryId == null && other.countryId != null) || (this.countryId != null && !this.countryId.equals(other.countryId))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "com.jpa.Countries[ countryId=" + countryId + " ]";
    }
   
}


No comments:

Post a Comment