介绍两种ORM框架

最新推荐文章于 2023-01-04 20:23:00 发布

Roden

最新推荐文章于 2023-01-04 20:23:00 发布

阅读量244

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/luo_deng/article/details/113064517

版权

Java 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

该博客展示了Jooq和QueryDsl两种Java ORM库在数据库操作上的使用示例。Jooq示例中，创建了插入记录并查询的SQL操作，而QueryDsl例子则演示了如何执行复杂查询并获取结果。文章还包含了相关的Maven配置，用于根据数据表生成对象。

摘要由CSDN通过智能技术生成

Jooq与QueryDsl

package com.roden.sqldsl;
// For convenience, always static import your generated tables and
// jOOQ functions to decrease verbosity:
import static com.roden.sqldsl.jooq.generated.tables.Author.AUTHOR;
import java.sql.*;
import java.util.ResourceBundle;

import org.jooq.*;
import org.jooq.impl.*;

/**
 * https://www.jooq.org/doc/3.11/manual/
 */
public class JooqDemo {
    /**
     * @param args
     */
    public static void main(String[] args) {
        ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");

        // Connection is the only JDBC resource that we need
        // PreparedStatement and ResultSet are handled by jOOQ, internally
        try (Connection conn = DriverManager.getConnection(jdbc.getString("url"),jdbc.getString("user"),jdbc.getString("password"))) {
            DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
            create.insertInto(AUTHOR,
                    AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
                    .values(103, "Hermann", "Hesse")
                    .execute();

            Result<Record> result = create.select().from(AUTHOR).fetch();
            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);
            }
        }
        // For the sake of this tutorial, let's keep exception handling simple
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

package com.roden.sqldsl;

import com.querydsl.sql.HSQLDBTemplates;
import com.querydsl.sql.SQLQuery;
import com.querydsl.sql.SQLTemplates;
import com.roden.sqldsl.querydsl.generated.QAuthor;

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

/**
 * http://www.querydsl.com/static/querydsl/latest/reference/html/ch02s03.html
 */
public class QueryDslDemo {
    public static void main(String[] args) throws Exception {
        ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(jdbc.getString("url"),jdbc.getString("user"),jdbc.getString("password"));
        // SQL-dialect
        SQLTemplates dialect = new HSQLDBTemplates();
        SQLQuery<?> query = new SQLQuery<Void>(connection, dialect);

        QAuthor author = new QAuthor("a");
        List<String> lastNames = query.select(author.lastName)
                .from(author)
                .where(author.firstName.eq("Hermann")).limit(2)
                .fetch();
        System.out.println(lastNames.toString());
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.roden</groupId>
    <artifactId>sqldsl</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <!--防止maven改动IDE的language level -->
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <!--数据库迁移所用的参数 -->
        <db.driver>com.mysql.cj.jdbc.Driver</db.driver>
        <db.url>jdbc:mysql://localhost:3306/spring?serverTimezone=UTC</db.url>
        <db.user>luodeng</db.user>
        <db.password>123456</db.password>
        <db.schema>spring</db.schema>
        <querydsl.version>4.2.1</querydsl.version>
        <jooq.version>3.11.11</jooq.version>
        <mysql.version>8.0.16</mysql.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.6.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
        </dependency>
        <dependency>
            <groupId>io.lettuce</groupId>
            <artifactId>lettuce-core</artifactId>
            <version>5.1.7.RELEASE</version>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq</artifactId>
            <version>${jooq.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-meta</artifactId>
            <version>${jooq.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen</artifactId>
            <version>${jooq.version}</version>
        </dependency>

        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql-codegen</artifactId>
            <version>${querydsl.version}</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <!--jooq生成文件的插件 -->
            <plugin>
                <!-- Specify the maven code generator plugin -->
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${jooq.version}</version>
                <!-- The plugin should hook into the generate goal -->
                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <!-- JDBC connection parameters -->
                    <jdbc>
                        <driver>${db.driver}</driver>
                        <url>${db.url}</url>
                        <user>${db.user}</user>
                        <password>${db.password}</password>
                    </jdbc>
                    <!-- Generator parameters -->
                    <generator>
                        <database>
                            <!--如MySQLDatabase在jOOQ早期版本中出现在org.jooq.util下，在按Java 9/JPMS规范做了重新规划后，被安排在org.jooq.meta包下。-->
                            <name>org.jooq.meta.mysql.MySQLDatabase</name>
                            <includes>.*</includes>
                            <inputSchema>${db.schema}</inputSchema>
                            <forcedTypes>
                                <forcedType>
                                    <name>BOOLEAN</name>
                                    <expression>.*\.HANDMADE</expression>
                                    <types>.*</types>
                                </forcedType>
                            </forcedTypes>
                        </database>
                        <target>
                            <packageName>com.roden.sqldsl.jooq.generated</packageName>
                            <directory>target/generated-sources/java</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>
            <!-- querydsl 生成文件的插件 -->
            <plugin>
                <!-- https://mvnrepository.com/artifact/com.querydsl/querydsl-maven-plugin -->
                <groupId>com.querydsl</groupId>
                <artifactId>querydsl-maven-plugin</artifactId>
                <version>${querydsl.version}</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>export</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jdbcDriver>${db.driver}</jdbcDriver>
                    <jdbcUrl>${db.url}</jdbcUrl>
                    <jdbcUser>${db.user}</jdbcUser>
                    <jdbcPassword>${db.password}</jdbcPassword>
                    <packageName>com.roden.sqldsl.querydsl.generated</packageName>
                    <targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>${mysql.version}</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

运行pom可以根据数据表生成对象AUTHOR与QAUTHOR