Android Bind Expandable Listview from MS SQL Database

In this post we will bind Android Expandable listview from ms sql database. We will create SQL Database first containing Movies information. The expandable listview will contain Movies name and Year it released in as its header information.

Download source code.

Please download JTDS jar file library. We will use this library to connect our android application to ms sql database and bind our Expandable Listview using this.

jtds.jar file download.

MS SQL Database script:

Create database ParallelCodes

use ParallelCodes

create table tblMovies
(
Id int primary key identity(1,1) not null,
Movie nvarchar(50),
ReleasedYear int
)

Insert into tblMovies (Movie,ReleasedYear) values ('Moonlight', '2016')
Insert into tblMovies (Movie,ReleasedYear) values ('Arrival', '2016')
Insert into tblMovies (Movie,ReleasedYear) values ('La La Land', '2016')
Insert into tblMovies (Movie,ReleasedYear) values ('Deadpool', '2016')

Insert into tblMovies (Movie,ReleasedYear) values ('Wonder Woman', '2017')
Insert into tblMovies (Movie,ReleasedYear) values ('Dunkirk', '2017')
Insert into tblMovies (Movie,ReleasedYear) values ('Get Out', '2017')
Insert into tblMovies (Movie,ReleasedYear) values ('Logan', '2017')

Insert into tblMovies (Movie,ReleasedYear) values ('Black Panther', '2018')
Insert into tblMovies (Movie,ReleasedYear) values ('Avengers Infinity War', '2018')
Insert into tblMovies (Movie,ReleasedYear) values ('A Quiet Place', '2018')
Insert into tblMovies (Movie,ReleasedYear) values ('Aquaman', '2018')

Insert into tblMovies (Movie,ReleasedYear) values ('Toy Story 4', '2019')
Insert into tblMovies (Movie,ReleasedYear) values ('Avengers Endgame', '2019')
Insert into tblMovies (Movie,ReleasedYear) values ('Knives Out', '2019')
Insert into tblMovies (Movie,ReleasedYear) values ('Ford vs Ferrari', '2019')

Select * from tblMovies order by ReleasedYear asc

Now let’s create the application.

First, please download following icons from IconFinder:

1. Plus icon
2. Minus icon

Project structure:

Please copy the icons as shown in the image above.

Create layout and drawable selector android xml files as below:

res > drawable >  listselection.xml:

<?xml version="1.0" encoding="utf-8"?>
<selector xmlns:android="http://schemas.android.com/apk/res/android">
    <item android:drawable="@drawable/minus" android:state_empty="false" />
    <item android:drawable="@drawable/minus" android:state_expanded="true" />
    <item android:drawable="@drawable/plus" />
</selector>

res > layout > mylist.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">


    <ExpandableListView
        android:id="@+id/myExpandableList"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:divider="#000"
        android:dividerHeight="1dp"
        android:groupIndicator="@drawable/listselection"/>

</LinearLayout>

res > layout > listparent.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical">

    <TextView
        android:id="@+id/txtHeader"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:padding="10dp"
        android:textSize="25sp"
        android:textStyle="bold"
        android:textColor="#000" />
</LinearLayout>

res > layout > listchilds.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:orientation="vertical">

    <TextView
        android:id="@+id/txtChild"
        android:padding="20dp"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textColor="#4E4E4E"
        android:textSize="20sp"/>
</LinearLayout>

The listparent will be our Header design layout file and listchild will be our childs design layout file respectively. The mylist xml file is our main layout file.

Create a new class file with name SQLConnection.java and edit it as below:

SQLConnection.java:

package com.app.expandablelist;

import android.annotation.SuppressLint;
import android.os.StrictMode;

import java.sql.Connection;

import android.util.Log;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

public class SQLConnection {

