android sqlite数据库使用

 

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", "用户表初始化失败");

   }