Android SQL statements enable addition, deletion, modification, and query of databases. androidsql

Android SQL statements enable addition, deletion, modification, and query of databases. androidsql

This topic describes how to add, delete, modify, and query databases in android.

Review SQL Syntax:

* Add

Insert into info (name, phone) values ('wuyudong', '123 ')

* Delete

Delete from person where name = 'wuyudong'

* Change

Update person set number = '000000' where name = 'wuyudong'

* Query

Select * from person

Select * from person where name = 'wuyudong'

The database file is stored in/data/package name/databases/xxx. db

The following code is used to complete related operations:

First define a Person class

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

The code that implements the related operations is as follows:

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 PersonSQLiteOpenHelp Er (context);}/*** add a record to the database ** @ param name * @ param number * phone */public void add (String name, String number) {SQLiteDatabase db = helper. getWritableDatabase (); db.exe cSQL ("insert into person (name, number) values (?,?) ", New Object [] {name, number}); db. close ();}/*** check whether the record exists ** @ param name * name return true, false does not exist */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 * name of the person to be modified * @ param newnumber * New Number */public void update (String name, string newnumber) {SQLiteDatabase db = helper. getReadableDatabase (); db.exe cSQL ("update person set number =? Where name =? ", New Object [] {newnumber, name}); db. close ();}/*** delete a record * @ param name */public void delete (String name) {SQLiteDatabase db = helper. getReadableDatabase (); db.exe cSQL ("delete from person where name =? ", New Object [] {name}); db. close ();}/*** return all database information * @ 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 ;}}

The complete test code is as follows:

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