@Test public void testManager() throws Exception { jOOQAbstractTest.reset = false; List<A> select = create().fetch(TAuthor()); assertEquals(2, select.size()); select = create().fetch(TAuthor(), TAuthor_FIRST_NAME().equal("Paulo")); assertEquals(1, select.size()); assertEquals("Paulo", select.get(0).getValue(TAuthor_FIRST_NAME())); try { create().fetchOne(TAuthor()); fail(); } catch (InvalidResultException expected) {} A selectOne = create().fetchOne(TAuthor(), TAuthor_FIRST_NAME().equal("Paulo")); assertEquals("Paulo", selectOne.getValue(TAuthor_FIRST_NAME())); // Some CRUD operations A author = create().newRecord(TAuthor()); author.setValue(TAuthor_ID(), 15); author.setValue(TAuthor_LAST_NAME(), "Kästner"); assertEquals(1, create().executeInsert(author)); author.refresh(); assertEquals(Integer.valueOf(15), author.getValue(TAuthor_ID())); assertEquals("Kästner", author.getValue(TAuthor_LAST_NAME())); assertEquals(0, create().executeUpdate(author, TAuthor_ID().equal(15))); author.setValue(TAuthor_FIRST_NAME(), "Erich"); assertEquals(1, create().executeUpdate(author, TAuthor_ID().equal(15))); author = create().fetchOne(TAuthor(), TAuthor_FIRST_NAME().equal("Erich")); assertEquals(Integer.valueOf(15), author.getValue(TAuthor_ID())); assertEquals("Erich", author.getValue(TAuthor_FIRST_NAME())); assertEquals("Kästner", author.getValue(TAuthor_LAST_NAME())); // [#1692] Check for new simplified update method author.setValue(TAuthor_FIRST_NAME(), "Fritz"); assertEquals(1, create().executeUpdate(author)); author = create().fetchOne(TAuthor(), TAuthor_FIRST_NAME().equal("Fritz")); assertEquals(Integer.valueOf(15), author.getValue(TAuthor_ID())); assertEquals("Fritz", author.getValue(TAuthor_FIRST_NAME())); assertEquals("Kästner", author.getValue(TAuthor_LAST_NAME())); create().executeDelete(author); assertEquals(null, create().fetchOne(TAuthor(), TAuthor_FIRST_NAME().equal("Erich"))); }
@Test public void testPlainSQL() throws Exception { jOOQAbstractTest.reset = false; // Field and Table // --------------- Field<Integer> ID = field(TBook_ID().getName(), Integer.class); Result<Record> result = create().select().from("t_book").orderBy(ID).fetch(); assertEquals(4, result.size()); assertEquals(BOOK_IDS, result.getValues(ID)); assertEquals(BOOK_TITLES, result.getValues(TBook_TITLE())); // [#271] Aliased plain SQL table Result<Record1<Integer>> result2 = create().select(ID).from("(select * from t_book) b").orderBy(ID).fetch(); assertEquals(4, result2.size()); assertEquals(BOOK_IDS, result2.getValues(ID)); // [#271] Aliased plain SQL table Result<Record> result3 = create().select().from("(select * from t_book) b").orderBy(ID).fetch(); assertEquals(4, result3.size()); assertEquals( Arrays.asList(1, 2, 3, 4), result3.getValues(ID)); // [#836] Aliased plain SQL table Result<Record> result4 = create().select().from(table("t_book").as("b")).orderBy(ID).fetch(); assertEquals(4, result4.size()); assertEquals(BOOK_IDS, result4.getValues(ID)); // [#271] Check for aliased nested selects. The DescribeQuery does not seem to work // [#836] Aliased plain SQL nested select Result<Record> result5 = create().select().from(table("(select * from t_book)").as("b")).orderBy(ID).fetch(); assertEquals(4, result5.size()); assertEquals( Arrays.asList(1, 2, 3, 4), result5.getValues(ID)); // Field, Table and Condition // -------------------------- Field<?> LAST_NAME = field(TAuthor_LAST_NAME().getName()); Field<?> COUNT1 = field("count(*) x"); Field<?> COUNT2 = field("count(*) y", Integer.class); Result<?> result6 = create() .select(LAST_NAME, COUNT1, COUNT2) .from("t_author a") .join("t_book b").on("a.id = b.author_id") .where("b.title != 'Brida'") .groupBy(LAST_NAME) .orderBy(LAST_NAME).fetch(); assertEquals(2, result6.size()); assertEquals("Coelho", result6.getValue(0, LAST_NAME)); assertEquals("Orwell", result6.getValue(1, LAST_NAME)); assertEquals("1", result6.get(0).getValue(COUNT1, String.class)); assertEquals("2", result6.get(1).getValue(COUNT1, String.class)); assertEquals(Integer.valueOf(1), result6.getValue(0, COUNT2)); assertEquals(Integer.valueOf(2), result6.getValue(1, COUNT2)); // Field, Table and Condition // -------------------------- Result<?> result7 = create().select(LAST_NAME, COUNT1, COUNT2) .from("t_author a") .join("t_book b").on("a.id = b.author_id") .where("b.title != 'Brida'") .groupBy(LAST_NAME) .having("{count}(*) = ?", 1).fetch(); assertEquals(1, result7.size()); assertEquals("Coelho", result7.getValue(0, LAST_NAME)); assertEquals("1", result7.get(0).getValue(COUNT1, String.class)); assertEquals(Integer.valueOf(1), result7.getValue(0, COUNT2)); // Query // ----- assertEquals(1, create() .query("insert into t_author (id, first_name, last_name) values (?, ?, ?)", 3, "Michèle", "Roten") .execute()); A author = create().fetchOne(TAuthor(), TAuthor_ID().equal(3)); assertEquals(Integer.valueOf(3), author.getValue(TAuthor_ID())); assertEquals("Michèle", author.getValue(TAuthor_FIRST_NAME())); assertEquals("Roten", author.getValue(TAuthor_LAST_NAME())); // [#724] Check for correct binding when passing // --------------------------------------------- // - (Object[]) null: API misuse // - (Object) null: Single null bind value assertEquals(1, create() .query("update t_author set first_name = ? where id = 3", (Object[]) null) .execute()); author.refresh(); assertEquals(Integer.valueOf(3), author.getValue(TAuthor_ID())); assertEquals(null, author.getValue(TAuthor_FIRST_NAME())); assertEquals("Roten", author.getValue(TAuthor_LAST_NAME())); // Reset name assertEquals(1, create() .query("update t_author set first_name = ? where id = 3", "Michèle") .execute()); author.refresh(); assertEquals(Integer.valueOf(3), author.getValue(TAuthor_ID())); assertEquals("Michèle", author.getValue(TAuthor_FIRST_NAME())); assertEquals("Roten", author.getValue(TAuthor_LAST_NAME())); // [#724] Check for correct binding when passing (Object) null assertEquals(1, create() .query("update t_author set first_name = ? where id = 3", (Object) null) .execute()); author.refresh(); assertEquals(Integer.valueOf(3), author.getValue(TAuthor_ID())); assertEquals(null, author.getValue(TAuthor_FIRST_NAME())); assertEquals("Roten", author.getValue(TAuthor_LAST_NAME())); // Function // -------- assertEquals("ABC", create().select(function("upper", String.class, val("aBc"))).fetchOne(0)); assertEquals("abc", create().select(function("lower", SQLDataType.VARCHAR, val("aBc"))).fetchOne(0)); // Fetch // ----- Result<Record> books = create().fetch("select * from t_book where id in (?, ?) order by id", 1, 2); assertNotNull(books); assertEquals(2, books.size()); assertEquals(Integer.valueOf(1), books.getValue(0, TBook_ID())); assertEquals(Integer.valueOf(2), books.getValue(1, TBook_ID())); assertEquals(Integer.valueOf(1), books.getValue(0, TBook_AUTHOR_ID())); assertEquals(Integer.valueOf(1), books.getValue(1, TBook_AUTHOR_ID())); }