java 使用 JDBC 将 Android 连接到 SQL Server

Connecting Android to SQL Server using JDBC

提问by plasmy

I am currently making an application for Android that is supposed to synchronize it's data to MSSQL Server 2008. I am currently testing out ways to make it work, since I have never done it before. I should mention that the device will sync whenever it is connected to the USB port and not through WiFi, since the company doesn't want to register the devices on the network.

我目前正在为 Android 制作一个应用程序，该应用程序应该将其数据同步到 MSSQL Server 2008。我目前正在测试使其工作的方法，因为我以前从未这样做过。我应该提到，只要设备连接到 USB 端口而不是通过 WiFi 连接，设备就会同步，因为该公司不想在网络上注册设备。

So far this is what I've worked out to connect Java to SQL Server. This is a simple Select code (I am currently using SQLExpress to test):

到目前为止，这是我将 Java 连接到 SQL Server 的方法。这是一个简单的 Select 代码（我目前正在使用 SQLExpress 进行测试）：

  String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;" +
             "databaseName=Android;integratedSecurity=true;";

  // Declare the JDBC objects.
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;

  try {
     // Establish the connection.
     Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
     con = DriverManager.getConnection(connectionUrl);

     // Create and execute an SQL statement that returns some data.
     String SQL = "SELECT * FROM AndroidTest;";
     stmt = con.createStatement();
     rs = stmt.executeQuery(SQL);

     // Iterate through the data in the result set and display it.
     while (rs.next()) {
        System.out.println(rs.getString(1) + " " + rs.getString(2));
     }
  }

  // Handle any errors that may have occurred.
  catch (Exception e) {
     e.printStackTrace();
  }
  finally {
     if (rs != null) try { rs.close(); } catch(Exception e) {}
     if (stmt != null) try { stmt.close(); } catch(Exception e) {}
     if (con != null) try { con.close(); } catch(Exception e) {}
  }

Now, I've tried the same thing in Android and this is what it looks like:

现在，我在 Android 中尝试了同样的事情，这就是它的样子：

package com.example.testsqlserver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.widget.EditText;

public class MainActivity extends Activity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.activity_main, menu);
        return true;
    }

    public void clickSend(View view) {
        (new Thread(new TestThread())).start();
    }
    public class TestThread extends Thread {
      public void run() {
          String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;" +
                     "databaseName=Android;integratedSecurity=true;";

          // Declare the JDBC objects.
          Connection con = null;
          Statement stmt = null;

          try {
             // Establish the connection.
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             con = DriverManager.getConnection(connectionUrl);

             //Get information from EditText
             EditText txtTest = (EditText)findViewById(R.id.txtTest);
             EditText txtName = (EditText)findViewById(R.id.txtName);
             String test = txtTest.getText().toString();
             String name = txtName.getText().toString();

             // Create and execute an SQL statement that returns some data.
             String SQL = "INSERT INTO AndroidTest VALUES('" + test + "', '" + name + "');";
             stmt = con.createStatement();
             stmt.executeUpdate(SQL);
             Log.e("Success", "Success");
          }

          // Handle any errors that may have occurred.
          catch (Exception e) {
             e.printStackTrace();
              Log.e("Error", e.toString());
          }
          finally {
             if (stmt != null) try { stmt.close(); } catch(Exception e) {}
             if (con != null) try { con.close(); } catch(Exception e) {}
          }
      }

      public void main(String args[]) {
          (new TestThread()).start();
      }
    }
}

In the first example it works perfectly, but in the second example it gives me this error:

在第一个例子中它完美地工作，但在第二个例子中它给了我这个错误：

12-17 20:15:12.589: E/Error(1668): com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "failed to connect to /127.0.0.1 (port 1433) after 403ms: isConnected failed: ECONNREFUSED (Connection refused). Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

12-17 20:15:12.589: E/Error(1668): com.microsoft.sqlserver.jdbc.SQLServerException: TCP/IP 连接到主机 127.0.0.1，端口 1433 失败。错误：“403 毫秒后无法连接到 /127.0.0.1（端口 1433）：isConnected 失败：ECONNREFUSED（连接被拒绝）。验证连接属性，检查 SQL Server 实例是否正在主机上运行并接受 TCP/IP 连接在端口，并且没有防火墙阻止到端口的 TCP 连接。”。

I had that error the first time I ran the first code and I just had to enable port 1433 in the SQL Server Settings. I don't understand, though, why isn't it working on the second table. It is the same code, the only difference is that it's executed through a button press and that I have it running on a separate thread.

我第一次运行第一个代码时遇到了这个错误，我只需要在 SQL Server 设置中启用端口 1433。不过，我不明白为什么它不能在第二张桌子上工作。这是相同的代码，唯一的区别是它是通过按下按钮执行的，并且我让它在单独的线程上运行。

Any help will be appreciated, thank you.

任何帮助将不胜感激，谢谢。

回答by Squonk

See this section on Emulator Netorking.

请参阅有关Emulator Netorking 的这一部分。

You need to use 10.0.2.2 which allows you to communicate from an emulator to the development machines 127.0.0.1 address.

您需要使用 10.0.2.2，它允许您从模拟器通信到开发机器 127.0.0.1 地址。

You may also have to do some port redirection (see further in that documentation).

您可能还需要进行一些端口重定向（请参阅该文档中的进一步内容）。