Revision 6602
Added by ben leinfelder almost 13 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