安卓tv程序连接SQLSERVER2008问题

Question

安卓8TV程序问题：连接局域网SQLSERVER2008数据库一直失败报错，能ping通，连接方式无误。

build代码

plugins {
    id("com.android.application")
    id("org.jetbrains.kotlin.android") version "1.9.0"
}

android {
    namespace = "com.example.pharmacytv"
    compileSdk = 34

    defaultConfig {
        applicationId = "com.example.pharmacytv"
        minSdk = 26
        targetSdk = 34
        versionCode = 1
        versionName = "1.0"
        vectorDrawables {
            useSupportLibrary = true
        }
    }

    buildTypes {
        release {
            isMinifyEnabled = false
            proguardFiles(
                getDefaultProguardFile("proguard-android-optimize.txt"),
                "proguard-rules.pro"
            )
        }
    }

    compileOptions {
        sourceCompatibility = JavaVersion.VERSION_1_8
        targetCompatibility = JavaVersion.VERSION_1_8
    }

    kotlinOptions {
        jvmTarget = "1.8"
    }

    buildFeatures {
        viewBinding = true
        compose = true  // Enable Compose
    }

    composeOptions {
        kotlinCompilerExtensionVersion = "1.5.0"  // Use a version compatible with your Kotlin version
    }
}

dependencies {
    implementation("androidx.core:core-ktx:1.10.1")
    implementation("androidx.appcompat:appcompat:1.6.1")
    implementation("androidx.constraintlayout:constraintlayout:2.1.4")
    implementation("androidx.lifecycle:lifecycle-runtime-ktx:2.6.1")

    // SQL Server JDBC Driver

    implementation(files("libs/mssql-jdbc-12.6.3.jre8.jar"))

    // Uncomment and use if needed
    //implementation("com.microsoft.sqlserver:mssql-jdbc:9.2.1.jre8")

    // Compose dependencies
    implementation("androidx.compose.ui:ui:1.5.0")
    implementation("androidx.compose.material3:material3:1.1.0")
    implementation("androidx.compose.ui:ui-tooling-preview:1.5.0")
    debugImplementation("androidx.compose.ui:ui-tooling:1.5.0")

    // Android TV dependencies
    implementation("androidx.tv:tv-foundation:1.0.0-alpha07")
    implementation("androidx.tv:tv-material:1.0.0-alpha07")
}

main程序

package com.example.pharmacytv

import android.os.Bundle
import android.os.Handler
import android.util.Log
import android.view.Gravity
import android.view.View
import android.widget.ArrayAdapter
import android.widget.TextView
import androidx.appcompat.app.AppCompatActivity
import com.example.pharmacytv.databinding.ActivityMainBinding
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import kotlin.concurrent.thread
import java.util.logging.Logger;
import java.util.logging.Level;

class MainActivity : AppCompatActivity() {

    private lateinit var binding: ActivityMainBinding
    private val handler = Handler()
    private lateinit var refreshRunnable: Runnable

    private val DB_URL = "jdbc:sqlserver://192.168.8.20:1433;databaseName=wangfengbi;encrypt=false;trustServerCertificate=false;"


    private val USER = "sa"
    private val PASS = "iron"

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        binding = ActivityMainBinding.inflate(layoutInflater)
        setContentView(binding.root)

        val waitingAdapter = createCenteredAdapter()
        val pickupAdapter = createCenteredAdapter()

        binding.waitingList.adapter = waitingAdapter
        binding.pickupList.adapter = pickupAdapter

        refreshRunnable = object : Runnable {
            override fun run() {
                loadData(waitingAdapter, pickupAdapter)
                scrollList()
                handler.postDelayed(this, 30000) // 每30秒钟执行一次
            }
        }

