Java Code Examples for com.j256.ormlite.dao.Dao#queryBuilder()

@Test
public void testCountInReadOnlyField() throws Exception {
	Dao<AsField, Integer> dao = createDao(AsField.class, true);

	AsField foo1 = new AsField();
	int val1 = 123213;
	foo1.val = val1;
	assertEquals(1, dao.create(foo1));

	AsField foo2 = new AsField();
	int val2 = 122433213;
	foo2.val = val2;
	assertEquals(1, dao.create(foo2));

	QueryBuilder<AsField, Integer> qb = dao.queryBuilder();
	qb.selectRaw("*");
	int val3 = 12;
	qb.selectRaw(val3 + " AS " + AsField.SUM_FIELD);

	List<AsField> results = dao.queryRaw(qb.prepareStatementString(), dao.getRawRowMapper()).getResults();
	assertEquals(2, results.size());
	assertEquals(val1, (int) results.get(0).val);
	assertEquals(val3, (int) results.get(0).sum);
	assertEquals(val2, (int) results.get(1).val);
	assertEquals(val3, (int) results.get(1).sum);
}
 

private QueryBuilder<Location, Long> getQuery(User user) throws SQLException {
	Dao<Location, Long> dao = DaoStore.getInstance(context).getLocationDao();
	QueryBuilder<Location, Long> query = dao.queryBuilder();

	Where<? extends Temporal, Long> where = query.where();
	if (user != null) {
		where.eq("user_id", user.getId()).and().eq("event_id", user.getUserLocal().getCurrentEvent().getId());
	}

	if (filter != null) {
		filter.and(where);
	}

	query.orderBy("timestamp", false);

	return query;
}
 

private CloseableIterator<Location> iterator() throws SQLException {
	Dao<Location, Long> dao = DaoStore.getInstance(context).getLocationDao();
	QueryBuilder<Location, Long> query = dao.queryBuilder();
	Where<? extends Temporal, Long> where = query.where().ge("timestamp", locationCollection.getLatestDate());
	User currentUser = null;
	try {
		currentUser = UserHelper.getInstance(context.getApplicationContext()).readCurrentUser();
	} catch (UserException e) {
		e.printStackTrace();
	}
	if (currentUser != null) {
		where.and().ne("user_id", currentUser.getId()).and().eq("event_id", currentUser.getUserLocal().getCurrentEvent().getId());
	}
	if (filter != null) {
		filter.and(where);
	}
	query.orderBy("timestamp", false);


	return dao.iterator(query.prepare());
}
 

@Test
public void testUtf8() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);

	Foo foo = new Foo();
	foo.stringField = "اعصاب";
	assertEquals(1, dao.create(foo));

	QueryBuilder<Foo, Integer> qb = dao.queryBuilder();

	List<Foo> results = qb.where().like(Foo.STRING_COLUMN_NAME, '%' + foo.stringField + '%').query();
	assertNotNull(results);
	assertEquals(1, results.size());
	assertEquals(foo.id, results.get(0).id);
	assertEquals(foo.stringField, results.get(0).stringField);

	qb.reset();
	results = qb.where().like(Foo.STRING_COLUMN_NAME, new SelectArg('%' + foo.stringField + '%')).query();
	assertNotNull(results);
	assertEquals(1, results.size());
	assertEquals(foo.id, results.get(0).id);
	assertEquals(foo.stringField, results.get(0).stringField);
}
 

@Test
public void testOffsetWithLimit() throws Exception {
	Dao<Foo, Object> dao = createDao(Foo.class, true);
	Foo foo1 = new Foo();
	foo1.id = "stuff1";
	assertEquals(1, dao.create(foo1));
	Foo foo2 = new Foo();
	foo2.id = "stuff2";
	assertEquals(1, dao.create(foo2));
	assertEquals(2, dao.queryForAll().size());

	QueryBuilder<Foo, Object> qb = dao.queryBuilder();
	long offset = 1;
	long limit = 2;
	qb.offset(offset);
	qb.limit(limit);
	List<Foo> results = dao.query(qb.prepare());

	assertEquals(1, results.size());
}
 

public List<ElementEvaluation> getElementsEvaluation(ListeDesElementsEvaluation listeDesElementsEvaluation) {
    DatabaseHelper dbHelper = new DatabaseHelper(context);
    List<ElementEvaluation> elementEvaluationList = null;
    try {
        Dao<ElementEvaluation, String> elementsEvaluationDao = dbHelper.getDao(ElementEvaluation.class);
        QueryBuilder<ElementEvaluation, String> builder = elementsEvaluationDao.queryBuilder();

        Where where = builder.where();
        where.eq("listeDesElementsEvaluation_id", listeDesElementsEvaluation);

        elementEvaluationList = builder.query();

    } catch (SQLException e) {
        Log.e("SQL Exception", e.getMessage());
    }
    return elementEvaluationList;
}
 

