SQL Adapter

[judaco]

activity_main

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin">

    <ListView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:id="@+id/listProducts"/>
</LinearLayout>

[judaco]

Main Activity

package com.example.hackeru.sqliteupgrade;

import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.widget.ListView;

public class MainActivity extends AppCompatActivity {

    ListView listProducts;
    ProductsCursorAdapter adapter;

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

        Database.start(this);
        Database.openRead();

        listProducts = (ListView) findViewById(R.id.listProducts);//pointer to the list view
        adapter = new ProductsCursorAdapter(this);

         /*long millisecondsInDay = 1000L * 60L * 60L * 24L;
        long now = System.currentTimeMillis();
        int days = (int)(now / millisecondsInDay);
        Calendar calendar = GregorianCalendar.getInstance();
        calendar.setTimeInMillis(now);
        int year = calendar.get(Calendar.YEAR);*/

        /*Database.openWrite();
        Database.addProduct("screen", 3, 199.9f, 1000, false);
        Database.close();*/

        /*
        Database.openWrite();
        Database.updateProduct(2, "red baloon", 2, 13.4f, 13, false);
        Database.close();
        */

        /*
        Database.openWrite();
        Database.deleteProduct(2);
        Database.close();

        Database.openRead();
        Cursor cursor = Database.getAllProducts();
        while (cursor.moveToNext()){
            int productId = cursor.getInt(0);
            String productName = cursor.getString(1);
            int categoryId = cursor.getInt(2);
            float unitPrice = cursor.getFloat(3);
            int unitsInStock = cursor.getInt(4);
            boolean discontinued = cursor.getInt(5) == 1;
            Log.d("Juda", "product: " + productId + " " + productName + " "
                    + categoryId + " " + unitPrice + " " + unitsInStock +
                    " " + discontinued);
        }
        Database.close();
        Database.finish();

    }*/

        listProducts.setAdapter(adapter);

        }
    @Override
    protected void onDestroy () {
        super.onDestroy();
        Database.close();
        Database.finish();
    }
}

[judaco]

Database

package com.example.hackeru.sqliteupgrade;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by hackeru on 29/01/2017.
 */

public class Database {
    static final int DATABASE_VERSION = 1;
    enum Type{
        INTEGER, REAL, TEXT, BLOB
    }
    static class Column{
        String name;
        Type type;
        boolean primaryKey, autoIncrement, notNull;
        public Column(String name, Type type) {
            this(name, type, false, false, false);
        }
        public Column(String name, Type type, boolean primaryKey,
                      boolean autoIncrement, boolean notNull) {
            this.name = name;
            this.type = type;
            this.primaryKey = primaryKey;
            this.autoIncrement = autoIncrement;
            this.notNull = notNull;
        }
        String getCreate(){
            String s = name + " ";
            switch (type){
                case INTEGER:
                    s += "integer";
                    break;
                case REAL:
                    s += "real";
                    break;
                case TEXT:
                    s += "text";
                    break;
                case BLOB:
                    s += "blob";
                    break;
            }
            if (primaryKey)
                s += " primary key";
            if(autoIncrement)
                s += " autoincrement";
            if(notNull)
                s += " not null";
            return s;
        }
    }
    static abstract class Table{
        Column[] columns;
        String name;
        public Table(Column[] columns, String name) {
            this.columns = columns;
            this.name = name;
        }
        String getCreate(){
            String s = "CREATE TABLE " + name + "(";
            for (int i = 0; i < columns.length; i++) {
                s += columns[i].getCreate();
                if(i<columns.length-1)
                    s += ",";
            }
            s += ")";
            return s;
        }
        String getDrop(){
            return "DROP TABLE IF EXISTS " + name;
        }
        String[] getAllColumns(){
            String[] columnNames = new String[columns.length];
            for (int i = 0; i < columns.length; i++) {
                columnNames[i] = columns[i].name;
            }
            return columnNames;
        }
    }
    static class TableProducts extends Table{
        TableProducts() {
            super(new Column[]{
                    new Column("ProductID",Type.INTEGER, true, true, false),
                    new Column("ProductName", Type.TEXT, false, false, true),
                    new Column("CategoryID", Type.INTEGER, false, false, true),
                    new Column("UnitPrice", Type.REAL, false, false, true),
                    new Column("UnitsInStock", Type.INTEGER, false, false, true),
                    new Column("Discontinued", Type.INTEGER, false, false, true)
            }, "Products");
        }
    }
    static class TableOrders extends Table{
        TableOrders() {
            super(new Column[]{
                    new Column("OrderID", Type.INTEGER, true, true, false),
                    new Column("OrderDate", Type.INTEGER, false, false, true),
                    new Column("CustomerID", Type.INTEGER, false, false, true)
            },"Orders");
        }
    }
    static class TableOrderDetails extends Table{
        TableOrderDetails() {
            super(new Column[]{
                    new Column("OrderID", Type.INTEGER, false, false, true),
                    new Column("ProductID", Type.INTEGER, false, false, true),
                    new Column("UnitPrice", Type.REAL, false, false, true),
                    new Column("Quantity", Type.REAL, false, false, true),
                    new Column("Discount", Type.REAL, false, false, true)
            },"OrderDetails");
        }
    }
    private static SQLiteDatabase db;
    private static DatabaseHelper helper;
    private static boolean isWritable = false;
    private static TableProducts tableProducts;
    private static TableOrders tableOrders;
    private static TableOrderDetails tableOrderDetails;
    static {
        tableProducts = new TableProducts();
        tableOrders = new TableOrders();
        tableOrderDetails = new TableOrderDetails();
    }
    public static void start(Context context){
        if(helper == null)
            helper = new DatabaseHelper(context);
    }
    public static void finish(){
        if(db != null){
            db.close();
            db = null;
        }
        helper.close();
        helper = null;
    }
    public static void openRead(){
        if(helper == null || db != null)
            throw new IllegalStateException();
        db = helper.getReadableDatabase();
        isWritable = false;
    }
    public static void openWrite(){
        if(helper == null || db != null)
            throw new IllegalStateException();
        db = helper.getWritableDatabase();
        isWritable = true;
    }
    public static void close(){
        if(db == null)
            throw new IllegalStateException();
        db.close();
        db = null;
    }
    public static long addProduct(String productName, int categoryId,
                                  float unitPrice, int unitsInStock,
                                  boolean discontinued){
        if(helper == null || db == null || isWritable == false)
            throw new IllegalStateException();
        ContentValues contentValues = new ContentValues();
        contentValues.put(tableProducts.columns[1].name, productName);
        contentValues.put(tableProducts.columns[2].name, categoryId);
        contentValues.put(tableProducts.columns[3].name, unitPrice);
        contentValues.put(tableProducts.columns[4].name, unitsInStock);
        contentValues.put(tableProducts.columns[5].name, discontinued ? 1 : 0);
        return db.insert(tableProducts.name, null, contentValues);
    }
    public static boolean deleteProduct(long productId){
        if(helper == null || db == null || isWritable == false)
            throw new IllegalStateException();
        return db.delete(tableProducts.name,
                tableProducts.columns[0].name + "=" + productId, null) > 0;
    }
    public static boolean updateProduct(long productId, String productName, int categoryId,
                                        float unitPrice, int unitsInStock,
                                        boolean discontinued){
        if(helper == null || db == null || isWritable == false)
            throw new IllegalStateException();
        ContentValues contentValues = new ContentValues();
        contentValues.put(tableProducts.columns[1].name, productName);
        contentValues.put(tableProducts.columns[2].name, categoryId);
        contentValues.put(tableProducts.columns[3].name, unitPrice);
        contentValues.put(tableProducts.columns[4].name, unitsInStock);
        contentValues.put(tableProducts.columns[5].name, discontinued ? 1 : 0);
        return db.update(tableProducts.name, contentValues,
                tableProducts.columns[0].name + "=" + productId, null) > 0;
    }
    public static Cursor getAllProducts(){
        if(helper == null || db == null)
            throw new IllegalStateException();
        return db.query(tableProducts.name, tableProducts.getAllColumns(), null, null, null, null, null);
    }

