使用步骤:
Cursor的常用方法可分为3类:
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 = "";
}
}