Datasource

Clownfish - datasource

Clownfish can be connected to external datasources like MySQL or MS SQLServer. Oracle databases are also supported, but you must download the specific JDBC driver (ojdbc8.jar) from the Oracle site https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html and integrate it to your local Maven repository. Clownfish uses version 19.3 with JDK8 support JDBC driver.

To manually integrate it to your local Maven repository use following bash command:

mvn install:install-file -Dfile=path/to/your/ojdbc8.jar -DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=19.3 -Dpackaging=jar

IMPORTANT: check the local Maven repository that the filename is ojdbc8-19.3.jar. The version number MUST be included. Otherwise the build will fail.

How to specify an external datasource

You can use external datasources by defining the appropriate access data to your database. On the Datasource tab you have to enter following entries:

• Name - Name of the datasource
• Server - Hostname or IP address of the database
• URL - Connection string to the database (e.g. jdbc:jtds:sqlserver://hostname:port/databasename)
• Port - Database port
• Database name - Name of the database
• User - User with read access or if you want to write data also with write access
• Password - Password of the user
• Driver class - JDBC driver class

The driver classes supported right now:

• MSSQL: net.sourceforge.jtds.jdbc.Driver
• Oracle: oracle.jdbc.OracleDriver
• MySQL: com.mysql.jdbc.Driver
• PostgreSQL: org.postgresql.Driver

Using datasources in templates

To use your defined datasources in your templates you have to call Clownfish API methods. There are two methods. One for reading data and the second for executing SQL statements.

The API method for reading:

public Map dbread(String catalog, String tablename, String sqlstatement)

The catalog is the database catalog name or schema name. The tablename is used for the returned mapped data. You can access your data in the template with the tablename. The sqlstatement is usually a SELECT query.

The API method for executing:

public boolean dbexecute(String catalog, String sqlstatement)

The catalog is the database catalog name or schema name. The sqlstatement is usually an INSERT, UPDATE or DELETE statement. You will recieve a boolean value of true, when the statement was successful or false when not.

Here is an example for reading the data from the Clownfish table cf_site.

<#assign sql_clownfish = "SELECT * from cf_site">
<#assign siteList = databaseBean.dbread("clownfish", "clownfishdata", sql_clownfish)>
<#if siteList?has_content>
  <#list siteList.db.clownfishdata as site>
    <div>${site.name}</div>
  </#list>
</#if>

The first line assigns the SQL statement to the sql_clownfish variable. The second line calls the Clownfish API method dbread. Notice: All API methods in the SQL domain start with databaseBean. The arguments are catalogname ("clownfish"), tablename of the returned Map ("clownfishdata") and the SQL statement (the assigned sql_clownfish variable). The returned map is assigned to the variable siteList. The next line checks, if sitelist has content. If so, the next line starts the output with the list. Notice: The datastructure is called with the assigned list variable siteList followed by db and at last the tablename clownfishdata. To simply use an shortcut of the output you can use the alias site. The next line shows the rendering of the value. The name of a site is stored in the name field. You can render it with ${site.name}.

Notice: You are not limited to select data from one table with each dbread call. You can also define SQL SELECT statements with JOINS.

The next example is for a dbexecute API call. Assuming we have defined an external datasource called Warehouse with a table called products. We want to insert a product in this table.

<#assign name = parameter.name>
<#assign price = parameter.price>
<#assign sql_warehouse_insert_product = "INSERT INTO products (name, price) VALUES ('" + name + "', " + price + ")">
<#assign return_ok = databaseBean.dbexecute("warehouse", sql_warehouse_insert_product)>
<#if return_ok == true>
  <#assign sql_warehouse = "SELECT * from products">
  <#assign productList = databaseBean.dbread("warehouse", "productdata", sql_warehouse)>
  <#if productList?has_content>
    <#list productList.db.productdata as product>
      <div>${product.name}</div>
    </#list>
  </#if>
</#if>

Line one and two fetch the parameters name and price from the URL (e.g. http://localhost:9000/warehouse?name=Clownfish&price=15.5). Line three assigns the INSERT statement to the sql_warehouse_insert_product variable and injects the parameters into it. Line four executes the statement and assigns the boolean return value to return_ok. The next lines are common to the first example. When return_ok is true, you will see the list of products.