Android菜鸟笔记08:创建数据库及完成数据库增删改查

一，创建数据库：由于android应用是安装在用户手机上，所以我们需要应用有自动创建数据库的功能：

DBOpenHelper.java：

package im.qvod.service;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBOpenHelper extends SQLiteOpenHelper {

    public DBOpenHelper(Context context) {
        super(context, "qvod.db", null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {// 数据库第一次被创建的时候调用，适合生成数据库表
                                                // SQLiteDatabase封装了对数据库的所以操作，如增删改查
        db.execSQL("CREATE TABLE person(personid integer primary key autoincrement, name varchar(20))");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // 版本变化调用该方法
        db.execSQL("ALTER TABLE person ADD phone varchar(12) NULL");

    }

}

二，数据库的增删改查（包含分页获取数据、获取总记录数）:

实体类:Person.java：

package im.qvod.domain;

public class Person {
    private Integer id;
    private String name;
    private String phone;

    public Person() {
    }
    
    public Person(String name, String phone) {
        this.name = name;
        this.phone = phone;
    }

    public Person(Integer id, String name, String phone) {
        this.id = id;
        this.name = name;
        this.phone = 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;
    }

    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]";
    }
    

}

 

a,接口：PersonService.java:

package im.qvod.service;

import im.qvod.domain.Person;

import java.util.List;

public interface PersonService {
    /**
     * 添加记录
     * @param person
     */
    public abstract void save(Person person);
    /**
     * 删除记录
     * @param personid
     */
    public abstract void delete(Integer personid);
    /**
     * 更新记录
     * @param person
     */
    public abstract void update(Person person);
    /**
     * 查找记录
     * @param personid
     * @return
     */
    public abstract Person findByPersonid(Integer personid);
    /**
     * 分页获取数据
     * @param offset 跳过前面多少条记录
     * @param maxResult
     * @return
     */
    public abstract List<Person> getScrollData(int offset, int maxResult);
    /**
     * 记录数
     * @return
     */
    public abstract long getCount();

}

b,实现类:PersonServiceImpl.java:

package im.qvod.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import im.qvod.domain.Person;

public class PersonServiceImpl implements PersonService {
    private DBOpenHelper dbOpenHelper;

    public PersonServiceImpl(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() });
        // db.close();

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

    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.getId() });
    }

    public Person findByPersonid(Integer personid) {
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person where personid=?",
                new String[] { personid.toString() });
        if (cursor.moveToFirst()) {
            // 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 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()) {
            int personid = cursor.getInt(cursor.getColumnIndex("personid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String phone = cursor.getString(cursor.getColumnIndex("phone"));
            // Person person = new Person();
            persons.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();        
        return cursor.getLong(0);

    }

}

 

三，测试类：

PersonServiceTest.java：

package im.qvod.test;

import java.util.List;

import im.qvod.domain.Person;
import im.qvod.service.DBOpenHelper;
import im.qvod.service.PersonService;
import im.qvod.service.PersonServiceImpl;
import android.test.AndroidTestCase;
import android.util.Log;

public class PersonServiceTest extends AndroidTestCase {
    private static final String TAG = "PersonServiceTest";

    public void testOnCreate() throws Exception {
        DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());
        dbOpenHelper.getReadableDatabase();
        // dbOpenHelper.getWritableDatabase();
    }

    public void testSave() throws Exception {
        PersonService personService = new PersonServiceImpl(this.getContext());
        for (int i = 0; i < 20; i++) {
            personService.save(new Person("WWK" + i, "1891967152" + i));
        }

    }

    public void testDelete() throws Exception {
        PersonService personService = new PersonServiceImpl(this.getContext());
        personService.delete(21);
    }

    public void testUpdate() throws Exception {
        PersonService personService = new PersonServiceImpl(this.getContext());
        Person person = personService.findByPersonid(1);
        person.setName("LLL");
        personService.update(person);
    }

    public void testFindByPersonid() throws Exception {
        PersonService personService = new PersonServiceImpl(this.getContext());
        Log.i(TAG, personService.findByPersonid(1).toString());

    }

    public void testGetScrollData() throws Exception {
        PersonService personService = new PersonServiceImpl(this.getContext());
        List<Person> persons = personService.getScrollData(20, 5);
        for(Person person:persons){
            Log.i(TAG, person.toString());
            
        }
    }

    public void testGetCount() throws Exception {
        PersonService personService = new PersonServiceImpl(this.getContext());
        Log.i(TAG, String.valueOf(personService.getCount()));
    }

}