spBee - Stored Procedure Bee

This framework allows to declare the stored procedures calls declarative instead of programmatically. Only the interfaces, the entities and few annotations need to be written. The code for the stored procedures, the necessary entity mappers and the implementations of the DAO interfaces are generated by spBee. It’s also possible to use abstract classes instead of interfaces.

An important point is that there is no magic behind this framework like JPA/Hibernate with crazy reflection, proxies and framework logic. The only logic is how to translate the annotations to the corresponding Java code. Thereby spBee has only about 1700 LoC (measured with sloc).

Everything is done at compile time. Therefore there is no performance shortcoming. No reflection. No proxies.

So some people will wonder why the hell should they use stored procedures? Then read my thoughts about that topic.

Requirements

  • Java 8
  • Spring JDBC as dependency: which is used internally
  • Spring Context for the Dependency Injection
    • Component scan needs to be configured to autowire the implementation of the generated classes
  • spBee#core as dependency
  • at.rseiler.spbee.core.SPBeeAnnotationProcessor as annotation processor

Spring is needed because spBee builds on top of org.springframework.jdbc.object.StoredProcedure and uses Spring`s Dependency Injection.

How it works (short)

Within Java 1.6 it’s possible to pre process annotations (before the code is compiled). In this pre process phase it’s also possible to generate code (Java source files or Java class files). spBee uses this mechanism to generate the boilerplate code for the stored procedures, the entity mappers and the DAO implementations. spBee generates readable Java code because this allows to set break points in the generated code. Another useful effect is that you can just grab the generated files and work with them - you don’t rely forever on spBee.

How to configure spBee

Look at spBee#demo to see the a runnable project with minimal configuration to use spBee.

Maven

In Maven you need to add spBee as dependency and define spBee`s annotation processor. That’s it.

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.2</version>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
                <annotationProcessors>
                    <annotationProcessor>
                        at.rseiler.spbee.core.SPBeeAnnotationProcessor
                    </annotationProcessor>
                </annotationProcessors>
            </configuration>
        </plugin>
    </plugins>
</build>

<dependencies>
    <dependency>
        <groupId>at.rseiler.spbee</groupId>
        <artifactId>core</artifactId>
        <version>1.0</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
</dependencies>

Spring

In Spring you need to define the dataSource and the component-scan. The component scan must include the @Dao annotated interfaces and abstract classes.

<context:component-scan base-package="your.package.name"/>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver"/>
    <property name="url" value="jdbc:hsqldb:mem:mymemdb"/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
</bean>

Simple Example

A simple example of the usage of spBee. The User class and the UserDao interface is all what is needed to call the both stored procedures below and get the User objects.

@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<User> getUsers();


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

}

// execution
List<User> users = userDao.getUsers();
User user = userDao.getUser(1);


CREATE PROCEDURE sp_get_users()
  READS SQL DATA
  DYNAMIC RESULT SETS 1
  BEGIN ATOMIC

    DECLARE resultUser CURSOR WITH RETURN FOR
      SELECT
        id,
        name
      FROM User;

    OPEN resultUser;
  END;

CREATE PROCEDURE sp_get_user(idUser INT)
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN ATOMIC

  DECLARE resultUser CURSOR WITH RETURN FOR
    SELECT
      id,
      name
    FROM User
    WHERE id = idUser;

  OPEN resultUser;
END;

Return Types

The list of all possible return types of a stored procedure method.

Void

It the stored procedure doesn’t return anything. If the stored procedure nevertheless returns something then it’s ignored.

List

If the stored procedure returns several rows then just use a generic list as return type. If no row is returned by the stored procedure then an empty list will be returned.

Entity

The generated code will automatically retrieve the element from the list and return it.

  • If no row is returned then a ObjectDoesNotExist runtime exception is thrown.
    • It’s possible that in this case null is returned if the @ReturnNull annotation is used.
    • It’s possible that in this case Optional.empty() is returned if the Entity is packed into an Optional type.
  • If more than one row is returned then a MultipleObjectsReturn runtime exception is thrown.

ResultSet

If the stored procedure returns several result sets then we need to model it in Java. Example.

  • Write a class which contains for each result set a corresponding field. These fields can be annotated with: @ReturnNull and @RowMapper
  • Create an corresponding constructor to initialize the fields.
  • Annotate the written class with @ResultSet.

Important Notes

Please note that the order of the Java fields and parameters is important and must match the corresponding stored procedure.

@StoredProcedure

The order of the parameters must be the same as the input parameters of the stored procedure.

As input parameters only basic types are allowed. See StoredProcedureGenerator#getSqlParameter. If you use arrays then they must use the class kind not the primitive kind. E.g. use Integer[] instead of int[]. See StoredProcedureGenerator#getArrayType.

@Entity

The order of the parameters of the constructor must be the same as the columns of the stored procedure.

As constructor variables only basic types are allowed. See MapperGenerator#getResultSetMethod.

@ResultSet

The order of the class fields and the parameters of the constructor must be the same as the returned result sets.

@Dao Abstract Class

  • the idea behind the abstract class is that it’s possible to mix spBee methods with own methods
    • if some special calls to the database are needed which spBee doesn’t support
    • if you don’t want to return the result of the stored procedure directly. E.g.: in spBee#demo look at AbstractUserDao#getUserWithPermissions.
  • two kinds of constructors are supported
    • default constructor (no parameters)
    • constructor with javax.sql.DataSource as parameter

Interceptor

The idea of the interceptor is to log and monitor the stored procedure calls. If a interceptor is configured then before and after the stored procedure call the interceptor is called. It’s not possible to modify the arguments of the stored procedure call. The only thing what would be possible is to throw a RuntimeException to cancel the stored procedure call.

The interceptor class must contain following method signatures:

