一、首先写一个类继承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);
}
@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});
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) {
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 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 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;
}
