Android SQLite基本用法

1.创建表格

第一步，先创建一个类，然后继承 SQLiteOpenHelper这个类，并且实现抽象方法。

第二步，创建一个构造方法。

第三步，在Oncreat方法里面建表。

activity继承个人写的baseActivity，读者可用自己的代替，本人的里面涉及到一个顶部返回的按钮，只要在布局中去掉即可，同时把activity设置标题的的代码去掉

package com.pad.untek.mes.mespda.test.activitys.sqlite;

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

/**
 * Sam on 2020/8/5.
 */

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        //在这个方法里面来建表
        String sql = "create table student(id integer primary key autoincrement,stuid varchar(50), stuname varchar(20),stuclass varchar(20))";
        db.execSQL(sql);// 这一步就完成了加载驱动和建立连接
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

activity里面调用，实现增删改查

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.view.View;
import android.widget.TextView;

import com.pad.untek.mes.mespda.test.R;
import com.pad.untek.mes.mespda.test.activitys.BaseActivity;
import com.pad.untek.mes.mespda.test.view.ClearEditText;

import java.util.ArrayList;
import java.util.List;

import butterknife.BindView;
import butterknife.ButterKnife;
import butterknife.OnClick;

public class SQLiteTestActivity extends BaseActivity {

    //替换个人BaseActivity需去掉textTitle
    @BindView(R.id.text_title)
    TextView textTitle;

    @BindView(R.id.cet_name)
    ClearEditText cetName;
    @BindView(R.id.cet_id)
    ClearEditText cetId;
    @BindView(R.id.cet_class)
    ClearEditText cetClass;
    @BindView(R.id.tv_add)
    TextView tvAdd;
    @BindView(R.id.tv_delete)
    TextView tvDelete;
    @BindView(R.id.tv_change)
    TextView tvChange;
    @BindView(R.id.tv_query)
    TextView tvQuery;
    @BindView(R.id.rv_list)
    RecyclerView rvList;

    Context context;
    String studentId;
    String studentName;
    String studentClass;
    String contents = "";

