Kotlin语言下的SQLite数据库操作指南

服务端可以使用Sqlite数据库吗？

当然是可以的。SQLite在小、中型流量的网站上还是可以用的。通常情况下，每天点击量在100K（10万）以下是完全没有问题的。

首先要添加依赖包：

dependencies {	...  implementation "org.xerial:sqlite-jdbc:3.36.0.2"  ...}

//数据库操作object DBUtil {    private const val url = "jdbc:sqlite:dbs/hy_users.db"    /**     * 支持drop table、create table、insert into、update……     * @param sql 数据库查询语句     * @param params PreparedStatement使用的参数     * @param timeout 超时时间     * @param block 接收返回的每一行数据     */    fun update(sql: String, params: Array<Any?>? = null, timeout: Int = 30): Boolean {        var state = 0        dose {            if (params != null) {                val statement = getPreparedStatement(it, sql, timeout, params)                state = statement.executeUpdate()            } else {                val statement = it.createStatement()                statement.queryTimeout = timeout                state = statement.executeUpdate(sql)            }        }        return state > 0    }    /**     * 查询操作     * @param sql 数据库查询语句     * @param params PreparedStatement使用的参数     * @param timeout 超时时间     * @param block 接收返回的每一行数据     */    fun query(sql: String, params: Array<Any?> = arrayOf(), timeout: Int = 30, block: (ResultSet) -> Unit) {        dose {            if (params.isNotEmpty()) {                //使用PreparedStatement                val statement = getPreparedStatement(it, sql, timeout, params)                val rs = statement.executeQuery()                while (rs.next()) {                    block(rs)                }            } else {                //使用Statement                val statement = it.createStatement()                statement.queryTimeout = timeout                val rs = statement.executeQuery(sql)                while (rs.next()) {                    block(rs)                }            }        }    }    /**     * 获取PreparedStatement     */    private fun getPreparedStatement(        it: Connection,        sql: String,        timeout: Int,        params: Array<Any?>    ): PreparedStatement {        val statement = it.prepareStatement(sql)        statement.queryTimeout = timeout        for (i in 1..params.size) {            when (val a = params[i - 1]) {                null -> statement.setNull(i, Types.NULL)                is String -> {                    when {                        RegexUtil.isTimestamp(a) -> {                            statement.setTimestamp(i, Timestamp.valueOf(a))                        }                        RegexUtil.isShortDate(a) -> {                            statement.setDate(i, Date.valueOf(a))                        }                        RegexUtil.isShortTime(a) -> {                            statement.setTime(i, Time.valueOf(a))                        }                        else -> {                            statement.setString(i, a)                        }                    }                }                is Boolean -> statement.setBoolean(i, a)                is Byte -> statement.setByte(i, a)                is Short -> statement.setShort(i, a)                is Int -> statement.setInt(i, a)                is Long -> statement.setLong(i, a)                is Float -> statement.setFloat(i, a)                is Double -> statement.setDouble(i, a)                is BigDecimal -> statement.setBigDecimal(i, a)                is ByteArray -> statement.setBytes(i, a)                is Date -> statement.setDate(i, a)                is Time -> statement.setTime(i, a)                is Timestamp -> statement.setTimestamp(i, a)            }        }        return statement    }    /**     * 获取数据库连接并执行数据库操作     */    private fun dose(block: (Connection) -> Unit) {        try {            DriverManager.getConnection(url).use {                block(it)            }        } catch (e: SQLException) {            System.err.println(e.message)        }    }}

再上个Dao的代码：

object UserDao {    /**     * 创建用户     */    fun createUser(user: User): String {        var str = HyCode.dbError        val sql = "insert into User(name,password) values(?,?)"        val flag = DBUtil.update(sql, arrayOf(user.name, user.password))        if (flag) {            str = HyCode.dbSuccess        }        return str    }    /**     * 通过用户名查询用户     */    fun findUserByName(name: String): User? {        var user: User? = null        val sql = "select * from User where name=?"        DBUtil.query(sql, arrayOf(name)) {            user = User(it.getString("name"), it.getString("password"), it.getInt("id"))        }        return user    }    /**     * 通过用户名查询用户Id     */    fun findUserIdByName(name: String): Int? {        var userId: Int? = null        val sql = "select id from User where name=?"        DBUtil.query(sql, arrayOf(name)) {            userId = it.getInt("id")        }        return userId    }}