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/