CRUD Operations in SQLite Database using ORMLite with Dependency Injection(Android, SQLite, ORMLite 4.47, RoboGuice 2.0)

Today I’m going to post about data manipulation in Android environment. My implementation consisted with techniques like Object Relational Mapping and Dependency Injection. Detail configuration and using of these two technologies as follows.

ORMLite

ORMLite is a very Lightweight Java ORM which is compatible with Android and SQLite database. You can read more about it on ORMLite Home Page.

Before using this, you have to download these two files and add it into your libs folder.

  1. ormlite.com/releases/4.47/ormlite-core-4.47.jar
  2. ormlite.com/releases/4.47/ormlite-android-4.47.jar

RoboGuice

RoboGuice is a framework which supports Dependency Injection in Android development environment. Read more about this on code.google.com/p/roboguice/

Before using this, download these files and add them into your libs folder.

  1. http://repo1.maven.org/maven2/org/roboguice/roboguice/2.0/roboguice-2.0.jar
  2. http://repo1.maven.org/maven2/com/google/inject/guice/3.0/guice-3.0-no_aop.jar
  3. http://repo1.maven.org/maven2/com/google/code/findbugs/jsr305/1.3.9/jsr305-1.3.9.jar

Include javax.inject.jar into your libs folder too. File can be found in http://code.google.com/p/atinject/downloads/detail?name=javax.inject.zip

RoboGuice configuration

In-order to up and running RoboGuice and make use of dependency injection, you have to make use of the AbstractModule class. Here is the implementation.

AbstractModule

public class BaseModule extends AbstractModule {

	@Override
	protected void configure() {
        //binding will be placed here.....
	}

}

Add file called roboguice.xml to res/values folder.

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string-array name="roboguice_modules">
    	<item>com.androidrobo.BaseModule</item>
    </string-array>
</resources>

com.androidrobo was the package where BaseModule class was resided.

ORMLite configuration, database and table creation

Here is my Product class for ORMLite table, field mapping, etc.

@DatabaseTable(tableName = "Product")
public class Product implements Serializable{

	private static final long serialVersionUID = 1L;

	// this is the primary key column which is not automatically generated
	@DatabaseField(columnName = "Id", id = true, generatedId = false, canBeNull = false)
	private String code;

	@DatabaseField(columnName = "Description", canBeNull = false)
	private String description;

	@DatabaseField(columnName = "Price", canBeNull = false)
	private double price;

	@DatabaseField(columnName = "Qty")
	private int qty;

	public void setCode(String code) {
		this.code = code;
	}

	public String getCode() {
		return code;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public String getDescription() {
		return description;
	}

	public void setPrice(double price) {
		this.price = price;
	}

	public double getPrice() {
		return price;
	}

	public void setQty(int qty) {
		this.qty = qty;
	}

	public int getQty() {
		return qty;
	}
}

Here is the initial database creation helper class. There you can see a static class called TableUtils which was used for table creation with mapping classes.

public class DbHelper extends OrmLiteSqliteOpenHelper {

	private final static String DATABASENAME = "Main.db";
	private final static int DATABASEVERSION = 4;

