Apart from the ORMs...
If you're using the rs.getString and rs.getInt routines, then you can certainly ease your maintenance burden if you rely on named columns rather than numbered columns.
Specifically rs.getInt("id") rather than rs.getInt(1), for example.
It's been rare that I've had an actual column change data type, so future SQL maintenance is little more than adding the new columns that were done to the table, and those can be simply tacked on to the end of your monster bind list in each of you little DAO objects.
Next, you then take that idiom of using column names, and you extend it to a plan of using consistent names, and, at the same, time, "unique" names. The intent there is that each column in your database has a unique name associated with it. In theory it can be as simple (albeit verbose) as tablename_columnname, thus if you have a "member" table, the column name is "member_id" for the id column.
What does this buy you?
It buys you being able to use your generic DAOs on any "valid" result set.
A "valid" result set is a result set with the columns named using your unique naming spec.
So, you get "select id member_id, name member_name from member where id = 1".
Why would you want to do that? Why go to that bother?
Because then your joins become trivial.
PreparedStatement = con.prepareStatement("select m.id member_id, m.name member_name, p.id post_id, p.date post_date, p.subject post_subject from member m, post p where m.id = p.member_id and m.id = 123");
ResultSet rs = ps.executeQuery();
Member m = null;
Post p = null;
while(rs.next()) {
if (m == null) {
m = MemberDAO.createFromResultSet(rs);
}
p = PostDAO.createFromResultSet(rs);
m.addPost(p);
}
See, here the binding logic doesn't care about the result set contents, since it's only interested in columns it cares about.
In your DAOs, you make them slightly clever about the ResultSet. Turns out if you do 'rs.getInt("member_id")' and member_id doesn't happen to actually BE in the result set, you'll get a SQLException.
But with a little work, using ResultSetMetaData, you can do a quick pre-check (by fetching all of the column names up front), then rather than calling "rs.getInt" you can call "baseDAO.getInt" which handles those details for you so as not to get the exception.
The beauty here is that once you do that, you can fetch incomplete DAOs easily.
PreparedStatement = con.prepareStatement("select m.id member_id from member m where m.id = 123");
ResultSet rs = ps.executeQuery();
Member m = null;
if (rs.next()) {
m = MemberDAO.createFromResultSet(rs);
}
Finally, it's really (really) a trivial bit of scripting (using, say, AWK) that can take the properties of a bean and convert it into a proper blob of binding code for an initial DAO. A similar script can readily take a SQL table statement and convert it in to a Java Bean (at least the base members) that then your IDE converts in to a flurry of getters/setters.
By centralizing the binding code in to the DAO, maintenance is really hardly anything at all, since it's changed in one place. Using partial binding, you can abuse them mercilessly.
PreparedStatement = con.prepareStatement("select m.name member_name, max(p.date) post_date from member m, post p where post.member_id = m.id and m.id = 123");
ResultSet rs = ps.executeQuery();
Member m = null;
Post p = null;
if (rs.next()) {
m = MemberDAO.createFromResultSet(rs);
p = MemberDAO.craateFromResultSet(rs);
}
System.out.println(m.getName() + " latest post was on " + p.getDate());
Your burden moving forward is mostly writing the SQL, but even that's not horrible. There's not much difference between writing SQL and EQL. Mind, is does kind of suck having to write a select statement with a zillion columns in it, since you can't (and shouldn't anyway) use "select * from ..." (select * always (ALWAYS) leads to trouble, IME).
But those are just the reality. I have found,though, that (unless you're doing reporting), that problem simply doesn't happen a lot. It happens at least once for most every table, but it doesn't happen over and over and over. And, naturally, once you have it once, you can either "cut and paste" your way to glory, or refactor it (i.e. sql = "select " + MemberDAO.getAllColumns() + ", " + PostDAO.getAllColumns() + " from member m, post p").
Now, I like JPA and ORMs, I find them useful, but I also find them a PITA. There is a definite love/hate relationship going on there. And when things are going smooth, boy, is it smooth. But when it gets rocky -- hoo boy. Then it can get ugly. As a whole, however, I do recommend them.
But if you're looking for a "lightweight" non-framework, this technique is useful, practical, low overhead, and gives you a lot of control over your queries. There's simply no black magic or dark matter between your queries and your DB, and when things don't work, it's not some arcane misunderstanding of the framework or edge case bug condition in someone elses 100K lines of code, but rather, odds are, a bug in your SQL -- where it belongs.