Java play framework 2.0.4 ebean query

0
votes

I am wondering how to query the database using the model in play 2.0 with a query like the one I listed below. I didn't see an option to pass in direct sql into the play framework 2.0.

I am trying to get a list of the expenses from a particular month. 

SELECT * FROM Expensesdb.expense
WHERE month(expense.purchase_date) = 01

The option I see is to query for all the expenses and then parse each one for the month they are listed using the Date object.

I think there should be an efficient way, I can't seem to find a way to do this using ebean with Java play framework 2.0 to perform this query.

Update

Thanks Nico, I tried the exact code you have, with DateTime and I tried to use the code below, it doesn't return any Expenses. Am I doing something wrong?

    Calendar calendar = Calendar.getInstance();
    calendar.set(2012, 0, 01);

    Date startDate = calendar.getTime();
    calendar.set(2012, 0, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));

    Date endDate = calendar.getTime();
    List<Expense> expenses = find.where().between("purchaseDate", startDate, endDate).findList();
    return expenses;
2
javaplayframeworkplayframework-2.0ebean

2 Answers

4
votes

I see two options:

1 - Using Ebean mapping

The idea is to search the expenses between the beginning and the end of the month, something like: 

Datetime firstDayOfMonth= new Datetime().withDayOfMonth(1);
Datetime lastDayOfMonth = new Datetime().dayOfMonth().withMaximumValue();
return finder.where()
    .between("purchaseDate", firstDayOfMonth, lastDayOfMonth).findList();

2 - Using RawSQL

For this, please take a look at the Ebean documentation.

The main drawback of raw sql is that this code will not be portable for different SQL servers (if you don't plan to use several db engine, it will not matter).

1
votes

+1 for @nico_ekito

On the other hand, while you are suggesting getting all rows from DB and then parsing them in the loop, I'd rather suggest to parse them... while creating and store in format easier to search and index. Just create additional column(s) in your DB, and override save() and/or update(Object o) methods in your model, to make sure, that every change will set the field, ie use String purchasePeriod for storing string like 2012-11;

you can find then:

# in November of ANY year
SELECT * FROM table WHERE purchase_period LIKE '%-11';

# in whole 2012 
SELECT * FROM table WHERE purchase_period LIKE '2012-%';

# in December 2012
SELECT * FROM table WHERE purchase_period LIKE '2012-12';

alternatively you can divide it into two Integer fields: purchaseYear, purchaseMonth.

For the first scenario the overriden save() method in the Expense model can look ie like this:

public void save() {
    this.purchaseDate = new Date();
    this.purchasePeriod = new SimpleDateFormat("yyyy-MM").format(this.purchaseDate);
    super.save();
}