Android--SQLite（增，刪，改，查）操作實例代碼

2020-04-11 12:31:24

字體：大中小

來源：轉載

供稿：網友

需要5個類：

1.實體類：Person.java

2.抽象類:SQLOperate.java(封裝了對數據庫的操作)

3.助手類：DBOpenHelper.java(繼承SQLiteOpenHelper)

4.實現類:SQLOperateImpl.java(實現抽象類SQLOperate.java)

5.測試類：Test.java(繼承AndroidTestCase)

1.Person.java

復制代碼代碼如下:

package com.mrzhu.sqltite;

public class Person {

private int _id;
private String name;

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

@Override
public String toString() {
  return "Person [id=" + _id + ", name=" + name + "]";
}

public Person() {
  super();
}

public Person(int _id, String name) {
  super();
  this._id = _id;
  this.name = name;
}
}

2.SQLOperate.java

復制代碼代碼如下:

package com.mrzhu.sqltite;

import java.util.List;

/**
* 增刪改查
* @author ZLQ
*
*/
public interface SQLOperate {
public void add(Person p);
public void delete(int id);
public void updata(Person p);
public List<Person> find();
public Person findById(int id);
}

3.DBOpenHelper.java

復制代碼代碼如下:

package com.mrzhu.sqltite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
* 助手類
* @author ZLQ
*
*/
public class DBOpneHelper extends SQLiteOpenHelper {

private static final int VERSION = 1;//版本
private static final String DB_NAME = "people.db";//數據庫名
public static final String STUDENT_TABLE = "student";//表名
public static final String _ID = "_id";//表中的列名
public static final String NAME = "name";//表中的列名
//創建數據庫語句，STUDENT_TABLE，_ID ，NAME的前后都要加空格
private static final String CREATE_TABLE = "create table " + STUDENT_TABLE + " ( " + _ID + " Integer primary key autoincrement," + NAME + " text)";

public DBOpneHelper(Context context) {
super(context, DB_NAME, null, VERSION);
}

//數據庫第一次被創建時調用
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}

//版本升級時被調用
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

4.SQLOperateImpl.java

復制代碼代碼如下:

package com.mrzhu.sqltite;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SQLOperateImpl implements SQLOperate{

private DBOpneHelper dbOpenHelper;

public SQLOperateImpl(Context context) {
dbOpenHelper = new DBOpneHelper(context);
}

/**
* 增，用insert向數據庫中插入數據
*/
public void add(Person p) {
  SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(DBOpneHelper._ID, p.getId());
  values.put(DBOpneHelper.NAME, p.getName());
  db.insert(DBOpneHelper.STUDENT_TABLE, null, values);
}

/**
* 刪，通過id刪除數據
*/
public void delete(int id) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.delete(DBOpneHelper.STUDENT_TABLE, DBOpneHelper._ID + "=?", new String[]{String.valueOf(id)});
}

/**
* 改，修改指定id的數據
*/
public void updata(Person p) {
  SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(DBOpneHelper._ID, p.getId());
  values.put(DBOpneHelper.NAME, p.getName());
  db.update(DBOpneHelper.STUDENT_TABLE, values, DBOpneHelper._ID + "=?", new String[]{String.valueOf(p.getId())});
}

/**
* 查，查詢表中所有的數據
*/
public List<Person> find() {
  List<Person> persons = null;
  SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
  Cursor cursor = db.query(DBOpneHelper.STUDENT_TABLE, null, null, null, null, null, null);
  if(cursor != null){
   persons = new ArrayList<Person>();
   while(cursor.moveToNext()){
    Person person = new Person();
    int _id = cursor.getInt(cursor.getColumnIndex(DBOpneHelper._ID));
    String name = cursor.getString(cursor.getColumnIndex(DBOpneHelper.NAME));
    person.setId(_id);
    person.setName(name);
    persons.add(person);
   }
  }
  return persons;
}

/**
* 查詢指定id的數據
*/
public Person findById(int id) {
  SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
  Cursor cursor = db.query(DBOpneHelper.STUDENT_TABLE, null, DBOpneHelper._ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
  Person person = null;
  if(cursor != null && cursor.moveToFirst()){
   person = new Person();
   int _id = cursor.getInt(cursor.getColumnIndex(DBOpneHelper._ID));
   String name = cursor.getString(cursor.getColumnIndex(DBOpneHelper.NAME));
   person.setId(_id);
   person.setName(name);
  }
  return person;
}
}

5.Test.java

在AndroidManifest.xml中的<application></application>外添加

（targetPackage是當前工程的包名）

<instrumentation

android:targetPackage="com.mrzhu.sqltite"

android:name="android.test.InstrumentationTestRunner">

</instrumentation>

在<application></application>中添加<uses-library android:name="android.test.runner"/>

復制代碼代碼如下:

package com.mrzhu.sqltite;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

public class Test extends AndroidTestCase {
public void testAdd() throws Exception{
  SQLOperateImpl test = new SQLOperateImpl(getContext());
  Person person = new Person(2, "Peter");
  test.add(person);
}

public void testDelete() throws Exception{
  SQLOperateImpl test = new SQLOperateImpl(getContext());
  test.delete(1);
}

public void testUpdata() throws Exception{
  SQLOperateImpl test = new SQLOperateImpl(getContext());
  Person person = new Person(1, "Tom");
  test.updata(person);
}

public void testFind() throws Exception{
  SQLOperateImpl test = new SQLOperateImpl(getContext());
  List<Person> persons = test.find();
  for (Person person : persons) {
   Log.i("System.out", person.toString());
  }
}

public void testFindById() throws Exception{
  SQLOperateImpl test = new SQLOperateImpl(getContext());
  Person person = test.findById(2);
  Log.i("System.out", person.toString());
}
}