sqlite資料庫學習

標籤：

1.0版代碼:

 

package com.swust.sqlitedatabase.test;import com.swust.sqlitedatabase.myOpenHelper;import android.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;public class TestCase extends AndroidTestCase {    public void test(){        //第一個參數：this->現在應用沒啟動，沒有，真正的時候用this。所以擷取虛擬上下文getContext()        //第二個參數：資料庫檔案        //第四個參數：版本號碼        //recNO欄位是系統的，無視        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);        //如果資料庫不存在，先建立資料庫，再擷取可讀可寫資料庫物件，如果資料存在，就直接開啟        SQLiteDatabase db=moh.getWritableDatabase();        //建立對象也可讀可寫,如果儲存空間滿了，那麼返回唯讀資料庫物件        //SQLiteDatabase db=moh.getReadableDatabase();    }    public void insert(){        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);        SQLiteDatabase db=moh.getWritableDatabase();        //資料庫char ,double等都是字串，因為這個輕量級資料不檢測資料        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**大學",25.34,"12.345"});        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**大學",25.34,"12.345"});        db.close();    }}

插入後：

刪除：

public void delete(){        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);        SQLiteDatabase db=moh.getWritableDatabase();        db.execSQL("delete from person where name =?",new Object[]{"王**"});    }

之後，欄位名字為“王**”的被刪除

效果：

-------------------------------------

代碼未最佳化：

package com.swust.sqlitedatabase.test;import com.swust.sqlitedatabase.myOpenHelper;import android.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;public class TestCase extends AndroidTestCase {    public void test(){        //第一個參數：this->現在應用沒啟動，沒有，真正的時候用this。所以擷取虛擬上下文getContext()        //第二個參數：資料庫檔案        //第四個參數：版本號碼        //recNO欄位是系統的，無視        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);        //如果資料庫不存在，先建立資料庫，再擷取可讀可寫資料庫物件，如果資料存在，就直接開啟        SQLiteDatabase db=moh.getWritableDatabase();        //建立對象也可讀可寫,如果儲存空間滿了，那麼返回唯讀資料庫物件        //SQLiteDatabase db=moh.getReadableDatabase();    }    public void insert(){        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);        SQLiteDatabase db=moh.getWritableDatabase();        //資料庫char ,double等都是字串，因為這個輕量級資料不檢測資料        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**大學",25.34,"12.345"});        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王樹青大學",25.34,"12.345"});        db.close();    }    public void delete(){        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);        SQLiteDatabase db=moh.getWritableDatabase();        db.execSQL("delete from person where name =?",new Object[]{"王**"});    }}

最佳化後：

package com.itheima.sqlitedatabase.test;import com.itheima.sqlitedatabase.MyOpenHelper;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;public class TestCase extends AndroidTestCase {    //此時測試架構還沒有初始化完畢，沒有虛擬內容物件//    private MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 1);    private MyOpenHelper oh;    private SQLiteDatabase db;    public void test(){        //getContext():擷取一個虛擬上下文        MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 1);        //如果資料庫不存在，先建立資料庫，再擷取可讀可寫的資料庫物件，如果資料庫存在，就直接開啟        SQLiteDatabase db = oh.getWritableDatabase();        //如果儲存空間滿了，那麼返回唯讀資料庫物件//        SQLiteDatabase db = oh.getReadableDatabase();    }        //測試架構初始化完畢之後，在測試方法執行之前，此方法調用    @Override    protected void setUp() throws Exception {        super.setUp();                oh = new MyOpenHelper(getContext(), "people.db", null, 1);        db = oh.getWritableDatabase();    }    //測試方法執行完畢之後，此方法調用    @Override    protected void tearDown() throws Exception {        // TODO Auto-generated method stub        super.tearDown();        db.close();    }        public void insert(){        //        db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志的老婆[1]", "13000", 138438});//        db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志的兒子", 14000, "13888"});        db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志", 14000, "13888"});    }        public void delete(){        db.execSQL("delete from person where name = ?", new Object[]{"小志"});    }        public void update(){        db.execSQL("update person set phone = ? where name = ?", new Object[]{186666, "小志的兒子"});    }        public void select(){        Cursor cursor = db.rawQuery("select name, salary from person", null);                while(cursor.moveToNext()){            //通過列索引擷取列的值            String name = cursor.getString(cursor.getColumnIndex("name"));            String salary = cursor.getString(1);            System.out.println(name + ";" + salary);        }    }        public void insertApi(){        //把要插入的資料全部封裝至ContentValues對象        ContentValues values = new ContentValues();        values.put("name", "遊天龍");        values.put("phone", "15999");        values.put("salary", 16000);        db.insert("person", null, values);    }        public void deleteApi(){        int i = db.delete("person", "name = ? and _id = ?", new String[]{"小志的兒子", "3"});        System.out.println(i);    }        public void updateApi(){        ContentValues values = new ContentValues();        values.put("salary", 26000);        int i = db.update("person", values, "name = ?", new String[]{"遊天龍"});        System.out.println(i);    }        public void selectApi(){        Cursor cursor = db.query("person", null, null, null, null, null, null, null);        while(cursor.moveToNext()){            String name = cursor.getString(cursor.getColumnIndex("name"));            String phone = cursor.getString(cursor.getColumnIndex("phone"));            String salary = cursor.getString(cursor.getColumnIndex("salary"));            System.out.println(name + ";" + phone + ";" + salary);        }    }        public void transaction(){        try{            //開啟事務            db.beginTransaction();            ContentValues values = new ContentValues();            values.put("salary", 12000);            db.update("person", values, "name = ?", new String[]{"小志"});                        values.clear();            values.put("salary", 16000);            db.update("person", values, "name = ?", new String[]{"小志的兒子"});                        int i = 3/0;            //設定  事務執行成功            db.setTransactionSuccessful();        }        finally{            //關閉事務，同時提交，如果已經設定事務執行成功，那麼sql語句就生效了，反之，sql語句復原            db.endTransaction();        }    }}

 

sqlite資料庫學習