Jump to content

Incorporar BD sqlite Existente em Android


Psycop
 Share

Recommended Posts

Olá

Estou a iniciar-me em Android + BD e até já consegui fazer um mini jogo quiz, mas o que acontece é que agora me estou a deparar com um problema ao gerar os apk, onde a BD não tem registos e estoura a app.

Eu consegui colocar a funcionar no AVD indo ao Android Device Manager e fazendo o upload da BD manualmente.

Como poderei fazer para que a BD seja sempre copiada juntamente com a App?

O código do meu DbHelper é o seguinte:

public class DbHelper extends SQLiteOpenHelper
{
//Database Version
private static final int DATABASE_VERSION = 1;
//Database Name
private static final String DATABASE_NAME = "Quiz.sqlite";
//Nome da Tabela
private static final String TABLE_QUIZ = "quiz";
//Nomes das colunas da tabela
private static final String KEY_ID = "id";
private static final String KEY_PERGUNTA = "pergunta"; //pergunta
private static final String KEY_RESPOSTA = "resposta"; //resposta correcta
private static final String KEY_OPC_A = "opc_a";
private static final String KEY_OPC_B = "opc_b";
private static final String KEY_OPC_C = "opc_c";
private static final String KEY_OPC_D = "opc_d";
private SQLiteDatabase dbase;
//public DbHelper(Context context){super(context, DATABASE_NAME, null, DATABASE_VERSION);}

//*************************************
public DbHelper(Context context){
 super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

//Criar a Base de Dados
@Override
public void onCreate(SQLiteDatabase db) {
 dbase = db;
 String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_QUIZ + " ( "
		 + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_PERGUNTA
		 + " TEXT, " + KEY_RESPOSTA + " TEXT, " + KEY_OPC_A + " TEXT, "
		 + KEY_OPC_B + " TEXT, " + KEY_OPC_C + " TEXT, " + KEY_OPC_D + " TEXT)";
 db.execSQL(sql);
 //db.close();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 //eliminar a Antiga BD caso exista
 db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUIZ);
 //criar de novo as tabelas
 onCreate(db);
}
}

Li umas coisas sobre usar a BD na pasta Assets mas não consegui encontrar um exemplo que entenda, podem me ajudar?

Cumprimentos

Edited by Psycop
Link to comment
Share on other sites

Bom Dia

Segui esse tutorial mas continuo a ter algum erro que estoura com a abertura da app.

na classe DbHelper.java tenho o seguinte:

