Empfohlen
Weitere ähnliche Inhalte
Was ist angesagt?
Was ist angesagt? (15)
Ähnlich wie JOOQ and Flyway
Ähnlich wie JOOQ and Flyway (20)
Mehr von Dmitry Buzdin
Mehr von Dmitry Buzdin (20)
Kürzlich hochgeladen
Kürzlich hochgeladen (20)
JOOQ and Flyway
- 1. Getting Started with JOOQ and Flyway Wiring up your Java app with DB JUG.LV 08.05.2014 RIGA DMITRY LEBEDEV & RUSTAM ARSLANOV @ 28STONE
- 2. About Dmitry - developer Rustam - DevOps guy Customer - finances Project - set of microservices http://www.28stone.com
- 3. Motivation Use simple tools Try something new Keep codebase maintainable
- 4. Flyway SQL scripts for migration Java beans for complex logic Simple commands
- 5. JOOQ Java API corresponds to SQL semantics ActiveRecord & JPA beans generation Strict typing in Java
- 7. Simple Tool: JOOQ List<BookRecord> list = create.select(field("BOOK.TITLE"), field("AUTHOR. FIRST_NAME"), field("AUTHOR.LAST_NAME")) .from(table("BOOK")) .join(table("AUTHOR")) .on(field("BOOK.AUTHOR_ID").equal(field("AUTHOR.ID"))) .where(field("BOOK.PUBLISHED_IN").equal(1948)) .fetchInto(BookRecord.class);
- 13. Build systems support Flyway: command line (java) java API ant maven 2,3 gradle sbt Jooq: command line (java) java API maven 2,3 gradle (3d party)
- 14. domain-objects build workflow (gradle) clean resource preparation deployflyway generated compilation jar main compilation ● resources filtering clean init repair validate info migrate ● generated compile jooq generateJooq
- 15. domain-objects deploy workflow (gradle) clean deployflyway get dependencies clean init repair validate info migrate ... ... ... ● get domain- objects ● deploy or run application
- 16. Flyway in CI (jenkins)
- 17. Flyway versioning One or more numeric parts Separated by a dot (.) or an underscore (_) Underscores are replaced by dots at runtime Leading zeroes are ignored in each part 1 001 5.2 5_2 (5.2 at runtime) 1.2.3.4.5.6.7.8.9 205.68 20130115113556 2013.1.15.11.35.56 2013.01.15.11.35.56 1__CreateTable.sql 001__AlterTable.sql 5.2__InsertDate.sql 5_2__DeleteData.sql 1.2.3.4.5.6.7.8.9__AnotherScript.sql 205.68__Transform.sql 20130115113556__InsertView.sql 2013.1.15.11.35.56__AddService.sql 2013.01.15.11.35.56__Delete.sql
- 18. Let’s Do Some Programming! Building queries / SQL generation Fetching results Updating records
- 19. Not covering: ● Lazy fetch ● Transaction management ● Constraints & Relationships
- 20. DSLContext - starting point DSLContext create = DSL.using(conn, SQLDialect.MYSQL); Result<Record> result = create.select().from(AUTHOR).fetch();
- 21. Building queries create.select(field("BOOK.TITLE"), field("AUTHOR.FIRST_NAME"), field ("AUTHOR.LAST_NAME")) .from(table("BOOK")) .join(table("AUTHOR")) .on(field("BOOK.AUTHOR_ID").equal(field("AUTHOR.ID"))) .where(field("BOOK.PUBLISHED_IN").equal(1948)) .fetch();
- 22. Building queries create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)) .where(BOOK.PUBLISHED_IN.equal(1948)) .fetch();
- 23. Building queries create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)) .where(BOOK.PUBLISHED_IN.equal(1948)) .orderBy(BOOK.TITLE.asc()) .fetch();
- 24. Building queries create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)) .where(BOOK.PUBLISHED_IN.equal(1948)) .orderBy(BOOK.TITLE.asc()).limit(10).offset(100) .fetch();
- 25. SQL Generation String sql = create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR. LAST_NAME) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)) .where(BOOK.PUBLISHED_IN.equal(1948)) .orderBy(BOOK.TITLE.asc()).limit(10).offset(100) .getSQL();
- 26. Fetching results: Record create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)) .where(BOOK.PUBLISHED_IN.equal(1948)) .orderBy(BOOK.TITLE.asc()).limit(10).offset(100) .fetch();
- 28. List<BookRecord> result1 = create.selectFrom(BOOK)... Result<Record> result2 = create.select().from(BOOK)... // somewhere in iteration loop bookRecord.getTitle(); record.get(“title”); Record VS. TableRecord
- 33. Fetching results: POJOs create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)) .where(BOOK.PUBLISHED_IN.equal(1948)) .orderBy(BOOK.TITLE.asc()).limit(10).offset(100) .fetchInto(BookDTO.class);
- 34. Fetching results: Maps create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)) .where(BOOK.PUBLISHED_IN.equal(1948)) .orderBy(BOOK.TITLE.asc()).limit(10).offset(100) .fetchMaps();
- 35. Records: create // Create a new record BookRecord book1 = create.newRecord(BOOK); // Insert the record: INSERT INTO BOOK (TITLE) VALUES ('1984'); book1.setTitle("1984"); book1.store();
- 36. Records: update BookRecord book2 = create.fetchOne(BOOK, BOOK.ID.equal(id)); // Update the record: UPDATE BOOK SET TITLE = 'Animal Farm' WHERE ID = [id] book2.setTitle("Animal Farm"); book2.store();
- 37. Records: delete BookRecord book = create.fetchOne(BOOK, BOOK.ID.equal(5)); // Delete the book book.delete();
- 38. Records: batch changes // Fetch a bunch of books List<BookRecord> books = create.fetch(BOOK); // Modify the above books, and add some new ones: modify(books); addMore(books); // Batch-update and/or insert all of the above books create.batchStore(books);
- 39. Records: update using a query create.update(BOOK) .set(BOOK.IS_BESTSELLER, 1) .where(BOOK.SELLED_COPIES.greater(100000)) .execute();