By Mark Vital


2008-11-18 18:03:53 8 Comments

To implement data access code in our application we need some framework to wrap around jdbc (ORM is not our choice, because of scalability).

The coolest framework I used to work with is Spring-Jdbc. However, the policy of my company is to avoid external dependencies, especially spring, J2EE, etc. So we are thinking about writing own handy-made jdbc framework, with functionality similar Spring-jdbc: row mapping, error handling, supporting features of java5, but without transaction support.

Does anyone have experience of writing such jdbc wrapper framework? If anyone has experience of using other jdbc wrapper frameworks, please share your experience.

Thanks in advance.

6 comments

@Mikhail Fursov 2018-09-13 16:18:13

mJDBC: https://mjdbc.github.io/

I use it for years and found it very useful.

It is inspired by JDBI library but has no dependencies, adds transactions support, provides performance counters and allows to switch to the lowest possible SQL level in Java (old plain JDBC API) easily in case if you really need it.

@schieferstapel 2019-09-21 11:53:53

You should probably mention that you're the author.

@egaga 2012-10-11 18:30:45

The one I prefer: Dalesbred. It's MIT licensed.

A simple example of getting all rows for a custom class (Department).

List<Department> departments = db.findAll(Department.class,
    "select id, name from department");

when the custom class is defined as:

public final class Department {
    private final int id;
    private final String name;

    public Department(int id, String name) {
        this.id = id;
        this.name = name;
    }
}

Disclaimer: it's by a company I work for.

@yegor256 2012-05-17 11:46:54

Try JdbcSession from jcabi-jdbc. It's as simple as JDBC should be, for example:

String name = new JdbcSession(source)
  .sql("SELECT name FROM foo WHERE id = ?")
  .set(123)
  .select(new SingleOutcome<String>(String.class));

That's it.

@Matt 2012-05-17 12:08:49

This sounds like a very short sighted decision. Consider the cost of developing/maintaining such a framework, especially when you can get it, and it's source code for free. Not only do you not have to do the development yourself, you can modify it at will if need be.

That being said, what you really need to duplicate is the notion of JdbcTemplate and it's callbacks (PreparedStatementCreator, PreparedStatementCallback), as well and RowMapper/RowCallbackHandler. It shouldn't be overcomplicated to write something like this (especially considering you don't have to do transaction management).

Howver, as i've said, why write it when you can get it for free and modify the source code as you see fit?

@jdigital 2008-11-18 19:13:17

We wrote our own wrapper. This topic is worthy of a paper but I doubt I'll ever have time to write it, so here are some key points:

  • we embraced sql and made no attempt to hide it. the only tweak was to add support for named parameters. parameters are important because we do not encourage the use of on-the-fly sql (for security reasons) and we always use PreparedStatements.

  • for connection management, we used Apache DBCP. This was convenient at the time but it's unclear how much of this is needed with modern JDBC implementations (the docs on this stuff is lacking). DBCP also pools PreparedStatements.

  • we didn't bother with row mapping. instead (for queries) we used something similar to the Apache dbutil's ResultSetHandler, which allows you to "feed" the result set into a method which can then dump the information wherever you'd like it. This is more flexible, and in fact it wouldn't be hard to implement a ResultSetHandler for row mapping. for inserts/updates we created a generic record class (basically a hashmap with some extra bells and whistles). the biggest problem with row mapping (for us) is that you're stuck as soon as you do an "interesting" query because you may have fields that map to different classes; because you may have a hierarchical class structure but a flat result set; or because the mapping is complex and data dependent.

  • we built in error logging. for exception handling: on a query we trap and log, but for an update we trap, log, and rethrow an unchecked exceptions.

  • we provided transaction support using a wrapper approach. the caller provides the code that performs transaction, and we make sure that the transaction is properly managed, with no chance of forgetting to finish the transaction and with rollback and error handling built-in.

  • later on, we added a very simplistic relationship scheme that allows a single update/insert to apply to a record and all its dependencies. to keep things simple, we did not use this on queries, and we specifically decided not to support this with deletes because it is more reliable to use cascaded deletes.

This wrapper has been successfully used in two projects to date. It is, of course, lightweight, but these days everyone says their code is lightweight. More importantly, it increases programmer productivity, decreases the number of bugs (and makes problems easier to track down), and it's relatively easy to trace through if need be because we don't believe in adding lots of layers just to provide beautiful architecture.

@jlpp 2008-11-18 18:28:50

Spring-JDBC is fantastic. Consider that for an open source project like Spring the down side of external dependency is minimized. You can adopt the most stable version of Spring that satisfies your JDBC abstraction requirements and you know that you'll always be able to modify the source code yourselves if you ever run into an issue -- without depending on an external party. You can also examine the implementation for any security concerns that your organization might have with code written by an external party.

Related Questions

Sponsored Content

21 Answered Questions

[SOLVED] Find Oracle JDBC driver in Maven repository

8 Answered Questions

[SOLVED] What is the difference between canonical name, simple name and class name in Java Class?

  • 2013-03-04 13:45:28
  • Mohamed Taher Alrefaie
  • 221787 View
  • 917 Score
  • 8 Answer
  • Tags:   java

12 Answered Questions

[SOLVED] How to get the insert ID in JDBC?

11 Answered Questions

16 Answered Questions

5 Answered Questions

[SOLVED] Spring DAO vs Spring ORM vs Spring JDBC

14 Answered Questions

[SOLVED] To prevent a memory leak, the JDBC Driver has been forcibly unregistered

  • 2010-07-23 16:45:16
  • mona
  • 343501 View
  • 316 Score
  • 14 Answer
  • Tags:   java tomcat jdbc

3 Answered Questions

[SOLVED] Spring Transaction Synchronization of JDBC and JMS

0 Answered Questions

provide connection to jdbc template

  • 2017-10-06 17:10:12
  • user3495734
  • 48 View
  • 1 Score
  • 0 Answer
  • Tags:   java spring jdbc

2 Answered Questions

Sponsored Content