---
# System prepended metadata

title: Q3：SQLite03 改成連接 MS SQL Server

---

# 步驟
1. Andoid Studio 創建專案：mssql2

2. mssql2.java 程式碼中的 public Connection connectionclass() 修改資料庫相關資訊：
    * ip：在電腦 cmd 中輸入 ipconfig 取得電腦 ip (IPv4 位址)。
    ![image](https://hackmd.io/_uploads/SkKpPBhER.png)
    * port：不需修改。
    * username：可以改成自己的學號。
    * password：隨意，自己設定。
    * databasename：隨意，自己設定。

3. AndroidManifest.xml 程式碼中新增：
    ```javascript
    <uses-permission android:name="android.permission.INTERNET"/>
    ```
    
4. 開啟 SQL Server
    * 伺服器名稱：右邊箭頭 -> <瀏覽其他...> -> 資料庫引擎(左邊+) -> 選擇該電腦伺服器後確定。
    * 驗證：選擇 Windows 驗證。
    * 結束按下連線。

5. 安全性(左邊+) -> 登入(右鍵)：新增登入
    (Andoid Studio 中 mssql2.java 的資料庫相關資訊)
    * 登入名稱：username。
    * 選擇 SQL Server 驗證。
    * 密碼：password。
    * 取消勾選："強制執行密碼逾期"、"使用者必須在下次登入時變更密碼"。
    * 結束按下確定。

6. 資料庫(右鍵)：新增資料庫
    (Andoid Studio 中 mssql2.java 的資料庫相關資訊)
    *  資料庫名稱：databasename。
    *  擁有者：右邊 ... 瀏覽 -> 選擇 username 後確定。
    *  結束按下確定。

7. 資料庫 -> databasename(右鍵)：新增查詢
    *   貼上 SQL Server 程式碼。
    *   執行。

8. 執行 Andoid Studio 成功後就大功告成!!!


:::success
期末雲端資料夾：
https://drive.google.com/drive/folders/1_rgG-lRthMWN_Q3UNJuMZk2t9kH4pNdi?usp=sharing
:::

## activity_mssql2.xml

:::spoiler 新版-自己電腦
```javascript
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".MainActivity">

    <LinearLayout
        android:id="@+id/linearLayout"
        android:layout_width="0dp"
        android:layout_height="76dp"
        android:orientation="horizontal"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="1.0"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.43">

        <Button
            android:id="@+id/btnAdd"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="ADD" />

        <Button
            android:id="@+id/btnUpdate"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="UPDATE" />

        <Button
            android:id="@+id/btnDelete"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="DELETE" />

        <Button
            android:id="@+id/btnGet"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="GET" />
    </LinearLayout>

    <EditText
        android:id="@+id/editText_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="number"
        android:textSize="20sp"
        android:textStyle="bold"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.742"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.109" />

    <TextView
        android:id="@+id/textView_price"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Price："
        android:textSize="20sp"
        android:textStyle="bold"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.159"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.309" />

    <TextView
        android:id="@+id/textView_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="ID："
        android:textSize="20sp"
        android:textStyle="bold"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.223"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.118" />

    <EditText
        android:id="@+id/editText_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="text"
        android:textSize="20sp"
        android:textStyle="bold"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="@+id/editText_id"
        app:layout_constraintHorizontal_bias="0.0"
        app:layout_constraintStart_toStartOf="@+id/editText_id"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.21" />

    <TextView
        android:id="@+id/textView_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Name："
        android:textSize="20sp"
        android:textStyle="bold"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.142"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.219" />

    <EditText
        android:id="@+id/editText_price"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="number"
        android:textSize="20sp"
        android:textStyle="bold"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="@+id/editText_name"
        app:layout_constraintHorizontal_bias="0.0"
        app:layout_constraintStart_toStartOf="@+id/editText_name"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.306" />

    <TextView
        android:id="@+id/textView"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.498"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.587" />

</androidx.constraintlayout.widget.ConstraintLayout>
```
:::

### 舊版-學校電腦
```javascript
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_mssql2"
    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="com.example.isu.mssql2.mssql2">

    <LinearLayout
        android:orientation="vertical"
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <LinearLayout
            android:orientation="horizontal"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <TextView
                android:text="ID"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:id="@+id/id_label"
                android:layout_weight="1.63"
                tools:text="ID"
                android:textColor="@android:color/holo_orange_dark"
                android:textSize="24sp"
                android:textAlignment="center" />

            <EditText
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:inputType="number"
                android:ems="10"
                android:id="@+id/id_intertxt"
                android:layout_weight="1" />
        </LinearLayout>

        <LinearLayout
            android:orientation="horizontal"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <TextView
                android:text="Name"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:id="@+id/name_label"
                android:layout_weight="0.63"
                android:textColor="@android:color/holo_orange_dark"
                android:textSize="24sp"
                android:textAlignment="center" />

            <EditText
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:inputType="textMultiLine"
                android:ems="10"
                android:id="@+id/name_intertxt"
                android:layout_weight="1" />
        </LinearLayout>

        <LinearLayout
            android:orientation="horizontal"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <TextView
                android:text="Price"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:id="@+id/price_label"
                android:layout_weight="0.87"
                android:textSize="24sp"
                android:textColor="@android:color/holo_orange_dark"
                android:textAlignment="center" />

            <EditText
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:inputType="number"
                android:ems="10"
                android:id="@+id/price_intertxt"
                android:layout_weight="1" />
        </LinearLayout>

        <LinearLayout
            android:orientation="horizontal"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <Button
                android:text="ADD"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:id="@+id/add_btn"
                android:textColorLink="@android:color/holo_orange_light"
                android:backgroundTint="@android:color/holo_orange_light" />

            <Button
                android:text="UPDATE"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:id="@+id/update_btn"
                android:backgroundTint="@android:color/holo_orange_light" />

            <Button
                android:text="DELETE"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:id="@+id/delete_btn"
                android:backgroundTint="@android:color/holo_orange_light" />

            <Button
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:id="@+id/get_btn"
                android:layout_weight="1"
                android:text="GET"
                android:textColorLink="@android:color/holo_orange_light"
                android:backgroundTint="@android:color/holo_orange_light" />
        </LinearLayout>

        <TableRow
            android:layout_width="match_parent"
            android:layout_height="match_parent" >

            <TextView
                android:layout_width="match_parent"
                android:layout_height="match_parent"
                android:id="@+id/textView4" />
        </TableRow>
    </LinearLayout>
</RelativeLayout>
```


---
## mssql2.java

:::spoiler 新版-自己電腦
```javascript
import androidx.appcompat.app.AppCompatActivity;
import android.annotation.SuppressLint;
import android.os.Bundle;
import android.os.StrictMode;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MainActivity extends AppCompatActivity {
    Button btnAdd, btnUpdate, btnDelete, btnGet;
    EditText editText_id, editText_name, editText_price;
    TextView textView;

    @SuppressLint("MissingInflatedId")
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        editText_id = findViewById(R.id.editText_id);
        editText_name = findViewById(R.id.editText_name);
        editText_price = findViewById(R.id.editText_price);
        textView = findViewById(R.id.textView);
        btnAdd = findViewById(R.id.btnAdd);
        btnUpdate = findViewById(R.id.btnUpdate);
        btnDelete = findViewById(R.id.btnDelete);
        btnGet = findViewById(R.id.btnGet);

        btnAdd.setOnClickListener(btnAddListener);
        btnUpdate.setOnClickListener(btnUpdateListener);
        btnDelete.setOnClickListener(btnDeleteListener);
        btnGet.setOnClickListener(btnGetListener);
    }

    private int getId() {
        String idStr = editText_id.getText().toString();
        return idStr.isEmpty() ? -1 : Integer.parseInt(idStr);
    }

    private int getPrice() {
        String priceStr = editText_price.getText().toString();
        return priceStr.isEmpty() ? -1 : Integer.parseInt(priceStr);
    }

    private String getName() {
        return editText_name.getText().toString();
    }

    private Button.OnClickListener btnAddListener = new Button.OnClickListener() {
        @SuppressLint("NonConstantResourceId")
        public void onClick(View v) {
            int id = getId();
            String name = getName();
            int price = getPrice();

            if (id == -1 || name.isEmpty() || price == -1) {
                Toast.makeText(getApplicationContext(), "請輸入所有必要資訊", Toast.LENGTH_SHORT).show();
                return;
            }

            Connection connection = connectionclass();
            try {
                if (connection != null) {
                    String sqlinsert = "Insert into fruit values ('" + id + "','" + name + "','" + price + "')";
                    Statement st = connection.createStatement();
                    st.executeUpdate(sqlinsert);
                    Toast.makeText(getApplicationContext(), "新增成功", Toast.LENGTH_SHORT).show();
                }
            } catch (Exception exception) {
                Log.e("Error", exception.getMessage());
            }
        }
    };

    private Button.OnClickListener btnUpdateListener = new Button.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = getId();
            String name = getName();
            int price = getPrice();

            if (id == -1 || name.isEmpty() || price == -1) {
                Toast.makeText(getApplicationContext(), "請輸入所有必要資訊", Toast.LENGTH_SHORT).show();
                return;
            }

            Connection connection = connectionclass();
            try {
                if (connection != null) {
                    String sqlupdate = "update fruit set name='" + name + "', price='" + price + "' where id= '" + id + "'";
                    Statement st = connection.createStatement();
                    st.executeUpdate(sqlupdate);
                    Toast.makeText(getApplicationContext(), "更新成功", Toast.LENGTH_SHORT).show();
                }
            } catch (Exception exception) {
                Log.e("Error", exception.getMessage());
            }
        }
    };

    private Button.OnClickListener btnDeleteListener = new Button.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = getId();

            if (id == -1) {
                Toast.makeText(getApplicationContext(), "請輸入 ID", Toast.LENGTH_SHORT).show();
                return;
            }

            Connection connection = connectionclass();
            try {
                if (connection != null) {
                    String sqldelete = "delete from fruit where id= '" + id + "'";
                    Statement st = connection.createStatement();
                    st.executeUpdate(sqldelete);
                    Toast.makeText(getApplicationContext(), "刪除成功", Toast.LENGTH_SHORT).show();
                }
            } catch (Exception exception) {
                Log.e("Error", exception.getMessage());
            }
        }
    };

    private Button.OnClickListener btnGetListener = new Button.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = getId();
            Connection connection = connectionclass();
            try {
                if (connection != null && id != -1) {
                    String sqlget = "select * from fruit where id= '" + id + "'";
                    Statement st = connection.createStatement();
                    ResultSet rs = st.executeQuery(sqlget);
                    if (rs.next()) {
                        editText_name.setText(rs.getString("name"));
                        editText_price.setText(rs.getString("price"));
                    }
                }
            } catch (Exception exception) {
                Log.e("Error", exception.getMessage());
            }
            try {
                if (connection != null) {
                    String sqlgetAll = "select * from fruit";
                    Statement st = connection.createStatement();
                    ResultSet rs = st.executeQuery(sqlgetAll);
                    StringBuilder sb = new StringBuilder();
                    while (rs.next()) {
                        String recordId = rs.getString("id");
                        String recordName = rs.getString("name");
                        String recordPrice = rs.getString("price");
                        sb.append(recordId).append("     ").append(recordName).append("     ").append(recordPrice).append("\n");
                    }
                    textView.setText(sb.toString());
                }
            } catch (Exception exception) {
                Log.e("Error", exception.getMessage());
            }
        }
    };

    @SuppressLint("NewApi")
    public Connection connectionclass() {
        Connection con = null;
        String ip = "10.0.2.2", port = "1433", username = "user1", password = "123", databasename = "fruitDB";
        StrictMode.ThreadPolicy tp = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(tp);
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            String connectionUrl = "jdbc:jtds:sqlserver://" + ip + ":" + port + ";databasename=" + databasename + ";User=" + username + ";password=" + password + ";";
            con = DriverManager.getConnection(connectionUrl);
        } catch (Exception exception) {
            Log.e("Error", exception.getMessage());
        }
        return con;
    }
}
```

:::

### 舊版-學校電腦
```javascript=
import android.annotation.SuppressLint;
import android.os.StrictMode;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;


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

public class mssql2 extends AppCompatActivity {
    /* 自建的資料庫類別 */


	/* 資料表欄位 */
    //private final static String	_ID	= "_id";
    //private final static String	NAME = "name";
    //private final static String	PRICE = "price";

    Button add_btn,update_btn,delete_btn,get_btn;
    EditText id_intertxt,name_intertxt,price_intertxt;
    TextView textView4;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_mssql2);

        // 取得元件
        id_intertxt=(EditText)findViewById(R.id.id_intertxt);
        name_intertxt=(EditText)findViewById(R.id.name_intertxt);
        price_intertxt=(EditText)findViewById(R.id.price_intertxt);
        add_btn=(Button)findViewById(R.id.add_btn);
        update_btn=(Button)findViewById(R.id.update_btn);
        delete_btn=(Button)findViewById(R.id.delete_btn);
        get_btn=(Button)findViewById(R.id.get_btn);
        textView4 = (TextView) findViewById(R.id.textView4);

        // 設定偵聽
        add_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Connection connection = connectionclass();
                try {
                    if (connection != null) {
                        String sqlinsert = "Insert into Table1 values ('" + id_intertxt.getText().toString() + "','" + name_intertxt.getText().toString() + "','" + price_intertxt.getText().toString() + "')";
                        Statement st = connection.createStatement();
                        ResultSet rs = st.executeQuery(sqlinsert);
                        Toast.makeText(getApplicationContext(), "新增成功", Toast.LENGTH_SHORT).show();
                    }
                } catch (Exception exception) {
                    Log.e("Error", exception.getMessage());
                }
            }
        });

        update_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Connection connection = connectionclass();
                try {
                    if (connection != null) {
                        String sqlupdate = "update Table1 set id='" + id_intertxt.getText().toString() + "',name='" + name_intertxt.getText().toString() + "',price='" + price_intertxt.getText().toString() + "' where id= '"+ id_intertxt.getText().toString()+"'";
                        Statement st = connection.createStatement();
                        int rows = st.executeUpdate(sqlupdate);
                        Toast.makeText(getApplicationContext(), "更新成功", Toast.LENGTH_SHORT).show();
                    }
                } catch (Exception exception) {
                    Log.e("Error", exception.getMessage());
                }
            }
        });

        delete_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Connection connection = connectionclass();
                try {
                    if (connection != null) {
                        String sqldelete = "delete Table1  where id= '"+ id_intertxt.getText().toString()+"'";
                        Statement st = connection.createStatement();
                        int rows = st.executeUpdate(sqldelete);
                        Toast.makeText(getApplicationContext(), "刪除成功", Toast.LENGTH_SHORT).show();
                    }
                } catch (Exception exception) {
                    Log.e("Error", exception.getMessage());
                }
            }
        });

        get_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Connection connection = connectionclass();
                try {
                    if (connection != null) {
                        String sqlget = "select * from Table1 where id= '"+ id_intertxt.getText().toString()+"'";
                        Statement st = connection.createStatement();
                        ResultSet rs = st.executeQuery(sqlget);
                        while(rs.next()){
                            id_intertxt.setText(rs.getString(1));
                            name_intertxt.setText(rs.getString(2));
                            price_intertxt.setText(rs.getString(3));


                        }

                    }
                } catch (Exception exception) {
                    Log.e("Error", exception.getMessage());
                }
                try {
                    if (connection != null) {
                        String sqlget = "select * from Table1 ";
                        Statement st = connection.createStatement();
                        ResultSet rs = st.executeQuery(sqlget);
                        StringBuilder sb = new StringBuilder();
                        while(rs.next()){

                            String id = rs.getString("id");

                            String name = rs.getString("name");

                            String price = rs.getString("price");
                            // 在 Logcat 中顯示取得的資料
                            sb.append(id+"     ").append(name+"     ").append(price).append("\n");

                        }textView4.setText(sb.toString());

                    }
                } catch (Exception exception) {
                    Log.e("Error", exception.getMessage());
                }
            }
        });
    }
    @SuppressLint("NewApi")
    public Connection connectionclass() {
        Connection con = null;
        
        // 資料庫相關資訊需要修改部分!!
        String ip = "10.10.10.25", port = "1433", username = "11003018a", password = "12345", databasename = "db1";
        
        StrictMode.ThreadPolicy tp = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(tp);
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            String connectionUrl = "jdbc:jtds:sqlserver://" + ip + ":" + port + ";databasename=" + databasename + ";User=" + username + ";password=" + password + ";";
            con = DriverManager.getConnection(connectionUrl);
        } catch (Exception exception) {
            Log.e("Error", exception.getMessage());
        }
        return con;
    }
}

```

## AndroidManifest.xml
```javascript
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.isu.mssql2">
    <uses-permission android:name="android.permission.INTERNET"/>  // 新增這行指令
    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".mssql2">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>
</manifest>
```

## SQL Server
```javascript
CREATE TABLE Table1 
(id nvarchar(100), 
 name nvarchar(100), 
 price nvarchar(100));
INSERT INTO Table1 VALUES
('1', 'banana', '30'), 
('2', 'watermelon', '120'), 
('3', 'pear', '250'), 
('4', 'peach', '280');
```
