Android SQLite数据库

最新推荐文章于 2024-04-04 14:25:56 发布

gdcpzk

最新推荐文章于 2024-04-04 14:25:56 发布

阅读量703

点赞数

本文链接：https://blog.csdn.net/zksdu/article/details/117013321

版权

文章目录

- Android SQLite数据库

Android SQLite数据库

布局文件

<?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"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity"
    android:orientation="vertical">

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="创建/打开数据库"
        android:id="@+id/btn_1"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="创建数据表"
        android:id="@+id/btn_2"/>


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="插入数据"
        android:id="@+id/btn_3"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询数据"
        android:id="@+id/btn_4"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除数据"
        android:id="@+id/btn_5"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="更新数据"
        android:id="@+id/btn_6"/>

    <TextView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询结果"
        android:id="@+id/tv_result"/>

</LinearLayout>

主界面代码

package com.example.demo;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

import java.io.File;

public class MainActivity extends AppCompatActivity {
    private Button btn1;
    private Button btn2;
    private Button btn3;
    private Button btn4;
    private Button btn5;
    private Button btn6;
    private TextView tv_result;
    private SQLiteDatabase sqLiteDatabase;

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

        btn1 = findViewById(R.id.btn_1);
        btn1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 定义数据库的文件路径
                String dbFilePath = getFilesDir().getAbsolutePath() + File.separator + "stu.db";
                sqLiteDatabase =
                        SQLiteDatabase.openOrCreateDatabase(dbFilePath, null);
            }
        });

        btn2 = findViewById(R.id.btn_2);
        btn2.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 创建学生表
                String sql = "create table if not exists student (stuno varchar, name varchar, age int)";
                sqLiteDatabase.execSQL(sql);
            }
        });

        btn3 = findViewById(R.id.btn_3);
        btn3.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 插入一条数据
               /* String sql = "insert into student values('20190166','陈炳鑫',21)";
                sqLiteDatabase.execSQL(sql);*/

                // 第二种添加方法
                ContentValues contentValues = new ContentValues();
                contentValues.put("stuno","2019555");
                contentValues.put("name", "黄心怡");
                contentValues.put("age", 19);
                sqLiteDatabase.insert("student", null,contentValues);
            }
        });

        tv_result = findViewById(R.id.tv_result);
        btn4 = findViewById(R.id.btn_4);
        btn4.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 查询数据
               Cursor cursor = sqLiteDatabase.query("student",null,null,null,
                        null,null,null);

               StringBuffer stringBuffer = new StringBuffer();
               // 遍历Cursor
               for(cursor.moveToFirst(); !cursor.isAfterLast();cursor.moveToNext()){
                   String stuno = cursor.getString(0);
                   String name = cursor.getString(1);
                   int age = cursor.getInt(2);

                   stringBuffer.append(stuno).append("   ").append(name).append("   ").append(age).append("\n");
               }

                tv_result.setText(stringBuffer);
            }
        });

        btn5 = findViewById(R.id.btn_5);
        btn5.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 删除一条数据
                /*String sql = "delete from student where name = '张莹'";
                sqLiteDatabase.execSQL(sql);*/

                // 第二种删除
//                sqLiteDatabase.delete("student", "name='陈炳鑫'", null);
                String[] args = {"黄心怡"};
                sqLiteDatabase.delete("student", "name=?", args);
            }
        });

        btn6 = findViewById(R.id.btn_6);
        btn6.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 修改一条数据
               /* String sql = "update student set age=22 where name='黄心怡'";
                sqLiteDatabase.execSQL(sql);*/

                // 第二种更新方式
                ContentValues contentValues = new ContentValues();
                contentValues.put("name","李佩珊");
                contentValues.put("age", 18);
                sqLiteDatabase.update("student", contentValues, "stuno='2019555'", null);
            }
        });


    }
}

找到当前应用的文件目录和数据库文件

在这里插入图片描述

电脑和模拟器共享的文件目录

在这里插入图片描述

使用SQLiteOpenHelper

定义一个StudentOpenHelper

package com.example.demo;

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

import androidx.annotation.Nullable;

public class StudentOpenHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.e("StudentOpenHelper", "onCreate");
        // 创建所需要的数据表
        String sql = "create table student (id varchar, name varchar, age int, address varchar)";
        db.execSQL(sql);

        // 创建一个分数表
        String sql2 = "create table score (stuid varchar, lesson varchar, score int)";
        db.execSQL(sql2);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if(oldVersion == 1 && newVersion == 2){
            String sql = "alter table student add address varchar";
            db.execSQL(sql);
        }
    }
}

