How to use ORDER BY in Android sqlite?

AndroidMobile DevelopmentApps/Applications

<p>Before getting into example, we should know what sqlite data base in android is. SQLite is an open source SQL database that stores data to a text file on a device. Android comes in with built in SQLite database implementation. SQLite supports all the relational database features. In order to access this database, you don&#39;t need to establish any kind of connections for it like JDBC, ODBC etc.</p><p>This example demonstrate about How to use ORDER BY in Android sqlite.</p><p><strong>Step 1</strong> &minus; Create a new project in Android Studio, go to File &rArr; New Project and fill all required details to create a new project.</p><p><strong>Step 2</strong> &minus; Add the following code to res/layout/activity_main.xml.</p><pre class="prettyprint notranslate">&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt; &lt;LinearLayout xmlns:android=&quot;; &nbsp; &nbsp;xmlns:tools=&quot;; &nbsp; &nbsp;android:layout_width=&quot;match_parent&quot; &nbsp; &nbsp;android:layout_height=&quot;match_parent&quot; &nbsp; &nbsp;tools:context=&quot;.MainActivity&quot; &nbsp; &nbsp;android:orientation=&quot;vertical&quot;&gt; &nbsp; &nbsp;&lt;EditText &nbsp; &nbsp; &nbsp; android:id=&quot;@+id/name&quot; &nbsp; &nbsp; &nbsp; android:layout_width=&quot;match_parent&quot; &nbsp; &nbsp; &nbsp; android:hint=&quot;Enter Name&quot; &nbsp; &nbsp; &nbsp; android:layout_height=&quot;wrap_content&quot; /&gt; &nbsp; &nbsp;&lt;EditText &nbsp; &nbsp; &nbsp; android:id=&quot;@+id/salary&quot; &nbsp; &nbsp; &nbsp; android:layout_width=&quot;match_parent&quot; &nbsp; &nbsp; &nbsp; android:inputType=&quot;numberDecimal&quot; &nbsp; &nbsp; &nbsp; android:hint=&quot;Enter Salary&quot; &nbsp; &nbsp; &nbsp; android:layout_height=&quot;wrap_content&quot; /&gt; &nbsp; &nbsp;&lt;LinearLayout &nbsp; &nbsp; &nbsp; android:layout_width=&quot;wrap_content&quot; &nbsp; &nbsp; &nbsp; android:layout_height=&quot;wrap_content&quot;&gt;&lt;Button &nbsp; &nbsp; &nbsp; android:id=&quot;@+id/save&quot; &nbsp; &nbsp; &nbsp; android:text=&quot;Save&quot; &nbsp; &nbsp; &nbsp; android:layout_width=&quot;wrap_content&quot; &nbsp; &nbsp; &nbsp; android:layout_height=&quot;wrap_content&quot; /&gt; &nbsp; &nbsp; &nbsp; &lt;Button &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:id=&quot;@+id/refresh&quot; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:text=&quot;Refresh&quot; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:layout_width=&quot;wrap_content&quot; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:layout_height=&quot;wrap_content&quot; /&gt; &nbsp; &nbsp; &nbsp; &lt;Button &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:id=&quot;@+id/udate&quot; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:text=&quot;Update&quot; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:layout_width=&quot;wrap_content&quot; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;android:layout_height=&quot;wrap_content&quot; /&gt; &nbsp; &nbsp;&lt;/LinearLayout&gt; &nbsp; &nbsp;&lt;ListView &nbsp; &nbsp; &nbsp; android:id=&quot;@+id/listView&quot; &nbsp; &nbsp; &nbsp; android:layout_width=&quot;match_parent&quot; &nbsp; &nbsp; &nbsp; android:layout_height=&quot;wrap_content&quot;&gt; &nbsp; &nbsp;&lt;/ListView&gt; &lt;/LinearLayout&gt;</pre><p>In the above code, we have taken name and salary as Edit text, when user click on save button it will store the data into sqlite data base. Click on refresh button after insert values to update listview from cursor <strong>WITH ORDER</strong>. If User click on update button it will update the data.</p><p><strong>Step 3</strong> &minus; Add the following code to src/</p><pre class="prettyprint notranslate">package com.example.andy.myapplication; import android.os.Bundle; import; import android.view.View; import android.widget.ArrayAdapter; import android.widget.Button; import android.widget.EditText; import android.widget.ListView; import android.widget.Toast; import java.util.ArrayList; public class MainActivity extends AppCompatActivity { &nbsp; &nbsp;Button save, refresh; &nbsp; &nbsp;EditText name, salary; &nbsp; &nbsp;private ListView listView; &nbsp; &nbsp;@Override &nbsp; &nbsp;protected void onCreate(Bundle readdInstanceState) { &nbsp; &nbsp; &nbsp; super.onCreate(readdInstanceState); &nbsp; &nbsp; &nbsp; setContentView(R.layout.activity_main); &nbsp; &nbsp; &nbsp; final DatabaseHelper helper = new DatabaseHelper(this); &nbsp; &nbsp; &nbsp; final ArrayList array_list = helper.getAllCotacts(); &nbsp; &nbsp; &nbsp; name = findViewById(; &nbsp; &nbsp; &nbsp; salary = findViewById(; &nbsp; &nbsp; &nbsp; listView = findViewById(; &nbsp; &nbsp; &nbsp; final ArrayAdapter arrayAdapter = new ArrayAdapter(MainActivity.this, android.R.layout.simple_list_item_1, array_list); &nbsp; &nbsp; &nbsp; listView.setAdapter(arrayAdapter); &nbsp; &nbsp; &nbsp; findViewById( View.OnClickListener() { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@Override &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;public void onClick(View v) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; array_list.clear(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; array_list.addAll(helper.getAllCotacts()); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; arrayAdapter.notifyDataSetChanged(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; listView.invalidateViews(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; listView.refreshDrawableState(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; }); &nbsp; &nbsp; &nbsp; findViewById( View.OnClickListener() { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@Override &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;public void onClick(View v) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!name.getText().toString().isEmpty() &amp;&amp; !salary.getText().toString().isEmpty()) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if (helper.insert(name.getText().toString(), salary.getText().toString())) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Toast.makeText(MainActivity.this, &quot;Inserted&quot;, Toast.LENGTH_LONG).show(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} else { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Toast.makeText(MainActivity.this, &quot;NOT Inserted&quot;, Toast.LENGTH_LONG).show(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;name.setError(&quot;Enter NAME&quot;); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;salary.setError(&quot;Enter Salary&quot;); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; }); &nbsp; &nbsp;} }</pre><p><strong>Step 4</strong> &minus; Add the following code to src/</p><pre class="prettyprint notranslate">package com.example.andy.myapplication; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; import; import java.util.ArrayList; class DatabaseHelper extends SQLiteOpenHelper { &nbsp; &nbsp;public static final String DATABASE_NAME = &quot;salaryDatabase5&quot;; &nbsp; &nbsp;public static final String CONTACTS_TABLE_NAME = &quot;SalaryDetails&quot;; &nbsp; &nbsp;public DatabaseHelper(Context context) { &nbsp; &nbsp; &nbsp; super(context,DATABASE_NAME,null,1); &nbsp; &nbsp;} &nbsp; &nbsp;@Override &nbsp; &nbsp;public void onCreate(SQLiteDatabase db) { &nbsp; &nbsp; &nbsp; try { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;db.execSQL( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &quot;create table &quot;+ CONTACTS_TABLE_NAME +&quot;(id INTEGER PRIMARY KEY, name text,salary text,datetime default current_timestamp )&quot; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;); &nbsp; &nbsp; &nbsp; } catch (SQLiteException e) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;try { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw new IOException(e); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} catch (IOException e1) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e1.printStackTrace(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp;} &nbsp; &nbsp;@Override &nbsp; &nbsp;public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { &nbsp; &nbsp; &nbsp; db.execSQL(&quot;DROP TABLE IF EXISTS &quot;+CONTACTS_TABLE_NAME); &nbsp; &nbsp; &nbsp; onCreate(db); &nbsp; &nbsp;} &nbsp; &nbsp;public boolean insert(String s, String s1) { &nbsp; &nbsp; &nbsp; SQLiteDatabase db = this.getWritableDatabase(); &nbsp; &nbsp; &nbsp; ContentValues contentValues = new ContentValues(); &nbsp; &nbsp; &nbsp; contentValues.put(&quot;name&quot;, s); &nbsp; &nbsp; &nbsp; contentValues.put(&quot;salary&quot;, s1); &nbsp; &nbsp; &nbsp; db.replace(CONTACTS_TABLE_NAME, null, contentValues); &nbsp; &nbsp; &nbsp; return true; &nbsp; &nbsp;} &nbsp; &nbsp;public ArrayList getAllCotacts() { &nbsp; &nbsp; &nbsp; SQLiteDatabase db = this.getReadableDatabase(); &nbsp; &nbsp; &nbsp; ArrayList&lt;String&gt; array_list = new ArrayList&lt;String&gt;(); &nbsp; &nbsp; &nbsp; Cursor res = db.rawQuery( &quot;select * from &quot;+CONTACTS_TABLE_NAME+&quot; ORDER BY name&quot;, null ); &nbsp; &nbsp; &nbsp; res.moveToFirst(); &nbsp; &nbsp; &nbsp; while(res.isAfterLast() == false) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;array_list.add(res.getString(res.getColumnIndex(&quot;name&quot;))); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;res.moveToNext(); &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; return array_list; &nbsp; &nbsp;} &nbsp; &nbsp;public boolean update(String s, String s1) { &nbsp; &nbsp; &nbsp; SQLiteDatabase db = this.getWritableDatabase(); &nbsp; &nbsp; &nbsp; db.execSQL(&quot;UPDATE &quot;+CONTACTS_TABLE_NAME+&quot; SET name = &quot;+&quot;&#39;&quot;+s+&quot;&#39;, &quot;+ &quot;salary = &quot;+&quot;&#39;&quot;+s1+&quot;&#39;&quot;); &nbsp; &nbsp; &nbsp; return true; &nbsp; &nbsp;} &nbsp; &nbsp;public boolean delete() { &nbsp; &nbsp; &nbsp; SQLiteDatabase db = this.getWritableDatabase(); &nbsp; &nbsp; &nbsp; db.execSQL(&quot;DELETE from &quot;+CONTACTS_TABLE_NAME); &nbsp; &nbsp; &nbsp; return true; &nbsp; &nbsp;} }</pre><p>Let&#39;s try to run your application. I assume you have connected your actual Android Mobile device with your computer. To run the app from android studio, open one of your project&#39;s activity files and click Run <img src="" class="fr-fic fr-dii" width="14" height="14">&nbsp;icon from the toolbar. Select your mobile device as an option and then check your mobile device which will display your default screen &minus;</p><p><img src="" class="fr-fic fr-dib" style="width:278px; height=:556px;" width="278" height="556"></p><p>In the above result, we are inserting some values after save all records now click on refresh button, it will show name values with ASC order as shown below &minus;</p><p><img src="" class="fr-fic fr-dib" style="width:278px; height=:556px;" width="278" height="556"></p><p>Click <a href="/android/projects/how_to_use_order_by_in_android_sqlite/" rel="nofollow" target="_blank">here</a> to download the project code</p>
Updated on 30-Jul-2019 22:30:25