使用jdbc操作SQLite数据库(Kotlin版)

服务端可以使用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
    }
}

Tags：system.data.sqlite

微信扫一扫：分享

微信里点“发现”，扫一下

二维码便可将本文分享至朋友圈。