[SQL] executeQuery

executeQuery

• Executes the SQL query in this PreparedStatement object

• and returns the ResultSet object generated by the query.

Returns:
a ResultSet object that contains the data produced by the query; never null

[JPA] ObjectDB Manual (02 - Entity Classes)

[JPA Persistable Types]

• JPA persistable types:
• User defined classes
• Entity classes
• Mapped superclasses
• Embeddable classes.
• Instances of an embeddable class can only be stored in the database as embedded objects, i.e. as part of a containing entity object.
• Instances of embeddable classes are always embedded in other entity objects and do not require separate space allocation and separate store and retrieval operations. Therefore, using embeddable classes can save space in the database and improve efficiency.
• Simple Java data types
• Primitive types
• Wrappers
• String
• Date and Math types.
• java.sql.Date - represents date only (e.g. 2010-12-31).
• java.sql.Time - represents time only (e.g. 23:59:59).
• java.sql.Timestamp - represents date and time (e.g. 2010-12-31 23:59:59).
• @Temporal(TemporalType.DATE)
• Multi value types
• Collections
• Maps
• Arrays.
• Miscellaneous types
• Enum types
• Serializable types (user or system defined).
• [Note] Only instances of entity classes can be stored in the database directly. Other persistable types can only be embedded in entity classes as fields.
• A portable JPA entity class:
• Should be a top-level class (i.e. not a nested / inner class).
• Should have a public or protected no-arg constructor.
• Cannot be final and cannot have final methods or final instance variables.
• Mapped superclasses are really only useful in applications that use an ORM-based JPA provider (such as Hibernate, TopLink, EclipseLink, OpenJPA, JPOX, DataNucleus, etc.).

[JPA Entity Fields]

Fields of persistable user defined classes can be classified into the following five groups:

• Transient fields
• Do not participate in persistence and their values are never stored in the database
• Static and final entity fields are always considered to be transient
• Persistent fields
• Every persistent field can be marked with one of the following annotations:
• OneToOne, ManyToOne - for references of entity types.
• OneToMany, ManyToMany - for collections and maps of entity types.
• Basic - for any other persistable type.
• Specifying optional=false causes an exception to be thrown on any attempt to store an entity with a null value in that field.
• Inverse fields
• Inverse (or mapped by) fields contain data that is not stored as part of the entity in the database, but is still available after retrieval by a special automatic query.
• The mappedBy element specifies that the field is an inverse field rather than a persistent field. The content of the field is not stored as part of outer entity. Instead, the inverse field is automatically populated when the outer entity is retrieved from the database.
• The mappedBy element defines a bidirectional relationship. In a bidirectional relationship, the side that stores the data is the owner. Only changes to the owner side affect the database, since the other side is not stored and calculated by a query.
• Inverse fields may improve efficiency when managing very large collections that are changed often.
• Primary key (ID) fields
• Version field
• Should be treated as read only by the application and no mutator methods should be written against them. Only one version field per entity class is allowed.

• The first three groups (transient, persistent and inverse fields) can be used in both entity classes and embeddable classes.
• The last two groups (primary key and version fields) can only be used in entity classes.

[JPA Primary Key]

* Entity Identification

• Only entity objects have primary keys.

* Automatic Primary Key

• By default the primary key is a sequential 64 bit number (long) that is set automatically by ObjectDB for every new entity object that is stored in the database.
• The primary key of the first entity object in the database is 1.
• Primary key values are not recycled when entity objects are deleted from the database.
• The @GeneratedValue annotation specifies that the primary key is automatically allocated by ObjectDB.

* Application Set Primary Key

• If an entity has a primary key field that is not marked with @GeneratedValue, automatic primary key value is not generated and the application is responsible to set a primary key by initializing the primary key field.

* Composite Primary Key

• A composite primary key consist of multiple primary key fields.
• When an entity has multiple primary key fields, JPA requires defining a special ID class that is attached to the entity class using the @IdClass annotation.

* Embedded Primary Key

• An alternate way to represent a composite primary key is to use an embeddable class.
• The entity contains a single primary key field that is annotated with @EmbeddedId and contains an instance of that embeddable class.

* Using Primary Keys for Object Clustering

• it is useful to choose a primary key that helps clustering entity objects in the database in an efficient way. This is especially useful when using queries that return large result sets.

[Auto Generated Values]

• Marking a field with the @GeneratedValue annotation specifies that a value will be automatically generated for that field.
• This is primarily intended for primary key fields but ObjectDB also supports this annotation for non-key numeric persistent fields as well.

* The Auto Strategy

• @GeneratedValue(strategy=GenerationType.AUTO)
• But AUTO is the default strategy

