Package org.jooq

Examples of org.jooq.SelectQuery

210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
    }

    @Test
    public void testFetch() throws Exception {
        SelectQuery q = create().selectQuery();
        q.addFrom(TAuthor());
        q.addSelect(TAuthor().getFields());
        q.addOrderBy(TAuthor_LAST_NAME());

        Result<?> result = q.fetch();

        assertEquals(2, result.size());
        assertEquals("Coelho", result.get(0).getValue(TAuthor_LAST_NAME()));
        assertEquals("Orwell", result.get(1).getValue(TAuthor_LAST_NAME()));

        try {
            q.fetchOne();
            fail();
        }
        catch (InvalidResultException expected) {}

        Record record = q.fetchAny();
        assertEquals("Coelho", record.getValue(TAuthor_LAST_NAME()));
    }
View Full Code Here
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
        super(delegate);
    }

    @Test
    public void testSelectSimpleQuery() throws Exception {
        SelectQuery q = create().selectQuery();
        Field<Integer> f1 = val(1).as("f1");
        Field<Double> f2 = val(2d).as("f2");
        Field<String> f3 = val("test").as("f3");

        q.addSelect(f1);
        q.addSelect(f2);
        q.addSelect(f3);

        int i = q.execute();
        Result<?> result = q.getResult();

        assertEquals(1, i);
        assertEquals(1, result.size());
        assertEquals(3, result.getFields().size());
        assertTrue(result.getFields().contains(f1));
View Full Code Here
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
        assertEquals("test", result.get(0).getValue(f3));
    }

    @Test
    public void testSelectQuery() throws Exception {
        SelectQuery q = create().selectQuery();
        q.addFrom(TAuthor());
        q.addSelect(TAuthor().getFields());
        q.addOrderBy(TAuthor_LAST_NAME());

        int rows = q.execute();
        Result<?> result = q.getResult();

        assertEquals(2, rows);
        assertEquals(2, result.size());
        assertEquals("Coelho", result.get(0).getValue(TAuthor_LAST_NAME()));
        assertEquals("Orwell", result.get(1).getValue(TAuthor_LAST_NAME()));
View Full Code Here
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
        assertEquals("test", result.getValue(0, 7));
    }

    @Test
    public void testCombinedSelectQuery() throws Exception {
        SelectQuery q1 = create().selectQuery();
        SelectQuery q2 = create().selectQuery();

        q1.addFrom(TBook());
        q2.addFrom(TBook());

        q1.addConditions(TBook_AUTHOR_ID().equal(1));
        q2.addConditions(TBook_TITLE().equal("Brida"));

        // Use union all because of clob's
        Select<?> union = q1.unionAll(q2);
        int rows = union.execute();
        assertEquals(3, rows);
View Full Code Here
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
        Field<Integer> f9b = F317Field(FNumberField(1), FNumberField(2), FNumberField(3), FNumberField(4)).cast(Integer.class);

        // Null argument checks
        Field<Integer> f10 = FAuthorExistsField(null).cast(Integer.class);

        SelectQuery q = create().selectQuery();
        q.addSelect(
            f1a, f2a, f3a, f4a, f5a, f6a, f7a, f8a, f9a,
            f1b, f2b, f3b, f4b, f5b, f6b, f7b, f8b, f9b, f10);
        q.execute();
        Result<Record> result = q.getResult();

        assertEquals(1, result.size());
        assertEquals("1", result.get(0).getValueAsString(f1a));
        assertEquals("0", result.get(0).getValueAsString(f2a));
        assertEquals("1", result.get(0).getValueAsString(f3a));
View Full Code Here
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
        assertEquals(8, create().nextval(sequence).intValue());
    }

    @Test
    public void testAccessInternalRepresentation() throws Exception {
        SelectQuery query =
        create().select(TBook_ID())
                .from(TBook())
                .where(TBook_ID().in(1, 2, 3))
                .getQuery();

        query.addGroupBy(TBook_ID());
        query.addHaving(count().greaterOrEqual(1));
        query.addOrderBy(TBook_ID());
        query.execute();

        Result<Record> result = query.getResult();

        assertEquals(3, result.size());
        assertEquals(Arrays.asList(1, 2, 3), result.getValues(TBook_ID()));
    }
View Full Code Here
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
        Field<Integer> a_authorID = a.getField(TAuthor_ID());
        Field<Integer> b_authorID = b.getField(TBook_AUTHOR_ID());
        Field<String> b_title = b.getField(TBook_TITLE());

        SelectQuery q2 = create().selectQuery();
        q2.addFrom(a);
        q2.addJoin(b, b_authorID.equal(a_authorID));
        q2.addConditions(b_title.notEqual("1984"));
        q2.addOrderBy(lower(b_title));

        int rows1 = q1.execute();
        int rows2 = q2.execute();

        assertEquals(3, rows1);
        assertEquals(3, rows2);

        Result<L> result1 = q1.getResult();
        Result<?> result2 = q2.getResult();

        assertEquals("Animal Farm", result1.get(0).getValue(VLibrary_TITLE()));
        assertEquals("Animal Farm", result2.get(0).getValue(b_title));

        assertEquals("Brida", result1.get(1).getValue(VLibrary_TITLE()));