布局文件

<?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"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity"
    android:orientation="vertical">

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="添加分数"
        android:id="@+id/btn_add_score"/>


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询分数"
        android:id="@+id/btn_query_score"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="添加学生"
        android:id="@+id/btn_add"/>


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询学生"
        android:id="@+id/btn_query"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除学生"
        android:id="@+id/btn_delete"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="更新学生"
        android:id="@+id/btn_update"/>

    <TextView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/tv_query"
        android:text="查询结果"/>

</LinearLayout>

增删查改学生

package com.example.demo;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    private Button btn_add;
    private Button btn_query;
    private Button btn_delete;
    private Button btn_update;
    private Button btn_add_score;
    private Button btn_query_score;
    private TextView tv_query;
    private StudentOpenHelper studentOpenHelper;

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

        // 初始化openHelper
        studentOpenHelper = new StudentOpenHelper(MainActivity.this,
                "stu.db", null, 2);

        tv_query = findViewById(R.id.tv_query);
        btn_add = findViewById(R.id.btn_add);
        btn_add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                addUseContentProvider();
//                SQLiteDatabase writableDatabase = studentOpenHelper.getWritableDatabase();
//                ContentValues contentValues = new ContentValues();
//                contentValues.put("id", 1003);
//                contentValues.put("name", "许泽森");
//                contentValues.put("age", 19);
//                contentValues.put("address","天源路789");
//                writableDatabase.insert("student", null, contentValues);
            }
        });

        btn_add_score = findViewById(R.id.btn_add_score);
        btn_add_score.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                //stuid varchar, lesson varchar, score int

                ContentValues values = new ContentValues();
                values.put("stuid", 1002);
                values.put("lesson", "Java");
                values.put("score", 95);
                getContentResolver().insert(StudentProvider.URI_SCORE, values);
            }
        });

        btn_query_score = findViewById(R.id.btn_query_score);
        btn_query_score.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Cursor cursor = getContentResolver().query(StudentProvider.URI_SCORE,null, null, null, null);

                StringBuffer stringBuffer = new StringBuffer();
                for (cursor.moveToFirst(); !cursor.isAfterLast();cursor.moveToNext()){
                    String stuid = cursor.getString(0);
                    String lessson = cursor.getString(1);
                    int score=  cursor.getInt(2);
                    stringBuffer.append("学号").append(stuid).append("课程").append(lessson).append("分数").append(score).append("\n");
                }
                tv_query.setText(stringBuffer.toString());
            }
        });

        btn_query = findViewById(R.id.btn_query);
        btn_query.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
//                queryUseOpenHelper();
                queryUseContentProvider();
            }
        });

        btn_delete = findViewById(R.id.btn_delete);
        btn_delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
