一、首先寫一個類繼承SQLiteOpenHelper類
重寫他的方法指定db的名稱、版本，重寫oncreat和onUpgrade方法，寫SQL語句創建表
public class MySQLiteOpenhelper extends SQLiteOpenHelper { private static String name = "person.db"; private static int version = 1; public MySQLiteOpenhelper(Context context){ super(context,name,null,version); } /* *數據庫第一次被創建時調用的方法 *db是被創建的數據庫 */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table person(id integer primary key autoincrement,name varchar(20),number varchar(20) )"); } /*當數據庫版本更新時調用此方法*/ @Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }二、創建一個DAO類對外提供增刪改查接口
其中執行增刪改查的方法可以用SQL語句也可以使用系統給出的API,下面的代碼中把兩種方法都寫了出來
public class Persondao { private MySQLiteOpenhelper helper; public Persondao(){ } public Persondao(Context context){ helper = new MySQLiteOpenhelper(context); } public void add(String name,String number){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("insert into person (name,number)values(?,?)",new Object[]{name,number}); /*ContentValues values = new ContentValues(); values.put("number",number); values.put("name", name); long id = db.insert("Person",null, values);*/ db.close(); } public boolean find(String name){ SQLiteDatabase db = helper.getWritableDatabase(); //Cursor cursor = db.rawQuery("select *from person where name=?", new String[]{name}); Cursor cursor = db.query("person", null,"name=?",new String[]{name},null,null, null); boolean result =cursor.moveToNext(); cursor.close(); db.close(); return result; } public int update(String name,String newnumber){ SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("update person set number=? where name=?",new Object[]{newnumber,name}); ContentValues values = new ContentValues(); values.put("number",newnumber); int number = db.update("person", values,"name=?",new String[]{newnumber}); db.close(); return number; } public int delet(String name){ SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("delete from person where name=?",new String[]{name}); int number = db.delete("person","name=?",new String[]{name}); db.close(); return number; } public List<Person> findAll(){ List<Person> persons = new ArrayList<Person>(); SQLiteDatabase db = helper.getWritableDatabase(); //Cursor cursor = db.rawQuery("select *from person", null); Cursor cursor = db.query("person",new String[]{"id","name","number"}, null, null, null, null, null); while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); String number = cursor.getString(cursor.getColumnIndex("number")); Person p = new Person(); persons.add(p); } db.close(); cursor.close(); return persons; }}三、增刪改查操作
import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper; public class ToDoDB extends SQLiteOpenHelper { private final static String DATABASE_NAME = "todo_db"; private final static int DATABASE_VERSION = 1; private final static String TABLE_NAME = "todo_table"; public final static String FIELD_id = "_id"; public final static String FIELD_TEXT = "todo_text"; public ToDoDB(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { /* 建立table */ String sql = "CREATE TABLE " + TABLE_NAME + " (" + FIELD_id + " INTEGER primary key autoincrement, " + " " + FIELD_TEXT + " text)"; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String sql = "DROP TABLE IF EXISTS " + TABLE_NAME; db.execSQL(sql); onCreate(db); } public Cursor select() { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db .query(TABLE_NAME, null, null, null, null, null, null); return cursor; } public long insert(String text) { SQLiteDatabase db = this.getWritableDatabase(); /* 將新增的值放入ContentValues */ ContentValues cv = new ContentValues(); cv.put(FIELD_TEXT, text); long row = db.insert(TABLE_NAME, null, cv); return row; } public void delete(int id) { SQLiteDatabase db = this.getWritableDatabase(); String where = FIELD_id + " = ?"; String[] whereValue = { Integer.toString(id) }; db.delete(TABLE_NAME, where, whereValue); } public void update(int id, String text) { SQLiteDatabase db = this.getWritableDatabase(); String where = FIELD_id + " = ?"; String[] whereValue = { Integer.toString(id) }; /* 將修改的值放入ContentValues */ ContentValues cv = new ContentValues(); cv.put(FIELD_TEXT, text); db.update(TABLE_NAME, cv, where, whereValue); }}四、寫一個JavaBean設置他的get、set方法
public class Person { private int id; private String name; private String number; public Person(){ } public Person(int id, String name, String number) { this.id = id; this.name = name; this.number = number; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getNumber() { return number; } public void setNumber(String number) { this.number = number; }}五、mainactivity中使用它
private SQLiteOpenHelper helper;Persondao dao = new Persondao();helper = new MySQLiteOpenhelper(this);helper.getWritableDatabase(); SQLiteDatabase db = helper.getWritableDatabase();
六、關于數據庫的事務處理
android開發中數據庫的操作非常慢，將所有操作打包成一個事務能夠大大的提高處理速度，其中最重要的是保證了數據的一致性，讓事務中的所有操作都能成功執行，或者失敗，或者這所有操作都回滾。
SQLiteDatabase db = helper.getWritableDatabase();db.beginTransaction();try{ //在這里執行多個數據庫操作，執行過程中可能會拋出異常 db.execSQL("update person set number=? where name=?",new Object[]{"1",jacky}); db.execSQL("update person set number=? where name=?",new Object[]{"2","sunny"}); db.setTransactionSuccessful();}catch{ //捕獲異常 throw e；}finally{ //所有操作完成結束一個事務 db.endTransaction(); db.close;}新聞熱點
疑難解答