	@Inject
	public DbHelper(Context context) {
		super(context, DATABASENAME, null, DATABASEVERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {
		try {
			TableUtils.createTableIfNotExists(connectionSource, Product.class);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource,
			int oldVersion, int newVersion) {
	}

}

Extended Application class can be used for initial database creation.
Note: Application class is the first starting point of the application.

Ex:

public class InitApp extends Application {

	@Override
	public void onCreate() {

		super.onCreate();
		//initial database creation..
		new DbHelper(getApplicationContext()).getWritableDatabase();

	}
}

Above section concludes, the database creation with relevant table.

Here is how the CRUD Operations were performed using ProductRepo. In-order to perform CRUD Operations we have to use a Data Access Object(Dao) for each entity(Product) which is provided by ORMLite. So here how it was implemented. DaoManager is a static class which is used for getting the Dao Object.

public class ProductDaoProvider implements Provider<Dao<Product, String>> {

	private ConnectionSource connectionSource;

	@Inject
	public ProductDaoProvider(DbHelper dbHelper) {
		connectionSource = dbHelper.getConnectionSource();
	}

	@Override
	public Dao<Product, String> get() {
		try {
			return DaoManager.createDao(connectionSource, Product.class);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}

Here is the IProductRepo interface which used to construct concrete ProductRepo class.

public interface IProductRepo {

	public List GetProducts() throws SQLException;
	public Product GetProduct(String id) throws SQLException;
	public void DeleteProduct(Product deleteProduct) throws SQLException;
	public void SaveProduct(Product saveProduct) throws SQLException;
	public void UpdateProduct(Product updateProduct) throws SQLException;

}

Implementation of ProductRepo class as follows.

public class ProductRepo implements IProductRepo {

	private Dao<Product, String> _productDao;

	@Inject
	public ProductRepo(Context context, ProductDaoProvider productDaoProvider) {
		_productDao = productDaoProvider.get();
	}

	@Override
	public List GetProducts() throws SQLException {
		return _productDao.queryForAll();
	}

	@Override
	public Product GetProduct(String id) throws SQLException {
		return _productDao.queryForId(id);
	}

	@Override
	public void DeleteProduct(Product deleteProduct) throws SQLException {
		_productDao.delete(deleteProduct);
	}

	@Override
	public void SaveProduct(Product deleteProduct) throws SQLException {
		_productDao.create(deleteProduct);
	}

	@Override
	public void UpdateProduct(Product updateProduct) throws SQLException {
		_productDao.update(updateProduct);
	}
}

Implementation of ProductRepo binding was done by modifying configure method in BaseModule class.

public class BaseModule extends AbstractModule {

	@Override
	protected void configure() {
		bind(IProductRepo.class).to(ProductRepo.class);
	}
}

Finally, I’m going to show how to perform the CRUD Operations in Activities.

1. Implementation of Product List Activity.

Layout : product_list_row.xml

<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:background="@android:color/black"
    android:padding="10dp" >

    <TableRow
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@android:color/transparent" >

        <TextView
            android:id="@+id/listProductId"
            android:layout_width="0px"
            android:layout_height="wrap_content"
            android:layout_weight=".3"
            android:gravity="left"
            android:height="20dip"
            android:textColor="@android:color/white">
        </TextView>

        <TextView
            android:id="@+id/listProductDescription"
            android:layout_width="0px"
            android:layout_height="wrap_content"
            android:layout_weight=".7"
            android:gravity="left"
            android:textColor="@android:color/white">
        </TextView>

    </TableRow>

    <TableRow
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@android:color/transparent" >

        <TextView
            android:id="@+id/listProductPrice"
            android:layout_width="0px"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="left"
            android:textColor="@android:color/white">
        </TextView>

    </TableRow>

</TableLayout>

Adapter : ProducListAdapter

public class ProducListAdapter extends BaseAdapter {

	private List _productList;
	private Context _context;

	public ProducListAdapter(Context context, List products) {
		_context = context;
		_productList = products;
	}

	static class ViewHolder {
		protected TextView textViewProductId, textViewProductDescription,
				textViewProductPrice;
	}

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

		ViewHolder holder = null;
		LayoutInflater inflater = LayoutInflater.from(_context);

		if (convertView == null) {

			convertView = inflater.inflate(R.layout.product_list_row, null);
			holder = new ViewHolder();

			holder.textViewProductId = (TextView) convertView
					.findViewById(R.id.listProductId);
			holder.textViewProductDescription = (TextView) convertView
					.findViewById(R.id.listProductDescription);
			holder.textViewProductPrice = (TextView) convertView
					.findViewById(R.id.listProductPrice);

			convertView.setTag(holder);

		} else {
			holder = (ViewHolder) convertView.getTag();
		}

		Product product = _productList.get(position);

		if (product != null) {

			try {

				holder.textViewProductId.setText(String.format(_context
						.getString(R.string.list_product_code_format,
								product.getCode())));
				holder.textViewProductDescription.setText(String
						.format(_context.getString(
								R.string.list_product_description_format,
								product.getDescription())));
				holder.textViewProductPrice.setText(String.format(_context
						.getString(R.string.list_product_price_format,
								String.valueOf(product.getPrice()))));

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

		}

		return convertView;
	}

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

	@Override
	public Object getItem(int pos) {
		return _productList.get(pos);
	}

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

Layout : product_list.xml

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

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight=".9" >

        <ListView
            android:id="@+id/listviewProduct"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:layout_centerHorizontal="true"
            android:layout_centerVertical="true"
            android:clickable="true"
            android:fastScrollEnabled="true"
            android:scrollingCache="true"
            android:textFilterEnabled="true" >
        </ListView>

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_weight=".1"
        android:layout_height="wrap_content"
        android:orientation="vertical" >

        <Button
            android:id="@+id/buttonProductAdd"
            android:layout_width="200dp"
            android:layout_height="wrap_content"
            android:layout_gravity="right"
            android:text="@string/productbutton_add_label" />
    </LinearLayout>

</LinearLayout>

Activity : ProductListActivity

@ContentView(R.layout.product_list)
public class ProductListActivity extends RoboActivity {

	@Inject
	private IProductRepo _productRepo;

	@InjectView(R.id.listviewProduct)
	private ListView _listViewProduct;

	@InjectView(R.id.buttonProductAdd)
	private Button _buttonProductAdd;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);

		try {

			List products = _productRepo.GetProducts();
			ProducListAdapter producListAdapter = new ProducListAdapter(
					getApplicationContext(), products);
			_listViewProduct.setAdapter(producListAdapter);

			_buttonProductAdd.setOnClickListener(new View.OnClickListener() {

				@Override
				public void onClick(View v) {

					Intent intent = new Intent(ProductListActivity.this,
							ProductEditActivity.class);
					intent.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
					startActivityForResult(intent, 0);
					finish();

				}
			});

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

}

Note: used RoboActivity as the base class instead of Activity class.

2. Implementation of Product Edit Activity.

Layout : product_edit.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content">

        <TextView
            android:id="@+id/labelProductId"
            android:layout_width="0px"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:text="@string/productid_label" />

        <EditText
            android:id="@+id/editTextProductId"
            android:layout_weight="8"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content">

        <TextView
            android:id="@+id/labelProductName"
            android:layout_width="0px"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:text="@string/productname_label" />

        <EditText
            android:id="@+id/editTextProductName"
            android:layout_weight="8"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <TextView
            android:id="@+id/labelProductPrice"
            android:layout_width="0px"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:text="@string/productprice_label" />

        <EditText
            android:id="@+id/editTextProductPrice"
            android:layout_weight="8"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <TextView
            android:id="@+id/labelProductQuantity"
            android:layout_width="0px"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:text="@string/productqty_label" />

        <EditText
            android:id="@+id/editTextProductQty"
            android:layout_weight="8"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <Button
            android:id="@+id/buttonProductSave"
            android:layout_width="0px"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="@string/productbutton_save_label" />

        <Button
            android:id="@+id/buttonProductDelete"
            android:layout_width="0px"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="@string/productbutton_delete_label" />

    </LinearLayout>

</LinearLayout>

Activity : ProductEditActivity

@ContentView(R.layout.product_edit)
public class ProductEditActivity extends RoboActivity {

	@Inject
	private IProductRepo _productRepo;

	@InjectView(R.id.editTextProductId)
	private EditText _editTextProductCode;

	@InjectView(R.id.editTextProductName)
	private EditText _editTextProductDescription;

	@InjectView(R.id.editTextProductPrice)
	private EditText _editTextProductPrice;

	@InjectView(R.id.editTextProductQty)
	private EditText _editTextProductQty;

	@InjectView(R.id.buttonProductSave)
	private Button _buttonProductSave;

	@InjectView(R.id.buttonProductDelete)
	private Button _buttonProductDelete;

	private Product _selectedProduct;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);

		_selectedProduct = (Product) (getIntent()
				.getSerializableExtra("Product"));
		loadSelectedProduct(_selectedProduct);

		_buttonProductSave.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {
				try {

					if (_selectedProduct == null) {

						Product product = new Product();

						product.setCode(_editTextProductCode.getText()
								.toString());

						product.setDescription(_editTextProductDescription
								.getText().toString());
						product.setPrice(Double
								.parseDouble(_editTextProductPrice.getText()
										.toString()));
						product.setQty(Integer.parseInt(_editTextProductQty
								.getText().toString()));

						_productRepo.SaveProduct(product);

					} else {

						_selectedProduct
								.setDescription(_editTextProductDescription
										.getText().toString());
						_selectedProduct.setPrice(Double
								.parseDouble(_editTextProductPrice.getText()
										.toString()));
						_selectedProduct.setQty(Integer
								.parseInt(_editTextProductQty.getText()
										.toString()));

						_productRepo.UpdateProduct(_selectedProduct);

					}

					Intent intent = new Intent(ProductEditActivity.this,
							ProductListActivity.class);
					intent.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
					startActivityForResult(intent, 0);
					finish();

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

			}
		});

		_buttonProductDelete.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {

				if (_selectedProduct != null) {

					try {

						_productRepo.DeleteProduct(_selectedProduct);
						Intent intent = new Intent(ProductEditActivity.this,
								ProductListActivity.class);
						intent.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
						startActivityForResult(intent, 0);
						finish();

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

			}
		});
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

	private void loadSelectedProduct(Product selectedProduct) {

		if (selectedProduct != null) {

			_editTextProductCode.setText(selectedProduct.getCode());
			_editTextProductCode.setEnabled(false);

			_editTextProductDescription.setText(selectedProduct
					.getDescription());
			_editTextProductPrice.setText(String.valueOf(selectedProduct
					.getPrice()));
			_editTextProductQty
					.setText(String.valueOf(selectedProduct.getQty()));
		}

	}

}

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.androidrobo"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="12" />

    <application
        android:allowBackup="true"
        android:name="InitApp"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="com.androidrobo.ProductListActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity android:name="com.androidrobo.ProductEditActivity" >
        </activity>
    </application>

</manifest>

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="app_name">AndroidRobo</string>
    <string name="action_settings">Settings</string>
    <string name="hello_world">Hello world!</string>

    <string name="productid_label">Code</string>
    <string name="productname_label">Description</string>
    <string name="productprice_label">Price</string>
    <string name="productqty_label">Quantity</string>

    <string name="productbutton_save_label">Save</string>
    <string name="productbutton_delete_label">Delete</string>
    <string name="productbutton_add_label">New</string>

    <string formatted="false" name="list_product_code_format">Code %s</string>
	<string formatted="false" name="list_product_description_format">Name %s</string>
    <string formatted="false" name="list_product_price_format">Price %s</string>

</resources>

Screen-shots

Product List

list
Add New Product

new

Edit/Delete Product

edit-delete