A JDBCExecutor is an object that is capable of executing "select" queries and returning the results in a form that is suitable for processing with JETT, outputting the results to the destination Excel spreadsheet. Normally, an instance is created in Java code, taking an open Connection as an argument. Then, it is exposed in a beans map to make it visible in the template processing. Then, the template contains an expression that executes the execQuery method, which executes the "select" query it's given, and returns a list of results that can be processed by JETT. Usually this list is given as the value of the attribute items in a forEach tag.
Here is example code that creates and exposes a JDBCExecutor.
Connection conn = getConnection(); // Gets a Connection by other means
JDBCExecutor jdbc = new JDBCExecutor(conn);
Map<String, Object> beans = new HashMap<String, Object>();
beans.put("jdbc", jdbc);
// transformation code here
Here is an example template spreadsheet that uses the JDBCExecutor.
| First | Last | Salary |
| <jt:forEach items="${jdbc.execQuery('SELECT first_name, last_name, salary FROM employee')}" var="employee">${employee.first_name} | ${employee.last_name} | ${employee.salary}</jt:forEach> |
This is the result.
| First | Last | Salary |
| Robert | Stack | $1,000.00 |
| Suzie | Queue | $900.00 |
| Elmer | Fudd | $800.00 |
| Bugs | Bunny | $1,500.00 |
It is also possible to include JDBC placeholders ("?" characters) in the query. Supply one additional parameter to the execQuery method for every ? character in the query. Assuming the bean theTitle is the string "Cartoon Character"...
| First | Last | Salary |
| <jt:forEach items="${jdbc.execQuery('SELECT first_name, last_name, salary FROM employee WHERE title = ?', theTitle)}" var="employee">${employee.first_name} | ${employee.last_name} | ${employee.salary}</jt:forEach> |
... gets translated to:
| First | Last | Salary |
| Elmer | Fudd | $800.00 |
| Bugs | Bunny | $1,500.00 |
It is possible to use single-quote characters inside the SQL string. But because they are embedded in a JEXL string that is itself delimited by single-quotes, they need to be escaped with a backslash. But then JETT requires that the backslash itself needs to be escaped. This results in two backslashes for an escaped single-quote character. This example demostrates how to escape single-quote characters in the SQL string embedded in the attribute.
| First | Last | Salary |
| <jt:forEach items="${jdbc.execQuery('SELECT first_name, last_name, salary FROM employee WHERE title = \\'Cartoon Character\\'')}" var="employee">${employee.first_name} | ${employee.last_name} | ${employee.salary}</jt:forEach> |