spBee - Stored Procedure Bee: a database framework

The Background Story
At work we use only stored procedures to access the database. First I was quite skeptical. Why don't we use JAP/Hibernate? But there are some reasons for that:

• Performance
• It's very easy. Three other projects at work are using Hibernate. All of them had to fight with performance issues. It's quite complicate to make Hibernate work efficiently.
• It's the interface to the database. As a developer I don't care about the database tables. I just want the data I am interested in and the stored procedures give me the data in a very easy way.

There are more reasons for and against them. But actually I don't want to discuss this in greater detail.

The Problem

One thing that always bothered me with our stored procedure implementation was the necessity to map the Resultset manually. It looks like this but with more fields/columns:

Pojo pojo = new Pojo();
pojo.setId(theResultSet.getInt(1));
pojo.setName(theResultSet.getString(2));
pojo.setEnum(SomeEnum.fromId(theResultSet.getInt(3)));
return pojo;

I wanted a more elegant solution. So I spent some of my free time to figure out a solution. The solution I wanted should be very easy to use and the implementation should be easy, too. Nowadays many things rely on in-memory-code-generation and reflection. But the problem with that is that it's impossible to debug. Furthermore it's not easy to implement.

The Solution: spBee - how it works
That is why I decided to generate Java code. It's easy to create Java code with JCodeModel and it gives you the opportunity to debug the code. The code is generated based on the structure of the Java classes and on some Java annotations. To make the integration smooth everything is done in the Pre-Compile-Phase. Since Java 1.6 it's possible to hook up an annotation processor before the code is compiled. In this step it's possible to generate Java code. The generated Java code will be compiled to byte code together with the rest of the code. With Maven it's very easy to configure the annotation processor. So every time you build the project the boring to write code will be generated automatically.

The current implementation depends heavily on Spring. The first dependency to Spring is that the StoredProcedure from Spring is used - because of that it saved me to write more code. The second dependency to Spring is that it builds on top of the Dependency Injection of Spring. This has the benefit that the source code is always clean. Because in the code only the interfaces are used. The implementation of the interfaces, which will be generated by spBee, will be wired up correctly by Spring at runtime.

Example

@Entity 
public class User {  
  private int id;
  private String name;

  private User() {}

  @MappingConstructor
  public User(int id, String name) {
     this(id, name);   
  }

  public int getId() { return id; }

  public String getName() { return name; } 
}

@Dao 
public interface UserDao {  
  // list of entities  
  @StoredProcedure("sp_get_users")  
  List getUsers();

  // single entity  
  @StoredProcedure("sp_get_user")
  public User getUser(int id); 
}

// execution
@Autowired UserDao userDao; 
List users = userDao.getUsers(); 
User user = userDao.getUser(1)

Explanation of the example
The UserDao interface just groups together all stored procedures which are related to the user. The interface has two methods which call two different stored procedures. The name of the stored procedure is declared within the @StoredProcedure annotation. The getUsers() method will just call the sp_ger_users stored procedures without any parameters. Then the result is mapped into a list of User entities. How does spBee know how to map the result? It just reads the @MappingConstrutor, looks at the types of the parameters and generates the corresponding code. It's possible to have several @MappingConstructors - you just have to give them names. Actually, that's it! The getUser() method will call the stored procedure with one int value. The list is automatically unpacked into a single element. If there is not exactly one row returned from the database then an exception is thrown. But you can configure it so that it returns null. Or you could wrap it into an Optional type.

There are more features like multiple result sets and so on. If you are interested then please take a look at the spBee documentation.

Conclusion
For me it was fun to write this framework. I believe that the framework is stable and others can benefit from it, too. Furthermore take a look at the SPBeeAnnotationProcessor. It's a very powerful but quite unknown feature of Java. In combination with Dependency Injection it integrates very well. That's because nowhere in your code you are using the generated code - no errors occur, if the code wasn't generated yet. Just one warning: it's a little bit troublesome to write an annotation processor and you can't use reflection at this point. You need to read the code with the help of the AST API.
Nevertheless it's great fun and it's awesome if it works in the end!