使用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? = 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 = 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     ): 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     } }