java - seleccione la inserción en los valores() con las conversiones de tipo correcto utilizando jOOQ

sql postgresql

Question

Estoy usando jOOQ para insertar bastantes filas en una tabla que es una relación de muchos a muchos. El código funciona, el SQL generado es el esperado, mi problema es que espero que el código jOOQ sea más simple.

Tengo una estructura simplificada (todo lo que se cambió de nombre, la mayoría de los campos se eliminó, la mayoría de las restricciones se eliminó, es solo un ejemplo tonto pero preciso de la estructura):

CREATE TABLE person (
    person_id BIGSERIAL PRIMARY KEY,
    person_name VARCHAR(64) NOT NULL UNIQUE
);

CREATE TABLE company (
    company_id BIGSERIAL PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employment_contract (
    company_id BIGINT NOT NULL REFERENCES company,
    person_id BIGINT NOT NULL REFERENCES person,
    PRIMARY KEY (company_id, person_id),

    salary INT NOT NULL,
    creation_date_time TIMESTAMP NOT NULL
);

Mi código de inserción:

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
    row(
        cast(null, COMPANY.COMPANY_NAME),
        cast(null, PERSON.PERSON_NAME),
        cast(null, EMPLOYMENT_CONTRACT.SALARY),
        cast(null, EMPLOYMENT_CONTRACT.CREATION_DATE_TIME)
    )
).as("insert_values",
        COMPANY.COMPANY_NAME.getName(),  -- these lines are bugging me
        PERSON.PERSON_NAME.getName(),
        EMPLOYMENT_CONTRACT.SALARY.getName(),
        EMPLOYMENT_CONTRACT.CREATION_DATE_TIME.getName()
);

Insert<AffectedSubscriberRecord> insert = insertInto(EMPLOYMENT_CONTRACT)
    .columns(EMPLOYMENT_CONTRACT.COMPANY_ID,
            EMPLOYMENT_CONTRACT.PERSON_ID,
            EMPLOYMENT_CONTRACT.SALARY,
            EMPLOYMENT_CONTRACT.CREATION_DATE_TIME
    )
    .select(
        select(
            COMPANY.COMPANY_ID,
            PERSON.PERSON_ID,
            insertValues.field(EMPLOYMENT_CONTRACT.SALARY),
            insertValues.field(EMPLOYMENT_CONTRACT.CREATION_DATE_TIME)
        )
        .from(insertValues)
            .join(COMPANY).using(COMPANY.COMPANY_NAME)
            .join(PERSON).using(PERSON.PERSON_NAME)
    );

Luego ato todas mis filas a context.batch(insert) y ejecuto la cosa. Sé con certeza que las claves de referencia para la person y la company ya existen, y el código original también resuelve los duplicados, no tenemos que preocuparnos por esas cosas aquí.

Lo que me molesta es la tabla insertValues : necesito especificar los tipos de columna y los nombres dos veces, en un proceso de copia y pegado propenso a errores, utilizando llamadas .getName() que ocultan todo el código y son fáciles de intercambiar por error. Lo que intenté en su lugar:

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
    row( (String)null, (String)null, (Integer)null, (Timestamp)null )
).as("insert_values",
        COMPANY.COMPANY_NAME.getName(),
        PERSON.PERSON_NAME.getName(),
        EMPLOYMENT_CONTRACT.SALARY.getName(),
        EMPLOYMENT_CONTRACT.CREATION_DATE_TIME.getName()
);

Obviamente, esto no funciona, ni jOOQ ni Postgres conocen los tipos insertados, el DB adivina varchar y falla. Necesitamos jOOQ para generar pronósticos al menos para la primera fila en la consulta. Otro intento:

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
    row( COMPANY.COMPANY_NAME, PERSON.PERSON_NAME, EMPLOYMENT_CONTRACT.SALARY, EMPLOYMENT_CONTRACT.CREATION_DATE_TIME )
).as("insert_values");

Esta sería la bomba. JOOQ sabe de esta manera los tipos correctos y podría generar los lanzamientos para mí, toda la duplicación de código desaparece y las cosas están seguras. Sin embargo, esto también falla. JOOQ no entiende que le estoy dando una fila llena de nulos.

¿Hay alguna forma de lograr la misma consulta resultante (o equivalente) sin las .getName() impuras .getName() , pasando directamente los campos a algún lugar?

Answer 1

Así que los values() en jOOQ te dan un constructor de tablas. Por defecto, select() le da todas las columnas como están, lo que significa que jOOQ definirá como Field<object>

Creo que la manera de pensar sobre esto es que jOOQ en este punto es reconocer dos cosas. Puede intentar definir campos en la salida o puede aceptar objetos e intentar manejar casos de tipo en el camino de regreso. Mi preferencia sería para los campos en la salida.

Un aspecto importante a considerar aquí es que las filas deben tener los mismos tipos para una columna dada, pero hay casos en que las conversiones implícitas en SQL pueden interferir con eso en jOOQ. Por ejemplo, si inserta un valor numérico en la primera fila y un entero en la segunda, el entero se convertirá en numérico implícitamente. La definición de campos para la selección evita este problema. Así que en términos de la pregunta, definiría los campos por separado y específicamente en la sección de selección de valores. Eso debería resolver ese problema. Con VALUES() , ya está solo y jOOQ no puede inferir tipos de forma segura.

Así que con esto, tu código debería convertirse en:

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
    row(
        cast(null, COMPANY.COMPANY_NAME),
        cast(null, PERSON.PERSON_NAME),
        cast(null, EMPLOYMENT_CONTRACT.SALARY),
        cast(null, EMPLOYMENT_CONTRACT.CREATION_DATE_TIME)
    )
).as("insert_values", "company_name", "person_name", "salary", "creation_time");
Field<String> ivCompanyNmae = field("insert_values.company_name". Stirng.class);
Field<Integer> ivSalary = field("insert_values.salary", Integer.class);
...

Insert<AffectedSubscriberRecord> insert = insertInto(EMPLOYMENT_CONTRACT)
    .columns(EMPLOYMENT_CONTRACT.COMPANY_ID,
        EMPLOYMENT_CONTRACT.PERSON_ID,
        EMPLOYMENT_CONTRACT.SALARY,
        EMPLOYMENT_CONTRACT.CREATION_DATE_TIME
    )
    .select(
        select(
            COMPANY.COMPANY_ID,
            PERSON.PERSON_ID,
            ivSalary,
            ivCreatedTime
        )
        .from(insertValues)
            .join(COMPANY).using(COMPANY.COMPANY_NAME)
            .join(PERSON).using(PERSON.PERSON_NAME)
     );

Es este punto donde jOOQ genera los moldes. Los desconocidos pueden convertirse en arqueros, pero luego serán lanzados explícitamente correctamente.