Description
Queries a database and renders tabular result. Example: {dbview:java:comp/env/jdbc/testDS|select * from salary}.
Parameters
- fully qualified JNDI datasource name to be used for database access (mandatory)
- SQL string that will produce the tabular data to be rendered (mandatory)
- header off - set to 'true' if rendering of the resulting table must not include a header row (optional)
Example result
| ID | EMP_ID | SALARY | STAMP |
|---|
| 100 | 19382 | 10000 | foo-20050530 |
| 101 | 29809 | 12345 | foo-2003-0404 |
Download
See attachements of this snip.
Installing
Copy dbview-macro.jar to your snipsnap/WEB-INF/lib directory. Then set up the data source:
Define the datasource
For Tomcat, this is done in the tomcat/conf/server.xml file in the snipsnap context:
<Context path="/snipsnap" docBase="/tomcat-5.0/webapps/snipsnap" debug="5" reloadable="true">
<Resource name="jdbc/testDS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/testDS">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!-- MySQL dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>foo</value>
</parameter>
<parameter>
<name>password</name>
<value>bar</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/test?autoReconnect=true</value>
</parameter>
</ResourceParams>
</Context>Reference the datasource
For Tomcat, this is added in the snipsnap/WEB-INF/web.xml file:
<resource-ref>
<res-ref-name>
jdbc/testDS
</res-ref-name>
<res-type>
javax.sql.DataSource
</res-type>
<res-auth>
Container
</res-auth>
</resource-ref>Use the datasource
In the dbview-macro, a datasource as defined above would be referenced as follows:
{dbview:java:comp/env/jdbc/testDS|select * from foobar}.WARNING
- Datasource name and SQL query can be seen by EVERYONE, even if NOT LOGGED IN.
- If logged in, users may use arbitrary SQL queries and therefore gain access to any data provided by the data source available.
Please consider this before installing the macro on any site.
Web Start Demo!Resources
Powered by SnipSnap 1.0b3-uttoxeter 
