java - How I can convert a special select from sql statement into criteria API -
today have problem existing sql statement. want need use tomee (tomcat+java ee) , written in criteria down.
i have these selects in sql:
select dest.zc_zip zip, dest.zc_location_name locname, acos( sin(radians(src.zc_lat)) * sin(radians(dest.zc_lat)) + cos(radians(src.zc_lat)) * cos(radians(dest.zc_lat)) * cos(radians(src.zc_lon) - radians(dest.zc_lon)) ) * 6371 distance what did? first 2 select no problem, last one, don't know how can realize it:
@persistenceunit(unitname="umkreissuche-jpa") private entitymanagerfactory emf = null; private entitymanager em = null; .. em = emf.createentitymanager(); // getting criteria builder criteriabuilder cb = em.getcriteriabuilder(); // setting zccoordinate model main profile criteriaquery<zccoordinate> cq = cb.createquery(zccoordinate.class); // setting root<zccoordinate> dest = cq.from(zccoordinate.class); // setting inner join join<zccoordinate, zccoordinate> src = dest.join("zcid", jointype.inner); // setting selects cq.multiselect(dest.get("zczip").alias("zip"), dest.get("zclocationname").alias("locname"), ???); here zccoordinate model:
package de.circlesearch.model; import java.io.serializable; import javax.persistence.*; /** * persistent class zc_coordinates database table. * */ @entity @table(name="zc_coordinates") @namedquery(name="zccoordinate.findall", query="select z zccoordinate z") public class zccoordinate implements serializable { private static final long serialversionuid = 1l; @id @generatedvalue(strategy=generationtype.auto) @column(name="zc_id", unique=true, nullable=false) private int zcid; @column(name="zc_lat", nullable=false) private double zclat; @column(name="zc_loc_id", nullable=false) private int zclocid; @column(name="zc_location_name", nullable=false, length=255) private string zclocationname; @column(name="zc_lon", nullable=false) private double zclon; @column(name="zc_zip", nullable=false, length=10) private string zczip; public zccoordinate() { } public int getzcid() { return this.zcid; } public void setzcid(int zcid) { this.zcid = zcid; } public double getzclat() { return this.zclat; } public void setzclat(double zclat) { this.zclat = zclat; } public int getzclocid() { return this.zclocid; } public void setzclocid(int zclocid) { this.zclocid = zclocid; } public string getzclocationname() { return this.zclocationname; } public void setzclocationname(string zclocationname) { this.zclocationname = zclocationname; } public double getzclon() { return this.zclon; } public void setzclon(double zclon) { this.zclon = zclon; } public string getzczip() { return this.zczip; } public void setzczip(string zczip) { this.zczip = zczip; } @override public int hashcode() { final int prime = 31; int result = 1; result = prime * result + zcid; return result; } @override public boolean equals(object obj) { if (this == obj) { return true; } if (obj == null) { return false; } if (!(obj instanceof zccoordinate)) { return false; } zccoordinate other = (zccoordinate) obj; if (zcid != other.zcid) { return false; } return true; } @override public string tostring() { return "zccoordinate [zcid=" + zcid + ", zclat=" + zclat + ", zclocid=" + zclocid + ", zclocationname=" + zclocationname + ", zclon=" + zclon + ", zczip=" + zczip + "]"; } }
my solution create transient field named distance calculation need obtain value, work not querying distance if not going work.
@transient private double distance; public double getdistance(){ acos( sin(radians(src.zc_lat)) * sin(radians(dest.zc_lat)) + cos(radians(src.zc_lat)) * cos(radians(dest.zc_lat)) * cos(radians(src.zc_lon) - radians(dest.zc_lon)) ) * 6371 distance } just consider if not using distance value within condition.
Comments
Post a Comment