Android Bind ListView from MySQL Database

In this post we will see How we can Bind Android ListView from MySQL Database table using mysql-connector.jar. You can download Jar Library using this Link.

Note : I’m not using any type of Webservice or other web api library.

Android Bind ListView from MySQL Database:

Create a new Android Application in Android Studio with

Package name : parallelcodes.mysqlapp

Application name : MySQLApp

You can always name your Package and Application name according to your requirements.

Download Source code.

Application Working :

Application will connect with MySQL Database instance and fetch data from the specified database mysql table and display it on Android Listview.

MySQL Database Script :

create schema myDB

use myDB

create table tblCountries
(
ID int NOT NULL AUTO_INCREMENT primary key,
Country varchar(255) NOT NULL
)

Insert into tblCountries (Country) values ('India')
Insert into tblCountries (Country) values ('Australia')
Insert into tblCountries (Country) values ('Mauritius')
Insert into tblCountries (Country) values ('USA')
Insert into tblCountries (Country) values ('England')
Insert into tblCountries (Country) values ('New Zealand')
Insert into tblCountries (Country) values ('Spain')
Insert into tblCountries (Country) values ('China')
Insert into tblCountries (Country) values ('Maldives')
Insert into tblCountries (Country) values ('New Zealand')
Insert into tblCountries (Country) values ('South Africa')
Insert into tblCountries (Country) values ('West Indies')
Insert into tblCountries (Country) values ('Nepal')
Insert into tblCountries (Country) values ('Sri Lanka')
Insert into tblCountries (Country) values ('Russia')
Insert into tblCountries (Country) values ('Germany')
 
Select * from tblCountries
select distinct Country from tblCountries

Now, create list_main.xml in res > layout folder open your list_main.xml file and edit it as below :

list_main.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:fitsSystemWindows="true"
android:orientation="vertical"
android:padding="5dp">

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="5dp"
android:padding="5dp"
android:text="Android MySQL Application"
android:textColor="@color/colorAccent"
android:textSize="20sp"
android:id="@+id/txtData"
android:textStyle="bold" />

<Button
android:id="@+id/btnFetch"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
android:layout_margin="2dp"
android:background="@color/colorPrimaryDark"
android:minWidth="250dp"
android:text="Fetch Data"
android:textColor="#fff" />

<ListView
android:id="@+id/lstData"
android:divider="#000"
android:dividerHeight="1dp"
android:layout_width="match_parent"
android:layout_height="wrap_content" />

</LinearLayout>

Now create listemplate.xml and edit it as below:

listtemplate.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">

<TextView
android:id="@+id/lblcountryname"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="25sp" />
</LinearLayout>

Create class file ListActivity.java and edit it as below:

ListActivity.java:

package com.parallelcodes.sql;

import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
import android.widget.Toast;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class ListActivity extends AppCompatActivity {

    private static final String url = "jdbc:mysql://192.168.0.192:3306/myDB";
    private static final String user = "hitesh";
    private static final String pass = "1234";
    Button btnFetch;
    TextView txtData;
    ListView lstData;
    SimpleAdapter ADAhere;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.list_main);
        txtData = (TextView) this.findViewById(R.id.txtData);
        btnFetch = (Button) findViewById(R.id.btnFetch);

        btnFetch.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                ConnectMySql connectMySql = new ConnectMySql();
                connectMySql.execute("");
            }
        });

        lstData = (ListView) findViewById(R.id.lstData);
    }

    private class ConnectMySql extends AsyncTask<String, Void, String> {
        String res = "";

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

        }

        @Override
        protected String doInBackground(String... params) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection con = DriverManager.getConnection(url, user, pass);
                System.out.println("Databaseection success");

                String result = "Database Connection Successful\n";
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery("select distinct Country from tblCountries");
                ResultSetMetaData rsmd = rs.getMetaData();

                List<Map<String, String>> data = null;
                data = new ArrayList<Map<String, String>>();

                while (rs.next()) {
                    Map<String, String> datanum = new HashMap<String, String>();
                    datanum.put("A", rs.getString(1).toString());
                    data.add(datanum);
                }

                String[] fromwhere = { "A" };
                int[] viewswhere = { R.id.lblcountryname };
                ADAhere = new SimpleAdapter(ListActivity.this, data,
                        R.layout.listtemplate, fromwhere, viewswhere);

                while (rs.next()) {
                    result += rs.getString(1).toString() + "\n";
                }
                res = result;
            } catch (Exception e) {
                e.printStackTrace();
                res = e.toString();
            }
            return res;
        }

        @Override
        protected void onPostExecute(String result) {
            txtData.setText(result);
            lstData.setAdapter(ADAhere);
        }
    }

}

AndroidManifest.xml:

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.parallelcodes.sql">
<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=".ListActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />

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

</manifest>

Download Source code.