1 /*
2  * Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4  *
5  * This code is free software; you can redistribute it and/or modify it
6  * under the terms of the GNU General Public License version 2 only, as
7  * published by the Free Software Foundation.
8  *
9  * This code is distributed in the hope that it will be useful, but WITHOUT
10  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
11  * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
12  * version 2 for more details (a copy is included in the LICENSE file that
13  * accompanied this code).
14  *
15  * You should have received a copy of the GNU General Public License version
16  * 2 along with this work; if not, write to the Free Software Foundation,
17  * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
18  *
19  * Please contact Oracle, 500 Oracle Parkway, Redwood Shores, CA 94065 USA
20  * or visit www.oracle.com if you need additional information or have any
21  * questions.
22  */
23 package test.rowset.joinrowset;
24 
25 import java.sql.SQLException;
26 import java.sql.Types;
27 import java.util.ArrayList;
28 import java.util.List;
29 import javax.sql.RowSet;
30 import javax.sql.rowset.CachedRowSet;
31 import javax.sql.rowset.JoinRowSet;
32 import javax.sql.rowset.RowSetMetaDataImpl;
33 import javax.sql.rowset.WebRowSet;
34 import static org.testng.Assert.assertEquals;
35 import org.testng.annotations.DataProvider;
36 import org.testng.annotations.Test;
37 import test.rowset.webrowset.CommonWebRowSetTests;
38 
39 public class JoinRowSetTests extends CommonWebRowSetTests {
40 
41     private final String SUPPLIERS_TABLE = "SUPPLIERS";
42     // Expected COF_IDs to be found
43     private final Object[] EXPECTED = {4, 1};
44     // SUPPLIERS Primary Key to use to validate the joins
45     private final int SUP_ID = 101;
46     // Join Column between the SUPPLIERS and COFFEES table
47     private final String JOIN_COLNAME = "SUP_ID";
48     // Column index in COFFEES table which contains SUP_ID
49     private final int COFFEES_JOIN_COLUMN_INDEX = 3;
50     // Column index in SUPPLIERS table which contains SUP_ID
51     private final int SUPPLIERS_JOIN_COLUMN_INDEX = 1;
52 
53     @Override
newInstance()54     protected JoinRowSet newInstance() throws SQLException {
55         return rsf.createJoinRowSet();
56     }
57 
58     /*
59      * Initializes the SUPPLIERS metadata
60      */
initSuppliersMetaData(CachedRowSet crs)61     private void initSuppliersMetaData(CachedRowSet crs) throws SQLException {
62         RowSetMetaDataImpl rsmd = new RowSetMetaDataImpl();
63 
64         /*
65          *  CREATE TABLE SUPPLIERS (
66          *   SUP_ID INTEGER NOT NULL,
67          *   SUP_NAME VARCHAR(32) NOT NULL,
68          *   STREET VARCHAR(32) NOT NULL,
69          *   CITY VARCHAR(32) NOT NULL,
70          *   STATE CHAR(2) NOT NULL,
71          *   ZIP CHAR(5) NOT NULL,
72          *   PRIMARY KEY (SUP_ID))
73          */
74         rsmd.setColumnCount(6);
75         rsmd.setColumnName(1, "SUP_ID");
76         rsmd.setColumnName(2, "SUP_NAME");
77         rsmd.setColumnName(3, "STREET");
78         rsmd.setColumnName(4, "CITY");
79         rsmd.setColumnName(5, "STATE");
80         rsmd.setColumnName(6, "ZIP");
81 
82         rsmd.setColumnType(1, Types.INTEGER);
83         rsmd.setColumnType(2, Types.VARCHAR);
84         rsmd.setColumnType(3, Types.VARCHAR);
85         rsmd.setColumnType(4, Types.VARCHAR);
86         rsmd.setColumnType(5, Types.CHAR);
87         rsmd.setColumnType(6, Types.CHAR);
88         crs.setMetaData(rsmd);
89         crs.setTableName(SUPPLIERS_TABLE);
90     }
91 
92     /*
93      * Add rows to SUPPLIERS table
94      */
createSuppiersRows(RowSet rs)95     protected void createSuppiersRows(RowSet rs) throws SQLException {
96 
97         // insert into SUPPLIERS values(49, 'Superior Coffee', '1 Party Place',
98         // 'Mendocino', 'CA', '95460')
99         rs.moveToInsertRow();
100         rs.updateInt(1, 49);
101         rs.updateString(2, "Superior Coffee");
102         rs.updateString(3, "1 Party Place");
103         rs.updateString(4, "Mendocino");
104         rs.updateString(5, "CA");
105         rs.updateString(6, "95460");
106         rs.insertRow();
107 
108         // insert into SUPPLIERS values(101, 'Acme, Inc.', '99 Market Street',
109         // 'Groundsville', 'CA', '95199')
110         rs.moveToInsertRow();
111         rs.updateInt(1, 101);
112         rs.updateString(2, "Acme, Inc.");
113         rs.updateString(3, "99 Market Street");
114         rs.updateString(4, "Groundsville");
115         rs.updateString(5, "CA");
116         rs.updateString(6, "95199");
117         rs.insertRow();
118         // insert into SUPPLIERS values(150, 'The High Ground',
119         // '100 Coffee Lane', 'Meadows', 'CA', '93966')
120         rs.moveToInsertRow();
121         rs.updateInt(1, 150);
122         rs.updateString(2, "The High Ground");
123         rs.updateString(3, "100 Coffee Lane");
124         rs.updateString(4, "Meadows");
125         rs.updateString(5, "CA");
126         rs.updateString(6, "93966");
127         rs.insertRow();
128         // insert into SUPPLIERS values(456," 'Restaurant Supplies, Inc.',
129         // '200 Magnolia Street', 'Meadows', 'CA', '93966')
130         rs.moveToInsertRow();
131         rs.updateInt(1, 456);
132         rs.updateString(2, "Restaurant Supplies, Inc.");
133         rs.updateString(3, "200 Magnolia Stree");
134         rs.updateString(4, "Meadows");
135         rs.updateString(5, "CA");
136         rs.updateString(6, "93966");
137         rs.insertRow();
138         // insert into SUPPLIERS values(927, 'Professional Kitchen',
139         // '300 Daisy Avenue', 'Groundsville'," 'CA', '95199')
140         rs.moveToInsertRow();
141         rs.updateInt(1, 927);
142         rs.updateString(2, "Professional Kitchen");
143         rs.updateString(3, "300 Daisy Avenue");
144         rs.updateString(4, "Groundsville");
145         rs.updateString(5, "CA");
146         rs.updateString(6, "95199");
147         rs.insertRow();
148     }
149 
150     /*
151      * DataProvider used to set parameters for basic types that are supported
152      */
153     @DataProvider(name = "createCachedRowSetsToUse")
createCachedRowSetsToUse()154     private Object[][] createCachedRowSetsToUse() throws SQLException {
155         CachedRowSet crs = rsf.createCachedRowSet();
156         initCoffeesMetaData(crs);
157         createCoffeesRows(crs);
158         // Make sure you are not on the insertRow
159         crs.moveToCurrentRow();
160         CachedRowSet crs1 = rsf.createCachedRowSet();
161         initSuppliersMetaData(crs1);
162         createSuppiersRows(crs1);
163         // Make sure you are not on the insertRow
164         crs1.moveToCurrentRow();
165         return new Object[][]{
166             {crs, crs1}
167         };
168     }
169 
170     /*
171      * Validate that the correct coffees are returned for SUP_ID
172      */
validateResults(final JoinRowSet jrs)173     private void validateResults(final JoinRowSet jrs) throws SQLException {
174         List<Integer> results = new ArrayList<>();
175         jrs.beforeFirst();
176         while (jrs.next()) {
177             if (jrs.getInt(JOIN_COLNAME) == SUP_ID) {
178                 results.add(jrs.getInt("COF_ID"));
179             }
180         }
181         assertEquals(results.toArray(), EXPECTED);
182     }
183 
184     /*
185      * Join two CachedRowSets specifying a column name to join against
186      */
187     @Test(dataProvider = "createCachedRowSetsToUse")
joinRowSetTests0000(CachedRowSet crs, CachedRowSet crs1)188     public void joinRowSetTests0000(CachedRowSet crs, CachedRowSet crs1)
189             throws Exception {
190 
191         try (JoinRowSet jrs = newInstance()) {
192             jrs.addRowSet(crs, JOIN_COLNAME);
193             jrs.addRowSet(crs1, JOIN_COLNAME);
194             validateResults(jrs);
195             crs.close();
196             crs1.close();
197         }
198     }
199 
200     /*
201      * Join two CachedRowSets specifying a column index to join against
202      */
203     @Test(dataProvider = "createCachedRowSetsToUse")
joinRowSetTests0001(CachedRowSet crs, CachedRowSet crs1)204     public void joinRowSetTests0001(CachedRowSet crs, CachedRowSet crs1)
205             throws Exception {
206 
207         try (JoinRowSet jrs = newInstance()) {
208             jrs.addRowSet(crs, COFFEES_JOIN_COLUMN_INDEX);
209             jrs.addRowSet(crs1, SUPPLIERS_JOIN_COLUMN_INDEX);
210             validateResults(jrs);
211             crs.close();
212             crs1.close();
213         }
214     }
215 
216     /*
217      * Join two CachedRowSets specifying a column name to join against
218      */
219     @Test(dataProvider = "createCachedRowSetsToUse")
joinRowSetTests0002(CachedRowSet crs, CachedRowSet crs1)220     public void joinRowSetTests0002(CachedRowSet crs, CachedRowSet crs1)
221             throws Exception {
222 
223         try (JoinRowSet jrs = newInstance()) {
224             RowSet[] rowsets = {crs, crs1};
225             String[] joinCols = {JOIN_COLNAME, JOIN_COLNAME};
226             jrs.addRowSet(rowsets, joinCols);
227             validateResults(jrs);
228             crs.close();
229             crs1.close();
230         }
231     }
232 
233     /*
234      * Join two CachedRowSets specifying a column index to join against
235      */
236     @Test(dataProvider = "createCachedRowSetsToUse")
joinRowSetTests0003(CachedRowSet crs, CachedRowSet crs1)237     public void joinRowSetTests0003(CachedRowSet crs, CachedRowSet crs1)
238             throws Exception {
239 
240         try (JoinRowSet jrs = newInstance()) {
241             RowSet[] rowsets = {crs, crs1};
242             int[] joinCols = {COFFEES_JOIN_COLUMN_INDEX,
243                 SUPPLIERS_JOIN_COLUMN_INDEX};
244             jrs.addRowSet(rowsets, joinCols);
245             validateResults(jrs);
246             crs.close();
247             crs1.close();
248         }
249     }
250 
251     /*
252      * Join two CachedRowSets specifying a column name to join against
253      */
254     @Test(dataProvider = "createCachedRowSetsToUse")
joinRowSetTests0005(CachedRowSet crs, CachedRowSet crs1)255     public void joinRowSetTests0005(CachedRowSet crs, CachedRowSet crs1)
256             throws Exception {
257 
258         try (JoinRowSet jrs = newInstance()) {
259             crs.setMatchColumn(JOIN_COLNAME);
260             crs1.setMatchColumn(JOIN_COLNAME);
261             jrs.addRowSet(crs);
262             jrs.addRowSet(crs1);
263             validateResults(jrs);
264             crs.close();
265             crs1.close();
266         }
267     }
268 
269     /*
270      * Join two CachedRowSets specifying a column index to join against
271      */
272     @Test(dataProvider = "createCachedRowSetsToUse")
joinRowSetTests0006(CachedRowSet crs, CachedRowSet crs1)273     public void joinRowSetTests0006(CachedRowSet crs, CachedRowSet crs1)
274             throws Exception {
275 
276         try (JoinRowSet jrs = newInstance()) {
277             crs.setMatchColumn(COFFEES_JOIN_COLUMN_INDEX);
278             crs1.setMatchColumn(SUPPLIERS_JOIN_COLUMN_INDEX);
279 
280             jrs.addRowSet(crs);
281             jrs.addRowSet(crs1);
282             validateResults(jrs);
283             crs.close();
284             crs1.close();
285         }
286     }
287 
288     // Disabled tests due to bugs in JoinRowSet
289     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0004(CachedRowSet rs)290     public void commonCachedRowSetTest0004(CachedRowSet rs) throws Exception {
291     }
292 
293     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0005(CachedRowSet rs)294     public void commonCachedRowSetTest0005(CachedRowSet rs) throws Exception {
295     }
296 
297     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0008(CachedRowSet rs)298     public void commonCachedRowSetTest0008(CachedRowSet rs) throws Exception {
299     }
300 
301     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0026(CachedRowSet rs)302     public void commonCachedRowSetTest0026(CachedRowSet rs) throws Exception {
303     }
304 
305     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0027(CachedRowSet rs)306     public void commonCachedRowSetTest0027(CachedRowSet rs) throws Exception {
307     }
308 
309     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0053(CachedRowSet rs)310     public void commonCachedRowSetTest0053(CachedRowSet rs) throws Exception {
311     }
312 
313     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0054(CachedRowSet rs)314     public void commonCachedRowSetTest0054(CachedRowSet rs) throws Exception {
315     }
316 
317     @Test(dataProvider = "rowSetType", enabled = false)
commonCachedRowSetTest0055(CachedRowSet rs)318     public void commonCachedRowSetTest0055(CachedRowSet rs) throws Exception {
319     }
320 
321     @Test(dataProvider = "rowSetType")
WebRowSetTest0009(WebRowSet wrs1)322     public void WebRowSetTest0009(WebRowSet wrs1) throws Exception {
323     }
324 }
325