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;
/**
* 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;
}
}
#================================================================================
# SqlQueryResource properties
#================================================================================
[SqlQueryResource]
driver = org.apache.derby.jdbc.EmbeddedDriver
connectionUrl = jdbc:derby:C:/testDB;create=true
allowTempUpdates = true
includeRowNums = true