Java日期格式化及其使用例子收集

1 SimpleDateFormat担当重任,怎样格式化都行
import java.util.Date;
import java.text.SimpleDateFormat;
publicclass Demo
{
publicstaticvoid main(String[] args)
{
 Date now=new Date();
 SimpleDateFormat f=newSimpleDateFormat("今天是"+"yyyy年MM月dd日 E kk点mm分");
 System.out.println(f.format(now));
 
 f=new SimpleDateFormat("a hh点mm分ss秒");
 System.out.println(f.format(now));
 }
}
                                 
2 从字符串到日期类型的转换：
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.GregorianCalendar;
import java.text.*;
publicclass Demo
{
publicstaticvoid main(String[] args)
{
 String strDate="2005年04月22日";
 //注意：SimpleDateFormat构造函数的样式与strDate的样式必须相符
 SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy年MM月dd日");
 //必须捕获异常try
 {
  Date date=simpleDateFormat.parse(strDate);
  System.out.println(date);
 }
 catch(ParseException px)
 {
  px.printStackTrace();
 }
}
}
                                 
3 将毫秒数换转成日期类型
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.GregorianCalendar;
import java.text.*;
publicclass Demo
{
publicstaticvoid main(String[] args)
{
 long now=System.currentTimeMillis();
 System.out.println("毫秒数："+now);
 Date dNow=new Date(now);
 System.out.println("日期类型："+dNow);
}
}
这3例源自http://blog.csdn.net/zhoujian2003/archive/2005/04/22/358363.aspx
 
