今天要來提提SqliteDataBase的這個應用，他是一種本地資料庫如同Room、SharePreferences，只是當該應用清除資料或者是移除後，其資料也會跟著消失，那麼就開始今天的內容，今天不需要添加依賴及權限，算是蠻方便的應用。
首先建立一個class(SqlDBHelper)並使其繼承SQLOpenHelper，他會要你繼承方法，然後後面還要繼承他的constructor。
接著就設計需要的資料庫基底，主要都是透過String去下你的sqlite指令，然後在透過sqLiteDatabase.execSQL(sql)去做使用。
public class SqlDBHelper extends SQLiteOpenHelper {
private String table_name;
public SqlDBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version,String table_name) {
super(context, name, factory, version);
this.table_name=table_name;
}
//建立table，值包含自動累加Id以及一串字串
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String sql="CREATE TABLE IF NOT EXISTS "+table_name+"(" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT," +
"saveText TEXT" +
")";
sqLiteDatabase.execSQL(sql);
}
//當版本更新時
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
String sql = "DROP TABLE "+table_name;
sqLiteDatabase.execSQL(sql);
}
}
首先你先將資料庫進行初始化，
SqlDBHelper helper;
SQLiteDatabase db;
//初始化資料庫
helper = new SqlDBHelper(this, "SqlDB"
, null, 1, "textTable");
db = helper.getWritableDatabase();
之後便可開始進行增刪查改的動作，這幾個方法我到時會新增至MainActivity中，而除了查的這個方法沒有對應的方法外，其他的新增(insert)、改(update)、刪(delete)都有其對應的方法能直接做使用，而一樣也可以用sqlite的語法去寫：db.rawQuery(指令,null)。
//
//Sql_CRUD
public void createText(String text){
//按照你SqlDBHelper設定的屬性去添加
ContentValues values = new ContentValues();
values.put("saveText", text);
db.insert(table_name, null, values);
}
public ArrayList<Integer> readId(){
Cursor c = db.rawQuery("SELECT * FROM " + table_name,null);
ArrayList<Integer> idArray = new ArrayList<>();
while(c.moveToNext()){
//有id、text，id為0，text為1
Integer id = c.getInt(0);
idArray.add(id);
}
return idArray;
}
public ArrayList<String> readText(){
Cursor c = db.rawQuery("SELECT * FROM " + table_name,null);
ArrayList<String> textArray = new ArrayList<>();
while(c.moveToNext()){
//有id、text，id為0，text為1
String text = c.getString(1);
textArray.add(text);
}
return textArray;
}
public void updateText(String text,int position){
//按照你SqlDBHelper設定的屬性去修改
ContentValues values = new ContentValues();
values.put("saveText",text);
db.update(table_name,values,"_id = " + position,null);
}
public void deleteText(int position){
db.delete(table_name,"_id = " + position,null);
}
到時我會將結果顯示至RecyclerView上，那麼接著先進行設計RecyclerView調配器的部分。
可以看到他還有傳入一個interface，到時會用MainActivity實作"dataInterface"這個介面，並且我會新增兩個方法(當點選這個項目-onItemClick 以及 當點擊刪除按紐-onItemDelete)，接著附上Interface(dataInterface)及頁面(item_data.xml)。
public class DataAdapter extends RecyclerView.Adapter<DataAdapter.MyHolder> {
ArrayList<String> text;//宣告資料
dataInterface Interface;
public DataAdapter(ArrayList<String> text,dataInterface Interface){
//取得List內容
this.text=text;
this.Interface=Interface;
}
//將原本RecyclerView.ViewHolder的部分皆改為MyHolder
@Override
public MyHolder onCreateViewHolder(ViewGroup parent, int viewType) {
View view= LayoutInflater.from(parent.getContext()).inflate(R.layout.item_data,parent,false);
return new MyHolder(view);//連接布局，新增一個view給viewholder綁定元件
}
@Override
public void onBindViewHolder(MyHolder holder, int position) {
holder.txv_id.setText(""+(position+1));//position為索引值，用get來取得arraylist資料
holder.txv_text.setText(text.get(position));
//當點選刪除按紐
holder.btn_delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Interface.onItemDelete(position);
text.remove(position);
notifyDataSetChanged();
}
});
}
@Override
public int getItemCount() {
return text.size(); //回傳List大小
}
public class MyHolder extends RecyclerView.ViewHolder{
private TextView txv_id,txv_text;
private ImageButton btn_delete;
public MyHolder(View Holder){
super(Holder);
//當點選項目時(getAdapterPosition()為點擊的position)
Holder.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Interface.onItemClick(getAdapterPosition(),text.get(getAdapterPosition()));
// notifyItemRemoved(getAdapterPosition());
Log.d("AdapterPosition",""+getAdapterPosition());
}
});
//取得從onCreateViewHolder的view，此ViewHolder綁定主布局元件
txv_id=Holder.findViewById(R.id.itemId);
txv_text=Holder.findViewById(R.id.itemText);
btn_delete=Holder.findViewById(R.id.btn_delete);
}
}
}
public interface dataInterface {
void onItemClick(int id,String text);//當點擊項目時
void onItemDelete(int id);//當點擊項目的刪除按紐時
}
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:id="@+id/itemId"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="28dp"
android:text="" />
<TextView
android:id="@+id/itemText"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="7.7"
android:textSize="28dp"
android:text="" />
<ImageButton
android:id="@+id/btn_delete"
android:layout_width="wrap_content"
android:layout_height="match_parent"
android:layout_weight="0.3"
android:layout_gravity="center"
android:background="#00FF0000"
app:srcCompat="@android:drawable/ic_menu_delete" />
</LinearLayout>
接著最後便來設計需要的MainActivity!
public class MainActivity extends AppCompatActivity implements dataInterface{
private String db_name ="SqlDB";
private int version=1;
private String table_name="textTable";
private SqlDBHelper helper;
private SQLiteDatabase db;
private Button btn_send,btn_update;
private EditText et_text;
private TextView txv_show;
private int update_pos=-1;
RecyclerView recyclerView;
DataAdapter dataAdapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//初始化資料庫
helper = new SqlDBHelper(this, db_name
, null, version, table_name);
db = helper.getWritableDatabase();
et_text=findViewById(R.id.et_text);
txv_show=findViewById(R.id.txv_show);
recyclerView=findViewById(R.id.recyclerview);
setRecyclerView();
btn_send=findViewById(R.id.btn_send);
btn_send.setOnClickListener(view->{
String strText=et_text.getText().toString();
//判斷輸入不為空
if(!strText.equals("")){
createText(strText);
setRecyclerView();
}
});
btn_update=findViewById(R.id.btn_update);
btn_update.setOnClickListener(view->{
String strText=et_text.getText().toString();
//判斷輸入不為空
Log.d("Pos",""+update_pos);
if(!strText.equals("")&&update_pos!=-1){
Log.d("Pos","Runnnnnnnnn!");
updateText(strText,update_pos);
}
});
}
//Sql_CRUD
public void createText(String text){
ContentValues values = new ContentValues();
values.put("saveText", text);
db.insert(table_name, null, values);
}
public ArrayList<Integer> readId(){
Cursor c = db.rawQuery("SELECT * FROM " + table_name,null);
ArrayList<Integer> idArray = new ArrayList<>();
while(c.moveToNext()){
Integer id = c.getInt(0);
idArray.add(id);
}
return idArray;
}
public ArrayList<String> readText(){
Cursor c = db.rawQuery("SELECT * FROM " + table_name,null);
ArrayList<String> textArray = new ArrayList<>();
while(c.moveToNext()){
String text = c.getString(1);
textArray.add(text);
}
return textArray;
}
public void updateText(String text,int position){
ContentValues values = new ContentValues();
values.put("saveText",text);
db.update(table_name,values,"_id = " + readId().get(position),null);
setRecyclerView();
update_pos=-1;
}
public void deleteText(int position){
Log.d("DB_Delete",""+readId().get(position));
db.delete(table_name,"_id = " + readId().get(position),null);
dataAdapter.notifyItemRemoved(position);
}
public void setRecyclerView(){
if(readText()!=null&&readText().size()>0) {
dataAdapter = new DataAdapter(readText(),this);
recyclerView.setLayoutManager(new LinearLayoutManager(this));//使用LinearLayout布局
//分割線套件
recyclerView.addItemDecoration(new DividerItemDecoration(this,
DividerItemDecoration.VERTICAL));
recyclerView.setAdapter(dataAdapter);//將資料給recyclerView顯示
}
}
@Override
public void onItemClick(int id, String text) {
update_pos=id;
et_text.setText(text);
txv_show.setText("已選第"+(id+1)+"筆");
}
@Override
public void onItemDelete(int id) {
deleteText(id);
txv_show.setText("已刪除第"+(id+1)+"筆");
}
}
iThome鐵人賽
看影片追技術