1.建DatabaseHelper类，继承SQLiteOpenHelper
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
public class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context, String name, CursorFactory cursorFactory, int version) {
super(context, name, cursorFactory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
}
}
2. 创建数据库操作类型类
public class DBType {
public static final int CREATE = 0;
public static final int INSERT = 1;
public static final int UPDATE = 2;
public static final int DELETE = 3;
public static final int DROP = 4;
public static final int SELECT=5;
}
3.创建执行SQL语句的工具类
import java.util.ArrayList;
import java.util.HashMap;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
public class MSNDBTools {
public MSNDBTools() {}
// 执行用户表的数据库操作
public String exeUserSQL(DatabaseHelper dbHelper, String tbName, int type,
String sql, String newValue) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
switch (type) {
case DBType.CREATE:
case DBType.INSERT:
case DBType.DROP:
//创建、插入和删除表操作的实质都是一样，即执行一条sql语句即可，所以归为一类
try {
db.execSQL(sql);
Log.i("msn", type + " sql success/n");
return "true";
} catch (SQLException ex) {
Log.i("msn", type + " sql failure/n" + ex.toString() + "/n");
return "false";
}
case DBType.UPDATE:
try {
ContentValues values = new ContentValues();
// 分割用户名和密码
String[] userTmp = newValue.split(",");
values.put("userName", userTmp[0]);
values.put("pwd", userTmp[1]);
db.update(tbName, values, "", null);
Log.i("msn", type + " sql success/n");
return "true";
} catch (SQLException ex) {
Log.i("msn", type + " sql failure/n" + ex.toString() + "/n");
return "false";
}
//删除
case DBType.DELETE:
try {
db.delete(tbName, "id=?", new String[] { "1" }); //执行删除操作，向id传参，删除Id为1的记录
Log.i("msn", type + " sql success/n");
return "true";
} catch (SQLException ex) {
Log.i("msn", type + " sql failure/n" + ex.toString() + "/n");
return "false";
}
//查询
case DBType.SELECT:
String userInfo = "";
try {
String[] column = { "userName", "pwd" }; //查询的字段
Cursor cursor = db.query(tbName, column, null, null, null,
null, null);
Integer num = cursor.getCount();
Log.i("msn", type + " count= " + Integer.toString(num) + "/n");
//设置游标起始位置
cursor.moveToFirst();
//逐条获取记录
while (cursor.getPosition() != cursor.getCount()) {
userInfo = cursor.getString(0) + "," + cursor.getString(1);
Log.i("msn", "userInfo:" + userInfo);
cursor.moveToNext();
}
return userInfo;
} catch (SQLException ex) {
Log.i("msn", type + " select failure/n" + ex.toString() + "/n");
return userInfo;
}
}
return null;
}
}
4.在activity或service中调用执行数据库操作
如：
//数据库操作
dbHelper = new DatabaseHelper(this, DATABASE_NAME, null, DATABASE_VERSION);
dbTools = new MSNDBTools();
//创建用户表，若存在则不创建,DROP table if exists" + TABLE_NAME + ";
String sql = "CREATE TABLE IF NOT EXISTS " + USER_TABLE_NAME
+ " (ID INTEGER PRIMARY KEY, userName VARCHAR, pwd VARCHAR,createTime datetime);";
if(dbTools.exeUserSQL(dbHelper, USER_TABLE_NAME, DBType.CREATE,sql, "").equals("true"))
{
Log.i("msn", "用户表初始化成功");
}
else
{
Log.i("msn", "用户表初始化失败");
}