创建一个Android工程
一 新建数据库1 新建一个类DBOpenHelper
内容如下：
import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper {public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {// 数据库保持位置<包>/databasessuper(context, name, factory, version);}@Override// 数据库第一次被创建时调用public void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE person(id integer primary key autoincrement, name varchar(20))");}@Override// 数据库文件的版本号发生变更时调用public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");}}
onCreate函数在数据库创建时调用；onUpgrade在数据库版本号version发生变化时调用。
2 测试修改MainActivity.java代码如下：
protected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);int version = 1;DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);dbOpenHelper.getWritableDatabase();}
此时在data文件夹中可以看到test.db文件如图：
打开可以看到：
打开person:
修改version的值：int version = 2;
再次查看test.db的内容：
二 数据库的基本操作1 新建一个类Person类
用于保存对象数据，内容如下：public class Person {private Integer id;private String name;private String phone;public Person(){}public Person(String name, String phone) {this.name = name;this.phone = phone;}public Person(int id, String name, String phone) {this.id = id;this.name = name;this.phone = phone;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}@Overridepublic String toString() {return "Person{" +"id=" + id +", name='" + name + '\&;' +", phone='" + phone + '\&;' +'}';}}
2 新建一个类PersonService类
用于对数据进行操作，内容如下：import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import java.util.ArrayList;import java.util.List;public class PersonService {private DBOpenHelper dbOpenHelper;public PersonService(Context context) {int version = 2;this.dbOpenHelper = new DBOpenHelper(context, "test.db", null, version);}// 增public void save(Person person){SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();db.execSQL("insert into person(name, phone) values(?, ?)", new Object[]{person.getName(), person.getPhone()});}// 删public void delete(Integer id){SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();db.execSQL("delete from person where id=?", new Object[]{id});}// 改public void update(Person person){SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();db.execSQL("update person set name=?,phone=? where id=?", new Object[]{person.getName(), person.getPhone(), person.getId()});}// 查public Person find(Integer id){SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person where id=?", new String[]{id.toString()});if (cursor.moveToFirst()){int personId = cursor.getInt(cursor.getColumnIndex("id"));String name = cursor.getString(cursor.getColumnIndex("name"));String phone = cursor.getString(cursor.getColumnIndex("phone"));return new Person(personId, name, phone);}cursor.close();return null;}// 分页获取记录，跳过前offset条记录，查找maxResult条记录public List<Person> getScrollData(int offset, int maxResult){SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person order by id asc limit ?,?",new String[]{String.valueOf(offset), String.valueOf(maxResult)});List<Person> persons = new ArrayList<Person>();while (cursor.moveToNext()){int personId = cursor.getInt(cursor.getColumnIndex("id"));String name = cursor.getString(cursor.getColumnIndex("name"));String phone = cursor.getString(cursor.getColumnIndex("phone"));persons.add(new Person(personId, name, phone));}cursor.close();return persons;}// 获取记录条数public long getCount(){SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select count(*) from person",null);cursor.moveToFirst();long res = cursor.getLong(0);cursor.close();return res;}}
3 测试添加数据游手好闲会使人心智生锈
