AndroidStudio通过jtds远程访问SQLserver数据库

我的是腾讯云远程连接sqlserver，本地的话应该也一样。

一、测试开发环境

windows10 + androidstudio3.3.2 + SqlServer2008R2

二、下载SQL驱动JTDS

1 到这里
https://sourceforge.net/projects/jtds/files/jtds/
各种版本都有
下载 jtds-1.3.1-dist.zip
现在的1.3.1已经可以用了，网上说的大部分都是以前的1.2.7版本的。
放在libs下。（博主用的1.2.7）

• 1 SQL ERVER 允许远程访问
  开始–SQL2008–配置工具–SQL Server配置管理器–SQL Server网络配置–SQLexpress的协议–TCP/IP
  状态 要改为 已启用
  

• 2.可能有的还需要配置端口号1433，不需要配置的忽略这一步。

• 具体可看
  https://jingyan.baidu.com/article/6c67b1d6ca06f02787bb1ed1.html

• 3 建立数据库和表
  数据库 BOOK
  建立表 管理员，字段 id，name，pwd，内容如下：

  id name pwd
  1001 zhangsan 1234
  1002 lisi 1234
  1003 wangwu 1234
  1004 jiujiu 4567
  

三、用JTDS连接SQLServer数据库相关代码

1、创建数据库连接类 DBUtil.java

 package com.example.linkdatabaseapplication;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * @author Admin
     * @version $Rev$
     * @des ${TODO}
     * @updateAuthor $Author$
     * @updateDes ${TODO}
     */
    public class DBUtil {
    private static Connection getSQLConnection(String ip, String user,String pwd,String db){
    Connection con = null;
    try {
    Class.forName("net.sourceforge.jtds.jdbc.Driver");
    //DriverManager.getConnection("jdbc:jtds:sqlserver://" + ip + ":1433/" + db + ";charset=utf8", user, pwd);
    con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + ip + ":1433/" + db , user, pwd);
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return con;
    }
    
    public static String QuerySQL(){
    String result ="";
    
    
    try {
    Connection conn = getSQLConnection("xxx.xxx.xxx.xxx", "sa", "1234", "book");
    String sql = "select top 10 * from 管理员";
    Statement stmt = conn.createStatement();//
    ResultSet rs = stmt.executeQuery(sql);
    while (rs.next())
    {
    String s1 = rs.getString("name");
    String s2 = rs.getString("pwd");
    result += s1 + "  -  " + s2 + "\n";
    System.out.println(s1 + "  -  " + s2);
    }
    rs.close();
    stmt.close();
    conn.close();
    } catch (SQLException e) {
    e.printStackTrace();
    result += "查询数据异常!" + e.getMessage();
    }
    return result;
    }
    public static void main(String[] args)
    {
    QuerySQL();
    }
    }

2、创建一个布局文件，activity_main.xml

 <?xml version="1.0" encoding="utf-8"?>
    <RelativeLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity">
    
    <Button
    android:id="@+id/btnTestSql"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="@string/testSQL"
    />
    <Button
    android:id="@+id/btnClean"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentRight="true"
    android:text="@string/clean"
    />
    <TextView
    android:id="@+id/tvTestResult"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@+id/btnTestSql"
    android:text="@string/hello_world"
    />
    </RelativeLayout>

Strings.xml

  <resources>
    <string name="app_name">LinkDatabaseApplication</string>
    <string name="action_settings">Settings</string>
    <string name="hello_world">Hello world!</string>
    <string name="testSQL">测试SQL</string>
    <string name="clean">清除数据</string>
    </resources>

dimens.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
<dimen name="activity_vertical_margin">50dp</dimen>
<dimen name="activity_horizontal_margin">30dp</dimen>
</resources>

3、具体代码调用 MainActivity.java

package com.example.linkdatabaseapplication;

import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.TextView;

public class MainActivity extends AppCompatActivity {

private View mBtnTest;
private View btnClean;
private TextView tvTestResult;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mBtnTest = findViewById(R.id.btnTestSql);
btnClean = findViewById(R.id.btnClean);
tvTestResult = findViewById(R.id.tvTestResult);
mBtnTest.setOnClickListener(getClickEvent());
btnClean.setOnClickListener(getClickEvent());
}

private View.OnClickListener getClickEvent() {
return new View.OnClickListener() {
@Override
public void onClick(View view) {
tvTestResult.setText("...");
if (view == mBtnTest) {
test();
}
}


};
}

private void test() {
Runnable run = new Runnable() {
@Override
public void run() {
String ret = DBUtil.QuerySQL();
Message msg = new Message();
msg.what=1001;
Bundle data = new Bundle();
data.putString("result", ret);
msg.setData(data);
mHandler.sendMessage(msg);
}
};
new Thread(run).start();
}

Handler mHandler = new Handler(){
public void handleMessage(android.os.Message msg) {
switch (msg.what)
{
case 1001:
String str = msg.getData().getString("result");
tvTestResult.setText(str);
break;

default:
break;
}
};
};
}

4、在AndroidManifest.xml 加上权限

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />

##运行结果：