simple jdbc wrapper

Question

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.

Penny

"the policy of my company is to avoid external dependencies, especially spring, J2EE, etc." wow this sounds like a nightmare. Sounds like an endless loop of re-inventing the wheel – Featherstone

J2EE is an "external dependency"?? – Snapshot

If you're looking for simplistic SQL execution to object mapping, mybatis is an option. I wouldn't call it an ORM in the sense that it doesn't do object graphs like hibernate would. It simply allows you to execute sql, and pull in parameters from your input, or map columns to output object(s). – Leviathan

If anyone comes here having a similar question: you might consider this wrapper as well: sourceforge.net/p/dyndblayer/wiki/Home (I am the developer). – Proportionate

Answer 1

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.

Separatrix

Answer 2

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.

Morphia

Answer 3

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.

Arson

Answer 4

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.

Snapshot

Answer 5

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?

Leviathan

Answer 6

Jedoo

There is a wrapper class called Jedoo out there that uses database connection pooling and a singleton pattern to access it as a shared variable. It has plenty of functions to run queries fast.

Usage

To use it you should add it to your project and load its singleton in a java class:

import static com.pwwiur.util.database.Jedoo.database;

And using it is pretty easy as well:

if(database.count("users") < 100) {
    long id = database.insert("users", new Object[][]{
        {"name", "Amir"},
        {"username", "amirfo"}
    });
    
    database.setString("users", "name", "Amir Forsati", id);

    try(ResultSetHandler rsh = database.all("users")) {
         while(rsh.next()) {
             System.out.println("User ID:" + rsh.getLong("id"));
             System.out.println("User Name:" + rsh.getString("name"));
         }
    }
}

There are also some useful functions that you can find in the documentation linked above.

Lampyrid

Answer 7

Try mine library as alternative:

<dependency>
  <groupId>com.github.buckelieg</groupId>
  <artifactId>jdbc-fn</artifactId>
  <version>0.2</version>
</dependency>

More info here

Engedi

Answer 8

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

I use it for years and found it very useful (I'm the author of this library).

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.

Kelleher

You should probably mention that you're the author. – Catherine