Design objectives

• Lightweight, fast and low-overhead. Only a handful of classes, no XML, annotations, reflection
• This is not full-blown ORM. No relationship handling, lazy loading, dirty checking, caching
• CRUD implemented in seconds
• For small applications where JPA is an overkill
• Use when simplicity is needed or when future migration e.g. to JPA is considered
• Minimalistic support for database dialect differences (e.g. transparent paging of results)

Features

• Mapping to/from domain objects through RowMapper abstraction
• Generated and user-defined primary keys
• Extracting generated key
• Compound (multi-column) primary keys
• Immutable domain objects
• Paging (requesting subset of results)
• Sorting over several columns (database agnostic)
• Optional support for many-to-one relationships
• Supported databases (continuously tested):
  • MySQL
  • PostgreSQL
  • H2
  • HSQLDB
  • Derby
  • ...and most likely most of the others
• Easily extendable to other database dialects via SqlGenerator class.
• Easy retrieval of records by ID

API

public interface PagingAndSortingRepository<T, ID extends Serializable> extends CrudRepository<T, ID> {
             T  save(T entity);
    Iterable<T> save(Iterable<? extends T> entities);
             T  findOne(ID id);
        boolean exists(ID id);
    Iterable<T> findAll();
           long count();
           void delete(ID id);
           void delete(T entity);
           void delete(Iterable<? extends T> entities);
           void deleteAll();
    Iterable<T> findAll(Sort sort);
        Page<T> findAll(Pageable pageable);
}

Page<User> page = userRepository.findAll(
    new PageRequest(
        5, 10, 
        new Sort(
            new Order(DESC, "reputation"), 
            new Order(ASC, "user_name")
        )
    )
);

SELECT *
FROM USERS
ORDER BY reputation DESC, user_name ASC
LIMIT 50 OFFSET 10

SELECT * FROM (
    SELECT ROW_NUMBER() OVER () AS ROW_NUM, t.*
    FROM (
        SELECT * 
        FROM USERS 
        ORDER BY reputation DESC, user_name ASC
        ) AS t
    ) AS a 
WHERE ROW_NUM BETWEEN 51 AND 60

Reasons to use

• You consider migration to JPA or even some NoSQL database in the future.
  
  Since your code will rely only on methods defined in PagingAndSortingRepository and CrudRepository from Spring Data Commons umbrella project you are free to switch from JdbcRepository implementation (from this project) to: JpaRepository, MongoRepository, GemfireRepository or GraphRepository. They all implement the same common API. Of course don't expect that switching from JDBC to JPA or MongoDB will be as simple as switching imported JAR dependencies - but at least you minimize the impact by using same DAO API.
  
  
• You need a fast, simple JDBC wrapper library. JPA or even MyBatis is an overkill
  
  
• You want to have full control over generated SQL if needed
  
  
• You want to work with objects, but don't need lazy loading, relationship handling, multi-level caching, dirty checking... You need CRUD and not much more
  
  
• You want to by DRY
  
  
• You are already using Spring or maybe even JdbcTemplate, but still feel like there is too much manual work
  
  
• You have very few database tables
  
  

Getting started

Prerequisites

<dependency>
    <groupId>com.blogspot.nurkiewicz</groupId>
    <artifactId>jdbcrepository</artifactId>
    <version>0.1</version>
</dependency>

$ git clone git://github.com/nurkiewicz/spring-data-jdbc-repository.git
$ git checkout 0.1
$ mvn javadoc:jar source:jar install

@EnableTransactionManagement
@Configuration
public class MinimalConfig {

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public DataSource dataSource() {
        MysqlConnectionPoolDataSource ds = new MysqlConnectionPoolDataSource();
        ds.setUser("user");
        ds.setPassword("secret");
        ds.setDatabaseName("db_name");
        return ds;
    }

}

Entity with auto-generated key

CREATE TABLE COMMENTS (
    id INT AUTO_INCREMENT,
    user_name varchar(256),
    contents varchar(1000),
    created_time TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
);

public class Comment implements Persistable<Integer> {

    private Integer id;
    private String userName;
    private String contents;
    private Date createdTime;

    @Override
    public Integer getId() {
        return id;
    }

    @Override
    public boolean isNew() {
        return id == null;
    }

    //getters/setters/constructors/...
}

@Repository
public class CommentRepository extends JdbcRepository<Comment, Integer> {

    public CommentRepository() {
        super(ROW_MAPPER, ROW_UNMAPPER, "COMMENTS");
    }

