?? querydaobean.java
字號:
String maxAge = result.toString();
//獲取平均年齡
query = em.createQuery("select avg(p.age) from Person p");
result = query.getSingleResult();
String avgAge = result.toString();
//獲取最小年齡
query = em.createQuery("select min(p.age) from Person p");
result = query.getSingleResult();
String minAge = result.toString();
//獲取總人數
query = em.createQuery("select count(*) from Person p");
result = query.getSingleResult();
String countperson = result.toString();
//獲取年齡總和
query = em.createQuery("select sum(p.age) from Person p");
result = query.getSingleResult();
String sumage = result.toString();
StringBuffer out = new StringBuffer("*************** QueryConstructor 結果打印 ****************<BR>");
out.append("最大年齡："+ maxAge+ "<BR>");
out.append("平均年齡："+ avgAge+ "<BR>");
out.append("最小年齡："+ minAge+ "<BR>");
out.append("總人數："+ countperson+ "<BR>");
out.append("年齡總和："+ sumage+ "<BR>");
return out.toString();
}
private String QueryGroupBy(){
//返回男女生各自的總人數
Query query = em.createQuery("select p.sex, count(*) from Person p group by p.sex");
//集合中的元素不再是Person,而是一個Object[]對象數組
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryGroupBy 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
while( iterator.hasNext() ){
//取每一行
Object[] row = (Object[]) iterator.next();
//數組中的第一個值是sex
boolean sex = Boolean.parseBoolean(row[0].toString());
//數組中的第二個值是聚合函數COUNT返回值
String sextotal = row[1].toString();
out.append((sex ? "男生":"女生")+ "總共有"+ sextotal+ "人<BR>");
}
}
return out.toString();
}
private String QueryGroupByHaving(){
//返回人數超過1人的性別
Query query = em.createQuery("select p.sex, count(*) from Person p group by p.sex having count(*) >?1");
//設置查詢中的參數
query.setParameter(1, new Long(1));
//集合中的元素不再是Person,而是一個Object[]對象數組
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryGroupByHaving 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
while( iterator.hasNext() ){
//取每一行
Object[] row = (Object[]) iterator.next();
//數組中的第一個值是sex
boolean sex = Boolean.parseBoolean(row[0].toString());
//數組中的第二個值是聚合函數COUNT返回值
String sextotal = row[1].toString();
out.append((sex ? "男生":"女生")+ "總共有"+ sextotal+ "人<BR>");
}
}
return out.toString();
}
private String QueryLeftJoin(){
//獲取26歲人的訂單,不管Order中是否有OrderItem
Query query = em.createQuery("select o from Order o left join o.orderItems where o.ower.age=26 order by o.orderid");
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryLeftJoin 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
Integer orderid = null;
while( iterator.hasNext() ){
Order order = (Order) iterator.next();
if (orderid==null || !orderid.equals(order.getOrderid())){
orderid = order.getOrderid();
out.append("訂單號："+ orderid+ "<BR>");
}
}
}
return out.toString();
}
private String QueryInnerJoin(){
//獲取26歲人的訂單,Order中必須要有OrderItem
Query query = em.createQuery("select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid");
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryInnerJoin 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
Integer orderid = null;
while( iterator.hasNext() ){
Order order = (Order) iterator.next();
if (orderid==null || !orderid.equals(order.getOrderid())){
orderid = order.getOrderid();
out.append("訂單號："+ orderid+ "<BR>");
}
}
}
return out.toString();
}
private String QueryInnerJoinLazyLoad(){
// 默認EJB3 QL編譯后不關聯集合屬性變量(orderItems)對應的表
Query query = em.createQuery("select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid");
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryInnerJoinLazyLoad 結果打印 ****************<BR>");
if (result!=null){
if( result.size()>0){
//這時獲得Order實體中orderItems( 集合屬性變量 )為空
Order order = (Order) result.get(0);
//當應用需要時，EJB3 Runtime才會執行一條SQL語句來加載屬于當前Order的OrderItems
Set<OrderItem> list = order.getOrderItems();
Iterator<OrderItem> iterator = list.iterator();
if (iterator.hasNext()){
OrderItem orderItem =iterator.next();
out.append("訂購產品名："+ orderItem.getProductname()+ "<BR>");
}
}
}
return out.toString();
}
private String QueryJoinFetch(){
//獲取26歲人的訂單,Order中必須要有OrderItem
Query query = em.createQuery("select o from Order o inner join fetch o.orderItems where o.ower.age=26 order by o.orderid");
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryJoinFetch 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
Integer orderid = null;
while( iterator.hasNext() ){
Order order = (Order) iterator.next();
if (orderid==null || !orderid.equals(order.getOrderid())){
orderid = order.getOrderid();
out.append("訂單號："+ orderid+ "<BR>");
}
}
}
return out.toString();
}
private String QueryEntityParameter(){
//查詢某人的所有訂單
Query query = em.createQuery("select o from Order o where o.ower =?1 order by o.orderid");
Person person = new Person();
person.setPersonid(new Integer(1));
//設置查詢中的參數
query.setParameter(1,person);
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryEntityParameter 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
Integer orderid = null;
while( iterator.hasNext() ){
Order order = (Order) iterator.next();
if (orderid==null || !orderid.equals(order.getOrderid())){
orderid = order.getOrderid();
out.append("訂單號："+ orderid+ "<BR>");
}
}
}
return out.toString();
}
private String QueryBatchUpdate(){
//把所有訂單的金額加10
Query query = em.createQuery("update Order as o set o.amount=o.amount+10");
//update的記錄數
int result = query.executeUpdate();
StringBuffer out = new StringBuffer("*************** QueryBatchUpdate 結果打印 ****************<BR>");
out.append("更新操作影響的記錄數："+ result+ "條<BR>");
return out.toString();
}
private String QueryBatchRemove(){
//把金額小于100的訂單刪除
Query query = em.createQuery("delete from Order as o where o.amount <100");
//delete的記錄數
int result = query.executeUpdate();
StringBuffer out = new StringBuffer("*************** QueryBatchRemove 結果打印 ****************<BR>");
out.append("刪除操作影響的記錄數："+ result+ "條<BR>");
return out.toString();
}
private String QueryNOTOperate(){
//查詢除了指定人之外的所有訂單
Query query = em.createQuery("select o from Order o where not(o.ower =?1) order by o.orderid");
Person person = new Person();
person.setPersonid(new Integer(2));
//設置查詢中的參數
query.setParameter(1,person);
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryNOTOperate 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
Integer orderid = null;
while( iterator.hasNext() ){
Order order = (Order) iterator.next();
if (orderid==null || !orderid.equals(order.getOrderid())){
orderid = order.getOrderid();
out.append("訂單號："+ orderid+ "<BR>");
}
}
}
return out.toString();
}
private String QueryBETWEENOperate(){
//查詢金額在300到1000之間的訂單
Query query = em.createQuery("select o from Order as o where o.amount between 300 and 1000");
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryBETWEENOperate 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
Integer orderid = null;
while( iterator.hasNext() ){
Order order = (Order) iterator.next();
if (orderid==null || !orderid.equals(order.getOrderid())){
orderid = order.getOrderid();
out.append("訂單號："+ orderid+ "<BR>");
}
}
}
return out.toString();
}
private String QueryINOperate(){
//查找年齡為26,21的Person
Query query = em.createQuery("select p from Person as p where p.age in(26,21)");
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryINOperate 結果打印 ****************<BR>");
if (result!=null){
Iterator iterator = result.iterator();
while( iterator.hasNext() ){
Person person= (Person)iterator.next();
out.append(person.getName()+ "<BR>");
}
}
return out.toString();
}
private String QueryLIKEOperate(){
//查找以字符串"li"開頭的Person
Query query = em.createQuery("select p from Person as p where p.name like 'li%'");
List result = query.getResultList();
StringBuffer out = new StringBuffer("*************** QueryLIKEOperate 結果打印 ****************<BR>");
if (result!=null){
out.append("---------- 查找以字符串\"li\"開頭的Person ----------<BR>");
Iterator iterator = result.iterator();
while( iterator.hasNext() ){
Person person= (Person)iterator.next();
out.append(person.getName()+ "<BR>");
}
}
//可以結合NOT一起使用，比如查詢所有name不以字符串"ming"結尾的Person
query = em.createQuery("select p from Person as p where p.name not like '%ming'");
result = query.getResultList();
if (result!=null){
out.append("---------- 查詢所有name不以字符串\"ming\"結尾的Person ----------<BR>");
Iterator iterator = result.iterator();
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -