android第十四步创建数据库与完成添删改查

SQLite支持五中类型null,integer,real(浮点数字),text(字符串),blob(二进制对象)

SQLite可以把各个类型的数据保存到任意字段中的，而不关心字段声明的数据类型是什么比如可以Integer保存在字符串中（无数据类型特点）,列外当字段被定义为Integer主键时就只能存入整形数据了

重点关键字

getWritableDatabase数据库磁盘控件满了就报错，所以读数据尽量用getReadableDatabase

SQLiteDatabase db = dbOpenHelper.getReadableDatabase();

SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

执行一段SQL，于添加，删除，修改，使用问号是为了避免用户输入信息时输入如’违规字符

db.execSQL("insert into person(name,phone) values(?,?)",new Object[]{person.getName(),person.getPhone()});

rawQuery用于查询，Cursor随机访问游标
Cursor cursor =db.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});

cursor.moveToFirst()游标到第一个数据，这个方法返回bool类型，可用于判断是否返回值

cursor.moveToNext()游标指向下一个用于读取多条数据

获取游标中的数据还有get各种类型

 int personid =cursor.getInt(cursor.getColumnIndex("personid"));

String name =cursor.getString(cursor.getColumnIndex("name"));

 

 

数据库实例

public class DBOpenHelper extends SQLiteOpenHelper {

    public DBOpenHelper(Context context) {
        /*
         *参数3 工厂游标  null代表使用系统默认 游标
         *参数4 版本号 不能为0*/
        super(context, "zhao.db",null, 2);  //包/database
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {//数据库第一次被创建的时候调用
        db.execSQL("create table person (personid integer primary key autoincrement,name varchar(20))");

    }
    
    //数据库版本号变更 的时候
    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
        db.execSQL("alter table person add phone vacher(20) null");

    }

}

第一种访问数据库方式

public class PersonService {
    private DBOpenHelper  dbOpenHelper;
    public PersonService(Context context) {
        this.dbOpenHelper = new DBOpenHelper(context);
    }
    
    public void save(Person person){
         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();//数据库操作实例
         db.execSQL("insert into person(name,phone) values(?,?)",
                 new Object[]{person.getName(),person.getPhone()});
         
    }
    public void delete(int id){
         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();//数据库操作实例
         db.execSQL("delete from person where personid=?",
                 new Object[]{id});
    }
    public void update(Person person){
         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();//数据库操作实例
         db.execSQL("update person set name = ?,phone = ? where personid=?",
                 new Object[]{person.getName(),person.getPhone(),person.getPersonid()});
    }
    //getWritableDatabase数据库磁盘控件满了就报错，所以读数据尽量用getReadableDatabase
    public Person find(int id){
         SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
         //Cursor随机访问游标
         Cursor cursor =db.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});
         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;
    }
    public List<Person> getScrollData(int start,int end){
         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(start),String.valueOf(end)});
         while(cursor.moveToNext()){
             int personid =cursor.getInt(cursor.getColumnIndex("personid"));
             String name =cursor.getString(cursor.getColumnIndex("name"));
             String phone =cursor.getString(cursor.getColumnIndex("phone"));
             persons.add(new Person(personid,name,phone));
         }
         cursor.close();
         return persons;
    }
    public long getConut(){
         SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
         Cursor cursor =db.rawQuery("select count(*) from person",null);
         cursor.moveToFirst();
         long result = cursor.getLong(0);
         return result;
    }

第二种数据访问方式

public class OtherPersonService {
    private DBOpenHelper  dbOpenHelper;
    public OtherPersonService(Context context) {
        this.dbOpenHelper = new DBOpenHelper(context);
    }
    
    public void save(Person person){
         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();//数据库操作实例
         ContentValues values = new ContentValues();
         values.put("name", person.getName());
         values.put("phone", person.getPhone());
         db.insert("person", null, values);

    }
    
    public void delete(int id){
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.delete("person", "personid=?", new String[]{String.valueOf(id)});
    }
    
    public void update(Person person){
         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();//数据库操作实例
         ContentValues values = new ContentValues();
         values.put("name", person.getName());
         values.put("phone", person.getPhone());
         db.update("person", values,"personid=?", new String[]{String.valueOf(person.getPersonid())});
    }
    //getWritableDatabase数据库磁盘控件满了就报错，所以读数据尽量用getReadableDatabase
    public Person find(int id){
         SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
         Cursor cursor= db.query("person",null,
                 "personid=?", 
                 new String[]{String.valueOf(id)},
                 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;
    }
    public List<Person> getScrollData(int start,int end){
         List<Person> persons = new ArrayList<Person>();
         SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
         
         Cursor cursor= db.query("person", null, null,null,
                                null, null, "personid asc", start+","+end);
         
         while(cursor.moveToNext()){
             int personid =cursor.getInt(cursor.getColumnIndex("personid"));
             String name =cursor.getString(cursor.getColumnIndex("name"));
             String phone =cursor.getString(cursor.getColumnIndex("phone"));
             persons.add(new Person(personid,name,phone));
         }
         cursor.close();
         return persons;
    }
    public long getConut(){
         SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
         Cursor cursor= db.query("person", new String[]{"count(*)"}, null, null, null, null, null);
         cursor.moveToFirst();
         long result = cursor.getLong(0);
         return result;
    }