* The Identity Strategy

• The IDENTITY strategy also generates an automatic value during commit for every new entity object.
• The difference is that a separate identity generator is managed per type hierarchy, so generated values are unique only per type hierarchy.

* The Sequence Strategy

• Defining a named sequence
• The @SequenceGenerator annotation is used to define a sequence and accepts a name, an initial value (the default is 1) and an allocation size (the default is 50).
• The number of IDs in each allocation is specified by the allocationSize attribute.

• Using the named sequence in one or more fields in one or more classes
• Unlike AUTO and IDENTITY, the SEQUENCE strategy generates an automatic value as soon as a new entity object is persisted (i.e. before commit).
• This may be useful when the primary key value is needed earlier.

* The Table Strategy

• Is very similar to the SEQUENCE strategy
• ORM-based JPA providers (such as Hibernate, TopLink, EclipseLink, OpenJPA, JPOX, etc.) simulate a sequence using a table to support this strategy.
• Whereas the SEQUENCE strategy maintains the next sequence number to be used the TABLE strategy maintains the last value that was used.

[Index Definition]

• Using proper indexes the iteration can be avoided and complex queries over millions of objects can be executed quickly.
• Index management introduces overhead in terms of maintenance time and storage space.

* Single Field Index

• @Unique represents a unique index that prevents duplicate values in the indexed field.
• A PersistenceException is thrown on commit (or flush) if different entities have the same value in a unique field (similar to how primary keys behave).
• @Index represents either an ordinary index with no unique constraint or a unique index if unique="true" is specified (the default is false).
• Indexes can only be defined on ordinary persistent fields (not on primary key / version fields).

* Composite Index

• A composite index is an index on more than one persistent field.
• It is defined by specifying multiple fields in the members attribute of the the @Index or @Unique annotations.
• Multiple @Index annotations can be wrapped with an @Indices annotation
• the @Uniques annotation can wrap multiple @Unique annotations.

* Multi Part Path Index

• Indexes must always refer to values that are stored as part of the entity.
• Indexes on multi part paths are only allowed when using embeddable classes as fields of embedded object are stored as part of the containing entity.
• Multi part paths in a composite index must have the same length.

* Indexes in Queries

• A BTree is an ordered map data structure that ObjectDB maintains in the file system rather than in memory.
• Indexes require maintenance time and consume storage space.
• Indexes are especially efficient in lookup and range queries.
• the order of the fields in the composite index definition is important, because it determines which field is used as the BTree's primary sort key and which field is used as a secondary sort key. If x is specified first, the BTree is ordered by x values, so a range scan on x values is supported.
• Indexes on collections are useful in JOIN queries.

[Database Schema Evolution]

• New entity objects have to be stored in the new class schema, and old entity objects, which were stored previously in the old class schema, have to be converted to the new schema.
• [Note] In client-server mode the ObjectDB server must be restarted after a schema change.

* Automatic Schema Evolution

• When an entity object of an old schema is loaded into memory it is automatically converted into an instance of the up to date entity class.
• The database object is only updated to the new schema when that entity object is stored to the database again.
• A matching field is a field with the same name and either the same type or a convertible type.
• A matching field might also be located in a different place in the class hierarchy.

[JPA Persistence Unit]

• A JPA Persistence Unit is a logical grouping of user defined persistable classes (entity classes, embeddable classes and mapped superclasses) with related settings.
• Defining a persistence unit is optional when using ObjectDB, but required by JPA.

* persistence.xml

• One persistence.xml file can include definitions for one or more persistence units.
• The portable way to instantiate an EntityManagerFactory in JPA requires a persistence unit.
• <provider>
• Indicates which JPA implementation should be used. 
• ObjectDB is represented by the com.objectdb.jpa.Provider string.
• If not specified, the first JPA implementation that is found is used.
• <mapping-file>
• Specify XML mapping files that are added to the default META-INF/orm.xml mapping file.
• Every annotation that is described in this manual can be replaced by equivalent XML in the mapping files (as explained below).
• <jar-file>
• Specify JAR files that should be searched for managed persistable classes.
• <class>
• Specify names of managed persistable classes .
• <property>
• Specify general properties. 
• JPA 2 defines standard properties for specifying database url, username and password, as demonstrated above.

* Managed Persistable Classes

• JPA requires registration of all the user defined persistable classes (entity classes, embeddable classes and mapped superclasses), which are referred to by JPA as managed classes, as part of a persistence unit definition.

* Reference
- ObjectDB 2.2 Developer's Guide

[JPA] ObjectDB Manual (01 - Quick Tour)

* Defining a JPA Entity Class