//                addUseOpenHelper();
                deleteUseContentProvider();
            }
        });

        btn_update = findViewById(R.id.btn_update);
        btn_update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                updateUseContentProvider();
               /* SQLiteDatabase writableDatabase = studentOpenHelper.getWritableDatabase();
                String where = "name = '李碧珊'";
                ContentValues contentValues = new ContentValues();
                contentValues.put("age", 20);
                int rows = writableDatabase.update("student", contentValues, where, null);
                Toast.makeText(MainActivity.this, "更新了"+ rows + "行", Toast.LENGTH_SHORT).show();*/
            }
        });
    }

    private void updateUseContentProvider() {
        String where = "name = '李四'";
        ContentValues values = new ContentValues();
        values.put("age", 30);
        values.put("address","广东省广州市");
        int rows = getContentResolver().update(StudentProvider.URI_STUDENT, values, where, null);
        Toast.makeText(MainActivity.this, "更新了"+ rows + "行", Toast.LENGTH_SHORT).show();
    }

    /*
    使用ContentPorvider 进行删除
     */
    private void deleteUseContentProvider() {
        String where = "name = '李四'";
        int rows = getContentResolver().delete(StudentProvider.URI_STUDENT, where, null);
        Toast.makeText(MainActivity.this, "删除了"+ rows + "行", Toast.LENGTH_SHORT).show();
    }

    /*
    使用ContentPorvider 进行添加
     */
    private void addUseContentProvider() {
        ContentValues contentValues = new ContentValues();
        contentValues.put("id", 1004);
        contentValues.put("name", "李四");
        contentValues.put("age", 19);
        contentValues.put("address","天源路789");
        getContentResolver().insert(StudentProvider.URI_STUDENT, contentValues);
    }

    private void addUseOpenHelper() {
        SQLiteDatabase writableDatabase = studentOpenHelper.getWritableDatabase();
        String where = "name = '蓝宁宁'";
        int rows = writableDatabase.delete("student", where, null);
        Toast.makeText(MainActivity.this, "删除了"+ rows + "行", Toast.LENGTH_SHORT).show();
    }

    /**
     * 使用ContentProvider查询
     */
    private void queryUseContentProvider() {
        Cursor cursor = getContentResolver().query(StudentProvider.URI_STUDENT, null,
                null, null, null);

        StringBuffer stringBuffer = new StringBuffer();
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            String id = cursor.getString(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);
            String address = cursor.getString(3);

            stringBuffer.append("学号").append(id).append(" 姓名").append(name).append(" 年龄").append(age).append("  地址").append(address).append("\n");
        }
        tv_query.setText(stringBuffer.toString());

    }

    /**
     * 使用SQliteOpenHelper查询
     */
    private void queryUseOpenHelper() {
        SQLiteDatabase readableDatabase = studentOpenHelper.getReadableDatabase();
        Cursor cursor = readableDatabase.query("student", null, null, null, null, null, null);
        StringBuffer stringBuffer = new StringBuffer();
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            String id = cursor.getString(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);
            String address = cursor.getString(3);

            stringBuffer.append("学号").append(id).append(" 姓名").append(name).append(" 年龄").append(age).append("  地址").append(address).append("\n");
            tv_query.setText(stringBuffer.toString());
        }
    }
}

定义ContentProvider

package com.example.demo;

import android.content.ContentProvider;
import android.content.ContentValues;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;

import androidx.annotation.NonNull;
import androidx.annotation.Nullable;

public class StudentProvider extends ContentProvider {
    private StudentOpenHelper studentOpenHelper;
    public static Uri URI_STUDENT = Uri.parse("content://com.example.demo/student");
    public static Uri URI_SCORE = Uri.parse("content://com.example.demo/score");

    static UriMatcher uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);

    static {
        uriMatcher.addURI("com.example.demo", "student", 1);
        uriMatcher.addURI("com.example.demo", "score", 2);
    }


    @Override
    public boolean onCreate() {
        studentOpenHelper = new StudentOpenHelper(getContext(), "stu.db"
                , null, 2);
        return false;
    }

    @Nullable
    @Override
    public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
        SQLiteDatabase readableDatabase = studentOpenHelper.getReadableDatabase();
        int match = uriMatcher.match(uri);
        if (match == 1) {
            return readableDatabase.query("student", projection, selection, selectionArgs, null, null, sortOrder);
        }
        if(match == 2){
            return readableDatabase.query("score", projection, selection, selectionArgs, null, null, sortOrder);
        }

        return null;
    }

    @Nullable
    @Override
    public String getType(@NonNull Uri uri) {
        return null;
    }

    @Nullable
    @Override
    public Uri insert(@NonNull Uri uri, @Nullable ContentValues values) {
        SQLiteDatabase writableDatabase = studentOpenHelper.getWritableDatabase();
        if (uriMatcher.match(uri) == 1) {
            writableDatabase.insert("student", null, values);
        }else if(uriMatcher.match(uri) == 2) {
            writableDatabase.insert("score", null, values);
        }

        writableDatabase.close();
        return null;
    }

    @Override
    public int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs) {
        SQLiteDatabase writableDatabase = studentOpenHelper.getWritableDatabase();
        if (uriMatcher.match(uri) == 1) {
            return writableDatabase.delete("student", selection, selectionArgs);
        }
        return 0;
    }

    @Override
    public int update(@NonNull Uri uri, @Nullable ContentValues values, @Nullable
            String selection, @Nullable String[] selectionArgs) {
        SQLiteDatabase writableDatabase = studentOpenHelper.getWritableDatabase();
        if (uriMatcher.match(uri) == 1) {
            return writableDatabase.update("student", values, selection, selectionArgs);
        }

        return 0;
    }
}