Android数据库操作

SQLiteDatabase数据库操作

SQLiteDatabase 打开管理工具 SQLiteExpertSetup

创建数据库

自动创建数据库功能
SQLiteOpenHelper  .getReadableDatabase() 或.getWriteableDatabase 
创建DBOpenHelper extends SQLiteOpenHelper
{
  public DBOpenHelper(Context context)
  {
    super(context,"itcast.db",null,1);    //数据库名称，版本号 默认保存目录 <包>/databases/
  }
  public void onCreate(SQLiteDatabase db) //是在数据库每一次被创建时调用的
  {
     //通过类SQLiteDatabase的实例来操作SQL语句
db.execSQL("CREATE TABLE person(personid integer primary key autoincrement,name varchar(20))");
  }
  public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)
  {
    //文件版本号发生变更时调用,如版本由1变为2
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
  }
}
  Person表类
  public class Person
  {
    private Integer id;
private String name;
private String phone;
public Integer getId()
{
 return id;
}
public void setId(Integer id)
{
 this.id=id;
}
public String getName()
{
 return name;
}
public void setName(String name)
{
 this.name=name;
}
public String getPhone()
{
 return phone;
}
public void setPhone(String phone)
{
 this.phone=phone;
}
public String toString()
{
 return "Person [id=" +id+" , name="+ name +", phone="+phone+"]";
}

  }
  业务PersonService
   private DBOpenHelper dbOpenHelper;
   public PersonService(Context context){
     this.dbOpenHelper = new DBOpenHelper(context);
   }
  public class PersonService{
    public void save(Person person)
{
 SQLiteDatabase db=dbOpenHelper.getWriteableDatabase();
 db.execSQL("insert into person(name,phone) values (?,?)", new Object[]{person.getName(),+person.getPhone()});
 //db.close();
}
public void delete(Person person)
{
 SQLiteDatabase db=dbOpenHelper.getWriteableDatabase();
 db.execSQL("delete from person where personid=?", new Object[]{id});
}
public void update(Person person)
{
 SQLiteDatabase db=dbOpenHelper.getWriteableDatabase();
 db.execSQL("update person set name=?,phone=? where personid=?",new Object[]{person.getName(),person.getPhone(),person.getId()});
}
public void find(Person person)
{
  SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
  Cursor cursor= db.rawQuery("select * from  person where personid=?", new String[]{id.toString()});  //cursor用于对查询结果集进行随机访问
  if(cursor.moveToFirst())
  {
    int personid=cursor.getInt(cursor.getColumnIndex("personid"));
String name=cursor.getString(cursor.getColumnIndex("name"));
String phone=cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone);
  }
  cursor.close();
  return null;
}
/**
*分页获取记录 
*@param offset 跳过前面多少条记录
*@param maxResult 每页获取多少条记录
*@return
**/

public List<Person> getScrollData(int offset,int maxResult)
{
      List<Person> persons = new ArrayList<Person>();
      SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
    Cursor cursor= db.rawQuery("select * from  person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});   
  while(cursor.moveToNext()) //类似ResultSet.next()
  {
     int personid=cursor.getInt(cursor.getColumnIndex("personid"));
     String name=cursor.getString(cursor.getColumnIndex("name"));
     String phone=cursor.getString(cursor.getColumnIndex("phone"));
 person.add(new Person(personid,name,phone));
  }
  cursor.close();
  return persons;
}

public long getCount()
{
 SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
 Cursor cursor=db.rawQuery("select count(*) from person",null);
 cursor.moveToFirst();
 long result=cursor.getLong(0);
 cursor.close();
 return result;
}
  }
  测试类：
  public class PersonServiceTest extends AndroidTestCase
  {
    private static final String　TAG="PersonServiceTest";
    public void testCreateDB() throws Exception
{
 DBOpenHelper dbOpenHelper=new DBOpenHelper(getContext());
 dbOpenHelper.getWriteableDatabase();
}
public void testSave() throws Exception
{
 PersonService service = new PersonService(this.getContext());
 for(int i=0;i<20;i++)
 {
 Person person =new Person("zhangxx"+i,"132234324"+i);
 service.save(person);
 }
 }
public void testDelete() throws Exception
{
 PersonService service= new PersonService(this.getContext());
 service.delete(21);
}
public void testUpdate() throws Exception
{
   PersonService service= new PersonService(this.getContext());
Person person=service.find(1);
person.setName("zhangxiaoxiao");
service.update(person);
}
public void testFind() throws Exception
{
   PersonService service= new PersonService(this.getContext());
Person person=service.find(1);
Log.i(TAG,person.toString());
}
public void testScrollData() throws Exception
{
  PersonService service= new PersonService(this.getContext());
  List<Person> person=service.getScrollData(0,5);
  for(Person person :persons)
  {
    Log.i(TAG,person.toString());
  }
}
public void testCount() throws Exception
{
    PersonService service= new PersonService(this.getContext());
long result=service.getCount();
Log.i(TAG,result+"");
 
}
  }
  
  其他PersonService
   private DBOpenHelper dbOpenHelper;
   public PersonService(Context context){
     this.dbOpenHelper = new DBOpenHelper(context);
   }
  public class PersonService{
    public void save(Person person)
{
 SQLiteDatabase db=dbOpenHelper.getWriteableDatabase();
 ContentValues values = new ContentValues();
 values.put ("name" , person.getName());
 values.put("phone" , person.getPhone());
 db.insert("person",null,values); //NULL值字段
 
 //db.execSQL("insert into person(name,phone) values (?,?)", new Object[]{person.getName(),+person.getPhone()});
 //db.close();
}
public void delete(Person person)
{
 SQLiteDatabase db=dbOpenHelper.getWriteableDatabase();
 db.delete("person" , "personid=?" , new String[] {id.toString()});
 
 //db.execSQL("delete from person where personid=?", new Object[]{id});
}
public void update(Person person)
{
 SQLiteDatabase db=dbOpenHelper.getWriteableDatabase();
 ContentValues values = new ContentValues();
 values.put ("name" , person.getName());
 values.put("phone" , person.getPhone());
 db.update("person" , values,"personid=?",new String[]{person.getId().toString()});
 //db.execSQL("update person set name=?,phone=? where personid=?",new Object[]{person.getName(),person.getPhone(),person.getId()});
}
public void find(Person person)
{
  SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
  Cursor cursor=db.query("person", null,"personid=?", new String[]{id.toString()},null,null,null});
  if(cursor.moveToFirst())
  {
    int personid=cursor.getInt(cursor.getColumnIndex("personid"));
String name=cursor.getString(cursor.getColumnIndex("name"));
String phone=cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone);
  }
  cursor.close();
  return null;
}
/**
*分页获取记录 
*@param offset 跳过前面多少条记录
*@param maxResult 每页获取多少条记录
*@return
**/

public List<Person> getScrollData(int offset,int maxResult)
{
      List<Person> persons = new ArrayList<Person>();
      SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
  Cursor cursor=db.query("person",null,null,null,null,null,"personid asc", offset+","+maxResult);  
  while(cursor.moveToNext()) //类似ResultSet.next()
  {
     int personid=cursor.getInt(cursor.getColumnIndex("personid"));
     String name=cursor.getString(cursor.getColumnIndex("name"));
     String phone=cursor.getString(cursor.getColumnIndex("phone"));
 person.add(new Person(personid,name,phone));
  }
  cursor.close();
  return persons;
}

public long getCount()
{
 SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
 Cursor cursor=db.query("person", new String[]{"count(*)"},null,null,null,null,null);
 cursor.moveToFirst();
 long result=cursor.getLong(0);
 cursor.close();
 return result;
}
  }