4 获取系统时期和时间，转换成SQL格式后更新到数据库
(http://blog.csdn.net/netrope/archive/2005/11/19/532729.aspx)
java.util.Date d=new java.util.Date();    //获取当前系统的时间new java.text.SimpleDateFormat s= new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //格式化日期
String dateStr = s.format(d); //转为字符串                
使用RS更新数据库，仍然要用rs.updateString，而不是rs.updateDade。
rs.updateString("regtime",dateStr);  //regtime字段为datetime类型的
下面两例源自 http://blog.csdn.net/kingter520/archive/2004/10/27/155435.aspx
 
5 按本地时区输出当前日期
Date myDate = new Date();
System.out.println(myDate.toLocaleString());
输出结果为：
2003-5-30
                                 
 
6 如何格式化小数
DecimalFormat df = new DecimalFormat(",###.00");  
double aNumber = 33665448856.6568975;
String result = df.format(aNumber);  
Sytem. out.println(result);
               
输出结果为：
33,665,448,856.66
其他：获取毫秒时间 System.currentTimeMillis();
 
7 在数据库里的日期只以年-月-日的方式输出
(http://blog.csdn.net/zzsxvzzsxv/archive/2007/08/27/1761004.aspx)
定义日期格式：SimpleDateFormat sdf = new SimpleDateFormat(yy-MM-dd);
sql语句为：String sqlStr = "select bookDate from roomBook where bookDate between '2007-4-10' and '2007-4-25'";
输出：
System.out.println(df.format(rs.getDate("bookDate")));
 
8 经典例子(http://blog.csdn.net/donkeyzheng/archive/2005/12/30/566470.aspx)
Date date = from.getAfmdate();
if (null != date) {
     SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy");
     SimpleDateFormat monthFormat = new SimpleDateFormat("MM");
     SimpleDateFormat dayFormat = new SimpleDateFormat("dd");
     to.setAfmYear(yearFormat.format(date));
     to.setAfmMonth(monthFormat.format(date));
     to.setAfmDay(dayFormat.format(date));
   }
   SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
   appPo.setAfmdate(format.parse(appForm.getAfmYear() + "-" +
                                 appForm.getAfmMonth() + "-" +
                                 appForm.getAfmDay()));

使 OpenOffice.org 运行更快的技巧

OpenOffice.org 是我目前在 Linux 下所用的主力办公软件，但它有一个令人诟病的地方：启动时总是慢吞吞的，这让人十分不爽。今天在 Zolved 上读到一篇可使 OpenOffice.org 提速的技巧文章，在试验后感觉效果很明显，特介绍给大家分享。

首先，你需要启动 OpenOffice.org Writer，在设置后，其他的 Calc、Impress、Draw 等组件也会生效。接着，执行"工具->选项"菜单命令，选择左边的"内存"，设置要点如下：

• 在"撤销"选项，步骤数目建议设置为 20 或 30，总之应小于 100。
• 在"图形缓存"选项，OpenOffice.org 使用设置为 128MB，每个对象的内存设置为 20MB。
• 在"插入对象缓存"选项，设置对象数为 20。
• 选中"启用快速启动"选项。

然后，再选择左边的"Java"，取消选择"使用 Java 运行环境"选项。最后点击"确定"保存你的设置。现在你可以关闭 OpenOffice.org 并再次启动它以感受速度的变化了。

十一种刷新按钮的方法

十一种刷新按钮的方法:
<input type=button value=刷新 onclick="history.go(0)">
<input type=button value=刷新 onclick="location.reload()">
<input type=button value=刷新 onclick="location=location">
<input type=button value=刷新 onclick="location.assign(location)">
<input type=button value=刷新 onclick="document.execCommand('Refresh')">
<input type=button value=刷新 onclick="window.navigate(location)">
<input type=button value=刷新 onclick="location.replace(location)">
<input type=button value=刷新 onclick="window.open('自身的文件','_self')">
<input type=button value=刷新 onClick=document.all.WebBrowser.ExecWB(22,1)>
<OBJECT classid=CLSID:8856F961-340A-11D0-A96B-00C04FD705A2 height=0 id=WebBrowser width=0></OBJECT>
<form action="自身的文件">
<input type=submit value=刷新>
</form>
<a id=a1 href="自身的文件"></a>
<input type=button value=刷新 onclick="a1.click()">

EJB3 - JPA - Query

EJB3 - JPA - Query

Description
JPA的Query是很重要又方便的�西, 不�要注意�量不要把Query和一般的entity�理方式放一起.
因�JPA�要求persistence provider必��Query和persistence context同步, 所以�在就常看到一些因�使用Query�致使用者需要先flush才能Query的�象.
Reference
EJB3 in Action - CH10 - Using the query and JPQL to retrieve entities
Notes

• Query物件由EntityManager建立

1.        createNamedQuery可用JPQL也可放native SQL
2.        createNativeQuery(String sqlString)用� UPDATE 或 DELETE
3.        createNativeQuery(String sqlString, Class result-class)用�取得single entity type
4.        createNativeQuery(String sqlString, String result-setMapping)用�取得multiple entity types

• named query可用annotation定�在entity上或在orm xml上, 只要是多�地方�使用到的query都�合用作named query
• named query 可增加效能, 因�他��一次之後就可有效率的重�使用
• 使用query不需要transaction, 在�有transaction的情�下使用query��取得的entity detach.
• 一�named query的name在persistence unit中必�是唯一的, 所以要注意命名
• 用@NamedQuery或@NamedQueries

@Entity
@NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer")
public class Programmer implements Serializable {...}
       
@Entity
@NamedQueries(
   @NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer")
)
public class Programmer implements Serializable {...}

@Entity
@NamedQueries({
   @NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer"),
   @NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer")
})
public class Programmer implements Serializable {...}
   

• 使用named query的方式

Query q = em.createNamedQuery(Programmer.JPQL_FIND_ALL_PROGRAMMER);

• 使用dynamic query的方式

Query q = em.createQuery("from Programmer");

• 取得Query物件後, �特�的method

1.        setParameter(String name, Object value)�定named paramater
2.        setParameter(String name, Date value, TemporalType temporalType)�定Date型�的named parameter
3.        setParameter(String name, Calendar value, TemporalType temporalType)�定Calendar型�的named parameter
4.        setParameter(int position, Object value)�定特定位置parameter的值
5.        setParameter(int position, Date value, TemporalType temporalType)�定特定位置Date型�parameter的值
6.        setParameter(int position, Calendar value, TemporalType temporalType)�定特定位置Calendar型�parameter的值

• 使用position的���定方式, 建�用named parameter比�好debug

private void useParametericQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select p from Programmer p where p.id >= ?100 AND p.id <= ?70");
   /* 如果指定��, 例如放65.
    * �出�java.lang.IllegalArgumentException:
    * org.hibernate.QueryParameterException:
    * could not locate named parameter [65] */
   q.setParameter(70, Long.valueOf(100));
   q.setParameter(100, Long.valueOf(50));
   List< Programmer > resultList = q.getResultList();
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getId() + ":" + programmer.getName());
   }
}
   

• 使用named parameter的Query (建�用法)

private void useNamedParametericQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select p from Programmer p " +
                   "where p.id >= :lowerID and p.id <= :higherID");
   q.setParameter("lowerID", Long.valueOf(30));
   q.setParameter("higherID", Long.valueOf(80));
   List< Programmer > resultList = q.getResultList();
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getId() + ":" + programmer.getName());
   }
}
   

• 使用getSingleResult取得一�entity.

