Android MySQL → Android Retrofit MySQL ServerSide Multi-Column Search/Filter ListView – Camposha

Android MySQL

Android MySQL

Android MySQL

Android Retrofit MySQL ServerSide Multi-Column Search/Filter ListView

Android Retrofit MySQL Serverside Multi-Column Search/Filter ListView Tutorial

In this class we see how to perform a multi-column search filter against mysql database from our android app. We use Java to write our app and Retrofit as our HTTP Client. Furthermore we use ListView as our AdapterView. MySQL is our database while PHP is our serverside programming language.

Demo

Here’s the demo of the application:

Android Retrofit ServerSide Search Filter
Android Retrofit ServerSide Search Filter
Video Tutorial

We have a fast growing ProgrammingWizards TV YouTube Channel with tutorials like this. Here is this tutorial in video format:

1. PHP

First we need to write PHP code that will:

  1. Connect to mysql database using mysqli class.
  2. Perform a multi-column search against mysql using SQL statements.
  3. Return results to a PHP array.
  4. JSON-encode that array and print it to the caller.

We use Object Oriented paradigm to code our PHP. We have only one file:

index.php

<?php

class Constants
{
    //DATABASE DETAILS
    static $DB_SERVER="localhost";
    static $DB_NAME="spacecraftsDB";
    static $USERNAME="root";
    static $PASSWORD="";

    //STATEMENTS
    static $SQL_SELECT_ALL="SELECT * FROM spacecraftsTB";
}

class Spacecrafts
{
    /*******************************************************************************************************************************************/
    /*
       1.CONNECT TO DATABASE.
       1. RETURN CONNECTION OBJECT
    */
    public function connect()
    {
        $con=new mysqli(Constants::$DB_SERVER,Constants::$USERNAME,Constants::$PASSWORD,Constants::$DB_NAME);
        if($con->connect_error)
        {
            return null;
        }else
        {
            return $con;
        }
    }
    /*******************************************************************************************************************************************/
    /*
       1.SELECT FROM DATABASE.
    */
    public function search($query)
    {

        $sql="SELECT * FROM spacecraftsTB WHERE name LIKE '%$query%' OR propellant LIKE '%$query%' OR destination LIKE '%$query%' ";
         //$sql="SELECT * FROM spacecraftsTB WHERE name LIKE '%$query%' ";

        $con=$this->connect();
        if($con != null)
        {
            $result=$con->query($sql);
            if($result->num_rows > 0)
            {
                $spacecrafts=array();
                while($row=$result->fetch_array())
                {
                    array_push($spacecrafts, array("id"=>$row['id'],"name"=>$row['name'],"propellant"=>$row['propellant'],"destination"=>$row['destination'],"image_url"=>$row['image_url'],"technology_exists"=>$row['technology_exists']));
                }
                print(json_encode(array_reverse($spacecrafts)));
            }else
            {
                print(json_encode(array("No item Found that matches the query: ".$query)));
            }
            $con->close();

        }else{
            print(json_encode(array("PHP EXCEPTION : CAN'T CONNECT TO MYSQL. NULL CONNECTION.")));
        }
    }
    public function handleRequest() {
        if($_SERVER['REQUEST_METHOD'] == 'POST'){
            $query=$_POST['query'];
            $this->search($query);
        } else{
            $this->search("");
        }

    }
}
$spacecrafts=new Spacecrafts();
$spacecrafts->handleRequest();
//end

Setup

We are interested in setting up in two files:

  1. build.gradle(app) – We add our dependencies here.
  2. AndroidManifest.xml – We add internet connectivity permission here.
(a). build.gradle

Let’s go over to dependencies closure and add dependencies

dependencies {
    implementation fileTree(dir: 'libs', include: ['*.jar'])
    testImplementation 'junit:junit:4.12'
    implementation 'com.android.support:appcompat-v7:28.0.0'
    implementation 'com.android.support.constraint:constraint-layout:1.0.2'
    implementation "com.android.support:cardview-v7:28.0.0"
    implementation 'com.squareup.retrofit2:retrofit:2.4.0'
    implementation 'com.squareup.retrofit2:converter-gson:2.4.0'
    implementation 'com.squareup.picasso:picasso:2.71828'
}
(b). AndroidManifest.xml

