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 RawSqlParentLoad() {
List<Data> exampleData = new ArrayList<>();
exampleData.add(new Data(0));
exampleData.add(new Data(1));
exampleData.add(new Data(2));
ChildA a = new ChildA(0);
a.setData(exampleData);
Ebean.save(a);
ChildB b = new ChildB(1);
b.setData(exampleData);
Ebean.save(b);
ChildA c = new ChildA(2);
c.setData(exampleData);
Ebean.save(c);
RawSql rawSql =
RawSqlBuilder.parse("select type, id, number from parent where number > 1").create();
List<Parent> partial = Ebean.find(Parent.class).setRawSql(rawSql).findList();
assertNotNull(partial.get(0).getData());
assertEquals(partial.get(0).getData().get(0).getNumber().intValue(), 0);
}
public void test() {
ResetBasicData.reset();
String sql =
" select order_id, o.status, c.id, c.name, sum(d.order_qty*d.unit_price) as totalAmount"
+ " from o_order o"
+ " join o_customer c on c.id = o.kcustomer_id "
+ " join o_order_detail d on d.order_id = o.id "
+ " group by order_id, o.status, c.id, c.name ";
RawSql rawSql =
RawSqlBuilder.parse(sql)
.columnMapping("order_id", "order.id")
.columnMapping("o.status", "order.status")
.columnMapping("c.id", "order.customer.id")
.columnMapping("c.name", "order.customer.name")
// .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount")
.create();
List<OrderAggregate> list2 =
Ebean.find(OrderAggregate.class)
.setRawSql(rawSql)
.fetch("order", new FetchConfig().query())
.fetch("order.details", new FetchConfig().query())
.where()
.gt("order.id", 2)
.having()
.gt("totalAmount", 10)
.filterMany("order.details")
.gt("unitPrice", 2d)
.findList();
output(list2);
}
// 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();
}
/**
* Search categories.
*
* @param key the key word
*/
public static List<ReportingCategory> getReportingCategoryAsListByKeywords(String key) {
String sql =
"SELECT rc.id FROM `reporting_category` rc LEFT OUTER JOIN `i18n_messages` im ON im.key = rc.name WHERE rc.deleted = 0";
sql +=
" AND (im.language = '"
+ Http.Context.current().lang().code()
+ "' OR im.language IS NULL)";
sql += " AND (rc.name LIKE \"" + key + "%\" OR im.value LIKE \"" + key + "%\") ";
RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("rc.id", "id").create();
return findReportingCategory.query().setRawSql(rawSql).findList();
}
@Test
public void RawSqlParentFetch() {
List<Data> exampleData = new ArrayList<>();
exampleData.add(new Data(0));
exampleData.add(new Data(1));
exampleData.add(new Data(2));
ChildA a = new ChildA(0);
a.setData(exampleData);
Ebean.save(a);
ChildB b = new ChildB(1);
b.setData(exampleData);
Ebean.save(b);
ChildA c = new ChildA(2);
c.setData(exampleData);
Ebean.save(c);
RawSql rawSql =
RawSqlBuilder.unparsed("select type, id from parent where number > 1")
.columnMappingIgnore("type")
.columnMapping("id", "parent.id")
.create();
List<ParentAggregate> aggregates =
Ebean.find(ParentAggregate.class)
.setRawSql(rawSql)
.fetch("parent", new FetchConfig().query())
.findList();
List<Parent> partial = new ArrayList<>();
for (ParentAggregate aggregate : aggregates) {
partial.add(aggregate.parent);
}
assertNotNull(partial.get(0).getData());
assertEquals(partial.get(0).getData().get(0).getNumber().intValue(), 0);
}
