Java Code Examples for android.database.sqlite.SQLiteQueryBuilder#setDistinct()

The following examples show how to use android.database.sqlite.SQLiteQueryBuilder#setDistinct() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: DbAdapter.java    From Onosendai with Apache License 2.0 6 votes vote down vote up 
@Override
public List<Tweet> findTweetsWithMeta (final int columnId, final MetaType metaType, final String data, final int numberOf) {
	if (!checkDbOpen()) return null;
	Cursor c = null;
	try {
		final SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
		qb.setTables(TBL_TW + " INNER JOIN " + TBL_TM + " ON " + TBL_TW + "." + TBL_TW_ID + " = " + TBL_TM_TWID);
		qb.setDistinct(true);
		c = qb.query(this.mDb,
				new String[] { TBL_TW + "." + TBL_TW_ID, TBL_TW_SID, TBL_TW_USERNAME, TBL_TW_FULLNAME, TBL_TW_USERSUBTITLE, TBL_TW_FULLSUBTITLE, TBL_TW_OWNER_USERNAME, TBL_TW_BODY, TBL_TW_TIME, TBL_TW_AVATAR, TBL_TW_INLINEMEDIA, TBL_TW_QUOTED_SID, TBL_TW_FILTERED },
				TBL_TW + "." + TBL_TW_ID + "=" + TBL_TM_TWID + " AND " + TBL_TM_TYPE + "=" + metaType.getId() + " AND " + TBL_TM_DATA + "=?"
						+ (columnId > Integer.MIN_VALUE ? " AND " + TBL_TW_COLID + "=" + columnId : ""),
				new String[] { data },
				TBL_TW_SID, null, TBL_TW_TIME + " desc", String.valueOf(numberOf));
		return readTweets(c, false);
	}
	finally {
		IoHelper.closeQuietly(c);
	}
}
Example 2
Source File: MonsterHunterDatabaseHelper.java    From MonsterHunter4UDatabase with MIT License 5 votes vote down vote up 
private SQLiteQueryBuilder builderHabitat(boolean Distinct) {
    String h = "h";
    String m = "m";
    String l = "l";

    HashMap<String, String> projectionMap = new HashMap<String, String>();

    projectionMap.put("_id", h + "." + S.COLUMN_HABITAT_ID + " AS " + "_id");
    projectionMap.put("start_area", h + "." + S.COLUMN_HABITAT_START + " AS " + "start_area");
    projectionMap.put("move_area", h + "." + S.COLUMN_HABITAT_AREAS + " AS " + "move_area");
    projectionMap.put("rest_area", h + "." + S.COLUMN_HABITAT_REST + " AS " + "rest_area");

    projectionMap.put(l + S.COLUMN_LOCATIONS_ID, l + "." + S.COLUMN_LOCATIONS_ID + " AS " + l + S.COLUMN_LOCATIONS_ID);
    projectionMap.put(l + S.COLUMN_LOCATIONS_NAME, l + "." + S.COLUMN_LOCATIONS_NAME + " AS " + l + S.COLUMN_LOCATIONS_NAME);
    projectionMap.put(l + S.COLUMN_LOCATIONS_MAP, l + "." + S.COLUMN_LOCATIONS_MAP + " AS " + l + S.COLUMN_LOCATIONS_MAP);

    projectionMap.put(m + S.COLUMN_MONSTERS_ID, m + "." + S.COLUMN_MONSTERS_ID + " AS " + m + S.COLUMN_MONSTERS_ID);
    projectionMap.put(m + S.COLUMN_MONSTERS_SORT_NAME, m + "." + S.COLUMN_MONSTERS_SORT_NAME + " AS " + m + S.COLUMN_MONSTERS_SORT_NAME);
    projectionMap.put(m + S.COLUMN_MONSTERS_NAME, m + "." + S.COLUMN_MONSTERS_NAME + " AS " + m + S.COLUMN_MONSTERS_NAME);
    projectionMap.put(m + S.COLUMN_MONSTERS_CLASS, m + "." + S.COLUMN_MONSTERS_CLASS + " AS " + m + S.COLUMN_MONSTERS_CLASS);
    projectionMap.put(m + S.COLUMN_MONSTERS_FILE_LOCATION, m + "." + S.COLUMN_MONSTERS_FILE_LOCATION + " AS " + m + S.COLUMN_MONSTERS_FILE_LOCATION);

    //Create new querybuilder
    SQLiteQueryBuilder QB = new SQLiteQueryBuilder();

    QB.setTables(S.TABLE_HABITAT + " AS h" + " LEFT OUTER JOIN " + S.TABLE_MONSTERS + " AS m" + " ON " + "h." +
            S.COLUMN_HABITAT_MONSTER_ID + " = " + "m." + S.COLUMN_MONSTERS_ID + " LEFT OUTER JOIN " + S.TABLE_LOCATIONS +
            " AS l " + " ON " + "h." + S.COLUMN_HABITAT_LOCATION_ID + " = " + "l." + S.COLUMN_LOCATIONS_ID);

    QB.setDistinct(Distinct);
    QB.setProjectionMap(projectionMap);
    return QB;
}
Example 3
Source File: MonsterHunterDatabaseHelper.java    From MonsterHunter4UDatabase with MIT License 5 votes vote down vote up 
private SQLiteQueryBuilder builderMonsterToArena(boolean Distinct) {
//		SELECT mta._id AS _id, mta.monster_id, mta.arena_id,
//		m.name AS mname, a.name AS aname,
//		FROM monster_to_arena AS mta
//		LEFT OUTER JOIN monsters AS m ON mta.monster_id = m._id
//		LEFT OUTER JOIN arena_quests AS a ON mta.arena_id = a._id;

        String mta = "mta";
        String m = "m";
        String a = "a";

        HashMap<String, String> projectionMap = new HashMap<String, String>();

        projectionMap.put("_id", mta + "." + S.COLUMN_MONSTER_TO_ARENA_ID + " AS " + "_id");

        projectionMap.put(S.COLUMN_MONSTER_TO_ARENA_ID, mta + "." + S.COLUMN_MONSTER_TO_ARENA_ID);
        projectionMap.put(S.COLUMN_MONSTER_TO_ARENA_MONSTER_ID, mta + "." + S.COLUMN_MONSTER_TO_ARENA_MONSTER_ID);
        projectionMap.put(S.COLUMN_MONSTER_TO_ARENA_ARENA_ID, mta + "." + S.COLUMN_MONSTER_TO_ARENA_ARENA_ID);

        projectionMap.put(m + S.COLUMN_MONSTERS_NAME, m + "." + S.COLUMN_MONSTERS_NAME + " AS " + m + S.COLUMN_MONSTERS_NAME);
        projectionMap.put(S.COLUMN_MONSTERS_TRAIT, m + "." + S.COLUMN_MONSTERS_TRAIT);
        projectionMap.put(S.COLUMN_MONSTERS_FILE_LOCATION, m + "." + S.COLUMN_MONSTERS_FILE_LOCATION);
        projectionMap.put(a + S.COLUMN_ARENA_QUESTS_NAME, a + "." + S.COLUMN_ARENA_QUESTS_NAME + " AS " + a + S.COLUMN_ARENA_QUESTS_NAME);

        //Create new querybuilder
        SQLiteQueryBuilder QB = new SQLiteQueryBuilder();

        QB.setTables(S.TABLE_MONSTER_TO_ARENA + " AS mta" + " LEFT OUTER JOIN " + S.TABLE_MONSTERS + " AS m" + " ON " + "mta." +
                S.COLUMN_MONSTER_TO_ARENA_MONSTER_ID + " = " + "m." + S.COLUMN_MONSTERS_ID + " LEFT OUTER JOIN " + S.TABLE_ARENA_QUESTS +
                " AS a " + " ON " + "mta." + S.COLUMN_MONSTER_TO_ARENA_ARENA_ID + " = " + "a." + S.COLUMN_ARENA_QUESTS_ID);

        QB.setDistinct(Distinct);
        QB.setProjectionMap(projectionMap);
        return QB;
    }
