andorid SQLite數據庫的增刪改查

.xml

<?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="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.hanqi.application3.DataActivity2"
    android:orientation="vertical">

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="創建數據庫"
        android:id="@+id/bt_bt1"
        android:onClick="bt_bt1onClick"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="升級數據庫"
        android:id="@+id/bt_bt2"
        android:onClick="bt_bt2onClick"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="增加數據"
        android:id="@+id/bt_bt3"
        android:onClick="bt_bt3onClick"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查詢數據"
        android:id="@+id/bt_bt4"
        android:onClick="bt_bt4onClick"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="修改數據"
        android:id="@+id/bt_bt5"
        android:onClick="bt_bt5onClick"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="刪除數據"
        android:id="@+id/bt_bt6"
        android:onClick="bt_bt6onClick"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="事務操作"
        android:id="@+id/bt_bt7"
        android:onClick="bt_bt7onClick"/>


</LinearLayout>

.java

package com.hanqi.application3;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Toast;

public class DataActivity2 extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_data2);
    }
    //繼承SQLiteOpenHelper
    class DBHelper extends SQLiteOpenHelper
    {
        //構造方法
        //name 數據庫名
        //version數據庫版本號
        public DBHelper(String name,  int version) {
            //調用父類的構造方法，寫在第一行
            super(DataActivity2.this, name, null, version);
        }
        //回調方法
        //在創建數據庫時調用
        //什麼時候創建數據庫：連接數據庫的時候,如果數據文件不存在
        //只調用一次
        @Override
        public void onCreate(SQLiteDatabase db) {

            //1.創建數據庫的語句
            String creatTable = "create table user1 (_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,name varchar,age int)";
            db.execSQL(creatTable);

            //2.初始化數據
            ContentValues cv= new ContentValues();
            cv.put("name","tom");
            cv.put("age", 30);
            //如果不成功返回-1
            //1.第一個參數是表名，第二個參數空列的默認值，第三個字段和要插入值key/value得集合
            long l = db.insert("user1",null,cv);

            Toast.makeText(DataActivity2.this, "id="+l, Toast.LENGTH_SHORT).show();

        }
        //升級數據庫
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            //執行升級
            //  update user1 set age = 30 where _id = ?
            //? 是占位符，然後給占位符賦值
            ContentValues cv= new ContentValues();

            cv.put("age", 30);
            int rowcount = db.update("user1",cv,"_id=? and name=?",new String[]{"1","tom"});

            Log.e("TAG","rowcount = "+rowcount);

        }
    }
    //創建數據庫
    public void bt_bt1onClick(View v)
    {
        //創建
        DBHelper dh = new DBHelper("test.db",1);
        //獲取數據庫實例
        SQLiteDatabase sdd = dh.getWritableDatabase();
        sdd.close();

    }
    //升級數據庫
    public void bt_bt2onClick(View v)
    {
        //創建
        DBHelper dh = new DBHelper("test.db",2);
        //獲取數據庫實例
        SQLiteDatabase sdd = dh.getWritableDatabase();
        sdd.close();

    }
    //插入數據
    public void bt_bt3onClick(View v)
    {
        //創建
        DBHelper dh = new DBHelper("test.db",2);
        //獲取數據庫實例
        SQLiteDatabase sdd = dh.getWritableDatabase();

        ContentValues cv = new ContentValues();

        cv.put("name","tom1");
        cv.put("age", 15);
        long _id = sdd.insert("user1", null, cv);
        Log.e("TAG","_id= "+_id);

        sdd.close();

    }
    //查詢數據庫
    public void bt_bt4onClick(View v)
    {
        //創建
        DBHelper dh = new DBHelper("test.db",2);
        //獲取數據庫實例
        SQLiteDatabase sdd = dh.getWritableDatabase();
        //  全表查詢
        //返回游標
        Cursor cursor = sdd.query("user1", null, null, null, null, null, null);

        //Cursor  一開始會定位在第一條數據的上方
        // 移動游標到數據的上面，提取數據後，再繼續移動
        while (cursor.moveToNext())
        {
            long _id =cursor.getLong(cursor.getColumnIndex("_id"));
            String name = cursor.getString(1);
            int age = cursor.getInt(2);

            Log.e("TAG","_id = "+_id+"name = "+name+"age= "+age);
        }
        cursor.close();
        sdd.close();

    }
    //修改數據
    public void bt_bt5onClick(View v)
    {
        //創建
        DBHelper dh = new DBHelper("test.db",2);
        //獲取數據庫實例
        SQLiteDatabase sdd = dh.getWritableDatabase();

        ContentValues cv = new ContentValues();

        cv.put("name", "tom3");

        int count  = sdd.update("user1",cv,"_id >= 3",null);
        Log.e("TAG", "Updatecount= " + count);

        sdd.close();

    }
    //刪除數據
    public void bt_bt6onClick(View v)
    {
        //創建
        DBHelper dh = new DBHelper("test.db",2);
        //獲取數據庫實例
        SQLiteDatabase sdd = dh.getReadableDatabase();

        int count  = sdd.delete("user1","_id = 4",null);
        Log.e("TAG", "Deletcount= " + count);

        sdd.execSQL("delect from user1 where _id=3");

        sdd.close();

    }
    public void bt_bt7onClick(View v) {
        //創建
        DBHelper dh = new DBHelper("test.db", 2);
        //獲取數據庫實例
        SQLiteDatabase sdd = dh.getWritableDatabase();
        //鏈接
        sdd = SQLiteDatabase.openOrCreateDatabase("test.db",null);
        //判斷是否建表了，是否升級了
        try {


        //1.開啟事務
         sdd.beginTransaction();

        ContentValues cv = new ContentValues();

        cv.put("age", "40");

        int count = sdd.update("user1", cv, "_id = 1", null);
        //拋出異常
        boolean b = true;
        if (b) {
            //throw new RuntimeException("出現異常");
        }
        count += sdd.update("user1", cv, "_id = 2", null);
        Log.e("TAG", "Updatecount= " + count);
        //2.設置事務執行成功
        sdd.setTransactionSuccessful();
            //提交
        }
        catch (Exception e)
        {
            //回滾
            e.printStackTrace();

            Toast.makeText(DataActivity2.this, "異常信息"+e.getMessage(), Toast.LENGTH_SHORT).show();
        }
        //一定會被執行的代碼
        finally {

        //3.結束事務,1)提交 2）回滾;
        sdd.endTransaction();

        sdd.close();
        }
    }




}