Dev | SQL

SQL

• Use jooq for type safe SQL query.
• Generate sql ddl to Java class
• Support database: h2, postgres

Generate SQL DDL to Java class

• See example in SQL module
• Prepare sql ddl files and put it into src/main/resources. The tips if many sql ddl files, name it with prefix 01_, 02_ with appropriate order
• Adapt build script build.gradle similar as:

import org.jooq.meta.jaxb.ForcedType

import com.nubeiot.buildscript.jooq.DB
import com.nubeiot.buildscript.jooq.JooqGenerateTask
import com.nubeiot.buildscript.jooq.JooqGenerateTask.JsonDataType
import com.nubeiot.buildscript.jooq.Utils

dependencies {
    compile project(':core:sql')
    compile project.deps.database.h2
}

task jooqGen(type: JooqGenerateTask) {
    packageName = "com.nubeiot.edge.core.model"
    enumTypes = project(':core:sql').ext.enumTypes
    dbTypes = project(':core:sql').ext.dbTypes + [
        new ForcedType(userType: "com.nubeiot.edge.core.loader.ModuleType", types: DB.TYPES.varchar,
                       expression: Utils.toRegexIgnoreCase("service_type"),
                       converter: "com.nubeiot.edge.core.model.converter.ModuleTypeConverter")
    ]
    javaTypes = project(':core:sql').ext.javaTypes + [
        new JsonDataType(className: "com.nubeiot.edge.core.loader.ModuleType",
                         converter: "%s.name()",
                         parser: "com.nubeiot.edge.core.loader.ModuleTypeFactory.factory((String)%s)",
                         defVal: "com.nubeiot.edge.core.loader.ModuleTypeFactory.getDefault()")
    ]
}

• Run gradle jooq
• Generated class should be in ${project_folder}/generated/main/java/${your_define_package}

Using SQL Unit

TBD

NoSQL