Querying for Comments

Here is the table structure related to comments.

REG_COMMENT
REG_ID	REG_COMMENT_TEXT	REG_USER_ID	REG_COMMENT_TIME

REG_RESOURCE_COMMENT
REG_COMMENT_ID	REG_VERSION	REG_PATH_ID	REG_RESOURCE_NAME

Here is an example query to search comments on resources (non-collections) that have a description with a given pattern:

SELECT RC.REG_COMMENT_ID FROM REG_RESOURCE_COMMENT RC, REG_RESOURCE R

WHERE (R.REG_VERSION=RC.REG_VERSION OR

(R.REG_PATH_ID=RC.REG_PATH_ID AND R.REG_NAME=RC.REG_RESOURCE_NAME))

AND R.REG_DESCRIPTION LIKE ?

The example below shows the code to execute the query.

Note

You have to set "RESULT_TYPE_PROPERTY_NAME" to "COMMENTS_RESULT_TYPE."

String sql1 = "SELECT RC.REG_COMMENT_ID FROM REG_RESOURCE_COMMENT RC, REG_RESOURCE R " +

"WHERE (R.REG_VERSION=RC.REG_VERSION OR " +

"(R.REG_PATH_ID=RC.REG_PATH_ID AND R.REG_NAME=RC.REG_RESOURCE_NAME)) " +

"AND R.REG_DESCRIPTION LIKE ?";



Resource q1 = registry.newResource();

q1.setContent(sql1);

q1.setMediaType(RegistryConstants.SQL_QUERY_MEDIA_TYPE);

q1.addProperty(RegistryConstants.RESULT_TYPE_PROPERTY_NAME,

RegistryConstants.COMMENTS_RESULT_TYPE);

registry.put(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", q1);


Map parameters = new HashMap();

parameters.put("1", "%production%");

Collection result = registry.executeQuery(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", parameters);



for (String commentPath: result.getChildren()) {

        String commentResource = registry.get(commentPath);

        System.out.println(commentResource.getContent()));

    }

For more information about Queries see Custom Query.