《Android 学习笔记 之 SQLite数据库》

Android 学习笔记 之 SQLite数据库

任务需求：

• 使用 SQLiteOpenHelper类 创建数据库；
• 打开数据库，并设计初始表和初始数据；
• 用代码实现数据库的增加、删除、修改和查询操作。
• 效果图如下：
  

核心代码

package com.example.sy5;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class MyHelper extends SQLiteOpenHelper {
    public MyHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE information (_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),price INTEGER)");
        db.execSQL("insert into information (name,price) values('数据结构',45)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

package com.example.sy5;

import androidx.appcompat.app.AppCompatActivity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.LinearLayout;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

    SQLiteDatabase db ;
    Button[] buttons = new Button[8];
    String[] texts = { "" +
            "增加数据操作方式一",
            "增加数据操作方式二",
            "修改数据操作方式一",
            "修改数据操作方式二",
            "删除数据操作方式一",
            "删除数据操作方式二",
            "查询数据操作方式一",
            "查询数据操作方式二"
    };

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.my_layout);

        LinearLayout linearLayout = findViewById(R.id.linear);

        MyHelper helper=new MyHelper(this,"book.db",null,1);
        db=helper.getWritableDatabase();

        for (int i = 0; i < 8; i++) {
            buttons[i] = new Button(this);
            buttons[i].setText(texts[i]);
            buttons[i].setTag(i + 1);
            buttons[i].setLayoutParams(new LinearLayout.LayoutParams(ViewGroup.LayoutParams.WRAP_CONTENT, ViewGroup.LayoutParams.WRAP_CONTENT, 0));
            linearLayout.addView(buttons[i]);
            buttons[i].setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    int tag = (Integer) v.getTag();
                    switch (tag) {
                        case 1:
                            Log.i("log", "增加数据操作方式一！");
                            insert1();
                            Toast.makeText(MainActivity.this, "增加数据操作方式一！", Toast.LENGTH_SHORT).show();
                            break;
                        case 2:
                            Log.i("log", "增加数据操作方式二！");
                            insert2();
                            Toast.makeText(MainActivity.this, "增加数据操作方式二！", Toast.LENGTH_SHORT).show();
                            break;
                        case 3:
                            Log.i("log", "修改数据操作方式一！");
                            update1();
                            Toast.makeText(MainActivity.this, "修改数据操作方式一！", Toast.LENGTH_SHORT).show();
                            break;
                        case 4:
                            Log.i("log", "修改数据操作方式二！");
                            update2();
                            Toast.makeText(MainActivity.this, "修改数据操作方式二！", Toast.LENGTH_SHORT).show();
                            break;
                        case 5:
                            Log.i("log", "删除数据操作方式一！");
                            delete1();
                            Toast.makeText(MainActivity.this, "删除数据操作方式一！", Toast.LENGTH_SHORT).show();
                            break;
                        case 6:
                            Log.i("log", "删除数据操作方式二！");
                            delete2();
                            Toast.makeText(MainActivity.this, "删除数据操作方式二！", Toast.LENGTH_SHORT).show();
                            break;
                        case 7:
                            Log.i("log", "查询数据操作方式一！");
                            query1();
                            Toast.makeText(MainActivity.this, "查询数据操作方式一！", Toast.LENGTH_SHORT).show();
                            break;
                        case 8:
                            Log.i("log", "查询数据操作方式二！");
                            query2();
                            Toast.makeText(MainActivity.this, "查询数据操作方式二！", Toast.LENGTH_SHORT).show();
                            break;
                    }
                }
            });
        }

    }

    private void insert1(){
        db.execSQL("insert into information (name,price) values('Android程序设计1', 100)");
    }

    private void update1(){
        db.execSQL("update information set name='C++程序设计1' where  name = 'Android程序设计1'");
    }

    private void delete1(){
        db.execSQL("delete from information where name = 'C++程序设计1'");
    }

    private void query1(){
        Cursor c=db.query("information",null,null,null,null,null,null);
        String msg="";
        while(c.moveToNext()){
            String name=c.getString(c.getColumnIndex("name"));
            int price=c.getInt(c.getColumnIndex("price"));
            msg=msg + name + "," + price + "\n";
        }
        Log.i("****",msg);
    }

    private void insert2(){
        ContentValues cv=new ContentValues();
        cv.put("name","Android程序设计2");
        cv.put("price",33);
        db.insert("information",null,cv);
    }

    private void update2(){
        ContentValues cs=new ContentValues();
        cs.put("name", "C++程序设计2");
        db.update("information",cs,"name = ?",new String[]{"Android程序设计2"});
    }

    private void delete2(){
        db.delete("information", "name = ?", new String[]{"C++程序设计2"});
    }

    private void query2(){
        Cursor c = db.rawQuery("select * from information", null);
        String msg="";
        while(c.moveToNext()){
            String name=c.getString(c.getColumnIndex("name"));
            int price=c.getInt(c.getColumnIndex("price"));
            msg=msg + name + "," + price + "\n";
        }
        Log.i("****",msg);
    }
}

布局文件

日志查看

观察生成的文件位置