Simple classes for JDBC
Tutorial Details:
Simple classes for JDBC
Simple classes for JDBC
By: By Madhu Siddalingaiah
Avoid the tedium of JDBC programming
all me a troublemaker. I know what you are thinking. Yet another tool/wizard/API/framework to complicate your life. I can understand your hesitation: the problem of object-relational mapping has been kicked around for years. Everyone has a solution that is easier, better, faster, cheaper. Why do we need more code that probably does what someone has already done somewhere else? Two reasons: Most of the solutions appear more complicated than necessary, and they require a significant commitment. Once you buy in, it's hard to get out.
I like things simple. A colleague recently shared a nice quote:
"Simplicity leads to ubiquity, complexity leads to obscurity."
And we've all heard the Einstein quote:
"A scientific theory should be as simple as possible, but no simpler."
Here's one from James Gosling himself:
"Complexity is in many ways just evil. Complexity makes things harder to understand, harder to build, harder to debug, harder to evolve, harder to just do about everything."
I could go on and on. There are many examples of complexity, too many. In the case of database access, you can use entity beans, or an object relational mapping tool, or the latest open source framework. I don't have a formal complaint against any of those tools; I'm certain their authors had a great vision in mind when developing them. For me, today, I want something simple.
Charting the latitudes
Let's step back a bit. Most of the time, an application needs to take information from a user and create/edit/delete/view it as rows in a database. One important detail is that information to and from the user must be in the form of a string. This means that validation/parsing has to be done on the way in and formatting, on the way out. This is not hard, but it's repetitive and involves numerous annoying null checks. Another important feature that users have come to expect are the so-called VCR controls: the ability to page through a large set of results or jump to the end, not easy to do with a ResultSet alone.
One popular solution is to manually or automatically map SQL results and table schemas to objects and classes. At the outset, this appears reasonable, if not natural. In general, database modelers and object modelers try to accomplish the same thing: manage business information in a logical and extensible manner.
Unfortunately, the honeymoon ends there. Object modelers prefer to isolate themselves from the sundry details of how data goes in and out. To some, the database is just a big hashmap with more than one key. This might be an accurate assessment, but there is a tricky SQL animal known as the table join. Database experts argue that joins give relational databases their power. Object modelers might look at a join as an untamed beast that just causes more trouble than it's worth. Regardless of your opinions, objects and databases are not likely to change significantly in the near future. Applications and business logic will continue to use objects, and data persistence will continue to take the form of a relational database.
Cultural barriers aside, significant differences exist between objects/classes and rows/tables. Objects can include behavior, which distinguishes them from structures and other aggregate types. Classes offer inheritance and polymorphism, which makes them reusable and extensible. But databases are about efficient storage and quick searching. That cannot be underestimated when you consider the potential of tens of thousands, if not millions, of rows of information. This information, and its efficient access, proves just as important as the code that manages it.
Another important detail is that classes are easy to change and code is often refactored when convenient and helpful. Changing a database's structure is a much bigger deal. Changing column names or even creating new tables and reloading data is not in itself challenging, but adjusting all of the code that relies on that structure can prove problematic. For that reason, typically, database tables change little after they are populated.
All of these differences have led to the so-called impedance mismatch, a term borrowed from the electrical-engineering principle of maximum power transfer. Even though we can get data in and out of a database easily and efficiently, the objects that use the data might not fit well. The typical result is lots of boilerplate code that looks like manual object serialization. This is tedious and error prone.
To avoid the tedium, many tools have evolved over the years. The J2EE specification offers a model in the form of container-managed entity beans and EJB-QL (Enterprise JavaBean Query Language). In the open source community, Hibernate has become a popular solution. These solutions depend on a query language like SQL, but not exactly. The result is that you might have to adjust your queries to fit the tool, rather than use what the database is capable of doing by itself.
These can be effective solutions for a broad range of issues, but they do not yet meet my arbitrary standard of simplicity. I stress arbitrary because many will find these solutions meet their requirements; it's just a difference of opinion and nothing more.
The Chase
Many developers who I have worked with are not object modelers or database modelers; they represent a third category of programmer. They are equally comfortable with objects and SQL; they are PowerBuilder developers. PowerBuilder has its good and bad qualities, like any product, but in my opinion, it has one jewel: the data window. Instead of mapping every table or potential result set into a distinct class, PowerBuilder has one class: the data window. Simply put, the data window is like an updatable ResultSet . To use it, simply issue a query, any query, no matter how complex. Any results returned could be easily formatted, sorted, and navigated in any order. Further, any of the returned data could be modified and committed back to the database in one method call. The data window handles all primary key management and transactions. The data window reduces the standing wave ratio (SWR) of the impedance mismatch from 10:1 down to something like 1.5:1. It's not perfect, but it does the job.
The Chase, Version 1
Having convinced myself that the data window can be an effective solution, I started writing something similar in Java on top of JDBC (Java Database Connectivity). I had a fairly complete solution, but there were problems. One of the beauties of the data window is that it automatically manages primary keys and column data types. This significantly reduces the burden on the developer. The problem with my implementation was the availability of high quality database drivers. The implementation depends heavily on result set metadata. This metadata, like any other metadata, such as Java Reflection and XML Schema, is powerful when accurate. The problem is that many drivers, even high quality drivers, do not return all of the necessary metadata. Nothing in the JDBC specification requires drivers to do so. The methods exist, but they don't necessarily work for all combinations of drivers and databases.
From what I am told, PowerBuilder also runs into metadata problems when using ODBC (Open Database Connectivity) and native drivers. The solution is to implement custom database drivers for each database it supports to guarantee robust functionality. Foolishly, I wasted a lot of time investigating why some drivers just didn't provide all of the metadata. The answer is simple: there are lots of databases, lots of drivers, and a few people developing them. Most likely, perfect metadata is not high on the list of priorities; 100 percent support is not likely to be achieved in the near future.
The Chase, Version 2
I attempted to scale back the metadata requirements in the hopes of supporting the most popular database drivers and databases. This proved to be better, but less convenient than the original implementation, and it would still leave some out in the cold.
I concluded that maybe I was too bold. Like others, I tried to solve too large a problem. I retreated to something more manageable, something simple. I decided that what I liked best were the navigation facilities, formatting, parsing, and validation. I scrapped the idea of managing primary keys and left that to the developer. I concluded that in the end, primary keys are not that hard to manage, a fair compromise in my opinion.
The end result is a scaled-back solution that tolerates limitations of available database drivers, but is still relatively easy to use.
Nuts and bolts
Enough background -- it's time to see the implementation and some examples. We'll see how the package can be used to perform basic select/insert/update/delete operations and also format and validate data.
Here's a simplified UML diagram of this article's package, which can be downloaded from Resources :
UML diagram of key classes
The Database class does two things: maintains a map of formatters and implements methods to select, insert, update, and delete rows. There's also a validate() method, which I will talk about shortly. Formatter is an abstract class that defines three methods: parse() , format() , and validate() . As mentioned earlier, the users' data view is in the form of strings, so the formatters are responsible for converting database types to and from strings. When entering data, invalid data is always possible, e.g., nonnumeric characters in a numeric field, dates incorrectly formatted, or strings that don't match a pattern. For each of the expected column data types, there exists a Formatter subclass.
Let's look at a simple example of selecting data:
Connection con = getConnection();
myDB = new Database(false);
myDB.loadDefaultFormatters(con, "roles");
String[] params = {"roles.id", "99"};
Results rs = myDB.select
Read
Tutorial at: Click here to view the tutorial
Rate Tutorial: Simple classes for JDBC
View Tutorial: Simple classes for JDBC
Related
Tutorials:
 Integrating Databases
