一.什么是安卓SQLite
SQLite是安卓的一种轻量级数据存储方式之一。
SQLite占资源非常低,在嵌入式设备中,只需几百K的内存。
SQLite支持标准的SQL语句,遵循数据库的ACID(增删改查)事务。
SQLite不要安装,不需要密码。
二.为什么要用SQLite
当数据量大、结构复杂,使用ShareFreferences和文件存储相当复杂,容易出错,效率低下。而使用SQLite就比较简单。
三.如何使用SQLite
1.创建数据库和数据表
2.添加数据
//获得SQLiteDatabase对象,读写模式
SQLiteDatabase sqLiteDatabase = datebaseHepler.getWritableDatabase();
//ContentValues类似HashMap,区别是ContentValues只能存简单数据类型,不能存对象
ContentValues contentValues = new ContentValues();
contentValues.put("name", cc.getText().toString());
//执行插入操作
sqLiteDatabase.insert("book", null, contentValues);
3.修改数据
String name = cc.getText().toString();
SQLiteDatabase sqLiteDatabase2 = datebaseHepler.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", dd.getText().toString());
//第二个参数是修改的字段及修改的值(已经存放到ContentValues中)
//第三个参数是WHERE语句
//第四个参数是WHERE语句中占位符的填充值
//如果第三四个参数为null,那就将每条记录都改掉
sqLiteDatabase2.update("book", values, "name=?", new String[]{name});
3.删除数据
SQLiteDatabase sqLiteDatabase3 = datebaseHepler.getWritableDatabase();
String name1 = cc.getText().toString();
//第二个参数是WHERE语句(即执行条件,删除哪条数据)
//第三个参数是WHERE语句中占位符(即"?"号)的填充值
sqLiteDatabase3.delete("book", "name=?", new String[]{name1});
四.查询数据
//得到数据库对象
SQLiteDatabase sqLiteDatabase1 = datebaseHepler.getReadableDatabase();
//创建游标
Cursor cursor = sqLiteDatabase1.query("book", null, null, null, null, null, null);
//游标置顶
cursor.moveToFirst();
//遍历
do {
String name = cursor.getString(cursor.getColumnIndex("name"));
Log.e("NAME", name);
} while (cursor.moveToNext());
//游标归零
cursor.close();
4.创建数据库和数据表的步骤
新建类继承SQLiteOpenHelper;
实现构造方法,重写onCreate、onUpgrade方法;
实例化SQLiteOpenHelper的子类对象;
调用getReadableDatabase或getWritableDatabase方法
五.SQLite实例(代码展示)
1.创建类继承SQLiteOpenHelper,并写出数据库
package com.example.ll.storeapplication.store;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
/**
* Created by ll on 2018/3/20.
*/
public class MyDatebaseHepler extends SQLiteOpenHelper {
public static final String CREATE_BOOK = "create table book("
+ "id integer primary key autoincrement,"
+ "auto text,"
+ "price real,"
+ "name text not null)";
private Context mcontext;
public MyDatebaseHepler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
mcontext = context;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//创建数据表语句
sqLiteDatabase.execSQL(CREATE_BOOK);
Toast.makeText(mcontext, "Create succeed", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
//更新数据表结构
}
}
2.在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"
android:orientation="vertical"
tools:context="com.example.ll.storeapplication.SQLActivity">
<EditText
android:id="@+id/nametv"
android:gravity="center"
android:textSize="20sp"
android:hint="姓名"
android:layout_width="match_parent"
android:layout_height="50dp" />
<EditText
android:id="@+id/selecttv"
android:gravity="center"
android:textSize="20sp"
android:hint="修改姓名"
android:layout_width="match_parent"
android:layout_height="50dp" />
<Button
android:id="@+id/scs"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="插入" />
<Button
android:text="导出"
android:id="@+id/daochubtn"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<Button
android:text="修改"
android:id="@+id/xiugaibtn"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<Button
android:text="删除"
android:id="@+id/sangchubtn"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
</LinearLayout>
3.SQLActivity文件代码
package com.example.ll.storeapplication;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import com.example.ll.storeapplication.store.MyDatebaseHepler;
public class SQLActivity extends AppCompatActivity implements View.OnClickListener {
private EditText cc;
private Button aa;
private Button bb;
private Button dd;
private Button ee;
private MyDatebaseHepler datebaseHepler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sql);
datebaseHepler = new MyDatebaseHepler(this, "bookshop", null, 2);
//绑定id
aa = findViewById(R.id.scs);
cc = findViewById(R.id.nametv);
bb = findViewById(R.id.daochubtn);
aa.setOnClickListener(this);
bb.setOnClickListener(this);
dd = findViewById(R.id.xiugaibtn);
ee = findViewById(R.id.sangchubtn);
dd.setOnClickListener(this);
ee.setOnClickListener(this);
}
@Override
public void onClick(View view) {
switch (view.getId()) {
case R.id.scs:
//获得SQLiteDatabase对象,读写模式
SQLiteDatabase sqLiteDatabase = datebaseHepler.getWritableDatabase();
//ContentValues类似HashMap,区别是ContentValues只能存简单数据类型,不能存对象
ContentValues contentValues = new ContentValues();
contentValues.put("name", cc.getText().toString());
//执行插入操作
sqLiteDatabase.insert("book", null, contentValues);
break;
case R.id.daochubtn:
//得到数据库对象
SQLiteDatabase sqLiteDatabase1 = datebaseHepler.getReadableDatabase();
//创建游标
Cursor cursor = sqLiteDatabase1.query("book", null, null, null, null, null, null);
//游标置顶
cursor.moveToFirst();
//遍历
do {
String name = cursor.getString(cursor.getColumnIndex("name"));
Log.e("NAME", name);
} while (cursor.moveToNext());
//游标归零
cursor.close();
break;
case R.id.xiugaibtn:
String name = cc.getText().toString();
SQLiteDatabase sqLiteDatabase2 = datebaseHepler.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", dd.getText().toString());
//第二个参数是修改的字段及修改的值(已经存放到ContentValues中)
//第三个参数是WHERE语句
//第四个参数是WHERE语句中占位符的填充值
//如果第三四个参数为null,那就将每条记录都改掉
sqLiteDatabase2.update("book", values, "name=?", new String[]{name});
break;
case R.id.sangchubtn:
SQLiteDatabase sqLiteDatabase3 = datebaseHepler.getWritableDatabase();
String name1 = cc.getText().toString();
//第二个参数是WHERE语句(即执行条件,删除哪条数据)
//第三个参数是WHERE语句中占位符(即"?"号)的填充值
sqLiteDatabase3.delete("book", "name=?", new String[]{name1});
break;
}
}
}
4.完成效果
修改数据库的效果如下