Metacat

import java.sql.PreparedStatement;

import java.util.ArrayList;

import java.util.List;

import edu.ucsb.nceas.metacat.DBQuery;

import edu.ucsb.nceas.metacat.database.DBConnectionPool;

    /**

     * NOTE: there are no quotes around the values because we are comparing it to a PreparedStatement.toString()

     * String after binding the parameter values. Please trust that the PreparedStatement is correct.

     */

    private String query = 

    	"(SELECT DISTINCT docid FROM xml_path_index WHERE  (UPPER(nodedata) "+

        "LIKE %LAND% AND path IN ( dataset/title, geographicCoverage/boundingCoordinates/southBoundingCoordinate )) " +

        "OR ((UPPER(nodedata) LIKE %JONES% AND path LIKE organizationName) ) OR ((UPPER(nodedata) LIKE %LAND % AND path LIKE keyword) ) " +

        "OR ((UPPER(nodedata) LIKE %DATOS% AND path LIKE entityName) ) UNION ((SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) " +

        "LIKE %VALUE1% AND parentnodeid IN (SELECT nodeid FROM xml_index WHERE path LIKE path1) )  UNION " +

        "(SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) LIKE %VALUE2% AND parentnodeid IN " +

        "(SELECT nodeid FROM xml_index WHERE path LIKE path2) ) ))";

    	// keep track of parameter values

        List<Object> parameterValues = new ArrayList<Object>();

    	String queryString = group.printSQL(true, parameterValues);

    	try {

    		// fill in the values to really check the query string matches original/expected

    		PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(queryString);

    		pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);

    		String preparedQueryString = pstmt.toString();

    		System.out.println("Prepared query: " + preparedQueryString);

    		System.out.println("Original query: " + query);

    		assertEquals(query, preparedQueryString);

    	} catch (Exception e) {

    		e.printStackTrace();

			fail(e.getMessage());

		}