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.
