JOOQ وحفرة أرنبه. كيف تعيش بدون السبات

ما هو JOOQ؟

select * from countries c where c.population > 10000000;

List<Country> findAllByPopulationAfter(Long amount);

@Query("select c from Country c where c.population > :amount")
List<Country> findAllPopulationGreaterThan(@Param("amount") Long amount);

return dsl.selectFrom(COUNTRIES)
                .where(COUNTRIES.POPULATION.greaterThan(amount))
                .fetch();

استفسارات JOOQ

return dsl.selectFrom(COUNTRIES)
                .where(COUNTRIES.POPULATION.greaterThan(amount))
                .fetch();

return dsl.selectFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .fetchAny();

return dsl.insertInto(Countries.COUNTRIES)
                .set(Countries.COUNTRIES.NAME, country.getName())
                .set(Countries.COUNTRIES.POPULATION, country.getPopulation())
                .set(Countries.COUNTRIES.GOVERNMENT_FORM, nameOrNull(country.getGovernmentForm()))
                .returning()
                .fetchOne();

نعم سيكون مخضرم

<dependencies>

        <!-- Spring Starters -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Database -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Helpers -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons.lang3.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- JOOQ Generator -->
        <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>

    </dependencies>

1. يحدث تحويل DTO إلى كيان بالفعل في المستودع.
2. نحن لا نكتب كيانًا على هذا النحو. هو مكتوب لنا من قبل مولد JOOQ.

• جيل الكيان.
• كتابة استعلامات CRUD.
• أمثلية طلبات CRUD.
• وآخر طلب CRUD التحسين.
• رسم خرائط الكيانات باستخدام مكتبة JOOQ القياسية.
• وناقش لماذا كل هذا ضروري.

ماذا نكتب؟

@Data
public class Country {

    private Long id;
    private String name;
    private GovernmentForm governmentForm;
    private Integer population;

    private List<City> cities;
}

@Data
public class City {

    private Long id;
    private Long countryId;
    private String name;
}

create table countries
(
    id              bigserial primary key,
    name            varchar(255),
    government_form varchar(255),
    population      int
);

create table cities
(
    id         bigserial primary key,
    country_id bigint,
    name       varchar(255)
);

لنبدأ بتوليد الكيانات.

• من خلال البرنامج المساعد مخضرم.
• في كود Java.

توليد الكيانات في مخضرم

            <!-- JOOQ Generator Plugin -->
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${jooq.version}</version>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jdbc>  <!--    -->
                        <driver>${db.driver}</driver>
                        <url>${db.url}</url>
                        <user>${db.username}</user>
                        <password>${db.password}</password>
                    </jdbc>
                    <generator>
                        <database>
                            <includes>.*</includes>  <!--     -->
                            <excludes>  <!--     -->
                                flyway_schema_history
                            </excludes>
                            <inputSchema>public</inputSchema>  <!--  -->
                        </database>
                        <generate>
                            <records>true</records>
                        </generate>
                        <target>
                            <!--      -->
                            <packageName>ru.xpendence.jooqexample.domain</packageName>
                            <!--  .    target. -->
                            <directory>target/generated-sources/jooq</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>

public class CountriesRecord extends UpdatableRecordImpl<CountriesRecord> implements Record4<Long, String, String, Integer> {
    //...

@Component
public class AfterStartupApplicationListener implements ApplicationListener<ContextRefreshedEvent> {

    @Value("${spring.datasource.driver-class-name}")
    private String driver;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${jooq.generator.database.name}")
    private String databaseName;

    @Value("${jooq.generator.database.with-includes}")
    private String databaseWithIncludes;

    @Value("${jooq.generator.database.with-input-schema}")
    private String databaseWithInputSchema;

    @Value("${jooq.generator.target.package-name}")
    private String targetPackageName;

    @Value("${jooq.generator.target.directory}")
    private String targetDirectory;

    @SneakyThrows
    @Override
    public void onApplicationEvent(ContextRefreshedEvent contextRefreshedEvent) {
        new GenerationTool().run(configureGenerator());
    }

    private Configuration configureGenerator() {
        return new Configuration()
                .withJdbc(new Jdbc()
                        .withDriver(driver)
                        .withUrl(url)
                        .withUser(username)
                        .withPassword(password))
                .withGenerator(new Generator()
                        .withDatabase(new Database()
                                .withName(databaseName)
                                .withIncludes(databaseWithIncludes)
                                .withExcludes("")
                                .withInputSchema(databaseWithInputSchema))
                        .withTarget(new Target()
                                .withPackageName(targetPackageName)
                                .withDirectory(targetDirectory)));
    }
}

إنشاء مستودع

@Repository
@RequiredArgsConstructor
public class CountryRepository implements CrudRepository<Country> {

