In my last post, I described a quick way to set up an Amazon MySQL RDS (Relational Database Service).
In this post, I’m going to build an Android app which uses JDBC to search that database, and list results.
Caveat: As I mentioned in my previous post, this is a “quick and dirty” way of doing things, and it’s not recommended to do things exactly this way. However, this method is fine when you’re building a proof of concept or a demo and you need to get things done quickly. It took me an afternoon to throw together a working demo using this method!
To get started on your app, fire up Android Studio and create a “New Project” with an “Empty Activity”. Accept all the defaults, but make sure your app is for Java (unless you want to work with Kotlin).
We just want to add a few simple items for the user interface: a text input for searching on a term, a button to submit the search term, and a scrollview that can be used to display results. Let’s do that now.
When I created my empty activity, a new layout file was added called activity_main.xml. I opened that up in the design view, and added the widgets that I wanted. Eventually, I finished the layout by customizing it in the text view. Here’s the final layout:
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
tools:context=".MainActivity">
<EditText
android:id="@+id/editText"
android:layout_width="357dp"
android:layout_height="48dp"
android:ems="10"
android:hint="Enter Search term and hit button for results"
android:inputType="text"
app:layout_constraintTop_toTopOf="parent"
android:layout_marginTop="8dp"
android:layout_marginBottom="8dp"
android:layout_marginRight="8dp"
app:layout_constraintRight_toRightOf="parent"
android:layout_marginLeft="8dp"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintHorizontal_bias="0.513"
android:layout_marginStart="8dp"
android:layout_marginEnd="8dp"/>
<Button
android:id="@+id/btnSearch"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Search"
android:layout_marginRight="8dp"
app:layout_constraintRight_toRightOf="parent"
android:layout_marginLeft="8dp"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintHorizontal_bias="0.502"
app:layout_constraintTop_toBottomOf="@+id/editText"
android:layout_marginStart="8dp"
android:layout_marginEnd="8dp"
android:layout_marginTop="8dp"/>
<ScrollView
android:id="@+id/scrollview"
android:layout_width="wrap_content"
android:layout_height="0dp"
android:layout_margin="8dp"
app:layout_constraintBottom_toTopOf="@+id/textView"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintRight_toRightOf="parent"
app:layout_constraintTop_toBottomOf="@+id/btnSearch">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<TextView
android:id="@+id/tvResults"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:text=""></TextView>
</LinearLayout>
</ScrollView>
<TextView
android:id="@+id/textView"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginBottom="8dp"
android:text="Type in text, click a button to search"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintRight_toRightOf="parent" />
</androidx.constraintlayout.widget.ConstraintLayout>
It looks like a lot, but it isn’t. Android layout files are quite verbose! One comment: notice that the ScrollView has a layout height of 0dp. It took me a few minutes of searching to figure out that this was necessary. Prior to doing that, the ScrollView results overlapped the search button and instructional text.
Notice that I’ve set Android @+ids for the parts that I need to access programmatically. I need to be able to click the search Button (@+id/btnSearch), get text from the input EditText (@+id/editText), and display text in the ScrollView‘s TextView (@+id/tvResults).
Next, I opened the MainActivity class, and added the methods needed to click the button, get results, and display them – like this:
package com.fullstackoasis.myapplication;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.text.Editable;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
public class MainActivity extends AppCompatActivity implements AsyncResponse {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Button b = (Button)this.findViewById(R.id.btnSearch);
b.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
searchByName();
}
});
}
protected void searchByName() {
EditText et = (EditText)findViewById(R.id.editText);
Editable editable = et.getText();
String s = editable.toString();
Log.d("MainActivity", "searchByName " + s);
if (s.length() > 2) {
MySQLAsyncTask mySQLAsyncTask = new MySQLAsyncTask();
mySQLAsyncTask.setDelegate(this);
mySQLAsyncTask.execute(s);
} else {
displayResults("Please type in at least 3 letters, for example 'Italian'");
}
}
public void processFinish(String result) {
if (result.length() > 502) {
Log.d("MainActivity:", "processFinish " + result.substring(0, 500));
} else {
Log.d("MainActivity:", "processFinish " + result);
}
displayResults(result);
}
private void displayResults(String res) {
TextView tvResults = (TextView)findViewById(R.id.tvResults);
tvResults.setText(res);
}
}
Now I only needed one more crucial bit, the Java class which contacts the Amazon RDS. I added a new Java class by clicking the menu item File > New > Java Class, and chose the name MySQLAsyncTask. I had it extend AsyncTask. The source for that class is shown next. If you copy this code for your own working demo, you will have to edit the url string to use your own RDS endpoint. Also, notice the big warnings about checking in files into source control if they contain hard-coded strings that would make your credentials publicly available! I’m not going to go into how to handle that here, but just don’t do it.
package com.fullstackoasis.myapplication;
import android.os.AsyncTask;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.PreparedStatement;
final class MySQLAsyncTask extends AsyncTask<String, Void, String> {
private static final String url = "jdbc:mysql://healthdata-1.c84gpzpanfrn.us-east-1.rds.amazonaws.com:3306/food_inspections";
private static final String user = "MY_WONDERFUL_USER_NAME"; // WARNING! DO NOT CHECK IN YOUR CREDENTIALS INTO PUBLIC SOURCE CONTROL
private static final String pass = "MY_WONDERFUL_PASSWORD"; // WARNING! DO NOT CHECK IN YOUR CREDENTIALS INTO PUBLIC SOURCE CONTROL
private static String res;
private AsyncResponse delegate = null;
void setDelegate(AsyncResponse d) {
delegate = d;
}
@Override
protected void onPreExecute() {
super.onPreExecute();
}
@Override
protected String doInBackground(String... params) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection(url, user, pass);
System.out.println("Database Connection success " + params);
String result = "Database Connection Successful\n";
// Let's search by dba_name and / or aka name.
// Limit results by to top 10 results, and let user scroll
PreparedStatement ps = con.prepareStatement("SELECT * FROM health_reports" +
" WHERE " +
"dba_name LIKE ? OR aka_name LIKE ? LIMIT 10");
String searchPartial = params[0] + "%"; // LIKE 'Blah%'
ps.setString(1, searchPartial);
ps.setString(2, searchPartial);
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
String sep = " | ";
while (rs.next()) {
result += rs.getInt(1) + sep + // id
rs.getInt(2) + sep + // inspection_id
rs.getString(3) + sep + // dba_name
rs.getString(4) + sep + // aka_name
rs.getInt(5) + sep + // license_num
rs.getString(6) + sep + // facility_type
rs.getString(7) + sep + // risk
rs.getString(8) + sep + // address
rs.getString(9) + sep + // city
rs.getString(10) + sep + // state
rs.getString(11) + sep; // zip
try {
result += rs.getString(12).toString() + sep; // inspection_date
} catch (Exception e) {
// e.printStackTrace();
}
result += rs.getString(13) + sep + // inspection_type
rs.getString(14) + sep + // results
rs.getString(15) + sep + // violations
rs.getString(16) + sep // location
;
result += System.lineSeparator();
result += "------------";
result += System.lineSeparator();
}
res = result;
if (res.length() > 502) {
Log.d("Task:", "Database Result success " + result.substring(0, 500));
} else {
Log.d("Task:", "Database Result success " + result);
}
} catch (Exception e) {
e.printStackTrace();
res = e.toString();
}
return res;
}
@Override
protected void onPostExecute(String result) {
Log.d("Task:", "onPostExecute");
this.res = result;
delegate.processFinish(result);
}
}
This class uses the MySQL JDBC driver. You have to add the MySQL database connector as a module to your project. The instructions to do that are in StackOverflow – click that link and follow the instructions, which were pretty easy, at least with Android Studio 3.5.
For test purposes, I ran this demo in the Android Emulator. I typed in ‘Italian’ for the search term, and got back a bunch of results. It took a short while, because I never added any indexes to my database table, but that’s something to fine-tune later.
As a finishing touch, I built the Android APK, and loaded it onto my phone. Here’s a screenshot of the result:
Now, as mentioned earlier, you shouldn’t use a direct connection to the database in production code. A hacker might crack open your app, find the user name and password to your database, and do bad things! Ideally, you’ll want to connect to your database using some middleware which fields requests to the database, and makes sure that things like access permissions are enforced. That’s why this little Android app is just for demonstration purposes. The good part is that it can be built quickly, so you don’t have to waste time building middleware until you’re 100% sure you’re going to need it in a publicly available app!