    public static int getProductsCount(){
        if(helper == null || db == null)
            throw new IllegalStateException();
        Cursor cursor = db.rawQuery("SELECT COUNT (*) FROM Products", null);//counting how much rows are in the products
        int count = 0;
        while (cursor.moveToNext()){
            count = cursor.getInt(0);
        }
        cursor.close();;
        return count;
    }
    private static class DatabaseHelper extends SQLiteOpenHelper{
        public DatabaseHelper(Context context){
            super(context, "sales.db", null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(tableProducts.getCreate());
            db.execSQL(tableOrders.getCreate());
            db.execSQL(tableOrderDetails.getCreate());
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL(tableProducts.getDrop());
            db.execSQL(tableOrders.getDrop());
            db.execSQL(tableOrderDetails.getDrop());
            onCreate(db);
        }
    }
}

[judaco]

Product

package com.example.hackeru.sqliteupgrade;

/**
 * Created by hackeru on 01/02/2017.
 */

public class Product {
    long productID;
    String name;
    int categoryID;
    float unitPrice;
    int unitInStock;
    boolean discontinued;

    public Product(long productID, String name, int categoryID, float unitPrice, int unitInStock, boolean discontinued) {
        this.productID = productID;
        this.name = name;
        this.categoryID = categoryID;
        this.unitPrice = unitPrice;
        this.unitInStock = unitInStock;
        this.discontinued = discontinued;
    }
}

[judaco]

Products Cursor Adapter

package com.example.hackeru.sqliteupgrade;

import android.app.Activity;
import android.database.Cursor;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.BaseAdapter;
import android.widget.TextView;

/**
 * Created by hackeru on 01/02/2017.
 */

public class ProductsCursorAdapter extends BaseAdapter {

    Activity activity;
    Cursor cursor;//help to navigate between the table (query) in the database

    public ProductsCursorAdapter(Activity activity) {
        this.activity = activity;
        //we assume database is started, and opened (openRead)
        cursor = Database.getAllProducts();
    }

    @Override
    public int getCount() {
        return Database.getProductsCount();
    }

    @Override
    public Object getItem(int position) {
        if (cursor.moveToPosition(position)){
            Product product = new Product(
                    cursor.getLong(0),
                    cursor.getString(1),
                    cursor.getInt(2),
                    cursor.getFloat(3),
                    cursor.getInt(4),
                    cursor.getInt(5) == 1
            );
        }
        return null;
    }

    @Override
    public long getItemId(int position) {
        if (cursor.moveToPosition(position)){
            return cursor.getLong(0);
        }
        return 0;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        LayoutInflater inflater = activity.getLayoutInflater();
        View view = inflater.inflate(R.layout.item_product, parent, false);
        TextView lblProductName = (TextView)view.findViewById(R.id.lblProductName);
        if (cursor.moveToPosition(position)){
            lblProductName.setText(cursor.getString(1));
        }
        return view;
    }
}

[judaco]

item_product

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal" android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:padding="5dp">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/lblProductName"
        android:textSize="14sp"/>

</LinearLayout>