I'm working on a app where saxophone players can choose a saxophone (soprano, alto, tenor or baritone). The database should output all the music pieces that are available for that specific saxophone, together with the composer and publisher. I've used a prepopulated db (created with SQLite Manager) and used the SQLite Asset Helper. The database (so far) has some 600 records. Whenever I query the database (let's say on 'alto'), it takes some 10 seconds before the results are found. I've tried many things: indexing (both in the db file as in the Java code), changed between sqlite-file and db-file, tried on a real device instead of emulator etc, etc. Nothing seems to work... I hope someone out there can help me on this...
I use two java classes: MyDatabase and SQLiteOpenHelper. If you need more info, please let me know.
MyDatabase class:
package com.example.musicrepertoiredatabase2;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;
public class MyDatabase extends SQLiteAssetHelper {
SqliteAssetHelper helper = new SqliteAssetHelper();
private static final String DATABASE_NAME = "saxophone_repertoire.sqlite";
private static final String TABLE_NAME = "repertoire";
private static final int DATABASE_VERSION = 1;
private static final String ID = "id";
private static final String COMPOSER = "composer";
private static final String TITLE = "title";
private static final String PUBLISHER = "publisher";
private static final String SAXOPHONE = "saxophone";
private static final String ACCOMPANIMENT = "form";
public MyDatabase(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public Cursor getData() {
SQLiteDatabase db = this.getWritableDatabase();
String saxSort = SqliteAssetHelper.chooseSax;
String composer = SqliteAssetHelper.chooseComposer;
Cursor result = db.rawQuery("SELECT Composer, id FROM " + TABLE_NAME
+ " WHERE " + SAXOPHONE + "='" + saxSort + "'", null);
return result;
}
}
My SQLiteAssetHelper class:
package com.example.musicrepertoiredatabase2;
import android.app.ProgressDialog;
import android.database.Cursor;
import android.os.Bundle;
import android.os.Handler;
import android.support.v7.app.ActionBarActivity;
import android.text.Html;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;
public class SqliteAssetHelper extends ActionBarActivity implements
OnItemSelectedListener, OnClickListener {
TextView composer, title, saxophone, accompaniment, showInfo, count;
EditText etComposer, etTitle;
Spinner spinnerSaxophone, spinnerAccompaniment;
Button search, clear;
String[] arraySaxophone = { "Soprano", "Alto", "Tenor", "Baritone" };
String[] arrayAccompaniment = { "Solo", "Piano" };
public static String chooseSax = null;
public static String chooseComposer = null;
Handler updateBarHandler;
private MyDatabase db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
updateBarHandler = new Handler();
spinnerSaxophone = (Spinner) findViewById(R.id.spinner_saxophone);
spinnerSaxophone.setOnItemSelectedListener(this);
ArrayAdapter<String> adapter1 = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, arraySaxophone);
spinnerSaxophone.setAdapter(adapter1);
spinnerAccompaniment = (Spinner) findViewById(R.id.spinner_accompaniment);
spinnerAccompaniment.setOnItemSelectedListener(this);
ArrayAdapter<String> adapter2 = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, arrayAccompaniment);
spinnerAccompaniment.setAdapter(adapter2);
etComposer = (EditText) findViewById(R.id.et_composer);
etComposer.setHint(Html.fromHtml("<small>"
+ getString(R.string.hint_composer) + "<small>"));
etTitle = (EditText) findViewById(R.id.et_title);
etTitle.setHint(Html.fromHtml("<small>"
+ getString(R.string.hint_title) + "<small>"));
search = (Button) findViewById(R.id.btn_search);
clear = (Button) findViewById(R.id.btn_clear);
search.setOnClickListener(this);
clear.setOnClickListener(this);
showInfo = (TextView) findViewById(R.id.tv_showinfo);
count = (TextView) findViewById(R.id.tv_count);
db = new MyDatabase(this);
}
public void launchRingDialog() {
final ProgressDialog ringProgressDialog = ProgressDialog.show(this,
"Searching database...", "Please wait...", true);
ringProgressDialog.setCancelable(true);
new Thread(new Runnable(){
@Override
public void run(){
try{
Thread.sleep(5000);
}catch (Exception e){
}
ringProgressDialog.dismiss();
}
}).start();
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
int id = item.getItemId();
if (id == R.id.action_settings) {
return true;
}
return super.onOptionsItemSelected(item);
}
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position,
long id) {
int choosePositionSax = spinnerSaxophone.getSelectedItemPosition();
switch (choosePositionSax) {
case 0:
chooseSax = "Soprano";
break;
case 1:
chooseSax = "Alto";
break;
case 2:
chooseSax = "Tenor";
break;
case 3:
chooseSax = "Baritone";
break;
default:
break;
}
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_search:
launchRingDialog();
chooseComposer = etComposer.getText().toString();
Cursor result = db.getData();
if (result.getCount() == 0) {
Toast.makeText(this, "Nothing found", Toast.LENGTH_SHORT)
.show();
}
launchRingDialog();
StringBuffer buffer = new StringBuffer();
while (result.moveToNext()) {
//buffer.append("Title: " + result.getString(2) + "\n");
buffer.append("Composer: " + result.getString(1) + "\n\n");
showInfo.setText(buffer);
int countFiles = result.getCount();
count.setText(Integer.toString(countFiles));
}
break;
case R.id.btn_clear:
showInfo.setText("");
etComposer.setText("");
etComposer.requestFocus();
break;
default:
break;
}
}
}
Aucun commentaire:
Enregistrer un commentaire