Android-Sqlite数据库
本文使用Sqlite数据库，主要使用DatabaseHelper对数据库库表文件进行创建，使用DBManger对数据进行操作。
1.DatabaseHelper初始化
初始化部分放到了Application中实现。创建一个静态变量
/** 数据库Helper对象* */
public static DatabaseHelper databaseHelper = null;
在OnCreate()方法中进行实例化。
// 创建数据库
if (databaseHelper == null) {
databaseHelper = DatabaseHelper.getDBHelper(context);
}2.DatabaseHelper实现类
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
* @author M.xang
* @时间 2018年11月8日
* @描述 数据库
*/
public class DatabaseHelper extends SQLiteOpenHelper {
SQLiteDatabase database;
private static volatile DatabaseHelper instance = null;
private static String DB_NAME = "name.db";// 数据库名称
private static int VERSION_NUM = 1;// 数据库版本
/**
* @param context 上下文
* @param name 数据库名称
* @param factory 为了创建cursor对象, 默认为空
* @param version 数据库版本
*/
private DatabaseHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
database = this.getWritableDatabase();// 调用此方法时数据库才算真正创建
}
/**
* 创建实例
*
* @param context
* @return
*/
public static DatabaseHelper getDBHelper(Context context) {
if (instance == null) {
synchronized (DatabaseHelper.class) {
if (instance == null) {
instance = new DatabaseHelper(context,DB_NAME,null,VERSION_NUM);
}
}
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DBManager.getInstance().createEXCSQL);
db.execSQL(DBManager.getInstance().createOrderSQL);
}
/**
* 数据库升级
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion < newVersion) {
Log.e("数据库升级", "oldVersion < newVersion " + oldVersion + "<" + newVersion);
}
}
}3.DBManager公共类实现
package com.wonder.collectionsystem.db;
import java.util.ArrayList;
import java.util.concurrent.atomic.AtomicInteger;
import com.wonder.collectionsystem.MyApplication;
import com.wonder.collectionsystem.bean.ExcDBBean;
import com.wonder.collectionsystem.bean.OrderInfoBean;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
/**
* @author M.xang
* @时间 2018年11月8日
* @描述 数据库操作类
*/
public class DBManager2 {
private static DBManager2 dbManager;
private AtomicInteger mOpenCounter = new AtomicInteger();
private SQLiteDatabase mDatabase;
public DBManager2() {
super();
}
/** 获得DBManger实例化 */
public static final DBManager2 getInstance() {
if (dbManager == null) {
synchronized (DBManager2.class) {
if (dbManager == null) {
dbManager = new DBManager2();
}
}
}
return dbManager;
}
/**
* 保证获得同一个实例，这样如果数据库读写操作放在线程中，不会出现游标关闭的问题
*
* @return
*/
private synchronized SQLiteDatabase getReadableDatabase() {
if (mOpenCounter.incrementAndGet() == 1) {
mDatabase = MyApplication.databaseHelper.getReadableDatabase();
}
return mDatabase;
}
//-----------------------------------------异常库表------------------------------------------------
/** TODO 工单信息-表名 */
private String TABLE_EXC = "ExcTable";
/** 删除导入数据表SQL语句 */
public final String dropSQL_EXC = "delete from " + TABLE_EXC;
/** 创建表SQL语句 */
public final String createEXCSQL = "create table if not exists " + TABLE_EXC + "("
+ "id integer PRIMARY KEY" + // 主键编号
",devType varchar" + // 设备类型
",excType varchar" + // 异常类型
",excXianXiang varchar" + // 异常现象
",excXXID varchar" + // 异常现象ID
",excReason varchar" + // 异常原因
",excReasonID varchar" + // 异常原因ID
",excReasonTypeID varchar" + // 异常原因分组ID
")";
/**
** 插入数据
*
* @param saveBean
*/
public void insertRecord_EXC(ExcDBBean excDBBean) {
SQLiteDatabase database = null;
try {
database = getReadableDatabase();
String insertIntoTestDataSQL = "insert into " + TABLE_EXC + "(" + "excType" + // 异常类型
",excXianXiang" + // 异常现象
",excXXID" + // 异常现象ID
",excReason" + // 异常原因
",excReasonID" + // 异常原因ID
",devType" + // 设备类型
",excReasonTypeID" + // 异常原因分组ID
")" + " values(?,?,?,?,?,?,?)";
SQLiteStatement statement = database.compileStatement(insertIntoTestDataSQL);
statement.bindString(1, excDBBean.getExcType());
statement.bindString(2, excDBBean.getExcXianXiang());
statement.bindString(3, excDBBean.getExcXXID());
statement.bindString(4, excDBBean.getExcReason());
statement.bindString(5, excDBBean.getExcReasonID());
statement.bindString(6, excDBBean.getDevType());
statement.bindString(7, excDBBean.getExcReasonTypeID());
statement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeDB(database);
}
}
/**
** 根据设备类型、异常类型、查询采集 异常原因
*
* @param devType 设备类型
* @param excType 异常类型
* @return
*/
public ArrayList<ExcDBBean> queryCaiJiByDevAndExcType(String devType, String excType) {
String sql = "select * from " + TABLE_EXC + " where devType='" + devType + "' and excType='" + excType
+ "'";
return query(sql);
}
/**
** 根据设备类型、异常类型、异常现象查询异常数据，再根据其中的 异常现象ID 查询所有异常原因
*
* @param devType 设备类型
* @param excType 异常类型
* @param excXianXiang 异常现象
* @return
*/
public ArrayList<ExcDBBean> queryByDevExcTypeAndExcXX(String devType, String excType,
String excXianXiang) {
ArrayList<ExcDBBean> arrayResult = new ArrayList<ExcDBBean>();
String sql = "select * from " + TABLE_EXC + " where devType='" + devType + "' and excType='" + excType
+ "' and excXianXiang='" + excXianXiang + "'";
ArrayList<ExcDBBean> arrayList = query(sql);
for (int i = 0; i < arrayList.size(); i++) {
String excXXID = arrayList.get(i).getExcXXID();
sql = "select * from " + TABLE_EXC + " where excXXID='" + excXXID + "'";
ArrayList<ExcDBBean> arrayList2 = query(sql);
for (int j = 0; j < arrayList2.size(); j++) {
String excReasonTypeID = arrayList2.get(j).getExcReasonTypeID();
sql = "select * from " + TABLE_EXC + " where excReasonTypeID='" + excReasonTypeID + "'";
ArrayList<ExcDBBean> arrayList3 = query(sql);
arrayResult.addAll(arrayList3);
}
}
return arrayResult;
}
/**
* 查询异常类型所有
*
* @param devType
* @return
*/
public ArrayList<ExcDBBean> queryAllExcType(String excType) {
String sql = "select * from " + TABLE_EXC + " where excType='" + excType + "'";
return query(sql);
}
// 更新语句
// String updateSQL = "update " + TABLE_ORDER + " set " + "state='" + state + "'
// where orderNum='" + orderNum + "'";
private ArrayList<ExcDBBean> query(String sql) {
ArrayList<ExcDBBean> list = new ArrayList<ExcDBBean>();
SQLiteDatabase database = getReadableDatabase();
Cursor cursor = database.rawQuery(sql, null);
int count = cursor.getCount();
try {
if (count > 0) {// 有记录
int excTypeIndex = cursor.getColumnIndex("excType");
int excXianXiangIndex = cursor.getColumnIndex("excXianXiang");
int excXXIDIndex = cursor.getColumnIndex("excXXID");
int excReasonIndex = cursor.getColumnIndex("excReason");
int excReasonIDIndex = cursor.getColumnIndex("excReasonID");
int devTypeIndex = cursor.getColumnIndex("devType");
int excReasonTypeIDIndex = cursor.getColumnIndex("excReasonTypeID");
for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
String excType = getString(cursor, excTypeIndex);
String excXianXiang = getString(cursor, excXianXiangIndex);
String excXXID = getString(cursor, excXXIDIndex);
String excReason = getString(cursor, excReasonIndex);
String excReasonID = getString(cursor, excReasonIDIndex);
String devType = getString(cursor, devTypeIndex);
String excReasonTypeID = getString(cursor, excReasonTypeIDIndex);
ExcDBBean excDBBean = new ExcDBBean();
excDBBean.setExcType(excType);
excDBBean.setExcXianXiang(excXianXiang);
excDBBean.setExcXXID(excXXID);
excDBBean.setExcReason(excReason);
excDBBean.setExcReasonID(excReasonID);
excDBBean.setDevType(devType);
excDBBean.setExcReasonTypeID(excReasonTypeID);
;
list.add(excDBBean);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null) {
cursor.close();
}
closeDB(database);
}
return list;
}
/**
** 删除所有数据库中的表
*
* @return
*/
public boolean deleteEXCTable() {
boolean Ret = false;
SQLiteDatabase database = null;
try {
database = getReadableDatabase();
database.beginTransaction();// 开始事务
database.execSQL(dropSQL_EXC);// 删除异常表
database.setTransactionSuccessful();// 事务完成
Ret = true;
database.endTransaction();// 结束事务
} catch (Exception e) {
e.printStackTrace();
} finally {
closeDB(database);// 释放数据库资源
}
return Ret;
}
/**
* 关闭数据库释放数据库资源
*/
private void closeDB(SQLiteDatabase database) {
try {
if (database != null) {
// 更新器管理的给定对象的字段的当前值为“0”的时候，才正式关闭数据库
if (mOpenCounter.decrementAndGet() == 0) {
database.close();// 释放数据库资源
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 删除所有数据库中的表
*
* @return
*/
public boolean deleteAllDBTable() {
boolean Ret = false;
SQLiteDatabase database = null;
try {
database = getReadableDatabase();
database.beginTransaction();// 开始事务
// database.execSQL(dropSQL_EXC);// 删除异常库
database.setTransactionSuccessful();// 事务完成
Ret = true;
database.endTransaction();// 结束事务
} catch (Exception e) {
e.printStackTrace();
} finally {
closeDB(database);// 释放数据库资源
}
return Ret;
}
private String getString(Cursor cursor, int index) {
if (cursor != null && index >= 0) {
return cursor.getString(index);
}
return "";
}
@SuppressWarnings("unused")
private int getInt(Cursor cursor, int index) {
if (cursor != null && index >= 0) {
return cursor.getInt(index);
}
return 0;
}
@SuppressWarnings("unused")
private int getLong(Cursor cursor, int index) {
if (cursor != null && index >= 0) {
return (int) cursor.getLong(index);
}
return 0;
}
}在获取SQLiteDatabase的database对象时，这里采用了getReadableDatabase()方法，这样写法是为了防止当在线程中对数据库进行读写操作时，出现数据流关闭的情况。
private synchronized SQLiteDatabase getReadableDatabase() {
if (mOpenCounter.incrementAndGet() == 1) {
mDatabase = MyApplication.databaseHelper.getReadableDatabase();
}
return mDatabase;
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
