SQLite对数据进行增删改查

数据库

1.无数据类型(最好加上数据类型)

2.SQLite可以解析大部分标准的SQL语句

3.SELECT last_insert_rowid() 获得自增长后的id值

 

首先建表：

public class DBOpenHelpr extends SQLiteOpenHelper {

    public DBOpenHelpr(Context context) {
        //3.游标工厂，Null使用系统默认的游标 2.指定数据库名称 4.版本号
        super(context, "itcast.db", null, 1);
    }

    //数据库第一次被创建的时候调用，SQLiteDatabase 封装了数据库的所有操作
    @Override
    public void onCreate(SQLiteDatabase db) {
    //生成应用时要用的数据库表
      db.execSQL("CREATE TABLE person(" +
              "personid integer primary key autoincrement," +
              "phone varchar(12)"+
              "name varchar(20)))");
    }
    //数据库文件版本号发生变更时要用
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVerson, int newVerson) {
      //向数据库表中增加phone
        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 Person(Integer id, String name, String phone) {
        this.id = id;
        this.name = name;
        this.phone = phone;
    }

    public Person() {
    }

 

 

增删改查功能：

 

public class PersonService {
    private DBOpenHelpr dbOpenHelpr;

    public PersonService(Context context) {
        this.dbOpenHelpr = new DBOpenHelpr(context);
    }

    //增加数据
    public void save(Person person){
        SQLiteDatabase db = dbOpenHelpr.getWritableDatabase();
        ContentValues contentValues  = new ContentValues();
        contentValues.put("name","yanxi");
        contentValues.put("phone","12222");
        //2.NULL值字段

//共有下列两种方法增加数据
        db.insert("person",null,contentValues);
//        db.execSQL("insert into person(name,phone) " +
//                "values(?,?)",new Object[]{person.getName(),person.getPhone()});
        //在应用中只有一个地方使用数据库可以不用关数据库
    }
    //删除数据
    public void delete(Integer id){
        SQLiteDatabase db = dbOpenHelpr.getWritableDatabase();
        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 = dbOpenHelpr.getWritableDatabase();
        ContentValues contentValues  = new ContentValues();
        contentValues.put("name",person.getName());
        contentValues.put("phone",person.getPhone());
        db.update("person",contentValues,"personid=?",new String[]{person.getId().toString()});
//        db.execSQL("update person set name=?,phone=? where personid=?",
//                new Object[]{person.getName(),person.getPhone(),person.getId()});
    }
    //查询数据
    public Person find(Integer id){
        //dbOpenHelpr.getReadableDatabase()默认开始会调用dbOpenHelpr.getWritableDatabase()，
        //但是如果磁盘满了之后，就只会以只读的方式打开。
        SQLiteDatabase db = dbOpenHelpr.getReadableDatabase();
        //2.字段列表部分
        Cursor cursor = db.query("person",null,"personid=?",new String[]{id.toString()},null,null,null);
//        Cursor cursor = db.rawQuery("select * from person where personid=?",new String[]{id.toString()});
        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> getScollData(int offset, int maxResult){
        List<Person> list = new ArrayList<>();
        SQLiteDatabase db = dbOpenHelpr.getReadableDatabase();
        Cursor cursor = db.query("person",null,null,null,null,"perrson asc",offset+","+maxResult);

        //Cursor cursor = db.rawQuery("select * from person order by personid asc limit ?,?",
          //      new String[]{String.valueOf(offset),String.valueOf(maxResult)});
        while(cursor.moveToNext()){
            int personid = cursor.getInt(cursor.getColumnIndex("personid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String phone = cursor.getString(cursor.getColumnIndex("phone"));
            list.add(new Person(personid,name,phone));
        }
        cursor.close();
        return null;
    }
    //获得数据库表的记录数
    public long getCount(){
        SQLiteDatabase db = dbOpenHelpr.getReadableDatabase();
        Cursor cursor =db.query("person",new String[]{"count(*)"},null,null,null,null,null);
//        Cursor cursor = db.rawQuery("select count (*)  form person",null);
        cursor.moveToFirst();
        long result = cursor.getLong(0);
        cursor.close();
        return result;
    }

}

 

 

测试类：

 

public class PersonServiceTest extends AndroidTestCase {
    private static final String TAG = "PeraonServiceTAG";
    public void testCreateDB() throws Exception{
        DBOpenHelpr dbOpenHelpr = new DBOpenHelpr(getContext());
        //第一次调用该方法，创建数据库，默认目录：<包>database
        dbOpenHelpr.getWritableDatabase();
    }

    //增加数据测试类
    @Test
    public void testSave() throws Exception{
        PersonService p = new PersonService(this.getContext());
        Person person = new Person("yanxi","1777");
        p.save(person);
    }
    //删除数据测试类
    public void testDelete() throws Exception{
        PersonService p = new PersonService(this.getContext());
        p.delete(1);
    }
    //修改数据测试类
    public void testUpDate() throws Exception{
        PersonService p = new PersonService(this.getContext());
        Person person = p.find(1);
        person.setName("weimiao");
        p.update(person);
    }
    //查找数据测试类
    public void testFind() throws Exception{
        PersonService p = new PersonService(this.getContext());
        Person person = p.find(1);
        Log.i(TAG,person.toString());
    }
    //分页数据测试类
    public void testScollData() throws Exception{
        PersonService p = new PersonService(this.getContext());
        p.getScollData(0,5);
    }
    //数据记录数测试类
    public void testCount() throws Exception{
        PersonService p = new PersonService(this.getContext());
        long i = p.getCount();
        Log.i(TAG,String.valueOf(i));

    }
}