Solution and code for connecting to SQL Server using Java

The following shows the entire process of connecting to the SQL Server database:

  • Load database driver
  • Establish database connection
  • Execute SQL statement
  • process result
  • close connection

Before connecting, the premise is to ensure that the database is successfully downloaded, created, and the account and password are configured.

Code that runs successfully:

import java.sql.*;

public class sqlserverConnection {<!-- -->
\t
//driver
    private static String cxDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    //Connection address
    private static String cxUrl = "jdbc:sqlserver://172.16.100.1:1433;databaseName=abc;encrypt=false";
    //username
    private static String cxUser = "abc";
    //User password: database password
    private static String cxPassword = "abc";
\t
public static void main(String[] args) {<!-- -->
try{<!-- -->
            Class.forName(cxDriver); //Load the driver class of sqlserver
            System.out.println("Loading SQLServer driver class successfully!");
        }
        catch(ClassNotFoundException a){<!-- -->
            System.out.println("Loading SQLServer driver failed!");
            a.printStackTrace();
        }
        Connection dbcon=null; //Process the connection to a specific database
        try{<!-- -->
            dbcon=DriverManager.getConnection(cxUrl,cxUser,cxPassword);
            System.out.println("Database connection successful!");
            dbcon.close();
        }
        catch(SQLException e){<!-- -->
            System.out.println("Database connection failed!");
            e.printStackTrace();
        }

}

}

Error reported:

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)

The problem is that class files compiled with a higher version of jdk are run on a lower version of JVM. In layman’s terms, it means that the compiled and running versions do not match.

It’s a pity that I’m not
Check whether there are redundant and unnecessary versions in the jar package directory and remove them.
The first time I imported both jre8 and jre11 versions, the driver failed to load.
Use cmd java -version to check whether the java javac version matches.

The driver was loaded successfully, but the connection failed again.

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
...

The most effective way to solve this problem is to add encrypt=false after the connection address Url.

Execute SQL statement:

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

In the above code, the statement object is used to execute SQL statements. sql is the SQL statement to be executed, here is a simple query statement. The executeQuery() method is used to execute query statements and returns a ResultSet object for obtaining query results.

process result

while (resultSet.next()) {<!-- -->
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    // Process each query result
}

In the above code, the resultSet.next() method is used to determine whether there is a next query result, and then methods such as getInt() and getString() are used to obtain the specific field values in the query results.

close connection

resultSet.close();
statement.close();
connection.close();

In the above code, the resultSet, statement and connection objects are closed respectively and related resources are released.

Refer to our documentation
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