Example 4
Source File: MonsterHunterDatabaseHelper.java    From MonsterHunter4UDatabase with MIT License 5 votes vote down vote up 
private SQLiteQueryBuilder builderMonsterToQuest(boolean Distinct) {
//		SELECT mtq._id AS _id, mtq.monster_id, mtq.quest_id,
//		mtq.unstable, m.name AS mname, q.name AS qname,
//		q.hub, q.stars
//		FROM monster_to_quest AS mtq
//		LEFT OUTER JOIN monsters AS m ON mtq.monster_id = m._id
//		LEFT OUTER JOIN quests AS q ON mtq.quest_id = q._id;

        String mtq = "mtq";
        String m = "m";
        String q = "q";

        HashMap<String, String> projectionMap = new HashMap<String, String>();

        projectionMap.put("_id", mtq + "." + S.COLUMN_MONSTER_TO_QUEST_ID + " AS " + "_id");

        projectionMap.put(S.COLUMN_MONSTER_TO_QUEST_MONSTER_ID, mtq + "." + S.COLUMN_MONSTER_TO_QUEST_MONSTER_ID);
        projectionMap.put(S.COLUMN_MONSTER_TO_QUEST_QUEST_ID, mtq + "." + S.COLUMN_MONSTER_TO_QUEST_QUEST_ID);
        projectionMap.put(S.COLUMN_MONSTER_TO_QUEST_UNSTABLE, mtq + "." + S.COLUMN_MONSTER_TO_QUEST_UNSTABLE);

        projectionMap.put(m + S.COLUMN_MONSTERS_NAME, m + "." + S.COLUMN_MONSTERS_NAME + " AS " + m + S.COLUMN_MONSTERS_NAME);
        projectionMap.put(S.COLUMN_MONSTERS_TRAIT, m + "." + S.COLUMN_MONSTERS_TRAIT);
        projectionMap.put(S.COLUMN_MONSTERS_FILE_LOCATION, m + "." + S.COLUMN_MONSTERS_FILE_LOCATION);
        projectionMap.put(q + S.COLUMN_QUESTS_NAME, q + "." + S.COLUMN_QUESTS_NAME + " AS " + q + S.COLUMN_QUESTS_NAME);
        projectionMap.put(S.COLUMN_QUESTS_HUB, q + "." + S.COLUMN_QUESTS_HUB);
        projectionMap.put(S.COLUMN_QUESTS_STARS, q + "." + S.COLUMN_QUESTS_STARS);

        //Create new querybuilder
        SQLiteQueryBuilder QB = new SQLiteQueryBuilder();

        QB.setTables(S.TABLE_MONSTER_TO_QUEST + " AS mtq" + " LEFT OUTER JOIN " + S.TABLE_MONSTERS + " AS m" + " ON " + "mtq." +
                S.COLUMN_MONSTER_TO_QUEST_MONSTER_ID + " = " + "m." + S.COLUMN_MONSTERS_ID + " LEFT OUTER JOIN " + S.TABLE_QUESTS +
                " AS q " + " ON " + "mtq." + S.COLUMN_MONSTER_TO_QUEST_QUEST_ID + " = " + "q." + S.COLUMN_QUESTS_ID);

        QB.setDistinct(Distinct);
        QB.setProjectionMap(projectionMap);
        return QB;
    }
