Android下利用SQLite数据库实现增删改查

浏览数：44 / 时间：2015年06月11日

1：首先介绍如何利用adb查看数据库

1： adb shell

2： cd /data/data/包名/databases

3: sqlite3 数据库

4 接下来就可以进行数据库的sql语法的使用了

bean对象：

public class Person {
    
    private int id;
    private String name;
    private String number;
    
}

View Code

数据库的创建以及表的创建：

package com.example.db;

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

public class BaseDao extends SQLiteOpenHelper{

    /**
     * 数据库的构造方法， 用来定义数据库的名称， 数据库查询结果集， 数据库的版本号
     * 
     */
    public BaseDao(Context context) {
        super(context, "person.db", null, 1);
    }
    
    /**
     *数据库别创建的时候调用的方法
     * 
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //初始化数据库的表结构
        db.execSQL("create table person (id integer primary key autoincrement, name varchar(20), number varchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        
    }

}

View Code

利用纯SQL语句实现增删改查

package com.example.dao;

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

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

import com.example.bean.Person;
import com.example.db.BaseDao;

public class PersonDaoSQL {
    
    private BaseDao helper; //helper
    
    /**
     * 在构造方法中实例化helper
     * @param context
     */
    public PersonDaoSQL(Context  context) {
        helper = new BaseDao(context);
    }
    
    /**
     * 增
     * @param person
     */
    public void add(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("insert into person (name, number) values(?, ?)", new String[] {person.getName(), person.getNumber()});
        db.close();
    }
    
    /**
     * 按姓名查
     * @param person
     * @return
     */
    public boolean find(Person person) {
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person where name = ?", new String[] {person.getName()});
        boolean result = cursor.moveToNext();
        cursor.close();
        db.close();
        return result;
    }
    
    /**
     * 删除一条记录
     */
    public void delete(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("delete from person where name = ?", new String[]{person.getName()});
        db.close();
    }
    
    /**
     * 更新
     */
    public void update(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("update person set number = ? where name = ?", new String[]{person.getNumber(),person.getName()});
        db.close();
    }
    public List<Person> findAll() {
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select id, name, number from person", null);
        List<Person> persons = new ArrayList<Person>();
        Person person = null;
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String number = cursor.getString(cursor.getColumnIndex("number"));
            person = new Person(id, name, number);
            persons.add(person);
        }
        cursor.close();
        db.close();
        return persons;
    }

}

View Code

利用API实现增删改查：

package com.example.dao;

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

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

import com.example.bean.Person;
import com.example.db.BaseDao;

public class PersonDaoAPI {
    
    private BaseDao helper; //helper
    /**
     * 在构造方法中实例化helper
     * @param context
     */
    public PersonDaoAPI(Context  context) {
        helper = new BaseDao(context);
    }
    
    public long add(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put("name", person.getName());
        values.put("number", person.getNumber());
        long result = db.insert("person", null, values);
        
        db.close();
        return result;
    }
    
    public int delete(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        
        int result = db.delete("person", "name = ?", new String[] {person.getName()});        
        db.close();
        
        return result;
    }
    
    public int update(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put("number", person.getNumber());
        
        int result = db.update("person", values, "name=?", new String[] {person.getName()});    
        db.close();
        
        return result;
    }
    
    public boolean find(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        
        Cursor cursor = db.query("person", null, "name = ?", new String[]{person.getName()}, null, null, null);
        boolean result = cursor.moveToNext();
        
        db.close();
        return result;
    }
    
    public List<Person> findAll() {
        SQLiteDatabase db = helper.getWritableDatabase();
        
        Cursor cursor = db.query("person", null, null, null, null, null,null);
        List<Person> persons = new ArrayList<Person>();
        Person person = null;
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String number = cursor.getString(cursor.getColumnIndex("number"));
            person = new Person(id, name, number);
            persons.add(person);
        }
        
        db.close();
        return persons;
    }
    

}

View Code

利用Junit进行测试，

注意一定要在AndroidManifest.xml中添加：

和

<uses-library android:name="android.test.runner" />

package com.example.test;

import java.util.List;

import android.test.AndroidTestCase;

import com.example.bean.Person;
import com.example.dao.PersonDaoAPI;
import com.example.dao.PersonDaoSQL;
import com.example.db.BaseDao;

public class TestPersonDB extends AndroidTestCase {
    /**
     * 测试数据库的创建
     * @throws Exception
     */
    public void testCreateDB() throws Exception{
        BaseDao helper = new BaseDao(getContext());
        helper.getReadableDatabase();
    }
    
    /**
     * 测试添加
     */
    public void testAdd() {
        //PersonDaoSQL dao = new PersonDaoSQL(getContext());
        PersonDaoAPI dao = new PersonDaoAPI(getContext());
        for (int i = 0; i < 10 ; ++i) {
            Person person = new Person("lisi" + i + 1, "1347515770" + i);
            dao.add(person);
        }
    }
    /**
     * 测试修改
     */
    public void testUpdate() {
        //PersonDaoSQL dao = new PersonDaoSQL(getContext());
        PersonDaoAPI dao = new PersonDaoAPI(getContext());
        Person person = new Person("lisi" + 11, "1347515770" + "x");
        dao.update(person);
    }
    
    /**
     * 测试单个查询查询
     */
    public void testFind() {
        //PersonDaoSQL dao = new PersonDaoSQL(getContext());
        PersonDaoAPI dao = new PersonDaoAPI(getContext());
        Person person = new Person("lisi" + 21, "1347515770" + "x");
        boolean result = dao.find(person);
        assertEquals(true, result);
    }
    
    /**
     * 查询所有
     */
    public void testFindAll() {
        //PersonDaoSQL dao = new PersonDaoSQL(getContext());
        PersonDaoAPI dao = new PersonDaoAPI(getContext());
        List<Person> persons = dao.findAll();
        for (Person person : persons) {
            System.out.println(person.getName());
        }
    }
    
    /**
     * 删除
     */
    public void testDelete() {
        PersonDaoSQL dao = new PersonDaoSQL(getContext());
        Person person = new Person("lisi" + 21, "1347515770" + "x");
        dao.delete(person);
    }

}