sqlite简单使用

工程结构图 

创建DBHelp继承SQLiteOpenHelper

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

public class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context, int version) {
        //形参2 数据库名 3 游标工厂
        super(context, "godv.db", null, version);
    }

    //创建数据库的条件 1文件本身不存在 连接数据库时
    //当数据库文件创建的时候调用 1
    //用来建表（插入初始化数据）
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //建表语句
        String sql = "create table godv(_id integer primary key autoincrement, name varchar ,age int)";
        //执行语句
        sqLiteDatabase.execSQL(sql);
        //插入初始化数据
        sqLiteDatabase.execSQL("insert into godv (name,age)values('godv',11)");
        sqLiteDatabase.execSQL("insert into godv (name,age)values('goda',12)");
        sqLiteDatabase.execSQL("insert into godv (name,age)values('godb',13)");
    }

    //当传入的数据库版本号大于数据库的版本号时调用
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

main-activity 增删改查

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.Toast;

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }

    public void CreateDB(View view) {
        //2 版本号
        DBHelper dbHelper = new DBHelper(this, 1);
        //Readable 在内存满的情况下不报异常   另一个相反
        //创建数据库   得到连接
        SQLiteDatabase database = dbHelper.getReadableDatabase();
    }

    //更新库
    public void UpdateDB(View view) {
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
    }

    //1.得到连接
    //2.通过连接执行insert
    //3.关闭连接
    //插入数据
    public void Insert(View view) {
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        //参数1  表名
        ContentValues values = new ContentValues();
        values.put("name", "godb");
        values.put("age", "22");
     //mysql版本的插入
     //insert into godv (name,age) values (godb,22);
        long id = database.insert("godv", null, values);
        database.close();
        Toast.makeText(this, "id =" + id, Toast.LENGTH_LONG).show();
    }


    public void Update(View view) {
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", "jack");
        values.put("age", 13);
        //参数二更新的字段
        //参数三where后面的字段 ？可以设置多个  参数四补全
        //补全参数三问号
        int updateCount = database.update("godv", values, "_id=?", new String[]{"4"});
        database.close();
        Toast.makeText(this, "updateCount =" + updateCount, Toast.LENGTH_LONG).show();
    }

    //删除
    public void Delete(View view) {
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        //参数二where后面的字段 ？可以设置多个  参数四补全
        //补全参数三问号
        int deleteCount = database.delete("godv", "_id=2", null);
        database.close();
        Toast.makeText(this, "deleteCount =" + deleteCount, Toast.LENGTH_LONG).show();
    }

    public void Query(View view) {
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        //参数二后 查询所有字段设置为null
//        Cursor cursor = database.query("godv", null, null, null, null, null, null);

        //根据条件查询
        Cursor cursor = database.query("godv", null, "_id=?", new String[]{"3"}, null, null, null);
//得到 数据个数
        int count = cursor.getCount();
        //是否有下一个
        while (cursor.moveToNext()) {
            int id = cursor.getInt(0);
            String name = cursor.getString(1);
            //动态查
//            int age = cursor.getInt(cursor.getColumnIndex("age"));
            Log.i("godv", id + ":" + name);
        }
        cursor.close();
        database.close();
        Toast.makeText(this, "count =" + count, Toast.LENGTH_LONG).show();

    }


    //事务
    //update person set age = 2 where _id = 1
    //update person set age = 3 where _id = 3
    public void TextTransaction(View view) {
        SQLiteDatabase database = null;
        try {
            DBHelper dbHelper = new DBHelper(this, 2);
            database = dbHelper.getReadableDatabase();

            //事务处理的步骤
            //在连接获取后开启事务
            database.beginTransaction();


            ContentValues values = new ContentValues();
            values.put("age", 2);
            int update = database.update("godv", values, "_id=?", new String[]{"1"});
            Log.i("godv", "update = " + update);

            boolean flag = true;
//            if (flag) {
//                throw new RuntimeException();
//            }

            values = new ContentValues();
            values.put("age", 3);
            int update1 = database.update("godv", values, "_id=?", new String[]{"3"});
            //在全部正常执行完毕 设置事务成功
            database.setTransactionSuccessful();


            Log.i("godv", "update1 = " + update1);
        } catch (Exception e) {
            e.printStackTrace();
            Toast.makeText(this, "异常的问题", Toast.LENGTH_LONG).show();
        } finally {
            //在finally结束事务
            if (database != null) {
                database.endTransaction();
                database.close();
            }
        }
//        Toast.makeText(this, "count =" + count, Toast.LENGTH_LONG).show();

    }


}

main-xml布局文件

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">


    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="CreateDB"
        android:text="CreateDB" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="UpdateDB"
        android:text="UpdateDB" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="Insert"
        android:text="Insert" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="Update"
        android:text="Update" />
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="Delete"
        android:text="Delete" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="Query"
        android:text="query" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="TextTransaction"
        android:text="Test Transaction" />
</LinearLayout>

数据库文件利用adb方式操作

cd data/data
cd com.android.providers.contacts 包名这里用 通信录 数据库为例
cd databases
sqlite3 xxx.db
sqlite> select * from xxx;