/**
 * This method will be called before the stored procedure call.
 *
 * @param spName    the name of the stored procedure
 * @param arguments the arguments of the stored procedure
 * @return the ID object to identify the call in the after method
 **/
public static Object before(String spName, Object... arguments)

/**
 * This method will be called after the invocation of the stored procedure.
 *
 * @param id        the ID object (returned from the before method)
 * @param spName    the name of the stored procedure
 * @param arguments the arguments of the stored procedure
 */
public static void after(Object id, String spName, Object... arguments)

Then the spbee.properties file is required to be in the class path with the interceptor class configured:

interceptor=at.rseiler.spbee.demo.SpLogger

In the spbee#demo module is an example of a stored procedure logger. See at.rseiler.spbee.demo.SpLogger.

Generated Classes

All non spBee related annotations will be added to the methods of the DAO implementation class.

Thereby it’s possible to use the @Transactional annotation on the interface or the abstract class.

Type Pattern Description
DAO *.{CLASS_NAME}Impl The class name will be the name of the annotated class with Impl as postfix.
Stored Procedure *.storedprocedure.{STORED_PROCEDURE_NAME} The class name will be the name of the stored procedure (defined in the @StoredProcedure annotation). But the name will be modified a little bit:
The first character will be uppercase, underscores will be removed and the following character will be uppercase.
The package is relative to the DAO.
Mapper *.mapper.{ENTITY_NAME}{CONSTRUCTOR_NAME}Mapper The constructor name is Default if no other name is defined (with @MappingConstructor("some_constructor_name")
The package is relative to the entity.

Annotations

Name Target Description
@Dao TYPE Annotate the DAO with this annotation. Then spBee will generate the DAO implementation class.
@Entity TYPE Annotate all entities which are used as return types in the stored procedure methods of the DAO.
@MappingConstructor CONSTRUCTOR|METHOD|FIELD Annotate the constructors and give them names. These constructors will be used to create an entity instance. The names allow to use different views/subsets of an entity. Example.
@ResultSet TYPE If an stored procedure returns several result sets then a Java class will represent these result sets. These class must be annotated with @ResultSet. The fields of the class describes the different result sets and the fields can be annotated with: @ReturnNull or @RowMapper. Example.
@ReturnNull METHOD|FIELD If the stored procedure method just returns an entity (not a list of entities) and if no row is returned then the method will return null. Example.
@RowMapper METHOD|FIELD The value defines the class of the mapper which should be used to map the entities. Instead of the generated default mappers. Example.
@StoredProcedure METHOD The value defines the name of the stored procedure. Only methods annotated with @StoredProcedure will be processed by spBee.

More Examples

More examples can be found in the spBee#demo module.

Multiple @ResultSets from the stored procedure 


@ResultSet
public class UserPermissionsResultSet {

    @RowMapper(SimpleUserMapper.class)
    private final Optional<User> user;
    private final List<Permission> permissions;

    public UserPermissionsResultSet(Optional<User> user, List<Permission> permissions) {
        this.user = user;
        this.permissions = permissions;
    }

    public Optional<User> getUser() {
        if (user.isPresent()) {
            return Optional.of(new Builder(user.get()).permissions(permissions).build());
        }
        return Optional.empty();
    }

}


@Dao
public abstract class AbstractUserDao {

    @StoredProcedure("sp_get_user_with_permissions")
    public abstract UserPermissionsResultSet getUserWithPermissions(int id);

}

Use your own @RowMapper 


public class SimpleUserMapper implements RowMapper<User> {

    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new User(
                rs.getInt(1),
                "not loaded",
                new ArrayList<>());
    }

}



@Dao
public interface UserDao {

    @RowMapper(SimpleUserMapper.class)
    @StoredProcedure("sp_get_simple_users")
    List<User> getSimpleUsersWithOwnMapper();

}



@ResultSet
public class UserPermissionsResultSet {

    @RowMapper(SimpleUserMapper.class)
    private final User user;
    private final List<Permission> permissions;

    public UserPermissionsResultSet(User user, List<Permission> permissions) {
        this.user = user;
        this.permissions = permissions;
    }
}

Return null instead of an ObjectDoesNotExist runtime exception 


@Dao
public interface UserDao {

    @ReturnNull
    @StoredProcedure("sp_get_simple_user")
    User getUser();

}

@ResultSet
public class UserPermissionsResultSet {

    @ReturnNull
    private final User user;
    private final List<Permission> permissions;

    public UserPermissionsResultSet(Optional<User> user, List<Permission> permissions) {
        this.user = user;
        this.permissions = permissions;
    }
}

Return Optional.empty() instead of an ObjectDoesNotExist runtime exception 


@Dao
public interface UserDao {

    @StoredProcedure("sp_get_simple_user")
    Optional<User> getUser();

}

@ResultSet
public class UserPermissionsResultSet {

    private final Optional<User> user;
    private final List<Permission> permissions;

    public UserPermissionsResultSet(Optional<User> user, List<Permission> permissions) {
        this.user = user;
        this.permissions = permissions;
    }
}

@MappingConstructor 


@Entity
public class User {

    private final long loginId;
    private final String userName;
    private final int age;

    @MappingConstructor
    public User(long loginId, String userName, int age) {
        this.loginId = loginId;
        this.userName = userName;
        this.age = age;
    }

    @MappingConstructor("userCoreData")
    public User(long loginId, String userName) {
        this.loginId = loginId;
        this.userName = userName;
        this.age = -1;
    }

}

@Dao
public abstract class AbstractUserDao {

    @StoredProcedure("pr_get_user")
    public abstract User getUser(long loginId);

    @MappingConstructor("userCoreData")
    @StoredProcedure("pr_get_user_core_data")
    public abstract User getUserCoreData(long loginId);

}