Revision 6602
Added by ben leinfelder over 12 years ago
| QuerySpecificationTest.java | ||
|---|---|---|
| 27 | 27 |
import java.io.FileNotFoundException; |
| 28 | 28 |
import java.io.FileReader; |
| 29 | 29 |
import java.io.IOException; |
| 30 |
import java.sql.PreparedStatement; |
|
| 31 |
import java.util.ArrayList; |
|
| 32 |
import java.util.List; |
|
| 30 | 33 |
|
| 31 | 34 |
import junit.framework.Test; |
| 32 | 35 |
import junit.framework.TestSuite; |
| 33 | 36 |
|
| 34 | 37 |
import edu.ucsb.nceas.MCTestCase; |
| 38 |
import edu.ucsb.nceas.metacat.DBQuery; |
|
| 35 | 39 |
import edu.ucsb.nceas.metacat.QuerySpecification; |
| 40 |
import edu.ucsb.nceas.metacat.database.DBConnectionPool; |
|
| 36 | 41 |
import edu.ucsb.nceas.metacat.properties.PropertyService; |
| 37 | 42 |
import edu.ucsb.nceas.metacat.util.MetacatUtil; |
| 38 | 43 |
import edu.ucsb.nceas.utilities.PropertyNotFoundException; |
| ... | ... | |
| 49 | 54 |
|
| 50 | 55 |
/** The utilities object for accessing property values */ |
| 51 | 56 |
|
| 52 |
private static String selectionQuery = "SELECT docid,docname,doctype,date_created, date_updated, rev " + |
|
| 53 |
"FROM xml_documents WHERE docid IN (((((SELECT DISTINCT docid FROM xml_nodes " + |
|
| 54 |
"WHERE UPPER(nodedata) LIKE '%JONES%' ) ))))"; |
|
| 57 |
private static String selectionQuery = |
|
| 58 |
"SELECT docid,docname,doctype,date_created, date_updated, rev " + |
|
| 59 |
"FROM xml_documents WHERE docid IN (((((SELECT DISTINCT docid FROM xml_nodes " + |
|
| 60 |
"WHERE UPPER(nodedata) LIKE %JONES% ) )))) "; |
|
| 55 | 61 |
/*private static String extendedQuery = "select xml_nodes.docid, 'dataset/title' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'title' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataset' ) ) AND xml_nodes.docid in " + |
| 56 | 62 |
"('obfs.45337', 'obfs.45338', 'obfs.45346') AND xml_nodes.nodetype = 'TEXT' AND " +
|
| 57 | 63 |
"xml_nodes.rootnodeid = xml_documents.rootnodeid UNION select xml_nodes.docid, 'originator/individualName/surName' as " + |
| ... | ... | |
| 84 | 90 |
"IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'eml' AND parentnodeid = rootnodeid ) ) ) ) " + |
| 85 | 91 |
"AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346') AND xml_nodes.nodetype = 'TEXT' " +
|
| 86 | 92 |
"AND xml_nodes.rootnodeid = xml_documents.rootnodeid";*/ |
| 87 |
private static String extendedQuery ="select xml_nodes.docid, xml_index.path, xml_nodes.nodedata, xml_nodes.parentnodeid, xml_nodes.nodetype FROM xml_index, xml_nodes WHERE"+ |
|
| 88 |
" ( (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ('dataset/title', 'originator/individualName/surName' , 'keyword' ) AND xml_nodes.nodetype = 'TEXT') "+
|
|
| 89 |
"OR (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( '/eml/@packageId', '/eml/dataset/access/@authSystem' , '/eml/dataset/access/@order' ) AND xml_nodes.nodetype = 'ATTRIBUTE'))) "+ |
|
| 90 |
"AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346')";
|
|
| 93 |
private static String extendedQuery = |
|
| 94 |
"select xml_nodes.docid, xml_index.path, xml_nodes.nodedata, xml_nodes.parentnodeid, xml_nodes.nodetype " + |
|
| 95 |
"FROM xml_index, xml_nodes " + |
|
| 96 |
"WHERE ( (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ( dataset/title, originator/individualName/surName , keyword ) " + |
|
| 97 |
"AND xml_nodes.nodetype = 'TEXT') "+ |
|
| 98 |
"OR (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( /eml/@packageId, /eml/dataset/access/@authSystem , /eml/dataset/access/@order ) " + |
|
| 99 |
"AND xml_nodes.nodetype = 'ATTRIBUTE'))) "+ |
|
| 100 |
"AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346')";
|
|
| 91 | 101 |
|
| 92 | 102 |
/* Initialize properties*/ |
| 93 | 103 |
static |
| ... | ... | |
| 98 | 108 |
} |
| 99 | 109 |
catch(Exception e) |
| 100 | 110 |
{
|
| 101 |
System.err.println("Exception in initialize option in MetacatServletNetTest "+e.getMessage());
|
|
| 111 |
System.err.println("Exception in initialize option in MetacatServletNetTest " + e.getMessage());
|
|
| 102 | 112 |
} |
| 103 | 113 |
} |
| 104 | 114 |
|
| ... | ... | |
| 149 | 159 |
QuerySpecification qspec = new QuerySpecification(xml, |
| 150 | 160 |
PropertyService.getProperty("xml.saxparser"),
|
| 151 | 161 |
PropertyService.getProperty("document.accNumSeparator"));
|
| 152 |
String query = qspec.printSQL(false); |
|
| 153 |
assertTrue(selectionQuery.equals(query.trim())); |
|
| 154 |
} catch (IOException e) {
|
|
| 155 |
fail(e.getMessage()); |
|
| 156 |
} catch (PropertyNotFoundException pnfe) {
|
|
| 157 |
fail(pnfe.getMessage()); |
|
| 158 |
} |
|
| 162 |
// keep track of parameter values |
|
| 163 |
List<Object> parameterValues = new ArrayList<Object>(); |
|
| 164 |
String query = qspec.printSQL(false, parameterValues); |
|
| 165 |
// fill in the values to really check the query string matches original/expected |
|
| 166 |
PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(query);
|
|
| 167 |
pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt); |
|
| 168 |
String preparedQueryString = pstmt.toString(); |
|
| 169 |
|
|
| 170 |
System.out.println("Prepared query: " + preparedQueryString);
|
|
| 171 |
System.out.println("Original query: " + selectionQuery);
|
|
| 172 |
|
|
| 173 |
assertEquals(selectionQuery, preparedQueryString); |
|
| 174 |
|
|
| 175 |
} catch (Exception e) {
|
|
| 176 |
e.printStackTrace(); |
|
| 177 |
fail(e.getMessage()); |
|
| 178 |
} |
|
| 159 | 179 |
} |
| 160 | 180 |
|
| 161 | 181 |
/** |
| ... | ... | |
| 164 | 184 |
public void testPrintExtendedSQL() |
| 165 | 185 |
{
|
| 166 | 186 |
try {
|
| 167 |
System.out.println("---- orginal EXT SQL ------------------\n"+extendedQuery);
|
|
| 187 |
System.out.println("---- orginal EXT SQL ------------------");
|
|
| 188 |
System.out.println(extendedQuery); |
|
| 168 | 189 |
QuerySpecification qspec = new QuerySpecification(xml, |
| 169 | 190 |
PropertyService.getProperty("xml.saxparser"),
|
| 170 | 191 |
PropertyService.getProperty("document.accNumSeparator"));
|
| 192 |
// keep track of parameter values |
|
| 193 |
List<Object> parameterValues = new ArrayList<Object>(); |
|
| 171 | 194 |
String query = |
| 172 | 195 |
qspec.printExtendedSQL( |
| 173 |
"'obfs.45337', 'obfs.45338', 'obfs.45346'", true); |
|
| 174 |
System.out.println("---- builded EXT SQL ------------------ \n"+query);
|
|
| 175 |
assertTrue(extendedQuery.equals(query)); |
|
| 176 |
} catch (IOException e) {
|
|
| 196 |
"'obfs.45337', 'obfs.45338', 'obfs.45346'", true, parameterValues); |
|
| 197 |
|
|
| 198 |
System.out.println("---- built EXT SQL ------------------");
|
|
| 199 |
System.out.println(query); |
|
| 200 |
|
|
| 201 |
// fill in the values to really check the query string matches original/expected |
|
| 202 |
PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(query);
|
|
| 203 |
pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt); |
|
| 204 |
String preparedQueryString = pstmt.toString(); |
|
| 205 |
|
|
| 206 |
System.out.println("Prepared query: " + preparedQueryString);
|
|
| 207 |
System.out.println("Original query: " + extendedQuery);
|
|
| 208 |
|
|
| 209 |
assertEquals(extendedQuery, preparedQueryString); |
|
| 210 |
|
|
| 211 |
} catch (Exception e) {
|
|
| 212 |
e.printStackTrace(); |
|
| 177 | 213 |
fail(e.getMessage()); |
| 178 |
} catch (PropertyNotFoundException pnfe) {
|
|
| 179 |
fail(pnfe.getMessage()); |
|
| 180 | 214 |
} |
| 181 | 215 |
} |
| 182 | 216 |
|
Also available in: Unified diff
uses prepared statement parameter binding for queries
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5527