Random Musings of a Coffee Technologist
Tutorial: Accessing a PostgreSQL database from an Android Device

Abstract: This is a brief tutorial on how to get starting using a PostgreSQL database from an Android application.

Prerequisites: It is assumed that the Android SDK has been installed and that Eclipse with the ADT plugin is being used according to the instructions here. Remember that the SDK installer has a bug where it sometimes claims to be unable to find an installed JDK. If you run into that, click the back button and then click the next button and things should work again. It is also assumed that you’ve gone through the hello world tutorial and have successfully deployed an application to the Android emulator. Finally, it is assumed that you have already set up a PostgreSQL database that you would like to connect to and have some basic experience with SQL. The Step 1 instructions for setting up PosgreSQL for use with Typica can walk you through that on Windows if you are unfamiliar with this. Getting set up on Linux or Mac OS X is similarly simple. You can also find some introductory material on using databases in the PostgreSQL documentation.

Project Setup: Open Eclipse and from the File menu, select New→Android Project. From here, you’ll need to fill in several fields. I’ll be using the following:

Project name: HelloPostgreSQL

Application name: Hello, PostgreSQL

Package name: com.example.hellopostgresql

✓ Create Activity: HelloPostgreSQLActivity

Min SDK Version: 13

I also have Android 3.2 checked in the Build Target panel. Once all of that is entered, click Finish. Your new project should now be listed in the package explorer. Note that this probably works with much older Android and SDK versions, but I have not done any testing to determine just how far back this works.

Obtaining Permission: Chances are you won’t be running PostgreSQL on the android device itself. Instead, you’ll be connecting to the database running on another computer on the network. As a result, you’ll need to ask for permission to access the Internet. On Android, this is done through the manifest file. Expand your project in the package explorer and double click AndroidManifest.xml. At the bottom of the view that opens, you’ll see several tabs. One of these is labeled AndroidManifest.xml. Click that to edit the file directly. You’ll need to add a <uses-permission> tag. I like to place this in between the <uses-sdk> tag and the <application> tag. Specifically, you want to add the following:

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

You can now save and close that file.

Packaging the JDBC Driver: PostgreSQL support is not built into Android, but since programs are written in Java, we can just bring in a .jar file that provides this support. I’m using the JDBC 4 PostgreSQL Driver. Download that and save the file someplace that is convenient for you.

In Eclipse, right click your project in the package explorer and select Properties from the menu. Next, select Java Build Path from the panel on the left. Next click the Add External JARs… button to the right, select the JDBC driver you downloaded, click Open, and then click OK. In the package explorer, you should now see a new section called Referenced Libraries which contains the file you just selected.

The Code: Now that the preparations are complete, we can move on to writing some code. In the package explorer, expand src/com.example.hellopostgresql and double click HelloPostgreSQLActivity.java to open the source file that was stubbed out when the project was created. We’ll be replacing most of that, so just go ahead and delete the contents and instead type in the following:

package com.example.helllopostgresql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import android.app.Activity;
import android.os.AsyncTask;
import android.os.Bundle;
import android.widget.TextView;

public class HelloPostgreSQLActivity extends Activity {
    TextView resultArea;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        resultArea = new TextView(this);
        resultArea.setText("Please wait.");
        setContentView(resultArea);
        new FetchSQL().execute();
    }
    private class FetchSQL extends AsyncTask<Void,Void,String> {
        @Override
        protected String doInBackground(Void... params) {
            String retval = "";
            try {
                Class.forName("org.postgresql.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                retval = e.toString();
            }
            String url = "jdbc:postgresql://10.0.2.2/dbname?user=username&password=pass";
            Connection conn;
            try {
                DriverManager.setLoginTimeout(5);
                conn = DriverManager.getConnection(url);
                Statement st = conn.createStatement();
                String sql;
                sql = "SELECT 1";
                ResultSet rs = st.executeQuery(sql);
                while(rs.next()) {
                    retval = rs.getString(1);
                }
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                retval = e.toString();
            }
            return retval;
        }
        @Override
        protected void onPostExecute(String value) {
            resultArea.setText(value);
        }
    }
}

Don’t hit run yet because you’ll want to customize that a little bit. First, the connection string defined above on the line:

String url = "jdbc:postgresql://10.0.2.2/dbname?user=username&password=pass";

is probably not what you want. There are three parts that you might need to change. First, the 10.0.2.2 IP address is what the Android emulator sees as the computer you’re running the emulator on. If that’s where you have the database running, that’s fine, but if you have the database running on another computer, you’ll want to connect to that instead. Next, dbname should be replaced with the name of the database you want to connect to. If you haven’t created one, there’s probably a database named postgres that you can use. Similarly, if your database user name isn’t username, you’ll need to replace that with a real user name. If you haven’t created one, chances are one named postgres already exists. Finally, unless your password is pass, you’ll need to put in a real password there.

Now, from the Run menu, select Run, then select Android Application, then click OK. The Android emulator will start and after a while (you may need to unlock the screen) your program will start. You should see your TextView, first with the text “Please wait” and then the number 1. If you have, then congratulations, you’ve connected to a PostgreSQL database, run a query, and displayed the result. If instead you have an error message that the connection attempt failed, double check that your emulator thinks it has Internet access, that you’re asking for permission properly in the manifest, and that the access credentials you’re using work from psql. If something else isn’t working, double check for typos.

What is Going on Here? First, we have a public class that represents our application. The HelloPostgreSQLActivity class is loaded when we start the program and the onCreate() callback method is run. You can read all the details of that in the reference documentation. Here we’re creating the TextView that has been defined previously as a member of the class where we will store our results, setting a value just so something is displayed while waiting for database access, and displaying that view. Next, we create a new instance of FetchSQL, a private class that we’ll define soon, and call the execute() method on the newly created object.

FetchSQL builds on AsyncTask. When the execute() method (which we don’t have to write) is called, a new thread of execution is created and that thread runs the code in the doInBackground() method. This keeps database operations (and the network operations that go along with that) out of the main thread that runs the user interface. While that might not matter for something trivial like this (aside from that if you try to run it in the main thread your program will get an exception and die), it’s generally a good idea to use separate threads for things that might take a while. This lets the user interface stay responsive while waiting for other things to finish.

In this method, we first make sure that our JDBC driver is loaded. This is done in the line:

Class.forName("org.postgresql.Driver");

Next, we try to establish a connection to the database with:

conn = DriverManager.getConnection(url);

After that, we execute a query and process the result, placing it in the return value for this method.

That return value is passed as the argument to the onPostExecute() method which runs back in our main thread where we can interact with the user interface by replacing the displayed text in our TextView with the result of the query.

Critique: The code here is written to explain how to connect to a PostgreSQL database and run a query from an Android application. As a result, there are many things that are very wrong with this code that you wouldn’t want to do in a real application, but that fixing would involve more code that would distract from the lesson. The hard coded connection string, for example, is a very bad idea. There should be a way for the user to configure the connection details instead. The error handling presented here is not very robust (and there is at least one bug in it. Can you find it?). There’s a lack of generality in the code and probably a lot of other problems that someone more familiar with Java as a language could point out, but for what it tries to do, the program does work.

Exercises for the Reader:

0) Read the reference documentation for the classes used here. Try making changes to the code and see what happens.

1) Provide an interface to allow the user to change the host name, database name, user name, and password (bonus points for also adding port number, connection timeout, and SSL settings) and have the program use this instead of hard coded information.

2) Alter FetchSQL so that it can execute an arbitrary SQL statement and return a full result set. Alter the interface to present a full table of information.

3) Make it interactive. Think of a task you may want to do with a database and provide an interface for creating new records, updating existing records, and obtaining some interesting information from the database.

End Note, Read Before Commenting:

As mentioned elsewhere I’ve closed the Disqus thread below. For some reason people have still been able to leave comments in it which has had the result that some comments appear to have been deleted, which is unfortunate but unintended. I have tried closing the thread again, but if you see it open again in the future, please do not bother placing questions there as I have no intention of answering any more questions on this topic for the reasons stated in the linked post. The example works if you follow the instructions. If it isn’t working for you, it’s because you didn’t follow the instructions.

  1. mau075 reblogged this from appliedcoffeetechnology
  2. appliedcoffeetechnology posted this
Blog comments powered by Disqus