JPA 原生态SQL 的复杂查询（多表Join）

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>  var Entries;                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 . var Entries;      }                public  void  setVarEntries(Set<VarEntry>  var Entries) {          this . var Entries =  var Entries;      }               }

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

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

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

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

下面是Dao层的查询：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

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