安卓数据库帮助类
/**
* 数据库帮助类，用于管理数据库
* @author Administrator
*
*/
public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {
private String tag="PersonSQLiteOpenHelper";
public PersonSQLiteOpenHelper(Context context) {
//数据库名，数据库版本号
super(context, "zj.db", null, 2);
// TODO Auto-generated constructor stub
}
/**
* 数据库第一次创建时调用此方法
*/
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
//操作数据库
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) {
// TODO Auto-generated method stub
if(oldVersion==1&&newVersion==2)
{
Log.i(tag, "数据库更新了");
//在表中添加一个余额列
db.execSQL("alter table person add balance interger;");
}else if(oldVersion==2&&newVersion==3)
{
//其他操作
}
}
}
其中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();
}
}
第二种方法删除与更新
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();
}
}
第一种方法查询一项与查询所有
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;
}
第二种方法查询数据库
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;
}
数据库的事务，防止多线程并发操作
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';");
//ATM机损坏
//int result=10/0;
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();
}
}
Android数据库基本操作完成