Example 5
Source File: DbAdapter.java    From Onosendai with Apache License 2.0 5 votes vote down vote up 
@Override
public Map<String, Long> getColumnUserStats (final int columnId) {
	final SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
	qb.setTables(TBL_TW + " INNER JOIN " + TBL_TM + " ON " + TBL_TW + "." + TBL_TW_ID + " = " + TBL_TM_TWID);
	qb.setDistinct(true);
	final Cursor c = qb.query(this.mDb,
			new String[] { TBL_TM_DATA, "count(*) AS count" },
			TBL_TM_TYPE + "=" + MetaType.OWNER_NAME.getId() + " AND " + TBL_TW_COLID + "=?",
			new String[] { String.valueOf(columnId) },
			TBL_TM_DATA,
			"count>1",
			"count DESC",
			"50");
	try {
		final Map<String, Long> ret = new LinkedHashMap<String, Long>();
		if (c != null && c.moveToFirst()) {
			final int colUsername = c.getColumnIndex(TBL_TM_DATA);
			final int colCount = c.getColumnIndex("count");
			do {
				final String username = c.getString(colUsername);
				final Long count = c.getLong(colCount);
				ret.put(username, count);
			}
			while (c.moveToNext());
		}
		return ret;
	}
	finally {
		IoHelper.closeQuietly(c);
	}
}
Example 6
Source File: MmsSmsDatabase.java    From bcm-android with GNU General Public License v3.0 4 votes vote down vote up 
private Cursor queryTables(String[] projection, String selection, String order, String limit, String groupBy, String having) {
        String[] mmsProjection = getMmsProjection();

        String[] smsProjection = getSmsProjection();

        SQLiteQueryBuilder mmsQueryBuilder = new SQLiteQueryBuilder();
        SQLiteQueryBuilder smsQueryBuilder = new SQLiteQueryBuilder();

        mmsQueryBuilder.setDistinct(true);
        smsQueryBuilder.setDistinct(true);

        smsQueryBuilder.setTables(SmsDatabase.TABLE_NAME);
        mmsQueryBuilder.setTables(MmsDatabase.TABLE_NAME + " LEFT OUTER JOIN " +
                AttachmentDatabase.TABLE_NAME +
                " ON " + AttachmentDatabase.TABLE_NAME + "." + AttachmentDatabase.ROW_ID + " = " +
                " (SELECT " + AttachmentDatabase.TABLE_NAME + "." + AttachmentDatabase.ROW_ID +
                " FROM " + AttachmentDatabase.TABLE_NAME + " WHERE " +
                AttachmentDatabase.TABLE_NAME + "." + AttachmentDatabase.MMS_ID + " = " +
                MmsDatabase.TABLE_NAME + "." + MmsDatabase.ID + " LIMIT 1)");


        Set<String> mmsColumnsPresent = getMmsColumnsPresent();

        Set<String> smsColumnsPresent = getSmsColumnsPresent();

//        @SuppressWarnings("deprecation")
        String mmsSubQuery = mmsQueryBuilder.buildUnionSubQuery(TRANSPORT, mmsProjection, mmsColumnsPresent, 4, MMS_TRANSPORT, selection, null, null);
        ALog.d(TAG, "mmsSubQuery: " + mmsSubQuery + "\n");

//        @SuppressWarnings("deprecation")
        String smsSubQuery = smsQueryBuilder.buildUnionSubQuery(TRANSPORT, smsProjection, smsColumnsPresent, 4, SMS_TRANSPORT, selection, null, null);
        ALog.d(TAG, "smsSubQuery: " + smsSubQuery + "\n");

        SQLiteQueryBuilder unionQueryBuilder = new SQLiteQueryBuilder();
        String unionQuery = unionQueryBuilder.buildUnionQuery(new String[]{smsSubQuery, mmsSubQuery}, order, limit);
        ALog.d(TAG, "unionQuery: " + unionQuery + "\n");

        SQLiteQueryBuilder outerQueryBuilder = new SQLiteQueryBuilder();
        outerQueryBuilder.setTables("(" + unionQuery + ")");
//        @SuppressWarnings("deprecation")
        String query = outerQueryBuilder.buildQuery(projection, null, groupBy, having, null, null);
        ALog.d(TAG, "target query: " + query + "\n");

        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        return db.rawQuery(query, null);
    }