A little demo showing how easily we can create a SQL query utility using Oracle's XML SQL Utility (XSU) within a JAX-RS Web Service. Although basic you can easily create more query parameters to take full advantage of the XML SQL Utility (XSU) API.
1. Create a JAX-RS Web Service as follows.
package pas.au.xmlsql.services; import java.sql.Connection; import java.sql.SQLException; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import javax.ws.rs.GET; import javax.ws.rs.Path; import javax.ws.rs.Produces; import javax.ws.rs.QueryParam; import javax.ws.rs.core.MediaType; import oracle.xml.parser.v2.XMLDocument; import oracle.xml.sql.query.OracleXMLQuery; @Path ("/queryservice") public class QueryService { private DataSource ds = null; private final String dataSourceLocation = "jdbc/scottDS"; public QueryService() { Context context; try { context = getInitialContext(); // lookup data source at this point ds = (DataSource) context.lookup (dataSourceLocation); } catch (NamingException e) { throw new RuntimeException (e.getMessage()); } } private Connection getConnection () throws SQLException { return ds.getConnection(); } private static Context getInitialContext() throws NamingException { return new InitialContext(); } @GET @Path("/query") @Produces(MediaType.APPLICATION_XML) public XMLDocument runQuery (@QueryParam("sql")String querySql) { Connection conn = null; OracleXMLQuery qry = null; XMLDocument domDoc = null; try { conn = getConnection(); System.out.println("Query is -> " + querySql); qry = new OracleXMLQuery(conn, querySql); domDoc = (XMLDocument)qry.getXMLDOM(); return domDoc; } catch (Exception e) { throw new RuntimeException (String.format("Error running query [%s]", querySql), e); } finally { /* * if exception occurs here no real need to log it just catch it and continue */ if (qry != null) { qry.close(); } if (conn != null) { try { conn.close(); } catch (SQLException s) {} } } } }
2. Ensure you add the required libraries within JDeveloper. Apart from the Jersey libraries for JAX-RS you will need the 3 extra libraries as shown below.
3. Run Query Service and eventually it should show it's ready as follows.
Run startup time: 4461 ms.
[Application XMLSQLUtility deployed to Server Instance IntegratedWebLogicServer]
Target Application WADL -- http://localhost:7101/xmlsql-service/jersey/application.wadl
Target URL -- http://localhost:7101/xmlsql-service/jersey/queryservice/query?sql=string
4. Now using "curl" run some queries as shown below. Of course it only accepts SQL queries but still useful enough.
Query: select user from dual
[oracle@beast pas]$ curl http://paslap-au.au.oracle.com:7101/xmlsql-service/jersey/queryservice/query?sql="select user from dual" <?xml version="1.0" encoding="UTF-8" standalone="no"?> <ROWSET> <ROW num="1"><USER>SCOTT</USER></ROW> </ROWSET>
Query: select * from dept
[oracle@beast pas]$ curl http://paslap-au.au.oracle.com:7101/xmlsql-service/jersey/queryservice/query?sql="select * from dept" <?xml version="1.0" encoding="UTF-8" standalone="no"?> <ROWSET> <ROW num="1"><DEPTNO>10</DEPTNO><DNAME>ACCOUNTING</DNAME><LOC>NEW YORK</LOC</ROW> <ROW num="2"><DEPTNO>20</DEPTNO><DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></ROW> <ROW num="3"><DEPTNO>30</DEPTNO><DNAME>SALES</DNAME><LOC>CHICAGO</LOC></ROW> <ROW num="4"><DEPTNO>40</DEPTNO><DNAME>OPERATIONS</DNAME><LOC>BOSTON</LOC></ROW></ROWSET>