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
54 protected JoinRowSet newInstance() throws SQLException {
55 return rsf.createJoinRowSet();
56 }
57
58 /*
59 * Initializes the SUPPLIERS metadata
60 */
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 */
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")
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 */
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")
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")
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")
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")
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")
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")
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)
290 public void commonCachedRowSetTest0004(CachedRowSet rs) throws Exception {
291 }
292
293 @Test(dataProvider = "rowSetType", enabled = false)
294 public void commonCachedRowSetTest0005(CachedRowSet rs) throws Exception {
295 }
296
297 @Test(dataProvider = "rowSetType", enabled = false)
298 public void commonCachedRowSetTest0008(CachedRowSet rs) throws Exception {
299 }
300
301 @Test(dataProvider = "rowSetType", enabled = false)
302 public void commonCachedRowSetTest0026(CachedRowSet rs) throws Exception {
303 }
304
305 @Test(dataProvider = "rowSetType", enabled = false)
306 public void commonCachedRowSetTest0027(CachedRowSet rs) throws Exception {
307 }
308
309 @Test(dataProvider = "rowSetType", enabled = false)
310 public void commonCachedRowSetTest0053(CachedRowSet rs) throws Exception {
311 }
312
313 @Test(dataProvider = "rowSetType", enabled = false)
314 public void commonCachedRowSetTest0054(CachedRowSet rs) throws Exception {
315 }
316
317 @Test(dataProvider = "rowSetType", enabled = false)
318 public void commonCachedRowSetTest0055(CachedRowSet rs) throws Exception {
319 }
320
321 @Test(dataProvider = "rowSetType")
322 public void WebRowSetTest0009(WebRowSet wrs1) throws Exception {
323 }
324 }