public List<User> fetchUserByRand(int numbers) throws Exception {
// TODO Auto-generated method stub
javax.persistence.Query _query;
StringBuffer _queryQL=new StringBuffer("SELECT * FROM user");
_queryQL.append(" ORDER BY RAND() LIMIT ");
_queryQL.append(numbers);
_query=getEntityManager().createNativeQuery(_queryQL.toString(),User.class);
List<User> result =_query.getResultList();
return result.isEmpty()?null:result;
}
功能: 根据numbers 多少,在表user里随机查询出多少人。
补充下其他原生SQL:【我采用的是第二种:简单实体的原生查询】
原生查询
EntityManager接口有三个创建原生查询的方法:一个返回标量值,一个返回实体类型,还有一个返回多个实体与标量值组合
标量原生查询
createNativeQuery(String sql);
简单实体的原生查询
createNativeQuery(String sql,Class entityClass);根据某个实体定义好的映射元数据,将返回值映射到该实体
复杂原生查询
createNativeQuery(String sql,String mappingName)
返回多个实体
@Entity
@SqlResultSetMapping(name=”customerAndCreditCardMapping”,//映射名
entities={@EntityResult(entityClass=Customer.class),//entities指定映射的实体
@EntityResult(entityClass=CreditCard.class,
fields={@FieldResult(name=”id”,column=”CC_ID”),//指定实体内成员与返回字段的映射
@FieldResult(name=”number”,column=”number”)})
})
public class Customer{………}
等价XML
<entity-mappings>
<sel-result-set-mapping name=” customerAndCreditCardMapping”>
<entity-result entity-class=”com.titan.domain.Customer”/>
<entity-result entity-class=”com.titan.domain.CreditCard”>
<field-result name=”id” column=”CC_ID”/>
<field-result name=”number” column=”number”/>
</entity-result>
</sel-result-set-mapping>
</entity-mappings>
使用方法
String sql=”select c.id,c.firstName,cc.id As CC_IC,cc.number from CUST_TABEL
c,CREDIT_CARD_TABLE cc….”
manager.createNativeQuery(sql,”customerAndCreditCardMapping”);
返回结果既有标量也有实体
@SqlResultSetMapping(name=”reservationCount”,//映射名
entities={@EntityResult(entityClass=Cruise.class,
fields={@FieldResult(name=”id”,column=”id”) })},
columns={@ColumnsResult(name=”resCount”)}//标量
)
@Entity
public class Cruise{……}
等价XML
<entity-mappings>
<sel-result-set-mapping name=” reservationCount”>
<entity-result entity-class=”com.titan.domain.Cruise”>
<field-result name=”id” column=”id”/>
</entity-result>
<column-result name=”resCount”>
</sel-result-set-mapping>
</entity-mappings>
使用方法
String sql=”select c.id,count(Resrvation.id) as resCount from Cruise c left join
Reservation on c.id=………..”;
manager.createNativeQuery(sql,” reservationCount”);
具名EJB QL查询
预先定义好EJB QL或原生的SQL查询
使用@javax.persistence.NamedQuery用来预定EJB QL的,NamedQuerys用于定义多条查询
@NamedQuerys({
NamedQuery(name=”getAverageReservateion”,//名字
query=”select AVG(r.amountPaid) from Cruise As c Join c.reservatons r where
c=:cruise”),//EJB QL
NamedQuery(……)
})
@Entity
public class Cruise{…….}
等价XML
<entity-mapping>
<named-query name=” getAverageReservateion”>
<query>
EJB QL
</query>
</ named-query >
</entity-mapping >
使用方式
Query query=em.createNamedQuery(“getAverageReservateion”);
query.setParameter(“cruise”.cruise);
具名原生查询
使用@javax.persistence.NamedNativeQuery注解来预定义具名原生SQL查询
@NamedNativeQuery(
name=”findCustAndCCNum”,
query=”select ……..”,//原生SQL
resultClass=”…”,//只返回一个实体时用
resultSetMapping=”….”
//一个声明的@SqlResultSetMapping,用于返回多个实体和或实体与标量混合的查询
)
等价XML
<entity-mappings>
<named-native-query name=” getAverageReservateion” result-set-mapping=”….”>
<query>
原生SQL
</query>
</ named-native-query >
</entity-mappings >
使用方式
Query query=em.createNamedQuery(“getAverageReservateion”);
query.setParameter(“cruise”.cruise);
@SqlResultSetMapping(name=”customerAndCreditCardMapping”,//映射名
entities={@EntityResult(entityClass=Customer.class),//entities指定映射的实体
@EntityResult(entityClass=CreditCard.class,
fields={@FieldResult(name=”id”,column=”CC_ID”),//指定实体内成员与返回字段的映射
@FieldResult(name=”number”,column=”number”)})
})
public class Customer{………}
等价XML
<entity-mappings>
<sel-result-set-mapping name=” customerAndCreditCardMapping”>
<entity-result entity-class=”com.titan.domain.Customer”/>
<entity-result entity-class=”com.titan.domain.CreditCard”>
<field-result name=”id” column=”CC_ID”/>
<field-result name=”number” column=”number”/>
</entity-result>
</sel-result-set-mapping>
</entity-mappings>
使用方法
String sql=”select c.id,c.firstName,cc.id As CC_IC,cc.number from CUST_TABEL
c,CREDIT_CARD_TABLE cc….”
manager.createNativeQuery(sql,”customerAndCreditCardMapping”);
返回结果既有标量也有实体
@SqlResultSetMapping(name=”reservationCount”,//映射名
entities={@EntityResult(entityClass=Cruise.class,
fields={@FieldResult(name=”id”,column=”id”) })},
columns={@ColumnsResult(name=”resCount”)}//标量
)
@Entity
public class Cruise{……}
等价XML
<entity-mappings>
<sel-result-set-mapping name=” reservationCount”>
<entity-result entity-class=”com.titan.domain.Cruise”>
<field-result name=”id” column=”id”/>
</entity-result>
<column-result name=”resCount”>
</sel-result-set-mapping>
</entity-mappings>
使用方法
String sql=”select c.id,count(Resrvation.id) as resCount from Cruise c left join
Reservation on c.id=………..”;
manager.createNativeQuery(sql,” reservationCount”);
具名EJB QL查询
预先定义好EJB QL或原生的SQL查询
使用@javax.persistence.NamedQuery用来预定EJB QL的,NamedQuerys用于定义多条查询
@NamedQuerys({
NamedQuery(name=”getAverageReservateion”,//名字
query=”select AVG(r.amountPaid) from Cruise As c Join c.reservatons r where
c=:cruise”),//EJB QL
NamedQuery(……)
})
@Entity
public class Cruise{…….}
等价XML
<entity-mapping>
<named-query name=” getAverageReservateion”>
<query>
EJB QL
</query>
</ named-query >
</entity-mapping >
使用方式
Query query=em.createNamedQuery(“getAverageReservateion”);
query.setParameter(“cruise”.cruise);
具名原生查询
使用@javax.persistence.NamedNativeQuery注解来预定义具名原生SQL查询
@NamedNativeQuery(
name=”findCustAndCCNum”,
query=”select ……..”,//原生SQL
resultClass=”…”,//只返回一个实体时用
resultSetMapping=”….”
//一个声明的@SqlResultSetMapping,用于返回多个实体和或实体与标量混合的查询
)
等价XML
<entity-mappings>
<named-native-query name=” getAverageReservateion” result-set-mapping=”….”>
<query>
原生SQL
</query>
</ named-native-query >
</entity-mappings >
使用方式
Query query=em.createNamedQuery(“getAverageReservateion”);
query.setParameter(“cruise”.cruise);