Add internet connectivity permission here. Also register our DetailsActivity.

 <uses-permission android_name="android.permission.INTERNET"/>

    <application>
        <activity android_name=".MainActivity">
           ..
        </activity>
        <activity android_name=".DetailsActivity" android_parentActivityName=".MainActivity"/>
    </application>

Our Classes

We have only two files:

  1. MainActivity.java
  2. DetailsActivity.java
(a). MainActivity.java

Our main activity.

package info.camposha.retrofitserversidesearch;
import android.content.Context;
import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.SearchView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.CheckBox;
import android.widget.Filter;
import android.widget.Filterable;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.ProgressBar;
import android.widget.TextView;
import android.widget.Toast;
import com.google.gson.annotations.SerializedName;
import com.squareup.picasso.Picasso;
import java.util.ArrayList;
import java.util.List;
import retrofit2.Call;
import retrofit2.Callback;
import retrofit2.Response;
import retrofit2.Retrofit;
import retrofit2.converter.gson.GsonConverterFactory;
import retrofit2.http.Body;
import retrofit2.http.Field;
import retrofit2.http.GET;
import retrofit2.http.POST;
import retrofit2.http.FormUrlEncoded;
public class MainActivity extends AppCompatActivity {
//private static final String BASE_URL = "http://10.0.2.2"; or "http://10.0.3.2" or your computers ip address
private static final String BASE_URL = "http://192.168.12.2";//replace this wih the ip address for your computer
private static final String FULL_URL = BASE_URL+"/PHP/spaceship/";
class Spacecraft {
@SerializedName("id")
private int id;
@SerializedName("name")
private String name;
@SerializedName("propellant")
private String propellant;
@SerializedName("destination")
private String destination;
@SerializedName("image_url")
private String imageURL;
@SerializedName("technology_exists")
private int technologyExists;
public Spacecraft(String name){
this.name=name;
}
public Spacecraft(int id, String name, String propellant,String destination, String imageURL, int technologyExists) {
this.id = id;
this.name = name;
this.propellant = propellant;
this.destination=destination;
this.imageURL = imageURL;
this.technologyExists = technologyExists;
}
/*
*GETTERS AND SETTERS
*/
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public String getPropellant() {
return propellant;
}
public String getDestination() {
return destination;
}
public String getImageURL() {
return imageURL;
}
public int getTechnologyExists() {
return technologyExists;
}
@Override
public String toString() {
return name;
}
}
interface MyAPIService {
@GET("/PHP/spaceship")
Call<List<Spacecraft>> getSpacecrafts();
@FormUrlEncoded
@POST("/PHP/spaceship/index.php")
Call<List<Spacecraft>> searchSpacecraft(@Field("query") String query);
}
static class RetrofitClientInstance {
private static Retrofit retrofit;
public static Retrofit getRetrofitInstance() {
if (retrofit == null) {
retrofit = new Retrofit.Builder()
.baseUrl(BASE_URL)
.addConverterFactory(GsonConverterFactory.create())
.build();
}
return retrofit;
}
}
class ListViewAdapter extends BaseAdapter {
private List<Spacecraft> spacecrafts;
private Context context;
public ListViewAdapter(Context context, List<Spacecraft> spacecrafts) {
this.context = context;
this.spacecrafts = spacecrafts;
}
@Override
public int getCount() {
return spacecrafts.size();
}
@Override
public Object getItem(int pos) {
return spacecrafts.get(pos);
}
@Override
public long getItemId(int pos) {
return pos;
}
@Override
public View getView(int position, View view, ViewGroup viewGroup) {
if (view == null) {
view = LayoutInflater.from(context).inflate(R.layout.model, viewGroup, false);
}
TextView nameTxt = view.findViewById(R.id.nameTextView);
TextView txtPropellant = view.findViewById(R.id.propellantTextView);
CheckBox chkTechExists = view.findViewById(R.id.myCheckBox);
ImageView spacecraftImageView = view.findViewById(R.id.spacecraftImageView);
final Spacecraft thisSpacecraft = spacecrafts.get(position);
nameTxt.setText(thisSpacecraft.getName());
txtPropellant.setText(thisSpacecraft.getPropellant());
chkTechExists.setChecked(thisSpacecraft.getTechnologyExists() == 1);
chkTechExists.setEnabled(false);
if (thisSpacecraft.getImageURL() != null && thisSpacecraft.getImageURL().length() > 0) {
Picasso.get().load(FULL_URL + "/images/" + thisSpacecraft.getImageURL()).placeholder(R.drawable.placeholder).into(spacecraftImageView);
} else {
Toast.makeText(context, "Empty Image URL", Toast.LENGTH_LONG).show();
Picasso.get().load(R.drawable.placeholder).into(spacecraftImageView);
}
view.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Toast.makeText(context, thisSpacecraft.getName(), Toast.LENGTH_SHORT).show();
String techExists = "";
if (thisSpacecraft.getTechnologyExists() == 1) {
techExists = "YES";
} else {
techExists = "NO";
}
String[] spacecrafts = {
thisSpacecraft.getName(),
thisSpacecraft.getPropellant(),
thisSpacecraft.getDestination(),
techExists,
FULL_URL + "/images/" + thisSpacecraft.getImageURL()
};
openDetailActivity(spacecrafts);
}
});
return view;
}
private void openDetailActivity(String[] data) {
Intent intent = new Intent(MainActivity.this, DetailsActivity.class);
intent.putExtra("NAME_KEY", data[0]);
intent.putExtra("PROPELLANT_KEY", data[1]);
intent.putExtra("DESTINATION_KEY", data[2]);
intent.putExtra("TECHNOLOGY_EXISTS_KEY", data[3]);
intent.putExtra("IMAGE_KEY", data[4]);
startActivity(intent);
}
}
private ListViewAdapter adapter;
private ListView mListView;
private ProgressBar mProgressBar;
private SearchView mSearchView;
private void initializeWidgets(){
mListView = findViewById(R.id.mListView);
mProgressBar= findViewById(R.id.mProgressBar);
mProgressBar.setIndeterminate(true);
mProgressBar.setVisibility(View.VISIBLE);
mSearchView=findViewById(R.id.mSearchView);
mSearchView.setIconified(true);
}
private void populateListView(List<Spacecraft> spacecraftList) {
adapter = new ListViewAdapter(this,spacecraftList);
mListView.setAdapter(adapter);
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
this.initializeWidgets();
/*Create handle for the RetrofitInstance interface*/
final MyAPIService myAPIService = RetrofitClientInstance.getRetrofitInstance().create(MyAPIService.class);
//Call<List<Spacecraft>> call = myAPIService.getSpacecrafts();
final Call<List<Spacecraft>> call = myAPIService.searchSpacecraft("");
call.enqueue(new Callback<List<Spacecraft>>() {
@Override
public void onResponse(Call<List<Spacecraft>> call, Response<List<Spacecraft>> response) {
mProgressBar.setVisibility(View.GONE);
populateListView(response.body());
}
@Override
public void onFailure(Call<List<Spacecraft>> call, Throwable throwable) {
mProgressBar.setVisibility(View.GONE);
Toast.makeText(MainActivity.this, throwable.getMessage(), Toast.LENGTH_LONG).show();
}
});
mSearchView.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
@Override
public boolean onQueryTextSubmit(String query) {
return false;
}
@Override
public boolean onQueryTextChange(String query) {
final Call<List<Spacecraft>> call = myAPIService.searchSpacecraft(query);
call.enqueue(new Callback<List<Spacecraft>>() {
@Override
public void onResponse(Call<List<Spacecraft>> call, Response<List<Spacecraft>> response) {
mProgressBar.setVisibility(View.GONE);
populateListView(response.body());
}
@Override
public void onFailure(Call<List<Spacecraft>> call, Throwable throwable) {
populateListView(new ArrayList<Spacecraft>());
mProgressBar.setVisibility(View.GONE);
Toast.makeText(MainActivity.this, "ERROR: "+throwable.getMessage(), Toast.LENGTH_LONG).show();
}
});
return false;
}
});
}
}
//end
(b). DetailsActivity.java

