= Database: MySQL =
== Using RESTful API ==

== Using Java Connector (no API) ==
==== DB Connector ====
Download the appropriate Java database connector:
  * [[https://dev.mysql.com/downloads/connector/j/3.0.html|Download JDBC / ConnectorJ library]] (select 'Platform Independent' version).
  * Extract JAR file to some folder.
  * Add JAR file as module: ''app'', right-click then ''New'' > ''Module'', then select 'Import .JAR/.AAR Package', and find JAR file.
  * JAR package should be now listed under the project Gradle Scripts: ''build.gradle (mysql-connector-java-8.0.11)''.
  * If there is an error including the JAR package, try the following:
    * In ''settings.gradle (Project Settings)'', add references to the mysql-connector-java: <code bash>
include ':app'
include ':mysql-connector-java-8.0.11'
</code>
  * Sync project (File > Sync Project with Gradle Files).
  * Add ''mysql-connector-java'' module as dependency to ''app'' module:
    * Right-click on the ''app'' module folder, and select "Open Module Settings" (F4).
    * In the top left navigation pane of the "Project Structure" window, go to category "Modules" and select tab "Dependencies".
    * Click on the green plus (+), and select 'Module Dependencies'. Select the ''mysql-connector-java'' module.
    * Click OK, and close all the opened windows. A synchronization process should take place once more.
  * If there is an error including the JAR package, try the following:
    * In ''build.gradle (Module:app)'', add references to compile ''mysql-connector-java'' under dependencies: <code bash>
dependencies {
  //...
  compile project(':mysql-connector-java-8.0.11')
}
</code>
  * If succesful, under the ''app'' module folder there should be a ''mysql-connector-java'' module folder.
  * Include the classes required in your code.


==== Manifest ====
Add permission to access the Internet and Network State.  Go to ''app'' > ''manifests'' > ''AndroidManifest.xml'':
<code xml>
<manifest...>
    <uses-permission android:name="android.permission.INTERNET"/>
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
    
    <application...>
    </application>
</manifest>
</code>

==== Database Strings ====
Create a class to hold the database strings:
File ''DbStrings.java'':
<code java>
package com.acme.myapp;

public class DbStrings
{
    static final String DATABASE_URL  = "192.168.0.1:3306";
    static final String DATABASE_NAME = "mydatabase";
    static final String USERNAME      = "dbuser";
    static final String PASSWORD      = "dbsecret";
}
</code>


==== ItemAdapter ====
Create class ItemAdapter
  * Go to project tree, select ''app'' > ''java'' > //''<your app package name>''//. Right-click and select ''New'' > ''Java Class''.
  * Name: ''ItemAdapter''
  * Kind: ''Class''
  * Superclass: ''android.widget.BaseAdapter''

<code java>
package com.acme.myapp;

import android.view.ViewGroup;
import android.widget.BaseAdapter;

public class ItemAdapter extends BaseAdapter
{
    LayoutInflater mInflater;
    Map<String, Double> map;  // products & prices
    
    List<String> products;
    List<Double> prices;
    
    //
    // Constructor: Create custom constructor so properties get assigned right away
    //
    public ItemAdapter(Context cx, Map m)
    {
        map = m;
        products = new ArrayList<String>(map.keySet());
        prices   = new ArrayList<Double>(map.values());
        
        mInflater = (LayoutInflater) cx.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
    }

    @Override
    public int getCount()
    {
        return map.size();
    }
    
    @Override
    public Object getItem(int idx)
    {
        return products.get(idx);
    }
    
    @Override
    public long getItemId(int idx)
    {
        return idx;
    }
    
    @Override
    public View getView(int idx, View view, ViewGroup viewGroup)
    {
        // Use layout inflater.  Use listview_detail.xml layout
        
        View vw = mInflater.inflate(R.layout.listview_detail, null);
        TextView lblProduct = (TextView) vw.findViewById(R.id.lblProduct);
        TextView lblPrice   = (TextView) vw.findViewById(R.id.lblPrice);
        
        String strProduct = arrProducts.get(idx);
        String strPrice   = "$" + arrPrices.get(idx).toString();
        
        lblProduct.setText(strProduct);
        lblPrice.setText(strPrice);
        
        return vw;
    }
}
</code>


=== MainActivity ===
<code java>
package com.acme.myapp;
 
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
 
public class MainActivity extends AppCompatActivity 
{
    // Properties
    ItemAdapter itemAdapter;
    Context thisContext;
    ListView lstProducts;
    TextView lblProgress;
    Map<String,Double> mapProducts = new LinkedHashMap<String,Double>();
 
    @Override
    protected void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        Resources res = getResources();
        lstItems      = (ListView) findViewById(R.id.lstItems);
        lblProgress   = (TextView) findViewById(R.id.lblProgress);
        thisContext   = this;
        
        lblProgress.setText("");
        
        Button bntConnect = (Button) findViewById(R.id.btnConnect);
        btnConnect.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View vw) {
                GetData retrieveData = new GetData();
                retrieveData.execute("");
            }
        });
    } 
    
    private class GetData extends AsyncTask<String,String,String> 
    {
      // Properties
      String msg = "";   // progress textview
      
      // JDBC driver name and database URL
      static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
      
      // Example: 192.168.0.2:3306
      static final String DB_URL = "jdbc:mysql://" +
          DbStrings.DATABASE_URL + "/" +
          DbStrings.DATABASE_NAME;
          
       @Override
       protected void onPreExecute() 
       {
           lblProgress.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 products";
               ResultSet rs = stmt.executeQuery(sql);
               
               while(rs.next()) {
                   String name  = rs.getString("name");
                   double price = rs.getDouble("price");
                   
                   mapProducts.put(name, price);
               }
               
               msg = "Process complete.";
               
               rs.close();
               stmt.close();
               conn.close();
               
           } catch (SQLException connError) {
               msg = "Exception was thrown for JDBC.";
               connError.printStackTrace();
           } catch (ClassNotFoundException ex) {
               msg = "A 'Class not Found' exception was thrown.";
               ex.printStackTrace();
           } finally {
               try {
                   if (stmt != null) { stmt.close(); }
               } catch (SQLException ex) {
                   ex.printStackTrace();
               }
               try {
                   if (conn != null) { conn.close(); }
               } catch (SQLException ex) {
                   ex.printStackTrace();
               }
           }
           return null;
       }
       
       @Override
       protected void onPostExecute(String msg) {

           lblProgress.setText(this.msg);
           
           if (mapProducts.size() > 0) {
               itemAdapter = new ItemAdapter(thisContext, mapProducts);
               lstProducts.setAdapter(itemAdapter);
           }
       }
    }
}
</code>