1.        如果query�有entity��NoResultException, 若有多��NonUniqueResultException. 所以使用�要�定只�搜�到一�.
2.        由於NonUniqueResultException�NoResultException不�roll back transaction, 所以若有�理transaction要�得�理exception的�怎��.
private void useNoResultExceptionQuery(String pname) {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select p from Programmer p where p.name = :pname");
   q.setParameter("pname", pname);
   try {
       System.out.println(q.getSingleResult());
   } catch ( NoResultException nre ) {
       System.out.println("no result");
       nre.printStackTrace();
   } catch ( NonUniqueResultException nure ) {
       System.out.println("not unique result");
       nure.printStackTrace();
   }
}
       

• 用getResultList取得多�entities

1.        如果�有符合的就回�空List, 不�有exception.
2.        透�setMaxResults可�定一�最大query量
3.        透�setFirstResult可�定要�哪一��始查
private void usePaginationQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createNamedQuery(Programmer.JPQL_FIND_ALL_PROGRAMMER);
   q.setMaxResults(10);

   int resultCount = 0;
   while ( true ) {
       q.setFirstResult(resultCount);
       List< Programmer > resultList = q.getResultList();
       if ( resultList.size() == 0) {
           System.out.println("result end..");
           break;
       }
       resultCount += resultList.size();
       System.out.println("getResultList...");
       for (Programmer programmer : resultList) {
           System.out.println(programmer.getName());
       }
       System.out.println("next page...");
   }
}
       

• �定Query的FlushMode

1.        FlushModeType.AUTO: �是��值, ��Query�行前先把EntityManager中�entity的�理commit再query.
private void useAutoCommit() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   // 如果�有begin就不��上有AUTO COMMIT的效果, 就是有 INSERT 的 statement
   em.getTransaction().begin();
   System.out.println("transaction begin");
   prepareProgrammer(em, 999); // 在�新增一�Programmer但�commit
   System.out.println("prepare programmer finished");
   Query q = em.createQuery("from Programmer");
   System.out.println("prepare Query instance");
   
   // 到 getResultList 才� auto-commit 然後 select
   // ��候 query 的�果�是�理完entity的�果
   List< Programmer > resultList = q.getResultList();
   System.out.println("getResultList");
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getName());
   }
   
   // 可是如果最後��行commit�是不�把entity的�理放�DB
   em.getTransaction().commit();
}                
           
2.        FlushModeType.COMMIT: ��mode�有�定provider要怎��做. ��Hibernate是Query的�果不�包含�理�的entity.
private void useNotAutoCommit() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   em.getTransaction().begin();
   System.out.println("transaction begin");
   prepareProgrammer(em, 999);
   System.out.println("prepare programmer finished");
   Query q = em.createQuery("from Programmer");
   q.setFlushMode(FlushModeType.COMMIT);
   System.out.println("prepare Query instance");
   // ��候query的�果不�包含新增的Programmer, 因�FlushMode改�COMMIT
   List< Programmer > resultList = q.getResultList();
   System.out.println("getResultList");
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getName());
   }
}
           

• 用Query�行update

1.        JPQL 的 update 格式
UPDATE entityName indentifierVariable
SET single_value_path_expression1 = value1, ...
single_value_path_expressionN = valueN
WHERE where_clause
           
2.        使用JPQL update
private void useUpdateQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("update Programmer p set " +
                   "p.name = 'QQ 100' where p.name = 'QQ 999'");

   // �行Query的update需要transaction
   em.getTransaction().begin();
   q.executeUpdate();
   em.getTransaction().commit();
}
           

• 使用Query�行delete

1.        JPQL的delete格式
DELETE entityName indentifierVariable
WHERE where_clause
           
2.        使用JPQL delete
private void useDeleteQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("delete Certification c where c.name like 'SCWCD%'");

   // �有transaction�
   // javax.persistence.TransactionRequiredException: Executing an update/delete query
   em.getTransaction().begin();
   q.executeUpdate();
   em.getTransaction().commit();
}
           

• 替entity 命名

1.        @Entity可以命名, 如果�有替entity命名, 名�就�跟entity class name一�, 注意如果�有替entity的table命名, table名�也�跟entity name一�, 所以�用@Table�替entity命名的�, 一旦改�entity class name就�出�找不到table的exception
2.        在一�persistence unit中, entity name必�是唯一的, 否�在deploy�段就�出���
3.        下面��的named query�取得Certification的�料, �然entity name取得差很多
@Entity(name="Programmer2")
@Table(name="CERTIFICATION")
@NamedQueries({
   @NamedQuery(name=Certification.JPQL_FIND_ALL_CERT, query="from Certification as c")
})
public class Certification implements Serializable { ... }            
           

• 使用identifier variable

1.        使用JPQL��用到如
FROM Certification AS c
, 其中的 c 就是identifier variable
2.        identifier variable不能是同�persistence unit的entity name也不能是JPQL的保留字
3.        JPQL有保留字如下
SELECT, UPDATE, DELETE, FROM, WHERE, GROUP, HAVING, ORDER, BY, ASC, DESC

