使用步骤：
- 新建一个继承自SQLiteOpenHelper的数据库操作类，提示重写onCreate和OnUpgraed两个方法。其中，onCreate方法只在第一次打开数据库时执行，在此可进行表结构创建的操作；onUpgrade方法在数据库版本升高时执行，因此可以在onUpgraed函数内部根据新旧版本号进行表结构变更处理
- 封装保证数据库安全的必要方法，包括获取单例对象、打开数据库连接、关闭数据库连接
- 获取单例对象：确保App运行时数据库只被打开一次，避免重复打开引起错误
- 打开数据库连接：SQLite有锁机制，即读锁和写锁的处理；故而数据库连接也分两种，读连接可调用getReadableDatabase，写连接可调用getWritableDatabase
- 关闭数据库连接：数据库操作完毕后，应当调用SQLiteDatabase对象的close方法关闭连接
- 提供对表记录进行增加、删除、修改、查询的操作方法
- 可被SQLite直接使用的数据结构是ContentValues类，类似于映射Map，提供put和get方法来存取键值对。
- 对于查询操作来说，使用的是另一个游标类Cursor。调用SQLiteDatabase的query和rawQuery方法时，返回的都是Cursor对象，因此获取查询结果要根据游标的指示一条一条遍历结果集合。
Cursor的常用方法可分为3类：
- 游标控制类方法，用于指定游标的状态
- close：关闭游标
- isClosed：判断游标是否关闭
- isFirst：判断游标是否在开头
- isLast：判断游标是否在末尾
- 游标移动类方法，把游标移动到指定位置
- moveToFirst：移动游标到开头
- moveToLast：移动游标到末尾
- moveToNext：移动游标到下一条记录
- moveToPrevious：移动游标到上一条记录
- move：往后移动游标若干条记录
- moveToPosition：移动游标到指定位置的记录
- 获取记录类方法，可获取记录的数量、类型以及取值
- getCount：获取结果记录的数量
- getInt：获取指定字段的整型值
- getFloat
- getString
- getType
public class MyDatabaseHelper extends SQLiteOpenHelper {
private static final String TAG = "MyDatabaseHelper";
private static final String DB_NAME = "myDB.db";
private static final int DB_VERSION = 1;
private static MyDatabaseHelper mHelper = null;
private SQLiteDatabase mDB = null;
private static final String TABLE_NAME = "my_info";
private MyDatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
private MyDatabaseHelper(Context context, int version) {
super(context, DB_NAME, null, version);
}
/**
* 获取实例--单例模式
* @param context
* @param version
* @return
*/
public static MyDatabaseHelper getInstance(Context context, int version) {
if (version > 0 && mHelper == null) {
mHelper = new MyDatabaseHelper(context, version);
} else if (mHelper == null) {
mHelper = new MyDatabaseHelper(context);
}
return mHelper;
}
/**
* 获得数据库 读 连接
* @return
*/
public SQLiteDatabase openReadLink() {
if (mDB == null || mDB.isOpen() != true) {
mDB = mHelper.getReadableDatabase();
}
return mDB;
}
/**
* 获得数据库 写 连接
* @return
*/
public SQLiteDatabase openWriteLink() {
if (mDB == null || mDB.isOpen() != true) {
mDB = mHelper.getWritableDatabase();
}
return mDB;
}
/**
* 关闭连接
*/
public void closeLink() {
if (mDB != null && mDB.isOpen() == true) {
mDB.close();
mDB = null;
}
}
/**
* 获取数据库名称
* @return
*/
public String getDBName() {
if (mHelper != null) {
return mHelper.getDatabaseName();
} else {
return DB_NAME;
}
}
@Override
public void onCreate(SQLiteDatabase db) {
// 构建调用时打印sql日志
Log.d(TAG, "onCreate");
// 清空表数据
String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
Log.d(TAG, "drop_sql:" + drop_sql);
// 执行sql
db.execSQL(drop_sql);
// 新建表
String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
+ "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
+ "name VARCHAR NOT NULL," + "age INTEGER NOT NULL,"
+ "height LONG NOT NULL," + "weight FLOAT NOT NULL,"
+ "married INTEGER NOT NULL," + "update_time VARCHAR NOT NULL"
//演示数据库升级时要先把下面这行注释
+ ",phone VARCHAR" + ",password VARCHAR"
+ ");";
Log.d(TAG, "create_sql:" + create_sql);
// 执行sql
db.execSQL(create_sql);
}
/**
* 数据库升级操作
* @param db
* @param oldVersion
* @param newVersion
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(TAG, "onUpgrade oldVersion="+oldVersion+", newVersion="+newVersion);
if (newVersion > 1) {
//Android的ALTER命令不支持一次添加多列，只能分多次添加
String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "phone VARCHAR;";
Log.d(TAG, "alter_sql:" + alter_sql);
db.execSQL(alter_sql);
alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "password VARCHAR;";
Log.d(TAG, "alter_sql:" + alter_sql);
db.execSQL(alter_sql);
}
}
public int delete(String condition) {
int count = mDB.delete(TABLE_NAME, condition, null);
return count;
}
public int deleteAll() {
int count = mDB.delete(TABLE_NAME, "1=1", null);
return count;
}
public long insert(UserInfo info) {
ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
infoArray.add(info);
return insert(infoArray);
}
public long insert(ArrayList<UserInfo> infoArray) {
long result = -1;
for (int i = 0; i < infoArray.size(); i++) {
UserInfo info = infoArray.get(i);
ArrayList<UserInfo> tempArray = new ArrayList<UserInfo>();
// 如果存在同名记录，则更新记录
// 注意条件语句的等号后面要用单引号括起来
if (info.name!=null && info.name.length()>0) {
String condition = String.format("name='%s'", info.name);
tempArray = query(condition);
if (tempArray.size() > 0) {
update(info, condition);
result = tempArray.get(0).rowid;
continue;
}
}
// 如果存在同样的手机号码，则更新记录
if (info.phone!=null && info.phone.length()>0) {
String condition = String.format("phone='%s'", info.phone);
tempArray = query(condition);
if (tempArray.size() > 0) {
update(info, condition);
result = tempArray.get(0).rowid;
continue;
}
}
// 不存在唯一性重复的记录，则插入新记录
ContentValues cv = new ContentValues();
cv.put("name", info.name);
cv.put("age", info.age);
cv.put("height", info.height);
cv.put("weight", info.weight);
cv.put("married", info.married);
cv.put("update_time", info.update_time);
cv.put("phone", info.phone);
cv.put("password", info.password);
result = mDB.insert(TABLE_NAME, "", cv);
// 添加成功后返回行号，失败后返回-1
if (result == -1) {
return result;
}
}
return result;
}
public int update(UserInfo info, String condition) {
ContentValues cv = new ContentValues();
cv.put("name", info.name);
cv.put("age", info.age);
cv.put("height", info.height);
cv.put("weight", info.weight);
cv.put("married", info.married);
cv.put("update_time", info.update_time);
cv.put("phone", info.phone);
cv.put("password", info.password);
int count = mDB.update(TABLE_NAME, cv, condition, null);
return count;
}
public int update(UserInfo info) {
return update(info, "rowid="+info.rowid);
}
public ArrayList<UserInfo> query(String condition) {
String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time," +
"phone,password from %s where %s;", TABLE_NAME, condition);
Log.d(TAG, "query sql: "+sql);
ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
// 获得游标对象
Cursor cursor = mDB.rawQuery(sql, null);
if (cursor.moveToFirst()) {
for (;; cursor.moveToNext()) {
UserInfo info = new UserInfo();
info.rowid = cursor.getLong(0);
info.xuhao = cursor.getInt(1);
info.name = cursor.getString(2);
info.age = cursor.getInt(3);
info.height = cursor.getLong(4);
info.weight = cursor.getFloat(5);
//SQLite没有布尔型，用0表示false，用1表示true
info.married = (cursor.getInt(6)==0)?false:true;
info.update_time = cursor.getString(7);
info.phone = cursor.getString(8);
info.password = cursor.getString(9);
infoArray.add(info);
if (cursor.isLast() == true) {
break;
}
}
}
cursor.close();
return infoArray;
}
}
public class UserInfo {
public long rowid;
public int xuhao;
public String name;
public int age;
public long height;
public float weight;
public boolean married;
public String update_time;
public String phone;
public String password;
public UserInfo() {
rowid = 0l;
xuhao = 0;
name = "";
age = 0;
height = 0l;
weight = 0.0f;
married = false;
update_time = "";
phone = "";
password2 = "";
}
}
2404
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
