如何拼写FileNet中的SQL语句

       这个问题既简单又复杂，简单的是我也写过不少CRUD的代码，难就难在FileNet的特殊性，其实像Hibernate或者JPA来说，SQL语句或HQL语句都是有各自的语法，但是绝大部分还是相同的。这样的情况存在于很多地方。

 

      API，官方提供的标准文档，提供了几乎所有的开发必备知识点，其中关于SQL语法的部分被我找到，也解决了我遇到的问题。

 

      下面分享给大家。     

 

      SQLSyntax Reference 这里主要介绍了FileNet中SQL语法，其中

    General Query Examples

This sectionprovides some examples of simple SQL statements to retrieve various pieces ofinformation.

Retrieve documentscreated by the Administrator user after April 1, 2005, and return the specifiedlist of properties for each returned document:

   SELECT Creator, DateCreated, DocumentTitleFROM Document
            WHERE (DateCreated>20050401T080000Z AND Creator='Administrator')

Retrieve documentsby ID (GUID) that have a document title containing the character pattern"Acct":

   SELECT DocumentTitle Id FROM Document WHEREDocumentTitle LIKE '%Acct%'

If the object storecontains three documents whose titles contain the characters "Acct",then this query would return them in this format:

AcctDocument     {4E017CB8-4980-4BB2-88E9-248C555445E2}
MyAcctDocument   {01C92932-E840-4FC2-90E8-45E245248CB5}
AcctgStoredSearch {4ECDE7D9-F551-4C53-A109-8D81B1DE8577}

To search for aspecific ID, you can change the query to specify a particular GUID:

   SELECT DocumentTitle Id FROM Document WHEREId={4ECDE7D9-F551-4C53-A109-8D81B1DE8577E}

NOTE Do not usequotes to specify a GUID in a query.

The following queryretrieves the list of checked out documents (document versions in Reservationstate) created by the HRManager user:

   SELECT Id FROM Document WHEREVersionStatus=3 AND Creator='HRManager'

By changing thevalue of the VersionStatus property, you can use this query to search fordocuments that are Released (1), In Process (2),
 or Superseded (4).

The following SQLqueries retrieve the containees from a particular folder (in this example, thefolder "/Test"). 
Note that when retrieving a list of folders, theWorkplace application issues the query in lieu of calling theFolder.getContainees method.
 Folder.getContainees returns all items in thefolder, with no limit on the number of items returned. Using such queries, 
youcan set the criteria to limit the number of items returned to only thoserequired by your application.

Get subfolders:

   SELECT f.[ObjectType], f.[Id],f.[FolderName], f.[ContainerType], f.[ClassDescription],
            f.[OIID]  FROM Folder f
            WHERE (( f.This infolder'/Test')) AND (( [IsHiddenContainer]=false) ))
            ORDER BY FolderName

Get documents:

   SELECT d.[ObjectType], d.[Id],d.[LastModifier], d.[DocumentTitle], d.[DateLastModified], d.[ContentSize],
            d.[MajorVersionNumber],d.[ClassDescription], FROM Document d
            WHERE (( (d.This infolder'/Test') ) AND (( [MimeType] IS NULL)
                OR ([MimeType] <>'application/x-filenet-search') ) ))
            ORDER BY DocumentTitle

The following samplequery searches for and returns the ID of all HTML documents in the databasewith greater than 10,000 bytes of content,
 and that have been modified sinceDecember 12, 2005:

   SELECT Id FROM Document d WHERE ([MimeType]= 'text/html' AND [ContentSize] > 10000.0)
            AND [DateLastModified] >20051201T000000Z