Ktolin+Ktorm入门实现增删改查-CSDN博客

本文链接：https://blog.csdn.net/hcyxsh/article/details/147518941

Ktolin+Ktorm入门实现增删改查

Ktolin+Ktorm入门实现增删改查以及分页查询，此处以用户表为例。

第一步引依赖

plugins {
    kotlin("jvm") version "1.8.0"
    application
    kotlin("plugin.serialization") version "1.8.0" // 序列化插件
}

dependencies {
    // Ktor
    val ktor_version = "2.3.2"
    implementation("io.ktor:ktor-server-core:$ktor_version")
    implementation("io.ktor:ktor-server-netty:$ktor_version")
    implementation("io.ktor:ktor-serialization-kotlinx-json:$ktor_version")
    implementation("io.ktor:ktor-server-content-negotiation:$ktor_version")
    implementation("io.ktor:ktor-server-call-logging:$ktor_version")

    // Ktorm
    val ktorm_version = "3.6.0"
    implementation("org.ktorm:ktorm-core:$ktorm_version")
    implementation("org.ktorm:ktorm-support-mysql:$ktorm_version") // 根据数据库更换

    // MySQL
    implementation("mysql:mysql-connector-java:8.0.28")

    // 连接池
    implementation("com.zaxxer:HikariCP:5.0.1")

    // 测试
    testImplementation("io.ktor:ktor-server-tests:$ktor_version")
    testImplementation("org.jetbrains.kotlin:kotlin-test-junit:1.8.0")
}

第二步创建实体

创建表实体对象

interface User : Entity<User> {
    companion object : Entity.Factory<User>()

    var id: String?
    var name: String?
    var eamil: String?
    var createdBy: String?
    var createdTime: LocalDateTime?
    var updatedBy: String?
    var updatedTime: LocalDateTime?
    /**
     * 是否删除 1删除0正常
     **/
    var deleted: String
}

创建绑定实体对象

object UserDao : Table<User>("tb_user") {
    var id = varchar("id").primaryKey().bindTo { it.id }
    var name= varchar("name").bindTo { it.name}
    var typeId = varchar("email").bindTo { it.typeId }
    var createdBy = varchar("created_by").bindTo { it.createdBy }
    var createdTime = datetime("created_time").bindTo { it.createdTime }
    var updatedBy = varchar("updated_by").bindTo { it.updatedBy }
    var updatedTime = datetime("updated_time").bindTo { it.updatedTime }
    /**
     * 是否删除 1删除0正常
     **/
    var deleted = varchar("deleted").bindTo { it.deleted }
}

创建数据库操作对象

object Tables {
    /**
     * 用户信息
     **/
    val tpmEbUserInfo = TpmEbUserInfoDao
    /**
     *  用户信息
     **/
    val Database.tpmEbUserInfo get() = this.sequenceOf(TpmEbUserInfoDao,false)
}

第三步创建service层

@Service
class UserService:BaseService() {

    @Autowired
    lateinit var database: Database
	// 分页查询
    fun adminList(adminList: UserServiceQuery.AdminList): PageDto<List<User>> {
        val user = getAdminUser()
        val userInfo = database.tpmEbUserInfo.find { it.id eq user.userId!! }!!

        val listSearch = database.from(UserDao)
            .select()
            .where {
                val conditions = FdKtOrmBase.conditionsInit()
                
                if (!adminList.name.isNullOrEmpty()) {
                    conditions += UserDao.name eq adminList.name!!
                }
                if(!adminList.createdTime.isNullOrEmpty()){
                    val createdTimes = adminList.createdTime.split(",")
                    conditions += UserDao.createdTime between (LocalDateTime.parse("${createdTimes[0]} 00:00:00.000",
                        DateTimeFormatter.ofPattern(CommonConstant.dateTimeMillFormatter))..LocalDateTime.parse("${createdTimes[1]} 23:59:59.999",
                        DateTimeFormatter.ofPattern(CommonConstant.dateTimeMillFormatter)))
                }

                conditions += UserDao.deleted eq DeleteEnum.FALSE.value
                conditions.combineConditions()
            }
        var total = listSearch.totalRecords
        val list = listSearch
            .limit((adminList.page - 1) * adminList.pageSize, adminList.pageSize)
            .orderBy(UserDao.createdTime.desc())
            .map {
                var info = UserDao.createEntity(it)
                info
            }
        return PageDto(list, adminList.page, adminList.pageSize, total)
    }
	//查看
    fun adminGet(@Valid getQuery: GetQuery): User? {
        return database.Users.find { it.id eq getQuery.id!! }
    }
	//创建
    fun adminCreate(adminCreate: WmQrLogServiceQuery.AdminCreate) {
        val user = getAdminUser()
        val id = UUID.randomUUID().toString()
        database.Users.insert {
            set(it.id, id)
            set(it.name, adminCreate.name)
            set(it.email, adminCreate.email)
            set(it.createdBy, user.userId)
            set(it.createdTime, LocalDateTime.now())
            set(it.deleted, DeleteEnum.FALSE.value)
        }
    }
	
	//更新
	fun update(update: UserServiceQuery.Update) {

        val user = this.getAdminUser()

        val info = database.users.find { (it.id eq update.id) and (it.deleted eq DeleteEnum.FALSE.value) }
        if (info != null) {
            info.name = update.name
            info.updatedBy = user.userId!!
            info.updatedTime = LocalDateTime.now()
            info.flushChanges()
        }
    }
	
	//删除
	fun delete(delete: DeleteQuery) {
        val info = database.users.find { (it.id eq delete.id!!) and (it.deleted eq DeleteEnum.FALSE.value) }
        info?.deleted = DeleteEnum.TRUE.value
        info?.flushChanges()
    }


}

class PageDto<T>(t: T,page: Int,pageSize: Int,totalSize: Int) {

    var page = page
    var pageSize = pageSize
    var totalSize = totalSize
    var datalist = t

}