SqlQueryResource

The SqlQueryResource class shows examples of the following:

- Using the {@link oaj.dto.ResultSetList} to serialize database result sets.
- Using {@link oajr.RestContext#getConfig()} to access config properties.
- Using form entry beans.

The example uses embedded Derby to create a database whose name is defined in the external configuration files.

Pointing a browser to the resource shows the following:

http://localhost:10000/sqlQuery

Running a query results in the following output:

select count(*) from SYS.SYSTABLES; select TABLEID,TABLENAME,TABLETYPE from SYS.SYSTABLES;

##### SqlQueryResource.java

/** * Sample resource that shows how Juneau can serialize ResultSets. */ @RestResource( path="/sqlQuery", messages="nls/SqlQueryResource", title="SQL query service", description="Executes queries against the local derby '$C{SqlQueryResource/connectionUrl}' database", htmldoc=@HtmlDoc( widgets={ StyleMenuItem.class }, navlinks={ "up: request:/..", "options: servlet:/..", "$W{StyleMenuItem}", "source: $C{Source/gitHub}/org/apache/juneau/examples/rest/$R{servletClassSimple}.java" }, aside={ "<div style='min-width:200px' class='text'>", " <p>An example of a REST interface over a relational database.</p>", " <p><a class='link' href='?sql=select+*+from sys.systables'>try me</a></p>", "</div>" } ) ) public class SqlQueryResource extends BasicRestServlet { private String driver, connectionUrl; private boolean allowUpdates, allowTempUpdates, includeRowNums; /** * Initializes the registry URL and rest client. * * @param builder The resource config. * @throws Exception */ @RestHook(INIT) public void initConnection(RestContextBuilder builder) throws Exception { Config cf = builder.getConfig(); driver = cf.getString("SqlQueryResource/driver"); connectionUrl = cf.getString("SqlQueryResource/connectionUrl"); allowUpdates = cf.getBoolean("SqlQueryResource/allowUpdates", false); allowTempUpdates = cf.getBoolean("SqlQueryResource/allowTempUpdates", false); includeRowNums = cf.getBoolean("SqlQueryResource/includeRowNums", false); try { Class.forName(driver).newInstance(); } catch (Exception e) { throw new RuntimeException(e); } } /** GET request handler - Display the query entry page. */ @RestMethod(name=GET, path="/", summary="Display the query entry page") public Div doGet(RestRequest req, @Query("sql") String sql) { return div( script("text/javascript", "\n // Quick and dirty function to allow tabs in textarea." +"\n function checkTab(e) {" +"\n if (e.keyCode == 9) {" +"\n var t = e.target;" +"\n var ss = t.selectionStart, se = t.selectionEnd;" +"\n t.value = t.value.slice(0,ss).concat('\\t').concat(t.value.slice(ss,t.value.length));" +"\n e.preventDefault();" +"\n }" +"\n }" +"\n // Load results from IFrame into this document." +"\n function loadResults(b) {" +"\n var doc = b.contentDocument || b.contentWindow.document;" +"\n var data = doc.getElementById('data') || doc.getElementsByTagName('body')[0];" +"\n document.getElementById('results').innerHTML = data.innerHTML;" +"\n }" ), form("servlet:/").method(POST).target("buf").children( table( tr( th("Position (1-10000):"), td(input().name("pos").type("number").value(1)), th("Limit (1-10000):"), td(input().name("limit").type("number").value(100)), td(button("submit", "Submit"), button("reset", "Reset")) ), tr( td().colspan(5).children( textarea().name("sql").text(sql == null ? " " : sql) .style("width:100%;height:200px;font-family:Courier;font-size:9pt;").onkeydown("checkTab(event)") ) ) ) ), br(), div().id("results"), iframe().name("buf").style("display:none").onload("parent.loadResults(this)") ); } /** POST request handler - Execute the query. */ @RestMethod(name=POST, path="/") public List<Object> doPost(@Body PostInput in) throws Exception { List<Object> results = new LinkedList<Object>(); // Don't try to submit empty input. if (StringUtils.isEmpty(in.sql)) return results; if (in.pos < 1 || in.pos > 10000) throw new RestException(SC_BAD_REQUEST, "Invalid value for position. Must be between 1-10000"); if (in.limit < 1 || in.limit > 10000) throw new RestException(SC_BAD_REQUEST, "Invalid value for limit. Must be between 1-10000"); // Create a connection and statement. // If these fails, let the exception transform up as a 500 error. Connection c = DriverManager.getConnection(connectionUrl); c.setAutoCommit(false); Statement st = c.createStatement(); String sql = null; try { for (String s : in.sql.split(";")) { sql = s.trim(); if (! sql.isEmpty()) { Object o = null; if (allowUpdates || (allowTempUpdates && ! sql.matches("(?:i)commit.*"))) { if (st.execute(sql)) { ResultSet rs = st.getResultSet(); o = new ResultSetList(rs, in.pos, in.limit, includeRowNums); } else { o = st.getUpdateCount(); } } else { ResultSet rs = st.executeQuery(sql); o = new ResultSetList(rs, in.pos, in.limit, includeRowNums); } results.add(o); } } if (allowUpdates) c.commit(); else if (allowTempUpdates) c.rollback(); } catch (SQLException e) { c.rollback(); throw new RestException(SC_BAD_REQUEST, "Invalid query: {0}", sql).initCause(e); } finally { c.close(); } return results; } /** The parsed form post */ public static class PostInput { public String sql; public int pos = 1, limit = 100; } }

##### samples.cfg

#================================================================================ # SqlQueryResource properties #================================================================================ [SqlQueryResource] driver = org.apache.derby.jdbc.EmbeddedDriver connectionUrl = jdbc:derby:C:/testDB;create=true allowTempUpdates = true includeRowNums = true