        handler.postDelayed(refreshRunnable, 0) // 立即开始执行
    }

    override fun onDestroy() {
        super.onDestroy()
        handler.removeCallbacks(refreshRunnable) // 停止延迟任务，防止内存泄漏
    }

    private fun createCenteredAdapter(): ArrayAdapter<String> {
        return object : ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, mutableListOf<String>()) {
            override fun getView(position: Int, convertView: View?, parent: android.view.ViewGroup): View {
                val view = super.getView(position, convertView, parent)
                val textView = view.findViewById<TextView>(android.R.id.text1)
                textView.gravity = Gravity.CENTER
                textView.textSize = 20f // 调整文字大小
                textView.setTextColor(resources.getColor(android.R.color.white, null)) // 设置文字颜色为白色
                return view
            }
        }
    }

    private fun loadData(waitingAdapter: ArrayAdapter<String>, pickupAdapter: ArrayAdapter<String>) {
        thread {
            var connection: Connection? = null
            var waitingStmt: java.sql.Statement? = null
            var pickupStmt: java.sql.Statement? = null
            var waitingRs: ResultSet? = null
            var pickupRs: ResultSet? = null

            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
                Log.d("MainActivity", "Driver loaded successfully")

                connection = DriverManager.getConnection(DB_URL, USER, PASS)
                Log.d("MainActivity", "Database connected successfully")

                // 查询等待数据
                val waitingQuery = """
                    SELECT TOP 100 MAX(number) AS '报到号', patientname AS '姓名' 
                    FROM dbo.pdjh 
                    WHERE type = 0 AND DATEDIFF(day, bdtime, GETDATE()) = 0 
                    GROUP BY number, patientname 
                    ORDER BY number
                """.trimIndent()
                waitingStmt = connection.createStatement()
                waitingRs = waitingStmt.executeQuery(waitingQuery)

                val waitingListData = mutableListOf<String>()
                while (waitingRs.next()) {
                    waitingListData.add(waitingRs.getString("姓名"))
                }

                // 查询取药数据
                val pickupQuery = """
                    SELECT TOP 100 MAX(number) AS '报到号', patientname AS '姓名' 
                    FROM dbo.pdjh 
                    WHERE type = 1 AND DATEDIFF(day, bdtime, GETDATE()) = 0 
                    GROUP BY number, patientname 
                    ORDER BY number
                """.trimIndent()
                pickupStmt = connection.createStatement()
                pickupRs = pickupStmt.executeQuery(pickupQuery)

                val pickupListData = mutableListOf<String>()
                while (pickupRs.next()) {
                    pickupListData.add(pickupRs.getString("姓名"))
                }

                Log.d("MainActivity", "Waiting list data: $waitingListData")
                Log.d("MainActivity", "Pickup list data: $pickupListData")

                runOnUiThread {
                    updateListView(binding.waitingList, binding.waitingEmptyView, waitingAdapter, waitingListData)
                    updateListView(binding.pickupList, binding.pickupEmptyView, pickupAdapter, pickupListData)
                }
            } catch (e: Exception) {
                Log.e("MainActivity", "Database connection failed", e)
                runOnUiThread {
                    // 显示错误信息或适当处理错误
                    binding.waitingEmptyView.text = "数据加载失败，请检查网络连接"
                    binding.waitingEmptyView.visibility = View.VISIBLE
                    binding.waitingList.visibility = View.GONE

                    binding.pickupEmptyView.text = "数据加载失败，请检查网络连接"
                    binding.pickupEmptyView.visibility = View.VISIBLE
                    binding.pickupList.visibility = View.GONE
                }
            } finally {
                try {
                    waitingRs?.close()
                    pickupRs?.close()
                    waitingStmt?.close()
                    pickupStmt?.close()
                    connection?.close()
                } catch (e: Exception) {
                    Log.e("MainActivity", "Failed to close resources", e)
                }
            }
        }
    }

    private fun updateListView(listView: android.widget.ListView, emptyView: TextView, adapter: ArrayAdapter<String>, data: List<String>) {
        adapter.clear()
        adapter.addAll(data)
        adapter.notifyDataSetChanged()

        if (data.isEmpty()) {
            listView.visibility = View.GONE
            emptyView.visibility = View.VISIBLE
        } else {
            listView.visibility = View.VISIBLE
            emptyView.visibility = View.GONE
        }
    }

    private fun scrollList() {
        if (binding.waitingList.childCount > 0) {
            binding.waitingList.smoothScrollBy(binding.waitingList.getChildAt(0).height, 1000) // 每秒钟滚动一行
        }

        if (binding.pickupList.childCount > 0) {
            binding.pickupList.smoothScrollBy(binding.pickupList.getChildAt(0).height, 1000) // 每秒钟滚动一行
        }
    }

}