@Test
public void testLimit() throws Exception {
	Dao<Foo, String> fooDao = createTestData();
	QueryBuilder<Foo, String> qb = fooDao.queryBuilder();
	// no limit the default
	List<Foo> results = fooDao.query(qb.prepare());
	assertEquals(2, results.size());
	assertEquals(foo1, results.get(0));
	assertEquals(foo2, results.get(1));
	qb.limit(1L);
	results = fooDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertEquals(foo1, results.get(0));
	// set back to no-limit
	qb.limit(null);
	results = fooDao.query(qb.prepare());
	assertEquals(2, results.size());
	assertEquals(foo1, results.get(0));
	assertEquals(foo2, results.get(1));
}
 

public List<Treatment> getTreatmentDataFromTime(long from, long to, boolean ascending) {
    try {
        Dao<Treatment, Long> daoTreatments = getDao();
        List<Treatment> treatments;
        QueryBuilder<Treatment, Long> queryBuilder = daoTreatments.queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.between("date", from, to);
        PreparedQuery<Treatment> preparedQuery = queryBuilder.prepare();
        treatments = daoTreatments.query(preparedQuery);
        return treatments;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

@Test
public void testSimpleJoinColumnArg() throws Exception {
	Dao<Bar, Integer> barDao = createDao(Bar.class, true);
	Dao<Baz, Integer> bazDao = createDao(Baz.class, true);

	Bar bar1 = new Bar();
	int val = 1313123;
	bar1.val = val;
	assertEquals(1, barDao.create(bar1));
	Bar bar2 = new Bar();
	bar2.val = val;
	assertEquals(1, barDao.create(bar2));

	Baz baz1 = new Baz();
	baz1.bar = bar1;
	baz1.val = val;
	assertEquals(1, bazDao.create(baz1));
	Baz baz2 = new Baz();
	baz2.bar = bar2;
	baz2.val = val + 1;
	assertEquals(1, bazDao.create(baz2));
	Baz baz3 = new Baz();
	baz1.bar = bar2;
	baz1.val = val;
	assertEquals(1, bazDao.create(baz3));
	Baz baz4 = new Baz();
	baz2.bar = bar1;
	baz2.val = val;
	assertEquals(1, bazDao.create(baz4));

	QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
	barQb.where().eq(Bar.VAL_FIELD, new ColumnArg("baz", Baz.VAL_FIELD));
	List<Baz> results = bazDao.queryBuilder().query();
	assertEquals(4, results.size());
	results = bazDao.queryBuilder().join(barQb).query();
	assertEquals(1, results.size());
	assertEquals(bar1.id, results.get(0).bar.id);
}
 

@Test
public void testCountOf() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);

	QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
	assertEquals(0, qb.countOf());

	Foo foo1 = new Foo();
	int val = 123213;
	foo1.val = val;
	assertEquals(1, dao.create(foo1));
	assertEquals(1, qb.countOf());

	Foo foo2 = new Foo();
	foo2.val = val;
	assertEquals(1, dao.create(foo2));
	assertEquals(2, qb.countOf());

	String distinct = "DISTINCT(" + Foo.VAL_COLUMN_NAME + ")";
	assertEquals(1, qb.countOf(distinct));

	qb.setCountOf(distinct);
	assertEquals(1, dao.countOf(qb.prepare()));

	distinct = "DISTINCT(" + Foo.ID_COLUMN_NAME + ")";
	assertEquals(2, qb.countOf(distinct));

	qb.setCountOf(distinct);
	assertEquals(2, dao.countOf(qb.prepare()));
}
 

@Test
public void testMissingAndArg() throws Exception {
	Dao<Foo, String> fooDao = createDao(Foo.class, false);
	QueryBuilder<Foo, String> qb = fooDao.queryBuilder();
	try {
		qb.where().and();
		fail("expected exception");
	} catch (IllegalStateException e) {
		// expected
	}
}
 