    public static final RowMapper<Comment> ROW_MAPPER = //see below

    private static final RowUnmapper<Comment> ROW_UNMAPPER = //see below

    @Override
    protected Comment postCreate(Comment entity, Number generatedId) {
        entity.setId(generatedId.intValue());
        return entity;
    }
}

    public static final RowMapper<Comment> ROW_MAPPER = new RowMapper<Comment>() {

        @Override
        public Comment mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Comment(
                    rs.getInt("id"),
                    rs.getString("user_name"),
                    rs.getString("contents"),
                    rs.getTimestamp("created_time")
            );
        }
    };

    private static final RowUnmapper<Comment> ROW_UNMAPPER = new RowUnmapper<Comment>() {
        @Override
        public Map<String, Object> mapColumns(Comment comment) {
            Map<String, Object> mapping = new LinkedHashMap<String, Object>();
            mapping.put("id", comment.getId());
            mapping.put("user_name", comment.getUserName());
            mapping.put("contents", comment.getContents());
            mapping.put("created_time", new java.sql.Timestamp(comment.getCreatedTime().getTime()));
            return mapping;
        }
    };

By now you might have a feeling that, compared to JPA or Hibernate, there is quite a lot of manual work. However various JPA implementations and other ORM frameworks are notoriously known for introducing significant overhead and manifesting some learning curve. This tiny library intentionally leaves some responsibilities to the user in order to avoid complex mappings, reflection, annotations... all the implicitness that is not always desired.

This project is not intending to replace mature and stable ORM frameworks. Instead it tries to fill in a niche between raw JDBC and ORM where simplicity and low overhead are key features.

Entity with manually assigned key

CREATE TABLE USERS (
    user_name varchar(255),
    date_of_birth TIMESTAMP NOT NULL,
    enabled BIT(1) NOT NULL,
    PRIMARY KEY (user_name)
);

public class User implements Persistable<String> {

    private transient boolean persisted;

    private String userName;
    private Date dateOfBirth;
    private boolean enabled;

    @Override
    public String getId() {
        return userName;
    }

    @Override
    public boolean isNew() {
        return !persisted;
    }

    public User withPersisted(boolean persisted) {
        this.persisted = persisted;
        return this;
    }

    //getters/setters/constructors/...

}

@Repository
public class UserRepository extends JdbcRepository<User, String> {

    public UserRepository() {
        super(ROW_MAPPER, ROW_UNMAPPER, "USERS", "user_name");
    }

    public static final RowMapper<User> ROW_MAPPER = //...

    public static final RowUnmapper<User> ROW_UNMAPPER = //...

    @Override
    protected User postUpdate(User entity) {
        return entity.withPersisted(true);
    }

    @Override
    protected User postCreate(User entity, Number generatedId) {
        return entity.withPersisted(true);
    }
}

Compound primary key

CREATE TABLE BOARDING_PASS (
    flight_no VARCHAR(8) NOT NULL,
    seq_no INT NOT NULL,
    passenger VARCHAR(1000),
    seat CHAR(3),
    PRIMARY KEY (flight_no, seq_no)
);

public class BoardingPass implements Persistable<Object[]> {

    private transient boolean persisted;

    private String flightNo;
    private int seqNo;
    private String passenger;
    private String seat;

    @Override
    public Object[] getId() {
        return pk(flightNo, seqNo);
    }

    @Override
    public boolean isNew() {
        return !persisted;
    }

    //getters/setters/constructors/...

}

public class BoardingPassRepository extends JdbcRepository<BoardingPass, Object[]> {
    public BoardingPassRepository() {
        this("BOARDING_PASS");
    }

    public BoardingPassRepository(String tableName) {
        super(MAPPER, UNMAPPER, new TableDescription(tableName, null, "flight_no", "seq_no")
        );
    }

    public static final RowMapper<BoardingPass> ROW_MAPPER = //...

    public static final RowUnmapper<BoardingPass> UNMAPPER = //...

}

BoardingPass pass = repository.findOne(new Object[] {"FOO-1022", 42});

import static com.blogspot.nurkiewicz.jdbcrepository.JdbcRepository.pk;
//...

BoardingPass foundFlight = repository.findOne(pk("FOO-1022", 42));

Transactions

Caching

Contributions

Testing

Building

$ mvn install

Results :

Tests run: 265, Failures: 0, Errors: 0, Skipped: 106

Design

License