One of the more interesting ways to expand the functionality with STM is to leverage the Java Extension framework. In this example, it will be demonstrated how to provide a generic database query interface via HTTP, through the use of the JDBC interface. JDBC, which stands for Java Database Connector, is a standard API that allows Java applications to access databases in a generic way. In this example, we will leverage the mysql.jar JDBC connector, but any database should operate the same way.
The first step is to adjust the java classpath that will be used by the system. In the case of the MySQL jar, we would add the following to the system global settings:
java!classpath: /opt/zeus/zxtm/conf/jars/mysql.jar
This will insure that the jdbc jar will be available to our Java code.
Second, upload the jdbc jar file through the Java Extensions catalog, along with the attached class files (The Java source files are attached as well), while allowing a policy to be automatically created for the webserver class file.
From there, configure the webserver policy with the following parameters and modify the values as appropriate:
connectstring The connect string necessary to register the jdbc driver and connect, example: jdbc:mysql://192.168.122.21:3306
username The username to connect with
password The password of the user connecting
The Connect string can (and likely should) point to the IP address and port for a virtual server pointing to your databases, to allow for failover to be configured, in the event of a problem. This is documented for MySQL in the document <insert here>.
At this point, create a virtual server, pointing to the pool "discard", as no back-end connectivity will be used by the policy. If everything is setup properly, and a valid query is performed, it should be now possible to perform a query against the virtual server such as:
http://192.168.122.21:81/website?select%20*%20from%20referers;
The path value (here the value "website") corresponds to the "database" that should be used, and the value after the ? is the SQL query that is to be performed. As coded, this example will not allow modifications to be made to the database, so no insert or update will be allowed.
Here is an example output (in JSON format):
[
{
"count": 9,
"data": "/"
},
{
"count": 3,
"data": "/static/test.html"
},
{
"count": 4,
"data": "/icons/apache_pb.gif"
},
{
"count": 4,
"data": "/icons/poweredby.png"
},
{
"count": 4,
"data": "/favicon.ico"
}
]
