Android Connect MySQL Database Programmatically

ASH

7 years ago

In this post we will see How we can Connect Android to MySQL Database 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.

So, Let’s begin.

Android Connect MySQL Database Programmatically :

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.

Application Working :

Application will first try to connect with the MySQL Database and on successful Database connection, android application will display data from a table on a Android TextView. It will connect via IP address of the Database with a correct Database Credentials.

Download Source code.

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')

Select * from tblCountries
select distinct Country from tblCountries

Now, open your activity_main.xml file and edit it as below :

activity_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:textStyle="bold" />

<TextView
android:id="@+id/txtData"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="2dp"
android:text="Your Data will be Displayed here"
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" />

<Button
android:id="@+id/btnClear"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
android:layout_margin="2dp"
android:background="@color/colorAccent"
android:minWidth="250dp"
android:text="Clear"
android:textColor="#fff" />

</LinearLayout>

The layout contains a Android TextView to show our Table data from MySQL Database and two buttons, one for connecting to Database and other for clearing the TextView content.

Now, let’s code it.

MainActivity.java:

Declare this variables in your Java class

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,btnClear;
TextView txtData;

And initialize them onCreate method :

txtData = (TextView) this.findViewById(R.id.txtData);
btnFetch = (Button) findViewById(R.id.btnFetch);
btnClear = (Button) findViewById(R.id.btnClear);

url,user,pass must be valid and correct credentials of your mySql database for a successful Android-MySQL Connection.

192.168.0.192 is my Laptop’s IP Address and 3306 is my Port number to connect on.

Make an Android ASyncTask. This will accept the command and try to connect with your Database and on successful connection with MySQL assign a String variable with the table data.

Code :

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

@Override
protected void onPreExecute() {
super.onPreExecute();
Toast.makeText(MainActivity.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();

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);
}
}

Full Code of MainActivity.java file

MainActivity.java:

package parallelcodes.mysqlapp;

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.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;

public class MainActivity 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,btnClear;
TextView txtData;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
txtData = (TextView) this.findViewById(R.id.txtData);
btnFetch = (Button) findViewById(R.id.btnFetch);
btnClear = (Button) findViewById(R.id.btnClear);
btnFetch.setOnClickListener(new View.OnClickListener() {

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

}

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

@Override
protected void onPreExecute() {
super.onPreExecute();
Toast.makeText(MainActivity.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();

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);
}
}

}

And now run your application. On Pressing on the “Fetch Data” button it will fetch Data from MySQL Database table.

Note : If you are on personal Laptop or PC with WiFi router connected to it and your Android Device connected to the same router, it will work, i.e. It must be same network to work in. If your Database is out your Device’s network, it will not work. Also, if you are using Cellular Data network, your Database must be on Internet and it should be able to be connected remotely.