    @SuppressLint("NewApi")
    public Connection CONN() {

        String _user = "sa";
        String _pass = "789";
        String _DB = "ParallelCodes";
        String _server = "192.168.0.104";
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
                .permitAll().build();
        StrictMode.setThreadPolicy(policy);
        Connection conn = null;
        String ConnURL = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            ConnURL = "jdbc:jtds:sqlserver://" + _server + ";"
                    + "databaseName=" + _DB + ";user=" + _user + ";password="
                    + _pass + ";";
            conn = DriverManager.getConnection(ConnURL);
        } catch (SQLException se) {
            Log.e("ERRO", se.getMessage());
        } catch (ClassNotFoundException e) {
            Log.e("ERRO", e.getMessage());
        } catch (Exception e) {
            Log.e("ERRO", e.getMessage());
        }
        return conn;
    }

    public LinkedHashMap<String, List<String>> GetMovies() {

        ArrayList<MoviesInfo> movies = new ArrayList<MoviesInfo>();
        List<String> year = new ArrayList<String>();
        List<String> MovieData = new ArrayList<String>();
        LinkedHashMap<String, List<String>> listLinkedHashMap
                = new LinkedHashMap<String, List<String>>();
        try {
            Connection connect = CONN();
            PreparedStatement stmt = connect.prepareStatement("Select * from tblMovies order by ReleasedYear asc");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                MoviesInfo info = new MoviesInfo();
                info.MovieName = rs.getString("Movie");
                info.ReleasedYear = rs.getString("ReleasedYear");
                movies.add(info);

                if (!year.contains(rs.getString("ReleasedYear")))
                    year.add(rs.getString("ReleasedYear"));
            }


            for (int i = 0; i < year.size(); i++) {
                MovieData = new ArrayList<String>();

                for (MoviesInfo m : movies) {

                    if (m.ReleasedYear.equals(year.get(i)))
                        MovieData.add(m.MovieName);
                }
                listLinkedHashMap.put(year.get(i).toString(), MovieData);
            }

            return listLinkedHashMap;
        } catch (Exception ex) {
            return null;
        }
    }

    public class MoviesInfo {

        public String MovieName;
        public String ReleasedYear;

        public MoviesInfo() {

        }
    }

}

This class will return a SQL connection and also a LinkedHashMap object to bind our Expandable Listview. Create listadapter.java and edit it as below

listadapter.java :

package com.app.expandablelist;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseExpandableListAdapter;
import android.widget.TextView;

import java.util.LinkedHashMap;
import java.util.List;

public class listadapter extends BaseExpandableListAdapter {

    private Context context;
    private List<String> year;
    private LinkedHashMap<String, List<String>> movies;

    public listadapter(Context context, List<String> year,
                                       LinkedHashMap<String, List<String>> movieName) {
        this.context = context;
        this.year = year;
        this.movies = movieName;
    }

    @Override
    public Object getChild(int listPosition, int expandedListPosition) {
        return this.movies.get(this.year.get(listPosition))
                .get(expandedListPosition);
    }

    @Override
    public long getChildId(int listPosition, int expandedListPosition) {
        return expandedListPosition;
    }

    @Override
    public View getChildView(int listPosition, final int expandedListPosition,
                             boolean isLastChild, View convertView, ViewGroup parent) {
        final String expandedListText = (String) getChild(listPosition, expandedListPosition);
        if (convertView == null) {
            LayoutInflater layoutInflater = (LayoutInflater) this.context
                    .getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            convertView = layoutInflater.inflate(R.layout.listchilds, null);
        }
        TextView expandedListTextView = convertView.findViewById(R.id.txtChild);
        expandedListTextView.setText(expandedListText);
        return convertView;
    }

    @Override
    public int getChildrenCount(int listPosition) {
        return this.movies.get(this.year.get(listPosition))
                .size();
    }

    @Override
    public Object getGroup(int listPosition) {
        return this.year.get(listPosition);
    }

    @Override
    public int getGroupCount() {
        return this.year.size();
    }

    @Override
    public long getGroupId(int listPosition) {
        return listPosition;
    }

    @Override
    public View getGroupView(int listPosition, boolean isExpanded,
                             View convertView, ViewGroup parent) {
        String listTitle = (String) getGroup(listPosition);
        if (convertView == null) {
            LayoutInflater layoutInflater = (LayoutInflater) this.context.
                    getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            convertView = layoutInflater.inflate(R.layout.listparent, null);
        }
        TextView listTitleTextView = convertView.findViewById(R.id.txtHeader);

        listTitleTextView.setText(listTitle);
        return convertView;
    }

    @Override
    public boolean hasStableIds() {
        return false;
    }

    @Override
    public boolean isChildSelectable(int listPosition, int expandedListPosition) {
        return true;
    }
}

