android小功能實現之SQLite數據庫的基本操作
來源：程序員人生 發布時間：2015-03-25 11:47:49 閱讀次數：3623次
創建1個Android工程
1 新建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) {
//
數據庫保持位置<包>/databases
super(context, name, factory, version);
}
@Override
//
數據庫第1次被創建時調用
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的內容：
2 數據庫的基本操作
1 新建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;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", name='" + name + ''' +
", phone='" + phone + ''' +
'}';
}
}
2 新建1個類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 測試添加數據
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 創建
數據庫
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
//
數據庫基本操作
PersonService service = new PersonService(getApplicationContext());
Person person = new Person("寒風", "1596262XXXX");
service.save(person);
}
再次查看test.db的內容：
4 測試查找數據
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 創建
數據庫
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
//
數據庫基本操作
PersonService service = new PersonService(getApplicationContext());
/*
Person person = new Person("寒風", "1596262XXXX");
service.save(person);
*/
Person p = service.find(1);
Log.i("PersonService", p.toString());
}
注釋掉之前添加數據的代碼，講查詢出的數據打印到logcat，結果以下：
5 測試更新數據
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 創建
數據庫
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
//
數據庫基本操作
PersonService service = new PersonService(getApplicationContext());
/*
Person person = new Person("寒風", "1596262XXXX");
service.save(person);
*/
Person p = service.find(1);
p.setName("丿寒灬風丨");
service.update(p);
Person p1 = service.find(1);
Log.i("PersonService", p1.toString());
}
修改了名稱，并更新到數據庫，再次履行查詢結果如圖：
注意，前1條是上1次輸出的結果。
5 測試獲得記錄條數
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 創建
數據庫
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
//
數據庫基本操作
PersonService service = new PersonService(getApplicationContext());
/*
Person person = new Person("寒風", "1596262XXXX");
service.save(person);
*/
/*
Person p = service.find(1);
p.setName("丿寒灬風丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
}
結果如圖：
5 測試分頁方法
5.1 添加多條測試數據
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 創建
數據庫
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
//
數據庫基本操作
PersonService service = new PersonService(getApplicationContext());
for (int i=0; i<20;++i) {
Person person = new Person("寒風", "1596262XXX"+i);
service.save(person);
}
/*
Person p = service.find(1);
p.setName("丿寒灬風丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
// Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
}
查看test.db的內容：
5.2 測試刪除方法
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 創建
數據庫
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
//
數據庫基本操作
PersonService service = new PersonService(getApplicationContext());
/*
for (int i=0; i<20;++i) {
Person person = new Person("寒風", "1596262XXX"+i);
service.save(person);
}
*/
/*
Person p = service.find(1);
p.setName("丿寒灬風丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
// Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
List<Person> persons = service.getScrollData(0, 5);
for(Person person:persons){
Log.i("PersonService", person.toString());
}
Log.i("PersonService","-------------------------");
List<Person> persons1 = service.getScrollData(5, 5);
for(Person person:persons1){
Log.i("PersonService", person.toString());
}
}
結果如圖：
6 測試刪除方法
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 創建
數據庫
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
//
數據庫基本操作
PersonService service = new PersonService(getApplicationContext());
/*
for (int i=0; i<20;++i) {
Person person = new Person("寒風", "1596262XXX"+i);
service.save(person);
}
*/
/*
Person p = service.find(1);
p.setName("丿寒灬風丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
// Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
/*
List<Person> persons = service.getScrollData(0, 5);
for(Person person:persons){
Log.i("PersonService", person.toString());
}
Log.i("PersonService","-------------------------");
List<Person> persons1 = service.getScrollData(5, 5);
for(Person person:persons1){
Log.i("PersonService", person.toString());
}
*/
service.delete(1);
}
查看test.db的內容：
3 使用封裝方法實現
SQLiteDatabase類中對SQL語句做了簡單的封裝，每一個方法對以下：
import android.content.ContentValues;
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 save1(Person person){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
db.insert("person",null, values);
}
// 刪
public void delete(Integer id){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where id=?", new Object[]{id});
}
public void delete1(Integer id){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.delete("person", "id=?",new String[]{id.toString()});
}
// 改
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 void update1(Person person) {
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
db.update("person",values,"id=?", new String[]{person.getId().toString()});
}
// 查
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;
}
public Person find1(Integer id) {
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
//db.query("person", null, "id=?", new String[]{id.toString()},null, null,null);
Cursor cursor = db.query("person", new String[]{"id","name","phone"}, "id=?", new String[]{id.toString()},null, null,null);
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;
}
// 分頁獲得記錄
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 List<Person> getScrollData1(int offset, int maxResult){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", null, null,null,null,null,"id asc", offset+","+maxResult);
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;
}
public long getCount1(){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", new String[]{"count(*)"},null,null,null,null,null);
cursor.moveToFirst();
long res = cursor.getLong(0);
cursor.close();
return res;
}
}
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