JOIN, OUTER, INNER, LEFT, FETCH

DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS,
UNKNOWN, EMPTY, MEMBER, OF, IS, NEW, EXISTS, ALL, ANY, SOME

AVG, MAX, MIN, SUM, COUNT, MOD, UPPER, LOWER, TRIM, POSITION, CHARACTER_LENGTH,
CHAR_LENGTH, BIT_LENGTH, CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
           

• path expression

1.        from Programmer p where p.name = 'KK' 的 p.name就是一�path expression
2.        path expression就是identifier variable和persistence或相��位�用"."�起�
3.        用"."�起�的相���可以是一��一值(one-to-one, many-to-one)或一�collection(many-to-many, one-to-many).
4.        many-to-many, one-to-many的用法�
from Programmer p where p.certs is not empty
5.        single-value中one-to-one的用法就是直接在"."之後加上�位即�single-value
6.        � 法在many-to-one的情形下使用"."取得�位值, 比方�certificationCenter.sentCertifications.programmer, 因�sentCertifications是collection, �不知道要取得的programmer是哪一�而�生��

• where clause

1.        使用where的�候�乎所有的java literal都可用, 但是八�和十六�位的�字不行, byte[] 或 char[]也不行.

• Conditional�operator

1.        JPQL支援的operator如下
.

+, -

+, -, *, /

=, >, >=, <=, <>,
[not] bewteen, [not] like, [not] in, is [not] null, is [not] empty, [not] member [of]

NOT, AND, OR
           
2.        使用between的���性必�是同一�型�
private void useQueryBetween() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where c.createDate between ?0 and ?1");
   Calendar date1 = Calendar.getInstance(); date1.set(1998, 0, 1);
   Calendar date2 = Calendar.getInstance(); date2.set(2000, 0, 1);
   q.setParameter(0, date1);
   q.setParameter(1, date2);
   List< Certification > list = q.getResultList();

   q = em.createQuery("select count(c) from Certification c " +
                   "where c.createDate between ?0 and ?1");
   q.setParameter(0, date1);
   q.setParameter(1, date2);
   Long count = (Long) q.getSingleResult();

   System.out.println(Long.valueOf(list.size()).equals(count));  // true
}
           
3.        使用in�可用('A','B'), 就是把要判�的值放�括�中或是在括��放�sub query�. �一�query有subquery�, �先�行subquery再�行query
private void useQueryIn() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where" +
                   " c.name in ('SCJP 0', 'SCJP 1', 'SCJP 2')");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   q = em.createQuery("from Certification c " +
               "where c.name not in ('SCJP 0', 'SCJP 1', 'SCJP 2')");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   q = em.createQuery("from Certification c " +
               "where c.programmer in (from Programmer p where p.id > 600)");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName() +
                   ":" + certification.getProgrammer().getId());
   }

   q = em.createQuery("from Certification c where " +
               "c.name in (select c.name from c where c.id > 600)");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getId() + ":" + certification.getName());
   }
}
           
4.        使用like判�一��位是否符合某�字串格式, 可用底�(_)代表一�字或用百分�(%)代表任意��的字
private void useQueryLike() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where c.name like '%_P_9_'");
   List< Certification > list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where c.name not like '%_P_9_'");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }
}                
           
5.        JPQL定�的null和empty string是不同的, 但不是所有DB都��, 使用DB前要先����清楚.
�conditional expression遇到null�果�是 null 或 unknown.
不能用is null�判�一�collection是否�空collection. is null也不�判�一�collection是否�空.
�於collection, JPQL是透�empty�判�是否�空collection.
由於SQL中��有is empty���西, 所以JPQL的is empty其�是透�JOIN的方式去找看相��的entity collection是否�空.
TRUE  AND null = UNKNOWN
FALSE AND null = FALSE
Null  AND null = UNKNOWN
TRUE  OR  null = TRUE
Null  OR  null = UNKNOWN
FALSE OR  null = UNKNOWN
     NOT null = UNKNOWN
           
private void useQueryNull() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where c.name is null");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   q = em.createQuery("from Programmer p where p.certs is empty");
   List< Programmer > programmers = q.getResultList();
   for (Programmer programmer : programmers) {
       System.out.println(programmer.getName() + ":" + programmer.getCerts().size());
   }
}
           
