SQLite Android嵌入式数据库操作

仅供自己学习使用，轻喷

=====SqliteOpenHelper.java====

public class SqliteOpenHelper extends SQLiteOpenHelper{
    
    public SqliteOpenHelper(Context context){
        //1是版本号，一般选用正整数，非0
        super(context, "mysql.db", null, 1);
    }
    
    /**
     * 数据库第一次被创建时调用(SQLiteOpenHelper.getWritableDatabase();)，以后就不再调用
     */
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE person(personid primary key autoincrement, name varchar(20)");
    }

    /**
     * 当软件版本更新，db的版本号会随之升，当db的版本号升级时会调用onUpgrade方法
     */
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("ALTER TABLE person ADD phone varchar(12) Null");    
    }
}

=====PersonService.java=====

public class PersonService {

    private SqliteOpenHelper sqliteOpenHelper;

    public PersonService(SqliteOpenHelper sqliteOpenHelper) {
        super();
        this.sqliteOpenHelper = sqliteOpenHelper;
    }

    /**
     * 使用占位符 ？可以避免用户带有'等的输入，否则导致sql语句执行错误
     * 
     */
    public void save(Person person) {
        SQLiteDatabase db = sqliteOpenHelper.getWritableDatabase();
        db.execSQL("insert into person(name, phone) values(?, ?)",
                new Object[] { person.getName(), person.getPhone() });
    }

    public void delete(int id) {
        SQLiteDatabase db = sqliteOpenHelper.getWritableDatabase();
        db.execSQL("delete from person where personid=?", new Object[] { id });
    }

    public void update(Person person) {
        SQLiteDatabase db = sqliteOpenHelper.getWritableDatabase();
        db.execSQL(
                "update person set name=?, phone=? where personid=?",
                new Object[] { person.getName(), person.getPhone(),
                        person.getId() });
    }

    public Person find(Integer id) {
        Person person = null;
        SQLiteDatabase db = sqliteOpenHelper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from person where personid=?",
                new String[] { id.toString() });
        if (cursor.moveToFirst()) {
            String personid = cursor.getString(cursor
                    .getColumnIndex("personid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String phone = cursor.getString(cursor.getColumnIndex("phone"));
            person = new Person(personid, name, phone);
        }
        cursor.close();
        return person;
    }

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

====SqliteActivity.java调用====

SqliteOpenHelper sqliteOpenHelper = new SqliteOpenHelper(getApplicationContext());
PersonService service = new PersonService(sqliteOpenHelper);
service.find(1);