This class is our Adapter for Expandable listview. Create MyList.java and edit it as below:

MyList.java:

package com.app.expandablelist;

import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.DisplayMetrics;
import android.view.View;
import android.widget.ExpandableListView;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

public class MyList extends AppCompatActivity {

    ExpandableListView myExpandableList;
    listadapter expandableListAdapter;
    List<String> moviesTitle;
    LinkedHashMap<String, List<String>> moviesDetail;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.mylist);
        myExpandableList = findViewById(R.id.myExpandableList);
        DisplayMetrics metrics = new DisplayMetrics();

        getWindowManager().getDefaultDisplay().getMetrics(metrics);

        int width = metrics.widthPixels;

        myExpandableList.setIndicatorBounds(width - GetPixelFromDips(50),
                width - GetPixelFromDips(5));

        myExpandableList.setOnGroupExpandListener(new ExpandableListView.OnGroupExpandListener() {

            @Override
            public void onGroupExpand(int groupPosition) {
                Toast.makeText(getApplicationContext(),
                        moviesTitle.get(groupPosition) + " expanded.",
                        Toast.LENGTH_SHORT).show();
            }
        });

        myExpandableList.setOnGroupCollapseListener(new ExpandableListView.OnGroupCollapseListener() {

            @Override
            public void onGroupCollapse(int groupPosition) {
                Toast.makeText(getApplicationContext(),
                        moviesTitle.get(groupPosition) + " collapsed.",
                        Toast.LENGTH_SHORT).show();

            }
        });

        GetData getData = new GetData();
        getData.execute("");

        myExpandableList.setOnChildClickListener(new ExpandableListView.OnChildClickListener() {
            @Override
            public boolean onChildClick(ExpandableListView parent, View v,
                                        int groupPosition, int childPosition, long id) {
                Toast.makeText(
                        getApplicationContext(),
                        moviesDetail.get(
                                moviesTitle.get(groupPosition)).get(
                                childPosition), Toast.LENGTH_SHORT
                ).show();
                return false;
            }
        });
    }

    public int GetPixelFromDips(float pixels) {
        // Get the screen's density scale
        final float scale = getResources().getDisplayMetrics().density;
        // Convert the dps to pixels, based on density scale
        return (int) (pixels * scale + 0.5f);
    }

    private class GetData extends AsyncTask<String, Void, String> {

        String res = "";
        ArrayList<String> data;
        ProgressDialog pd1;

        @Override
        protected void onPreExecute() {
            super.onPreExecute();
            Toast.makeText(MyList.this, "Please wait...", Toast.LENGTH_SHORT)
                    .show();

        }

        @Override
        protected String doInBackground(String... params) {

            try {
                SQLConnection connection = new SQLConnection();

                moviesDetail = connection.GetMovies();
                if (moviesDetail == null)
                    res = "Object is null";
                else {
                    moviesTitle = new ArrayList<String>(moviesDetail.keySet());

                    expandableListAdapter = new listadapter(MyList.this, moviesTitle, moviesDetail);
                }
                res = "1";
            } catch (Exception e) {
                res = "error:" + e.getMessage().toString();
            }

            return res;
        }

        @Override
        protected void onPostExecute(String result) {
            if (result.equals("1")) {
                myExpandableList.setAdapter(expandableListAdapter);
            } else {
                Toast.makeText(MyList.this, result, Toast.LENGTH_SHORT).show();

            }
        }
    }


}

This class is our Layout activity file.

Edit res>values>strings.xml as below:

res > values > strings.xml:

<resources>
    <string name="app_name">ExpandableList</string>
    <string name="action_settings">Settings</string>
    <string name="movies_header">Movies by Year</string>
</resources>

Edit colors.xml as below:

res > values > colors.xml:

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <color name="colorPrimary">#3949AB</color>
    <color name="colorPrimaryDark">#1F2966</color>
    <color name="colorAccent">#000</color>
</resources>

Lastly, add internet permission in AndroidManifest.xml file as below:

AndroidManifest.xml:

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.app.expandablelist">

    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
    <uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity
            android:name=".MyList"
            android:label="@string/movies_header"
            android:theme="@style/AppTheme">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Now, run your app to see Android Expandable Listview from sql database in action.

Download source code.