• A JPA annotated entity class as have the ability to represent objects in the database and similar to serializable classes.
• By default, any field that is not declared as static or transient is a persistent field.

* Obtaining a JPA Database Connection

• In JPA a database connection is represented by the EntityManager interface.
• Operations that modify database content also require an EntityTransaction instance.
• To obtain an instance of EntityManagerFactory that represents the relevant database.
• Can use that factory instance to get an EntityManager instance.
• Define a standard persistence unit in an XML file
• Can simply provide the file path of the ObjectDB database.
• Closing an EntityManager does not close the database itself.
• Once the EntityManager object is closed it cannot be reused. But, the owning EntityManagerFactory instance may preserve the EntityManager's resources

* CRUD Database Operations with JPA

• Operations that modify the content of the database (such as storing new objects) require an active transaction.
• The EntityManager object serves as the factory for Query instances.
• The getSingleResult method executes the query and returns the result. It should only be used when exactly one result value is expected (a single Long object in the query above).
• The TypedQuery interface is a type safe subinterface of Query and is usually the preferred way to work with queries.
• The EntityManager that manages an entity is responsible for automatically detecting changes to the entity object and applying them to the database when the transaction is committed.

* Reference
- ObjectDB 2.2 Developer's Guide

[CSS] anchors

[Note] 如果為下段中的 anchor 指定了全域 CSS，這些樣式會覆蓋為 <h2> 指定的樣式。

Note CSS for h2

* id 像 name 屬性，可為頁面指定 anchor，但有些 browser 不支援。

* 不建議使用 name 屬性，所以可以考慮同時使用 id and name，但只有在相同標籤時才能這樣，也只有特定標籤允許這樣做:
ex: <a>, <applet>, <form>, <frame>, <iframe>, <img>, <map>。


* Order: LoVe/HAte

• a:link
• a:visited
• a:hover
• a:active

[Note] 冒號後不可空格!

[SQL] Join

join 總共分為六種:

• Inner Join
• Natural Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join

Inner join:

• 兩個資料表只顯示匹對的資料
• 其實等同於多個Where條件式的連結

Outer join

• 不管是否有匹對，都會將資料顯示出來，又分為 LEFT, RIGHT, FULL join。
• 外部連接的語法是依資料庫的不同而有所不同的。
• ex: 在 Oracle 上， 我們會在 WHERE 子句中要選出所有資料的那個表格之後加上一個 "(+)" 來代表說這個表格中的所有資料我們都要。
• 當第二個表格沒有相對的資料時， SQL 會傳回 NULL 值。
• ex: ...where A1.store_name = A2.store_name (+): 表示 A1 的資訊會全撈。

Update on 2013/01/07

• join = inner join
• left join = left outer join

* Reference
- [MySQL]left, right, inner, outer join 使用方法
- SQL 外部連接
- SQL Join語法筆記

[SQL] distinct

若接著 SELECT DISTINCT 後面有指定兩個以上的欄位

則要符合所有欄位值皆同樣重複的情況下該筆資料才會被捨棄。

(若只有其中一個欄位值相同但其它欄位值並不同，則仍會取出該筆資料。)


* Reference
- SELECT DISTINCT (SQL SELECT DISTINCT Statement)

[SQL] PK and FK

盡量使用 PK 當 FK

否則 join/物件連結 時會很慢

[JPA] Metamodel

有些屬性 JPA 不認得  而 metadata 就是讓 JPA 知道是和什麼屬性做比較

ex: date, object

CSS and HTML

• <em> : 斜體並且強調

• font-style: italic;
• 僅表示斜體外觀。

• <strong> : 粗體並且更強烈的強調

• font-weight: bold;
• 僅表示粗體外觀。

[JPA] Criteria Query

entityManager -> criteriaBuilder -> criteriaQuery -> root

[Annotation] JPA Annotation and JPQL API

Annotation Id

• Specifies the primary key of an entity.

Annotation Column

• Is used to specify the mapped column for a persistent property or field.
• If no Column annotation is specified, the default values apply.
• 如果名稱相同，則不需要使用此指定。

Annotation GeneratedValue

• Provides for the specification of generation strategies for the values of primary keys.
• String generator
• (Optional) The name of the primary key generator to use as specified in the SequenceGenerator or TableGenerator annotation.
• GenerationType strategy (Refer the next)
• (Optional) The primary key generation strategy that the persistence provider must use to generate the annotated entity primary key.
• Default value:
• javax.persistence.GenerationType.AUTO

Enum GenerationType

• GenerationType AUTO
• Default for Annotation GeneratedValue
• GenerationType IDENTITY
• GenerationType SEQUENCE
• Indicates that the persistence provider must assign primary keys for the entity using a database sequence.
• GenerationType TABLE

