拓展阅读

The jdbc pool for java.(java 手写 jdbc 数据库连接池实现)

The simple mybatis.（手写简易版 mybatis）

JOOQ

JOOQ 可以通过数据库直接生成 java 代码，通过 flent-api 进行数据库操作。

SQL builder

JOOQ 非常的灵活和强大。你可以用它来拼装 SQL 语句

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.
// For simplicity reasons, we're using the API to construct case-insensitive object references, here.
String sql = create.select(field("BOOK.TITLE"), field("AUTHOR.FIRST_NAME"), field("AUTHOR.LAST_NAME"))
                   .from(table("BOOK"))
                   .join(table("AUTHOR"))
                   .on(field("BOOK.AUTHOR_ID").eq(field("AUTHOR.ID")))
                   .where(field("BOOK.PUBLISHED_IN").eq(1948))
                   .getSQL();

结合代码生成

上面的字段都是我们手动指定的，可能会出错。

可以结合代码的自动生成+SQL构建：

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.
String sql = create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
                   .from(BOOK)
                   .join(AUTHOR)
                   .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                   .where(BOOK.PUBLISHED_IN.eq(1948))
                   .getSQL();

使用工具直接执行 SQL

// Typesafely execute the SQL statement directly with jOOQ
Result<Record3<String, String, String>> result =
create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(BOOK)
      .join(AUTHOR)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .where(BOOK.PUBLISHED_IN.eq(1948))
      .fetch();
  • 或者直接执行
// Use your favourite tool to construct SQL strings:
String sql = "SELECT title, first_name, last_name FROM book JOIN author ON book.author_id = author.id " +
             "WHERE book.published_in = 1984";

// Fetch results using jOOQ
Result<Record> result = create.fetch(sql);

// Or execute that SQL with JDBC, fetching the ResultSet with jOOQ:
ResultSet rs = connection.createStatement().executeQuery(sql);
Result<Record> result = create.fetch(rs);

CRUD

// Fetch an author
AuthorRecord author : create.fetchOne(AUTHOR, AUTHOR.ID.eq(1));

// Create a new author, if it doesn't exist yet
if (author == null) {
    author = create.newRecord(AUTHOR);
    author.setId(1);
    author.setFirstName("Dan");
    author.setLastName("Brown");
}

// Mark the author as a "distinguished" author and store it
author.setDistinguished(1);

// Executes an update on existing authors, or insert on new ones
author.store();