Fanbatis

ORM was a simple problem to solve. Until the likes of hibernate complicated it.

Mybatis nicely fitted the bill of being a pefectly-simple datamappter but is more verbose than necessary due to Java itself. With Fantom's syntax sugars and Dsls we can simplify everything and try to hit that sweet spot between directly using SQL, datamappers and ORMs.

Fanbatis is the default data-access library that comes with tales. However fanbatis can be used outside of it

The goal is to provide syntax sugar for commonly used things..

//Get one blog from database
Blog blog := Db.one(Blog{id = 1})

.. and expose the full power of sql for everything else

User getUser(Int id){S<| 
    select u.userid, u.name from User u,Company c 
    where u.id = #{id} and u.company_id  = c.id and c.is_deleted = 0
|>}

If you're using tales, there is no need to install fanbatis. Tales comes with fanbatis bundled up

If you're using fanbatis outside of tales, get it from tales repo

fanr install -r http://repo.talesframework.org:9000 fanbatis

If you are using fanbatis with tales, open Settings.fan and add a DbSetting like this:

add(DbSetting{
  hostString = "jdbc:mysql://localhost:3306/host"
  driver = "com.mysql.jdbc.Driver"
  username = "kaushik"
  password = "password"
})

If you are running fanbatis outside of tales, configure the Batis object like this

using fanbatis
class Main{
  static const Str:Str props :=  Str:Str[
                                 "url":"jdbc:h2:mem:test",
                                 "driver":"org.h2.Driver",
                                 "username": "sa",
                                 "password":""]

  static Void setup(){
    batis := Batis(true) //true if dev mode
    batis.configure(props)
    
    Blog blog := Db.one(Blog{id = 1})
    ...
  }
}

Assume a table like this:

create table IF NOT EXISTS blog
(id integer auto_increment primary key, 
subject varchar(200), detail varchar(2000));

And a class like this:

class Blog {
  @Primary
  @Autogen
  Int? id
  Str? subject
  Str? detail
}

Here are a few examples of doing short-hand queries

Fetch one blog by id

Blog blog := Db.one(Blog{id = 1})

Fetch all blogs

Blog[] blogs := Db.list(Blog{})

Fetch all blogs by subject

Blog[] blogs := Db.list(Blog{subject="My First blog"})

Fetch one blog by name and authorId

Blog blog := Db.one(Blog{name="My Blog"; authorId = 1})

Create a blog

Blog blog := Db.create(Blog{subject = "test"; it.detail="test"})
echo(blog.id) 
//Note: since id is annotated with autogen,
// id will be auto populated

Fetching and updating a blog

Blog blog := Db.one(Blog{id = 1})
blog.authorId = 2
Db.save(blog)

Deleting a blog

Blog blog := Db.one(Blog{id = 1})
Db.remove(blog)

Fetching a list of blogs with custom where clause

Blog[] blogs := Db.list(Blog#, "subject = #{param.subject}", ["subject", "My Blog"])

Ad hoc querying for one value

Row row := Db.queryOne("select 1*2 val from dual");
echo(row->val)

Ad hoc querying for one multiple values

Row[] rows := Db.querylist("select id, name from employees where name like '#{param.name}%'",
                                ["name", "A"]);
rows.each{  
  echo(row->id)
  echo(row->name)
}

You can make use of the following annotations when querying with the Db class

@Primary - Mark a column as the primary column, required when updating a row
@Autogen - Mark a column as having autogenerated value after insert
@Table{name = "xx"} - By default table name is assumed to be the class name, 
                      to change this use this annotation on a class
@Column{name="xx"} - By default column name is assumed to be the field name, 
                     to change this use this annotation on a field

SqlMaps are the "holy-grail" of fanbatis. SqlMap lets you map query results to objects in the simples-possible way.

Here's a sql map to select one blog by id

class BlogSql {
  static Blog findById(Int id){
    S<|
         select * from blog where id = #{id}
    |>
  }
}

//Getting the blog
blog := BlogSql.findById(1)

Take a look at how you can wrap a sql in a method. That's all there to it. Fanbatis will execute the query, map the result to return type and return you the result

There are 4 DSLs S<||> (for select), I<||> (for insert), U<||> (for update), D<||> (for delete)

You can use any method parameter or any of it's own property for example

static Void saveBlog(Blog blog){
  U<|
       update blog set subject = #{blog.subject} where id = #{blog.id}
  |>
}

Here's a simple insert

static Void create(Blog blog){
  I<|
    insert into tblog(subject, post) values(#{blog.subject}, #{blog.post})
  |>
}

When inserting you can fetch back autogenerated values using @UseGeneratedKeys facet, example

@UseGeneratedKeys{key = "blog.id"}
static Void create(Blog blog){
  I<|
    insert into tblog(subject, post) values(#{blog.subject}, #{blog.post})
  |>
}

You can also fetch back auto generated values by querying using the @SelectKey facet

@SelectKey{keyProperty="blog.id"; order="AFTER"; type=Int#; query = "SELECT IDENTITY()"}
static Void insertBlogWithSelect(Blog blog){
  I<|
      insert into blog(subject, post)
        values(#{blog.subject} , #{blog.post})
  |>
}

Sometimes you will want to do more complex mapping when trying to fetch data from multiple tables using joins. Here's a example. You will want to look at the mybatis documentation see more about result maps.

In this example we will solve the n+1 select problem by fetching all the blogs along with the author

Assume the blog class with Author

class Blog {
  Int? id
  Str? subject
  Str? detail
  Author? author
}

.. and the author class

class Author{
  Int? id
  Str? name
  DateTime? birthDate
}

Here's the sql map to fetch all blogs along with their authors

using fanbatis
class BlogSql{
  @MapWith{name = "blogMap"}
  Blog[] selectBlogsWithAuthor(){
    S<|
          select b.id blog_id, b.subject blog_subject,
          b.detail blog_detail,
          a.id author_id, a.name author_name,  
          a.birthDate as birth_date
          from
          blog b 
          left outer join
          Author a on b.author_id = a.id
     |>
  }


  static ResultMap blogMap(){ 
    ResultMap{
      it.type = Blog#
      id->id = "blog_id"
      result->subject = "blog_subject"
      result->detail = "blog_detail"
      association->author = Association{resultMap="authorMap"}
    }
  } 

  static ResultMap authorMap(){
    ResultMap{
      it.type = Author#
      id->id = "author_id"
      result->name = "author_name"
      result->birthDate = "birth_date"
    }
  }
}

Note how resultmaps are declared as static methods in the same class. Mention the resultmap's name using @MapWith annotation.

Fanbatis - Easy data access for Fantom