Kotlin安卓App通过API读写SQL数据库实现登录验证与记录求助

1. 实现LoginDataSource的数据库交互逻辑 #

补全LoginDataSource.kt，实现用户验证与登录记录插入的数据库操作，用协程处理异步任务避免主线程阻塞：

import com.jimmysworld.data.model.LoggedInUser
import com.jimmysworld.data.Result
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.withContext
import java.sql.DriverManager
import java.util.*

class LoginDataSource {

    // 建议将数据库凭证移至加密存储或gradle配置文件，避免硬编码
    private val DB_URL = "jdbc:mysql://sql99.dbn7.host-h.net:3306/JimmysWorldDB"
    private val DB_USER = "你的数据库用户名"
    private val DB_PASS = "你的数据库密码"

    suspend fun login(username: String, password: String): Result<LoggedInUser> {
        return withContext(Dispatchers.IO) {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver")
                DriverManager.getConnection(DB_URL, DB_USER, DB_PASS).use { conn ->
                    // 验证用户：匹配用户名、密码且HasAccess为true
                    val querySql = """
                        SELECT Username FROM UserLogins 
                        WHERE Username = ? AND Password = ? AND HasAccess = TRUE
                    """.trimIndent()
                    conn.prepareStatement(querySql).use { stmt ->
                        stmt.setString(1, username)
                        stmt.setString(2, password)
                        val resultSet = stmt.executeQuery()
                        if (resultSet.next()) {
                            // 登录成功后插入记录
                            insertLoginRecord(username)
                            Result.Success(LoggedInUser(username, username))
                        } else {
                            Result.Error(Exception("用户名密码不匹配或无访问权限"))
                        }
                    }
                }
            } catch (e: Exception) {
                Result.Error(e)
            }
        }
    }

    private fun insertLoginRecord(username: String) {
        try {
            DriverManager.getConnection(DB_URL, DB_USER, DB_PASS).use { conn ->
                val insertSql = """
                    INSERT INTO LoginRecords (LoginDate, LoginTime, Username)
                    VALUES (CURDATE(), CURTIME(), ?)
                """.trimIndent()
                conn.prepareStatement(insertSql).use { stmt ->
                    stmt.setString(1, username)
                    stmt.executeUpdate()
                }
            }
        } catch (e: Exception) {
            e.printStackTrace()
        }
    }

    fun logout() {
        // 可选：清理本地登录状态
    }
}

2. 移除LoginViewModel中的硬编码验证逻辑 #

修改LoginViewModel.kt，基于Repository返回的真实结果更新登录状态：

import android.util.Patterns
import androidx.lifecycle.LiveData
import androidx.lifecycle.MutableLiveData
import androidx.lifecycle.ViewModel
import androidx.lifecycle.viewModelScope
import com.jimmysworld.R
import com.jimmysworld.data.LoginRepository
import com.jimmysworld.data.Result
import kotlinx.coroutines.launch

class LoginViewModel(private val loginRepository: LoginRepository) : ViewModel() {

    private val _loginForm = MutableLiveData<LoginFormState>()
    val loginFormState: LiveData<LoginFormState> = _loginForm

    private val _loginResult = MutableLiveData<LoginResult>()
    val loginResult: LiveData<LoginResult> = _loginResult

    fun login(username: String, password: String) {
        viewModelScope.launch {
            val result = loginRepository.login(username, password)
            when (result) {
                is Result.Success -> {
                    _loginResult.value = LoginResult(success = LoggedInUserView(displayName = result.data.displayName))
                }
                is Result.Error -> {
                    _loginResult.value = LoginResult(error = R.string.login_failed)
                }
            }
        }
    }

    fun loginDataChanged(username: String, password: String) {
        if (!isUserNameValid(username)) {
            _loginForm.value = LoginFormState(usernameError = R.string.invalid_username)
        } else if (!isPasswordValid(password)) {
            _loginForm.value = LoginFormState(passwordError = R.string.invalid_password)
        } else {
            _loginForm.value = LoginFormState(isDataValid = true)
        }
    }

    private fun isUserNameValid(username: String): Boolean {
        return if (username.contains('@')) {
            Patterns.EMAIL_ADDRESS.matcher(username).matches()
        } else {
            username.isNotBlank()
        }
    }

    private fun isPasswordValid(password: String): Boolean {
        return password.length > 7
    }
}

3. 调整LoginRepository适配协程异步操作 #

修改LoginRepository.kt的登录方法为挂起函数，适配DataSource的异步逻辑：

import com.jimmysworld.data.model.LoggedInUser
import com.jimmysworld.data.Result

class LoginRepository(val dataSource: LoginDataSource) {

    var user: LoggedInUser? = null
        private set

    val isLoggedIn: Boolean
        get() = user != null

    init {
        user = null
    }

    fun logout() {
        user = null
        dataSource.logout()
    }

    suspend fun login(username: String, password: String): Result<LoggedInUser> {
        val result = dataSource.login(username, password)
        if (result is Result.Success) {
            setLoggedInUser(result.data)
        }
        return result
    }

    private fun setLoggedInUser(loggedInUser: LoggedInUser) {
        this.user = loggedInUser
    }
}

必要配置补充 #

• 添加MySQL JDBC依赖到build.gradle：

  implementation 'mysql:mysql-connector-java:8.0.33'
  

• 在AndroidManifest.xml添加网络权限：

  <uses-permission android:name="android.permission.INTERNET" />
  

内容的提问来源于stack exchange，提问作者DevNoob