What Java DAO Layer Is Best for Your Project

There are many ways how to connect your Java application to a database. Here, I explain which framework is best suited to the requirements of your project.

Comparable Technologies

• JDBC (Java database connectivity).
• JOOQ (Java Object Oriented Querying).
• MyBatis.
• Hibernate.
• Spring Data.

JDBC: Simple Database Querying

The simplest way to get data is to use provided by java api also know as Java Database Connectivity (JDBC). Provided api returns result set for given sql query:

xxxxxxxxxx

ResultSet rs = stmt.executeQuery("SELECT id, name FROM Employees");

while(rs.hasNext()){

 log.info("Employee id: " + rs.getInt("id") + " has name: " + rs.getString("name"));

}

Tip: In order to reduce a lot of  boilerplate code, I recommend using jdbc-template tools, like Spring JDBC template or Apache DBUtils. For example, in that request, the Spring template sends a request with parameters, deserialize data, close connection. This all happens in just one line: 

xxxxxxxxxx

User user = jdbc.qeuryForObject("SELECT * FROM USERS WHERE ID = ?", 1, User.class);

JOOQ: Java Object-Oriented Querying

JOOQ framework provides a language based on generated entities. Using this language, you can create compile-time-safe queries. JOOQ can generate dialects for many databases. Also, it clean up boilerplate code, like closing connections, etc.

xxxxxxxxxx

UserRecord user = new UserRecord();

user.setId(1);

user.setName("Peter");

Result<UserRecord> books1 = DSL.using(configuration)

   .selectFrom(USERS)

   .where(condition(user))

   .fetch();

MyBatis: Simple ORM With Querying Support

Object relation mapping or ORM provides another way to communicate with database. The idea is to create mapping between Java objects (entities) and their corresponding tables in the database. One ORM provider is the MyBatis framework. 

MyBatis is a lightweight framework that creates mapping between entites and tables using queries (not bean structures, as it's done in JPA providers, like Hibernate). So, this framework uses queries and provides ORM features. Here, you can see a short example (without a config file):

xxxxxxxxxx

// Bean mapping

public interface BlogMapper {

  @Select("SELECT * FROM blog WHERE id = #{id}")

  Blog selectBlog(int id);

}

// Fetching data

BlogMapper mapper = session.getMapper(BlogMapper.class);

Blog blog = mapper.selectBlog(101);

Hibernate and Spring Data 

Both technologies support JPA (Java Persistence API). That means that both solutions can be deployed to application servers. JPA standards require mappings between tables/columns and Java objects (entites). For example, a USERS table can be mapped to the following entity:

xxxxxxxxxx

@Data // this is not hibernate annotation, it's lombok getter/setter generator

@Entity

@Table(name = "USERS")

public class User {

    @Id

    @Column(name = "id")

    private Integer id;  

    @Column(name = "name")

    private String name;

}

Hibernate

This is the most popular ORM framework with numerous built-in features. It was first released almost 20 years ago. Hibernate also supports HQL language for custom SQL queries. 

xxxxxxxxxx

Session session = sessionFactory.openSession();

User oldUser  = (User) session.get(User.class, 1); //get user

User newUser = new User(123,"John");

session.save(developer); //add user

//HQL example

Query query = session.createQuery("FROM Users");

List users = query.list();

Spring Data or New Level of ORM Abstraction

On top of JPA entites, Spring Data provides a rich CRUD API, as well as expression query language. The key advantage of Spring data is that it requires only 2-3 lines of implementation. The generated API is based on method naming conversions. 

//Implementation, just by extending CrudRepository interface

public interface UserRepository extends CrudRepository<User, Long> {

  User findByName(String name);

  User findById(long id);

  @Query("SELECT u.ID FROM USERS u WHERE like ?1") //custom expression

  List<Integer> findByUserName(String name);

}

//It's how to use this repository:

User johnUser = userRepository.findByName("John");

User johnUser = userRepository.findById(id);

List<Integer> usersIdsWithOVPostfix = userRepository.findByUserName("%OV%");

Final Comparison

It is very difficult to give an objective comparative assessment. Below, I give a comparison, which is my personal opinion, and does not claim the absolute truth.