irstv/orbisgis — pull request #96 by gourlaysama (2012-08-14 15:35:28+00:00)
Title: Use real compiled SQL scripts instead of building an SQL String at runtime

@agueganno, @ebocher, @nicolas-f: this PR shows how to use an SQL script compiled with [gdmsql-maven-plugin)](https://github.com/irstv/gdmsql-maven-plugin).

Basically:
* put a script into `src/main/sql`
* it will get parsed into a binary query plan, mostly ready to execute, and serialized to a `.bsql` file
* this file becomes a resource that can be loaded with

```java
SQLScript s = Engine.loadScript(getClass().getResourceAsStream("toto.bsql");
```
* then some parameters can be set and it can be executed. See the diff for the full example.

The example here is the SQL that gets executed when exporting a table into a DB from the UI.

This is better for several reason:
1. SQL script are syntax-checked at compile time. Some other simple checks are done as well (all DSF-independent stuff). Failing early is better :-)
2. It is faster to load the binary produced from disk that re-parsing the script, even for a small string.
3. The SQL used is more readable because it is separated from the java code instead of entangled with it.
4. This is more secure because all **input parameters are properly escaped**, i.e. no SQL injection. Goodbye concatenating String objects!
5. Since the SQL is in a separate file, it is properly versioned as well, as a single entity, which isn't so bad :-)

See [the wiki page](https://github.com/irstv/gdmsql-maven-plugin/wiki) of the maven plugin for more information on the plugin itself and how to use it.

PS: I also cleaned up a bit the DataBaseRow class, which was an awful mess...