6.        member的用法是
entity_expression [NOT] MEMBER [OF] collection_value_path_expression
, 如下所示
private void useQueryMember() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Certification c = em.find(Certification.class, Long.valueOf(114));
   Query q = em.createQuery("from Programmer p where ?0 member of p.certs");
   q.setParameter(0, c);
   List< Programmer > list = q.getResultList();
   for (Programmer programmer : list) {
       System.out.println(programmer.getCerts().contains(c));
   }
}
           

• 使用JPQL中字串�理的function. 因�java�理字串比放到DB才�理更快, 所以建�在JPQL�理字串.

private void useJPQLStringFunction() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where " +
                   "CONCAT('scwcd ', '14') = LOWER(c.name) ");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // substring第一�字母index就是一, 不像java�0�始
   q = em.createQuery("from Certification c where " +
               "UPPER(SUBSTRING('abcscwcd 14123', 4, 8)) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(BOTH ' ' FROM '   scwcd 14  ')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(LEADING ' ' FROM '   scwcd 14')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(TRAILING ' ' FROM 'scwcd 14  ')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(BOTH 'a' FROM 'aaaascwcd 14aaaaaaaaa')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where LENGTH(c.name) = 8");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   // LOCATE第一�字index也是1
   q = em.createQuery("from Certification c where LOCATE('wcd', LOWER(c.name)) = 3");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

}            
       

• 使用JPQL的arithmetic function

1.        有ABS(arithmetic_expression), SQRT(arithmetic_expression), MOD(num, div), SIZE(collection_value_path_expression)
private void useJPQLArithmeticFunction() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // ABS ��值
   Query q = em.createQuery("from Certification c where ABS(c.id) = 100");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // SQRT 平方根
   q = em.createQuery("from Certification c where SQRT(c.id) = 10");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // MOD ��
   q = em.createQuery("from Certification c where c.id = MOD(1100, 1000)");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // SIZE 量
   q = em.createQuery("from Programmer p where SIZE(p.certs) > 2");
   List< Programmer > programmers = q.getResultList();
   for (Programmer programmer : programmers) {
       System.out.println(programmer.getName());
   }
}                
           

• 使用JPQL的temporal function

1.        有 CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, 不����法使用CURRENT_TIME
private void useJPQLTemporalFunction() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // �DB 取DATE
   Query q = em.createQuery("select distinct CURRENT_DATE from Certification");
   System.out.println(q.getSingleResult());

   // �法使用CURRENT_TIME, �有
   //java.sql.SQLException: ORA-00904: "CURRENT_TIME": invalid identifier
   q = em.createQuery("select distinct CURRENT_TIME from Certification");
   System.out.println(q.getSingleResult());

   // �DB取得TIMESTAMP
   q = em.createQuery("select distinct CURRENT_TIMESTAMP from Certification");
   System.out.println(q.getSingleResult());
}
           

• 使用select

1.        select的�容可以用逗�分隔�一�以上的identifier variable 或 single-value path expression 或 aggregate functions.
private void useJPQLSelect() {
   EntityManager em = GeneralEAO.getEntityManager();
   Query q = null;

   // simple select clause
   q = em.createQuery("select p from Programmer p, Address a where a.employee.id = p.id");
   List< Programmer > simpleProgrammer = q.getResultList();
   for (Programmer programmer : simpleProgrammer) {
       System.out.println(programmer.getName());
   }

   // select distinct
   q = em.createQuery("select distinct(p) from Programmer p, " +
               "Address a where a.employee.id = p.id");
   List< Programmer > distinctProgrammer = q.getResultList();
   for (Programmer programmer : distinctProgrammer) {
       System.out.println(programmer.getName());
   }

   // select single property
   q = em.createQuery("select p.name from Programmer p where " +
               "p.payment = (select max(p.payment) from Programmer p)");
   List< String > oneProp = q.getResultList();
   for (String name : oneProp) {
       System.out.println(name);
   }

   // select two property
   q = em.createQuery("select p.name, p.payment from Programmer p " +
               "where p.payment = (select max(p.payment) from Programmer p)");
   List< Object[] > twoProp = q.getResultList();
   for (Object[] objects : twoProp) {
       String name = (String) objects[0];
       Double payment = (Double) objects[1];
       System.out.println(name + ":" + payment);
   }

   // select association entity
   q = em.createQuery("select n.owner.name, n from NoteBook n where " +
               "n.owner.payment = (select MAX(p.payment) from Programmer p)");
   List< Object[] > twoProp2 = q.getResultList();
   for (Object[] objects : twoProp2) {
       String name = (String) objects[0];
       NoteBook noteBook = (NoteBook) objects[1];
       System.out.println(name + ":" + noteBook.getMachineNumber() +
                   ":" + noteBook.getOwner().getName());
   }

   // �上好像�不能select collection的, 不����正常
   // select collection entities
   q = em.createQuery("select p.addressList from Programmer p");
   List< Address > addressList = q.getResultList();
   for (Address address : addressList) {
       System.out.println(address.getStreet());
   }

   // ��就�有�, 因�除了collection以外又加一�single-value
   try {
       q = em.createQuery("select p.name, p.addressList from Programmer p");
       List< Object > nameAddressList = q.getResultList();
       for (Object object : nameAddressList) {
           System.out.println(object);
       }
   } catch (Exception e) {
       System.out.println("select p.name, p.addressList has " +
               "exception because can't select single-value and collections." + e);
   }

   // new entity.StringObject�入的����不能是null, 而且要有�映的建�子. 型�也要一� 如下:
   // < code >
   //    public class StringObject {
   //    
   //    private String str1;
   //    private String str2;
   //    private Double double3;
   //
   //    public StringObject(String str1, String str2, Double double3) {
   // < /code >
   // ��的StringObject不用是entity也不用mapping到DB.
   q = em.createQuery("select new entity.StringObject(p.name, p.lang, p.payment)" +
               " from Programmer p");
   List< StringObject > stringObjectList = q.getResultList();
   for (StringObject stringObject : stringObjectList) {
       System.out.println(stringObject.getStr1());
       System.out.println(stringObject.getStr2());
       System.out.println(stringObject.getDouble3());
   }
   
   // Employee是abstract super class, 透�JPQL搜�可取得subclass
   List< Employee > employeeList = em.createQuery("from Employee").getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }
}                
           

