iT邦幫忙

2021 iThome 鐵人賽

DAY 20
0
Mobile Development

android studio 30天學習筆記系列 第 20

android studio 30天學習筆記-day 20-SQLlite

SQLlite是android內建的資料庫,可以做資料儲存、更改、查詢等操作。

建立一個SqlDataBaseHelper.java

此class的是建立、更新資料表與資料表各個欄位。
資料表:Users
欄位:

  1. _id=整數類(主鍵)
  2. user=文字類,(not null 不為空值)
  3. salary文字類,(not null 不為空值)
public class SqlDataBaseHelper extends SQLiteOpenHelper {
    //資料庫名稱
    private static final String DataBaseName = "DBIt";
    //資料庫版本
    private static final int DataBaseVersion = 1;
    
    public SqlDataBaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version,String TableName) {
        super(context, DataBaseName, factory, DataBaseVersion);//將這裡第二個參數name改成DataBaseName,第四個參數version改成DataBaseVersion。
    }

    @Override
    public void onCreate(SQLiteDatabase db) { //建立資料表
        String SqlTable = "CREATE TABLE IF NOT EXISTS Users (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "user text not null," +
                "salary text not null" +
                ")";
        db.execSQL(SqlTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//於資料庫版本更新時才會執行
        final String SQL = "DROP TABLE Users";
        db.execSQL(SQL);
    }
}

建立ShowDataAdapter

public class ShowDataAdapter extends RecyclerView.Adapter<ShowDataAdapter.ViewHolder> {
    private ArrayList<HashMap<String,String>> dataList;
    private LayoutInflater inflater;


    ShowDataAdapter(Context context, ArrayList<HashMap<String,String>> dataList){
        this.inflater=LayoutInflater.from(context);
        this.dataList=dataList;
    }
    @NonNull
    @Override
    public ShowDataAdapter.ViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {//綁定要顯示的layout
        View view= inflater.inflate(R.layout.sql_data,parent,false);
        return new ShowDataAdapter.ViewHolder(view);
    }
    
    @Override
    public void onBindViewHolder(@NonNull ShowDataAdapter.ViewHolder holder, final int position) {//顯示資料
        holder.tvID.setText(dataList.get(position).get("id"));
        holder.tvName.setText(dataList.get(position).get("userID"));
        holder.tvSalary.setText(dataList.get(position).get("price"));
    }

    @Override
    public int getItemCount() { //取得資料數量
        return dataList.size();
    }

    public class ViewHolder extends RecyclerView.ViewHolder {//綁定使用元件

        TextView tvID,tvName,tvSalary;
        public ViewHolder(@NonNull View itemView) {
            super(itemView);
            tvID=itemView.findViewById(R.id.tvID);
            tvName=itemView.findViewById(R.id.tvName);
            tvSalary=itemView.findViewById(R.id.tvSalary);
        }
    }
}

MainActivity

開啟資料庫

public class MainActivity extends AppCompatActivity {
    private static final String DataBaseName = "DBIt";//資料庫名稱
    private static final int DataBaseVersion = 1; //資料庫版本
    private static String DataBaseTable = "Users"; //資料表名稱
    private static SQLiteDatabase db; 
    private SqlDataBaseHelper sqlDataBaseHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        sqlDataBaseHelper = new SqlDataBaseHelper(this,DataBaseName,null,DataBaseVersion,DataBaseTable);
       
    }
 } 
   

新增資料

使用ContentValues放入要新增之欄位的資料,db.insert將資料新增到資料庫

public void AddList(View view) {
        String user =name.getText().toString();
        String userSalary =salary.getText().toString();
        
        if (!user.isEmpty() ||!userSalary.isEmpty()) {
            ContentValues contentValues = new ContentValues();
            contentValues.put("user", user);
            contentValues.put("salary", userSalary);
            db.insert(DataBaseTable, null, contentValues);
            Toast.makeText(this,"新增完成",Toast.LENGTH_SHORT).show();
        }
        else{
            Toast.makeText(this,"請填寫資料",Toast.LENGTH_SHORT).show();
        }
    }

更改資料

使用ContentValues放入要更改之欄位的資料,db.update更改資料表的資料

public void UpdateData(View view) {
        String user =name.getText().toString();
        String userSalary =salary.getText().toString();
        if (!user.isEmpty() ||!userSalary.isEmpty()) {
            ContentValues contentValues = new ContentValues();
            contentValues.put("user", user);
            contentValues.put("salary", userSalary);
            db.update(DataBaseTable, contentValues, "_id =" + 1,null );
            Toast.makeText(this,"更新完成",Toast.LENGTH_SHORT).show();
        }
    }

查詢資料

使用db.rawQuery()取得資料,資料表第0欄為id,第1欄為userID,第2欄為price

