How to get Data From SQLite Database In Android

Before starting this Tutorial you should know

Please follow the steps below in order to get all data from SQLite database table and show it on your Android:

Step 1) First of all we’re going to take a button and this button we are going to use to view all data. So we change the text as view all and the button ID also I’m going to change as button.

Step 2) Now once our button is created what we’re going to do is we re going to create a method to get all the table data. So we are going to go to our database helper class and inside this database helper class we are going to create a new method and this method we are going to call as get all data which will be a public method and its going to return a class or an instance of a class called curser.

This curser will show some error so you just need to press alt+enter to import the class which is curser class.

Step 3) Now we’re going to create the instance of database class just as we have done in the previous tutorial.

Step 4) Then we will create an instance of our curser class and name it as, for examples, 'rest' for result and then we will take an instance of our database and call a raw query on this database so raw query. And if you know how to query SQLite database, you may know how to query all the data from the table. So this is a simple query we going to write

The table an argument which we are going to pass it as null for now.

Note: (*) asterisk stands for all from table.

And now we just need to return the instance of the cursor which is RES.

This cursor class is the interface which provides the random read-write access to your result okay. So you can see here we are querying the database and the result we are storing it in the cursor instance and using this we have an access to our data.

Read Here About How to insert data into SQLite database in Android

Step 5) Now we are going to our main activity .java class and in here, first of all, we will create the variable for view all button and also we will cast this button.

Step 6) And now once we have casted our button we can use the button object to call set on click listener. Let's create a method, it will be a public method which will take no argument and in here we can call the object of our view all button and we can call set on click listener, set on click listener and inside this we will create a new on click listener and when this button is clicked we want to perform some action.

Step 7) Now we are going to get all data using this function which we have just created get all data. So we will use the instance of our database helper class which is 'mydDb'. And we are going to save it as cursor because it returns as an object of the cursor. Now object 'res' has some properties. If we call this 'res' object and we can get the count of the lines. This is the result count we are getting, if this is equal to 0, then it means that there is no data available for us. From this get all data, by querying to the database.

Step 8) If there is no result then we are going to show arrow and return, otherwise if there is some other result then we are going to create some string buffer and then we are going to display this data, so we are going to create and instance of string buffer and then we are going to get all the data one by one using this RES object and how we can do it? we can use a while loop and as a condition of this while loop we can take this RES object and we can call a method move to next.

Step 9) Then we are going to get the result and we will store it in the buffer. And In order to append the result, we will write the name of our columns and then the index of the column.

So the index of the column starts from zero. And if you remember our table was containing four columns. first was ID, the second was name, third was a surname and fourth was marks. So the index of ID will be 0, index of the name will be 1, index of the surname will be 2 and index of marks will be 3.

Note: Just give double line break to the last column so that next data is printed, it is printed after line break

Step 10) Now we want to shows all the data. So let's create a new method will be also a public method and it will return nothing so void. This method is going to take 2 arguments. First is the title and second is the message itself.

Step 11) And in here we are going to create an instance of alert dialogue builder and it takes the argument which is the context so this itself and using this builder we can create an alert dialogue, okay so we can set title and set message using this builder to alert dialogue. So lets create, first of all, lets use this builder to set cancellable. And then we will set the title, and then we will set the message. Okay and then we can just call show method on this builder. This will show our dialogue or alert dialogue.

So just copy this showmessage function here. First of all, if no data is found, what we’re going to do is, we’re going to show the message.

And when the data is found in here, we can just show some message that data the dialogue.

Step 12) One more thing which is remaining is we need to call viewAll() method inside our onCreate method so just copy this view all method and paste it inside your onCreate method of your main activity.

Okay, now lets run our programme. So our app is running now, so when we create this view all button we will be able to see this data

Complete Code

public Cursor getAllData(){
	SQLiteDatabase db = this.getWritableDatabase();
	Cursor res = db.rawQuery("select * from "+TABLE_NAME,null);
	return res;
}

public class MainActivity extends ActionBarActivity{
	DatabaseHelper myDb;
	EditText editName,editSurname,editMarks;
	Button btnAddData;
	Button btnviewAll;
}

public void viewAll(){
	btnviewAll.setOnClickListener(
	new View.OnClickListener(){
		@Override
		public void onClick(View v){
			
		}
	}
	)
}

@Override
public void onClick(View v){
	Cursor res = myDb.getAllData();
	if(res.getCount() == 0){
		return;
	}
}

StringBuffer buffer = new StringBuffer();
while (res.moveToNext()){
	buffer.append("Id: "+ res.getString(0)+"n");
	buffer.append("Name: "+ res.getString(1)+"n");
	buffer.append("Surname: "+ res.getString(2)+"n");
	buffer.append("Marks: "+ res.getString(3)+"n");
}

public static final Sting Col_1 = "ID";
public static final Sting Col_2 = "Name";
public static final Sting Col_3 = "Surname";
public static final Sting Col_4 = "Marks";

public void showMessage(Spring title, String Message){
	AlertDialog.Builder builder = new AlertDialog.Builder(this);
	builder.setCancelable(true);
	builder.setTitle(title);
	builder.setMessage(Message);
	builder.show();
}