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