implement the android content provider's query method,with multiple tables in use -
i have 3 tables , use content provider manage them.below code content provider:
private static final urimatcher surimatcher = new urimatcher( urimatcher.no_match); static { surimatcher.adduri(authority, meter_path, all_meters); surimatcher.adduri(authority, meter_path + "/#", single_meter); surimatcher.adduri(authority, customers_path, all_customers); surimatcher.adduri(authority, customers_path + "/#", single_customer); surimatcher.adduri(authority, bill_path, all_bills); surimatcher.adduri(authority, bill_path + "/#", single_bill); } @override public cursor query(uri uri, string[] projection, string selection, string[] selectionargs, string sortorder) { sqlitedatabase db = database.getwritabledatabase(); // using sqlitequerybuilder instead of query() method sqlitequerybuilder querybuilder = new sqlitequerybuilder(); querybuilder .settables(metertabledetails.table_meters + " meters " + " inner join " + customertabledetails.table_customers + " customers " + " on " + (metertabledetails.meter_id = customertabledetails.key_meter_id) + " inner join " + waterbilltabledetails.table_water_bill + " waterbills " + " on " + (customertabledetails.key_meter_id = waterbilltabledetails.bill_meter_id)); int uritype = surimatcher.match(uri); switch (uritype) { case all_meters: break; case single_meter: // adding id original query string id = uri.getpathsegments().get(1); querybuilder.appendwhere(metertabledetails.meter_id + "=" + id); break; case all_customers: break; case single_customer: // adding id original query string id1 = uri.getpathsegments().get(1); querybuilder.appendwhere(customertabledetails.key_customer_id + "=" + id1); break; case all_bills: break; case single_bill: // adding id original query string id2 = uri.getpathsegments().get(1); querybuilder.appendwhere(waterbilltabledetails.bill_id + "=" + id2); break; default: throw new illegalargumentexception("unknown uri: " + uri); } cursor cursor = querybuilder.query(db, projection, selection, selectionargs, null, null, sortorder); // make sure potential listeners getting notified cursor.setnotificationuri(getcontext().getcontentresolver(), uri); return cursor; } i have 3 tables,and have created joins in querybuilder.settables method.i'm trying display meter items in list meters table.i have simplecursoradapter loadercallbacks implementation. following error in logcat , think it's because of joins , query:
03-20 15:11:59.692: e/sqlitecursor(2001): requesting column name table name -- meters._id 03-20 15:11:59.692: e/sqlitecursor(2001): java.lang.exception 03-20 15:11:59.692: e/sqlitecursor(2001): @ android.database.sqlite.sqlitecursor.getcolumnindex(sqlitecursor.java:180) 03-20 15:11:59.692: e/sqlitecursor(2001): @ android.database.abstractcursor.getcolumnindexorthrow(abstractcursor.java:301) 03-20 15:11:59.692: e/sqlitecursor(2001): @ android.database.cursorwrapper.getcolumnindexorthrow(cursorwrapper.java:78) 03-20 15:11:59.692: e/sqlitecursor(2001): @ android.support.v4.widget.simplecursoradapter.findcolumns(simplecursoradapter.java:317) 03-20 15:11:59.692: e/sqlitecursor(2001): @ android.support.v4.widget.simplecursoradapter.swapcursor(simplecursoradapter.java:328) 03-20 15:11:59.692: e/sqlitecursor(2001): @ com.isys.waterbillingsystem.metersactivity.onloadfinished(metersactivity.java:180) 03-20 15:11:59.692: e/sqlitecursor(2001): @ com.isys.waterbillingsystem.metersactivity.onloadfinished(metersactivity.java:1) edit
private static final string create_customer_view = "" + "create view " + table_customer_view + " select "+metertabledetails.table_meters+"."+metertabledetails.meter_id+" "+ metertabledetails.table_meters+"."+metertabledetails.meter_id +","+ " "+customertabledetails.key_first_name+","+ " "+customertabledetails.key_last_name+","+ " "+customertabledetails.key_meter_id+","+ " "+customertabledetails.key_meter_number+","+ " "+customertabledetails.key_plot_number+","+ " "+customertabledetails.table_customers+"."+ customertabledetails.key_customer_id+ " "+customertabledetails.table_customers+" customers "+" inner join "+metertabledetails.table_meters+" meters"+ " on "+customertabledetails.key_meter_id+" = "+metertabledetails.table_meters+"."+metertabledetails.meter_id; public static tabledescriptor getdescriptor() { tabledescriptor descriptor = new tabledescriptor(); descriptor.settablename(table_customer_view); descriptor.setcolumnid(customer_view_id); string[] available = { viewcustomers.customer_view_id, viewcustomers.customer_view_lastname, viewcustomers.customer_view_lastname, viewcustomers.customer_view_key_meter_id, viewcustomers.customer_view_meter, viewcustomers.customer_view_plot}; descriptor.setavailablecolumns(available); return descriptor; } edit 2
private static final string create_meter_reading_view = "" + "create view " + table_meter_reading_view + " select " + waterbilltabledetails.table_water_bill+ ".*" + ", " +customertabledetails.table_customers+"."+customertabledetails.key_meter_number+"," +" "+customertabledetails.table_customers+"."+customertabledetails.key_plot_number+"," +" "+customertabledetails.table_customers+"."+customertabledetails.key_account_number+"," +" "+customertabledetails.table_customers+"."+customertabledetails.key_meter_id+"" +" "+waterbilltabledetails.table_water_bill+" waterbills "+" join "+customertabledetails.table_customers+" customers" +" on "+waterbilltabledetails.bill_customer_id+" ="+customertabledetails.table_customers+"."+customertabledetails.key_customer_id; logcat error
03-25 10:45:03.476: e/androidruntime(1144): fatal exception: main 03-25 10:45:03.476: e/androidruntime(1144): java.lang.runtimeexception: unable start activity componentinfo{com.isys.waterbillingsystem/com.isys.waterbillingsystem.customerdetailsaccountsactivity}: java.lang.nullpointerexception 03-25 10:45:03.476: e/androidruntime(1144): caused by: java.lang.nullpointerexception 03-25 10:45:03.476: e/androidruntime(1144): @ com.isys.waterbillingsystem.customerdetailsaccountsactivity.oncreate(customerdetailsaccountsactivity.java:48 )
here quick example views:
public class hektordatabasehelper extends sqliteopenhelper { private static final string database_name = "hektor.db"; private static final int database_version = 91; public hektordatabasehelper(context context) { super(context, database_name, null, database_version); } // method called during creation of database @override public void oncreate(sqlitedatabase database) { appointmenttypestable.oncreate(database); } // method called during upgrade of database, // e.g. if increase database version @override public void onupgrade(sqlitedatabase database, int oldversion, int newversion) { appointmenttypestable.onupgrade(database, oldversion, newversion); } } this standard sqliteopenhelper.
public class appointmentwithtypeandcontactsview { public static final string table_name = "appointments_with_type_and_contacts"; public static final string column_id = appointmentstable.column_id; public static final string column_external_id = appointmentstable.column_external_id; public static final string column_start_date = appointmentstable.column_start_date; private static final string database_create = "" + "create view " + table_name + " select " + appointmentstable.table_name + ".*" + ", " + appointmenttypestable.table_name + "." + appointmenttypestable.column_name + ", " + buyerstable.table_name + "." + buyerstable.column_title + " || ' ' || " + buyerstable.table_name + "." + buyerstable.column_last_name + " || ' ' || " + buyerstable.table_name + "." + buyerstable.column_first_name + " " + buyerstable.column_last_name + ", " + sellersdetailstable.table_name + "." + sellersdetailstable.column_title + " || ' ' || " + sellersdetailstable.table_name + "." + sellersdetailstable.column_last_name + " || ' ' || " + sellersdetailstable.table_name + "." + sellersdetailstable.column_first_name + " " + sellersdetailstable.column_last_name + " " + appointmentstable.table_name + " left outer join " + appointmenttypestable.table_name + " on " + appointmentstable.table_name + "." + appointmentstable.column_type + " = " + appointmenttypestable.table_name + "." + appointmenttypestable.column_external_id + " left outer join " + buyerstable.table_name + " on " + appointmentstable.table_name + "." + appointmentstable.column_buyer + " = " + buyerstable.table_name + "." + buyerstable.column_external_id + " left outer join " + sellersdetailstable.table_name + " on " + appointmentstable.table_name + "." + appointmentstable.column_seller + " = " + sellersdetailstable.table_name + "." + sellersdetailstable.column_external_id; public static void oncreate(sqlitedatabase database) { database.execsql(database_create); } public static void onupgrade(sqlitedatabase database, int oldversion, int newversion) { log.w(appointmentwithtypeandcontactsview.class.getname(), "upgrading database version " + oldversion + " " + newversion + ", destroy old data"); database.execsql("drop view if exists " + table_name); oncreate(database); } public static tabledescriptor getdescriptor() { tabledescriptor descriptor = new tabledescriptor(); descriptor.settablename(table_name); descriptor.setcolumnid(column_id); string[] appointmentsavailablecolumns = appointmentstable.getdescriptor().getavailablecolumns(); string[] typesavailablecolumns = new string[] {appointmenttypestable.column_name}; string[] buyersavailablecolumns = new string[] {buyerstable.column_last_name}; string[] sellerssavailablecolumns = new string[] {sellersdetailstable.column_last_name}; descriptor.setavailablecolumns(arrayutils.concatall(appointmentsavailablecolumns, typesavailablecolumns, buyersavailablecolumns, sellerssavailablecolumns)); return descriptor; } } i've updated table class include utility methods.
public class tabledescriptor { private string tablename; private string columnid; private string[] availablecolumns; public string gettablename() { return tablename; } public void settablename(string tablename) { this.tablename = tablename; } public string getcolumnid() { return columnid; } public void setcolumnid(string columnid) { this.columnid = columnid; } public string[] getavailablecolumns() { return availablecolumns; } public void setavailablecolumns(string[] availablecolumns) { this.availablecolumns = availablecolumns; } } tabledescriptor container class.
public final class hektorcontentprovidercontract { public static final string authority = "fr.intuitiv.hektor.contentprovider"; public static final string appointment_with_type_base_path = "appointment_with_type"; public static final uri appointment_with_type_content_uri = uri.parse("content://" + authority + "/" + appointment_with_type_base_path); public static final string appointment_with_type_content_type = contentresolver.cursor_dir_base_type + "/vnd." + authority + "." + appointment_with_type_base_path; public static final string appointment_with_type_content_item_type = contentresolver.cursor_item_base_type + "/vnd." + authority + "." + appointment_with_type_base_path; } i create "contract" classes store public constants.
public class contentproviderhelper { private context context; public context getcontext() { return context; } public void setcontext(context context) { this.context = context; } public contentproviderhelper(context context) { this.setcontext(context); } public cursor query(sqliteopenhelper database, tabledescriptor table, boolean issingular, uri uri, string[] projection, string selection, string[] selectionargs, string sortorder) { // uisng sqlitequerybuilder instead of query() method sqlitequerybuilder querybuilder = new sqlitequerybuilder(); // set table querybuilder.settables(table.gettablename()); if (issingular) { querybuilder.appendwhere(table.getcolumnid() + "=" + uri.getlastpathsegment()); } sqlitedatabase db = database.getwritabledatabase(); cursor cursor = querybuilder.query(db, projection, selection, selectionargs, null, null, sortorder); // make sure potential listeners getting notified cursor.setnotificationuri(getcontext().getcontentresolver(), uri); return cursor; } public uri insert(sqliteopenhelper database, tabledescriptor table, uri uri, contentvalues values) { sqlitedatabase sqldb = database.getwritabledatabase(); long id = 0; id = sqldb.insertwithonconflict(table.gettablename(), null, values, sqlitedatabase.conflict_replace); getcontext().getcontentresolver().notifychange(uri, null); return uri.withappendedpath(gettableuri(table), long.tostring(id)); } public int delete(sqliteopenhelper database, tabledescriptor table, boolean issingular, uri uri, string selection, string[] selectionargs) { int rowsdeleted = 0; sqlitedatabase sqldb = database.getwritabledatabase(); if (!issingular) { rowsdeleted = sqldb.delete(table.gettablename(), selection, selectionargs); } else { string id = uri.getlastpathsegment(); if (textutils.isempty(selection)) { rowsdeleted = sqldb.delete(table.gettablename(), table.getcolumnid() + "=" + id, null); } else { rowsdeleted = sqldb.delete(table.gettablename(), table.getcolumnid() + "=" + id + " , " + selection, selectionargs); } } getcontext().getcontentresolver().notifychange(uri, null); return rowsdeleted; } public int update(sqliteopenhelper database, tabledescriptor table, boolean issingular, uri uri, contentvalues values, string selection, string[] selectionargs) { sqlitedatabase sqldb = database.getwritabledatabase(); int rowsupdated = 0; if (!issingular) { rowsupdated = sqldb.update(table.gettablename(), values, selection, selectionargs); } else { string id = uri.getlastpathsegment(); if (textutils.isempty(selection)) { rowsupdated = sqldb.update(table.gettablename(), values, table.getcolumnid() + "=" + id, null); } else { rowsupdated = sqldb.update(table.gettablename(), values, table.getcolumnid() + "=" + id + " , " + selection, selectionargs); } } getcontext().getcontentresolver().notifychange(uri, null); return rowsupdated; } public void checkcolumns(tabledescriptor table, string[] projection) { string[] available = table.getavailablecolumns(); if (projection != null) { hashset<string> requestedcolumns = new hashset<string>( arrays.aslist(projection)); hashset<string> availablecolumns = new hashset<string>( arrays.aslist(available)); // check if columns requested available if (!availablecolumns.containsall(requestedcolumns)) { throw new illegalargumentexception( "unknown columns in projection"); } } } protected uri gettableuri(tabledescriptor table) { uri result = null; string tablename = table.gettablename(); if (appointmentwithtypeview.table_name.equals(tablename)) { result = hektorcontentprovidercontract.appointment_with_type_content_uri; } return result; } } here utility class i'm using when working contentproviders. simplifies database management operations. quite handy if have lot of tables manage.
public class uridescriptor { private tabledescriptor table; private boolean singular; public boolean issingular() { return singular; } public void setsingular(boolean singular) { this.singular = singular; } public tabledescriptor gettable() { return table; } public void settable(tabledescriptor table) { this.table = table; } } this again container class - not interesting.
public class hektorcontentprovider extends contentprovider { private contentproviderhelper helper; // database private hektordatabasehelper database; // used urimacher private static final int appointment_with_type = 290; private static final int appointment_with_type_id = 300; private static final urimatcher surimatcher = new urimatcher( urimatcher.no_match); static { surimatcher.adduri(hektorcontentprovidercontract.authority, hektorcontentprovidercontract.appointment_with_type_base_path, appointment_with_type); surimatcher.adduri(hektorcontentprovidercontract.authority, hektorcontentprovidercontract.appointment_with_type_base_path + "/#", appointment_with_type_id); } @override public boolean oncreate() { database = new hektordatabasehelper(getcontext()); helper = new contentproviderhelper(getcontext()); return false; } @override public cursor query(uri uri, string[] projection, string selection, string[] selectionargs, string sortorder) { uridescriptor descriptor = getdescriptor(uri); helper.checkcolumns(descriptor.gettable(), projection); cursor cursor = helper.query(database, descriptor.gettable(), descriptor.issingular(), uri, projection, selection, selectionargs, sortorder); return cursor; } @override public string gettype(uri uri) { return null; } @override public uri insert(uri uri, contentvalues values) { uridescriptor descriptor = getdescriptor(uri); uri result = helper .insert(database, descriptor.gettable(), uri, values); return result; } @override public int delete(uri uri, string selection, string[] selectionargs) { int rowsdeleted = 0; uridescriptor descriptor = getdescriptor(uri); rowsdeleted = helper.delete(database, descriptor.gettable(), descriptor.issingular(), uri, selection, selectionargs); return rowsdeleted; } @override public int update(uri uri, contentvalues values, string selection, string[] selectionargs) { uridescriptor descriptor = getdescriptor(uri); int rowsupdated = helper.update(database, descriptor.gettable(), descriptor.issingular(), uri, values, selection, selectionargs); return rowsupdated; } protected uridescriptor getdescriptor(uri uri) { uridescriptor descriptor = new uridescriptor(); int uritype = surimatcher.match(uri); switch (uritype) { case appointment_with_type: descriptor.setsingular(false); descriptor.settable(appointmentwithtypeview.getdescriptor()); break; case appointment_with_type_id: descriptor.setsingular(true); descriptor.settable(appointmentwithtypeview.getdescriptor()); break; default: throw new illegalargumentexception("unknown uri: " + uri); } return descriptor; } } that's contentprovider class. quite simple, since of work done in contentproviderhelper class.
this class representing view. write such classes each view or table want create in database. both entities pretty used same way - have change sql statement create view create table. can see, views can created based on select statement - useful if want join several tables. when inserting of appointmentstable / appointmenttypestable tables data available via view too. prefer create such view , contentprovider working on it. select queries simple (just read view). have handle insert / deletes differently though - i.e. insert data associated table.
my application loads data web service, i'm doing on background. i've create content_url in contentprovider each table (i.e. appointmentstable , appointmenttypestable). used background process insert / update data. ui uses content_urls connected views, since need read data.
let me know if you've got idea. share more code if needed. :)
Comments
Post a Comment