Our details activity.

package info.camposha.retrofitserversidesearch;
import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.widget.CheckBox;
import android.widget.ImageView;
import android.widget.TextView;
import com.squareup.picasso.Picasso;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DetailsActivity extends AppCompatActivity {
TextView nameDetailTextView,propellantDetailTextView,dateDetailTextView,destinationDetailTextView;
CheckBox techExistsDetailCheckBox;
ImageView teacherDetailImageView;
private void initializeWidgets(){
nameDetailTextView= findViewById(R.id.nameDetailTextView);
propellantDetailTextView= findViewById(R.id.propellantDetailTextView);
dateDetailTextView= findViewById(R.id.dateDetailTextView);
destinationDetailTextView=findViewById(R.id.destinationDetailTextView);
techExistsDetailCheckBox= findViewById(R.id.techExistsDetailCheckBox);
teacherDetailImageView=findViewById(R.id.teacherDetailImageView);
}
private String getDateToday(){
DateFormat dateFormat=new SimpleDateFormat("yyyy/MM/dd");
Date date=new Date();
String today= dateFormat.format(date);
return today;
}
private void receiveAndShowData(){
//RECEIVE DATA FROM ITEMS ACTIVITY VIA INTENT
Intent i=this.getIntent();
String name=i.getExtras().getString("NAME_KEY");
String propellant=i.getExtras().getString("PROPELLANT_KEY");
String destination=i.getExtras().getString("DESTINATION_KEY");
String technologyExists=i.getExtras().getString("TECHNOLOGY_EXISTS_KEY");
String imageURL=i.getExtras().getString("IMAGE_KEY");
//SET RECEIVED DATA TO TEXTVIEWS AND IMAGEVIEWS
nameDetailTextView.setText(name);
propellantDetailTextView.setText(propellant);
dateDetailTextView.setText(getDateToday());
destinationDetailTextView.setText(destination);
techExistsDetailCheckBox.setChecked(technologyExists.equalsIgnoreCase("YES"));
techExistsDetailCheckBox.setEnabled(false);
Picasso.get().load(imageURL).placeholder(R.drawable.placeholder).into(teacherDetailImageView);
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_detail);
initializeWidgets();
receiveAndShowData();
}
}

