Android联合查询 [英] Android union query
问题描述
我有以下问题：
我有一个名为 planing 的表，它包含以下字段：
idplaning，month，year，shop，target，sales
$ p
$ b
月份商店目标销售额
所以表格中会包含这样的信息（忽略idplaning） -------------------------------------------
1 2014 ShopName1 3534 122
2 2014 ShopName1 2323 111
...
12 2014 ShopName1 7865 328
2014商店名称2 4544 544
2 2014 ShopName2 5675 642
。 ..
12 2014 ShopName2 4623 323
...
1 2015 ShopName1 3534 122
2 2015 ShopName1 2323 111
...
12 2015 ShopName1 7865 328
...等等
基本上，表格包含每月目标和每个笑的销售
shop Ianuary二月三月...十一月十二月
------------------------------------ ----------------------
ShopName1 3534 2323 235 3545 3355
...
ShopNamex 1527 7815 631 556 2754
$ / code>
为了做到这一点（在纯SQL中）我可以这样做一个联合：
select shop，sum（Ian），sum（Feb），sum（Mar），sum（Apr），sum（Mai） Iun，sum（Iul），sum（Aug），sum（Sep），sum（Oct），sum（Noi），sum（Decu）from
（
select shop，target as Ian，0如2月，0月3月，0月4月，0月为Mai，0年为Iun，0年为Iul，0年为8月0日为9月0日为10月0日为Noi，0年为Decu， = 1
联合
...
选择店铺，0为Ian，0为2月，0为3月，0为4月，0为Mai，0为Iun，0为Iul，0如Aug，0为Sep，0为Oct，0为Noi，目标为Decu，从刨开的位置开始年= 2015和月= 12
）作为tbl
按店铺分组
现在，我的问题是：如何使用Android中的SQLite数据库内容提供程序与查询方法？
我已经为各种常规数据库操作构建了内容提供程序，并使用 query 方法（而不是 rawquery ）
因此，我得到了一个正常的游标（用于我的 planing 表），如下所示：
String [] col = {MyFirstProvider.RSLDTL_ID_ID，MyFirstProvider.RSLDTL_SHOP，MyFirstProvider.RSLDTL_MONTH，MyFirstProvider.RSLDTL_TARGET，MyFirstProvider.RSLDTL_SALES};
String where =year = 2015;
Cursor cur = getActivity（）。getContentResolver（）。query（MyFirstProvider.CONTENT_URI_RSLDTL，col，where，null，null）;
我的问题：如何使用查询方法为我的UNION-分组查询获取游标（ not the rawquery ）
请用代码回答。谢谢
编辑
我的ContentProvider看起来像这样：
public class MyFirstProvider extends ContentProvider {
static final int DATABASE_VERSION = 7;
私有SQLiteDatabase数据库;
static final String DATABASE_NAME =myxdb.db;
public static final String TABLE_PLANING =planing;
...
static final String URLX =content：//+ PROVIDER_NAME +/ planing;
...
public static final Uri CONTENT_URI_PLANING = Uri.parse（URLX）;
...
static final int uriCode = 1; //一些表
static final int uriCode52 = 53; //计划
static final int uriCode53 = 54; // planing
static final UriMatcher uriMatcher;
private static HashMap< String，String>值;
static {
uriMatcher = new UriMatcher（UriMatcher.NO_MATCH）;
uriMatcher.addURI（PROVIDER_NAME，planing，uriCode52）;
uriMatcher.addURI（PROVIDER_NAME，planing / *，uriCode53）;
...
}
....
@Override
public游标查询（Uri uri，String []投影，字符串where，String [] selectionArgs，String sortOrder）{
SQLiteQueryBuilder qb = new SQLiteQueryBuilder（）;
光标c;
switch（uriMatcher.match（uri））{
case uriCode：
qb.setTables（TABLE_NAME）;
qb.setProjectionMap（values）;
c = qb.query（db，projection，where，selectionArgs，null，null，sortOrder）;
休息;
...
default：
throw new IllegalArgumentException（（qry）Unsupported URI：+ uri）;
}
if（c！= null）{
c.setNotificationUri（getContext（）。getContentResolver（），uri）;
}
return c;
}
那么如何在上述方法中实现您的想法？我的意思是我该如何为我的联合分组复杂查询声明uriCode？
我还没有找到任何方法来使用 query 而不是 rawquery 当您想要进行UNION查询时。
通过 SQLiteQueryBuilder 类包含构建UNION查询的方法，但它不包含任何事实，我认为它目前不存在方法来执行它，我不希望在其他任何地方找到它。
更具体地说，如果你看看 SQLiteQueryBuilder 类，你会发现 buildUnionQuery 方法返回一个 String 并且没有 query 方法将自定义查询字符串作为参数。
也就是说，我在我的代码中采用的解决方案是这样的：
在你的查询中（Uri uri，String [ ]投影，字符串选择，字符串[] selectionArgs，字符串sortOrder） switch语句...
SQLiteQueryBuilder builder = new SQLiteQueryBuilder（）;
builder.setTables（TABLE_NAME）;
HashSet< String> columnsPresentInTable = new HashSet<>（<表中的列>）;
String firstProjection =shop，目标为Ian，0为2月，0为3月，0为4月，0为Mai，0为Iun，0为Iul，0为8月，0为9月，0为10月， 0作为Noi，0作为Decu;
字符串firstSelectSubQuery = builder.buildUnionSubQuery（
null，
firstProjection，
columnsPresentInTable，
0，
getType（uri），
选择，
new String [] {
String.valueOf（2015），
String.valueOf（1）
}，
null
）;
[...]
String lastProjection =shop，0代表Ian，0代表Feb，0代表Mar，0代表Apr，0代表Mai，0代表Iun，0代表Iul，0代表Aug ，0表示Sep，0表示10月，0表示Noi，目标为Decu;
String lastSelectSubQuery = builder.buildUnionSubQuery（
null，
lastProjection，
columnsPresentInTable，
0，
getType（uri），
选择，
new String [] {
String.valueOf（2015），
String.valueOf（12）
}，
null
）;
String unionQuery = builder.buildUnionQuery（
new String [] {
firstSelectSubQuery，
[...]
lastSelectSubQuery
}，
sortOrder，
null
）;
cursor = db.rawQuery（unionQuery，null）;
这种方法看起来似乎比 I have the following problem:
I have a table called planing it has the following fields: so the table will contain info like this (ignoring the idplaning): basically, the table contains the monthly targets and sales for each shop I need to display in a Listview something like this: in order to do that (in plain SQL) I can do a union like this: Now, my problem is: how can I do that using a SQLite Database ContentProvider in Android with the query approach?
I already have the Content provider built for various regular DB operations, and I use the query approach (instead of rawquery) So I obtain a regular cursor (for my planing table) like this: My problem: How can I obtain a cursor for my UNION-Groupped query using the query approach (not the rawquery) Please answer with code. Thank you EDIT
My ContentProvider looks like this: public class MyFirstProvider extends ContentProvider { So how do I implement your idea in my above approach? I mean how do I declare the uriCode for my union grouped complex query? I haven't found any way to use a I'm brought to think it doesn't currently exist by the fact that More specifically, if you look at That said, the solution I adopted in my code is this: Inside your This approach can seem to you a little more tricky than just 这篇关于Android联合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ rawQuery ，也许不是你的问题的答案，但我使用它并推荐它，原因有两个：因为当有一种方法做某事时，我总是喜欢使用它，并且因为我相信它可以让我以支持未来的修改，例如，如果实施查询方法，只需很少的努力。 idplaning, month, year, shop, target, sales
month year shop target sales
-------------------------------------------
1 2014 ShopName1 3534 122
2 2014 ShopName1 2323 111
...
12 2014 ShopName1 7865 328
1 2014 ShopName2 4544 544
2 2014 ShopName2 5675 642
...
12 2014 ShopName2 4623 323
...
1 2015 ShopName1 3534 122
2 2015 ShopName1 2323 111
...
12 2015 ShopName1 7865 328
... and so on
shop Ianuary February March ... November December
----------------------------------------------------------
ShopName1 3534 2323 235 3545 3355
...
ShopNamex 1527 7815 631 556 2754
select shop, sum(Ian), sum(Feb),sum(Mar), sum(Apr), sum(Mai), sum(Iun), sum(Iul), sum(Aug), sum(Sep), sum(Oct), sum(Noi), sum(Decu) from
(
select shop,target as Ian, 0 as Feb, 0 as Mar, 0 as Apr, 0 as Mai, 0 as Iun, 0 as Iul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Noi, 0 as Decu from planing where year=2015 and month=1
union
...
select shop,0 as Ian, 0 as Feb, 0 as Mar, 0 as Apr, 0 as Mai, 0 as Iun, 0 as Iul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Noi, target as Decu from planing where year=2015 and month=12
) as tbl
group by shop
String[] col = {MyFirstProvider.RSLDTL_ID_ID,MyFirstProvider.RSLDTL_SHOP, MyFirstProvider.RSLDTL_MONTH, MyFirstProvider.RSLDTL_TARGET, MyFirstProvider.RSLDTL_SALES};
String where = "year=2015";
Cursor cur = getActivity().getContentResolver().query(MyFirstProvider.CONTENT_URI_RSLDTL, col, where, null, null);
static final int DATABASE_VERSION = 7;
private SQLiteDatabase db;
static final String DATABASE_NAME = "myxdb.db";
public static final String TABLE_PLANING = "planing";
...
static final String URLX = "content://" + PROVIDER_NAME + "/planing";
...
public static final Uri CONTENT_URI_PLANING = Uri.parse(URLX);
...
static final int uriCode = 1; // some table
static final int uriCode52 = 53; // planing
static final int uriCode53 = 54; // planing
static final UriMatcher uriMatcher;
private static HashMap<String, String> values;
static {
uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
uriMatcher.addURI(PROVIDER_NAME, "planing", uriCode52);
uriMatcher.addURI(PROVIDER_NAME, "planing/*", uriCode53);
...
}
....
@Override
public Cursor query(Uri uri, String[] projection, String where, String[] selectionArgs, String sortOrder) {
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
Cursor c;
switch (uriMatcher.match(uri)) {
case uriCode:
qb.setTables(TABLE_NAME);
qb.setProjectionMap(values);
c = qb.query(db, projection, where, selectionArgs, null,null, sortOrder);
break;
...
default:
throw new IllegalArgumentException("(qry)Unsupported URI: " + uri);
}
if (c != null){
c.setNotificationUri(getContext().getContentResolver(), uri);
}
return c;
}
query instead of rawquery when you want to do a UNION query.SQLiteQueryBuilder class contains methods to build a UNION query, but it doesn't contain any method to execute it, and I wouldn't expect to find it anywhere else.SQLiteQueryBuilder class, you'll find that buildUnionQuery method returns a String and there's no query method that takes a custom query String as a parameter.query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) switch statement...SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
builder.setTables(TABLE_NAME);
HashSet<String> columnsPresentInTable = new HashSet<>(<columns in your table>);
String firstProjection = "shop, target as Ian, 0 as Feb, 0 as Mar, 0 as Apr, 0 as Mai, 0 as Iun, 0 as Iul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Noi, 0 as Decu";
String firstSelectSubQuery = builder.buildUnionSubQuery(
null,
firstProjection,
columnsPresentInTable,
0,
getType(uri),
selection,
new String[]{
String.valueOf(2015),
String.valueOf(1)
},
null
);
[...]
String lastProjection = "shop, 0 as Ian, 0 as Feb, 0 as Mar, 0 as Apr, 0 as Mai, 0 as Iun, 0 as Iul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Noi, target as Decu";
String lastSelectSubQuery = builder.buildUnionSubQuery(
null,
lastProjection,
columnsPresentInTable,
0,
getType(uri),
selection,
new String[]{
String.valueOf(2015),
String.valueOf(12)
},
null
);
String unionQuery = builder.buildUnionQuery(
new String[]{
firstSelectSubQuery,
[...]
lastSelectSubQuery
},
sortOrder,
null
);
cursor = db.rawQuery(unionQuery, null);
rawQuery and maybe not the answer to your question, but I'm using it and recommending it anyway for two reasons: because when there's a method to do something I always prefer to use it, and because I believe it allows me to support future modifications, for example if a query approach is implemented, with minimum effort.
