[springboot DB series] jooq’s new record usage posture

Time:2021-11-21

[springboot DB series] jooq's new record usage posture

[springboot DB series] jooq’s new record usage posture

Next, we begin to enter the use posture series of jooq’s addition, deletion, modification and query. This article will mainly introduce how to use jooq to add data

<!– more –>

1. Project construction

The project relies onSpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEADevelop

1. Project dependency

How to create a springboot project is beyond the scope of this article. If you are interested, you can get it from the personal website at the end of this article

In this example project, we select h2dabase as the database (to facilitate interested partners to directly obtain the project source code and directly test the experience). Therefore, the corresponding POM core dependencies are as follows

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jooq</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>

2. Database initialization

We usejooq-codegen-mavenPlug in to automatically generate database related code. For partners interested in this logic, please refer to the blog:[DB series] automatic generation of jooq code

The table structure used in the following text is as follows

DROP TABLE IF EXISTS poet;

CREATE TABLE poet (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL default '',
  CONSTRAINT pk_t_poet PRIMARY KEY (ID)
);

DROP TABLE IF EXISTS poetry;
CREATE TABLE poetry (
  `id` int NOT NULL,
  `poet_id` int NOT NULL default '0',
  `title` varchar(128) not null default '',
  `content` varchar(128) not null default '',
  CONSTRAINT pk_t_poetry PRIMARY KEY (ID)
);

3. Configuration file

The connection configuration of h2database is as follows:application.properties

#Database Configuration
spring.datasource.url=jdbc:h2:~/h2-jooq-poet
spring.datasource.username=test
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver


#jOOQ Configuration
spring.jooq.sql-dialect=H2


spring.datasource.initialization-mode=never
spring.datasource.continueOnError=true


##H2 web console settings
spring.datasource.platform=h2
#After this configuration, H2 web consloe can be accessed remotely. Otherwise, it can only be accessed locally.
spring.h2.console.settings.web-allow-others=true
#With this configuration, you can use your_ URL / h2visit H2 web consloe
spring.h2.console.path=/h2
#With this configuration, H2 web consloe will be started when the program is started
spring.h2.console.enabled=true

2. New record

Next, let’s enter the formal introduction to the use posture of data insertion. Generally speaking, the new data will be divided into single and batch methods. We will introduce them respectively below

1. Adding method of record entity class

In jooq, the simplest case is to add a new one with the help of the automatically generated record class, as shown below

private static final PoetTB table = PoetTB.POET;
@Autowired
private DSLContext dsl;

/**
 *New record
 *
 * @param id
 * @param name
 * @return
 */
public boolean save(int id, String name) {
    PoetPO record = dsl.newRecord(table);
    record.setId(id);
    record.setName(name);
    return record.insert() > 0;
}

be careful:

  • Creation method of entity class:PoetPO record = dsl.newRecord(table);, do not directly new an object

2. Chain writing

The following description is very similar to SQL. It is also a way I use more personally. The feature is that it is clear at a glance

public boolean save2(int id, String name) {
    return dsl.insertInto(table).set(table.ID, id).set(table.NAME, name).execute() > 0;
}

3. Insertquery method

The above two methods are common, and the direct use of insertquery may not be as elegant as the above in actual business development, but it is still very useful in some special scenarios

/**
 *Do not use auto generated code to insert data natively
 *
 * @param id
 * @param name
 * @return
 */
public boolean save3(int id, String name) {
    //When the automatically generated object is not used, the table can be specified with DSL. Table(), and the column can be specified with DSL. Field()
    InsertQuery insertQuery = dsl.insertQuery(DSL.table("poet"));
    insertQuery.addValue(DSL.field("id", Integer.class), id);
    insertQuery.addValue(DSL.field("name", String.class), name);
    return insertQuery.execute() > 0;
}

Pay attention to the usage above,InsertQueryThe use of itself is not worth mentioning. The key point is that the above implementation does not use automatically generated code, such as

  • table: Dsl.table (table name)
  • field: Dsl.field (column name, type)

Through the above case, we can know how to operate the database without automatically generating the corresponding code of the DB

4. Record entity batch saving

With the help ofdsl.batchInsertTo batch add entities, which belongs to the most basic use posture

private PoetPO bo2po(PoetBO bo) {
    PoetPO po = dsl.newRecord(table);
    po.setId(bo.getId());
    po.setName(bo.getName());
    return po;
}

/**
 *Batch addition via record
 *
 * @param list
 * @return
 */
public boolean batchSave(List<PoetBO> list) {
    List<PoetPO> poList = list.stream().map(this::bo2po).collect(Collectors.toList());
    int[] ans = dsl.batchInsert(poList).execute();
    System.out.println(JSON.toJSONString(ans));
    return true;
}

5. Chain batch saving

It is also a SQL like chain insertion method. You should pay attention to the difference from the previous chain insertion of a single record. The following writing method is similar to the batch insertion method of SQL

/**
 *SQL like writing method, batch adding
 *
 * @param list
 * @return
 */
public boolean batchSave2(List<PoetBO> list) {
    InsertValuesStep2<PoetPO, Integer, String> step = dsl.insertInto(table).columns(table.ID, table.NAME);
    for (PoetBO bo : list) {
        step.values(bo.getId(), bo.getName());
    }
    return step.execute() > 0;
}

6. Insertquery batch save

The single entry insertion method of insetquery is introduced above. The following batch writing method is basically not much different

/**
 *Batch data addition is not based on automatically generated code
 *
 * @param list
 * @return
 */
public boolean batchSave3(List<PoetBO> list) {
    InsertQuery insertQuery = dsl.insertQuery(DSL.table("poet"));
    for (PoetBO bo : list) {
        insertQuery.addValue(DSL.field("id", Integer.class), bo.getId());
        insertQuery.addValue(DSL.field("name", String.class), bo.getName());
        insertQuery.newRecord();
    }

    return insertQuery.execute() > 0;
}

7. Test case

Next, test the execution of the above six methods

public void test() {
    This. Save (11, "one gray");
    This.save2 (12, "one gray");
    This. Save3 (13, "a gray blog");


    this.batchSave(Arrays.asList(new PoetBO(14, "yh"), new PoetBO(15, "yhh")));
    this.batchSave2(Arrays.asList(new PoetBO(16, "yihui"), new PoetBO(17, "yihuihui")));
    this.batchSave3(Arrays.asList(new PoetBO(18, "YiHui"), new PoetBO(19, "YiHuiBlog")));

    RecordMapper<PoetPO, PoetBO> mapper =
            dsl.configuration().recordMapperProvider().provide(table.recordType(), PoetBO.class);
    List<PoetBO> result = dsl.selectFrom(table).fetch().map(mapper);
    System.out.println(result);
}

The output results are as follows

[1,1]
[poetbo (1, Li Bai), poetbo (2, aikeong), poetbo (11, Yihui), poetbo (12, Yihui), poetbo (13, Yihui blog), poetbo (14, YH), poetbo (15, yhh), poetbo (16, Yihui), poetbo (17, yihuihui), poetbo (18, Yihui), poetbo (19, yihuiblog)]

2. Other

0. Project

Series blog posts

Project source code

1. A gray blog

The above contents are only the words of one family. Due to limited personal ability, it is inevitable that there are omissions and mistakes. If you find a bug or have better suggestions, you are welcome to criticize and correct and be grateful

The following is a gray personal blog, which records all blog posts in study and work. Welcome to visit

[springboot DB series] jooq's new record usage posture