Android-Sqlite数据库

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;
}