An introduction to GSQL
In this article I cover working with SQL from Groovy using the GSQL support built into the language. This is not a complete guide, rather a getting started guide, that shows you a few of the concepts. Firstly, GSQL is built on top of JDBC; which you are probably already familiar with. If not there are many good web guides and books on the subject. Lets get started looking a how to configure up GSQL and execute some statements.
Making the JDBC driver available
Before we can do anything else we need to establish a connection with a database. In order to do this we need to ensure that the appropriate JDBC driver is available on the classpath. If you are using groovy scripting, you can place the driver in $GROOVY_HOME/lib or you can edit $GROOVY_HOME/conf/groovy-starter.conf and add an extra entry.
Creating a connection to the database
Now we have the driver on the classpath, we need to make a connection to the database that we can use to execute statements against. If you’ve done this before with JDBC directly, it can be quite long winded. However lets look at how groovy does things:
//
// Here we create an sql instance - that we will use to interact with the database, make
// sure you have HSQL on your classpath for this.
//
def sql = Sql.newInstance("jdbc:hsqldb:mem:testdb", "sa", "", "org.hsqldb.jdbcDriver")
Executing a DDL statement - create table.
OK, so in the last step we managed to connect to the database, now we need to actually create a table. What we need to do is execute an SQL statement against the database. Lets take a look at how this is done:
sql.execute("""
create table atable (
name varchar(50),
address varchar(100)
)""")
Inserting data into the database
Using the same method as before, we will use the execute command to call insert for each record.
sql.execute("insert into atable(name, address) values(’dave’, ’some address’)")
sql.execute("insert into atable(name, address) values(’joe’, ’another address’)")
sql.execute("insert into atable(name, address) values(’fred’, ’yet another address’)")