View Full Code Here
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
        if (getDialect() == SQLDialect.SQLITE) {
            log.info("SKIPPING", "position function test");
            return;
        }

        SelectQuery q = create().selectQuery();
        q.addFrom(VLibrary());

        Field<Integer> position = position(VLibrary_AUTHOR(), "o").as("p");
        q.addSelect(VLibrary_AUTHOR());
        q.addSelect(position);

        // https://issues.apache.org/jira/browse/DERBY-5005
        q.addOrderBy(field(VLibrary_AUTHOR().getName()));

        q.execute();
        Record r1 = q.getResult().get(1); // George Orwell
        Record r2 = q.getResult().get(2); // Paulo Coelho

        assertEquals(Integer.valueOf(3), r1.getValue(position));
        assertEquals(Integer.valueOf(5), r2.getValue(position));

        // Implicit check on the rownum function in oracle dialect
View Full Code Here
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
            .value(1)
            .when(1, "A")
            .when(2, "B")
            .otherwise("C");

        SelectQuery query = create().selectQuery();
        query.addSelect(case1, case2, case3);
        query.addFrom(TBook());
        query.addOrderBy(TBook_PUBLISHED_IN());
        query.execute();

        Result<Record> result = query.getResult();
        assertEquals(null, result.getValue(0, case1));
        assertEquals(null, result.getValue(1, case1));
        assertEquals(null, result.getValue(2, case1));
        assertEquals(null, result.getValue(3, case1));

        assertEquals("Orwell", result.getValue(0, case2));
        assertEquals("Orwell", result.getValue(1, case2));
        assertEquals("unknown", result.getValue(2, case2));
        assertEquals("unknown", result.getValue(3, case2));

        assertEquals("A", result.getValue(0, case3));
        assertEquals("A", result.getValue(1, case3));
        assertEquals("A", result.getValue(2, case3));
        assertEquals("A", result.getValue(3, case3));

        Field<String> case4 = decode()
            .when(TBook_PUBLISHED_IN().equal(1948), "probably orwell")
            .when(TBook_PUBLISHED_IN().equal(1988), "probably coelho")
            .otherwise("don't know").as("case3");

        query = create().selectQuery();
        query.addSelect(case4);
        query.addFrom(TBook());
        query.addOrderBy(TBook_PUBLISHED_IN());
        query.execute();

        result = query.getResult();

        // Note: trims are necessary, as certain databases use
        // CHAR datatype here, not VARCHAR
        assertEquals("don't know", result.getValue(0, case4).trim());
        assertEquals("probably orwell", result.getValue(1, case4).trim());
View Full Code Here
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
            .where(TAuthor_FIRST_NAME().equal("George")).fetchOne(0));

        assertEquals("1ab45", create().select(concat(val(1), val("ab"), val(45))).fetchOne(0));

        // Standard String functions
        SelectQuery q = create().selectQuery();
        Field<String> constant = val("abc");

        switch (getDialect()) {

            // DERBY does not have a replace function
            case DERBY:
                log.info("SKIPPING", "replace function test");
                break;

            // These two tests will validate #154
            default: {
                Field<String> x = replace(constant, "b", "x");
                Field<String> y = replace(constant, "b", "y");
                Field<String> z = replace(constant, "b");
                Record record = create().select(x, y, z).fetchOne();

                assertEquals("axc", record.getValue(x));
                assertEquals("ayc", record.getValue(y));
                assertEquals("ac", record.getValue(z));
            }
        }

        Field<Integer> length = length(constant);
        Field<Integer> charLength = charLength(constant);
        Field<Integer> bitLength = bitLength(constant);
        Field<Integer> octetLength = octetLength(constant);
        q.addSelect(length, charLength, bitLength, octetLength);
        q.execute();

        Record record = q.getResult().get(0);

        assertEquals(Integer.valueOf(3), record.getValue(length));
        assertEquals(Integer.valueOf(3), record.getValue(charLength));

        switch (getDialect()) {
View Full Code Here
TOP

Related Classes of org.jooq.SelectQuery

  • org.jooq.examples.Library
  • org.jooq.test._.testcases.FetchTests
  • org.jooq.test._.testcases.FunctionTests
  • org.jooq.test._.testcases.GeneralTests
  • org.jooq.test._.testcases.JoinTests
  • org.jooq.test._.testcases.RoutineAndUDTTests
  • org.jooq.test._.testcases.SelectTests
  • org.jooq.test.jOOQTest
  • org.jooq.util.db2.DB2Database
    • Copyright © 2018 www.massapicom. All rights reserved.
    All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.