This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes One code base many DBs? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "One code base many DBs?" Watch "One code base many DBs?" New topic
Author

One code base many DBs?

Bacon
Ranch Hand

Joined: Jul 14, 2004
Posts: 305
As a relative new-comer to JDBC/Java and an old-timer to app/dev.

What is considered "best practice" for writing portable code for DB access.

I'm working through a book now that deals with JDBC, SQL and record level access. The record level access is proprietary because it uses the AS400 classes specially written for that function. Obviously that is not portable to other DBs and platforms.

JDBC, SQL are nearly ubiquitous. Connecting to a DB, in my limited understanding, is a specialized activity. If one uses JDBC and SQL to process DB functions, is it possible to get by with special classes that are designed for connecting to specific DBs and have a common code base for the rest of the functions? My guess is "yes" and you could use some sort of environment variable to tell the software what type of platform and DB it is connecting to.

My thought is to write software that can be installed and run over the common DBs and platforms available.

Thanks, in advance, for your input.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


My thought is to write software that can be installed and run over the common DBs and platforms available

A far better idea is to use someone else's. You should look at Entity Beans*, Hibernate, OJB, Castor etc. A google for "Java" + "Object Relational Mapping" should turn up many more. Also a quick glance through Javaranch's Object Relational Mapping forum will give you some more ideas.



*(but don't look too hard, they are rubbish. But have a look for comparison's sake)
[ July 29, 2004: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
One option you might want to consider is using Hibernate to map your Java objects to your relational database. Using Hibernate, your application doesn't have any DB specific SQL -- all that is taken care in Hibernate's configuration file where you specify the database's dialect (i.e. which class implements the mapping between Hibernate's query language and the SQL understood by the database in question -- Hibernate supports all major databases).


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Originally posted by Paul Sturrock:
*(but don't look too hard, they are rubbish. But have a look for comparison's sake)

Well, let's not be too harsh on EntityBeans. They do have their sides, including the fact that EJB is a standard and available on all J2EE application servers.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

True Lasse. I just have too many bad memories of the hell involved in trying to use them in a production system. Not happy times.

Of course being avaliable on all application servers is a down side too, Hibernate et al. don't need an application server, nor do they require EJB to access the ORM layer...
[ July 29, 2004: Message edited by: Paul Sturrock ]
Bacon
Ranch Hand

Joined: Jul 14, 2004
Posts: 305
Thanks for the tips, I will check them out.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Ray Marsh:
What is considered "best practice" for writing portable code for DB access.
I don't think there is a single "best practice". Certainly in places like TheServerSide the proponents and of Entity Beans and JDO regularly clash with each other in anything ranging from erudite and esoteric discussion to gutter level name calling. I'd like to pick up on some of the suggestions made in this thread and give you my particular take.

Let's start by oversimplifying the issue and plot "best practice" depending on business logic complexity on one hand, and distribution needs on the other. (I'll say a little bit more about those in a minute).Simple business logic means that you're mainly interested in shovelling data in and out of the database; there is no complex behaviour to implement. A good example from my own experience is the F.A. Premier League stats console, where the Java server just renders fact data out of an Oracle data warehouse. Complex business logic is just the opposite; the data models a complex problem and your business functions are implemented using decidedly non-trivial operations on the data. In such cases, best practice is to represent the problem using a Java objects (the "object model"), and use some Object-Relational mapper such as JDO or Entity EJBs to do the hard work of getting your Java objects in and out of the database. I should add that entity beans support only a very limited form of O/R mapping, which is one of the things people have against them. Most (web) applications, as opposed to simple websites, are complex enough to warrant an object model.

The other axis is colocated vs distributed. Colocated means that the entire application can run on one and the same JVM. You don't need to be particularly scalable or available, or if you do, simple load balancing is enough to get where you need to be. Distributed means that your system runs on multiple servers, with various parts of the system running on different servers. Your business logic needs to be independently scalable from your front end, or you have a service oriented architecture, there are strict availability or security needs...

You see that I left one of the quadrants blank. I haven't yet encountered any simple system with distribution needs.

A brief word about the technologies in the diagram:
  • By JDBC I do not mean you as a developer working against the raw JDBC interface. That is in most cases total madness. While JDBC seems simple, it is surprisingly hard to do it right: you need to churn out ridiculous amounts of code for a simple SQL statement, and it's very easy to mess up. (If you disagree with this, you aren't doing it right; believe me). There are excellent products out there which help JDBC development, such as the Spring JDBC templates (OSS), iBatis (commercial) and of course SQLJ (basically dead IMHO). While such tools are portable across a wide variety of databases, it is still up to you to ensure that the SQL you write is portable. I have to say that while portability of application code can sometimes give managers a warm fuzzy feeling, it is very rare that applications actually get ported to a totally different database platform so it is questionable whether you should expend effort and take performance hits in order to keep your SQL portable.
  • With JDO I of course mean products implementing the formal JDO standard, but also Hibernate and Castor (of the last two, I'd recommend Hibernate any day) which work among similar lines. Such tools offer sophisticated O/R mapping and, like JDBC, tend to work in any environment - J2EE web tier, J2EE EJB tier, or a plain old Java application. The best of them can integrate with distributed caches to boost performance in a load balanced or distributed environment. JDO (like) tools are typically portable across all mainstream databases.
  • Entity Beans have been controversial from the start, and have become even more so with the growing maturity of O/R mappers and the advent of JDO. IMHO they are best used with care, and on large and complex problems. Entity Beans, too, tend to be portable.
  • - Peter
    Bacon
    Ranch Hand

    Joined: Jul 14, 2004
    Posts: 305
    Thank you for taking the time to write such a lengthy response.

    From a total green-horns perspective (at least to this type of development) would a small-time developer be better off to build a code base that is mostly portable and create custom interfaces to new DBs as the need presents itself, instead of expending the effort to create totally portable code?

    Thanks again.
    Peter den Haan
    author
    Ranch Hand

    Joined: Apr 20, 2000
    Posts: 3252
    You will probably want to start with a JDBC wrapper such as the Spring JdbcTemplate. In that case, your major source of non-portability is the SQL you write. All you would need to do is to pull this SQL into an separarate configuration file, such as a properties file or (if you're using Spring anyway) a Spring configuration file. This is good practice anyway; you don't want loads of SQL mixed in with your Java code.

    To port to a new database, revise the SQL and redeploy. Of course I'm ignoring a few issues here, such as primary key generation, but as you indicate that's easily hidden behind an interface.

    Do familiarise yourself with O/R mapping and JDO or Hibernate at some point though.

    - Peter
    [ August 02, 2004: Message edited by: Peter den Haan ]
    Bacon
    Ranch Hand

    Joined: Jul 14, 2004
    Posts: 305
    Again, thank you.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: One code base many DBs?
     
    Similar Threads
    New to J2EE
    SQL Antipatterns: MVC and Common Pitfalls in DB application development
    What do you people think of..?
    transfer data from one DB to another DB ?
    How container talks to DB?