安卓数据库帮助类
/**
* 数据库帮助类,用于管理数据库
* @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数据库基本操作完成