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

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

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

}

static final String DATABASE_URL = "127.0.0.1:3306";