SqLite的基本使用

1、布局

<?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="wrap_content"
    android:orientation="vertical"
    tools:context="lpc.com.project641.MainActivity">
    <Button
        android:id="@+id/create"
        android:text="创建数据库"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal" />
    <Button
        android:id="@+id/add"
        android:text="添加数据"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal" />
    <Button
        android:id="@+id/update"
        android:text="更新数据"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal" />
    <Button
        android:id="@+id/delete"
        android:text="删除数据"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal" />
    <Button
        android:id="@+id/query"
        android:text="查询数据"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal" />
</LinearLayout>

2、mydatahelper

package lpc.com.project641;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
/**
 * 自定义DatabHelper，可以定义sql语句，继承SQLiteOpenHelper，需要重写构造方法，还有onCreate和
 * onUpgrade方法。
 */
public class MyDatabHelper extends SQLiteOpenHelper {
    //创建表语句
    public static final String CREATE_BOOK = "create table Book(" +
            "id integer primary key autoincrement," +
            "author text," +
            "price real," +
            "pages integer," +
            "name text)";
    //创建表语句
    public static final String CREATE_CATAGORY = "create table Catagory(" +
            "id integer primary key autoincrement," +
            "category_name text," +
            "category_code integer)";
    //创建表语句
    public static final String CREATE_CATAGORY1 = "create table Catagory1(" +
            "id integer primary key autoincrement," +
            "category_name1 text," +
            "category_code1 integer)";
    private Context mContext;
    public MyDatabHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        mContext = context;
    }
    //执行sql语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATAGORY);
        db.execSQL(CREATE_CATAGORY1);
        Toast.makeText(mContext,"创建成功",Toast.LENGTH_SHORT).show();
    }
    //升级或改写数据时，执行此逻辑，
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists Book");
        db.execSQL("drop table if exists Catagory");
        db.execSQL("drop table if exists Catagory1");
        onCreate(db);
    }
}

3、MainActivity

package lpc.com.project641;
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;
public class MainActivity extends AppCompatActivity {
    //创建一个MyDatabHelper 对象
    private MyDatabHelper dbHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //设置dbHelper对象的4个参数，
        /**
         * this             当前对象
         * BookStore.db     数据库名字
         * null             自定义的Cursor 一般传入null
         * 3                版本号，进行数据更新时，版本号要比上一个版本号高
         * */
        dbHelper = new MyDatabHelper(this,"BookStore.db",null,3);
        //获取4个按钮
        Button create = (Button) findViewById(R.id.create);
        Button add = (Button) findViewById(R.id.add);
        Button update = (Button) findViewById(R.id.update);
        Button delete = (Button) findViewById(R.id.delete);
        Button query = (Button) findViewById(R.id.query);
        Button shiwu = (Button) findViewById(R.id.shiwu);
        //创建数据库
        create.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
            }
        });
        //添加数据
        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put("name","刘朋程");
                values.put("author","lpc");
                values.put("pages",454);
                values.put("price",16.96);
                db.insert("Book",null,values);
                values.clear();
                values.put("name","李莉");
                values.put("author","lili");
                values.put("pages",555);
                values.put("price",19.95);
                db.insert("Book",null,values);
            }
        });
        //升级数据
        update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put("price",11.11);
                db.update("Book",values,"name=?",new String[]{"刘朋程"});
            }
        });
        //删除数据
        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                db.delete("Book","pages > ?",new String[]{"500"});
            }
        });
        //查询数据
        query.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                Cursor cursor = db.query("Book",null,null,null,null,null,null);
                if (cursor.moveToNext()){
                    do {
                        String name = cursor.getString(cursor.getColumnIndex("name"));
                        String author = cursor.getString(cursor.getColumnIndex("author"));
                        String pages = cursor.getString(cursor.getColumnIndex("pages"));
                        String price = cursor.getString(cursor.getColumnIndex("price"));
                        Log.d("TAG", "name is " + name);
                        Log.d("TAG", "author is " + author);
                        Log.d("TAG", "pages is " + pages);
                        Log.d("TAG", "price is " + price);
                    }while (cursor.moveToNext());
                }
                cursor.close();
            }
        });
        /**
         * 功能说明：事务的基本使用
         * 事务的特性可以保证让某一系列的操作要么全部完成，要不全不完成，
         * 期间如果发生 异常，就不会完成事务。
         * */
        shiwu.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                db.beginTransaction();
                try {
                    db.delete("Book", null, null);
                    ContentValues values = new ContentValues();
                    values.put("name", "中通快递");
                    values.put("author", "kuaidi");
                    values.put("pages", 720);
                    values.put("price", 20.85);
                    db.insert("Book", null, values);
                    db.setTransactionSuccessful();
                }catch (Exception e){
                    e.printStackTrace();
                } finally {
                    db.endTransaction();
                }
            }
        });
    }
}

来自为知笔记(Wiz)