I have used the following modules for SQlite database read and write After sometimes, when loading the records back , it shows the exception that I reopen the closed database connection
The below is my code
DatabaseManager.java
public class DatabaseManager
{
static private DatabaseManager instance;
static public DatabaseManager getInstance()
{
return instance;
}
static public void init(Context ctx)
{
if (null == instance)
{
instance = new DatabaseManager(ctx);
}
}
private DatabaseHelper helper;
private DatabaseManager(Context ctx)
{
helper = new DatabaseHelper(ctx);
helper.getWritableDatabase();
}
private synchronized DatabaseHelper getHelper()
{
return helper;
}
public void close(){
getHelper().close();
}
public List<History> getAllHistory()
{
List<History> records = new ArrayList<History>();
try
{
QueryBuilder<History, Integer> queryBuilder = getHelper().getHistoryDao().queryBuilder();
PreparedQuery<History> preparedQuery = queryBuilder.orderBy("date", false).prepare();
records = getHelper().getHistoryDao().query(preparedQuery);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records;
}
public History getHistory(String id)
{
List<History> records = new ArrayList<History>();
try
{
QueryBuilder<History, Integer> queryBuilder = getHelper().getHistoryDao().queryBuilder();
PreparedQuery<History> preparedQuery = queryBuilder.where().eq("t_id", id).prepare();
records = getHelper().getHistoryDao().query(preparedQuery);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records.get(0);
}
public void addHistory(History history){
try
{
getHelper().getHistoryDao().createOrUpdate(history);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public void delHistory(History history){
try
{
getHelper().getHistoryDao().delete(history);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public List<Favourite> getAllFavourite()
{
List<Favourite> records = new ArrayList<Favourite>();
try
{
QueryBuilder<Favourite, Integer> favouriteQB = getHelper().getFavouriteDao().queryBuilder();
PreparedQuery<Favourite> favouriteQ = favouriteQB.orderBy("date", false).prepare();
records = getHelper().getFavouriteDao().query(favouriteQ);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records;
}
public Favourite getFavourite(String id)
{
List<Favourite> records = new ArrayList<Favourite>();
try
{
QueryBuilder<Favourite, Integer> favouriteQB = getHelper().getFavouriteDao().queryBuilder();
PreparedQuery<Favourite> favouriteQ = favouriteQB.where().eq("t_id", id).prepare();
records = getHelper().getFavouriteDao().query(favouriteQ);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records.get(0);
}
public void addFavourite(Favourite f)
{
try
{
getHelper().getFavouriteDao().createOrUpdate(f);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public void delFavourite(Favourite f){
try
{
getHelper().getFavouriteDao().delete(f);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public List<Lm> getAllLM()
{
List<Lm> records = new ArrayList<Lm>();
try
{
QueryBuilder<Lm, Integer> lmQB = getHelper().getLmDao().queryBuilder();
PreparedQuery<Lm> lmQ = lmQB.orderBy("date", false).prepare();
records = getHelper().getLmDao().query(lmQ);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records;
}
public Lm getLM(String id)
{
List<Lm> records = new ArrayList<Lm>();
try
{
QueryBuilder<Lm, Integer> lmQB = getHelper().getLmDao().queryBuilder();
PreparedQuery<Lm> lmQ = lmQB.where().eq("t_id", id).prepare();
records = getHelper().getLmDao().query(lmQ);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records.get(0);
}
public void addLm(Lm l)
{
try
{
getHelper().getLmDao().createOrUpdate(l);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public void delLm(Lm l){
try
{
getHelper().getLmDao().delete(l);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public List<iconPlus> getAllIcon()
{
List<iconPlus> records = new ArrayList<iconPlus>();
try
{
QueryBuilder<iconPlus, Integer> IconQB = getHelper().getIconPlusDao().queryBuilder();
PreparedQuery<iconPlus> IconQ = IconQB.prepare();
records = getHelper().getIconPlusDao().query(IconQ);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records;
}
public void addIcon(iconPlus l)
{
try
{
getHelper().getIconPlusDao().createOrUpdate(l);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public void deleteIcon(iconPlus l)
{
try
{
getHelper().getIconPlusDao().delete(l);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public List<BlockedUser> getAllBlockedUser()
{
List<BlockedUser> records = new ArrayList<BlockedUser>();
try
{
QueryBuilder<BlockedUser, Integer> blockQB = getHelper().getBlockedUserDao().queryBuilder();
PreparedQuery<BlockedUser> blockQ = blockQB.prepare();
records = getHelper().getBlockedUserDao().query(blockQ);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records;
}
public boolean addBlockedUser(BlockedUser buser){
boolean result = false;
try
{
result = getHelper().getBlockedUserDao().create(buser) > 0;
}
catch (Exception ex)
{
ex.printStackTrace();
}
return result;
}
public void deleteBlockedUser(BlockedUser buser){
try
{
getHelper().getBlockedUserDao().delete(buser);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public boolean IsBlockedUserExist(int id)
{
List<BlockedUser> records = new ArrayList<BlockedUser>();
try
{
QueryBuilder<BlockedUser, Integer> queryBuilder = getHelper().getBlockedUserDao().queryBuilder();
PreparedQuery<BlockedUser> preparedQuery = queryBuilder.where().eq("u_id", id).prepare();
records = getHelper().getBlockedUserDao().query(preparedQuery);
}
catch (SQLException e)
{
e.printStackTrace();
}
return records!=null ? true : false;
}
}
DatabaseHelper.java
public class DatabaseHelper extends OrmLiteSqliteOpenHelper
{
private static final String DATABASE_NAME = "hkgalden.sqlite";
private static String DATABASE_INT_PATH =
String.format( Environment.getDataDirectory() + "//data//%s//databases//", new Object[] {GaldenUtils.APP_KEY});
private static String DATABASE_EXT_PATH =
Environment.getExternalStorageDirectory() + File.pathSeparator + GaldenUtils.FOLDER_NAME + File.pathSeparator;
private static final int DATABASE_VERSION = 6;
private ConnectionSource connectionSource = null;
private Dao<History, Integer> hDao = null;
private Dao<Favourite, Integer> fDao = null;
private Dao<Lm, Integer> lDao = null;
private Dao<iconPlus, Integer> iDao = null;
private Dao<BlockedUser, Integer> buDao = null;
public DatabaseHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase database,ConnectionSource connectionSource)
{
// database.execSQL("create table history(t_id integer, topic text, page integer, PRIMARY KEY(t_id));");
// database.execSQL("create table lm(t_id integer, PRIMARY KEY(t_id));");
// database.execSQL("create table favourites(t_id integer, PRIMARY KEY(t_id));");
try
{
TableUtils.createTable(connectionSource, History.class);
TableUtils.createTable(connectionSource, Favourite.class);
TableUtils.createTable(connectionSource, Lm.class);
TableUtils.createTable(connectionSource, iconPlus.class);
TableUtils.createTable(connectionSource, BlockedUser.class);
}
catch (SQLException e)
{
Log.e(DatabaseHelper.class.getName(), "Can't create database", e);
throw new RuntimeException(e);
}
catch (java.sql.SQLException e)
{
e.printStackTrace();
}
}
@Override
public ConnectionSource getConnectionSource() {
if (connectionSource == null) {
connectionSource = super.getConnectionSource();
}
return connectionSource;
}
@Override
public void onUpgrade(SQLiteDatabase db,ConnectionSource connectionSource, int oldVersion, int newVersion)
{
// db.execSQL("DROP TABLE IF EXISTS history"); //刪除舊有的資料表
// db.execSQL("DROP TABLE IF EXISTS lm");
// db.execSQL("DROP TABLE IF EXISTS favourite");
// db.execSQL("DROP TABLE IF EXISTS iconPlus");
try
{
/* List<String> allSql = new ArrayList<String>();
for (String sql : allSql)
{
db.execSQL(sql);
}
onCreate(db, connectionSource);*/
}
catch (Exception e)
{
Log.e(DatabaseHelper.class.getName(), "exception during onUpgrade", e);
throw new RuntimeException(e);
}
}
public void cleanUpIcon()
{
try{
ConnectionSource connectionsource = getConnectionSource();
TableUtils.dropTable(connectionsource, iconPlus.class, true);
TableUtils.createTable(connectionSource, iconPlus.class);
}catch(Exception e){
e.printStackTrace();
}
}
public void exportDB() {
// TODO Auto-generated method stub
try {
File sd = Environment.getExternalStorageDirectory();
File data = Environment.getDataDirectory();
if (sd.canWrite()) {
String currentDBPath= DATABASE_INT_PATH + DATABASE_NAME ;
String backupDBPath = DATABASE_EXT_PATH + DATABASE_NAME;
File currentDB = new File(data, currentDBPath);
File backupDB = new File(sd, backupDBPath);
FileChannel src = null;
FileChannel dst = null;
try {
src = new FileInputStream(currentDB).getChannel();
dst = new FileOutputStream(backupDB).getChannel();
dst.transferFrom(src, 0, src.size());
}finally{
src.close();
dst.close();
}
}
} catch (Exception e) {
}
}
public void importDB() {
// TODO Auto-generated method stub
try {
File sd = Environment.getExternalStorageDirectory();
File data = Environment.getDataDirectory();
if (sd.canWrite()) {
String currentDBPath= DATABASE_INT_PATH + DATABASE_NAME ;
String backupDBPath = DATABASE_EXT_PATH + DATABASE_NAME;
File currentDB = new File(data, currentDBPath);
File backupDB = new File(sd, backupDBPath);
FileChannel src = null;
FileChannel dst = null;
try {
src = new FileInputStream(currentDB).getChannel();
dst = new FileOutputStream(backupDB).getChannel();
dst.transferFrom(src, 0, src.size());
}finally{
src.close();
dst.close();
}
}
} catch (Exception e) {
}
}
@Override
public void close()
{
super.close();
hDao = null;
fDao = null;
lDao = null;
iDao = null;
buDao = null;
}
public Dao<History, Integer> getHistoryDao()
{
if (null == hDao)
{
try
{
hDao = getDao(History.class);
}
catch (java.sql.SQLException e)
{
e.printStackTrace();
}
}
return hDao;
}
public Dao<Favourite, Integer> getFavouriteDao()
{
if (null == fDao)
{
try
{
fDao = getDao(Favourite.class);
}
catch (java.sql.SQLException e)
{
e.printStackTrace();
}
}
return fDao;
}
public Dao<Lm, Integer> getLmDao()
{
if (null == lDao)
{
try
{
lDao = getDao(Lm.class);
}
catch (java.sql.SQLException e)
{
e.printStackTrace();
}
}
return lDao;
}
public Dao<iconPlus, Integer> getIconPlusDao()
{
if (null == lDao)
{
try
{
iDao = getDao(iconPlus.class);
}
catch (java.sql.SQLException e)
{
e.printStackTrace();
}
}
return iDao;
}
public Dao<BlockedUser, Integer> getBlockedUserDao()
{
if (null == buDao)
{
try
{
buDao = getDao(BlockedUser.class);
}
catch (java.sql.SQLException e)
{
e.printStackTrace();
}
}
return buDao;
}
}
Application: (onDestroy explicitly called closed() , also makes no difference)
DatabaseManager.init(getApplication());
DatabaseManager.getInstance().addHistory(history);
DatabaseManager.init(getApplication());
iconsDB = DatabaseManager.getInstance().getAllIcon();
0 comments:
Post a Comment