CODEWITHSUNDEEP
javajdbcdata-accessspring-jdbc
Mark Vital
Mark Vital

Reputation: 950

simple jdbc wrapper

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.

Upvotes: 14

Views: 17550

Answers (9)

nik0x1
nik0x1

Reputation: 1459

I'm currently working on Superb JDBC library that might be useful. It is an object-oriented wrapper of JDBC API that simplifies work with relational databases.

Here's how it works:

  1. Add the dependency into your pom.xml
<dependency>
   <groupId>com.nmalygin</groupId>
   <artifactId>superb-jdbc</artifactId>
   <version>${version}</version>
</dependency>
  1. Create a rdbms object
Rdbms rdbms = new RealRdbms(dataSource);
  1. Use the rdbms object
rdbms
  .change("INSERT INTO books(title) VALUES ('Clean Code')")
  .apply();

The library uses clear DBMS abstractions, has zero dependencies, uses MIT License.

Upvotes: 0

Mikhail Fursov
Mikhail Fursov

Reputation: 307

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.

Upvotes: -1

Amir Forsati
Amir Forsati

Reputation: 5970

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.

Upvotes: 0

Anatoly
Anatoly

Reputation: 89

Try mine library as alternative:

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

More info here

Upvotes: 0

egaga
egaga

Reputation: 21732

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.

Upvotes: 3

yegor256
yegor256

Reputation: 105193

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.

Upvotes: 1

Matt
Matt

Reputation: 11805

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?

Upvotes: 1

jdigital
jdigital

Reputation: 12296

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.

Upvotes: 14

jlpp
jlpp

Reputation: 1584

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.

Upvotes: 5

Related Questions