Integrating Databases
|
Use JDBC for
industrial-strength performance, Part 1 - JavaWorld January
2000
Use JDBC for
industrial-strength performance, Part 1 - JavaWorld January
2000
|
Code generation using Javadoc - JavaWorld August
2000
Code generation using Javadoc - JavaWorld August
2000
|
Create your own type 3 JDBC driver, Part 1
Create your own type 3 JDBC driver, Part 1
|
Overcome J2SE
1.3-1.4 incompatibilities
Overcome J2SE
1.3-1.4 incompatibilities
|
Simple classes for JDBC
Simple classes for JDBC
|
Using CachedRowSet to Transfer JDBC Query Results Between Classes
Using CachedRowSet to Transfer JDBC Query Results Between Classes
The Java Database Connectivity (JDBC) API provides developers with an interface to a SQL database server, such as MySQL or Oracle. Central to any JDBC application is the java.sql.ResultS
|
JDBC scripting, Part 2
JDBC scripting, Part 2
Programming and Java scripting in JudoScript
Summary
JudoScript is a rich functional scripting language, and an easy and powerful general programming and Java scripting language.
JudoScript's power comes from its synergy of
|
The JDBC RowSet Implementations Tutorial
In "The JDBC RowSet Implementations Tutorial," you will look at how to use the standard JDBC RowSet implementations specified in JSR-114.
|
StelsCSV - JDBC driver for text files
StelsCSV is a CSV JDBC type 4 driver that allows to perform SQL queries and other JDBC operations on text files (comma separated, tab separated, fixed length etc). Using this driver, you can easily create a simple database consisting of plain text files.
|
XlsJdbc Platform independent readonly jdbc driver to read xls files.
XlsJdbc is Platform independent readonly jdbc driver to read xls files.
|
Jeff Schmitt's JDBC Page
This tutorial assumes you are using the MySQL database and the GWE JDBC drivers. The host computer is triton.towson.edu.
|
Overview of JDBC and its use with Microsoft Access
JDBC provides a set of classes for Java with a standard SQL database access interface. Goal is uniform access to a wide range of relational databases.
|
Practically Groovy: JDBC programming with Groovy
Take your practical knowledge of Groovy one step further this month, as Andrew Glover shows you how to use GroovySql to build a simple data-reporting application. GroovySql combines closures and iterators to ease Java Database Connectivity (JDBC) programm
|
What is Persistence Framework?
What is Persistence Framework?
What is Persistence Framework?
A persistence framework moves the program data in its most natural form (in memory objects) to and from a permanent data store the database. The persistence framework manages the
|
First Step towards JDBC!
First Step towards JDBC!
First Step towards JDBC
Introduction
T his article introduce you with JDBC and shows you how to create a database application to access the databases. For the shake of simplicity, in very first example Access database and
|
First Step towards JDBC!
First Step towards JDBC!
Welcome to our JDBC Section
First Step towards JDBC
This article introduce you with JDBC and shows you how to create a database application to access the databases.
Accessing the Database from Servlet
This article shows
|
Introduction to the JDBC
Introduction to the JDBC
Introduction to the JDBC
Introduction
T his article introduce you with JDBC and shows you how to our search engine with database.
What is JDBC?
J ava Database Connectivity or JDBC for short is set of Java API's that
|
Connecting to the Database Using JDBC and Pure Java driver
Connecting to the Database Using JDBC and Pure Java driver
Connecting to the Database
JDBC Driver
In our search engine we are using MySQL database server and MM.MySQL Driver for connecting our application to the database. MM.MySQL Driver is
|
StelsCSV v2.2 - JDBC driver for CSV files
*StelsCSV* is a JDBC driver that allows to perform SQL queries and other JDBC operations on text files (CSV, tab-separated, fixed length etc). Using this driver, you can easily create a simple database consisting of plain text files.
|
|
|
|
