Compute SQL expressions in Java?

Question

In our project, we need to evaluate SQL statements without any database server. Can you offer any free Java library that has the ability to evaluate math-based SQL queries and return the result?

For instance:

Enter

SELECT 2*2 AS RESULT

Output

4

It will probably be called int result = SQLEvaluator.evaluate("SELECT 2*2 AS RESULT");

Answer 1

This can be achieved using ZQL , as shown in the code below. But I seriously advise you to choose a simple built-in database such as H2 ( example here ) and use it instead (project health is much higher).

Using H2:

 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 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 it to your pom.xml :

 <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.3.171</version> </dependency> 

Using ZQL:

 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 dependencies, download from the ZQL page or, for testing purposes, add this to your pom.xml (testing goals, because we don’t know who supports this 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> 

Answer 2

You can use the embedded Java database server, such as java Derby, HSQL or others, with a volatile database in memory. The advantage is a real standard compatible with the engine.

In this case, the thin shell can be equipped with the Java Scripting API, so you can work with variables and functions.

---

Scripting will

 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); } } 

SQL implementation for the Java scripting API

Create a project for sqlscript.jar with a text file:

 /META-INF/services/javax.script.ScriptEngineFactory 

containing:

 my.sqlscript.SQLScriptEngineFactory 

A powerful factory class can be detected, for example, by the name of the language. It provides ScriptEngine for evaluation.

 package my.sqlscript; public class SQLScriptEngineFactory implements ScriptEngineFactory { @Override public ScriptEngine getScriptEngine() { return new SQLScriptEngine(this); } } 

The engine can do the job. Here I used HSQLDB, which has a SELECT problem without FROM, but the same thing can be done even better with JavaDB / Derby pr H2SQL. Not that affordable parameter binding would also need a little plumbing.

 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 can make the connection more persistent in the factory class and have a pseudo-SQL shutdown statement to close the connection explicitly.

Conclusion

This is a relatively simple layer of abstraction that can help with reuse. Besides providing your own classes.