JDBC connects IDEA and SQL Server database

Steps of JDBC linking SQL Server database

  1. SQL Server2014 database configuration:

(1). Open the installed SQL Server 2014 Management Studio, select SQL server authentication, and enter the user name (SA by default) and password,

(2). Open the security login, right-click sa, select attribute status, and set whether to allow connection to the database engine for grant and enable

2. Computer port configuration

(1). Right click my computer, manage services and applications, SQL Server Configuration Manager, SQL server network configuration;

(2). Click the TCP/IP attribute, double-click to open the IP address tab, configure the TCP port of IP1 127.0.0.1 as 1433 (TCP dynamic port does not need to be set), enable and select Yes (if it cannot be clicked, click the inverted triangle of the direction with the mouse, and press and hold the down arrow key and Enter key on the keyboard), set the same settings as IP2 and IP1, and then scroll to the lowest IPALL TCP port as 1433 (TCP dynamic port does not need to be set);

(3). Restart SQL Server Service: click service, find SQL server, right-click and select restart

 (4). Open the control panel, programs, programs and functions, enable and close the windows function, check the Telnet function, win+r, enter cmd, enter telnet 127.0.0.1 1433, and press enter. If a blank box appears, it will be successful

 

3. Import JDBC package

 (1). The browser searches sql server jdbc driver, enters Microsoft's official website, selects the JBDC driver suitable for the corresponding Java version, downloads and decompresses it to any file location,

 (2). Open the IDEA software and create a new Java project and Java Class

  (3). Click '+' JRE or Directories under File, Project Structure, Modules, Dependencies, Module SDK in the menu bar, select the File path to store the decompressed JDBC, and select the appropriate jre;

 (4). Enter code, test link

Connection code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class JavaSql {
    static Connection dbConn = null;
    public static void main(String[] args) {
    String dbURL = "jdbc:sqlserver://localhost:1433;

DatabaseName=temp";

String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
        try {
            Class.forName(driver);
            System.out.println("Loading driver succeeded!");
            dbConn = DriverManager.getConnection(dbURL, "sa", " <your password>");
            System.out.println("Database connection succeeded!");

      }catch(Exception e) {
            e.printStackTrace();
            System.out.println("Failed to connect to database!");
        }
    }
}

Run the program to show that the database connection is successful!; Add SQL statement:

String sql="select * from S";
PreparedStatement statement=null;
statement=dbConn.prepareStatement(sql);
ResultSet res=null;
res=statement.executeQuery();
while(res.next()){
    String title=res.getString("SNAME");
    System.out.println(title);
}

Run program results:

Connection failure and solutions:

Error 1:

Exceptionin thread "main"java lang.UnsuportedClassVersionError

:com/Microsoft/SQLServerDriver has been compiled by a more recentversion of the java Runtime...

Solution: first, check whether the versions of jdk and jdbc are compatible. SQL jdbc_ 42 version File, and then select jre8 File to transfer sqljdbc 42 Jar is copied into Java--jdk--jre--lib--ext (taking java8 as an example), open idea, click the + sign in the upper right corner of File à Project Structure à SDKs à in the menu bar, and select the path copied above to import.

Error 2: com microsoft. sqlserver. jdbc. Sqlserverexception: the driver cannot establish a secure connection with SQL Server by using secure sockets layer (SSL) encryption. Error: sun security....

Solution: just click DatabaseName = "; Add after

userencrypt = true;trustServerCertificate=true; Can be solved

Tags: Database intellij-idea

Posted by harrisonad on Tue, 17 May 2022 23:58:36 +0300