SQLite对数据进行增删改查_sql

概述数据库 1.无数据类型(最好加上数据类型) 2.SQLite可以解析大部分标准的SQL语句 3.SELECT last_insert_rowid() 获得自增长后的id值首先建表： public class DBOpenHelpr extends SQLiteOpenHelper { public DBOpenHelpr(Context context) { //3.

数据库

1.无数据类型(最好加上数据类型)

2.sqlite可以解析大部分标准的sql语句

3.SELECT last_insert_rowID() 获得自增长后的ID值

首先建表：

public class DBOpenHelpr extends sqliteOpenHelper {

public DBOpenHelpr(Context context) {//3.游标工厂，Null使用系统默认的游标 2.指定数据库名称 4.版本号
super(context,"itcast.db"1);
}//数据库第一次被创建的时候调用，sqliteDatabase 封装了数据库的所有 *** 作@OverrIDe
public voID onCreate(sqliteDatabase db) {//生成应用时要用的数据库表db.execsql("CREATE table person(" +
"personID integer primary key autoincrement," "phone varchar(12)""name varchar(20)))");
//数据库文件版本号发生变更时要用onUpgrade(sqliteDatabase dboldVersonnewVerson) {//向数据库表中增加phone"ALTER table person ADD phone varchar(12) null";}

新建Person类：

Person {private Integer ID;String namephone
Integer getID() {return IDsetID(Integer ID) {this.ID = IDString getnamenamesetname(String name) {name = namegetPhonephonesetPhone(String phone) {phone = phonePerson(Integer IDString nameString phone) {Person() {}

增删改查功能：

PersonService {private DBOpenHelpr dbOpenHelpr
PersonService(Context context) {dbOpenHelpr = new DBOpenHelpr(context)//增加数据public voID save(Person person){sqliteDatabase db = dbOpenHelpr.getWritableDatabase();ContentValues contentValues = new ContentValues();contentValues.put("name","yanxi")"phone""12222"//2.NulL值字段

//共有下列两种方法增加数据db.insert("person",43); Font-family:'CourIEr New'">contentValues);// db.execsql("insert into person(name,phone) " +// "values(?,?)",new Object[]{person.getname(),person.getPhone()});//在应用中只有一个地方使用数据库可以不用关数据库}
//删除数据
public voID delete(Integer ID){sqliteDatabase db = dbOpenHelpr.getWritableDatabase();db.delete("person","personID=?"String[]{ID.toString()});
// db.execsql("delete from person where personID=?",new Object[]{ID});}//修改数据public voID update(Person person){sqliteDatabase db = dbOpenHelpr.getWritableDatabase();ContentValues contentValues = new ContentValues()contentValues.put("name",43); Font-family:'CourIEr New'">person.getname())"phone"person.getPhone())db.update(contentValues"personID=?"String[]{person.getID().toString()})// db.execsql("update person set name=?,phone=? where personID=?",43); Font-family:'CourIEr New'">// new Object[]{person.getname(),person.getPhone(),person.getID()});}//查询数据public Person find(Integer ID){//dbOpenHelpr.getReadableDatabase()默认开始会调用dbOpenHelpr.getWritableDatabase()，//但是如果磁盘满了之后，就只会以只读的方式打开。sqliteDatabase db = dbOpenHelpr.getReadableDatabase();//2.字段列表部分Cursor cursor = db.query("person",43); Font-family:'CourIEr New'; Font-weight:bold">"personID=?"String[]{ID.toString()});
// Cursor cursor = db.rawquery("select * from person where personID=?",new String[]{ID.toString()});if(cursor.movetoFirst()){int personID = cursor.getInt(cursor.getColumnIndex("personID"))String name = cursor.getString(cursor.getColumnIndex("name"String phone = cursor.getString(cursor.getColumnIndex("phone"return new Person(personID,43); Font-family:'CourIEr New'">namephone)}cursor.close()return null;}//分页数据public List<Person> getScollData(int offsetmaxResult){List<Person> List = new ArrayList<>().getReadableDatabase()Cursor cursor = db.query("person""perrson asc"offset+","+maxResult);

//Cursor cursor = db.rawquery("select * from person order by personID asc limit ?,?",// new String[]{String.valueOf(offset),String.valueOf(maxResult)});while(cursor.movetoNext()){personID = cursor.getInt(cursor.getColumnIndex("personID"))String name = cursor.getString(cursor.getColumnIndex("name"String phone = cursor.getString(cursor.getColumnIndex("phone"List.add(Person(personIDnamephone))}cursor.close()return null;
//获得数据库表的记录数public long getCount(){sqliteDatabase db = dbOpenHelpr.getReadableDatabase();Cursor cursor =db.query(String[]{"count(*)"}// Cursor cursor = db.rawquery("select count (*) form person",null);cursor.movetoFirst()long result = cursor.getLong(0)cursor.close()return result}}

测试类：

public class PersonServiceTest extends AndroIDTestCase {
private static final String TAG = "PeraonServiceTAG";public voID testCreateDB() throws Exception{DBOpenHelpr dbOpenHelpr = new DBOpenHelpr(getContext())//第一次调用该方法，创建数据库，默认目录：<包>databasedbOpenHelpr.getWritableDatabase()}
//增加数据测试类@TesttestSavePersonService p = PersonService(this.getContext())Person person = Person("yanxi",255); Font-family:'CourIEr New'">"1777")p.save(person)//删除数据测试类testDeletep.delete(1//修改数据测试类testUpDatePerson person = p.find(person.setname("weimiao"p.update(person)//查找数据测试类testFindLog.i(TAGperson.toString())//分页数据测试类testScollDatap.getScollData(05//数据记录数测试类testCountlong i = p.getCount()String.valueOf(i))}