报错

  E  Database connection failed
                                                                                                    com.microsoft.sqlserver.jdbc.SQLServerException: "encrypt" property is set to "false" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: Socket is closed. ClientConnectionId:aa10323e-b15c-41ec-a894-eea8312de959
                                                                                                        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:4290)

Answer 1

引用gpt4
从报错信息来看，主要的问题是与 SQL Server 的连接配置，特别是关于 SSL 加密和服务器证书的设置。当前的错误提示是“encrypt 属性设置为 false，trustServerCertificate 属性设置为 false，但驱动程序无法通过使用安全套接字层（SSL）加密建立到 SQL Server 的安全连接”。以下是一些可能的解决方案和调整：

解决方案一：设置 trustServerCertificate 为 true

将 trustServerCertificate 设置为 true 可以绕过证书验证，允许在没有有效证书的情况下进行连接。这样可以解决证书相关的问题，但在生产环境中这可能不安全。

private val DB_URL = "jdbc:sqlserver://192.168.8.20:1433;databaseName=wangfengbi;encrypt=false;trustServerCertificate=true;"

解决方案二：使用 SSL 加密

如果您需要使用 SSL 加密，确保 SQL Server 配置正确并且提供有效的证书。可以将 encrypt 设置为 true 并指定 trustStore 和 trustStorePassword。

private val DB_URL = "jdbc:sqlserver://192.168.8.20:1433;databaseName=wangfengbi;encrypt=true;trustServerCertificate=true;"

解决方案三：使用 Microsoft 提供的 JDBC 驱动程序版本

确保您使用的是最新版本的 Microsoft SQL Server JDBC 驱动程序。如果当前版本存在问题，尝试使用不同的版本。可以在 build.gradle 文件中指定驱动程序的版本：

dependencies {
    // 替换为最新版本的驱动程序
    implementation("com.microsoft.sqlserver:mssql-jdbc:9.2.1.jre8")
}

解决方案四：检查网络和防火墙设置

确保 SQL Server 的防火墙配置允许 Android 设备的 IP 地址连接到服务器。此外，检查 SQL Server 配置，确保 TCP/IP 协议已启用，并且在正确的端口上监听。

完整代码示例

结合上述解决方案，下面是更新后的代码示例：

package com.example.pharmacytv

import android.os.Bundle
import android.os.Handler
import android.util.Log
import android.view.Gravity
import android.view.View
import android.widget.ArrayAdapter
import android.widget.TextView
import androidx.appcompat.app.AppCompatActivity
import com.example.pharmacytv.databinding.ActivityMainBinding
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import kotlin.concurrent.thread

class MainActivity : AppCompatActivity() {

    private lateinit var binding: ActivityMainBinding
    private val handler = Handler()
    private lateinit var refreshRunnable: Runnable

    private val DB_URL = "jdbc:sqlserver://192.168.8.20:1433;databaseName=wangfengbi;encrypt=false;trustServerCertificate=true;"
    private val USER = "sa"
    private val PASS = "iron"

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        binding = ActivityMainBinding.inflate(layoutInflater)
        setContentView(binding.root)

        val waitingAdapter = createCenteredAdapter()
        val pickupAdapter = createCenteredAdapter()

        binding.waitingList.adapter = waitingAdapter
        binding.pickupList.adapter = pickupAdapter

        refreshRunnable = object : Runnable {
            override fun run() {
                loadData(waitingAdapter, pickupAdapter)
                scrollList()
                handler.postDelayed(this, 30000) // 每30秒钟执行一次
            }
        }

