JOOQ学习记录

一、下载JDBC驱动包

Java Data Base Connectivity：和各种数据库连通、交互的一系列API和规范

maven参考网站：https://mvnrepository.com/artifact/mysql/mysql-connector-java

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>

使用JDBC操作数据库：

// ------------直接操作------------
Class.forName("com.mysql.jdbc.Driver"); //指定连接类型
Connection connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?characterEncoding=UTF-8", "root", "txl"); //获取连接
Statement statement=connection.createStatement();
ResultSet resultSet=statement.executeQuery("select * from test");
while(resultSet.next())
{
    System.out.println(resultSet.getString("test_title"));
}
statement.close();
connection.close();

// ------------使用PreparedStatement------------
Connection connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?charset=utf8", "root", "txl"); //获取连接
Statement statement=connection.createStatement();
PreparedStatement statement= connection.prepareStatement("select * from test WHERE id=?");
statement.setInt(1,6);
ResultSet resultSet=statement.executeQuery();
while(resultSet.next())
{
    System.out.println(resultSet.getDate("test_date").toString());
}
statement.close();
connection.close();

问题：
1、每次连接执行，然后关闭，多次执行就要创建多次连接，好像有点浪费；
2、SQL都是硬编码，改了SQL就要重新编译；
3、目前的开发方式可能做点小功能没问题，做业务逻辑功能则会造成可读性、可维护性比较差。

二、使用DataSource（Tomcat-jdbc-pool）

下载地址：http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html (Commons DBCP的替代品)

可以不借助tomcat单独使用，参考：http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Standalone，需要添加2个maven库：

<!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-jdbc -->
<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jdbc</artifactId>
    <version>8.5.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-juli -->
<dependency>             <!-- tomcat自己的日志模块，无脑引用就可以了。不需要纠结-->
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-juli</artifactId>
    <version>8.5.15</version>
</dependency>

使用示例：

PoolProperties p = new PoolProperties();
p.setUrl("jdbc:mysql://127.0.0.1/test?characterEncoding=UTF-8");
p.setUsername("root");
p.setPassword("txl");
p.setDriverClassName("com.mysql.jdbc.Driver");
p.setMinIdle(5); //始终保留的连接的最小连接数 ，默认10
p.setInitialSize(5); //启动时的初始连接数 默认10
p.setMaxIdle(10); //设置池内最大空闲连接数
p.setTestOnBorrow(true); //取出连接时是否对其进行验证
p.setValidationQuery("select 1"); //如果上面的设置为true，则这要设置
p.setMaxActive(20); //最大活跃连接数
DataSource datasource = new DataSource();
datasource.setPoolProperties(p);
Connection connection=datasource.getConnection();

也可以开发自己的数据源，只需实现数据源接口即可：javax.sql.DataSource

使用JOOQ框架

1、下载

官网地址：https://www.jooq.org
maven参考地址：https://mvnrepository.com/artifact/org.jooq/jooq

<!-- https://mvnrepository.com/artifact/org.jooq/jooq -->
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.9.5</version>
 </dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-meta</artifactId>
    <version>3.9.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.jooq/jooq-codegen -->
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
    <version>3.9.5</version>
</dependency>

2、使用示例

//  执行原始的SQL
Result<Record> result=dslContext.fetch("select * from jt_news");
for(Record r :result)
{
    System.out.println(r.get("news_title"));
}

// SQLBuilder的方式
DSLContext dslContext= DSL.using(connection, SQLDialect.MYSQL);
Result<Record> result=dslContext.select()
        .from("jt_news")
        .orderBy(field("news_id").desc()).fetch();
for(Record r :result)
{
    System.out.println(r.get("news_title"));
}

3、生成数据表结构映射

参考：https://www.jooq.org/doc/3.9/manual-single-page/#jooq-in-7-steps-step3

1）、创建xml配置文件：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.9.2.xsd">
    <!-- Configure the database connection here -->
    <jdbc>
        <driver>com.mysql.jdbc.Driver</driver>
        <url>jdbc:mysql://127.0.0.1/jtthink2?characterEncoding=UTF-8</url>
        <user>root</user>
        <password>txl</password>
    </jdbc>

    <generator>
        <!-- The default code generator. You can override this one, to generate your own code style.
             Supported generators:
             - org.jooq.util.JavaGenerator
             - org.jooq.util.ScalaGenerator
             Defaults to org.jooq.util.JavaGenerator -->
        <name>org.jooq.util.JavaGenerator</name>

        <database>
            <!-- The database type. The format here is:
                 org.util.[database].[database]Database -->
            <name>org.jooq.util.mysql.MySQLDatabase</name>

            <!-- The database schema (or in the absence of schema support, in your RDBMS this
                 can be the owner, user, database name) to be generated -->
            <inputSchema>test</inputSchema>  <!-- 选择数据库 -->

            <!-- All elements that are generated from your schema
                 (A Java regular expression. Use the pipe to separate several expressions)
                 Watch out for case-sensitivity. Depending on your database, this might be important! -->
            <includes>(users_buy)|(news)|(products)</includes>  <!-- 包含数据表 -->

            <!-- All elements that are excluded from your schema
                 (A Java regular expression. Use the pipe to separate several expressions).
                 Excludes match before includes, i.e. excludes have a higher priority -->
            <excludes></excludes>  <!-- 排除数据表 -->
        </database>

        <target>
            <!-- The destination package of your generated classes (within the destination directory) -->
            <packageName>lop3.gen</packageName>  <!-- 在项目中的包名 -->

            <!-- The destination directory of your generated classes. Using Maven directory layout here -->
            <directory>C:Javapro1srcmainjavacom	xl</directory>  <!-- 项目路径 -->
        </target>
    </generator>
</configuration>

2）、执行生成

// 指定配置文件路径
org.jooq.util.GenerationTool.main(new String[]{"C:/Java/pro1/src/main/resources/jooqGen.xml"});

3）、使用示例

DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);
Result<Record> result = dslContext.select().from(USERS_BUY).orderBy(USERS_BUY.PAYMONEY.desc()).fetch();
for(Record r : result) {
    System.out.println(r.get(USERS_BUY.PAYMONEY) + "_" + r.get(USERS_BUY.PAYDATE));
}
dslContext.close();
connection.close();