安卓数据库帮助类
/** * 数据库帮助类,用于管理数据库 * @author Administrator * */public class PersonSQLiteOpenHelper extends SQLiteOpenHelper { private String tag="PersonSQLiteOpenHelper"; public PersonSQLiteOpenHelper(Context context) { super(context, "zj.db", null, 2); } /** * 数据库第一次创建时调用此方法 */ @Override public void onCreate(SQLiteDatabase db) { String sql="create table person(_id integer primary key,name varchar(20),age integer);"; db.execSQL(sql); } /** * 更新数据库的内容 */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if(oldVersion==1&&newVersion==2) { Log.i(tag, "数据库更新了"); db.execSQL("alter table person add balance interger;"); }else if(oldVersion==2&&newVersion==3) { } }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
其中onCreate只在第一次创建是调用,OnUpergrade在数据库版本号升级,如要修改数据库,为数据库增加一列,或删除一列的情况下使用
数据库操作
打开数据库
private PersonSQLiteOpenHelper mOpenHelper; public PersonDao(Context context) { mOpenHelper = new PersonSQLiteOpenHelper(context); }
由mOpenHelper得到数据库
SQLiteDatabase db= mOpenHelper.getWritableDatabase();
此时可设置版本号,便会根据版本号调用OnUpgrate
db.getVersion() db.setVersion(2)
对数据库增删改查有两种方式
插入数据 ,直接用SQL语句
public void insert(Person person) { SQLiteDatabase db= mOpenHelper.getWritableDatabase(); if(db.isOpen()) { db.execSQL("insert into person(name, age) values(?, ?);",new Object[]{person.getName(),person.getAge()}); db.close(); } }
插入数据方法二
public void insert(Person person) { SQLiteDatabase db= mOpenHelper.getWritableDatabase(); if(db.isOpen()) { ContentValues values=new ContentValues(); values.put("name", person.getName()); values.put("age", person.getAge()); long id=db.insert("person", null, values); Log.i(tag, "id:"+id); db.close(); } }
第一种方法删除与更新
public void delete(int id) { SQLiteDatabase db= mOpenHelper.getWritableDatabase(); if(db.isOpen()) { db.execSQL("delete from person where _id = ?;",new Integer[]{id}); db.close(); } } public void update(int id,String name) { SQLiteDatabase db= mOpenHelper.getWritableDatabase(); if(db.isOpen()) { db.execSQL("update person set name = ? where id = ?;",new Object[]{name,id}); db.close(); } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
第二种方法删除与更新
public void delete(int id) { SQLiteDatabase db= mOpenHelper.getWritableDatabase(); if(db.isOpen()) { String whereClause=" _id = ?"; String []whereArgs={id+""}; int count=db.delete("person", whereClause,whereArgs ); Log.i(tag, "count="+count+"行"); db.close(); } } public void update(int id,String name) { SQLiteDatabase db= mOpenHelper.getWritableDatabase(); if(db.isOpen()) { ContentValues values=new ContentValues(); values.put("name", name); int count=db.update("person", values, "_id=?", new String[]{id+""}); Log.i(tag, "修改了count="+count+"行"); db.close(); } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
第一种方法查询一项与查询所有
public List<Person> queryAll() { SQLiteDatabase db= mOpenHelper.getReadableDatabase(); if(db.isOpen()) { Cursor cursor= db.rawQuery("select * from person;", null); if(cursor!=null&&cursor.getCount()>0) { List<Person> personList=new ArrayList<Person>(); int id; String name; int age; while(cursor.moveToNext()) { id=cursor.getInt(0); name=cursor.getString(1); age=cursor.getInt(2); personList.add(new Person(id, name, age)); } db.close(); return personList; } db.close(); } return null; } public Person queryItem(int id) { SQLiteDatabase db= mOpenHelper.getReadableDatabase(); if(db.isOpen()) { Cursor cursor= db.rawQuery("select * from person where id= ?;", new String []{id+""}); if(cursor!=null&&cursor.moveToFirst()) { int id1=cursor.getInt(0); String name=cursor.getString(1); int age=cursor.getInt(2); db.close(); return new Person(id1,name,age); } db.close(); } return null; }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
第二种方法查询数据库
public List<Person> queryAll() { SQLiteDatabase db= mOpenHelper.getReadableDatabase(); if(db.isOpen()) { String [] columns={"_id","name","age"}; String selection=null; String[] selectionArgs=null; String groupBy=null; String having=null; String orderBy=null; Cursor cursor=db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy); int id; String name; int age; if(cursor!=null&&cursor.getCount()>0) { List<Person> personList=new ArrayList<Person>(); while(cursor.moveToNext()) { id=cursor.getInt(0); name=cursor.getString(1); age=cursor.getInt(2); personList.add(new Person(id,name,age)); } db.close(); return personList; } db.close(); } return null; } public Person queryItem(int id) { SQLiteDatabase db= mOpenHelper.getReadableDatabase(); if(db.isOpen()) { String [] columns={"_id","name","age"}; String selection="_id=?"; String[] selectionArgs={id+""}; String groupBy=null; String having=null; String orderBy=null; Cursor cursor=db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy); int _id; String name; int age; if(cursor!=null&&cursor.moveToFirst()) { _id=cursor.getInt(0); name=cursor.getString(1); age=cursor.getInt(2); db.close(); return new Person(_id,name,age); } db.close(); } return null; }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
数据库的事务,防止多线程并发操作
public void testTransaction() { PersonSQLiteOpenHelper openHelper=new PersonSQLiteOpenHelper(getContext()); SQLiteDatabase db= openHelper.getWritableDatabase(); if(db.isOpen()) { try { db.beginTransaction(); db.execSQL("update person set balance =balance -1000 where name='zhangsan';"); db.execSQL("update person set balance =balance +1000 where name='lisi';"); db.setTransactionSuccessful(); }finally { db.endTransaction(); } db.close(); } } public void testTransationInsert() { PersonSQLiteOpenHelper openHelper=new PersonSQLiteOpenHelper(getContext()); SQLiteDatabase db= openHelper.getWritableDatabase(); if(db.isOpen()) { long start=System.currentTimeMillis(); try { db.beginTransaction(); for(int i=0;i<10000;i++) { db.execSQL("insert into person(name, age, balance) values('wang" + i + "', " + (10 + i) + ", " + (10000 + i) + ")"); } db.setTransactionSuccessful(); }finally { db.endTransaction(); } long end=System.currentTimeMillis(); long diff=end-start; Log.i(tag, "耗时:"+diff+"ms"); db.close(); } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
Android数据库基本操作完成