@Test
public void testJoinOrder() throws Exception {
	Dao<Bar, Integer> barDao = createDao(Bar.class, true);
	Dao<Baz, Integer> bazDao = createDao(Baz.class, true);

	Bar bar1 = new Bar();
	bar1.val = 2234;
	assertEquals(1, barDao.create(bar1));
	Bar bar2 = new Bar();
	bar2.val = 324322234;
	assertEquals(1, barDao.create(bar2));

	Baz baz1 = new Baz();
	baz1.bar = bar1;
	assertEquals(1, bazDao.create(baz1));
	Baz baz2 = new Baz();
	baz2.bar = bar2;
	assertEquals(1, bazDao.create(baz2));

	QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
	barQb.orderBy(Bar.VAL_FIELD, true);
	List<Baz> results = bazDao.queryBuilder().join(barQb).query();
	assertEquals(2, results.size());
	assertEquals(bar1.id, results.get(0).bar.id);
	assertEquals(bar2.id, results.get(1).bar.id);

	// reset the query to change the order direction
	barQb.reset();
	barQb.orderBy(Bar.VAL_FIELD, false);
	results = bazDao.queryBuilder().join(barQb).query();
	assertEquals(2, results.size());
	assertEquals(bar2.id, results.get(0).bar.id);
	assertEquals(bar1.id, results.get(1).bar.id);
}
 

@Test
@SuppressWarnings("unchecked")
public void testNotNotComparison() throws Exception {
	Dao<Foo, String> fooDao = createTestData();
	QueryBuilder<Foo, String> qb = fooDao.queryBuilder();
	Where<Foo, String> where = qb.where();
	try {
		where.not(where.and(where.eq(Foo.ID_COLUMN_NAME, foo1.id), where.eq(Foo.ID_COLUMN_NAME, foo1.id)));
		fail("expected exception");
	} catch (IllegalArgumentException e) {
		// expected
	}
}
 

@Test
public void testExists() throws Exception {
	Dao<Foo, String> fooDao = createTestData();
	QueryBuilder<Foo, String> innerQb = fooDao.queryBuilder();
	innerQb.where().idEq(foo1.id);
	QueryBuilder<Foo, String> qb = fooDao.queryBuilder();

	qb.where().exists(innerQb);
	List<Foo> results = fooDao.query(qb.prepare());
	assertEquals(2, results.size());
	assertEquals(foo1, results.get(0));
	assertEquals(foo2, results.get(1));
}
 

@Test
public void testClear() throws Exception {
	Dao<Foo, String> dao = createDao(Foo.class, false);
	QueryBuilder<Foo, String> qb = dao.queryBuilder();
	qb.selectColumns(Foo.VAL_COLUMN_NAME);
	qb.groupBy(Foo.VAL_COLUMN_NAME);
	qb.having("COUNT(VAL) > 1");
	qb.where().eq(Foo.ID_COLUMN_NAME, 1);
	qb.reset();
	assertEquals("SELECT * FROM `foo` ", qb.prepareStatementString());
}
 

@Test(expected = SQLException.class)
public void testQueryRawColumnsNotQuery() throws Exception {
	Dao<SchemaFoo, String> dao = createDao(SchemaFoo.class, true);
	QueryBuilder<SchemaFoo, String> qb = dao.queryBuilder();
	qb.selectRaw("COUNT(*)");
	// we can't get SchemaFoo objects with the COUNT(*)
	dao.query(qb.prepare());
}
 

@Test(expected = IllegalArgumentException.class)
public void testGroupByForeignCollection() throws Exception {
	Dao<Project, Integer> dao = createDao(Project.class, false);
	QueryBuilder<Project, Integer> qb = dao.queryBuilder();
	qb.groupBy("categories");
}
 

@Test(expected = IllegalArgumentException.class)
public void testOrderByForeignCollection() throws Exception {
	Dao<Project, Integer> dao = createDao(Project.class, false);
	QueryBuilder<Project, Integer> qb = dao.queryBuilder();
	qb.orderBy("categories", false);
}
 

