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:
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
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.
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.
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!
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!
