插入语句：insert into person (name,number) values(‘zhangsan’,20)
查询语句：
(1)select * from person
(2)select * from person wherename=’zhangsan’
更新语句：update person set number='200' where name='zhangsan'
删除语句：delete from person where name='zhangsan'
使用数据库
一共要有四个类
（1）PersonsqliteOpenHelper（数据库类）
（2）PersonDB（操作数据库的类）
（3）PersonDao（对数据库进行增删改查的操作工具类）
（4）Person对象类
package com.example.db; import android.content.Context; import android.database.sqlite.sqliteDatabase; import android.database.sqlite.sqliteOpenHelper; public class PersonsqliteOpenHelper extends sqliteOpenHelper { /* * 数据库的构造方法，用来定义数据库的名称，数据库查询的结果集，数据库的版本 * */ public PersonsqliteOpenHelper(Context context) { super(context,"persondb",null,1); // TODO Auto-generated constructor stub } /* * 数据库第一次被创建调用的方法 * */ @Override public void onCreate(sqliteDatabase db) { // TODO Auto-generated method stub //初始化数据库的表结构 db.execsql("create table person (id integer primary key autoincrement,name varchar(20),number varchar(20))"); } @Override public void onUpgrade(sqliteDatabase arg0,int arg1,int arg2) { // TODO Auto-generated method stub } }
PersonDB代码：
package com.example.test; import java.util.List; import android.database.sqlite.sqliteDatabase; import android.test.AndroidTestCase; import com.example.db.PersonsqliteOpenHelper; import com.example.db.dao.PersonDao; import com.example.db.dao.domain.Person; 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("jim","0181"); dao.add("peter","0007"); } public void testfind() throws Exception{ PersonDao dao = new PersonDao(getContext()); boolean result = dao.find("jim"); } public void testDel() throws Exception{ PersonDao dao = new PersonDao(getContext()); dao.delete("jim"); } public void testUpdate() throws Exception{ PersonDao dao = new PersonDao(getContext()); dao.update("jim","9999"); } public void findAll() throws Exception{ PersonDao dao = new PersonDao(getContext()); List<Person> persons = dao.findAll(); for(Person per:persons){ System.out.println(per.toString()); } } }
PersonDao代码：
package com.example.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.example.db.PersonsqliteOpenHelper; import com.example.db.dao.domain.Person; public class PersonDao { private PersonsqliteOpenHelper helper; //在构造函数中进行初始化 public PersonDao(Context context) { helper = new PersonsqliteOpenHelper(context); } /* * 添加一条数据到数据库 * name，number * * */ public void add(String name,String number){ sqliteDatabase db = helper.getWritableDatabase(); db.execsql("insert into person (name,number) values (?,?)",new Object[]{"zhangsan",50}); db.close(); } //查询记录是否存在 //返回boolean public boolean find(String name){ sqliteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.rawQuery("select * from person where name=?",new String[]{name}); boolean result = cursor.moveToNext(); cursor.close(); db.close(); return result; } //更新数据 public void update(String name,String newnumber){ sqliteDatabase db = helper.getWritableDatabase(); db.execsql("update person set number=? where name=?",new Object[]{name,newnumber}); db.close(); } //删除数据 public void delete(String name){ sqliteDatabase db = helper.getWritableDatabase(); db.execsql("delete from person where name=?",new String[]{name}); db.close(); } //查询所有的数据 public List<Person> findAll(){ sqliteDatabase db = helper.getWritableDatabase(); List<Person> persons = new ArrayList<Person>(); Cursor cursor = db.rawQuery("select * from person",null); //Cursor cursor = db.rawQuery("select id,name,number 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 per = new Person(id,number); //把对象添加进集合中 persons.add(per); } //关闭数据库的连接 db.close(); return persons; } }
附：PersonDao代码中的db.execsql()直接执行sql语句可能会引起某些不可预知的错误，并且大部分人不熟sql语句，所以google工程师把增删改查的四个语句封装成了API，供直接使用，说以上面的红色部分的语句可改如下
public long add(String name,String number){
sqliteDatabase db = helper.getWritableDatabase();
//db.execsql("insert into person (name,50});
ContentValues values = new ContentValues();
values.put("name","zhangsan");
values.put("number",50);
long num = db.insert("person",values);
db.close();
return num;
}
//查询记录是否存在
//返回boolean
public boolean find(String name){
sqliteDatabase db = helper.getWritableDatabase();
//Cursor cursor = db.rawQuery("select * from person where name=?",new String[]{name});
Cursor cursor = db.query("person","nume=?",new String[]{name},null);
boolean result = cursor.moveToNext();
cursor.close();
db.close();
return result;
}
//更新数据
public int update(String name,String newnumber){
sqliteDatabase db = helper.getWritableDatabase();
//db.execsql("update person set number=? where name=?",newnumber});
ContentValues values = new ContentValues();
values.put("number",newnumber);
int number = db.update("person",values,"name=?",new String[]{name});
db.close();
return number;
}
//删除数据
public int delete(String name){
sqliteDatabase db = helper.getWritableDatabase();
//db.execsql("delete from person where name=?",new String[]{name});
int number = db.delete("person",new String[]{name});
db.close();
return number;
}
Person代码：
package com.example.db.dao.domain;
public class Person {
private int id;
private String name;
private String number;
public Person(){}
public Person(int id,String name,String number) {
super();
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;
}
public String toString(){
return this.id+" "+this.name+" "+this.number;
}
}
