JOOQ+PostgreSQL：如何将字符串转为inet类型插入数据库

安全阿岳

2026-6-19

技术问询

如何编写插入语句，将表示IP地址的字符串插入到PostgreSQL的inet类型列中？

现有代码

val rDate = LocalDateTime.parse("2018-12-29T02:47:17").toJavaLocalDateTime()
val iDate = LocalDateTime.parse("2018-12-29T22:11:00").toJavaLocalDateTime()
val ip = "192.168.1.10"


dslcontext.insertInto(IP_LIST)
    .columns(IP_LIST.RECV_DATE, IP_LIST.INSERT_DATE, IP_LIST.SENDER_IP)
    .values(rDate, iDate, ip)
    .execute()

我认为需要对ip值使用cast()，但未找到可用示例。已正确安装并运行jooq及org.jooq:jooq-postgres-extensions:3.19.11，可正常执行查询操作。

更新

检查JOOQ生成的表代码，发现sender_ip字段被设置为String类型，这是否正确？是否应该为Inet类型？
生成的代码：

/**
 * The column <code>public.ip_list.sender_ip</code>.
 */
val SENDER_IP: TableField<IpListRecord, String?> = createField(DSL.name("sender_ip"), SQLDataType.VARCHAR.nullable(false), this, "")

另外，使用HikariCP作为JOOQ的数据源，Kotlin版本为2.0.0，build.gradle配置如下：

import nu.studer.gradle.jooq.JooqEdition
import org.jooq.meta.jaxb.Logging
import org.jooq.meta.jaxb.*
    
plugins {
    id 'org.jetbrains.kotlin.jvm' version '2.0.0'
    id 'nu.studer.jooq' version '9.0'
}

group = 'org.me'
version = '1.02'

repositories {
    mavenCentral()
    mavenLocal()
}

dependencies {
    testImplementation 'org.jetbrains.kotlin:kotlin-test'
    runtimeOnly 'org.jetbrains.kotlinx:kotlinx-datetime:0.6.0'
    implementation 'org.jetbrains.kotlinx:kotlinx-datetime:0.6.0'

    implementation 'org.postgresql:postgresql:42.7.2'
    implementation 'com.zaxxer:HikariCP:5.1.0'

    jooqGenerator 'org.postgresql:postgresql:42.7.2'
    implementation 'org.jooq:jooq-postgres-extensions:3.19.11'

    testImplementation 'io.kotest:kotest-assertions-core-jvm:5.9.1'

}


jooq {
    version = '3.19.1'  // default (can be omitted)
    edition = JooqEdition.OSS  // default (can be omitted)

    configurations {
        main {  // name of the jOOQ configuration
            generateSchemaSourceOnCompilation = true  // default (can be omitted)

            generationTool {
                logging = Logging.DEBUG
                jdbc {
                    driver = 'org.postgresql.Driver'
                    url = 'jdbc:postgresql://localhost:5432/iplist'
                    user = 'iplist'
                    password = 'xxxxxx'
                    properties {
                        property {
                            key = 'ssl'
                            value = 'false'
                        }
                    }
                }
                generator {
                    name = 'org.jooq.codegen.KotlinGenerator'
                    database {
                        name = 'org.jooq.meta.postgres.PostgresDatabase'
                        inputSchema = 'public'
                        forcedTypes {
                            forcedType {
                                name = 'varchar'
                                includeExpression = '.*'
                                includeTypes = 'JSONB?'
                            }
                            forcedType {
                                name = 'varchar'
                                includeExpression = '.*'
                                includeTypes = 'INET'
                            }
                        }
                    }
                    generate {
                        deprecated = false
                        records = true
                        immutablePojos = true
                        fluentSetters = true
                    }
                    target {
                        packageName = 'nu.studer.sample'
                        directory = 'build/generated-src/jooq/main'  // default (can be omitted)
                    }
                    strategy.name = 'org.jooq.codegen.DefaultGeneratorStrategy'
                }
            }
        }
    }
}

tasks.named('generateJooq').configure { allInputsDeclared = true }

test {
    useJUnitPlatform()
}
kotlin {
    jvmToolchain(21)
}

更新二

将ip字符串转为Inet类型后，IDEA语法检查提示需要String类型。询问：若配置正常，以下insert语句语法是否正确？

fun String.toInet(): Inet {
    return inet(InetAddress.getByName(this))
}

dslcontext.insertInto(IP_LIST)
    .columns(IP_LIST.RECV_DATE, IP_LIST.INSERT_DATE, IP_LIST.SENDER_IP)
    .values(rDate, iDate, ip.toInet())
    .execute()

若使用String类型的ip，执行时会抛出JOOQ DataAccessException，错误信息：

ERROR: column "sender_ip" is of type inet but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 134

按照Lukas Eder的建议，在build.gradle的jooq{}块中添加了org.jooq:jooq-postgres-extensions:3.19.11依赖，无法直接添加到generator块，因此添加到jooq根级别和main{}级别。
将generationTool{}日志设置为Logging.DEBUG后，生成IP_LIST表时，"Forcing type"信息重复出现20次，日志内容如下：

16:26:57 INFO Generating table records 
16:26:57 INFO Generating record        : IpListRecord.kt
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>
16:26:57 INFO Forcing type             : public.ip_list.sender_ip to <priority>0</priority><name>varchar</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>INET</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>

JOOQ生成器似乎已正确识别到sender_ip为Inet类型，重复的强制类型信息是否表示存在问题？若没有问题，如何正确编写插入语句以支持Inet类型？

内容的提问来源于stack exchange，提问作者bigswifty

JOOQ+PostgreSQL：如何将字符串转为inet类型插入数据库

现有代码 #

更新 #

更新二 #

现有代码

更新

更新二