I designed DKOs to be stupidly simple. Don't get me wrong, I love what bytecode rewriting has brought to Java. But it brings pain with it (esp. when overused). One of the perks of this is that DKOs worked on Android almost out of the box.
This helloandroid
app is actually the initial stub of a real app I'm building. It's
a product database. There are four tables:
CREATE TABLE "scraper_product" (
"id" integer NOT NULL PRIMARY KEY,
"web_page_id" integer NOT NULL REFERENCES "scraper_webpage" ("id"),
"manufacturer_id" integer REFERENCES "scraper_manufacturer" ("id"),
"name" varchar(1000),
"discontinued" bool NOT NULL,
"created" datetime NOT NULL
);
CREATE TABLE "scraper_property" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(1000) NOT NULL,
"display_name" varchar(1000),
"parent_id" integer REFERENCES "scraper_property" ("id"),
"display" bool NOT NULL,
"created" datetime NOT NULL
);
CREATE TABLE "scraper_fact" (
"id" integer NOT NULL PRIMARY KEY,
"web_page_id" integer NOT NULL REFERENCES "scraper_webpage" ("id"),
"product_id" integer NOT NULL REFERENCES "scraper_product" ("id"),
"prop_id" integer NOT NULL REFERENCES "scraper_property" ("id"),
"value" text NOT NULL,
"created" datetime NOT NULL
);
CREATE TABLE scraper_manufacturer (
id integer PRIMARY KEY,
name varchar(1000),
created datetime
);
CREATE INDEX "scraper_fact_445e8cc4" ON "scraper_fact" ("web_page_id");
CREATE INDEX "scraper_fact_7f1b40ad" ON "scraper_fact" ("product_id");
CREATE INDEX "scraper_fact_b0389113" ON "scraper_fact" ("prop_id");
More simply: A Fact is the value between a Product and a Property. Products also have a Manufacturer. (Why isn't the manufacturer just another property? GOOD QUESTION...)
Android's internal database support is all driven by SQLite3, however they totally eschewed JDBC. DKOs support SQLite3, but JDBC is still needed. So:
Dependency: SQLDroid
A copy is already included in libs/sqldroid-1.0.0RC1.jar
, but if you want
to build it yourself:
$ git clone https://github.com/SQLDroid/SQLDroid.git
$ cd SQLDroid
$ sudo apt-get install rake
$ rake
You'll also need to build DKO. From this directory:
$ cd ../..
$ ant
Warning: Do not try to make SQLDroid (or DKO) dependent/library projects in Eclipse ADT. I've
never been able to get a deployable APK that way. Just build SQLDroid with rake
and DKO
with ant
, and copy the jars.
Import this project into Eclipse. It should build and run as-is.
Important files:
./src/org/kered/contactlensfinder/DB.java
Normally you would specify a default data source directly in your build.xml
such as
com.company.myproject.Util.getDefaultDataSource()
. But android is a bit more
complicated. Database files need to be written to /data/data/com.company.myproject
,
the path to which is gotten from the android Context, available from your Activity. So
DB.getReady(context)
needs to be called once in the onCreate()
of your
MainActivity
. This code does two things:
- Loads the SQLDroid JDBC driver.
- Checks to see if the SQLite3 database file exists, and if it doesn't, copies an initial one from
assets
. - Sets the default datasource for all DKOs in this VM.
./src/org/kered/contactlensfinder/ViewManufacturersActivity.java
This Activity
is a usage example. It displays a list of manufacturers with the ArrayAdapter
. Here's
the critical code:
// Define the query (note the case-insensitive order-by using the SQL LOWER() function).
Query<Manufacturer> query = Manufacturer.ALL.orderBy(LOWER(Manufacturer.NAME));
// The method asList() is a terminal operation (it doesn't return another query),
// so this is where the database is actually called. Note that Query objects
// are Iterable, so if you're in a loop the asList() is unnecessary (in fact
// it's counter-productive, as DKOs will steam when used as an Iterable).
final List<String> names = query.asList(Manufacturer.NAME);
The above code calls are split out to highlight the underlying types. However it would be more common to chain them all together as such:
final String[] names = Manufacturer.ALL
.orderBy(LOWER(Manufacturer.NAME))
.asList(Manufacturer.NAME);
Note: The method LOWER()
is from:
import static org.kered.dko.SQLFunction.LOWER;
./src/org/kered/contactlensfinder/ViewProductsActivity.java
This starts out much the same, but based in Intent
further filters the results. Note
the SQL join with the table scraper_fact
.
// Here we set up the base query. No SQL execution yet.
Query<Product> query = Product.ALL
.where(Product.NAME.neq(""))
.orderBy(LOWER(Product.NAME));
if (extras!=null && extras.containsKey("manufacturer_id")) {
int manufacturerId = extras.getInt("manufacturer_id");
// We want to show only products from a specific manufacturer, so add a where
// clause here. Notice this where is added after the orderBy() call above.
// That's OK. Notice also that this is the second call to where(). That's
// OK too. Multiple calls to where() are ANDed together. Finally, notice
// that we assign the query back to itself. Queries are immutable, so every
// where(), orderBy(), etc. always returns a new query.
query = query.where(Product.MANUFACTURER_ID.eq(manufacturerId));
}
if (extras!=null && extras.containsKey("property_id")) {
int propertyId = extras.getInt("property_id");
// A Fact has a foreign key to a Product, represented by:
// Fact.FK_PRODUCT_SCRAPER_PRODUCT (generally: FK_{COLUMNNAME}_{TABLENAME})
// We want to only show products that have a fact of a certain property type.
query = query.with(Fact.FK_PRODUCT_SCRAPER_PRODUCT)
.where(Fact.PROP_ID.eq(propertyId));
// Calling with() joins the two tables on their FK relationship, and the
// where() call filters.
}
// The query is executed here!
final List<String> names = query.asList(Product.NAME);
Good luck! Derek