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 */
23package test.rowset.joinrowset;
24
25import java.sql.SQLException;
26import java.sql.Types;
27import java.util.ArrayList;
28import java.util.List;
29import javax.sql.RowSet;
30import javax.sql.rowset.CachedRowSet;
31import javax.sql.rowset.JoinRowSet;
32import javax.sql.rowset.RowSetMetaDataImpl;
33import javax.sql.rowset.WebRowSet;
34import static org.testng.Assert.assertEquals;
35import org.testng.annotations.DataProvider;
36import org.testng.annotations.Test;
37import test.rowset.webrowset.CommonWebRowSetTests;
38
39public 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}
325