public ArrayList<HashMap<String,String>> showData(){
        db = sqlDataBaseHelper.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT * FROM  "+DataBaseTable , null);
        ArrayList<HashMap<String, String>> dataList2 = new ArrayList<>();
        while (c.moveToNext()) {  //迴圈 Cursor 取出需要的資料

            HashMap<String, String> map = new HashMap<>();//回傳從資料表取得的內容
            map.put("id", c.getString(0));
            map.put("userID", c.getString(1));
            map.put("price", c.getString(2));
        dataList2.add(map);

        }
        c.close(); //結束,關閉Cursor
        return dataList2;
    }

刪除資料

取得可讀寫資料庫,db.delete刪除id為1的資料

public void Delete(View view) {
        SQLiteDatabase db = sqlDataBaseHelper.getWritableDatabase();
        db.delete("Users","_id = " + 1,null);
    }

完整程式碼

public class MainActivity extends AppCompatActivity {
    private static final String DataBaseName = "DBIt"; /資料庫名稱
    private static final int DataBaseVersion = 1; //資料庫版本
    private static String DataBaseTable = "Users"; //資料表名稱
    private static SQLiteDatabase db; 
    private SqlDataBaseHelper sqlDataBaseHelper;
    private ShowDataAdapter adapter;
    private ArrayList<HashMap<String,String>> dataList=new ArrayList<>(); //顯示資料表的資料

    private EditText name,salary;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        sqlDataBaseHelper = new SqlDataBaseHelper(this,DataBaseName,null,DataBaseVersion,DataBaseTable);
        db = sqlDataBaseHelper.getWritableDatabase(); // 取得可讀寫的資料庫
        init();
        dataList=showData();//取得資料表並顯示
        recycle();
        Log.d("addData","data:"+showData());
    }

    private void recycle() {
        RecyclerView recyclerView=findViewById(R.id.recyclerView);
        recyclerView.addItemDecoration(new DividerItemDecoration(this, DividerItemDecoration.VERTICAL));
        recyclerView.setLayoutManager(new LinearLayoutManager(this));
        adapter=new ShowDataAdapter(this,dataList);
        recyclerView.setAdapter(adapter);
    }

    public void UpdateData(View view) { //更新按鈕
        String user =name.getText().toString();
        String userSalary =salary.getText().toString();
        if (!user.isEmpty() ||!userSalary.isEmpty()) {
            ContentValues contentValues = new ContentValues();
            contentValues.put("user", user);
            contentValues.put("salary", userSalary);
            db.update(DataBaseTable, contentValues, "_id =" + 1,null );
            Toast.makeText(this,"更新完成",Toast.LENGTH_SHORT).show();
            dataList=showData();
        }
    }

    public void AddList(View view) { //新增按鈕
        String user =name.getText().toString();
        String userSalary =salary.getText().toString();
        if (!user.isEmpty() ||!userSalary.isEmpty()) {
            ContentValues contentValues = new ContentValues();
            contentValues.put("user", user);
            contentValues.put("salary", userSalary);
            db.insert(DataBaseTable, null, contentValues);
            Toast.makeText(this,"新增完成",Toast.LENGTH_SHORT).show();
            dataList=showData();
        }
        else{
            Toast.makeText(this,"請填寫資料",Toast.LENGTH_SHORT).show();
        }
    }

    public void Search(View view) { //查詢按鈕
        dataList=showData();
        recycle();
    }
    private void init() {
        name=findViewById(R.id.name);
        salary=findViewById(R.id.salary);
    }
    public ArrayList<HashMap<String,String>> showData(){ //查詢資料表資料
        db = sqlDataBaseHelper.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT * FROM  "+DataBaseTable , null);
        ArrayList<HashMap<String, String>> dataList2 = new ArrayList<>();
        while (c.moveToNext()) {   //迴圈 Cursor 取出需要的資料
            HashMap<String, String> map = new HashMap<>();
            map.put("id", c.getString(0));
            map.put("userID", c.getString(1));
            map.put("price", c.getString(2));
        dataList2.add(map);

        }
        c.close(); //結束,關閉Cursor
        return dataList2;
    }

    public void Delete(View view) { //刪除按鈕
        SQLiteDatabase db = sqlDataBaseHelper.getWritableDatabase();
        db.delete("Users","_id = " + 1,null);
    }
}

用log回傳資料庫
https://ithelp.ithome.com.tw/upload/images/20210831/20138966Xefe3ZjYHc.png
https://ithelp.ithome.com.tw/upload/images/20210831/20138966eMdCJ7v9xN.png
成果:

https://ithelp.ithome.com.tw/upload/images/20210831/20138966zYAER9sVav.jpg


上一篇
30天學習筆記 -day 19-viewpager動畫(PageTransformer )
下一篇
android studio 30天學習筆記-day 21 -獲得日期
系列文
android studio 30天學習筆記30

尚未有邦友留言

立即登入留言