1. 首页
  2. 文章中心
  3. Android基础

Android SQLite基本用法

77 阅读 0 评论
我是靠谱客的博主 明亮豌豆，这篇文章主要介绍Android SQLite基本用法，现在分享给大家，希望可以做个参考。

1.创建表格

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

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

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

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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里面调用，实现增删改查

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
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关于表名，字段名等可以统一规范

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
 * 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

复制代码
1
2
3
4
5
6
7
8
9
10
11
/**
 * Sam on 2020/8/5.
 */

public class StudentBean {

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

 

adapter

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
<?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>

 

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?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>

 

最后

以上就是明亮豌豆最近收集整理的关于Android SQLite基本用法的全部内容，更多相关Android内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(51)

相关文章

设置屏保程序注解
设置屏保程序注解
标签超出图像控件c语言,VC++标签控件之图像标签控件
标签超出图像控件c语言,VC++标签控件之图像标签控件
Assert 的用法
Assert 的用法
mfc dll 小记
mfc dll 小记
Android SQLite基本用法
Android SQLite基本用法
ADB环境配置和常用命令笔记
ADB环境配置和常用命令笔记
Tomcat修改端口号(7.0 version)
Tomcat修改端口号(7.0 version)
未分类--Windows API--SafeArrayGetElement
未分类--Windows API--SafeArrayGetElement

评论列表共有 0 条评论

立即
投稿
返回
顶部