    MySQLiteOpenHelper mySQLiteOpenHelper;
    SQLiteDatabase database;
    List<StudentBean> studentBeanList = new ArrayList<>();
    SQLiteQueryDataAdapter adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite_test);
        ButterKnife.bind(this);
        context = this;
        initView();
    }

    private void initView() {
        rvList.setLayoutManager(new LinearLayoutManager(this));
        //替换个人BaseActivity需去掉textTitle
        textTitle.setText("SQLitet操作");//设置页面标题

        //建表
        mySQLiteOpenHelper = new MySQLiteOpenHelper(context, "students.db", null, 1);
        database = mySQLiteOpenHelper.getWritableDatabase();
    }

    @OnClick({R.id.tv_add, R.id.tv_delete, R.id.tv_change, R.id.tv_query})
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.tv_add:
                if (isEmpty(3)) {
                    addData();
                    queryData("");//添加后显示所有数据
                }
                showDataList(studentBeanList);
                break;

            case R.id.tv_delete:
                if (isEmpty(4)) {
                    deleteData(studentId);
                    queryData("");//删除后显示剩余所有数据
                }
                showDataList(studentBeanList);
                break;

            case R.id.tv_change:
                if (isEmpty(1)) {
                    String[] strings = new String[]{studentId, studentName, studentClass};
                    updateData(strings);
                    queryData("");//修改后显示所有数据
                }
                showDataList(studentBeanList);
                break;

            case R.id.tv_query:
                if (isEmpty(2)) {
                    queryData(contents);//查询后显示所有数据
                }
                showDataList(studentBeanList);
                break;
        }
    }

    /**
     * 增加data
     */
    private void addData() {
        queryData("");
        if (studentBeanList.size() > 0) {
            for (int i = 0; i < studentBeanList.size(); i++) {
                if (studentId.equals(studentBeanList.get(i).stuid)) {
                    toastSpek("学生ID已存在！");
                    return;
                }
            }
        }

        //create student data
        ContentValues contentValues = new ContentValues();
        contentValues.put(SQLiteConstants.stuid, studentId);
        contentValues.put(SQLiteConstants.stuname, studentName);
        contentValues.put(SQLiteConstants.stuclass, studentClass);

        //insert to students table
        database.insert(SQLiteConstants.tabelName, null, contentValues);
        toastSpek("添加学生成功！");
    }

    /**
     * 根据ID删除data
     *
     * @param studentId
     */
    private void deleteData(String studentId) {
        queryData("");
        if (studentBeanList.size() > 0) {
            for (int i = 0; i < studentBeanList.size(); i++) {
                if (studentId.equals(studentBeanList.get(i).stuid)) {
                    database.delete(SQLiteConstants.tabelName, SQLiteConstants.SQL_QUERY_STU_ID, new String[]{studentId});
                    toastSpek("删除学生成功！");
                    return;
                }
            }
            toastSpek("学生ID不存在！");
        }
    }

    /**
     * 根据ID修改data
     *
     * @param strings
     */
    private void updateData(String[] strings) {
        queryData("");
        if (studentBeanList.size() > 0) {
            for (int i = 0; i < studentBeanList.size(); i++) {
                if (studentId.equals(studentBeanList.get(i).stuid)) {
                    ContentValues contentValues = new ContentValues();
                    if (!strings[1].isEmpty()) contentValues.put(SQLiteConstants.stuname, strings[1]);
                    if (!strings[2].isEmpty()) contentValues.put(SQLiteConstants.stuclass, strings[2]);

                    database.update(SQLiteConstants.tabelName, contentValues, SQLiteConstants.SQL_QUERY_STU_ID, new String[]{strings[0]});
                    toastSpek("修改学生成功！");
                    return;
                }
            }
            toastSpek("学生ID不存在！");
        }
    }

    /**
     * 根据条件查询data
     *
     * @param contents 查询条件  如果都为空，查询所有数据；有值就进行条件查询
     */
    private void queryData(String contents) {

        studentBeanList.clear();//清空查询前的数据
        String content = "%" + contents + "%";

        //条件查询
        if (!contents.isEmpty()) {
            Cursor cursor; //通过游标获取数据
            cursor = database.query(SQLiteConstants.tabelName, null,
                    SQLiteConstants.SQL_QUERY_STU_ID
                            + SQLiteConstants.SQL_QUERY_OR
                            + SQLiteConstants.SQL_QUERY_STU_NAME
                            + SQLiteConstants.SQL_QUERY_OR
                            + SQLiteConstants.SQL_QUERY_STU_CLASS,
                    new String[]{content, content, content}, null, null, null);

            if (cursor != null) {
                while (cursor.moveToNext()) {
                    StudentBean student = new StudentBean();
                    student.id = cursor.getInt(0);
                    student.stuid = cursor.getString(1);
                    student.stuname = cursor.getString(2);
                    student.stuclass = cursor.getString(3);
                    studentBeanList.add(student);
                }
            }
            cursor.close();
        } else {//查询全部
            Cursor cursor; //通过游标获取数据
            cursor = database.query(SQLiteConstants.tabelName, null, null, null, null, null, null);

            if (cursor != null) {
                while (cursor.moveToNext()) {
                    StudentBean student = new StudentBean();
                    student.id = cursor.getInt(0);
                    student.stuid = cursor.getString(1);
                    student.stuname = cursor.getString(2);
                    student.stuclass = cursor.getString(3);
                    studentBeanList.add(student);
                }
            }
            cursor.close();
        }
    }


    /**
     * 展现data
     *
     * @param studentBeanList
     */
    private void showDataList(List<StudentBean> studentBeanList) {

        if (studentBeanList.size() > 0) {

            if (adapter == null) {
                rvList.setAdapter(new SQLiteQueryDataAdapter(context, studentBeanList));
            } else {
                adapter.updateDataList(studentBeanList);
            }
        } else {
            toastSpek("没有相关数据！");
        }
    }

    /**
     * @param type 需要判断的类型
     * @return
     */
    private boolean isEmpty(int type) {
        contents = "";
        studentId = cetId.getText().toString().trim();
        studentName = cetName.getText().toString().trim();
        studentClass = cetClass.getText().toString().trim();

        if (type == 3) {//增加data时候，都必填
            if (studentId.isEmpty() || studentName.isEmpty() || studentClass.isEmpty()) {
                toastSpek("name，ID和class都不能为空！");
                return false;
            }
        }

        if (type == 2) {
            if (!studentId.isEmpty()) contents = studentId;
            if (!studentName.isEmpty()) contents = studentName;
            if (!studentClass.isEmpty()) contents = studentClass;
            return true;
        }

        if (type == 1) {//修改data时候，ID不能为空
            if (studentId.isEmpty()) {
                toastSpek("ID不能为空！");
                return false;
            } else if (!studentName.isEmpty() || !studentClass.isEmpty()) {
                return true;
            } else {
                toastSpek("请填写修改内容！");
                return false;
            }
        }

        if (type == 4) {//删除data时候，ID不能为空
            if (studentId.isEmpty()) {
                toastSpek("ID不能为空！");
                return false;
            }
        }

        return true;
    }
}

 

