• Advertisement
Sign in to follow this  

[java] some thoughts on a jdbc wrapper

This topic is 4254 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

For simplicity I've only included the code for the Select statement.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlAccumulator implements SqlCommand, From, Where{
	private Connection conn;

	private Statement sql;

	private String query;

	private SqlAccumulator(String connection, String user, String pass) {
		try {
			conn = DriverManager.getConnection(connection, user, pass);
			sql = conn.createStatement();
		} catch (SQLException e) {
			System.err.println("Could not create connection to database");
		}
	}

	public static Select getSQLInterface(String connection, String user,
			String pass) {
		return new SqlAccumulator(connection, user, pass);
	}
	public From select(String columns) {
		query = "SELECT " + columns;
		return this;
	}

	public From select() {
		return this.select("*");
	}

	public From select(String[] columns) {
		String cols = "";
		for (int i = 0; i < columns.length; ++i) {
			cols += columns;
			if (i < columns.length - 1) {
				cols += ", ";
			}
		}
		return this.select(cols);
	}

	public Where from(String tableName) {
		query += " FROM " + tableName;
		return this;
	}

	public Where from(String[] tableNames) {
		String tabs = "";
		for (int i = 0; i < tableNames.length; ++i) {
			tabs += tableNames;
			if (i < tableNames.length - 1) {
				tabs += ", ";
			}
		}
		return this.from(tabs);
	}

	public Where from(String tableName, String alias) {
		return from(tableName + " " + alias);
	}

	public Where from(String[] tableNames, String[] aliases) {
		String res = "";
		for (int i = 0; i < tableNames.length; ++i) {
			res += tableNames + " " + aliases;
		}
		return from(res);
	}

	public SqlKeyWord where(String expression) {
		query += " WHERE " + expression;
		return this;
	}

	public ResultSet execute() {
		try {
			if (sql.execute(query)) {
				return sql.getResultSet();
			} else {
				// sql.getUpdateCount();
			}
		} catch (SQLException e) {
			System.err.println("Could not execute query: (" + query + ")");
			System.err.println("Reason: " + e.getMessage());
		}
		return null;
	}

	public void shutdown() {
		try {
			conn.commit();
			conn.close();
		} catch (SQLException e) {
			System.err.println("Error in shutdown");
		}

	}
}

Now, this is all sort of weird, and it gets weirder when you see the interfaces that are referenced in the above code
public interface SqlKeyWord {
	public ResultSet execute();
	public void shutdown();
}
public interface SqlCommand extends Select //, Insert, Update, Delete, etc
{
}
public interface Select extends SqlKeyWord {
	public From select();
	public From select(String columns);
	public From select(String[] columns);
}
public interface From extends SqlKeyWord {
	public Where from(String tableName);
	public Where from(String[] tableNames);
	public Where from(String tableName, String alias);
	public Where from(String[] tableNames, String[] aliases);
}
public interface Where extends SqlKeyWord {
	public SqlKeyWord where(String expression);
}

but put it all together into an example of usage, and it makes sense
//The old way
Connection conn = DriverManager.getConnection(connection, user, pass);
Statement sql = conn.createStatement();
ResultSet sql.executeQuery("SELECT COLUMN_NAME FROM TABLE_NAME WHERE COLUMN_NAME=VALUE");

