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


<<:  DAY 23 Big Data 5Vs – Variety(速度) Kinesis (3)

>>:  Terraform

Day13 - 提升收入的方法

接案不是创业，它只是另外一种领薪水的工作方式，所以没有办法一夕致富，纵使今天谈到了金额百万或千万的案...

DAY 16 『 改用 xib 进行界面创作 』

storyboard 有三个概念是最容易混淆： xib：实际是个xml文件，xib = XML n...

Day13: GuardDuty结果汇出至S3、发送告警Email设定

如何把GuardDuty找到的结果汇出到S3储存 GuardDuty找到的结果会保留90天，如果想要...

[Day 22] Facial Recognition: Google FaceNet

Google在2015年时发表了一篇论文， 提出了FaceNet网路架构。 而其实在前面几天实作人脸...

Video播放器 PyQt5 QMediaPlayer

Python PyQt5 QMediaPlayer Video播放器 前言： 写个Python GU...