• 使用aggregate functions

AVG:   Return Double
COUNT: Return Long
MAX:   Return persistence field type
MIN:   Return persistence field type
SUM:   Return Long or Double        
       
1.        ���function中, 除了COUNT可以放任何type以外, 其他都只能放persistence field.
private void useAggregateFunctions() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   /* 若放AVG(p.name)或SUM(p.name) �
    * javax.persistence.PersistenceException:
    * org.hibernate.exception.SQLGrammarException:
    * could not execute query */
   Query q = em.createQuery(
       "select AVG(p.id), MAX(p.id), MIN(p.id), SUM(p.id), COUNT(p), " +
                         "MAX(p.name), MIN(p.name),        COUNT(p.name) " +
                           "from Employee p");
   Object[] o = (Object[]) q.getSingleResult();
   Double idAvg = (Double) o[0];
   Long   idMax = (Long) o[1];
   Long   idMin = (Long) o[2];
   Long   idSum = (Long) o[3];
   Long   idCnt = (Long) o[4];
   String nameMax = (String) o[5];
   String nameMin = (String) o[6];
   Long   nameCnt = (Long) o[7];
   System.out.println("avg:" + idAvg);
   System.out.println("idMax:" + idMax);
   System.out.println("idMin:" + idMin);
   System.out.println("idSum:" + idSum);
   System.out.println("idCnt:" + idCnt);
   System.out.println("nameMax:" + nameMax);
   System.out.println("nameMin:" + nameMin);
   System.out.println("nameCnt:" + nameCnt);
}            

• 使用GROUP BY�HAVING

1.        使用group by的�候只允�aggregation functions.
2.        可用having再��
private void useGroupByAndHaving() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select c.owner.name, COUNT(c.id) from " +
               "Certification c group by c.owner.name");
   List< Object[] > resultList = q.getResultList();
   for (Object[] result : resultList) {
       String ownerName = (String) result[0];
       Long count = (Long) result[1];
       System.out.println(ownerName + " has " + count + " certs.");
   }

   q = em.createQuery("select c.owner.name, COUNT(c.id) from " +
               "Certification c group by c.owner.name having COUNT(c.id) = 4");
   resultList = q.getResultList();
   for (Object[] result : resultList) {
       String ownerName = (String) result[0];
       Long count = (Long) result[1];
       System.out.println(ownerName + " has " + count + " certs.");
   }

   q = em.createQuery("select c.owner.name, COUNT(c.id) from " +
               "Certification c where c.id < 40 group by c.owner.name " +
               "having COUNT(c.id) = 4");
   resultList = q.getResultList();
   for (Object[] result : resultList) {
       String ownerName = (String) result[0];
       Long count = (Long) result[1];
       System.out.println(ownerName + " has " + count + " certs.");
   }
}          
       

• 使用order by排序

1.        有asc, desc��
2.        �上����就是在order by中若使用path expression而不是只有identifier就必�在select clause中加上order by的�位.
比方�order by c.createDate, ��createDate就要包含在select子句中. 用Hibernate��的�候�����限制.
private void useOrderBy() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Sales s order by s.id desc, s.performance asc");
   List< Sales > list = q.getResultList();
   for (Sales sales : list) {
       System.out.println(sales.getId() + ":" + sales.getPerformance());
   }

   /* �上���是不�的, 因�order by s.id�包括在select clause中.
    * 不�Hibernate���可以 */
   q = em.createQuery("select s.performance from Sales s order by s.id desc");
   List< Double > performanceList = q.getResultList();
   for (Double performance : performanceList) {
       System.out.println(performance);
   }
}
       

