Java实现连接SQL Server解决方案及代码

下面展示了连接SQL Server数据库的整个流程：

• 加载数据库驱动
• 建立数据库连接
• 执行SQL语句
• 处理结果
• 关闭连接

在连接之前，前提是确保数据库成功的下载，创建，配置好账号密码。

运行成功的代码：

import java.sql.*;

public class sqlserverConnection {
	
	//驱动
    private static String cxDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    //连接地址
    private static String cxUrl = "jdbc:sqlserver://172.16.100.1:1433;databaseName=abc;encrypt=false";
    //用户名
    private static String cxUser = "abc";
    //用户密码:数据库的密码
    private static String cxPassword = "abc";
	
	public static void main(String[] args) {
		try{
            Class.forName(cxDriver);   //加载sqlserver的驱动类
            System.out.println("加载SQLServer驱动类成功!");
        }
        catch(ClassNotFoundException a){
            System.out.println("加载SQLServer驱动失败!");
            a.printStackTrace();
        }
        Connection dbcon=null;           //处理与特定数据库的连接
        try{
            dbcon=DriverManager.getConnection(cxUrl,cxUser,cxPassword);
            System.out.println("数据库连接成功!");
            dbcon.close();
        }
        catch(SQLException e){
            System.out.println("数据库连接失败!");
            e.printStackTrace();
        }

	}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

报错：

Exception in thread "main" java.lang.UnsupportedClassVersionError: com/microsoft/sqlserver/jdbc/SQLServerDriver has been compiled by a more recent version of the Java Runtime (class file version 55.0), this version of the Java Runtime only recognizes class file versions up to 52.0
	at java.lang.ClassLoader.defineClass1(Native Method)
	at java.lang.ClassLoader.defineClass(ClassLoader.java:763)
	at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
	at java.net.URLClassLoader.defineClass(URLClassLoader.java:468)
	at java.net.URLClassLoader.access$100(URLClassLoader.java:74)
	at java.net.URLClassLoader$1.run(URLClassLoader.java:369)
	at java.net.URLClassLoader$1.run(URLClassLoader.java:363)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(URLClassLoader.java:362)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at timeunit_test.sqlserverConnection.main(sqlserverConnection.java:17)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

问题在于用较高版本的jdk编译的class文件在低版本的JVM上运行所导致的，通俗讲就是编译运行版本不匹配。

可惜我不是
看下jar包目录下是否存在多余的，不需要的版本，去除
我第一次jre8,jre11版本的都导入了，导致驱动加载失败
使用cmd java -version 查看java javac版本 是否都匹配

加载驱动成功，但是连接又失败了

com.microsoft.sqlserver.jdbc.SQLServerException: "encrypt" property is set to "true" 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: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:f903a939-f589-4052-89f8-580442a86a1b
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:4026)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1954)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3552)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:3172)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:3014)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1836)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1246)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at timeunit_test.sqlserverConnection.main(sqlserverConnection.java:26)
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	...
1
2
3
4
5
6
7
8
9
10
11
12
13

这个问题最有效的办法是在连接地址Url后面加上encrypt=false。

执行SQL语句:

Statement statement = connection.createStatement();
String sql = "SELECT * FROM mytable";
ResultSet resultSet = statement.executeQuery(sql);
1
2
3

上述代码中，statement对象用于执行SQL语句。sql是要执行的SQL语句，这里是一个简单的查询语句。executeQuery()方法用于执行查询语句，并返回一个ResultSet对象，用于获取查询结果。

处理结果

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    // 处理每一条查询结果
}
1
2
3
4
5

上述代码中，通过resultSet.next()方法判断是否还有下一条查询结果，然后使用getInt()和getString()等方法获取查询结果中的具体字段值。

关闭连接

resultSet.close();
statement.close();
connection.close();
1
2
3

上述代码中，分别关闭resultSet、statement和connection对象，释放相关资源。

参考大家的文档
https://blog.csdn.net/qq243920161/article/details/78971861
https://blog.csdn.net/m0_46669582/article/details/111685213
https://blog.csdn.net/qq_42759370/article/details/103350930
https://blog.csdn.net/qq_37917691/article/details/108262286
https://blog.csdn.net/Green_Hand_is_me/article/details/122272151
https://blog.csdn.net/qq_45835014/article/details/128268932
https://blog.csdn.net/qq_51391437/article/details/121051234