SQLite数据库建立及相关操作

 

1. DatabaseHelper 创建数据库并建立数据连接

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

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "iblueye.db";
private static final int DATABASE_VERSION = 1;

public DatabaseHelper(Context context) {
// CursorFactory设置为null,使用默认值
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// 数据库第一次被创建时onCreate会被调用 INTEGER PRIMARY KEY AUTOINCREMENT
@Override
public void onCreate(SQLiteDatabase db) {
/* 智能家居创建的数据库 */
db.execSQL("CREATE TABLE IF NOT EXISTS profile"
+ "(profile_id VARCHAR PRIMARY KEY, profile_name VARCHAR,profile_enabled BOOL NOT NULL DEFAULT 0)");
db.execSQL("CREATE TABLE IF NOT EXISTS mode"
+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,profile_id VARCHAR, device_id VARCHAR, device_state VARCHAR)");

/* 智慧交通 */
db.execSQL("CREATE TABLE IF NOT EXISTS traffic"
+ "(traffic_id VARCHAR PRIMARY KEY, traffic_name VARCHAR, description VARCHAR, city VARCHAR,video_id VARCHAR)");

}

// 如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE profile ADD COLUMN other STRING");
db.execSQL("ALTER TABLE mode ADD COLUMN other STRING");
db.execSQL("ALTER TABLE traffic ADD COLUMN other STRING");
}
}

 

 2.HomeDBManager 数据库操作（增删查改）

 

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;

import com.wlx.iblueyeclient.model.home.Mode;
import com.wlx.iblueyeclient.model.home.Profile;

public class HomeDBManager {
private DatabaseHelper helper;
private SQLiteDatabase db;

public HomeDBManager(Context context) {
helper = new DatabaseHelper(context);
// 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0,
// mFactory);
// 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
db = helper.getWritableDatabase();
}

/**
* add profile
* 
* @param profile
*/
public void add(Profile profile) {
db.beginTransaction(); // 开始事务
try {
db.execSQL("INSERT INTO profile VALUES(?,?,?)",
new Object[] { profile.profileId, profile.profileName,
profile.isEnabled });
for (Mode mode : profile.modeList) {
db.execSQL("INSERT INTO mode VALUES(null,?,?,?)",
new Object[] { profile.profileId, mode.deviceId,
mode.deviceState });
}
db.setTransactionSuccessful(); // 设置事务成功完成
} finally {
db.endTransaction(); // 结束事务
}
}

/**
* update profile's name
* 
* @param profile
*/
public void updateProfileName(Profile profile) {
ContentValues cv = new ContentValues();
cv.put("profile_name", profile.profileName);
db.update("profile", cv, "profile_id = ?",
new String[] { profile.profileId });
}

/**
* delete old profile
* 
* @param profile
*/
public void deleteOldProfile(Profile profile) {
db.delete("profile", "profile_id == ?",
new String[] { String.valueOf(profile.profileId) });
}

/**
* query all profiles, return list
* 
* @return List<Profile>
*/
public List<Profile> query() {
ArrayList<Profile> profileList = new ArrayList<Profile>();
Cursor c = queryProfileCursor();
while (c.moveToNext()) {
Profile profile = new Profile();
profile.profileId = c.getString(c.getColumnIndex("profile_id"));
profile.profileName = c.getString(c.getColumnIndex("profile_name"));
profile.isEnabled = Boolean.parseBoolean(c.getString(c
.getColumnIndex("profile_enabled")));

String modeId = c.getString(c.getColumnIndex("mode_id"));
Cursor r = queryModeCursor(new String[] { "mode_id='" + modeId
+ "'" });
List<Mode> modeList = new ArrayList<Mode>();
while (r.moveToNext()) {
Mode mode = new Mode();
mode.deviceId = r.getString(r.getColumnIndex("device_id"));
mode.deviceState = r
.getString(r.getColumnIndex("device_state"));
modeList.add(mode);
}
profile.modeList = modeList;
profileList.add(profile);
}
c.close();
return profileList;
}

/**
* query all profiles, return cursor
* 
* @return Cursor
*/
public Cursor queryProfileCursor() {
Cursor c = db.rawQuery("SELECT * FROM profile", null);
return c;
}

/**
* query all mode, return cursor
* 
* @return Cursor
*/
public Cursor queryModeCursor(String[] selectionArgs) {
Cursor c = db.rawQuery("SELECT * FROM profile", selectionArgs);
return c;
}

/**
* close database
*/
public void closeDB() {
db.close();
}
}