Step 1: 準備(Preparation)
如果還沒有下載JOOQ,請先下載:
http://www.jooq.org/download
或者,可以創建一個Maven依賴項來下載jOOQ :
開源版 Open Source Edition
商業版 Commercial Editions(Java 8+)
商業版 Commercial Editions (Java 6+)
商業版 (試用)Commercial Editions (Free Trial)
注意,只有jOOQ開放源碼版本可以從Maven Central獲得。如果您使用的是jOOQ專業版或jOOQ企業版,則必須在本地Nexus或本地Maven緩存中手動安裝jOOQ。有關更多信息,請參閱授權許可頁面。
請參閱手冊中有關代碼生成配置的部分,以瞭解如何在Maven中使用jOOQ的代碼生成器。
對於本例,我們將使用MySQL。如果你還沒有下載MySQL Connector/J,請在這裡下載:
http://dev.mysql.com/downloads/connector/j/
Step 2: 你的數據庫(Your database)
我們將創建一個名為“library”的數據庫和一個相應的“author”表。通過命令行客戶端連接到MySQL,輸入以下命令:
CREATE DATABASE `library`;
USE `library`;
CREATE TABLE `author` (
`id` int NOT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
Step 3: 代碼生成 (Code generation)
在這一步中,我們將使用jOOQ的命令行工具來生成映射到我們剛剛創建的Author表的類。更多關於如何設置jOOQ代碼生成器的詳細信息可以在第6章裡找到。
生成schema的最簡單方法是將jOOQ jar文件(應該有3個)和MySQL Connector jar文件複製到一個臨時目錄。然後,創建一個庫。如下所示的xml:
用任何具有查詢數據庫元數據的適當特權的用戶替換user。您還需要查看其他值並根據需要進行替換。
以下是兩個有趣的特性:
- generator.target.package - 將此設置為要為生成的類創建的父包。 此處的test.generated包,將用來保存生成的test.generated.Author和test.generated.AuthorRecord兩個類.
- generator.target.directory - 輸出到的目錄
- 一旦您擁有了JAR文件和library.xml在temp目錄中,在Windows計算機上鍵入這個:
java -classpath jooq-3.11.4.jar:jooq-meta-3.11.4.jar:jooq-codegen-3.11.4.jar:mysql-connector-java-5.1.18-bin.jar:. org.jooq.codegen.GenerationTool library.xml
注意:jOOQ將嘗試從你的classpath中加載library.xml。這也是為什麼classpath尾部有一個點符號(.)的原因。如果在classpath中找不到這個文件,jOOQ將從當前工作目錄文件系統進行檢索。
用真實的文件名替換文件名。在本例中,使用的是jOOQ 3.11.4。如果一切正常,您應該在控制檯輸出中看到:
Nov 1, 2011 7:25:06 PM org.jooq.impl.JooqLogger info
INFO: Initialising properties : /library.xml
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Database parameters
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: ----------------------------------------------------------
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: dialect : MYSQL
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: schema : library
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: target dir : C:/workspace/MySQLTest/src
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: target package : test.generated
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: ----------------------------------------------------------
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Emptying : C:/workspace/MySQLTest/src/test/generated
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating classes in : C:/workspace/MySQLTest/src/test/generated
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating schema : Library.java
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Schema generated : Total: 122.18ms
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Sequences fetched : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Tables fetched : 5 (5 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating tables : C:/workspace/MySQLTest/src/test/generated/tables
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: ARRAYs fetched : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Enums fetched : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: UDTs fetched : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating table : Author.java
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Tables generated : Total: 680.464ms, +558.284ms
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating Keys : C:/workspace/MySQLTest/src/test/generated/tables
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Keys generated : Total: 718.621ms, +38.157ms
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Generating records : C:/workspace/MySQLTest/src/test/generated/tables/records
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Generating record : AuthorRecord.java
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Table records generated : Total: 782.545ms, +63.924ms
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Routines fetched : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Packages fetched : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: GENERATION FINISHED! : Total: 791.688ms, +9.143ms
在Mac操作系統上,基於jooq3.11.4,執行如下命令
bogon:temp goldfish$ java -classpath jooq-3.11.4.jar:jooq-meta-3.11.4.jar:jooq-codegen-3.11.4.jar:mysql-connector-java-5.1.46.jar:. org.jooq.codegen.GenerationTool library.xml
輸出內容:
Thank you for using jOOQ 3.11.4
…………… 信息太多省略了
Step 4: 連接數據庫(Connect to your database)
讓我們在項目中編寫一個普通的main class,其中包含生成的classes:
// For convenience, always static import your generated tables and jOOQ functions to decrease verbosity:
import static test.generated.Tables.*;
import static org.jooq.impl.DSL.*;
import java.sql.*;
public class Main
{
public static void main(String[] args)
{
String userName = "root";
String password = "";
String url = "jdbc:mysql://localhost:3306/library";
// Connection is the only JDBC resource that we need
// PreparedStatement and ResultSet are handled by jOOQ, internally
try (Connection conn = DriverManager.getConnection(url, userName, password))
{
// ...
}
// For the sake of this tutorial, let's keep exception handling simple
catch (Exception e)
{
e.printStackTrace();
}
}
}
這是建立MySQL連接的標準代碼。
Step 5 : 查詢(Querying)
讓我們添加一個用jOOQ的查詢DSL構造的簡單查詢:
DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
Result
首先獲得DSLContext的一個實例,這樣我們就可以編寫一個簡單的SELECT查詢。我們將MySQL連接的實例傳遞給DSL。注意,DSLContext不會關閉連接。我們得自己做。
然後,我們使用jOOQ的查詢DSL返回一個Result實例。我們將在下一步中使用這個結果。
Step 6 : 遍歷(Iterating)
在查詢出結果的行之後,讓我們迭代結果並打印出數據:
for (Record r : result)
{
Integer id = r.getValue(AUTHOR.ID);
String firstName = r.getValue(AUTHOR.FIRST_NAME);
String lastName = r.getValue(AUTHOR.LAST_NAME);
System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
}
完整的程序現在應該是這樣的:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import static test.generated.Tables.*;
public class Main
{
public static void main(String[] args)
{
String userName = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/library?characterEncoding=UTF-8&useSSL=false";
Connection conn = null;
try
{
conn = DriverManager.getConnection(url, userName, password);
// 使用JOOQ進行查詢
DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
Result
for (Record r : result)
{
Integer id = r.getValue(AUTHOR.ID);
String firstName = r.getValue(AUTHOR.FIRST_NAME);
String lastName = r.getValue(AUTHOR.LAST_NAME);
System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
}
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
}
注意:import static test.generated.Tables.*;
如果沒有靜態引入,就需要通過Tables.AUTHOR進行使用。
Step 7: 探索(Explore!)
jOOQ已經發展成為一個全面的SQL庫。更多信息,請考慮文件:
http://www.jooq.org/learn
... explore the Javadoc:
http://www.jooq.org/javadoc/latest/
... or join the news group:
https://groups.google.com/forum/#!forum/jooq-user
鏈接:https://www.jianshu.com/p/e617c30ba108
原文來自http://www.jooq.org/learn
閱讀更多 IT碼將 的文章