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