@Override
public HomeInvite findHomeInviteById(int id) {
String q = "find homeInvite where id = :id";
Query<HomeInvite> query = ebean.createQuery(HomeInvite.class, q);
query.setParameter("id", id);
return query.findUnique();
}
@Override
public Set<HomeInvite> findAllAvailableInvites(String invitee) {
String q;
if (configCore.useEbeanSearchLower())
q = "find homeInvite where lower(invitedPlayer) = lower(:invitee)";
else q = "find homeInvite where invitedPlayer = :invitee";
Query<HomeInvite> query = ebean.createQuery(HomeInvite.class, q);
query.setParameter("invitee", invitee);
return query.findSet();
}
@Test
public void test() {
ResetBasicData.reset();
Query<Order> q =
Ebean.find(Order.class)
.fetch("shipments")
.fetch("details")
.fetch("details.product")
.fetch("customer")
.where()
.gt("id", 0)
.query();
List<Order> list = q.findList();
String sql = q.getGeneratedSql();
Assert.assertTrue(!list.isEmpty());
Assert.assertTrue(sql.contains("join o_customer "));
Assert.assertFalse(sql.contains("left outer join contact "));
Assert.assertFalse(sql.contains("left outer join o_order_detail "));
Assert.assertFalse(sql.contains("left outer join o_product "));
}
public static List<Object> findAllIdsByUserId(Long id) {
final Query<Event> events = find.where().eq("userAdmin.id", id).select("id");
if (events != null) {
return events.findIds();
}
return new ArrayList<Object>();
}
public Result readAll() {
try {
String sql =
String.format(
"select id, shop_id, user_id, create_time from shake_record"
+ " join shop on shop.id = shop_id"
+ " where shop.app_id = %s",
session("appId"));
RawSql rawSql =
RawSqlBuilder.parse(sql)
.columnMapping("id", "id")
.columnMapping("shop_id", "shop_id")
.columnMapping("user_id", "user_id")
.columnMapping("create_time", "create_time")
.create();
Query<ShakeRecord> query = Ebean.find(ShakeRecord.class);
query.setRawSql(rawSql);
List<ShakeRecord> shakeList = query.findList();
return ok(Json.toJson(shakeList));
} catch (Throwable e) {
return status(ErrDefinition.E_SHAKE_RECORD_READ_ERROR, Messages.get("shakerecord.failure"));
}
}
@Test
public void testQuery() {
Car car = new Car();
car.setLicenseNumber("MARIOS_CAR_LICENSE");
Ebean.save(car);
VehicleDriver driver = new VehicleDriver();
driver.setName("Mario");
driver.setVehicle(car);
Ebean.save(driver);
Query<VehicleDriver> query = Ebean.find(VehicleDriver.class);
query.where().eq("vehicle.licenseNumber", "MARIOS_CAR_LICENSE");
List<VehicleDriver> drivers = query.findList();
Assert.assertNotNull(drivers);
Assert.assertEquals(1, drivers.size());
Assert.assertNotNull(drivers.get(0));
Assert.assertEquals("Mario", drivers.get(0).getName());
Assert.assertEquals("MARIOS_CAR_LICENSE", drivers.get(0).getVehicle().getLicenseNumber());
Vehicle car2 = Ebean.find(Vehicle.class, car.getId());
car2.setLicenseNumber("test");
Ebean.save(car);
}
public Result list() {
String jointTagNames = Form.form().bindFromRequest().get("tags");
// Fetch items from the database
List<Item> items;
if (jointTagNames != null && !jointTagNames.isEmpty()) {
List<String> tagNames = Arrays.asList(jointTagNames.split(","));
String oql =
"find item "
+ "where tags.name in (:tagList) "
+ "group by id "
+ "having count(distinct tags.name) = :tagCount";
Query<Item> query = Item.find.setQuery(oql);
query.setParameter("tagList", tagNames);
query.setParameter("tagCount", tagNames.size());
items = query.orderBy().desc("uploadDate").findList();
} else {
items = Item.find.orderBy().desc("uploadDate").findList();
}
if (items != null) {
String serialized =
new JSONSerializer()
.include("id")
.include("name")
.include("storageKey")
.include("tags.name")
.include("uploadDate")
.include("fileSize")
.exclude("*")
.serialize(items);
return ok(serialized);
} else return notFound();
}
public void runExample() {
LoadExampleData.load();
List<Customer> list2 = Customer.find.query().select("name").fetch("contacts").findList();
PathProperties pathProperties =
PathProperties.parse("(id,version,name,contacts(id,email,version))");
Query<Customer> query = Customer.find.query();
pathProperties.apply(query);
List<Customer> list = query.findList();
JsonContext jsonContext = Customer.find.db().json();
String jsonString = jsonContext.toJson(list);
System.out.println(jsonString);
for (Customer customer : list2) {
customer.getName();
List<Contact> contacts = customer.getContacts();
if (contacts != null) {
for (Contact contact : contacts) {
System.out.println("contact: " + contact.getFirstName() + " " + contact.getLastName());
}
}
}
}
/**
* Applies sorting as defined in Table to specified Query.
*
* @param query
*/
protected void applySorting(Query query) {
if (table.getSortBy() != null && table.getSortDirection() != Table.SORT_NONE) {
query.orderBy(table.getSortBy() + " " + table.getSortDirection());
} else {
query.orderBy("");
}
}
public void testForUpdate() {
ResetBasicData.reset();
Query<Order> query =
Ebean.find(Order.class)
.setAutofetch(false)
.setForUpdate(false)
.setMaxRows(1)
.order()
.asc("orderDate")
.order()
.desc("id");
int rc = query.findList().size();
Assert.assertTrue(rc > 0);
Assert.assertTrue(!query.getGeneratedSql().toLowerCase().contains("for update"));
query =
Ebean.find(Order.class)
.setAutofetch(false)
.setForUpdate(true)
.setMaxRows(1)
.order()
.asc("orderDate")
.order()
.desc("id");
rc = query.findList().size();
Assert.assertTrue(rc > 0);
Assert.assertTrue(query.getGeneratedSql().toLowerCase().contains("for update"));
}
public static Page<Official> page(
int page, int pageSize, String sortBy, String order, String filter) {
Query<Official> query = Ebean.find(Official.class);
query.where().ilike("lastName", "%" + filter + "%");
query.where().orderBy(sortBy + " " + order);
Page<Official> p = query.findPagingList(pageSize).getPage(page);
return p;
}
@Override
public Set<HomeInvite> findInvitesByHome(Home home) {
String q = "find homeInvite where home = :home";
Query<HomeInvite> query = ebean.createQuery(HomeInvite.class, q);
query.setParameter("home", home.getId());
return query.findSet();
}
public int getRowCount() {
if (query == null) {
return 0;
} else {
applySorting(query);
Query q = applyFilters(query);
return q.findRowCount();
}
}
public static Official findByNumber(String number, ProcessingType processingType) {
Official official;
Query<Official> query = null;
if (processingType.equals(ProcessingType.batch)) query = ebeanServer.find(Official.class);
else if (processingType.equals(ProcessingType.online)) query = Ebean.find(Official.class);
query.where().eq("number", number);
official = query.findUnique();
return official;
}
public static Official findByName(
String lastName, String firstName, ProcessingType processingType) {
Official official;
Query<Official> query = null;
if (processingType.equals(ProcessingType.batch)) query = ebeanServer.find(Official.class);
else if (processingType.equals(ProcessingType.online)) query = Ebean.find(Official.class);
query.where().eq("lastName", lastName);
query.where().eq("firstName", firstName);
official = query.findUnique();
return official;
}
/**
* Creates a _new_ Query from the specified Query and the filters as defined in Table.
*
* @param query
* @return a new Query object (even if no filters exist)
*/
protected Query applyFilters(Query query) {
Query q = query.copy();
if (table.getFilters().isEmpty()) {
return q;
}
for (Map.Entry<String, Filter> e : table.getFilters().entrySet()) {
Filter f = e.getValue();
q.where(f.asExpression(q));
}
return q;
}
public static List<Event> findAllByUserId(Long id) {
final Query<Event> events =
find.where()
.eq("userAdmin.id", id)
.select(
"id, heroImgUrl, eventEnd, eventStart, slug, status, schoolId, fundraisingEnd, fundraisingStart, goal, name, userAdmin")
.fetch("userAdmin");
if (events != null) {
return events.findList();
}
return new ArrayList<Event>();
}
/** {@inheritDoc} */
@Override
@Transactional(readOnly = true)
public PagingList<Session> find(
User user, String name, String orderBy, boolean ascending, int pageSize) {
// TODO union with sessions where the user has participated
Query<Session> query = ebean.find(Session.class);
ExpressionList<Session> expr = query.where().eq("author", user);
if (!Strings.isNullOrEmpty(name)) {
expr = expr.ilike("name", name);
}
query = ascending ? expr.orderBy().asc(orderBy) : expr.orderBy().desc(orderBy);
return new EbeanPagingList<Session>(query.findPagingList(pageSize));
}
@Override
public HomeInvite findInviteByHomeAndInvitee(Home home, String invitee) {
String q;
if (configCore.useEbeanSearchLower())
q = "find homeInvite where home = :home and lower(invitedPlayer) = lower(:invitee)";
else q = "find homeInvite where home = :home and invitedPlayer = :invitee";
Query<HomeInvite> query = ebean.createQuery(HomeInvite.class, q);
query.setParameter("home", home.getId());
query.setParameter("invitee", invitee);
return query.findUnique();
}
/** {@inheritDoc} */
@Override
public List<? extends Object> getList() {
if (query == null) {
return null;
} else {
applySorting(query);
Query q = applyFilters(query);
if (table.getPagingEnabled()) {
q.setFirstRow((table.getCurrentPage() - 1) * table.getEntriesPerPage());
q.setMaxRows(table.getEntriesPerPage());
}
return q.findList();
}
}
public void runExampleUsingOrders() {
LoadExampleData.load();
PathProperties pathProperties =
PathProperties.parse("(id,status,orderDate,customer(id,name),details(*,product(sku)))");
Query<Order> query = Ebean.createQuery(Order.class);
pathProperties.apply(query);
List<Order> orders = query.where().gt("id", 1).findList();
String rawJson = Ebean.json().toJson(orders);
System.out.println(rawJson);
}
// find airport whose name is closest to the argument string
protected static List<Airport> getAirportsLevenshtein(String string) {
String sql =
"select id, name, city, country, code, icao as ICAO, latitude, longitude, altitude, timezone, dst as DST "
+ "from airport "
+ "order by levenshtein(upper(replace(name, ' Intl', '')), '"
+ string
+ "') asc limit 1";
// TODO: if string contains intl or interntnl or international...
// replace it with intl and dont remove it from name?
// or easier to remove it and add name like '%Intl%'?
RawSql rawSql = RawSqlBuilder.parse(sql).create();
Query<Airport> query = Ebean.find(Airport.class);
query.setRawSql(rawSql);
return query.findList();
}
@Test
public void testCountOrderBy() {
ResetBasicData.reset();
Query<Order> query =
Ebean.find(Order.class).setAutofetch(false).order().asc("orderDate").order().desc("id");
// .orderBy("orderDate");
int rc = query.findList().size();
// int rc = query.findRowCount();
Assert.assertTrue(rc > 0);
// String generatedSql = query.getGeneratedSql();
// Assert.assertFalse(generatedSql.contains("order by"));
}
/**
* Retrieves a list of child objects for the given junctions
*
* @param junctions The junctions to get the child objects from
* @param params The params passed to the services
* @return A list of child objects
*/
private List<R> getChildObjects(List<? extends Model> junctions, ServiceParams params) {
List ids = new ArrayList();
for (Model junction : junctions) {
List<String> fields = Arrays.asList(junction._ebean_getFieldNames());
int fieldIndex = fields.indexOf(rightFieldName);
ids.add(EbeanUtil.getFieldValue((R) junction._ebean_getField(fieldIndex, junction), "id"));
}
Query<R> query = createQuery(params);
handleFieldsAndFetches(query, rightClass, params);
// ?orderBy=name asc
if (params.orderBy != null && params.orderBy.length > 0) {
query.orderBy(formatOrderBy(params.orderBy, rightClass));
}
return query.where().in("id", ids).findList();
}
@Test
public void testFind() throws Exception {
InetAddressRecord record = getMockInetAddressRecord();
when(query.findUnique()).thenReturn(record);
Assert.assertEquals("Record should be the same!", record, manager.find("127.0.0.1"));
verify(query).setParameter("address", "127.0.0.1");
verify(query, times(1)).findUnique();
}
/**
* Filters a query to return only the junctions that point to the left model and the right model
*
* @param leftId The id of the left item
* @param rightId The id of the right item
* @param query The query to filter
* @return
*/
private List<J> applyJunctionFiltering(Long leftId, Long rightId, Query<J> query) {
if (query == null) {
query = createJunctionQuery(new ServiceParams());
}
return query
.where()
.eq(getTableName(leftClass, leftFieldName) + "_id", leftId)
.eq(getTableName(junctionClass, rightFieldName) + "_id", rightId)
.findList();
}
public void test() {
ResetBasicData.reset();
// Due to the use of maxRows... we will convert
// the fetch join to contacts over to a query join
// ... otherwise we wouldn't be able to use the
// limit offset clause
Query<Customer> query =
Ebean.find(Customer.class)
// this will automatically get converted to a
// query join ... due to the maxRows
.fetch("contacts")
.setMaxRows(5);
List<Customer> list = query.findList();
System.out.println(list);
}
/*
* query 에 저장되어 있는 검색 조건을 만족하면서,
* labelIds 에 해당되는 라벨을 모두 가지고 있는 labelOwner 들의 ID를 찾는다.
* 조건을 만족하는 건이 없을 경우 빈 List 가 반환된다.
*/
private static List<Object> findIds(final Query<LabelOwner> query, final Set<Long> labelIds) {
final List<Object> ids = new ArrayList<>();
query
.copy()
.findVisit(
new QueryResultVisitor<LabelOwner>() {
/** @see com.avaje.ebean.QueryResultVisitor#accept */
@Override
public boolean accept(LabelOwner labelOwner) {
if (hasLabels(labelOwner, labelIds)) {
ids.add(labelOwner.asResource().getId());
}
return true;
}
});
return ids;
}
public static List<Official> findAll() {
Query<Official> query = Ebean.find(Official.class);
List<Official> officials = query.findList();
return officials;
}
public static List<Official> findActive(boolean active) {
Query<Official> query = Ebean.find(Official.class);
query.where().eq("active", active);
List<Official> officials = query.findList();
return officials;
}
