Writing custom queries to retrieve data from WSO2 Governance Registry

WSO2 Governance Registry is a big part of wso2 governance product stack. Even though it is primarily aimed at managing, versioning, rating, and commenting on SOA artifacts it can also be used as a simple data store. with the 3.0 version the G-Reg gave support to custom query execution from the client side. This feature helps immensely when you use the registry for non-standard tasks. For me I had to do some pagination work for the comments that belongs to a particular resource, hence my approach was to write few custom quires to get the job done. The code is as follows.

	/**
	 * Returns a chunk of comments
	 * 
	 * @param resPath	Path to the comment
	 * @param start		The beginning index
	 * @param size		Size of the chunk
	 * @return			an array of comments
	 */
 public Comment[] getCommentSet(String resPath, int start, int size) {
		Registry registry = null;
		try {
			registry = ; // get an instance of the registry 

			Resource comQuery = registry.newResource();

                        // The Sql Statement
			String sql = "SELECT REG_COMMENT_ID FROM REG_RESOURCE_COMMENT RC, REG_RESOURCE R, REG_PATH P WHERE "
					+ "RC.REG_VERSION=R.REG_VERSION AND "
					+ "R.REG_NAME=? AND "
					+ "P.REG_PATH_VALUE=? AND "
					+ "P.REG_PATH_ID=R.REG_PATH_ID LIMIT ?, ?";

			// Set SQL statement as the resource content
                        comQuery.setContent(sql);

                       // Setting the media type and properties
			comQuery.setMediaType(RegistryConstants.SQL_QUERY_MEDIA_TYPE);
			comQuery.addProperty(RegistryConstants.RESULT_TYPE_PROPERTY_NAME, RegistryConstants.COMMENTS_RESULT_TYPE);

			registry.put("system/myQueries/query", comQuery);
                 String resourceName = "testResource";
                 String pathToResource = "/system/myResources"

			Map params = new HashMap();

                        //Setting the parameters
			params.put("1", resourceName);
			params.put("2", pathToResource);
			params.put("3", start);
			params.put("4", size);

                       // Executing the SQL statement
			Collection qResults = registry.executeQuery("system/myQueries/query", params);

			String[] qPaths = (String[]) qResults.getContent();

			Comment[] comments = new Comment[qPaths.length];
                        // Loading the comment data to comment object array 
			for (int i = 0; i < qPaths.length; i++) {
				if (registry.resourceExists(qPaths[i])) {
					comments[i] = (Comment) registry.get(qPaths[i]);
				}
			}

			return comments;

		} catch (Exception e) {
			String errorMsg = "Backend server error - could not get comment set";
			log.error(new MyTestException(errorMsg, e));
			return null;
		}

	}

Yeah simple as that you get your resources set without much effort. A big thank goes to Dimuthu