Our Layouts

We have these layout files:

  1. activity_main.xml
  2. activity_detail.xml
  3. model
(a). activity_main.xml

This is the layout for the main activity.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 
android_layout_width="match_parent"
android_layout_height="match_parent"
android_orientation="vertical"
tools_context=".MainActivity">
<TextView
android_id="@+id/headerTxt"
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_text="Retrofit MySQL Serverside Search"
android_padding="5dp"
android_textAlignment="center"
android_textStyle="bold"
android_textAppearance="@style/TextAppearance.AppCompat.Large"
android_textColor="@color/colorAccent" />
<ProgressBar
android_id="@+id/mProgressBar"
style="?android:attr/progressBarStyleHorizontal"
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_indeterminate="true"
android_indeterminateBehavior="cycle"
android_visibility="gone" />
<android.support.v7.widget.SearchView
android_id="@+id/mSearchView"
app_queryHint="Search.."
android_layout_width="match_parent"
android_layout_height="wrap_content" />
<ListView
android_id="@+id/mListView"
android_layout_weight="0.5"
android_numColumns="auto_fit"
android_layout_width="match_parent"
android_layout_height="wrap_content" />
</LinearLayout>
(b). activity_detail.xml

This is the layout for the detail activity.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 
android_layout_width="match_parent"
android_layout_height="match_parent"
android_background="#009688"
tools_context=".DetailsActivity"
tools_showIn="@layout/activity_detail">
<android.support.v7.widget.CardView 
android_layout_width="match_parent"
android_layout_height="match_parent"
android_layout_margin="3dp"
card_view_cardCornerRadius="3dp"
card_view_cardElevation="5dp">
<ScrollView
android_layout_width="match_parent"
android_layout_height="match_parent">
<LinearLayout
android_layout_width="match_parent"
android_layout_height="match_parent"
android_orientation="vertical">
<ImageView
android_id="@+id/teacherDetailImageView"
android_layout_width="match_parent"
android_layout_height="250dp"
android_layout_alignParentTop="true"
android_padding="5dp"
android_scaleType="fitXY"
android_src="@drawable/placeholder" />
<LinearLayout
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_orientation="vertical">
<LinearLayout
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_orientation="horizontal">
<TextView
android_id="@+id/nameLabel"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="NAME : "
android_textAppearance="?android:attr/textAppearanceLarge"
android_textStyle="bold" />
<TextView
android_id="@+id/nameDetailTextView"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="Voyager"
android_textAppearance="?android:attr/textAppearanceLarge"/>
</LinearLayout>
<LinearLayout
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_orientation="horizontal">
<TextView
android_id="@+id/dateLabel"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="DATE : "
android_textAppearance="?android:attr/textAppearanceLarge"
android_textStyle="bold" />
<TextView
android_id="@+id/dateDetailTextView"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="Today"
android_textAppearance="?android:attr/textAppearanceLarge"/>
</LinearLayout>
<LinearLayout
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_orientation="horizontal">
<TextView
android_id="@+id/destinationLabel"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="DESTINATION : "
android_textAppearance="?android:attr/textAppearanceLarge"
android_textStyle="bold" />
<TextView
android_id="@+id/destinationDetailTextView"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="KY Cygni"
android_textAppearance="?android:attr/textAppearanceLarge"/>
</LinearLayout>
<LinearLayout
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_orientation="horizontal">
<TextView
android_id="@+id/propellantLabel"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="PROPELLANT : "
android_textAppearance="?android:attr/textAppearanceLarge"
android_textStyle="bold" />
<TextView
android_id="@+id/propellantDetailTextView"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="Chemical"
android_textAppearance="?android:attr/textAppearanceLarge"/>
</LinearLayout>
<LinearLayout
android_layout_width="match_parent"
android_layout_height="wrap_content"
android_orientation="horizontal">
<TextView
android_id="@+id/techExistsLabel"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_maxLines="1"
android_padding="5dp"
android_text="TECH EXISTS? : "
android_textAppearance="?android:attr/textAppearanceLarge"
android_textStyle="bold" />
<CheckBox
android_id="@+id/techExistsDetailCheckBox"
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_padding="5dp"/>
</LinearLayout>
</LinearLayout>
</LinearLayout>
</ScrollView>
</android.support.v7.widget.CardView>
</RelativeLayout>
(b). model.xml