Annotation ManyToOne

• Defines a single-valued association to another entity class that has many-to-one multiplicity.
• It is not normally necessary to specify the target entity explicitly since it can usually be inferred from the type of the object being referenced.
• The ManyToOne annotation may be used within an embeddable class to specify a relationship from the embeddable class to an entity class.
• CascadeType[] cascade
• (Optional) The operations that must be cascaded to the target of the association.
• By default no operations are cascaded.
• FetchType fetch
• (Optional) (refer the following items)
• Default value:
• javax.persistence.FetchType.EAGER

Enum CascadeType

• Defines the set of cascadable operations that are propagated to the associated entity.
• The value cascade=ALL is equivalent to cascade={PERSIST, MERGE, REMOVE, REFRESH, DETACH}.

Enum FetchType

• Defines strategies for fetching data from the database.
• The EAGER strategy
• A requirement on the persistence provider runtime that data must be eagerly fetched. 
• The LAZY strategy
• A hint to the persistence provider runtime that data should be fetched lazily when it is first accessed.
• The implementation is permitted to eagerly fetch data for which the LAZY strategy hint has been specified.

Annotation JoinColumn

• Specifies a column for joining an entity association or element collection.
• String referencedColumnName

Interface CriteriaBuilder

• Used to construct criteria queries, compound selections, expressions, predicates, orderings.
• Note that Predicate is used instead of Expression in this API in order to work around the fact that Java generics are not compatible with varags. (?)

Interface CriteriaQuery<T>

• The CriteriaQuery interface defines functionality that is specific to top-level queries.

Interface Root<X>

• A root type in the from clause. Query roots always reference entities.

* Reference
- JPA Reference (JavaDoc)

[JPQL][Annotation] Brief JPQL API

* @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="teacherSEQ")

• strategy = GenerationType.SEQUENCE: 主键策略
• generator = "teacherSEQ": 寻找数据库中sequence的名字的中间量.
• 若使用 @GeneratedValue(strategy = GenerationType.SEQUENCE)
• 则 hibernate 在 oracle 中自动生成一个sequence, 这个 sequence 可能是与其他表共用的, 但是这不影响主键的唯一性.

* 查询表达式被赋予泛型。一些典型的表达式是：

• Root: 相当于一个 FROM子句。
• Predicate: 其计算为布尔值 true 或 false（事实上，它被声明为 interface Predicate extends Expression）。
• Path: 表示从 Root表达式导航到的持久化属性。Root是一个没有父类的特殊 Path。
• QueryBuilder: 是 CriteriaQuery和各种查询表达式的工厂。
• CriteriaQuery: 被传递给一个可执行查询并保留类型信息，这样可以直接访问选择列表的元素，而不需要任何运行时强制类型转换。

* Reference
- Hibernate中连接Oracle10g(使用annotation标识数据库中的sequence)
- JPA2.0的学习一 **
- JPA 2.0 中的动态类型安全查询 **

JPA annotation

The JPA annotation cannot be inherited and override

Metamodel and JPQL (dynamic query)

[Metamodel API]

* 將 metamodel class 定義為 持久化實體類的描述。
就像 Reflection API 需要其他接口(比如 java.lang.reflect.Field 或 java.lang.reflect.Method)來描述 java.lang.Class 的组成一樣，JPA Metamodel API 也需要其他接口（比如 SingularAttribute 和 PluralAttribute）來描述元模型类的类型及其属性。

* 持久化实体在语义上区分为 MappedSuperClass、Entity 和 Embeddable。
在 JPA 2.0 之前，这种语义区分是通过持久化类定义中的对应类级别注释来表示的。


* JPA Metamodel 在 javax.persistence.metamodel 包中描述了 3 个独立的接口:

• MappedSuperclassType
• EntityType
• EmbeddableType

类似地，可以通过接口（比如 SingularAttribute、CollectionAttribute 和 MapAttribute）在类型定义级别上区分持久化属性。

* 持久化单元作用域的持久化实体在 META-INF/persistence.xml 文件的 <class> 子句中枚举。
通过 javax.persistence.metamodel.Metamodel 接口让开发人员可以在运行时使用作用域。Metamodel 接口是特定持久化单元知道的所有持久化实体的容器。

* 元模型接口是持久化单元中的类型的容器。

* 这个接口允许通过元模型元素的对应持久化实体类访问元模型元素。

* 可以在运行时浏览 EntityType<person> 获得在 Person 实体中声明的持久化属性。

