@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()));
}