Our row model layout.

<?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView 
android_orientation="horizontal" android_layout_width="match_parent"
android_layout_margin="5dp"
card_view_cardCornerRadius="10dp"
card_view_cardElevation="5dp"
android_layout_height="match_parent">
<RelativeLayout
android_layout_width="match_parent"
android_layout_height="match_parent">
<ImageView
android_layout_width="150dp"
android_layout_height="150dp"
android_id="@+id/spacecraftImageView"
android_padding="5dp"
android_scaleType="fitXY"
android_src="@drawable/placeholder" />
<TextView
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_textAppearance="?android:attr/textAppearanceLarge"
android_text="Spacecraft Name"
android_id="@+id/nameTextView"
android_padding="5dp"
android_textColor="@color/colorAccent"
android_layout_alignParentTop="true"
android_layout_toRightOf="@+id/spacecraftImageView" />
<TextView
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_textAppearance="?android:attr/textAppearanceMedium"
android_text="Propellant"
android_textStyle="italic"
android_id="@+id/propellantTextView"
android_padding="5dp"
android_layout_alignBottom="@+id/spacecraftImageView"
android_layout_toRightOf="@+id/spacecraftImageView" />
<CheckBox
android_layout_width="wrap_content"
android_layout_height="wrap_content"
android_id="@+id/myCheckBox"
android_text="Tech Exists?"
android_layout_alignParentRight="true" />
</RelativeLayout>
</android.support.v7.widget.CardView>
Download

Register as a member to download code. I will support members as soon as possible when they encounter problems. If you are already registered, just login to download code from any lesson.

 

Download “Retrofit MySQL Multi-Column Serverside Search” – Downloaded 0 times –

Leave a Reply

Your email address will not be published. Required fields are marked *

Join Us
X