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 void test() {
ResetBasicData.reset();
Query<Order> query =
Ebean.find(Order.class)
.where()
.eq("status", Order.Status.NEW)
.ge("id", 1)
.order()
.desc("id");
int rc0 = query.findRowCount();
List<Object> ids = query.findIds();
Assert.assertEquals(rc0, ids.size());
List<Order> list0 = query.findList();
Assert.assertEquals(rc0, list0.size());
int rc1 = query.findRowCount();
Assert.assertEquals(rc0, rc1);
List<Object> ids1 = query.findIds();
Assert.assertEquals(rc0, ids1.size());
List<Order> list1 = query.findList();
Assert.assertEquals(rc0, list1.size());
int idGt = 5;
if (ids1.size() > 0) {
Object id = ids.get(0);
idGt = Integer.valueOf("" + id);
}
// should still hit query plan cache
Query<Order> query2 =
Ebean.find(Order.class)
.where()
.eq("status", Order.Status.NEW)
.ge("id", idGt)
.order()
.desc("id");
int rc2 = query2.findRowCount();
System.out.println("Expection Not same " + rc0 + " != " + rc2);
Assert.assertNotSame(rc0, rc2);
List<Object> ids2 = query2.findIds();
Assert.assertEquals(rc2, ids2.size());
List<Order> list2 = query2.findList();
Assert.assertEquals(rc2, list2.size());
}
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"));
}
}
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());
}
}
}
}
@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 "));
}
@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 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
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();
}
}
@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"));
}
// 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();
}
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);
}
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;
}
public static List<Official> findAll() {
Query<Official> query = Ebean.find(Official.class);
List<Official> officials = query.findList();
return officials;
}
@Test
public void test() {
/*
* Original conversation:
* https://groups.google.com/forum/?fromgroups=#!topic/ebean/uuvi1btdCDQ%5B1-25-false%5D
*
* This test exposes what may be a general problem with columns required by the order by phrase being omitted from the select.
* I'm not sure this exposes all causes of the problem.
*/
MUserType ut = new MUserType("md");
Ebean.save(ut);
MUser user1 = new MUser("one");
user1.setUserType(ut);
Ebean.save(user1);
MUser user2 = new MUser("two");
user2.setUserType(ut);
Ebean.save(user2);
MRole roleA = new MRole("A");
Ebean.save(roleA);
MRole roleB = new MRole("B");
Ebean.save(roleB);
user1.addRole(roleA);
Ebean.save(user1);
user2.addRole(roleB);
Ebean.save(user2);
Query<MUser> query =
Ebean.find(MUser.class)
.fetch("userType", "name")
.where()
.eq("roles.roleName", "A")
.orderBy("userType.name, userName");
List<MUser> list = query.findList();
// select distinct t0.userid c0, t0.user_name c1, t1.id c2, t1.name c3
// from muser t0
// left outer join muser_type t1 on t1.id = t0.user_type_id
// join mrole_muser u1z_ on u1z_.muser_userid = t0.userid
// join mrole u1 on u1.roleid = u1z_.mrole_roleid
// where u1.role_name = ?
// order by t1.name, t0.user_name; --bind(A)
Assert.assertEquals(1, list.size());
Assert.assertEquals(user1, list.get(0));
String generatedSql = query.getGeneratedSql();
Assert.assertTrue(generatedSql.contains("select distinct t0.userid")); // using distinct
Assert.assertTrue(generatedSql.contains("order by t1.name,")); // name in order by
Assert.assertTrue(generatedSql.contains("t1.name c")); // name in select
// repeat with slight variation, not sure this really produces a different execution path
// this problem also manifests when autofetch eliminates properties from the select that aren't
// used in the objects
// still need them to be present for purpose of order by
// so here I'm "simulating" a scenario where autofetch has dropped userType.name
query =
Ebean.find(MUser.class)
.setAutofetch(false)
.select("userName")
.fetch("userType", "name")
.where()
.eq("roles.roleName", "A")
.orderBy("userType.name");
list = query.findList();
Assert.assertEquals(1, list.size());
Assert.assertEquals(user1, list.get(0));
// select distinct t0.userid c0, t0.user_name c1, t1.id c2, t1.name c3
// from muser t0
// left outer join muser_type t1 on t1.id = t0.user_type_id
// join mrole_muser u1z_ on u1z_.muser_userid = t0.userid
// join mrole u1 on u1.roleid = u1z_.mrole_roleid
// where u1.role_name = ?
// order by t1.name; --bind(A)
generatedSql = query.getGeneratedSql();
Assert.assertTrue(generatedSql.contains("select distinct t0.userid")); // using distinct
Assert.assertTrue(generatedSql.contains("order by t1.name")); // name in order by
Assert.assertTrue(generatedSql.contains("t1.name c")); // name in select
}