@Test
public void testLtGtEtc() throws Exception {
	Dao<Foo, String> fooDao = createTestData();
	QueryBuilder<Foo, String> qb = fooDao.queryBuilder();

	qb.where().eq(Foo.VAL_COLUMN_NAME, foo1.val);
	List<Foo> results = fooDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertEquals(foo1, results.get(0));

	qb.where().ge(Foo.VAL_COLUMN_NAME, foo1.val);
	results = fooDao.query(qb.prepare());
	assertEquals(2, results.size());
	assertEquals(foo1, results.get(0));
	assertEquals(foo2, results.get(1));

	qb.where().ge(Foo.VAL_COLUMN_NAME, foo1.val - 1);
	results = fooDao.query(qb.prepare());
	assertEquals(2, results.size());
	assertEquals(foo1, results.get(0));
	assertEquals(foo2, results.get(1));

	qb.where().ge(Foo.VAL_COLUMN_NAME, foo2.val);
	results = fooDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertEquals(foo2, results.get(0));

	qb.where().gt(Foo.VAL_COLUMN_NAME, foo1.val);
	results = fooDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertEquals(foo2, results.get(0));

	qb.where().gt(Foo.VAL_COLUMN_NAME, foo1.val - 1);
	results = fooDao.query(qb.prepare());
	assertEquals(2, results.size());
	assertEquals(foo1, results.get(0));
	assertEquals(foo2, results.get(1));

	qb.where().gt(Foo.VAL_COLUMN_NAME, foo2.val);
	results = fooDao.query(qb.prepare());
	assertEquals(0, results.size());

	qb.where().le(Foo.VAL_COLUMN_NAME, foo1.val);
	results = fooDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertEquals(foo1, results.get(0));

	qb.where().le(Foo.VAL_COLUMN_NAME, foo1.val - 1);
	results = fooDao.query(qb.prepare());
	assertEquals(0, results.size());

	qb.where().lt(Foo.VAL_COLUMN_NAME, foo1.val);
	results = fooDao.query(qb.prepare());
	assertEquals(0, results.size());

	qb.where().lt(Foo.VAL_COLUMN_NAME, foo1.val + 1);
	results = fooDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertEquals(foo1, results.get(0));

	qb.where().ne(Foo.VAL_COLUMN_NAME, foo1.val);
	results = fooDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertEquals(foo2, results.get(0));

	qb.where().ne(Foo.VAL_COLUMN_NAME, foo1.val).and().ne(Foo.VAL_COLUMN_NAME, foo2.val);
	results = fooDao.query(qb.prepare());
	assertEquals(0, results.size());
}
 

private PreparedQuery<Location> buildQuery(Dao<Location, Long> dao, int filterId) throws SQLException {
      QueryBuilder<Location, Long> qb = dao.queryBuilder();
      Calendar c = Calendar.getInstance();
String subtitle = "";
String footerText = "All people have been returned";

      if (filterId == getResources().getInteger(R.integer.time_filter_last_month)) {
          subtitle = "Last Month";
          footerText = "End of results for Last Month filter";
          c.add(Calendar.MONTH, -1);
      } else if (filterId == getResources().getInteger(R.integer.time_filter_last_week)) {
          subtitle = "Last Week";
          footerText = "End of results for Last Week filter";
          c.add(Calendar.DAY_OF_MONTH, -7);
      } else if (filterId == getResources().getInteger(R.integer.time_filter_last_24_hours)) {
          subtitle = "Last 24 Hours";
          footerText = "End of results for Last 24 Hours filter";
          c.add(Calendar.HOUR, -24);
      } else if (filterId == getResources().getInteger(R.integer.time_filter_today)) {
          subtitle = "Since Midnight";
          footerText = "End of results for Today filter";
          c.set(Calendar.HOUR_OF_DAY, 0);
          c.set(Calendar.MINUTE, 0);
          c.set(Calendar.SECOND, 0);
          c.set(Calendar.MILLISECOND, 0);
      }  else if (filterId == getResources().getInteger(R.integer.time_filter_custom)) {
	String customFilterTimeUnit = getCustomTimeUnit();
	int customTimeNumber = getCustomTimeNumber();

	subtitle = "Last " + customTimeNumber + " " + customFilterTimeUnit;
	footerText = "End of results for custom filter";
	switch (customFilterTimeUnit) {
		case "Hours":
			c.add(Calendar.HOUR, -1 * customTimeNumber);
			break;
		case "Days":
			c.add(Calendar.DAY_OF_MONTH, -1 * customTimeNumber);
			break;
		case "Months":
			c.add(Calendar.MONTH, -1 * customTimeNumber);
			break;
		default:
			c.add(Calendar.MINUTE, -1 * customTimeNumber);
			break;
	}

} else {
          // no filter
          c.setTime(new Date(0));
      }

      TextView footerTextView = footer.findViewById(R.id.footer_text);
      footerTextView.setText(footerText);
User currentUser = null;
try {
	currentUser = UserHelper.getInstance(context).readCurrentUser();
} catch (UserException e) {
	e.printStackTrace();
}

Where<Location, Long> where = qb.where().gt("timestamp", c.getTime());
if (currentUser != null) {
	where
              .and()
              .ne("user_id", currentUser.getId())
              .and()
              .eq("event_id", currentUser.getUserLocal().getCurrentEvent().getId());
      }

qb.orderBy("timestamp", false);

      ((AppCompatActivity) getActivity()).getSupportActionBar().setSubtitle(subtitle);

      return qb.prepare();
  }