SQLiteConstants关于表名，字段名等可以统一规范

/**
 * Sam on 2020/8/5.
 */

public class SQLiteConstants {

    public static String SQL_QUERY_STU_ID = "stuid like ?";
    public static String SQL_QUERY_STU_NAME = "stuname like ?";
    public static String SQL_QUERY_STU_CLASS = "stuclass like ?";
    public static String SQL_QUERY_OR = " or ";
    public static String SQL_QUERY_AND = " and ";

    public static String tabelName = "student";//table name
    public static String stuid = "stuid";//student id
    public static String stuname = "stuname";//student name
    public static String stuclass = "stuclass";//student class
}

 

数据bean

/**
 * Sam on 2020/8/5.
 */

public class StudentBean {

    public int id;
    public String stuid;
    public String stuname;
    public String stuclass;
}

 

adapter

import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

import com.pad.untek.mes.mespda.test.R;

import java.util.List;

import butterknife.BindView;
import butterknife.ButterKnife;

/**
 * Sam on 2020/8/5.
 */

public class SQLiteQueryDataAdapter extends RecyclerView.Adapter<RecyclerView.ViewHolder> {

    Context context;
    List<StudentBean> studentBeanList;

    public SQLiteQueryDataAdapter(Context context, List<StudentBean> studentBeanList) {
        this.context = context;
        this.studentBeanList = studentBeanList;
    }

    public void updateDataList(List<StudentBean> studentBeanList) {
        this.studentBeanList = studentBeanList;
        notifyDataSetChanged();
    }

    @Override
    public RecyclerView.ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        ViewMyHolder holder = new ViewMyHolder(LayoutInflater.from(context).inflate(R.layout.adapter_sqlite_query_data, parent, false));
        return holder;
    }

    @Override
    public void onBindViewHolder(RecyclerView.ViewHolder holder, int position) {
        ViewMyHolder viewMyHolder = (ViewMyHolder) holder;
        viewMyHolder.tvStuid.setText(studentBeanList.get(position).stuid);
        viewMyHolder.tvStuname.setText(studentBeanList.get(position).stuname);
        viewMyHolder.tvStuclass.setText(studentBeanList.get(position).stuclass);
    }

    @Override
    public int getItemCount() {
        return studentBeanList.size();
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public int getItemViewType(int position) {
        return position;
    }

    class ViewMyHolder extends RecyclerView.ViewHolder {

        @BindView(R.id.tv_stuid)
        TextView tvStuid;
        @BindView(R.id.tv_stuname)
        TextView tvStuname;
        @BindView(R.id.tv_stuclass)
        TextView tvStuclass;

        public ViewMyHolder(View itemView) {
            super(itemView);
            ButterKnife.bind(this,itemView);
        }
    }
}

 

布局文件，com.pad.untek.mes.mespda.test.view.ClearEditText是自定义输入框，可换成Android自带EditText

