001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one
003: * or more contributor license agreements. See the NOTICE file
004: * distributed with this work for additional information
005: * regarding copyright ownership. The ASF licenses this file
006: * to you under the Apache License, Version 2.0 (the
007: * "License"); you may not use this file except in compliance
008: * with the License. You may obtain a copy of the License at
009: *
010: * http://www.apache.org/licenses/LICENSE-2.0
011: *
012: * Unless required by applicable law or agreed to in writing,
013: * software distributed under the License is distributed on an
014: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
015: * KIND, either express or implied. See the License for the
016: * specific language governing permissions and limitations
017: * under the License.
018: */
019: package org.apache.openjpa.persistence.xmlmapping.query;
020:
021: import java.io.FileWriter;
022: import java.util.List;
023:
024: import javax.persistence.EntityManager;
025: import javax.persistence.EntityTransaction;
026: import javax.persistence.Query;
027:
028: import junit.textui.TestRunner;
029:
030: import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
031: import org.apache.openjpa.jdbc.sql.DB2Dictionary;
032: import org.apache.openjpa.jdbc.sql.DBDictionary;
033: import org.apache.openjpa.jdbc.sql.OracleDictionary;
034: import org.apache.openjpa.jdbc.sql.SQLServerDictionary;
035: import org.apache.openjpa.persistence.OpenJPAEntityManager;
036: import org.apache.openjpa.persistence.OpenJPAPersistence;
037: import org.apache.openjpa.persistence.OpenJPAEntityManagerFactory;
038: import org.apache.openjpa.persistence.OpenJPAEntityManagerFactorySPI;
039: import org.apache.openjpa.persistence.OpenJPAEntityManagerSPI;
040: import org.apache.openjpa.persistence.test.SQLListenerTestCase;
041: import org.apache.openjpa.persistence.xmlmapping.xmlbindings.myaddress.*;
042: import org.apache.openjpa.persistence.xmlmapping.entities.*;
043: import org.apache.openjpa.persistence.xmlmapping.entities.Customer.CreditRating;
044:
045: /**
046: * Test query with predicates on persistent field mapped to XML column.
047: *
048: * @author Catalina Wei
049: * @since 1.0.0
050: */
051: public class TestXMLCustomerOrder extends SQLListenerTestCase {
052:
053: private boolean enabled = false;
054:
055: public void setUp() {
056: OpenJPAEntityManagerFactorySPI emf = createEMF();
057: DBDictionary dict = ((JDBCConfiguration) emf.getConfiguration())
058: .getDBDictionaryInstance();
059:
060: // skip if dictionary has no support for XML column type
061: if (!dict.supportsXMLColumn)
062: return;
063:
064: enabled = true;
065:
066: setUp(
067: org.apache.openjpa.persistence.xmlmapping.entities.Customer.class,
068: org.apache.openjpa.persistence.xmlmapping.entities.Customer.CustomerKey.class,
069: org.apache.openjpa.persistence.xmlmapping.entities.Order.class,
070: org.apache.openjpa.persistence.xmlmapping.entities.EAddress.class);
071: }
072:
073: public static void main(String[] args) {
074: TestRunner.run(TestXMLCustomerOrder.class);
075: }
076:
077: public void testXMLCustomerOrder() {
078: // skip if dictionary has no support for XML column type
079: if (!enabled)
080: return;
081:
082: OpenJPAEntityManagerSPI em = emf.createEntityManager();
083: DBDictionary dict = ((JDBCConfiguration) em.getConfiguration())
084: .getDBDictionaryInstance();
085:
086: String sqllog = TestXMLCustomerOrder.class.getName();
087: sqllog = sqllog.replace('.', '/');
088: sqllog = "./" + sqllog;
089: if (dict instanceof DB2Dictionary)
090: sqllog += ".db2";
091: else if (dict instanceof OracleDictionary)
092: sqllog += ".oracle";
093: else if (dict instanceof SQLServerDictionary)
094: sqllog += ".sqlserver";
095:
096: // For platform specific expected sqls are under resources.
097: // The generated sql of the test is captured and written to file:
098: // ./TestXMLCustomerOrder.log
099: // This output file contents should match with the platform specfic
100: // sqls.
101: System.out.println("Expected pushdown SQL log file is in: "
102: + sqllog);
103:
104: sql.clear();
105:
106: try {
107: em.getTransaction().begin();
108: deleteAllData(em);
109: em.getTransaction().commit();
110:
111: em.getTransaction().begin();
112: loadData(em);
113: em.getTransaction().commit();
114:
115: em.close();
116:
117: // By closing and recreating the EntityManager,
118: // this guarantees that data will be retrieved from
119: // the database rather than just reused from the
120: // persistence context created by the load methods above.
121:
122: em = emf.createEntityManager();
123:
124: System.err.println("Main started.");
125: int test = 1;
126: List<Address> addrs = em.createQuery(
127: "select o.shipAddress from Order o")
128: .getResultList();
129: for (Address addr : addrs) {
130: System.out.println("addr= " + addr.toString());
131: }
132: String qstrings[] = {
133: "select o from Order o",
134: "select o from Order o, Order o2 where o.shipAddress.city "
135: + "= o2.shipAddress.city",
136: "select o from Order o, Customer c where o.shipAddress.city "
137: + "= c.address.city",
138: "select o from Order o where o.shipAddress.city = 'San Jose'" };
139: String qstring = null;
140: for (int i = 0; i < qstrings.length; i++) {
141: qstring = qstrings[i];
142: List orders = em.createQuery(qstring).getResultList();
143: printOrders(orders, test++);
144: }
145:
146: // query passing parameters
147: qstring = "select o from Order o where o.shipAddress.city = ?1";
148: Query q5 = em.createQuery(qstring);
149: q5.setParameter(1, "San Jose");
150: List orders = q5.getResultList();
151: printOrders(orders, test++);
152:
153: qstring = "select o from Order o where ?1 = o.shipAddress.city";
154: Query q6 = em.createQuery(qstring);
155: q6.setParameter(1, "San Jose");
156: orders = q6.getResultList();
157: printOrders(orders, test++);
158:
159: em.close();
160:
161: // test updates
162: em = emf.createEntityManager();
163: testUpdateShipaddress(em, test++);
164:
165: em.close();
166: em = emf.createEntityManager();
167:
168: // query after updates
169: orders = em.createQuery("select o from Order o")
170: .getResultList();
171: System.out.println("After Update:");
172: printOrders(orders, test++);
173:
174: // queries expecting exceptions
175: String[] badqstrings = {
176: "select o from Order o where o.shipAddress.city = 95141",
177: "select o from Order o where o.shipAddress.street "
178: + "= '555 Bailey'",
179: "select o from Order o where o.shipAddress.zip = 95141" };
180: for (int i = 0; i < badqstrings.length; i++) {
181: qstring = badqstrings[i];
182: try {
183: System.out.println("\n>> Query " + test + ": "
184: + qstring);
185: test++;
186: orders = em.createQuery(qstring).getResultList();
187: } catch (Exception e) {
188: System.out.println("Exception: " + e);
189: }
190: }
191:
192: dumpSql();
193: em.close();
194: emf.close();
195: System.out.println("Main ended normally.");
196: } catch (Exception e) {
197: System.out.println("Exception: " + e);
198: e.printStackTrace();
199: }
200: }
201:
202: private void dumpSql() {
203: String out = "./TestXMLCustomerOrder.log";
204: try {
205: FileWriter fw = new FileWriter(out);
206: for (int i = 0; i < sql.size(); i++) {
207: System.out.println(sql.get(i));
208: fw.write(sql.get(i) + "\n");
209: }
210: fw.close();
211: } catch (Exception e) {
212: }
213: }
214:
215: private void printOrders(List orders, int test) {
216: System.out.println("\n>> Query " + test);
217: System.out.println("result size = " + orders.size());
218: for (int i = 0; i < orders.size(); i++) {
219: printOrder((Order) orders.get(i));
220: }
221: }
222:
223: private void loadData(EntityManager em) {
224:
225: ObjectFactory addressFactory = new ObjectFactory();
226:
227: Customer c2 = new Customer();
228: c2.setCid(new Customer.CustomerKey("USA", 2));
229: c2.setName("A&J Auto");
230: c2.setRating(CreditRating.GOOD);
231: c2.setAddress(new EAddress("2480 Campbell Ave", "Campbell",
232: "CA", "95123"));
233: em.persist(c2);
234:
235: Customer c1 = new Customer();
236: c1.setCid(new Customer.CustomerKey("USA", 1));
237: c1.setName("Harry's Auto");
238: c1.setRating(CreditRating.GOOD);
239: c1.setAddress(new EAddress("12500 Monterey", "San Jose", "CA",
240: "95141"));
241: em.persist(c1);
242:
243: Order o1 = new Order(10, 850, false, c1);
244: USAAddress addr1 = addressFactory.createUSAAddress();
245: addr1.setCity("San Jose");
246: addr1.setState("CA");
247: addr1.setZIP(new Integer("95141"));
248: addr1.getStreet().add("12500 Monterey");
249: addr1.setName(c1.getName());
250: o1.setShipAddress(addr1);
251: em.persist(o1);
252:
253: Order o2 = new Order(20, 1000, false, c1);
254: CANAddress addr2 = addressFactory.createCANAddress();
255: addr2.setName(c2.getName());
256: addr2.getStreet().add("123 Warden Road");
257: addr2.setCity("Markham");
258: addr2.setPostalCode("L6G 1C7");
259: addr2.setProvince("ON");
260: o2.setShipAddress(addr2);
261: em.persist(o2);
262: }
263:
264: private void testUpdateShipaddress(EntityManager em, int test)
265: throws Exception {
266: em.getTransaction().begin();
267: String query = "select o from Order o where o.shipAddress.city "
268: + "= 'San Jose'";
269: List orders = em.createQuery(query).getResultList();
270: System.out.println("Before Update: ");
271: printOrders(orders, test);
272: em.getTransaction().commit();
273:
274: // update in separate transaction
275: Order o = (Order) orders.get(0);
276: EntityTransaction et = em.getTransaction();
277: et.begin();
278: Address addr = o.getShipAddress();
279: addr.setCity("Cupertino");
280: if (addr instanceof USAAddress)
281: ((USAAddress) addr).setZIP(95014);
282:
283: // update shipAddress
284: o.setShipAddress(addr);
285: et.commit();
286: }
287:
288: private void deleteAllData(EntityManager em) {
289: em.createQuery("delete from Order o").executeUpdate();
290: em.createQuery("delete from Customer c").executeUpdate();
291: }
292:
293: private void printOrder(Order o) {
294: System.out.println(" Customer ID:" + o.getCustomer().getCid());
295: System.out.println(" Order Number:" + o.getOid());
296: System.out.println("Ship to: " + o.getShipAddress().toString());
297: System.out.println();
298: }
299: }
|