In our project, we need to evaluate SQL statements without any database server. Can you suggest any free Java library that has the ability to evaluate math based SQL statements and return the result?
For example;
SELECT 2*2 AS RESULT
4
Will probably be called as int result = SQLEvaluator.evaluate("SELECT 2*2 AS RESULT");
It is possible to achieve that using ZQL as seen in the code below. But I seriously advise you pick a simple embedded database such as H2 (example here) and use it instead (the project health is much higher).
public class H2ExpEval {
public static void main(String... args) throws Exception {
evaluateUsingH2("SELECT 2+2");
evaluateUsingH2("SELECT 3+7-5");
evaluateUsingH2("SELECT 2*2*2+1");
}
private static void evaluateUsingH2(String sql) throws Exception {
Class.forName("org.h2.Driver");
// opens an in-memory database: no files are saved and it's all quicker
Connection conn = DriverManager.getConnection("jdbc:h2:mem:");
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(sql);
if (rs.next()) {
System.out.println(rs.getString(1));
}
stat.close(); conn.close();
}
}
Output:
4
5
9
To use it, add this to your pom.xml:
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.3.171</version>
</dependency>
public class ZqlEvalDemo {
public static void main(String args[]) throws Exception {
System.out.println(evaluate("SELECT 2+2 FROM RESULT;"));
System.out.println(evaluate("SELECT 3+7-5 FROM RESULT;"));
System.out.println(evaluate("SELECT 2*2*2+1 FROM RESULT;"));
}
private static ZqlParser p = new ZqlParser();
private static Object evaluate(String s) throws Exception {
p.initParser(new java.io.ByteArrayInputStream(s.getBytes()));
ZStatement st = p.readStatement();
ZSelectItem zSelectItem = ((ZQuery) st).getSelect().get(0);
ZExpression exp = (ZExpression) zSelectItem.getExpression();
return new ZEval().evalExpValue(new ZTuple(), exp);
}
}
Output:
4.0
5.0
9.0
For the dependencies, either download from the ZQL Page or, for testing purposes, add this to your pom.xml (testing purposes because we don't know who maintains that repository):
<dependencies>
<dependency>
<groupId>com.experlog</groupId>
<artifactId>zql</artifactId>
<version>1.0</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>zql</id>
<name>zql</name>
<url>http://dbappserv.cis.upenn.edu:8080/artifactory/ext-releases-local</url>
</repository>
</repositories>
You might use an embedded java-only database server, like java Derby, HSQL or others with a non-persistent in-memory database. The advantage being the existence of a real standards compliant engine.
On that a thin wrapper might be provided with the Java Scripting API, so you can work with variables and functions too.
Usage of scripting would be
public static void main(String[] args) {
ScriptEngineManager manager = new ScriptEngineManager();
for (ScriptEngineFactory factory : manager.getEngineFactories()) {
System.out.printf("language: %s, engine: %s%n", factory.getLanguageName(), factory.getEngineName());
}
ScriptEngine engine = manager.getEngineByName("SQL");
try {
Object result = engine.eval("SELECT 1+2;");
} catch (ScriptException ex) {
Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
}
}
Implementing SQL for the Java Scripting API
Make a project for sqlscript.jar with a text file:
/META-INF/services/javax.script.ScriptEngineFactory
containing:
my.sqlscript.SQLScriptEngineFactory
The engine factory class can so be discovered, by language name for instance. It delivers a ScriptEngine for evaluation.
package my.sqlscript;
public class SQLScriptEngineFactory implements ScriptEngineFactory {
@Override
public ScriptEngine getScriptEngine() {
return new SQLScriptEngine(this);
}
}
The engine can do the work. Here I used HSQLDB, which has a problem with a SELECT without FROM, but the same can be done even better with JavaDB / Derby pr H2SQL. Not that the available parameter binding would need a bit plumbing too.
public class SQLScriptEngine extends AbstractScriptEngine {
private final SQLScriptEngineFactory factory;
public SQLScriptEngine(SQLScriptEngineFactory factory) {
this.factory = factory;
}
@Override
public Object eval(String script, ScriptContext context)
throws ScriptException {
StringBuilder sb = new StringBuilder(); // Multi-column/multi-row result
Object singleValue = null; // Single value result
Server hsqlServer = new Server();
try {
File dbFile = File.createTempFile("sqlscript", ".db");
String dbURL = dbFile.toURI().toURL().toString();
hsqlServer.setLogWriter(null);
hsqlServer.setSilent(true);
hsqlServer.setDatabaseName(0, "db1");
hsqlServer.setDatabasePath(0, dbURL);
} catch (IOException | MalformedURLException ex) {
throw new ScriptException(ex);
}
hsqlServer.start();
try {
Class.forName("org.hsqldb.jdbcDriver");
Connection connection =
DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/db1", "sa", "");
try (PreparedStatement statement = connection.prepareStatement(script);
ResultSet rs = statement.executeQuery();) {
ResultSetMetaData meta = rs.getMetaData();
int columns = meta.getColumnCount();
int row = 1;
while (rs.next()) {
for (int column = 1; column <= columns; ++column) {
Object value = rs.getObject(column);
singleValue = row == 1 && column == 1? value : null;
sb.append(value);
if (column < columns) {
sb.append("\t");
}
}
sb.append("\n");
++row;
}
}
} catch (SQLException | ClassNotFoundException e2) {
Logger.getLogger(SQLScriptEngine.class.getName()).log(Level.SEVERE, null, e2);
}
hsqlServer.stop();
return singleValue != null ? singleValue : sb.toString();
}
}
You could make the connection more persistent in the factory class, and have a pseudo SQL statement shutdown to close the connection explicitly.
Conclusion
It is a relatively easy layer of abstraction, which could help in reuse. Other than providing your own classes.
