How can select random data from database and display in textview?

Question

I'm making a android quotes app which use data in database. i want to display random quote in listview when i click on next button. By the way, how can i save my selected in each time i open my app? please help! this is my Database class:

 public class SQLiteAdapter {
    public static final String MYDATABASE_NAME = "MY_DATABASE";
    public static final String MYDATABASE_TABLE = "MY_TABLE";
    public static final int MYDATABASE_VERSION = 1;
    public static final String KEY_ID = "_id";
    public static final String KEY_CONTENT = "Content";

//create table MY_DATABASE (ID integer primary key, Content text not null);
private static final String SCRIPT_CREATE_DATABASE =
    "create table " + MYDATABASE_TABLE + " ("
    + KEY_ID + " integer primary key autoincrement, "
    + KEY_CONTENT + " text not null);";

private SQLiteHelper sqLiteHelper;
private SQLiteDatabase sqLiteDatabase;

private Context context;

public SQLiteAdapter(Context c){
    context = c;
}

public SQLiteAdapter openToRead() throws android.database.SQLException {
    sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null,  MYDATABASE_VERSION);
    sqLiteDatabase = sqLiteHelper.getReadableDatabase();
    return this;    
}

public SQLiteAdapter openToWrite() throws android.database.SQLException {
    sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
    sqLiteDatabase = sqLiteHelper.getWritableDatabase();
    return this;    
}

public void close(){
    sqLiteHelper.close();
}

public long insert(String content){

    ContentValues contentValues = new ContentValues();
    contentValues.put(KEY_CONTENT, content);
    return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);
}

public int deleteAll(){
    return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null);
}

public Cursor queueAll(){
    String[] columns = new String[]{KEY_ID, KEY_CONTENT};
    Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, 
            null, null, null, null, null);

    return cursor;
}

public class SQLiteHelper extends SQLiteOpenHelper {

    public SQLiteHelper(Context context, String name,
            CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(SCRIPT_CREATE_DATABASE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

}

}

this is my xml file to display quote:

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity" >

<TextView
    android:id="@+id/textView1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentTop="true"
    android:layout_centerHorizontal="true"

  />

<Button
    android:id="@+id/next"
    android:layout_width="80dip"
    android:layout_height="wrap_content"
    android:layout_alignParentBottom="true"
    android:layout_alignRight="@+id/textView1"
    android:paddingBottom="5dip"
    android:paddingTop="5dip"
    android:text="Next"
    android:textColor="@android:color/black" />

Simon Schubert · Accepted Answer · 2012-12-29 15:46:56Z

1

The code is tested and working correctly. Good luck with your project. http://uploaded.net/file/9dqsjc9j Tip: If eclipse is missing imports in your java file press CTRL+SHIFT+O

AndroidSqlite.java

@Override
public void onCreate(Bundle savedInstanceState) {
    // ....
    mySQLiteAdapter = new SQLiteAdapter(this);
    mySQLiteAdapter.openToWrite();
    mySQLiteAdapter.deleteAll();

    mySQLiteAdapter.insert("A for Apply");
    // ....
    mySQLiteAdapter.insert("Z for Zoo");

    final TextView tv = (TextView)findViewById(R.id.textView1);
    Button btn = (Button)findViewById(R.id.next);
    btn.setOnClickListener(new OnClickListener(){
        public void onClick(View v)
        {
        tv.setText(mySQLiteAdapter.getRandomQuote());
        }

    });
}

@Override
public void onDestroy()
{
    super.onDestroy();
    mySQLiteAdapter.close();
}

Put this in your SQLiteAdapter.java

public String getRandomQuote()
{
Cursor c = sqLiteDatabase.query(MYDATABASE_TABLE + " ORDER BY RANDOM() LIMIT 1",
        new String[] { KEY_CONTENT }, null, null, null, null, null);

if(c.moveToFirst())
  return c.getString(c.getColumnIndex(KEY_CONTENT));
else 
  return "nothing";
}

edited Dec 29, 2012 at 15:46

answered Dec 29, 2012 at 13:45

Simon Schubert

2,02619 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

mr Z Over a year ago

there are have an error in this line: return c.getString(cursor.getColumnIndex(KEY_CONTENT). i can't fix it.

Simon Schubert Over a year ago

and what is the error saying if you hovering him with your mousepointer?

Ridcully Over a year ago

There's a ) missing at the end of that line: it should be return c.getString(cursor.getColumnIndex(KEY_CONTENT));

Ridcully Over a year ago

If you have lots of entries, you shouldn't use order by rand() but precalculate the line to use. See this article: titov.net/2005/09/21/…

mr Z Over a year ago

Multiple markers at this line - Syntax error, insert ")" to complete MethodInvocation - Syntax error, insert ";" to complete

|

Collectives™ on Stack Overflow

How can select random data from database and display in textview?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related