Sqlite数据库概述

Android中访问Sqlite数据库的类

实例

SQLiteOpenHelper的封装

关键代码

SQLiteOpenHelper的使用

activity_main.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:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">
    <Button
        android:id="@+id/btn1"
        android:text="创建数据库"
        android:textSize="20sp"
        android:onClick="btnCreate"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    <Button
        android:id="@+id/btn2"
        android:text="更新数据库"
        android:textSize="20sp"
        android:onClick="btnUpdate"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    <Button
        android:id="@+id/btn3"
        android:text="插入数据"
        android:textSize="20sp"
        android:onClick="btnInsertData"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    <Button
        android:id="@+id/btn4"
        android:text="更新数据"
        android:textSize="20sp"
        android:onClick="btnUpdateData"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    <Button
        android:id="@+id/btn5"
        android:text="查询数据"
        android:textSize="20sp"
        android:onClick="btnSelectData"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    <Button
        android:id="@+id/btn6"
        android:text="删除数据"
        android:textSize="20sp"
        android:onClick="btnDeleteData"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
</LinearLayout>

MainActivity.java

package com.bluelesson.sqlitedemo;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
public class MainActivity extends AppCompatActivity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }
    public void btnCreate(View view) {
        //创建一个DatabaseHelper对象
        DBHelper dbHelper1 = new DBHelper(MainActivity.this, "test_db");
        //取得一个只读的数据库对象
        SQLiteDatabase db1 = dbHelper1.getReadableDatabase();
        db1.close();
    }
    public void btnUpdate(View view) {
        DBHelper dbHelper2 = new DBHelper(MainActivity.this, "test_db", 2);
        //更新版本
        SQLiteDatabase db2 = dbHelper2.getReadableDatabase();
        db2.close();
    }
    public void btnInsertData(View view) {
        // 1. 准备一个数据对象
        // 1.1 创建存放数据的ContentValues对象
        ContentValues values = new ContentValues();
        // 1.2 向ContentValues中存放数据
        values.put("id", 1);
        values.put("name","zhangsan");
        // 2. 插入数据
        // 2.1 获取数据库对象
        DBHelper dbHelper3 = new DBHelper(MainActivity.this, "test_db");
        SQLiteDatabase db3 = dbHelper3.getWritableDatabase();
        //2.2 执行插入命令
        db3.insert("user", null, values);
        db3.close();
    }
    public void btnUpdateData(View view) {
        // 1. 准备要更新的数据对象
        ContentValues values2 = new ContentValues();
        values2.put("name", "xiaosan");
        // 2. 获取数据库对象
        DBHelper dbHelper4 = new DBHelper(MainActivity.this, "test_db");
        SQLiteDatabase db4 = dbHelper4.getWritableDatabase();
        // 3. 执行更新方法，更新
        db4.update("user", values2, "id=?", new String[]{"1"});
        db4.close();
    }
    public void btnSelectData(View view) {
        // 1. 获取数据库对象
        DBHelper dbHelper5 = new DBHelper(MainActivity.this, "test_db");
        SQLiteDatabase db5 = dbHelper5.getReadableDatabase();
        // 2. 查询数据，返回游标对象，遍历
        // 2.1 查询并接收游标对象
        Cursor cursor = db5.query("user", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null, null);
        // 2.2 利用游标遍历所有数据对象
        while(cursor.moveToNext()){
            String name = cursor.getString(cursor.getColumnIndex("name"));
            //日志打印输出
            Log.i("MainActivity", "query-->" + name);
        }
        db5.close();
    }
    public void btnDeleteData(View view) {
        DBHelper dbHelper6 = new DBHelper(MainActivity.this,"test_db");
        SQLiteDatabase db6 = dbHelper6.getWritableDatabase();
        // 删除数据，指定表，条件
        db6.delete("user", "id=?", new String[]{"1"});
        db6.close();
    }
}

DBHelper.java

package com.bluelesson.sqlitedemo;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;
import android.util.Log;
public class DBHelper extends SQLiteOpenHelper {
    public DBHelper(@Nullable Context context, @Nullable String name) {
        super(context, name, null, 1);
    }
    //@注解、限定的意思，@Nullable表示可以为空
    public DBHelper(@Nullable Context context, @Nullable String name,
                    int version) {
        super(context, name, null, version);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i("DBHelper", "create a Database");
        //创建数据库sql语句
        String sql = "create table user(id int,name varchar(20))";
        // 执行创建数据库操作
        db.execSQL(sql);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //创建成功，日志输出提示
        Log.i("DBHelper", "update a Database");
    }
}