Android数据的四种存储方式之SQLite数据库-阿里云开发者社区

Android数据的四种存储方式之SQLite数据库

2017-07-03 1322

简介：

+关注继续查看

　Test.java:

/**

* 本例解决的问题：

* 核心问题：通过SQLiteOpenHelper类创建数据库对象

* 通过数据库对象对数据库的数据的操作

* 1.sql语句方式操作SQLite数据库

* 2.谷歌提供的api对SQLite数据库的操作

* 3.SQLite对事务的操作

import com.ghsy.createsqlitedb.db.MyOpenHelper;

import android.content.ContentValues;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.test.AndroidTestCase;

public class Test extends AndroidTestCase {

MyOpenHelper oh;

SQLiteDatabase db;

public void test() {

// 创建一个MyOpenHelper对象

// 改动此处的版本，会运行upgrade方法--upgrade方法中加入?了一列

MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 3);

// 假设数据库不存在，先创建数据库，再打开数据库，假设已经存在，直接打开

SQLiteDatabase db = oh.getWritableDatabase();

db.close();

}

// 測试框架初始化完毕

/**

* This method is called before a test is executed

@Override

protected void setUp() throws Exception {

// TODO Auto-generated method stub

super.setUp();

oh = new MyOpenHelper(getContext(), "people.db", null, 3);

db = oh.getWritableDatabase();

}

　　// ===========sql语句方式操作SQLite数据库================================

public void insert() {

db.execSQL("insert into person(name, phone, money) values(?, ?, ?)",

new Object[] { "大明", "18666", 6000 });

db.execSQL("insert into person(name, phone, money) values(?, ?, ?)",

new Object[] { "小红", "18666", 6000 });

db.execSQL("insert into person(name, phone, money) values(?, ?, ?)",

new Object[] { "大红", "18666", 6000 });

}

public void delete() {

db.execSQL("delete from person where name = ?", new Object[] { "大明" });

}

public void update() {

db.execSQL("update person set money = 10000 where name = ?",

new Object[] { "小明" });

}

public void query() {

// 游标，存放查询返回的数据，获取方法跟resultSet高度雷同

Cursor c = db.rawQuery("select * from person", new String[] {});

while (c.moveToNext()) {

String name = c.getString(c.getColumnIndex("name"));

String phone = c.getString(c.getColumnIndex("phone"));

System.out.println(name + ";" + phone);

}

　　// ==============谷歌提供的api对SQLite数据库的操作======================

/**

* api-insert data to table

public void insertApi() {

ContentValues cv = new ContentValues();

cv.put("name", "微明");

cv.put("phone", 15666);

cv.put("money", 630);

long id = db.insert("person", null, cv);

System.out.println(id);

}

/**

* api-delete data from table

* @return the number of rows affected

public int deleteApi() {

int affectedNum = db.delete("person", "name=?", new String[] { "小红" });

return affectedNum;

}

/**

* api-update

public void updateApi() {

ContentValues contentValues = new ContentValues();

contentValues.put("name", "小红");

contentValues.put("money", "10");

// return the number of rows affected

db.update("person", contentValues, "name=?", new String[] { "大红" });

}

public void queryApi() {

Cursor cursor = db.query("person", new String[] { "phone", "money" },

null, null, null, null, null);

while (cursor.moveToNext()) {

String phone = cursor.getString(cursor.getColumnIndex("phone"));

String money = cursor.getString(cursor.getColumnIndex("money"));

System.out.println(phone + "##" + money);

}

　　// ===============SQLite对事务的操作=====================

/**

* 银行转账操作

public void transation(){

db.beginTransaction();

try {

//name为微明的用户向小红转账

ContentValues contentValues=new ContentValues();

contentValues.put("money", 1000);

db.update("person", contentValues, "name=?", new String[]{"微明"});

ContentValues contentValues2=new ContentValues();

contentValues2.put("money", 1100);

db.update("person", contentValues2, "name=?", new String[]{"小红"});

//全部语句运行完毕，若没有异常，则会运行这句设置事务成功的标记

db.setTransactionSuccessful();

} finally {

//会检查事务的标识，若没有调用setTransactionSuccessful()方法设置标志，则回滚事务。否则提交事务。

db.endTransaction();

}

MyOpenHelper.java

**SQLiteOpenHelper:

* A helper class to manage database creation and version management.

* 所以，SQLiteOpenHelper是对库本身的操作。若要对库中数据操作，须要使用库对象的方法。

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

public class MyOpenHelper extends SQLiteOpenHelper {

//name:数据库文件的名字

//factory：游标工厂

//version：版本号,必须大于等于1

public MyOpenHelper(Context context, String name, CursorFactory factory, int version) {

super(context, name, factory, version);

// TODO Auto-generated constructor stub

}

//数据库创建时调用

@Override

public void onCreate(SQLiteDatabase db) {

//创建一个person表

db.execSQL("create table person(_id integer primary key autoincrement, name char(10), phone char(20))");

System.out.println("oncreate调用了");

}

//数据库升级时调用

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// TODO Auto-generated method stub

System.out.println("onupgrade调用了");

db.execSQL("alter table person add money char(20)");

}

最新内容请见作者的GitHub页：http://qaseven.github.io/