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:\Java\pro1\src\main\java\com\txl</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();
