start > Virtual Brain > Java > snipsnap contributions

snipsnap contributions

Created by stefan. Last edited by stefan, 6 years and 350 days ago. Viewed 614 times. #3

[diff] [history] [edit] [rdf]

labels

attachments

dbview-macro-src.jar (6632)
dbview-macro.jar (6483)

DB-View Macro

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.

Please login to post a comment.

Sun	Mon	Tue	Wed	Thu	Fri	Sat
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31