sqlite数据库操作详细介绍增删改查，游标

本文来源于www.ifyao.com禁止转载！www.ifyao.com

Source code

package com.example.engine;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
private final static String DB_NAME = "NFCAssistant.db";// 数据库名称
private final static int DB_VERSION = 2;// 版本
private final static String DB_USER_TABLE = "tbl_users";// 用户表
private final static String DB_BatchEpment_TABLE = "tbl_batch";// 批量信息
private final static String DB_LargeEpment_TABLE = "tbl_large";// 大型设备
private final static String DB_LOG_TABLE = "tbl_log";// 日志
public DBOpenHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
// 用户表角色1管理员2超级管理员
String createTable = "CREATE TABLE " + DB_USER_TABLE + " ("
+ "Id integer PRIMARY KEY AUTOINCREMENT UNIQUE,"
+ "UserName varchar (10) NOT NULL UNIQUE,"
+ "PassWord varchar (49) NOT NULL,"
+ "Role varchar (10) DEFAULT 1" + ");";
// 批量信息表
String createTblBatchEpment = "CREATE TABLE " + DB_BatchEpment_TABLE
+ " (" + "Id integer PRIMARY KEY AUTOINCREMENT UNIQUE, "
+ "DeviceId integer NOT NULL UNIQUE,"
+ "Codes varchar (100) NOT NULL,"
+ "DeviceName varchar NOT NULL ," + "Price integer NOT NULL,"
+ "Custodian varchar NOT NULL," + "End_user varchar NOT NULL,"
+ "Time datatime NOT NULL" + ");";
// 大型设备表
String createTblLargeEpment = "CREATE TABLE " + DB_LargeEpment_TABLE
+ " (" + "Id integer PRIMARY KEY AUTOINCREMENT UNIQUE, "
+ "DeviceId integer NOT NULL UNIQUE,"
+ "DeviceName varchar NOT NULL ,"
+ "Codes varchar (100) NOT NULL," + "Price integer NOT NULL,"
+ "Custodian varchar NOT NULL," + "End_user varchar NOT NULL,"
+ "Time datatime NOT NULL," + "Location varchar NOT NULL"
+ ");";
// 日志表
String createTblLog = "CREATE TABLE " + DB_LOG_TABLE + " ("
+ "Id integer PRIMARY KEY AUTOINCREMENT UNIQUE, "
+ "UserName varchar NOT NULL ," + "DeviceId integer NOT NULL,"
+ "Time datatime NOT NULL" + ");";
db.execSQL(createTable);
db.execSQL(createTblBatchEpment);
db.execSQL(createTblLargeEpment);
db.execSQL(createTblLog);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS " + DB_USER_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + DB_BatchEpment_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + DB_LargeEpment_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + DB_LOG_TABLE);
onCreate(db);
}
}

数据库操作帮助类

DBhelper继承sqliteDBhelper

实现父类的两个方法

Oncreate方法当创建数据库的时候执行

更新方法，当数据库版本更新时执行

Source code

package com.example.engine;
import com.example.bean.BatchEpment;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
/**
* 操作数据库事物
*
* @author ming
*
*/
public class DBService {
private DBOpenHelper dbHelper;
/**
* 构造器，得到DBopenHelpper的操作实例
*
* @param context
*/
public DBService(Context context) {
this.dbHelper = new DBOpenHelper(context);
}
/**
* 将Batch批量数据excel数据保存到数据库
*
* @param batch
*/
public void saveExcelBatch(BatchEpment batch) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL(
"insert into tbl_batch (DeviceId,Codes,DeviceName,Price,Custodian,End_user,Time) values(?,?,?,?,?,?,?)",
new Object[] { batch.getDeviceId(), batch.getCodes(),
batch.getDeviceName(), batch.getPrice(),
batch.getCustodian(), batch.getEnd_user(),
batch.getTime() });
};
}

数据库增删改查业务类，

saveExcelBatch方法将数据插入数据库中

Source code

package com.example.test;
import com.example.bean.BatchEpment;
import com.example.engine.DBOpenHelper;
import com.example.engine.DBService;
import android.test.AndroidTestCase;
public class ExcelTest extends AndroidTestCase {
public void get() throws Exception {
BatchEpment batch = new BatchEpment();
batch.setDeviceId("111");
batch.setCodes("ss");
batch.setDeviceName("计算机");
batch.setPrice("2000");
batch.setCustodian("刘欢");
batch.setEnd_user("罗琼");
batch.setTime("20111115");
DBService dbService = new DBService(getContext());
dbService.saveExcelBatch(batch);
}
}

写一个测试类传入数据，调用业务类的方法插入数据

Source code

package com.example.bean;
/**
* 批量设备实体类
*
* @author ming
*
*/
public class BatchEpment {
String Id;// 自动编号
String DeviceId;// 设备编号
String Codes;// 分类号
String DeviceName;// 设备名称
String Price;// 价格
String Custodian;// 保管人
String End_user;// 使用单位
String Time;// 时间
public BatchEpment() {
};
public BatchEpment(String id, String deviceId, String codes,
String deviceName, String price, String custodian, String end_user,
String time) {
super();
Id = id;
DeviceId = deviceId;
Codes = codes;
DeviceName = deviceName;
Price = price;
Custodian = custodian;
End_user = end_user;
Time = time;
}
public String getId() {
return Id;
}
public void setId(String id) {
this.Id = id;
}
public String getDeviceId() {
return DeviceId;
}
public void setDeviceId(String deviceId) {
this.DeviceId = deviceId;
}
public String getCodes() {
return Codes;
}
public void setCodes(String codes) {
this.Codes = codes;
}
public String getDeviceName() {
return DeviceName;
}
public void setDeviceName(String deviceName) {
this.DeviceName = deviceName;
}
public String getPrice() {
return Price;
}
public void setPrice(String price) {
this.Price = price;
}
public String getCustodian() {
return Custodian;
}
public void setCustodian(String custodian) {
this.Custodian = custodian;
}
public String getEnd_user() {
return End_user;
}
public void setEnd_user(String end_user) {
this.End_user = end_user;
}
public String getTime() {
return Time;
}
public void setTime(String time) {
this.Time = time;
}
}

实体类 另外一个实例：values(?,?)？是占位符，后边数组中的是实际的值 db.rawQuery返回的是一个游标对象 cursor.movetofirst将游标移到第一条记录返回值是个Boolean值 得到由表里边的值 cursor.getInt(对应字段的行标，通过表的对用字段); 最后将游标关闭。 要是查询的是多条记录的话，就要迭代查询 cursor,moveTOnext 最后将数据存到Arraylist里边 getcount方法是得到记录的行数  android系统提供的四种操作数据的方法：insert(),delete(),updata(),query()详细介绍看下方图片截图。  将前面提供的用SQL写的例子变成系统方法 插入和删除 查询 分页