SQL Server: Connect và Khắc phục lỗi Connection Database Sql Server vs JAVA JDBC

Bước 1: bạn cần download Driver JDBC dành cho SQL Server

Chọn file có đuôi exe

Giải nén thành công chúng ta sẽ sử dụng 1 trong 2 file này để sử dụng cho Connect Jre7 or Jre8

Bước 2: Cần thêm port 1433 cho TCP/IP của máy tính bạn

TCP/IP connection to the host Failed: Solution

The solution is to ensure that your SQL Server instance is listening on the port. Follow these steps to ensure that SQL SERVER 2014 is start listening on port 1433:

1. Go to Start->All Programs-> Microsoft SQL Server 2012-> Configuration Tool
2. Click SQL Server Configuration Manager
3. Expand SQL Server Network Configuration-> Protocol
4. Enable TCP/IP Right box
5. Double Click on TCP/IP and go to IP Addresses Tap and Put port 1433 under TCP port.

Đây là lỗi khi chưa có port 1433

Java JDBC 2017

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 
localhost, port 1433 has failed. Error: "Connection refused: connect. 
Verify the connection properties. Make sure that an instance of SQL Server 
is running on the host and accepting TCP/IP connections at the port. 
Make sure that TCP connections to the port are not blocked by a firewall.

In Windows 8.1, you can also press Windows + c and click on Search icon to start searching for SQL Server Configuration Manager. Once found just click on it to open.

Anyway, once you got to the above screen just double click on highlighted TCP/IP link and this will open the following window, make sure you enter TCP Port as 1433. That's it.

That's all about how to fix The TCP/IP connection to the host localhost, port 1433 has failed error while connecting to SQL SERVER from Java using JDBC API. You also need to create user and logins in order to connect using JDBC and type 4 JDBC driver. I am going to post that information soon in next couple of articles.

Dưới đây là hai cách

Windows Store 2017

package com.fsoft.connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

    private static Connection connection = null;

    public static Connection getConnection() {
        if (connection != null) {
            return connection;
        } else {
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=MyDatabase", "sa", "12345678");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        }
    }

    //Test connection database
    public static void main(String[] args) {
        System.out.println(getConnection());
    }
}

package com.demo.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionFactory {

    //Cách này sử dụng tốt hơn
    private static ConnectionFactory instance = new ConnectionFactory();
    String url = "jdbc:sqlserver://localhost:1433;databaseName=MyDatabase";
    String user = "sa";
    String password = "12345678";
    String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    //private constructor
    private ConnectionFactory() {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static ConnectionFactory getInstance() {
        return instance;
    }

    public Connection getConnection() throws SQLException, ClassNotFoundException {
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }
}
//--------------------------------------------------------------
//  Khi sử dụng - khuyên dùng cách 2 sẽ có điểm khác riêng biệt
//--------------------------------------------------------------
public class mockDAO {
    Connection connection;
    Statement stmt;
    PreparedStatement prestmt;
     
    private static Connection getConnection() throws SQLException, ClassNotFoundException {
        Connection con = ConnectionFactory.getInstance().getConnection();
        return con;
    }
    
    public List<Item> listItem(){
     String query = "SELECT * FROM Item";
     List<Item> list = new ArrayList<>();
     Item item = null;
     try{
      connection = getConnection();
      stmt = connection.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while(rs.next()){
       item = new Item(rs.getInt("ItemID"),rs.getString("ItemName"));
       list.add(item);
      }
     } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally
        {
            try {
                if(stmt != null)
                    stmt.close();
                if(connection != null)
                    connection.close();
                } catch (SQLException e) {
                e.printStackTrace();
            }
        }
     return list;
    }
}

Đây là thông báo Connect thành công của cách 1