In this example we will create an operation that connects to a
relational database and executes an SQL statement. The result
should be inserted in the edited XML document as a
table
. To make the operation fully
configurable, it will have arguments for the database connection string, the
user name, the password and the SQL expression.
Create a new Java project, in your IDE.
Create the directory lib
in the
Java project directory and copy in it the oxygen.jar
file from the {oXygen_installation_directory}/lib
directory.
Create the class
simple.documentation.framework.QueryDatabaseOperation
.
This class must implements the
ro.sync.ecss.extensions.api.AuthorOperation
interface.
import ro.sync.ecss.extensions.api.ArgumentDescriptor; import ro.sync.ecss.extensions.api.ArgumentsMap; import ro.sync.ecss.extensions.api.AuthorAccess; import ro.sync.ecss.extensions.api.AuthorOperation; import ro.sync.ecss.extensions.api.AuthorOperationException; public class QueryDatabaseOperation implements AuthorOperation{
Let's define the arguments of the operation. For each
of them we will use a String
constant
representing the argument name:
private static final String ARG_JDBC_DRIVER ="jdbc_driver"; private static final String ARG_USER ="user"; private static final String ARG_PASSWORD ="password"; private static final String ARG_SQL ="sql"; private static final String ARG_CONNECTION ="connection";
We must describe each of the argument name and type.
To do this implement the
getArguments
method which
will return an array of argument descriptors:
public ArgumentDescriptor[] getArguments() { ArgumentDescriptor args[] = new ArgumentDescriptor[] { new ArgumentDescriptor( ARG_JDBC_DRIVER, ArgumentDescriptor.TYPE_STRING, "The name of the Java class that is the JDBC driver."), new ArgumentDescriptor( ARG_CONNECTION, ArgumentDescriptor.TYPE_STRING, "The database URL connection string."), new ArgumentDescriptor( ARG_USER, ArgumentDescriptor.TYPE_STRING, "The name of the database user."), new ArgumentDescriptor( ARG_PASSWORD, ArgumentDescriptor.TYPE_STRING, "The database password."), new ArgumentDescriptor( ARG_SQL, ArgumentDescriptor.TYPE_STRING, "The SQL statement to be executed.") }; return args; }
These names, types and descriptions will be listed in the Arguments table when the operation is configured.
When the operation is invoked, the implementation of
the doOperation
method
extracts the arguments, forwards them to the method
that connects to the database and generates the XML
fragment. The XML fragment is then inserted at the
caret position.
public void doOperation(AuthorAccess authorAccess, ArgumentsMap map) throws IllegalArgumentException, AuthorOperationException { // Collects the arguments. String jdbcDriver = (String)map.getArgumentValue(ARG_JDBC_DRIVER); String connection = (String)map.getArgumentValue(ARG_CONNECTION); String user = (String)map.getArgumentValue(ARG_USER); String password = (String)map.getArgumentValue(ARG_PASSWORD); String sql = (String)map.getArgumentValue(ARG_SQL); int caretPosition = authorAccess.getCaretOffset(); try { authorAccess.insertXMLFragment( getFragment(jdbcDriver, connection, user, password, sql), caretPosition); } catch (SQLException e) { throw new AuthorOperationException( "The operation failed due to the following database error: " + e.getMessage(), e); } catch (ClassNotFoundException e) { throw new AuthorOperationException( "The JDBC database driver was not found. Tried to load ' " + jdbcDriver + "'", e); } }
The getFragment
method loads
the JDBC driver, connects to the database and extracts
the data. The result is a table
element
from the
http://www.oxygenxml.com/sample/documentation
namespace. The header
element contains the
names of the SQL columns. All the text from the XML
fragment is escaped. This means that the '<' and
'&' characters are replaced with the '<' and
'&' character entities to ensure the fragment
is well-formed.
private String getFragment( String jdbcDriver, String connectionURL, String user, String password, String sql) throws SQLException, ClassNotFoundException { Properties pr = new Properties(); pr.put("characterEncoding", "UTF8"); pr.put("useUnicode", "TRUE"); pr.put("user", user); pr.put("password", password); // Loads the database driver. Class.forName(jdbcDriver); // Opens the connection Connection connection = DriverManager.getConnection(connectionURL, pr); java.sql.Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); StringBuffer fragmentBuffer = new StringBuffer(); fragmentBuffer.append( "<table xmlns=" + "'http://www.oxygenxml.com/sample/documentation'>"); // // Creates the table header. // fragmentBuffer.append("<header>"); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { fragmentBuffer.append("<td>"); fragmentBuffer.append( xmlEscape(metaData.getColumnName(i))); fragmentBuffer.append("</td>"); } fragmentBuffer.append("</header>"); // // Creates the table content. // while (resultSet.next()) { fragmentBuffer.append("<tr>"); for (int i = 1; i <= columnCount; i++) { fragmentBuffer.append("<td>"); fragmentBuffer.append( xmlEscape(resultSet.getObject(i))); fragmentBuffer.append("</td>"); } fragmentBuffer.append("</tr>"); } fragmentBuffer.append("</table>"); // Cleanup resultSet.close(); statement.close(); connection.close(); return fragmentBuffer.toString(); }
The complete source code of our operation is found in the Example Files Listings, the Java Files section.
Package the compiled class into a jar file.
Copy the jar file and the JDBC driver files into the
frameworks/sdf
directory.
Add the jars to the Author class path. For this, Open the options Document Type Dialog, select SDF and press the button.
Select the Classpath tab in the lower part of the dialog.
Click on the Actions label.
The action properties are:
An unique identifier for the action. Use clients_report.
The name of the action. Use Clients Report.
Use the letter r.
Enter the text Connects to the database and collects the list of clients.
Enter here: ${frameworks}/sdf/TableDB20.gif
We assume the image TableDB20.gif
for the toolbar
action is already present in the frameworks/sdf
directory.
Leave empty.
We will use: Ctrl+Shift+c.
Let's set up the operation. The action will work only
if the current element is a
section
.
Set the value to:
local-name()='section'
In this case, we'll use our Java operation
we defined earlier. Press the simple.documentation.framework.QueryDatabaseOperation
.
Once selected, the list of arguments is displayed.
In the figure below the first argument, jdbc_driver, represents the class name of the MySQL JDBC driver.
The connection string has the URL syntax : jdbc://<database_host>:<database_port>/<database_name>.
The SQL expression used in the example is:
SELECT userID, email FROM users
but it can be any valid SELECT expression which can be applied to the database.
Add the action to the toolbar, using the Toolbar panel.
To test the action you can open the sdf.xml
sample place the caret inside a section
between two
para
elements for instance. Press the
button from the toolbar. You can
see below the toolbar with the action button and sample table
inserted by the
action.