    private final DSLContext dsl;

} 

كتابة استعلامات CRUD

إدراج

insert into countries(name, government_form, population)
values (' -', 'UNITARY', 100500) returning *;

    public Country insertValues(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)  //insert into countries
                .values(country.getId(), country.getName(), country.getPopulation(), nameOrNull(country.getGovernmentForm()))  //values (? ? ? ?)
                .returning()  //returning
                .fetchOne()  //*
                .into(Country.class);
    }

.values(null, country.getName(), country.getPopulation(), nameOrNull(country.getGovernmentForm()))

org.postgresql.util.PSQLException: :     "id"   NOT NULL

    public Country insert(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(Countries.COUNTRIES.NAME, country.getName())
                .set(Countries.COUNTRIES.POPULATION, country.getPopulation())
                .set(Countries.COUNTRIES.GOVERNMENT_FORM, nameOrNull(country.getGovernmentForm()))
                .returning()
                .fetchOne()
                .into(Country.class);
    }

repository.save(country);

    public Country insert(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(dsl.newRecord(Countries.COUNTRIES, country))  //   
                .returning()
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error inserting entity: " + country.getId()))
                .into(Country.class);
    }

    public Long insertAndReturnId(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(dsl.newRecord(Countries.COUNTRIES, country))
                .returning(Countries.COUNTRIES.ID) //  Record    - id
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error inserting entity: " + country.getId()))
                .get(Countries.COUNTRIES.ID); // id
    }

تحديث

update countries
set name            = '',
    government_form = 'CONFEDERATE',
    population      = 100500
where id = 1
returning *;

    public Country update(Country country) {
        return dsl.update(Countries.COUNTRIES)
                .set(dsl.newRecord(Countries.COUNTRIES, country))
                .where(Countries.COUNTRIES.ID.eq(country.getId()))
                .returning()
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error updating entity: " + country.getId()))
                .into(Country.class);
    }

تحديد

select *
from countries c
where id = ?;

    public Country find(Long id) {
        return dsl.selectFrom(Countries.COUNTRIES) //select * from countries
                .where(Countries.COUNTRIES.ID.eq(id))  //where id = ?
                .fetchAny()  // ,    
                .into(Country.class);
    }

حذف

    public Boolean delete(Long id) {
        return dsl.deleteFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .execute() == 1;
    }

1 row affected in 5 ms

لم تكن خرافة. كانت مجرد مادة تشحيم. استخدام maper منتظم لرسم خرائط رقيقة لكيان في التسجيل والعكس بالعكس

    public Country find(Long id) {
        return dsl.selectFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .fetchAny()
                .map(r -> {
                    Country country = r.into(Country.class);
                    country.setCities(cityRepository.findAll(Cities.CITIES.COUNTRY_ID.eq(country.getId())));
                    return country;
                });
    }

@RequiredArgsConstructor
@Component
public class CountryRecordMapper implements RecordMapper<CountriesRecord, Country> {

    private final CityRepository cityRepository;

    @Override
    public Country map(CountriesRecord record) {
        Country country = record.into(Country.class);
        country.setCities(cityRepository.findAll(Cities.CITIES.COUNTRY_ID.eq(country.getId())));
        return country;
    }
}

    public Country findWithCustomMapper(Long id) {
        return dsl.selectFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .fetchAny()
                .map(r -> countryRecordMapper.map((CountriesRecord) r));
    }

حسنًا ، لقد تعلمنا كيفية تعيين السجل إلى كيان ، ولكن ماذا عن تحسين تعيين كيان في السجل؟

.set(dsl.newRecord(Countries.COUNTRIES, country))

نعم ، لكل مصمّم خرائط لدينا

@Component
@RequiredArgsConstructor
public class CountryRecordUnmapper implements RecordUnmapper<Country, CountriesRecord> {

    private final DSLContext dsl;

    @Override
    public CountriesRecord unmap(Country country) throws MappingException {
        CountriesRecord record = dsl.newRecord(Countries.COUNTRIES, country);
        record.setPopulation(-1);
        return record;
    }
}

    public Country insertWithUnmapper(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(countryRecordUnmapper.unmap(country))
                .returning()
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error inserting entity: " + country.getId()))
                .into(Country.class);
    }

الاستنتاجات والاستنتاجات