//My way
SqlCommand sql = SqlAccumulator.getSQLInterface("myconnectionstring", "myusername", "mypassword");
ResultSet results = sql.select("COLUMN_NAME").from("TABLE_NAME").where("COLUMN_NAME=VALUE");
It may not look like much of a difference, but I get two things immediately: code assist on some of my SQL when I'm developing in Eclipse and compile-time checking of some of my SQL. Additionally, with a more mature implementation of the Where interface (i.e. something to enable nested queries) this allows me to create very secure dynamic queries without the use of stored procedures. I could also create a much better error reporting system, instead of the garbage messages that are returned by the likes of Oracle. The code-assist thing is the most interesting, IMO. I don't do enough SQL to ever be fully familiar with it. So, I sometimes forget the order in which certain clauses must occur. In the implementation that I have written, I have the select, insert, update, and delete commands all implemented (I removed them just for terseness). When starting with the SqlCommand interface, I have only the option to call the select, insert, delete, etc. methods, the SQL commands like where, from, values, etc. are not available. After I choose the select method and enter my column name parameters, I immediately call another method. After a select statement, there is always a from statement, because we have to indicate what table or view we are using. So, the select method returns the accumulator object *through* the From interface. I can then call the from method of the From interface (again, gaining assistance on my parameters), and then be presented with an option: the next method call is either a where clause or executing the query. I was really suprised by how well this came out. I've seen other OO based database interfaces, but they have been extremely complicated. This is entirely managed by the interfacing system.

Share this post


Link to post
Share on other sites
Advertisement
Looks very similar to what you can do in Hibernate. Were you just creating a JDBC wrapper for fun/educational purposes, or is this an attempt to fill a perceived void?
Overall, it looks like it's a very handy way of doing your SQL. The one complaint I have is that it's trying to bend Java into SQL syntax. I suppose one new to Java and comfortable to SQL wouldn't have any problems.

Share this post


Link to post
Share on other sites

If it were for professional development, I would definitely advise the usage of Hibernate, using Annotations of the EJB 3 persistence API, which is a bless for anyone that ever worked with OR-Mapping in his life.

However, since you stated you don't use enough SQL to bother, may I suggest something? Instead of these interfaces, why don't you use a template model for SQL syntax (using Apache's Velocity as the template engine), where you input a class to the context, and it generates SQL code for you?

You could use this approach to generated and store all CRUD and basic select statements on loading time. You make these fixed statements accessible for persistence code, and it stays abstract from OR-Mapping. For the other refined queries, you can create a small QueryComposer interface (inspired by Hibernate, yes =) where you can define methods in the same way your JDBC wrapper does, to make it easy to use and suit your ways.

Anyway, it's just my rant, I think you would like to build something you don't have to change often. :D

Son Of Cain

Share this post


Link to post
Share on other sites
Yeah, it was mostly just playing around. I had been fiddling around with Hypersonic and this was sort of the result.

I will definitely be checking out Hibernate.

However, the point was the simplicity of the code design and how certain features of the code flow emerged out of this simple design. Perhaps there is a place for this "pattern" in other areas? Are there other examples of this sort of accumulator pattern with method access controlled through interfacing?

Share this post


Link to post
Share on other sites
Quote:
Original post by capn_midnight
Perhaps there is a place for this "pattern" in other areas? Are there other examples of this sort of accumulator pattern with method access controlled through interfacing?


None that I've heard of. But I do not have a broad range of knowledge on the matter - I work with Java/SQL most of the time, but I've always sticked to frameworks such as Hibernate to speed-up development.

As for the template suggestion, it was done with simplicity in mind - because when using templates (or even grammars) for this, you always have "default" statements generated for you, and, in case you need to tweak or change anything, you do so in the templates, not the code.

But all this is - of course - irrelevant, since using a OR-Mapping framework such as Hibernate or Top Link is much better. I strongly advise you to take a look at the new EJB 3 persistence API. Although the name is "EJB", this persistence API works with desktop applications too, no need to have an application server. It is clean, lightweight, and very powerful.

Share this post


Link to post
Share on other sites
This code is not taking care of SQL injection attacks and of passing parameters to the queries, the two main reasons to use PreparedStatement. To paraphrase someone's signature, if your JDBC code contains the word "createStatement", you have a security hole.

Moreover, the normal API for database access objects is parameters in, application objects out; fragments of SQL are a very unfriendly input and a ResultSet (with the dangerously open Statement and Connection behind it) is a very raw result.

The value of autocompletion is doubtful; you still have column and table names, in addition to values, in strings. Maybe developing queries in a specific tool, like Toad or SquirrelSQL or many others, with autocompletion of SQL in its entirety and of identifiers, would be an easier and more general solution.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement