OpenEJB 下原生SQL查询 随机会员

功能： 根据numbers 多少，在表user里随机查询出多少人。
?
补充下其他原生SQL：【我采用的是第二种：简单实体的原生查询】
?
原生查询
EntityManager接口有三个创建原生查询的方法：一个返回标量值，一个返回实体类型，还有一个返回多个实体与标量值组合

标量原生查询
createNativeQuery(String sql);

简单实体的原生查询
createNativeQuery(String sql,ClassentityClass);根据某个实体定义好的映射元数据，将返回值映射到该实体
?
复杂原生查询
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 classCustomer{………}
?????? 等价XML
??????<entity-mappings>
?????????<sel-result-set-mapping name=”customerAndCreditCardMapping”>
????????????<entity-resultentity-class=”com.titan.domain.Customer”/>
????????????<entity-resultentity-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>
?????? 使用方法
?????? Stringsql=”select c.id,c.firstName,cc.id As CC_IC,cc.number fromCUST_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 classCruise{……}
?????? 等价XML
??????<entity-mappings>
?????????<sel-result-set-mapping name=”reservationCount”>
????????????<entity-resultentity-class=”com.titan.domain.Cruise”>???
???????????????<field-result name=”id”column=”id”/>
????????????</entity-result>
????????????<column-result name=”resCount”>
?????????</sel-result-set-mapping>
??????</entity-mappings>
?????? 使用方法
?????? Stringsql=”select c.id,count(Resrvation.id) as resCount from Cruise cleft join
?????????? Reservationon c.id=………..”;
??????manager.createNativeQuery(sql,” reservationCount”);

具名EJB QL查询
??? 预先定义好EJBQL或原生的SQL查询
???使用@javax.persistence.NamedQuery用来预定EJBQL的,NamedQuerys用于定义多条查询
???@NamedQuerys({
??????NamedQuery(name=”getAverageReservateion”,//名字
?????????query=”select AVG(r.amountPaid) from Cruise As c Join c.reservatonsr where
????????????c=:cruise”),//EJB QL
??????NamedQuery(……)
??? })
???@Entity
??? public classCruise{…….}
??? 等价XML
???<entity-mapping>
??????<named-query name=”getAverageReservateion”>
?????????<query>
???????????? EJB QL
?????????</query>
??????</ named-query >
???</entity-mapping >
??? 使用方式
??? Queryquery=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 >
??? 使用方式
??? Queryquery=em.createNamedQuery(“getAverageReservateion”);
???query.setParameter(“cruise”.cruise);