JDBC connection issues in Android

Question

I followed a tutorial on youtube, to make a list app with database. Now I get the error:

An exception was thrown for JDBC.

This is my code:

package com.example.jopri.databaseconnectie;

import android.content.AsyncQueryHandler;
import android.content.Context;
import android.content.res.Resources;
import android.os.AsyncTask;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListView;
import android.widget.TextView;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class MainActivity extends AppCompatActivity {

ItemAdapter itemAdapter;
Context thisContext;
ListView myListView;
TextView progressTextView;
Map<String, Double> fruitsMap = new LinkedHashMap<String, Double>();

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    Resources res = getResources();
    myListView = (ListView) findViewById(R.id.myListView);
    progressTextView = (TextView) findViewById(R.id.progressTextView);
    thisContext = this;

    progressTextView.setText("");
    Button btn = (Button) findViewById(R.id.getDataButton);
    btn.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v){
            GetData retrieveData = new GetData();
            retrieveData.execute("");
        }
    });

}

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

    String msg = "";

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    static final String DB_URL = "jdbc:mysql://" +
            DbStrings.DATABASE_URL + "/" +
            DbStrings.DATABASE_NAME;

    protected void onPreExecute() {
        progressTextView.setText("Connecting to database...");
    }

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

        Connection conn = null;
        Statement stmt = null;

        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, DbStrings.USERNAME, DbStrings.PASSWORD);

            stmt = conn.createStatement();
            String sql = "SELECT * FROM fruit";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                String soort = rs.getString("soort");
                double prijs = rs.getDouble("prijs");

                fruitsMap.put(soort, prijs);
            }

            msg = "Process complete";

            rs.close();
            stmt.close();
            conn.close();

        } catch (SQLException connError) {
            msg = "An exception was thrown for JDBC";
            connError.printStackTrace();
        } catch (ClassNotFoundException e) {
            msg = "A class not found exception was thrown.";
            e.printStackTrace();
        } finally {

            try {
                if (stmt != null) {
                    stmt.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return null;
    }

    @Override
    protected void onPostExecute(String msg) {
        progressTextView.setText(this.msg);

        if(fruitsMap.size() > 0) {
            itemAdapter = new ItemAdapter(thisContext, fruitsMap);
            myListView.setAdapter(itemAdapter);
        }
    }
}

} // END

Connector:

package com.example.jopri.databaseconnectie;

import java.net.URL;

public class DbStrings {

static final String DATABASE_URL = "127.0.0.1:3306";
static final String DATABASE_NAME = "proef";
static final String USERNAME = "root";
static final String PASSWORD = "password";
}

Item adapter:

package com.example.jopri.databaseconnectie;

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

import java.util.ArrayList;
import java.util.List;
 import java.util.Map;

 public class ItemAdapter extends BaseAdapter {


LayoutInflater mInflator;
Map<String, Double> map;
List<String> soort;
List<Double> prijs;

public ItemAdapter(Context c, Map m) {
    mInflator = (LayoutInflater)     c.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
    map = m;
    soort = new ArrayList<String>(map.keySet());
    prijs = new ArrayList<Double>(map.values());

};

@Override
public int getCount() {
    return map.size();
}

@Override
public Object getItem(int position) {
    return soort.get(position);
}

@Override
public long getItemId(int position) {
    return position;
}

@Override
public View getView(int position, View convertView, ViewGroup parent) {

    View v = mInflator.inflate(R.layout.item_layout, null);
    TextView nameTextView = (TextView) v.findViewById(R.id.nameTextView);
    TextView priceTextView = (TextView) v.findViewById(R.id.priceTextView);

    nameTextView.setText(soort.get(position));
    priceTextView.setText("€" + prijs.get(position).toString());

    return v;
}

}

It's not really recommended to use JDBC in a mobile app https://stackoverflow.com/a/15853566/2308683 — OneCricketeer, Nov 05 '18 at 14:07
*DON'T DO THAT EVEN IF IT IS APPLICABLE* You should have a backend server that lets you to connect to your database otherwise your database will be at risk — Basil Battikhi, Nov 05 '18 at 14:09

OneCricketeer · Answer 1 · 2018-11-05T14:22:04.153

static final String DATABASE_URL = "127.0.0.1:3306";

This line implies that you're running a MySQL server on the Android device itself, which seems highly unlikely to me. If you watch over the video again, he never used localhost/127.0.0.1.

This URL should rather be a remote IP/hostname that's resolvable within your network(s) (ideally, should work over WiFi and mobile)

Also, by doing this, you're exposing your database password to anyone that can access your app/code, because it's just a plain string in memory. Therefore, in a real world environment, you'd actually use HTTP based APIs, which can more securely hide away and store database credentials in a single location, rather than have that information copied across a bunch of mobile devices (if you publish your app for others to use).

If all you want is a database with a listview, start with SQLite, which is embedded and available as a library as part of the Android SDK

With the caveat that using JDBC connection from a mobile application is still an *extremely bad idea* unless used in a highly controlled environment (e.g. as a fixed kiosk), as pointed out in previous comments. — Michael Dodd, Nov 05 '18 at 14:13
Even then, implementing joins and such business logic in a mobile app seems like a headache — OneCricketeer, Nov 05 '18 at 14:26

JDBC connection issues in Android

1 Answers1