你的位置:首页 > 操作系统

[操作系统]Android SQL语句实现数据库的增删改查


本文介绍android中的数据库的增删改查

复习sql语法:

* 增

insert into info (name,phone) values ('wuyudong','111')

* 删

delete from person where name = 'wuyudong'

* 改

update person set number='119' where name='wuyudong'

* 查

select * from person

select * from person where name='wuyudong'

数据库文件是在 /data/data/包名/databases/xxx.db


下面使用代码完成相关的操作

首先定义一个Person类

package com.wuyudong.db.domain;public class Person {  private int id;  private String name;  private String number;  public Person() {      }  public Person(int id, String name, String number) {    this.id = id;    this.name = name;    this.number = number;  }  public int getId() {    return id;  }  public void setId(int id) {    this.id = id;  }  public String getName() {    return name;  }  public void setName(String name) {    this.name = name;  }  public String getNumber() {    return number;  }  public void setNumber(String number) {    this.number = number;  }}

接着实现相关操作的代码:

package com.wuyudong.db.dao;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.wuyudong.db.PersonSQLiteOpenHelper;import com.wuyudong.db.domain.Person;public class PersonDao {  private PersonSQLiteOpenHelper helper;  public PersonDao(Context context) {    helper = new PersonSQLiteOpenHelper(context);  }  /**   * 添加一条记录到数据库   *   * @param name   *      姓名   * @param number   *      电话   */  public void add(String name, String number) {    SQLiteDatabase db = helper.getWritableDatabase();    db.execSQL("insert into person (name,number) values (?,?)",        new Object[] { name, number });    db.close();  }  /**   * 查询记录是否存在   *   * @param name   *      姓名 return true存在, false不存在   */  public boolean find(String name) {    SQLiteDatabase db = helper.getReadableDatabase();    Cursor cursor = db.rawQuery("select * from person where name=?",        new String[] { name });    boolean result = cursor.moveToNext();    cursor.close();    db.close();    return result;  }  /**   *   * @param name   *      要修改的人的姓名   * @param newnumber   *      新的号码   */  public void update(String name, String newnumber) {    SQLiteDatabase db = helper.getReadableDatabase();    db.execSQL("update person set number=? where name=?", new Object[]{newnumber,name});    db.close();  }    /**   * 删除一条记录   * @param name   */  public void delete(String name) {    SQLiteDatabase db = helper.getReadableDatabase();    db.execSQL("delete from person where name=?", new Object[]{name});    db.close();  }    /**   * 返回全部的数据库信息   * @return   */  public List<Person> findAll() {    List<Person> persons = new ArrayList<Person>();    SQLiteDatabase db = helper.getReadableDatabase();    Cursor cursor = db.rawQuery("select * from 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 person = new Person(id, name, number);      persons.add(person);    }    cursor.close();    db.close();    return persons;  }}

完整的测试代码如下:

package com.wuyudong.db.test;import java.util.List;import com.wuyudong.db.PersonSQLiteOpenHelper;import com.wuyudong.db.dao.PersonDao;import com.wuyudong.db.domain.Person;import android.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;public class TestPersonDB extends AndroidTestCase {  public void testCreateDB() throws Exception {    PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());    SQLiteDatabase db = helper.getWritableDatabase();  }  public void testAdd() throws Exception {    PersonDao dao = new PersonDao(getContext());    dao.add("wuyudong", "666");  }  public void testFind() throws Exception {    PersonDao dao = new PersonDao(getContext());    boolean result = dao.find("zhangsan");    assertEquals(true, result);  }  public void testUpdate() throws Exception {    PersonDao dao = new PersonDao(getContext());    dao.update("zhangsan", "655");  }  public void testDelete() throws Exception {    PersonDao dao = new PersonDao(getContext());    dao.delete("zhangsan");  }  public void testFindall() throws Exception {    PersonDao dao = new PersonDao(getContext());    List<Person> persons = dao.findAll();    for (Person person : persons) {      System.out.println(person.toString());    }  }}