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