Долго загружается приложение из базы SQLite
Добавлено: 29 апр 2016, 09:08
помогите товарищи, не могу понять, где в базе данных ошибка или устаревшие методы. Запускаю приложение, оно грузит базу данных чуть ли минута, от этого приложение просто скукотише. Приложение само как викторина тесты для школьников. База данных не весит большой объем, около 800 строк.
Реализация DataBaseHelper:
[syntax=java5]public class DatabaseHelper extends SQLiteOpenHelper {
private static int DB_VERSION = 1;
private static final String DB_NAME = "shool";
public static final String TABLE_QUESTION = "Question";
public static final String TABLE_ANSWER = "Answer";
public boolean _should_fill = false;
private SQLiteDatabase myDataBase;
private DatabaseHelper(Context context){
super(context, DB_NAME, null, DB_VERSION);
}
private static DatabaseHelper instance;
public static DatabaseHelper getInstance(Context context)
{
if (instance == null)
instance = new DatabaseHelper(context);
return instance;
}
public void setDataBase(SQLiteDatabase db)
{
myDataBase = db;
}
@Override
public void onCreate(SQLiteDatabase myDataBase) {
_should_fill = true;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+ TABLE_QUESTION);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_ANSWER);
onCreate(db);
}
public TestQuestion getQuestionWithAnswers(String topic, long quest_number, long curQuestId)
{
Cursor cursor;
if (curQuestId == -1)
{
if (topic != null)
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? AND topic=? ORDER BY RANDOM() LIMIT 1",
new String[] { String.valueOf(quest_number), topic });
else
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? ORDER BY RANDOM() LIMIT 1",
new String[] { String.valueOf(quest_number)});
cursor.moveToFirst();
}
else
{
if (topic != null)
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? AND topic=? ORDER BY RANDOM()",
new String[] { String.valueOf(quest_number), topic });
else
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? ORDER BY RANDOM()",
new String[] { String.valueOf(quest_number)});
cursor.moveToFirst();
// int cnt = cursor.getCount();
long qId = cursor.getLong(cursor.getColumnIndex("id_question"));
while (curQuestId == qId)
{
if (cursor.moveToNext())
qId = cursor.getLong(cursor.getColumnIndex("id_question"));
else
{
cursor.moveToFirst();
break;
}
}
}
// int cnt = cursor.getCount();
//let's get the selected question
Question question = new Question(
cursor.getLong(cursor.getColumnIndex("id_question")),
cursor.getString(cursor.getColumnIndex("text")),
quest_number,
cursor.getString(cursor.getColumnIndex("topic"))
);
System.out.println(question.getText()+" ; "+question.get_difficulty());
//this is the id of the question
long id = cursor.getLong(cursor.getColumnIndex("id_question"));
cursor.close();
//let's select the answers for this question
Cursor curs = myDataBase.rawQuery(
"SELECT * FROM Answer WHERE id_question = ?;",
new String[] { String.valueOf(id) }
);
Answer[] answers = new Answer[4];
if(curs != null){
if (curs.moveToFirst()){
int i = 0;
do{
answers[i++] = new Answer(
curs.getLong(curs.getColumnIndex("id_answer")),
curs.getString(curs.getColumnIndex("answer_text")),
id,
curs.getLong(curs.getColumnIndex("correct"))
);
} while(curs.moveToNext());
}
}
return new TestQuestion(question,answers);
}
[/syntax]
а в MainActivity я объявил его так:
[syntax=java5]AssetManager assetManager = getResources().getAssets();
InputStream inputStream;
mDbHelper = DatabaseHelper.getInstance(this);
mDbHelper.setDataBase(mDB = mDbHelper.getWritableDatabase());
Log.d("ATTENTION ", "BEFORE TRY body ");
try {
String fileName = "shool";
inputStream = assetManager.open (fileName);
if (inputStream != null){
BufferedReader br = new BufferedReader( new InputStreamReader(inputStream));
try{
String line;
while((line=br.readLine())!= null){
Log.d("",line);
mDB.execSQL(line);
if(line == "")
break;
}
}
catch (IOException e) {
e.printStackTrace();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
public void clearAll(){
mDB.delete(DatabaseHelper.TABLE_ANSWER, null, null);
Log.d("TABLE ", "ANSWER DELETED ");
mDB.delete(DatabaseHelper.TABLE_QUESTION, null, null);
Log.d("TABLE ", "QUESTION DELETED ");
}
[/syntax]
вот вроде бы ничего, все долно быть так. но стоит запустить эмулятор или на реальном устройстве, по логам видно, что он грузит базу данных 30-40 секунд.
Реализация DataBaseHelper:
[syntax=java5]public class DatabaseHelper extends SQLiteOpenHelper {
private static int DB_VERSION = 1;
private static final String DB_NAME = "shool";
public static final String TABLE_QUESTION = "Question";
public static final String TABLE_ANSWER = "Answer";
public boolean _should_fill = false;
private SQLiteDatabase myDataBase;
private DatabaseHelper(Context context){
super(context, DB_NAME, null, DB_VERSION);
}
private static DatabaseHelper instance;
public static DatabaseHelper getInstance(Context context)
{
if (instance == null)
instance = new DatabaseHelper(context);
return instance;
}
public void setDataBase(SQLiteDatabase db)
{
myDataBase = db;
}
@Override
public void onCreate(SQLiteDatabase myDataBase) {
_should_fill = true;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+ TABLE_QUESTION);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_ANSWER);
onCreate(db);
}
public TestQuestion getQuestionWithAnswers(String topic, long quest_number, long curQuestId)
{
Cursor cursor;
if (curQuestId == -1)
{
if (topic != null)
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? AND topic=? ORDER BY RANDOM() LIMIT 1",
new String[] { String.valueOf(quest_number), topic });
else
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? ORDER BY RANDOM() LIMIT 1",
new String[] { String.valueOf(quest_number)});
cursor.moveToFirst();
}
else
{
if (topic != null)
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? AND topic=? ORDER BY RANDOM()",
new String[] { String.valueOf(quest_number), topic });
else
cursor = myDataBase.rawQuery(
"SELECT * FROM Question WHERE difficulty=? ORDER BY RANDOM()",
new String[] { String.valueOf(quest_number)});
cursor.moveToFirst();
// int cnt = cursor.getCount();
long qId = cursor.getLong(cursor.getColumnIndex("id_question"));
while (curQuestId == qId)
{
if (cursor.moveToNext())
qId = cursor.getLong(cursor.getColumnIndex("id_question"));
else
{
cursor.moveToFirst();
break;
}
}
}
// int cnt = cursor.getCount();
//let's get the selected question
Question question = new Question(
cursor.getLong(cursor.getColumnIndex("id_question")),
cursor.getString(cursor.getColumnIndex("text")),
quest_number,
cursor.getString(cursor.getColumnIndex("topic"))
);
System.out.println(question.getText()+" ; "+question.get_difficulty());
//this is the id of the question
long id = cursor.getLong(cursor.getColumnIndex("id_question"));
cursor.close();
//let's select the answers for this question
Cursor curs = myDataBase.rawQuery(
"SELECT * FROM Answer WHERE id_question = ?;",
new String[] { String.valueOf(id) }
);
Answer[] answers = new Answer[4];
if(curs != null){
if (curs.moveToFirst()){
int i = 0;
do{
answers[i++] = new Answer(
curs.getLong(curs.getColumnIndex("id_answer")),
curs.getString(curs.getColumnIndex("answer_text")),
id,
curs.getLong(curs.getColumnIndex("correct"))
);
} while(curs.moveToNext());
}
}
return new TestQuestion(question,answers);
}
[/syntax]
а в MainActivity я объявил его так:
[syntax=java5]AssetManager assetManager = getResources().getAssets();
InputStream inputStream;
mDbHelper = DatabaseHelper.getInstance(this);
mDbHelper.setDataBase(mDB = mDbHelper.getWritableDatabase());
Log.d("ATTENTION ", "BEFORE TRY body ");
try {
String fileName = "shool";
inputStream = assetManager.open (fileName);
if (inputStream != null){
BufferedReader br = new BufferedReader( new InputStreamReader(inputStream));
try{
String line;
while((line=br.readLine())!= null){
Log.d("",line);
mDB.execSQL(line);
if(line == "")
break;
}
}
catch (IOException e) {
e.printStackTrace();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
public void clearAll(){
mDB.delete(DatabaseHelper.TABLE_ANSWER, null, null);
Log.d("TABLE ", "ANSWER DELETED ");
mDB.delete(DatabaseHelper.TABLE_QUESTION, null, null);
Log.d("TABLE ", "QUESTION DELETED ");
}
[/syntax]
вот вроде бы ничего, все долно быть так. но стоит запустить эмулятор или на реальном устройстве, по логам видно, что он грузит базу данных 30-40 секунд.