Jooq Java Object Oriented Querying
get back in control of your sql
jOOQ generates Java code from your database and lets you build type safe SQL queries through its fluent API

DSL  
DSLContext

DSL Domain Specific language



Table<Record> table = DSL.table("test_table"); 
DSLContext dslContext = DSL.using(connection);


DSLContext dslContext = DSL.using(connection);
Result<Record> fetch = dslContext.select().from(table).where("status = 1").and("id > 9").orderBy(DSL.field("time").asc()).fetch();
for (Object aResult : fetch) {
  Record record = (Record) aResult;
  System.out.println(record);
}







import java.sql.Connection;
import java.util.List;

import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.impl.DSL;

public class JooqDemo {

	private static final String TABLE = "test001";

	public static void main(String[] args) throws Exception {
		System.out.println("hello,jooq");

		Connection conn = Utils.getConn();
		System.out.println("conn=" + conn + "," + conn.getAutoCommit() + "," + conn.getTransactionIsolation());

		String createSql = "create table test001(id int primary key auto_increment,name varchar(32),age int)";
		DSLContext dslContext = DSL.using(conn);

		int createTableResult = dslContext.execute(createSql);
		System.out.println("createTableResult=" + createTableResult);

		String insertSql = "insert into test001(id,name,age) values(1,'dog',3)";

		int insertResult = dslContext.execute(insertSql);
		System.out.println("insertResult=" + insertResult);

		String insertSql2 = "insert into test001(id,name,age) values(2,'cat',7)";

		int insertResult2 = dslContext.execute(insertSql2);
		System.out.println("insertResult2=" + insertResult2);

		dslContext.insertInto(DSL.table(TABLE)).set(DSL.field("id"), 3).set(DSL.field("name"), "tiger")
				.set(DSL.field("age"), 9).execute();

		dslContext.update(DSL.table(TABLE)).set(DSL.field("age"), 10)
				// .where("id=3")
				.where(DSL.field("id").eq(3)).execute();

		Result<Record> pets = dslContext.select().from(TABLE).fetch();

		for (Record pet : pets) {
			System.out.println(pet);
		}
		for (Record pet : pets) {
			// 字段名需要大写
			System.out.println(pet.get("ID") + "," + pet.get("NAME") + "," + pet.get("AGE"));
		}

		// NAME
		List<?> names = dslContext.select().from("test001").fetch("NAME");
		System.out.println(names);

		pets = dslContext.select().from(TABLE).where("id>1").and("age>1").fetch();

		for (Record pet : pets) {
			System.out.println(pet);
		}

		List<Pet> pets2 = dslContext.select(DSL.field("ID").as("id")).select(DSL.field("NAME").as("name"))
				.select(DSL.field("AGE").as("age")).from("test001").fetchInto(Pet.class);

		for (Pet pet : pets2) {
			System.out.println(pet);
		}

		////

		conn.close();

	}

}



完整代码
https://gitee.com/dyyx/hellocode/blob/master/demo/jooqdemo/src/main/java/dyyx/JooqDemo.java



 java 数据库访问 持久层 框架