=== Example Quick Connection ===
''activity_main.xml'':
<code xml>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity" >

    <TextView
        android:id="@+id/textView0"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_centerVertical="true"
        android:text="@string/hello_world" />

</RelativeLayout>
</code>

''MainActivity.java'':
<code java>
package com.example.andmysql;

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


import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.app.Activity;
import android.widget.TextView;

public class MainActivity extends Activity implements Runnable
{
    private String hiduke = "";
    private int price     = 0;
    private String errmsg = "";
    
    public void run() 
    {
        System.out.println("Select Records Example by using the Prepared Statement!");
        int count = 0;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection ("jdbc:mysql://10.0.2.2:3306/stock","root","secret");
            
            try {
                String sql;
                //sql = "SELECT title,year_made FROM movies WHERE year_made >= ? AND year_made <= ?";
                sql = "SELECT hiduke, jikan, code, price FROM table_stock";
                PreparedStatement qry = conn.prepareStatement(sql);
                //qry.setInt(1,1980);
                //qry.setInt(2,2004);
                ResultSet rs = qry.executeQuery();
                while (rs.next()) {
                    hiduke = rs.getString(1);
                    price  = rs.getInt(4);
                    count++;
                    System.out.println(hiduke + "\t" + "- " + price);
                }
                System.out.println("Number of records: " + count);
                qry.close();
                conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL statement is not executed!");
                errmsg = errmsg + ex.getMessage();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            errmsg = errmsg + ex.getMessage();
        }

        handler.sendEmptyMessage(0);
    }
    
    private Handler handler = new Handler() 
    {
        public void handleMessage(Message msg) {
            TextView textView = (TextView) findViewById(R.id.textView0);  
            textView.setText("hiduke = " + hiduke + " price = " + price  + " " + errmsg);  
        }
    };

    
    @Override
    protected void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        Thread thread = new Thread(this);
        thread.start();   
    }

}
</code>

See: [[https://gist.github.com/cofearabi/5039135]]


== References ==
See also:
  * [[swdev:java:Connecting to MySQL Database]]
  * [[https://www.youtube.com/watch?v=bu5Y3uZ6LLM|Butterfield: Studio For Beginners Part 4: Connecting to MySQL]]
  * [[https://www.simplifiedcoding.net/android-mysql-tutorial-to-perform-basic-crud-operation|Android MySQL Tutorial to Perform Basic CRUD Using API]]
  * [[http://www.helloandroid.com/tutorials/connecting-mysql-database|Connecting MySQL Database with API]]
  * [[http://androidbash.com/connecting-android-app-to-a-database-using-php-and-mysql|Connecting Android App to MySQL Database]]