Android数据库SQLite的使用

最新推荐文章于 2021-05-27 14:38:16 发布

轨迹 ‎‎ ‎‎

最新推荐文章于 2021-05-27 14:38:16 发布

阅读量316

点赞数 1

文章标签： Android基础

本文链接：https://blog.csdn.net/weixin_43219615/article/details/99288260

版权

《零基础到App上线》学习笔记同时被 2 个专栏收录

46 篇文章 2 订阅

订阅专栏

《零基础到App上线》学习笔记

34 篇文章 1 订阅

订阅专栏

1.简介

SQLite是一个小巧的嵌入式数据库，它的多数语法和Oracle一样（还好这个学期学了数据库）。SQLiteDataBase是SQLite的数据库管理类，具有创建，删除，执行SQL命令以及执行其他常见数据库管理任务的方法。SQLiteOpenHelper是用于管理数据库创建和版本管理的帮助程序类，是个抽象类，一般我们会创建创建一个子类继承自SQLiteOpenHelper实现onCreate(SQLiteDatabase)，onUpgrade(SQLiteDatabase, int, int))。onCreate方法只在第一次打开数据库的时候执行，所以重写这个方法时可以创建表。
例子图片

2.例子

创建SQLiteActivity，写好辅助类UserDbHelper继承自SQLiteOpenHelper。

UserInfo.java。包装用户信息。

package xyz.strasae.androidlearn.my.bean;

public class UserInfo {
    public String id;
    public String password;
}

UserDbHelper.java。辅助类。

package xyz.strasae.androidlearn.my;

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

import java.util.ArrayList;

import xyz.strasae.androidlearn.my.bean.UserInfo;

/**
 * 单例模式 确保只打开一次
 */
public class UserDbHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "user.db";
    private static final int DB_VERSION = 1;
    private static UserDbHelper userDBHelper = null;
    private SQLiteDatabase sqLiteDatabase = null;
    private static final String TABLE_NAME = "user_info";

    /*单例模式 构造器私有*/
    private UserDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    /*单例模式 构造器私有*/
    private UserDbHelper(Context context, int db_version) {
        super(context, DB_NAME, null, db_version);
    }

    /*单例模式 提供对外的接口*/
    public static UserDbHelper getInstance(Context context, int db_version) {
        if(db_version > 0 && userDBHelper == null) {
            userDBHelper = new UserDbHelper(context, db_version);
        } else if(userDBHelper == null) {
            userDBHelper = new UserDbHelper(context);
        }
        return userDBHelper;
    }

    /*打开写连接*/
    public SQLiteDatabase openWriteLink() {
        if(sqLiteDatabase == null || sqLiteDatabase.isOpen() != true) {
            sqLiteDatabase = userDBHelper.getWritableDatabase();
        }
        return sqLiteDatabase;
    }

    /*打开读连接*/
    public SQLiteDatabase openReadLink() {
        if(sqLiteDatabase == null || sqLiteDatabase.isOpen() != true) {
            sqLiteDatabase = userDBHelper.getReadableDatabase();
        }
        return sqLiteDatabase;
    }

    /*关闭连接*/
    public void closeLink() {
        if(sqLiteDatabase != null && sqLiteDatabase.isOpen() == true) {
            sqLiteDatabase.close();
            sqLiteDatabase = null;
        }
    }

    /*获取数据库名称*/
    public String getDbName() {
        if(userDBHelper != null) {
            return userDBHelper.getDatabaseName();
        } else {
            return DB_NAME;
        }
    }

    @Override
    /*创建表*/
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
        sqLiteDatabase.execSQL(drop_sql);
        String create_sql =  "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "user_id VARCHAR NOT NULL, password VARCHAR NOT NULL);";
        sqLiteDatabase.execSQL(create_sql);
    }

    @Override
    /*数据库升级，注意一次只能添加一列 第二个参数表示oldVersion 第三个参数表示newVersion*/
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        if (i1 > 1) {
            String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "phone VARCHAR;";
            sqLiteDatabase.execSQL(alter_sql);
        }
    }

    /*删除指定关键字的记录 condition是where后的条件*/
    public int delete(String condition) {
        return sqLiteDatabase.delete(TABLE_NAME, condition, null);
    }

	/*删除所有记录（0=0是永远成立的）*/
    public int deleteAll() {
        return sqLiteDatabase.delete(TABLE_NAME, "0=0", null);
    }

    /*插入*/
    public long insert(ArrayList<UserInfo> infoArrayList) {
        long result = -1;
        for(int i = 0; i < infoArrayList.size(); i++) {
            UserInfo userInfo = infoArrayList.get(i);
            ContentValues contentValues = new ContentValues();
            contentValues.put("user_id", userInfo.id);
            contentValues.put("password", userInfo.password);
            result = sqLiteDatabase.insert(TABLE_NAME, "", contentValues);
        }
        return result;
    }

    public long insert(UserInfo userInfo) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("user_id", userInfo.id);
        contentValues.put("password", userInfo.password);
        return sqLiteDatabase.insert(TABLE_NAME, "", contentValues);
    }

    /*查询*/
    public ArrayList<UserInfo> query(String condition) {
        String sql = String.format("select _id,user_id,password" +
                " from %s where %s;", TABLE_NAME, condition);
        ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
        Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
        if(cursor.moveToFirst()) {
            for(;;cursor.moveToNext()) {
                UserInfo userInfo = new UserInfo();
                userInfo.id = cursor.getString(1);
                userInfo.password = cursor.getString(2);
                infoArray.add(userInfo);
                if (cursor.isLast() == true) {
                    break;
                }
            }
        }
        cursor.close();
        return infoArray;
    }
}

activity_sqlite.xml

