一、首先写一个类继承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) )"); } /*当数据库版本更新时调用此方法*/ @Override public 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; }