* 如果应用程序在 pClass（比如 pClass.getSingularAttribute("age", Integer.class)）上调用一个方法，它将返回一个 SingularAttribute<person, integer=""> 实例，该实例与实例化规范元模型类的静态 Person_.age 成员相同。

* 最重要的是，对于应用程序可以通过 Metamodel API 在运行时引用的属性，是通过实例化静态规范元模型 Person_ 类向 Java 编译器提供的。

* 除了将持久化实体分解为对应的元模型元素之外，Metamodel API 还允许访问所有已知的元模型类 (Metamodel.getManagedTypes())，或者通过类的持久化信息访问元模型类，例如 embeddable(Address.class)，它将返回一个 EmbeddableType<address> 实例（ManagedType<> 的子接口）。

* 持久化元信息分为两大类：

• 持久化, ex: @Entity
• 元模型仅为持久化注释（不是映射注释）捕捉元数据。因此，使用当前版本的 Metamodel API 可以知道哪些字段是持久化的，但不能找到它们映射到的数据库列。
• 映射, ex: @Table

* Person_.age

• 表示 Person 的持久化属性 age。
• 是 Person_ 类中的公共静态字段，Person_ 是静态、已实例化的规范元模型类，对应于原来的 Person 实体类。

* 元模型类描述持久化类的元数据。如果一个类安装 JPA 2.0 规范精确地描述持久化实体的元数据，那么该元模型类就是规范的。

• 规范的元模型类是静态的，因此它的所有成员变量都被声明为静态的（也是 public 的）。
• Person_.age 是静态成员变量之一。您可以在开发时在源代码中生成一个具体的 Person_.java 来实例化 一个规范类。
• 实例化之后，它就可以在编译期间以强类型的方式引用 Person 的持久化属性。

* JPA 2.0 提供的解决办法通过静态地公开相同的持久化属性实例化名为 Person_ 的元模型类（对应于 Person）。

• 元模型类将原来的 domain.Person 实体的每个持久化属性声明为类型为 SingularAttribute< Person,?> 的静态公共字段。
• 通过利用这个 Person_ 元模型类，可以在编译期间引用 domain.Person 的持久化属性 age — 不是通过 Reflection API，而是直接引用静态的 Person_.age 字段。
• 编译器可以根据 age 属性声明的类型实施类型检查。
• ex：QueryBuilder.gt(p.get(Person_.age), "xyz") 将导致编译器错误，因为编译器通过 QueryBuilder.gt(..) 的签名和 Person_.age 的类型可以确定 Person 的 age 属性是一个数字字段，不能与 String 进行比较。

* 元模型类被注释为 @StaticMetamodel(domain.Person.class) 以将其标记为一个与原来的持久化 domain.Person 实体对应的元模型类。



[Criteria API]

* This dynamic query definition capability, referred as Criteria API, is based on the:

• abstract persistent schema of the entities.
• their embedded objects.
• their relationships.

* The first step in constructing a query definition is specification of query roots.

• Query roots
• Specify the domain objects on which the query is evaluated. 
• Is an instance of the Root interface. 
• A query root is added to a CriteriaQuery by addRoot(Class c) method.

* A query domain can be further refined by joining to other domain objects.

* An attribute of a domain object can also be specified by navigating via get(String attr). The attribute should refer to a valid persistent property of the receiving domain object, however no such validation is enforced during the construction of the query definition.

* CriteriaBuilder interface

• The factory for CriteriaQuery. 
• Is obtained from either an EntityManagerFactory or an EntityManager.

* queryBuilder

• 是 CriteriaQuery 的工厂，同时也是查询表达式的工厂。
• 包含构造传统 JPQL 语法支持的所有查询表达式的 API 方法，并且还包含额外的方法。

* CriteriaQuery

• 一个查询表达式节点树。
• 在传统的基于字符串的查询语言中，这些表达式节点用于指定查询子句，比如 FROM、WHERE 和 ORDER BY。

* Reference
- JPA 2.0 中的动态类型安全查询(3)
- JPA 2.0 中的动态类型安全查询(4)
- 详解JPA 2.0动态查询机制:Criteria API(1)
- 详解JPA 2.0动态查询机制:Criteria API(2)
- Part 2. Java Persistence API

[ibatis] Attributes

<ispropertyavailable> 属性是存在

<isnotpropertyavailable> 属性不存在

<isnull> 属性值是null

<isempty> 判断 Collection.size < 1 或 String.length() < 1

<isequal> 等於

<isnotequal> 不等於

<isgreaterthan> 大於

<isgreaterequal> 大於等於

<islessthan> 小於

<islessequal> 小於等於



* Reference
- 如何进行ibatis动态多条件组合查询以及模糊查询(oracle,mysql)