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 功能