General Insert Statement

[jeffgbutler]

Provide a general insert statement that is not based on a POJO class. The current insert statements assume that a class exists that holds field data for insert statements. This works well for retrieving generated keys with MyBatis, but is unnecessary for Spring. If we allow a general INSERT like we do with UPDATE, we can also easily implement something like type handlers for Spring (like #131).

[stengvac]

Hello,

for our internal project after we did decide to leave JPA and use more flexible framework. First option was Exposed, but a lot of open bugs, zero doc + not released version discouraged me. So I did found mybatis-dynamic-sql which we now use with Kotlin, but I did wrap dynamic sql builders with Kotlin DSL lib which use mybatis-dynamic-dql as sql enerator + NamedParameterJdbcTemplate as executor. I have changed a lot of features, because my goal was to go without annotations, xml files, reflection + be Kotlin friendly. So in the end I have lib, which is Exposed like, with not null and nullable columns, enhanced where dsl, insert dsl, update dsl, 'smart collumns' which provide support for mapping values from and to db using interface similiar to mybatis TypeHandler, but not taken from mybatis, because I do not need whole mybatis as dependency. In few weeks I should be possible post this code to github and maybe some changes may be incorporated.

here is sample dls for kotlin insert DSL which I have created, because MapSqlParameterSource have problem with enum values and null embedded fields.

Not it is type safe provided value type must eq to collumn type including nullability, which is not shown in example. Today I have added possibility to map enum to any type so this example may be little deprecated, but may work as inspiration.

/**
 * JDBC template has problem with resolving Enum values without set jdbcType so easiest solution is to change type to String for now.
 */
class EnumSupportingSqlParamSource(params: Map<String, Any?>) : MapSqlParameterSource(params) {
    override fun getValue(paramName: String): Any? {
        val value = super.getValue(paramName)
        return if (value is Enum<*>) value.name else value
    }
}

class InsertMappingDsl<E>(
    private val insertDsl: InsertDSL<E>,
    private val insertAttributes: EnumSupportingSqlParamSource
) {
    operator fun <T> set(col: SqlColumn<T>, value: T) {
        insertDsl.map(col).toProperty(col.name())
        val vendorJdbc = col.jdbcType().orElse(null)?.vendorTypeNumber
        if (vendorJdbc != null) {
            insertAttributes.addValue(col.name(), value, vendorJdbc)
        } else {
            insertAttributes.addValue(col.name(), value)
        }
    }
}

usage

internal class InsertMappingDslTest {

    @Test
    fun `insert map values correctly`() {
        val entity = TestEntity(
            uuid = UUID.randomUUID(),
            enum = TestEnum.VAL
        )
        val insertAttributes = EnumSupportingSqlParamSource(mapOf())
        val insertDsl = SqlBuilder.insert(entity).into(TestTable)

        InsertMappingDsl<TestEntity>(insertDsl, insertAttributes).let { statement ->
            statement[TestTable.uuid] = entity.uuid
            statement[TestTable.enum] = entity.enum
        }

        assertThat(insertAttributes.values).hasSize(2)
        val paramNames = insertAttributes.parameterNames
        assertThat(paramNames).containsAll(listOf(TestTable.enum.name(), TestTable.uuid.name()))
        assertThat(insertAttributes.getValue(TestTable.enum.name())).isEqualTo(entity.enum.name)
        assertThat(insertAttributes.getValue(TestTable.uuid.name())).isEqualTo(entity.uuid)

        assertThat("insert into someTable (uuid, enum) values (:uuid, :enum)").isEqualTo(
            insertDsl.build().render(RenderingStrategies.SPRING_NAMED_PARAMETER).insertStatement
        )
    }

    private object TestTable : SqlTable("someTable") {
        val uuid = column<UUID>("uuid", JDBCType.OTHER)
        val enum = column<TestEnum>("enum", JDBCType.VARCHAR)
    }

    enum class TestEnum {
        VAL,
        VAR
    }

    data class TestEntity(
        val uuid: UUID,
        val enum: TestEnum
    )
}

it took some time, but in the end mapping without string path to getter or annotations work like this

        InsertMappingDsl<TestEntity>(insertDsl, insertAttributes).let { statement ->
            statement[TestTable.uuid] = entity.uuid
            statement[TestTable.enum] = entity.enum
        }

and dynamic sql lib does not need to know entity type only map of values

[jeffgbutler]

Thanks for posting - this is interesting to me.

My idea is to build an insert statement that would not need your TestEntity - you could just set any column to any value directly. I've got a lot of it complete - this will spur me on towards finishing that work :) I'll update this issue when there's something you could look at - I'd be interested in your feedback.