• 使用subquery

1.        subquery用在where或having中��遇取得的�果. 注意在from子句不能用subquery
2.        subquery可搭配ANY, SOME, ALL使用, ANY�SOME意思相同
private void useSubQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // normal subquery
   Query q = em.createQuery("select c.owner from Certification c " +
               "where c.owner = (from Employee e where e.id = 9)");
   List< Employee > employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getName());
   }

   // IN : �查是否在subquery中
   q = em.createQuery("select c.owner from Certification c " +
               "where c.owner in (from Employee e where e.id < 50)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getName());
   }

   // EXISTS : �查是否有�料存在
   q = em.createQuery("select c.owner from Certification c " +
               "where exists (from Employee e where e.id = c.owner.id)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getName());
   }

   // ANY : 只要�任一�相比�符合即可. 可搭配 =, >, >=, <, <=, < >
   q = em.createQuery("select c.owner from Certification c " +
               "where c.owner.id > ANY(select e.id from Employee e where e.id < 20)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }

   // SOME : 只要�任一�相比�符合即可. 可搭配 =, >, >=, <, <=, < >
   q = em.createQuery("select c.owner from Certification c " +
               "where c.owner.id > SOME(select e.id from Employee e where e.id > 700)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }

   // ALL : 必��所有的�料比�相符. 可搭配 =, >, >=, <, <=, < >
   q = em.createQuery("select c.owner from Certification c " +
           "where c.owner.id > ALL(select e.id from Employee e where e.id < 700)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }

}        
       

• 使用join

1.        theta-join : �然是�有constraint的�位, 但因�在意�上是有��的, 所以也拿�作select.
2.        relationship join : 就是用entity�的��join. 可以把完全吻合的entity都找出�, 若相��的entity�空就不算match
3.        inner join的�法是 : [INNER] JOIN join_association_path_expression [AS] identification_variable
4.        outer join和inner join不一�的就是不一定�match所有�件, 就是即使相��的entity�空也算match.
5.        inner [outer] join fetch 可取得原本��lazy的相��性
private void useJoin() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // theta-join
   Query q = em.createQuery("select ae.name from " +
           "AnotherEmployee ae, Certification c where ae.id = c.owner.id group by ae.name");
   List< String > nameList = q.getResultList();
   for (String name : nameList) {
       System.out.println(name);
   }

   // inner join . "inner"　is optional.
   q = em.createQuery("select e from Employee e inner join e.noteBook");
   List< Employee > employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() +
               " noteBook is null: " + (employee.getNoteBook() == null));
   }

   // inner join. "inner" and "as" is optional
   q = em.createQuery("select n from Employee e inner join e.noteBook as n");
   List< NoteBook > noteBookList = q.getResultList();
   for (NoteBook noteBook : noteBookList) {
       System.out.println(noteBook.getOwner().getId());
   }

   // outer join.
   q = em.createQuery("select e from Employee e left outer join e.noteBook");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() +
               " noteBook is null: " + (employee.getNoteBook() == null) );
   }

   /* both join �有java.lang.IllegalArgumentException:
    * org.hibernate.hql.ast.QuerySyntaxException:
    * unexpected token: both near line 1, column 33
    * [select e from entity.Employee e both join e.noteBook] */
   q = em.createQuery("select e from Employee e both join e.noteBook");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() + " noteBook is null: " +
               (employee.getNoteBook() == null) );
   }

   /**
    * right outer join 有 java.lang.UnsupportedOperationException:
    * join type not supported by OracleJoinFragment (use Oracle9Dialect)
    */
   q = em.createQuery("select e from Employee e right outer join e.noteBook");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() + " noteBook is null: " +
               (employee.getNoteBook() == null) );
   }

   /**
    * 用join fetch可直接透�Query取得lazy的相�型�
    * reference: http://edocs.bea.com/kodo/docs41/full/html/ejb3_langref.html
    *                       #ejb3_langref_fetch_joins
    */
   q = em.createQuery("select e from Employee e inner join fetch e.certs");
   employeeList = q.getResultList();
   em.clear(); // ��才可以知的detach
   for (Employee employee : employeeList) {
       System.out.println(employee.getCerts());
   }

}
       

• 使用update和delete