<?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"
    android:orientation="vertical"
    tools:context=".activitys.sqlite.SQLiteTestActivity">

    <include layout="@layout/title" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="@dimen/ten_dp"
        android:orientation="vertical"
        android:paddingLeft="@dimen/ten_dp"
        android:paddingRight="@dimen/ten_dp">

        <com.pad.untek.mes.mespda.test.view.ClearEditText
            android:id="@+id/cet_id"
            android:layout_width="match_parent"
            android:layout_height="@dimen/forty_dp"
            android:layout_marginTop="@dimen/ten_dp"
            android:background="@drawable/grey_round_white_kuang"
            android:drawableRight="@drawable/ic_edit_clear"
            android:hint="id" />

        <com.pad.untek.mes.mespda.test.view.ClearEditText
            android:id="@+id/cet_name"
            android:layout_width="match_parent"
            android:layout_height="@dimen/forty_dp"
            android:layout_marginTop="@dimen/ten_dp"
            android:background="@drawable/grey_round_white_kuang"
            android:drawableRight="@drawable/ic_edit_clear"
            android:hint="name" />

        <com.pad.untek.mes.mespda.test.view.ClearEditText
            android:id="@+id/cet_class"
            android:layout_width="match_parent"
            android:layout_height="@dimen/forty_dp"
            android:layout_marginTop="@dimen/ten_dp"
            android:background="@drawable/grey_round_white_kuang"
            android:drawableRight="@drawable/ic_edit_clear"
            android:hint="class" />

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="@dimen/forty_dp"
        android:layout_marginTop="@dimen/ten_dp"
        android:orientation="horizontal"
        android:paddingLeft="@dimen/ten_dp"
        android:paddingRight="@dimen/ten_dp">

        <TextView
            android:id="@+id/tv_add"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:background="@color/blue"
            android:gravity="center"
            android:text="增加"
            android:textColor="@color/white"
            android:textStyle="bold" />

        <TextView
            android:id="@+id/tv_delete"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_marginLeft="@dimen/ten_dp"
            android:layout_weight="1"
            android:background="@color/blue"
            android:gravity="center"
            android:text="删除"
            android:textColor="@color/white"
            android:textStyle="bold" />

        <TextView
            android:id="@+id/tv_change"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_marginLeft="@dimen/ten_dp"
            android:layout_weight="1"
            android:background="@color/blue"
            android:gravity="center"
            android:text="修改"
            android:textColor="@color/white"
            android:textStyle="bold" />

        <TextView
            android:id="@+id/tv_query"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_marginLeft="@dimen/ten_dp"
            android:layout_weight="1"
            android:background="@color/blue"
            android:gravity="center"
            android:text="查询"
            android:textColor="@color/white"
            android:textStyle="bold" />

    </LinearLayout>

    <LinearLayout
        android:id="@+id/linlayout"
        android:layout_width="match_parent"
        android:layout_height="@dimen/forty_dp"
        android:layout_marginTop="@dimen/ten_dp"
        android:gravity="center_vertical"
        android:orientation="horizontal"
        android:paddingLeft="@dimen/ten_dp"
        android:paddingRight="@dimen/ten_dp">

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="center"
            android:text="ID"
            android:textColor="@color/black"
            tools:ignore="Suspicious0dp" />

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="center"
            android:text="Name"
            android:textColor="@color/black"
            tools:ignore="Suspicious0dp" />

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="center"
            android:text="Class"
            android:textColor="@color/black"
            tools:ignore="Suspicious0dp" />

    </LinearLayout>

    <android.support.v7.widget.RecyclerView
        android:id="@+id/rv_list"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:paddingLeft="@dimen/ten_dp"
        android:paddingRight="@dimen/ten_dp" />

</LinearLayout>

 

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="@dimen/forty_dp"
    android:gravity="center_vertical"
    android:orientation="horizontal">

    <TextView
        android:id="@+id/tv_stuid"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_weight="1"
        android:gravity="center"
        android:hint="2020080500001"
        android:textColor="@color/black" />

    <TextView
        android:id="@+id/tv_stuname"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_weight="1"
        android:gravity="center"
        android:hint="黄飞鸿"
        android:textColor="@color/black" />

    <TextView
        android:id="@+id/tv_stuclass"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_weight="1"
        android:gravity="center"
        android:hint="武术一班"
        android:textColor="@color/black" />

</LinearLayout>