SQLite是一套開放原始碼的資料函式庫，使用標準的SQL語法，提供無須連線的資料庫的管理系統。
二大優點:
(1) 小而美,該資料庫系統非常小(大約 275k 大小空間),屬於輕量級的資料庫系統,主要是由 C 寫
成,支援大部份標準 SQL 語法,。
(2) 省成本:創建者不僅將其原始碼公開,還無須支付任何費用
SQLite 與其他 Server 等級的資料庫最大的差異點在於,SQLite 直接將資料庫的資料儲存在本機
端,而非 Server 端,而且一個資料庫即儲存成一個檔案。
建立 SQLite 資料庫，必須建立子類別
繼承 SQLiteOperHelper 並覆寫
onCreate() 與
onUpgrade(),尤以改寫onCreate() 為重要。
程式碼：
activity_main.xml：
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
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"
android:orientation="vertical"
tools:context="tw.com.hjchen.sqlitedemo.MainActivity">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Name"
android:textSize="20sp"/>
<EditText
android:id="@+id/editName"
android:layout_width="wrap_content"
android:layout_height="40dp"
android:width="230dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Tel"
android:textSize="20sp"/>
<EditText
android:id="@+id/editTel"
android:layout_width="wrap_content"
android:layout_height="40dp"
android:width="230dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Email"
android:textSize="20sp"/>
<EditText
android:id="@+id/editEmail"
android:layout_width="wrap_content"
android:layout_height="40dp"
android:width="230dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="ID"
android:textSize="20sp"/>
<EditText
android:id="@+id/editID"
android:layout_width="wrap_content"
android:layout_height="40dp"
android:width="230dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center_horizontal">
<Button
android:id="@+id/btnAdd"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Add"
android:width="100dp"/>
<Button
android:id="@+id/btnUpdate"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Update"
android:width="100dp"/>
<Button
android:id="@+id/btnDel"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Del"
android:width="100dp"/>
</LinearLayout>
<ScrollView
android:id="@+id/scrollView1"
android:layout_width="match_parent"
android:layout_height="120dp">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent">
<TextView
android:id="@+id/txtResult"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Result"
android:textSize="20sp"/>
</LinearLayout>
</ScrollView>
<ListView
android:id="@+id/listData"
android:layout_width="match_parent"
android:layout_height="match_parent"></ListView>
</LinearLayout>
data_item.xml：(listview裡的item)
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<TextView
android:id="@+id/txtId"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="20sp"/>
<TextView
android:id="@+id/txtName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="20sp"/>
<TextView
android:id="@+id/txtTel"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="20sp"/>
<TextView
android:id="@+id/txtEmail"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="20sp"/>
</LinearLayout>
DbConstants.java：
import android.provider.BaseColumns;
public interface DbConstants extends BaseColumns{
public static final String TABLE_NAME="friends";
public static final String NAME="name";
public static final String TEL="tel";
public static final String EMAIL="email";
}
DBHelper.java：
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import static android.provider.BaseColumns._ID;
import static tw.com.hjchen.sqlitedemo.DbConstants.TABLE_NAME;
import static tw.com.hjchen.sqlitedemo.DbConstants.NAME;
import static tw.com.hjchen.sqlitedemo.DbConstants.TEL;
import static tw.com.hjchen.sqlitedemo.DbConstants.EMAIL;
public class DBHelper extends SQLiteOpenHelper{
private final static String DATABASE_NAME="demo.db"; //資料庫檔案名稱
private final static int DATABASE_VERSION=1; //資料庫版本
public DBHelper(Context context){
super(context,DATABASE_NAME,null,DATABASE_VERSION);
}
public void onCreate(SQLiteDatabase db){
final String INIT_TABLE="create table "+TABLE_NAME+"("+_ID+" integer primary key autoincrement,"+NAME+" char,"+TEL+" char,"+EMAIL+" char)";
db.execSQL(INIT_TABLE);
}
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
final String DROP_TABLE="drop tabel if exists "+TABLE_NAME;
db.execSQL(DROP_TABLE);
onCreate(db);
}
}
MainActivity.java：
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import static android.provider.BaseColumns._ID;
import static tw.com.hjchen.sqlitedemo.DbConstants.TABLE_NAME;
import static tw.com.hjchen.sqlitedemo.DbConstants.NAME;
import static tw.com.hjchen.sqlitedemo.DbConstants.TEL;
import static tw.com.hjchen.sqlitedemo.DbConstants.EMAIL;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.*;
public class MainActivity extends AppCompatActivity implements OnClickListener{
private DBHelper dbHelper;
private TextView result;
private ListView listData;
private EditText editName,editTel,editEmail,editId;
private Button btnAdd,btnDel,btnUpdatel;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViews();
openDatabase(); //開啟資料庫
show(); //show scrollview
showInList(); //show listview
}
private void findViews(){
editName=(EditText)findViewById(R.id.editName);
editTel=(EditText)findViewById(R.id.editTel);
editEmail=(EditText)findViewById(R.id.editEmail);
editId=(EditText)findViewById(R.id.editID);
btnAdd=(Button)findViewById(R.id.btnAdd);
btnDel=(Button)findViewById(R.id.btnDel);
btnUpdatel=(Button)findViewById(R.id.btnUpdate);
result=(TextView)findViewById(R.id.txtResult);
listData=(ListView)findViewById(R.id.listData);
btnAdd.setOnClickListener(this);
btnDel.setOnClickListener(this);
btnUpdatel.setOnClickListener(this);
}
protected void onDestroy(){
super.onDestroy();
closeDatabase(); //關閉資料庫
}
private void openDatabase(){
dbHelper=new DBHelper(this); //取得DBHelper物件
}
private void closeDatabase(){
dbHelper.close();
}
public void onClick(View view){
switch (view.getId()){
case R.id.btnAdd:
add();
break;
case R.id.btnDel:
del();
break;
case R.id.btnUpdate:
update();
break;
default:
break;
}
show();
showInList();
}
private void add(){
SQLiteDatabase db=dbHelper.getWritableDatabase(); //透過dbHelper取得讀取資料庫的SQLiteDatabase物件，可用在新增、修改與刪除
ContentValues values=new ContentValues(); //建立 ContentValues 物件並呼叫 put(key,value) 儲存欲新增的資料，key 為欄位名稱 value 為對應值。
values.put(NAME,editName.getText().toString());
values.put(TEL,editTel.getText().toString());
values.put(EMAIL,editEmail.getText().toString());
db.insert(TABLE_NAME,null,values);
cleanEditText();
}
private void del(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
String id=editId.getText().toString();
db.delete(TABLE_NAME,_ID+"="+id,null);
cleanEditText();
}
private void update(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
String id=editId.getText().toString();
ContentValues values=new ContentValues();
values.put(NAME,editName.getText().toString());
values.put(TEL,editTel.getText().toString());
values.put(EMAIL,editEmail.getText().toString());
db.update(TABLE_NAME,values,_ID+"="+id,null);
cleanEditText();
}
private void cleanEditText(){
editName.setText("");
editTel.setText("");
editEmail.setText("");
editId.setText("");
}
private Cursor getCursor(){
SQLiteDatabase db=dbHelper.getReadableDatabase(); //透過dbHelper取得讀取資料庫的SQLiteDatabase物件，可用在查詢
String[] columns={_ID,NAME,TEL,EMAIL};
Cursor cursor = db.query(TABLE_NAME,columns,null,null,null,null,null); //查詢所有欄位的資料
return cursor;
}
private void show(){
Cursor cursor = getCursor(); //取得查詢物件Cursor
StringBuilder resultData = new StringBuilder("Result:\n");
while (cursor.moveToNext()){
int id = cursor.getInt(0);
String name = cursor.getString(1);
String tel = cursor.getString(2);
String email = cursor.getString(3);
resultData.append(id).append(": ");
resultData.append(name).append(": ");
resultData.append(tel).append(": ");
resultData.append(email).append("\n");
}
result.setText(resultData);
}
private void showInList(){
Cursor cursor = getCursor();
String[] from = {_ID,NAME,TEL,EMAIL};
int[] to = {R.id.txtId,R.id.txtName,R.id.txtTel,R.id.txtEmail};
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,R.layout.data_item,cursor,from,to); //SimpleCursorAdapter(Context context, int layout, Cursor c, String[] from, int[] to)
listData.setAdapter(adapter);
}
}
PS：這裡特別說明一下SQLite的查詢功能，SQLiteDatabase類別提供二種查詢功能方法：rawQuery() 與 query()
rawQuery():接受 SQL 查詢語法,可直接將 SQL 查詢語法傳入即可。
query():必須先將 SQL 查詢語法依照 query()的參數來切割。
例:SQL 查詢語法為:Select name,phone from member where name like ?
- rawQuery(String sql, String[] selectionArgs)
查詢語法為:
String sql = " Select name,phone from member where name like ? "
String[] selectionArgs = {"%明%"};
Cursor cursor = db.rawQuery(sql,selectionArgs);
- query(String table, String[] columns, String selection, String[] selectionArgs,String groupBy,String having, String orderBy);
查詢語法為:
String[] columns = {"name","phone"};
String selection = "name Like ? ";
String[] selectionArgs = {"%明%"};
Cursor cursor = db.query(TABLE_NAME , columns, selection, selectionArgs, null, null, null);
