While waiting for the project from outsource company, I've started to investigate information about Java Play 2 Framework. And it's really impressive modern tool, which can be used to create amazing web applications. It also has powerful tool to create REST services.

Outsourced application has Angular.js, Gulp, Node.js on front-end and Java Play 2 Framework on the backend. So I've decided to learn how to retrieve information from database and pass it to JavaScript frameworks, so I've decide to write an application that will get data from database and convert it into json.

I've got several applications and I think I could convert them into PhoneGap application for Android. So I need to connect Ebean ORM framework of Play 2 with existing tables. This could be achieved using special converters from database schema to JPA classes, but it quite complex task and I doubt that Ebean will connect generated class with existing table.

Setting up connection

Entity class

@Entity
@Sql
public class Excursion extends Model  
{
    @Id
    public int id;

    @Constraints.MaxLength(value = 128)
    public String title;

    public String description;

    public String image;

    @Constraints.MaxLength(value = 16)
    public String lat;
    @Constraints.MaxLength(value = 16)
    public String lng;

    public Date create_at;

    @Constraints.MaxLength(value = 128)
    public String duration;

    public Boolean is_active;

    @Constraints.MaxLength(value = 128)
    public String price;
}

This class describe fields of the table, which was genereated with Django. There are important derective @Sql from com.avaje.ebean.annotation package which indicated that this class will be based on raw sql instead of using table.

Next steps is to add SQL that will fetch all data from remote table and mapping that will fill Java class variables. It's quite unsafe, because remote table could change, but you've got nothing else left. It's using name that was genereated by Django - appname_tablename. I've seen a lot of possibilities to make this class more universal, but it's just test application and I'll let you apply changes in your code follwoing your own ideas

private static final String SQL = "select id, title, description, image, lat, lng, create_at, duration, is_active, price from excursion_excursion;";

public static RawSql getRawSql()  
{
    return RawSqlBuilder
            .parse( SQL )
            .columnMapping( "id", "id" )
            .columnMapping( "title", "title" )
            .columnMapping( "description", "description" )
            .columnMapping( "image", "image" )
            .columnMapping( "lat", "lat" )
            .columnMapping( "lng", "lng" )
            .columnMapping( "create_at", "create_at" )
            .columnMapping( "duration", "duration" )
            .columnMapping( "is_active", "is_active" )
            .columnMapping( "price", "price" )
            .create();
}

Controller code

Now you'll need to make it work. It's really simple and this is my controller's code:

public static Result index() {  
    Query<Excursion> query = Ebean.find( Excursion.class );
    List<Excursion> excursions = query.setRawSql( Excursion.getRawSql() ).findList();
    return ok(toJson(excursions));
}

If you're familiar with Ebean framework you could use having and where close in here. So I could say, that it's good way to connect exisitng table with Ebean class. All needed information you could keep in class so you'll need to look for database table changes and your app will be safe. Also, you could add new fields without affecting this code, so when you upgrade your website, your app could still be in working state.

I hope this article help to understand Ebean and how to use custom queries to connect this ORM with your existing tables.