<?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=".SQLiteActivity"
    android:orientation="vertical"
    android:padding="10dp">

    <!--注意ScrollView下只能挂一个节点-->
    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:orientation="vertical">
            <RelativeLayout
                android:layout_width="match_parent"
                android:layout_height="50dp">

                <TextView
                    android:id="@+id/tv_id"
                    android:layout_width="wrap_content"
                    android:layout_height="match_parent"
                    android:textColor="#000000"
                    android:text="账号："
                    android:textSize="18sp"
                    android:layout_alignParentLeft="true"
                    android:gravity="center_vertical"/>

                <EditText
                    android:id="@+id/et_id"
                    android:inputType="number"
                    android:maxLength="11"
                    android:layout_width="match_parent"
                    android:layout_height="match_parent"
                    android:hint="账号"
                    android:layout_toRightOf="@id/tv_id"/>
            </RelativeLayout>

            <RelativeLayout
                android:layout_width="match_parent"
                android:layout_height="50dp">

                <TextView
                    android:id="@+id/tv_password"
                    android:layout_width="wrap_content"
                    android:layout_height="match_parent"
                    android:textColor="#000000"
                    android:text="密码："
                    android:textSize="18sp"
                    android:layout_alignParentLeft="true"
                    android:gravity="center_vertical"/>

                <EditText
                    android:id="@+id/et_password"
                    android:inputType="textPassword"
                    android:maxLength="15"
                    android:layout_width="match_parent"
                    android:layout_height="match_parent"
                    android:hint="密码"
                    android:layout_toRightOf="@id/tv_password"/>
            </RelativeLayout>

            <!--textAllCaps属性设置大小写（默认全部大写）-->
            <Button
                android:textAllCaps="false"
                android:id="@+id/btn_ok"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:text="存入SQLite"/>

            <EditText
                android:id="@+id/et_input_id"
                android:inputType="number"
                android:maxLength="11"
                android:hint="请输入要删除的id"
                android:layout_marginTop="50dp"
                android:layout_width="match_parent"
                android:layout_height="wrap_content" />
            <Button
                android:textAllCaps="false"
                android:text="从SQLite删除指定数据"
                android:id="@+id/btn_delete"
                android:layout_width="match_parent"
                android:layout_height="wrap_content" />

            <Button
                android:textAllCaps="false"
                android:id="@+id/btn_query"
                android:layout_marginTop="50dp"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:text="从SQLite中查询数据"
                />

            <TextView
                android:id="@+id/tv_show_data"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textSize="18sp"
                android:textColor="#000000"
                android:hint="用于展示查询结果"
                />
        </LinearLayout>
    </ScrollView>
</LinearLayout>

SQLiteActivity.java

package xyz.strasae.androidlearn.my;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;

import xyz.strasae.androidlearn.my.bean.UserInfo;

public class SQLiteActivity extends AppCompatActivity {
    private UserDbHelper userDbHelper;
    private EditText et_id;
    private EditText et_password;
    private TextView tv_show_data;
    private EditText et_input_id;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
        et_id = findViewById(R.id.et_id);
        et_password = findViewById(R.id.et_password);
        tv_show_data = findViewById(R.id.tv_show_data);
        et_input_id = findViewById(R.id.et_input_id);
        findViewById(R.id.btn_ok).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if(userDbHelper==null) {
                    Toast.makeText(SQLiteActivity.this, "数据库连接为空", Toast.LENGTH_SHORT).show();
                    return;
                }
                userDbHelper.openWriteLink();
                String id = et_id.getText().toString();
                String password = et_password.getText().toString();
                if(TextUtils.isEmpty(id)) {
                    Toast.makeText(SQLiteActivity.this, "请先填写账号！", Toast.LENGTH_SHORT).show();
                    return ;
                } else if(TextUtils.isEmpty(password)){
                    Toast.makeText(SQLiteActivity.this, "密码不能为空！", Toast.LENGTH_SHORT).show();
                    return ;
                }
                UserInfo userInfo = new UserInfo();
                userInfo.id = id;
                userInfo.password = password;
                userDbHelper.insert(userInfo);
                Toast.makeText(SQLiteActivity.this, "存入数据库成功！", Toast.LENGTH_SHORT).show();
            }
        });
        findViewById(R.id.btn_query).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if(userDbHelper==null) {
                    Toast.makeText(SQLiteActivity.this, "数据库连接为空", Toast.LENGTH_SHORT).show();
                    return;
                }
                userDbHelper.openReadLink();
                ArrayList<UserInfo> userArray = userDbHelper.query("1=1");
                String desc = String.format("数据库查询到%d条记录，详情如下：", userArray.size());
                if(userArray == null || userArray.size() <= 0) {
                    desc = "查询结果为空";
                }
                for (int i=0; i<userArray.size(); i++) {
                    UserInfo info = userArray.get(i);
                    desc = String.format("%s\n第%d条记录信息如下：", desc, i+1);
                    desc = String.format("%s\n　id：%s", desc, info.id);
                    desc = String.format("%s\n　password：%s", desc, info.password);
                }
                tv_show_data.setText(desc);
            }
        });
        findViewById(R.id.btn_delete).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if(userDbHelper==null) {
                    Toast.makeText(SQLiteActivity.this, "数据库连接为空", Toast.LENGTH_SHORT).show();
                    return;
                }
                userDbHelper.openReadLink();
                int count = userDbHelper.delete("user_id=" + et_input_id.getText().toString());
                Toast.makeText(SQLiteActivity.this, "成功删除" + count + "条数据", Toast.LENGTH_SHORT).show();
            }
        });
    }

    protected void onStart() {
        super.onStart();
        userDbHelper = UserDbHelper.getInstance(this, 2);
    }

    @Override
    protected void onStop() {
        super.onStop();
        if(userDbHelper != null)
            userDbHelper.closeLink();
    }
}