1.        update, delete一定要在transaction中
2.        update, delete也可以加��
3.        建�把update, delete�立於平常的操作, 因�Query是否�persistence context是由provider�定, JPA��定要同步.
    private void useUpdateDelete() {
       EntityManager em = EntityManagerHelper.getEntityManager();
       Query q = em.createQuery("update Programmer p set p.lang = :lang");
       q.setParameter("lang", "Java");
       
       /**
        * �transaction�有javax.persistence.TransactionRequiredException:
        * Executing an update/delete query
        */
       em.getTransaction().begin();
       System.out.println("update..." + q.executeUpdate() + " records");
       em.getTransaction().commit();
       
       q = em.createQuery("delete NoteBook n where n.id < :lowID");
       q.setParameter("lowID", Long.valueOf(200));
       em.getTransaction().begin();
       System.out.println("delete..." + q.executeUpdate() + " records");
       em.getTransaction().commit();
   }            
       

• 使用Native SQL

1.        �量不要用Native SQL, 因�不portable.
2.        和delete, update一�, 由於��定要和persistence context同步, 因此最好把Query操作和一般的entity操作隔��.
3.        透�JPQL和Native SQL取得的Query在使用上�有不同. 在使用named query上也�有不同, 只是native sql的named query�有要求provider一定支援
4.        JPA不支援store procedure
private void useNativeSQL() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // simple native sql
   Query q = em.createNativeQuery("select e.EMPLOYEE_ID, e.EMPLOYEE_TYPE, " +
               "e.NAME from EMPLOYEE e");
   List< Object[] > objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       BigDecimal id = (BigDecimal) objAry[0];
       String type = (String) objAry[1];
       String name = (String) objAry[2];
       System.out.println(id + ":" + type + ":" + name);
   }

   // native sql with parameters
   q = em.createNativeQuery("select e.EMPLOYEE_ID, e.EMPLOYEE_TYPE, e.NAME " +
               "from EMPLOYEE e where e.EMPLOYEE_ID = :id");
   q.setParameter("id", Long.valueOf(1));
   objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       BigDecimal id = (BigDecimal) objAry[0];
       String type = (String) objAry[1];
       String name = (String) objAry[2];
       System.out.println(id + ":" + type + ":" + name);
   }

   // 指定所�的class, 注意�然SQL只�要select EMPLOYEE_ID, 透�Query�可取出整�owner.
   // 不���方式�有一次只能指定一�entity的��
   q = em.createNativeQuery("select NOTE_BOOK_ID, SERIAL_NO, EMPLOYEE_ID " +
               "from NOTE_BOOK where NOTE_BOOK_ID = 200", NoteBook.class);
   List< NoteBook > noteBookList = q.getResultList();
   for (NoteBook noteBook : noteBookList) {
       System.out.println(noteBook.getId() + ":" + noteBook.getSerialNO() +
               ":" + noteBook.getOwner());
   }

   /**
    * 透�指定SqlResultSetMapping即可同�用native sql取得��entity
    *  @SqlResultSetMapping(name="allNoteBooksAndCertifications",
    *      entities={
    *          @EntityResult(entityClass=NoteBook.class),
    *          @EntityResult(entityClass=Certification.class)})
    */
   q = em.createNativeQuery("select * from NOTE_BOOK n, CERTIFICATION c " +
           "where n.EMPLOYEE_ID = c.EMPLOYEE_ID and c.CERTIFICATION_ID = 202"
           , "allNoteBooksAndCertifications");
   objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       NoteBook noteBook = (NoteBook) objAry[0];
       Certification cert = (Certification) objAry[1];
       System.out.println(noteBook);
       System.out.println(cert);
   }

   /**
    * 透�在NoteBook上�定named query
    * @NamedNativeQueries({
    *      @NamedNativeQuery(name="all204NoteBook",
    *          query="select * from NOTE_BOOK where NOTE_BOOK_ID = 204",
    *          resultClass=NoteBook.class),
    *      @NamedNativeQuery(name="204NoteBooksAnd202Certifications",
    *          query="select * from NOTE_BOOK n, CERTIFICATION c " +
    *              "where n.EMPLOYEE_ID = c.EMPLOYEE_ID" +
    *              " and c.CERTIFICATION_ID = 202",
    *          resultSetMapping="allNoteBooksAndCertifications")
    * })
    */
   q = em.createNamedQuery("all204NoteBook");
   noteBookList = q.getResultList();
   for (NoteBook noteBook : noteBookList) {
       System.out.println(noteBook);
   }
   q = em.createNamedQuery("204NoteBooksAnd202Certifications");
   objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       NoteBook noteBook = (NoteBook) objAry[0];
       Certification cert = (Certification) objAry[1];
       System.out.println(noteBook);
       System.out.println(cert);
   }

}