# 步驟
1. Andoid Studio 創建專案：mssql2
2. mssql2.java 程式碼中的 public Connection connectionclass() 修改資料庫相關資訊：
* ip：在電腦 cmd 中輸入 ipconfig 取得電腦 ip (IPv4 位址)。
* 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');
```
Sign in with Wallet
Connect another wallet