public class DbHelper extends SQLiteOpenHelper
{
//Database Version
private static final int DATABASE_VERSION = 1;
//Path Database
private static String DATABASE_PATH = "/data/data/com.quiz.quiz/databases/";
//Database Name
private static final String DATABASE_NAME = "Quiz";
//Nome da Tabela
private static final String TABLE_QUIZ = "quiz";
//Nomes das colunas da tabela
private static final String KEY_ID = "id";
private static final String KEY_PERGUNTA = "pergunta"; //pergunta
private static final String KEY_RESPOSTA = "resposta"; //resposta correcta
private static final String KEY_OPC_A = "opc_a";
private static final String KEY_OPC_B = "opc_b";
private static final String KEY_OPC_C = "opc_c";
private static final String KEY_OPC_D = "opc_d";
private SQLiteDatabase dbase;
//public DbHelper(Context context){super(context, DATABASE_NAME, null, DATABASE_VERSION);}
private final Context myContext;
//*************************************
public DbHelper(Context context){
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
	this.myContext = context;

}

/**
 * Creates a empty database on the system and rewrites it with your own database.
 * */
public void createDataBase() throws IOException{
	boolean dbExist = checkDataBase();
	if(dbExist){
		//do nothing - database already exist
	}else{
		//By calling this method and empty database will be created into the default system path
		//of your application so we are gonna be able to overwrite that database with our database.
		this.getReadableDatabase();
		try {
			copyDataBase();
		} catch (IOException e) {
			throw new Error("Error copying database");
		}
	}
}
/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
private boolean checkDataBase(){
	SQLiteDatabase checkDB = null;
	try{
		String myPath = DATABASE_PATH + DATABASE_NAME;
		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
	}catch(SQLiteException e){
		//database does't exist yet.
	}
	if(checkDB != null){
		checkDB.close();
	}
	return checkDB != null ? true : false;
}
/**
 * Copies your database from your local assets-folder to the just created empty database in the
 * system folder, from where it can be accessed and handled.
 * This is done by transfering bytestream.
 * */
private void copyDataBase() throws IOException{
	//Open your local db as the input stream
	InputStream myInput = myContext.getAssets().open(DATABASE_NAME);
	// Path to the just created empty db
	String outFileName = DATABASE_PATH + DATABASE_NAME;
	//Open the empty db as the output stream
	OutputStream myOutput = new FileOutputStream(outFileName);
	//transfer bytes from the inputfile to the outputfile
	byte[] buffer = new byte[1024];
	int length;
	while ((length = myInput.read(buffer))>0){
		myOutput.write(buffer, 0, length);
	}
	//Close the streams
	myOutput.flush();
	myOutput.close();
	myInput.close();
}
public void openDataBase() throws SQLException{
	//Open the database
	String myPath = DATABASE_PATH + DATABASE_NAME;
	dbase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}
@Override
public synchronized void close() {
	if(dbase != null)
		dbase.close();
	super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
//Metodo Listar Todas as Perguntas
public List<Pergunta> getTodasPerguntas()
{
	List<Pergunta> listaPerguntas = new ArrayList<Pergunta>();
	//Seleccionar a Query
	String selectQuery = "SELECT *FROM " + TABLE_QUIZ;
	dbase = this.getReadableDatabase();
	Cursor cursor = dbase.rawQuery(selectQuery, null);
	//Loop por todas as linhas da BD e adiciona-las a lista
	if(cursor.moveToFirst())
	{
		do{
			Pergunta pergunt = new Pergunta();
			pergunt.setID(cursor.getInt(0));
			pergunt.setPERGUNTA(cursor.getString(1));
			pergunt.setRESPOSTA(cursor.getString(2));
			pergunt.setOPC_A(cursor.getString(3));
			pergunt.setOPC_B(cursor.getString(4));
			pergunt.setOPC_C(cursor.getString(5));
			pergunt.setOPC_D(cursor.getString(6));
			listaPerguntas.add(pergunt);
		}while(cursor.moveToNext());
	}
	//Return listaPerguntas;
	return listaPerguntas;
}

//Metodo Listar as Perguntas Aleatoriamente - Count 10 Perguntas (Fácil)
public List<Pergunta> getRandomPerguntas_10()
{
	List<Pergunta> listaPerguntas = new ArrayList<Pergunta>();
	//Seleccionar a Query
	String selectQuery = "SELECT *FROM " + TABLE_QUIZ + " ORDER BY RANDOM() LIMIT 10";
	dbase = this.getReadableDatabase();
	Cursor cursor = dbase.rawQuery(selectQuery, null);
	//Loop por todas as linhas da BD e adiciona-las a lista
	if(cursor.moveToFirst())
	{
		do{
			Pergunta pergunt = new Pergunta();
			pergunt.setID(cursor.getInt(0));
			pergunt.setPERGUNTA(cursor.getString(1));
			pergunt.setRESPOSTA(cursor.getString(2));
			pergunt.setOPC_A(cursor.getString(3));
			pergunt.setOPC_B(cursor.getString(4));
			pergunt.setOPC_C(cursor.getString(5));
			pergunt.setOPC_D(cursor.getString(6));
			listaPerguntas.add(pergunt);
		}while(cursor.moveToNext());
	}
	//Return listaPerguntas;
	return listaPerguntas;
}

//Método contar linhas
public int rowcount()
{
	int row = 0;
	String selectQuery = "SELECT *FROM " + TABLE_QUIZ;
	SQLiteDatabase db = this.getWritableDatabase();
	Cursor cursor = db.rawQuery(selectQuery, null);
	row = cursor.getCount();
	return row;
}

}

e na classe MainActivity instanciei o DbHelper para ele copiar a BD logo ao inicio.

public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
 super.onCreate(savedInstanceState);
 setContentView(R.layout.activity_main);
 DbHelper myDbHelper = new DbHelper(this);
 try {
	 myDbHelper.createDataBase();
 } catch (IOException ioe) {
	 throw new Error("Unable to create database");
 }
 try {
	 myDbHelper.openDataBase();
 }catch(SQLException sqle){
	 throw sqle;
 }
}
}

No debug encontrei o seguinte erro:

[quote08-29 10:04:26.210 1604-1604/? E/SQLiteLog﹕ (1) no such table: quiz

08-29 10:04:26.225 1604-1604/? D/AndroidRuntime﹕ Shutting down VM

08-29 10:04:26.288 1604-1604/? E/AndroidRuntime﹕ FATAL EXCEPTION: main

Process: com.motorquiz.nuno.motorquiz, PID: 1604

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.quiz.quiz/com.quiz.quiz.QuizActivity}: android.database.sqlite.SQLiteException: no such table: quiz (code 1): , while compiling: SELECT *FROM quiz ORDER BY RANDOM() LIMIT 10

at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2325)

at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2390)

at android.app.ActivityThread.access$800(ActivityThread.java:151)

at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)

at android.os.Handler.dispatchMessage(Handler.java:102)

at android.os.Looper.loop(Looper.java:135)

at android.app.ActivityThread.main(ActivityThread.java:5257)

at java.lang.reflect.Method.invoke(Native Method)

at java.lang.reflect.Method.invoke(Method.java:372)

at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)

at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

Caused by: android.database.sqlite.SQLiteException: no such table: quiz (code 1): , while compiling: SELECT *FROM quiz ORDER BY RANDOM() LIMIT 10

at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)

at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)

at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)

at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)

at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)

at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)

at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)

at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)

at com.motorquiz.nuno.motorquiz.DbHelper.getRandomPerguntas_10(DbHelper.java:212)

at com.motorquiz.nuno.motorquiz.QuizActivity.onCreate(QuizActivity.java:49)

at android.app.Activity.performCreate(Activity.java:5990)

at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)

at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)

at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2390)

at android.app.ActivityThread.access$800(ActivityThread.java:151)

at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)

at android.os.Handler.dispatchMessage(Handler.java:102)

at android.os.Looper.loop(Looper.java:135)

at android.app.ActivityThread.main(ActivityThread.java:5257)

at java.lang.reflect.Method.invoke(Native Method)

at java.lang.reflect.Method.invoke(Method.java:372)

at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)

at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

]

Tenho o ficheiro da BD na pasta assets, alguém te ideia do que posso estar a fazer de errado?

cumps

Edited by Psycop
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.