一、创建数据库帮助类
新建一类继承Android系统的SQLiteOpenHelper,重写父类的构造方法和onCreate()与onUpgrate()方法,
在类的构造方法中创建数据库,在onCreate()方法中创建表,onUpgrade()方法是在数据库升级时调用,一般用来修改数据库中表的表结构。
在数据库中创建表的语句是:
create table stu(_id integer primay key autoincrement,name varchar(20),num varchar(20))
修改数据库中表的表结构的语句是:
alter table stu add phone varchar(20);
public class MyDbOpenHelper extends SQLiteOpenHelper { public MyDbOpenHelper (Context context) { super (context, "student.db", null, 3); } //第一次创建数据库时执行 @Override public void onCreate (SQLiteDatabase db) { db.execSQL ("create table stu(_id integer primary key autoincrement,name varchar(20),num varchar(20))"); } //当数据库升级时,调用该方法 @Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) { switch (oldVersion){ case 1: db.execSQL ("alter table stu add sex varchar(20)"); db.execSQL ("alter table stu add phone varchar(20)"); break; case 2: db.execSQL ("alter table stu add phone varchar(20)"); break; } } }二、创建数据库帮助类对象,获取一个可写的数据库对象。
private void initDataBase () { MyDbOpenHelper mMyDbOpenHelper = new MyDbOpenHelper (this); mSQLiteDatabase = mMyDbOpenHelper.getWritableDatabase (); }三、数据库中增删查改的语句
增:insert into stu(name,num) values('ysy','1001');
删:delete from stu where _id='1';
查:select * from stu;
改:update stu set name = 'ysy1';
四、使用Sql语句进行增删查改
增:
private void insert () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getWritableDatabase (); db.execSQL ("insert into stu(name,num) values(?,?)",new String[]{"zhangsan","1001"}); db.close (); }
删:
private void delete () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getWritableDatabase (); db.execSQL ("delete from stu"); db.close (); }
查:
private void qurey () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getReadableDatabase (); Cursor cursor = db.rawQuery ("select * from stu", null); while (cursor.moveToNext ()){ String id = cursor.getString (0); String name = cursor.getString (1); String num = cursor.getString (2); Log.d ("ysy","id="+id+"name="+name+"num="+num); } db.close (); }
改:
private void update () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getWritableDatabase (); db.execSQL ("update stu set name='ysy'"); db.close (); }五、使用AndroidApi的方式进行数据库的增删查改
增:
private void insertAndroidAPI () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getWritableDatabase (); ContentValues values = new ContentValues (); values.put ("name","zhangsan"); values.put ("num","1001"); long res = db.insert ("stu", null, values); if(res==-1){ showToast ("失败"); }else{ showToast ("成功"+res); } db.close (); }
删:
private void deleteAndroidAPI () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getWritableDatabase (); int res = db.delete ("stu", null, null); if(res>0){ showToast ("成功"+res); }else{ showToast ("失败"); } db.close (); }
查:
private void qureyAndroidAPI () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getReadableDatabase (); //Cursor cursor = db.rawQuery ("select * from stu", null); Cursor cursor = db.query ("stu", null, null, null, null, null, null, null); while (cursor.moveToNext ()){ String id = cursor.getString (0); String name = cursor.getString (1); String num = cursor.getString (2); Log.d ("ysy","id="+id+"name="+name+"num="+num); } db.close (); }
改:
private void updateAndroidAPI () { MyDbOpenHelper myDbOpenHelper = new MyDbOpenHelper (this); SQLiteDatabase db = myDbOpenHelper.getWritableDatabase (); //db.execSQL ("update stu set name='ysy'"); ContentValues values = new ContentValues (); values.put ("name","lisi"); int res = db.update ("stu", values, null, null); if(res>0){ showToast ("成功"+res); }else{ showToast ("失败"); } db.close (); }六、总结
Android数据库的操作,也就是增删查改,它有两种方式,一种SQL语句方式,一种AndroidAPI的方式,相对而言AndroidApi的方式简单些,不容易出错,推荐使用。