JPA 原生态SQL 的复杂查询(多表Join)《转载》

JPA 原生态SQL 的复杂查询(多表Join)

更新: 2013-05-12字体: T T

import java.io.Serializable;import javax.persistence.*;import java.util.Set;                /** * The persistent class for the p_entry database table. *  */@NamedNativeQueries(    {       @NamedNativeQuery(           name="ReturnVariationInfoWithFullScalarType",           query=" select distinct vn.name as 'vnName', vn.location as 'location', vn.allele as 'allele',cs.class_name as 'className',dx.name as'dbName',er.reference_id as 'referenceId' from p_entry pe,var_entry ex,variation vn,db_xref dx ,class cs,external_reference er,var_phenotype_var vpv, var_phenotype vp     where pe.entry_id = ex.entry_id  and pe.entry_id=er.p_entry_id   and ex.variation_id=vn.id  and vn.class_id=cs.id  and vn.db_xref_id=dx.id  and vpv.variation_id = vn.id  and vpv.var_phenotype_id=vp.id  and vp.id=?1  and pe.entry_id=?2 ",           resultSetMapping="ReturnVariationInfoWithFullScalarType"),})@SqlResultSetMappings({    @SqlResultSetMapping    (       name="ReturnVariationInfoWithFullScalarType",       entities={},       columns=       {           @ColumnResult(name="vnName"),           @ColumnResult(name="location"),           @ColumnResult(name="allele"),           @ColumnResult(name="className"),           @ColumnResult(name="dbName"),           @ColumnResult(name="referenceId")       }    )})         @Entity@Table(name="p_entry")public class PEntry implements Serializable {    private static final long serialVersionUID = 1L;            @Id    @GeneratedValue(strategy=GenerationType.TABLE)    @Column(name="entry_id")    private String entryId;            //bi-directional many-to-one association to ExternalReference    @OneToMany(mappedBy="PEntry")    private Set<ExternalReference> externalReferences;            //bi-directional many-to-one association to GeneEntry    @OneToMany(mappedBy="PEntry")    private Set<GeneEntry> geneEntries;            //bi-directional many-to-one association to POrganism    @ManyToOne(fetch=FetchType.LAZY)    @JoinColumn(name="taxon_id")    private POrganism POrganism;            //bi-directional many-to-one association to VarEntry    @OneToMany(mappedBy="PEntry")    private Set<VarEntry> varEntries;            public PEntry() {    }            public String getEntryId() {        return this.entryId;    }            public void setEntryId(String entryId) {        this.entryId = entryId;    }            public Set<ExternalReference> getExternalReferences() {        return this.externalReferences;    }            public void setExternalReferences(Set<ExternalReference> externalReferences) {        this.externalReferences = externalReferences;    }                public Set<GeneEntry> getGeneEntries() {        return this.geneEntries;    }            public void setGeneEntries(Set<GeneEntry> geneEntries) {        this.geneEntries = geneEntries;    }                public POrganism getPOrganism() {        return this.POrganism;    }            public void setPOrganism(POrganism POrganism) {        this.POrganism = POrganism;    }                public Set<VarEntry> getVarEntries() {        return this.varEntries;    }            public void setVarEntries(Set<VarEntry> varEntries) {        this.varEntries = varEntries;    }            }

@NamedNativeQueries， 在POJO上声明SQL语句；

@NamedNativeQueries 如果在一个实体类中有多个NamedNativeQuery的话，必须使用该批注，并且将单个的NamedNativeQuery都作为NamedNativeQueries数组中的一个元素。

@NamedNativeQuery，在这里设置关于该本地查询的信息。name表示传递进EntityManager.createNamedQuery(“name”)的参数，query表示实施本地查询的sql语句，resultSetMapping表示返回结果集的映射方式。它的意思就是结果集将以哪种形式来保存。

@SqlResultSetMappings, 声明返回结果集的column 名

下面是Dao层的查询：

import java.util.List;     import javax.annotation.Resource;import javax.persistence.EntityManager;import javax.persistence.EntityManagerFactory;import javax.persistence.Query;     import org.springframework.orm.jpa.JpaTransactionManager;import org.springframework.stereotype.Repository;import org.springframework.transaction.annotation.Transactional;     import com.tilsi.solr.dao.summary.phenotype.iface.IPhenotypeDAO;import com.tilsi.solr.domain.summary.phenotype.PEntry;     @Repository("phenotypeDAO")@Transactional(readOnly = true)public class PhenotypeDAO implements IPhenotypeDAO {    @Resource(name = "summaryEmf")    private EntityManagerFactory emf;                  public PhenotypeDAO(){                 }         /**     * e.g.     * select distinct vn.id, vn.name, vn.location, vn.allele,cs.class_name,dx.name,vp.id,ex.entry_id,er.reference_id     *  from p_entry pe,var_entry ex,variation vn,db_xref dx ,class cs,external_reference er,var_phenotype_var vpv, var_phenotype vp      *  where pe.entry_id = ex.entry_id      * and pe.entry_id=er.p_entry_id     * and ex.variation_id=vn.id       * and vn.class_id=cs.id       * and vn.db_xref_id=dx.id     * and vpv.variation_id = vn.id     * and vpv.var_phenotype_id=vp.id     * and vp.id='2806'     * and ex.entry_id='ENSG00000136143'     */    @Override    public List<Object[]> findVariationInfo(String entryId, String vpId){        EntityManager em = emf.createEntityManager();        Query qry = em.createNamedQuery("ReturnVariationInfoWithFullScalarType");        qry.setParameter(1, vpId);        qry.setParameter(2, entryId);        List<Object[]> lst = qry.getResultList();        return lst;    }}

通过EntityManagerFactory 创建EntityManger, 然后利用EntityManager 调用createNamedQuery（namedQuerySql）方法来创建namedquery,进而实现Hibernate3 中Scalar 功能