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

转载于:https://www.cnblogs.com/buxinxin/p/5588767.html