java sqlite jdbc kotlin jdbc

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