        handler.postDelayed(refreshRunnable, 0) // 立即开始执行
    }

    override fun onDestroy() {
        super.onDestroy()
        handler.removeCallbacks(refreshRunnable) // 停止延迟任务，防止内存泄漏
    }

    private fun createCenteredAdapter(): ArrayAdapter<String> {
        return object : ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, mutableListOf<String>()) {
            override fun getView(position: Int, convertView: View?, parent: android.view.ViewGroup): View {
                val view = super.getView(position, convertView, parent)
                val textView = view.findViewById<TextView>(android.R.id.text1)
                textView.gravity = Gravity.CENTER
                textView.textSize = 20f // 调整文字大小
                textView.setTextColor(resources.getColor(android.R.color.white, null)) // 设置文字颜色为白色
                return view
            }
        }
    }

    private fun loadData(waitingAdapter: ArrayAdapter<String>, pickupAdapter: ArrayAdapter<String>) {
        thread {
            var connection: Connection? = null
            var waitingStmt: java.sql.Statement? = null
            var pickupStmt: java.sql.Statement? = null
            var waitingRs: ResultSet? = null
            var pickupRs: ResultSet? = null

            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
                Log.d("MainActivity", "Driver loaded successfully")

                connection = DriverManager.getConnection(DB_URL, USER, PASS)
                Log.d("MainActivity", "Database connected successfully")

                // 查询等待数据
                val waitingQuery = """
                    SELECT TOP 100 MAX(number) AS '报到号', patientname AS '姓名' 
                    FROM dbo.pdjh 
                    WHERE type = 0 AND DATEDIFF(day, bdtime, GETDATE()) = 0 
                    GROUP BY number, patientname 
                    ORDER BY number
                """.trimIndent()
                waitingStmt = connection.createStatement()
                waitingRs = waitingStmt.executeQuery(waitingQuery)

                val waitingListData = mutableListOf<String>()
                while (waitingRs.next()) {
                    waitingListData.add(waitingRs.getString("姓名"))
                }

                // 查询取药数据
                val pickupQuery = """
                    SELECT TOP 100 MAX(number) AS '报到号', patientname AS '姓名' 
                    FROM dbo.pdjh 
                    WHERE type = 1 AND DATEDIFF(day, bdtime, GETDATE()) = 0 
                    GROUP BY number, patientname 
                    ORDER BY number
                """.trimIndent()
                pickupStmt = connection.createStatement()
                pickupRs = pickupStmt.executeQuery(pickupQuery)

                val pickupListData = mutableListOf<String>()
                while (pickupRs.next()) {
                    pickupListData.add(pickupRs.getString("姓名"))
                }

                Log.d("MainActivity", "Waiting list data: $waitingListData")
                Log.d("MainActivity", "Pickup list data: $pickupListData")

                runOnUiThread {
                    updateListView(binding.waitingList, binding.waitingEmptyView, waitingAdapter, waitingListData)
                    updateListView(binding.pickupList, binding.pickupEmptyView, pickupAdapter, pickupListData)
                }
            } catch (e: Exception) {
                Log.e("MainActivity", "Database connection failed", e)
                runOnUiThread {
                    // 显示错误信息或适当处理错误
                    binding.waitingEmptyView.text = "数据加载失败，请检查网络连接"
                    binding.waitingEmptyView.visibility = View.VISIBLE
                    binding.waitingList.visibility = View.GONE

                    binding.pickupEmptyView.text = "数据加载失败，请检查网络连接"
                    binding.pickupEmptyView.visibility = View.VISIBLE
                    binding.pickupList.visibility = View.GONE
                }
            } finally {
                try {
                    waitingRs?.close()
                    pickupRs?.close()
                    waitingStmt?.close()
                    pickupStmt?.close()
                    connection?.close()
                } catch (e: Exception) {
                    Log.e("MainActivity", "Failed to close resources", e)
                }
            }
        }
    }

    private fun updateListView(listView: android.widget.ListView, emptyView: TextView, adapter: ArrayAdapter<String>, data: List<String>) {
        adapter.clear()
        adapter.addAll(data)
        adapter.notifyDataSetChanged()

        if (data.isEmpty()) {
            listView.visibility = View.GONE
            emptyView.visibility = View.VISIBLE
        } else {
            listView.visibility = View.VISIBLE
            emptyView.visibility = View.GONE
        }
    }

    private fun scrollList() {
        if (binding.waitingList.childCount > 0) {
            binding.waitingList.smoothScrollBy(binding.waitingList.getChildAt(0).height, 1000) // 每秒钟滚动一行
        }

        if (binding.pickupList.childCount > 0) {
            binding.pickupList.smoothScrollBy(binding.pickupList.getChildAt(0).height, 1000) // 每秒钟滚动一行
        }
    }
}

总结

1. 确保 trustServerCertificate 属性设置为 true。
2. 检查 SQL Server 的防火墙设置和 TCP/IP 协议配置。
3. 使用最新版本的 Microsoft SQL Server JDBC 驱动程序。
4. 确保网络连接配置正确。

这些调整应该有助于解决连接 SQL Server 数据库时遇到的问题。如果问题仍然存在，请提供更多错误信息，以便进一步诊断和解决。