Skip to content

Commit 4bc522d

Browse files
gk-browngk-brown
committed
Add support for attaching subqueries to ResultSetAdapter.
1 parent 31b0d85 commit 4bc522d

File tree

5 files changed

+167
-31
lines changed

5 files changed

+167
-31
lines changed

‎README.md‎

Lines changed: 101 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
[![Maven Central](https://img.shields.io/maven-central/v/org.httprpc/httprpc.svg)](http://repo1.maven.org/maven2/org/httprpc/httprpc/)
33

44
# Introduction
5-
HTTP-RPC is an open-source framework for implementing and interacting with RESTful and REST-like web services in Java. It is extremely lightweight and requires only a Java runtime environment and a servlet container. The entire framework is distributed as a single JAR file that is about 65KB in size, making it an ideal choice for applications where a minimal footprint is desired.
5+
HTTP-RPC is an open-source framework for implementing and interacting with RESTful and REST-like web services in Java. It is extremely lightweight and requires only a Java runtime environment and a servlet container. The entire framework is distributed as a single JAR file that is about 66KB in size, making it an ideal choice for applications where a minimal footprint is desired.
66

77
This guide introduces the HTTP-RPC framework and provides an overview of its key features.
88

@@ -13,6 +13,12 @@ Feedback is welcome and encouraged. Please feel free to [contact me](mailto:gk_b
1313
* [Getting HTTP-RPC](#getting-http-rpc)
1414
* [HTTP-RPC Classes](#http-rpc-classes)
1515
* [WebService](#webservice)
16+
* [Method Arguments](#method-arguments)
17+
* [Return Values](#return-values)
18+
* [Exceptions](#exceptions)
19+
* [Request and Repsonse Properties](#request-and-repsonse-properties)
20+
* [Path Variables](#path-variables)
21+
* [Documentation](#documentation)
1622
* [JSONEncoder and JSONDecoder](#jsonencoder-and-jsondecoder)
1723
* [CSVEncoder and CSVDecoder](#csvencoder-and-csvdecoder)
1824
* [BeanAdapter](#beanadapter)
@@ -702,7 +708,7 @@ Once applied, the statement can be executed:
702708
return new ResultSetAdapter(statement.executeQuery());
703709
```
704710

705-
A complete example that uses both classes is shown below. It is based on the "pet" table from the MySQL sample database:
711+
A complete example that uses both classes is shown below. It is based on the "pet" table from the MySQL "menagerie" sample database:
706712

707713
```sql
708714
CREATE TABLE pet (
@@ -792,7 +798,99 @@ the values of the "first_name" and "last_name" columns would be returned in a ne
792798
]
793799
```
794800

795-
TODO Nested queries
801+
### Nested Queries
802+
`ResultSetAdapter` can also be used to return the results of nested queries. The `attach()` method assigns a subquery to a key in the result map:
803+
804+
```java
805+
public void attach(String key, String subquery) { ... }
806+
```
807+
808+
Each attached query is executed once per row in the result set. The resulting rows are returned in a list that is associated with the corresponding key.
809+
810+
Internally, subqueries are executed as prepared statements using the `Parameters` class. All values in the base row are supplied as parameter values to each subquery.
811+
812+
An example based on the MySQL "employees" sample database is shown below. The base query retreives the employee's number, first name, and last name from the "employees" table. Subqueries to return the employee's salary and title history are optionally attached based on the values provided in the `details` parameter:
813+
814+
```java
815+
@RequestMethod("GET")
816+
@ResourcePath("?:employeeNumber")
817+
public void getEmployee(List<String> details) throws SQLException, IOException {
818+
String employeeNumber = getKey("employeeNumber");
819+
820+
Parameters parameters = Parameters.parse("SELECT emp_no AS employeeNumber, "
821+
+ "first_name AS firstName, "
822+
+ "last_name AS lastName "
823+
+ "FROM employees WHERE emp_no = :employeeNumber");
824+
825+
parameters.put("employeeNumber", employeeNumber);
826+
827+
try (Connection connection = DriverManager.getConnection(DB_URL);
828+
PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
829+
830+
parameters.apply(statement);
831+
832+
try (ResultSet resultSet = statement.executeQuery()) {
833+
ResultSetAdapter resultSetAdapter = new ResultSetAdapter(resultSet);
834+
835+
for (String detail : details) {
836+
switch (detail) {
837+
case "titles": {
838+
resultSetAdapter.attach("titles", "SELECT title, "
839+
+ "from_date AS fromDate, "
840+
+ "to_date as toDate "
841+
+ "FROM titles WHERE emp_no = :employeeNumber");
842+
843+
break;
844+
}
845+
846+
case "salaries": {
847+
resultSetAdapter.attach("salaries", "SELECT salary, "
848+
+ "from_date AS fromDate, "
849+
+ "to_date as toDate "
850+
+ "FROM salaries WHERE emp_no = :employeeNumber");
851+
852+
break;
853+
}
854+
}
855+
}
856+
857+
getResponse().setContentType("application/json");
858+
859+
JSONEncoder jsonEncoder = new JSONEncoder();
860+
861+
jsonEncoder.writeValue(resultSetAdapter.next(), getResponse().getOutputStream());
862+
}
863+
} finally {
864+
getResponse().flushBuffer();
865+
}
866+
}
867+
```
868+
869+
A sample response including both titles and salaries is shown below:
870+
871+
```json
872+
{
873+
"employeeNumber": 10004,
874+
"firstName": "Chirstian",
875+
"lastName": "Koblick",
876+
"titles": [
877+
{
878+
"title": "Senior Engineer",
879+
"fromDate": 817794000000,
880+
"toDate": 253370782800000
881+
},
882+
...
883+
],
884+
"salaries": [
885+
{
886+
"salary": 74057,
887+
"fromDate": 1006837200000,
888+
"toDate": 253370782800000
889+
},
890+
...
891+
]
892+
}
893+
```
796894

797895
### Typed Iteration
798896
The `adapt()` method of the `ResultSetAdapter` class can be used to facilitate typed iteration of query results. This method produces an `Iterable` sequence of values of a given interface type representing the rows in the result set. The returned adapter uses dynamic proxy invocation to map properties declared by the interface to column labels in the result set. A single proxy instance is used for all rows to minimize heap allocation.

‎httprpc-test/src/main/java/org/httprpc/test/mysql/EmployeeService.java‎

Lines changed: 39 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
import java.sql.PreparedStatement;
2121
import java.sql.ResultSet;
2222
import java.sql.SQLException;
23+
import java.util.List;
2324

2425
import javax.servlet.ServletException;
2526
import javax.servlet.annotation.WebServlet;
@@ -53,7 +54,11 @@ public void init() throws ServletException {
5354
}
5455

5556
@RequestMethod("GET")
56-
@Response("[{employeeNumber: integer, firstName: string, lastName: string}]")
57+
@Response("[{\n"
58+
+ " employeeNumber: integer,\n"
59+
+ " firstName: string,\n"
60+
+ " lastName: string\n"
61+
+ "}]")
5762
public void getEmployees(String name) throws SQLException, IOException {
5863
Parameters parameters = Parameters.parse("SELECT emp_no AS employeeNumber, "
5964
+ "first_name AS firstName, "
@@ -89,10 +94,18 @@ public void getEmployees(String name) throws SQLException, IOException {
8994
+ " employeeNumber: integer,\n"
9095
+ " firstName: string,\n"
9196
+ " lastName: string,\n"
92-
+ " salaries: [{salary: integer, fromDate: date, toDate: date}],\n"
93-
+ " titles: [{title: string, fromDate: date, toDate: date}]\n"
94-
+ "}")
95-
public void getEmployee() throws SQLException, IOException {
97+
+ " titles: [{\n"
98+
+ " title: string,\n"
99+
+ " fromDate: date,\n"
100+
+ " toDate: date\n"
101+
+ " }],\n"
102+
+ " salaries: [{\n"
103+
+ " salary: integer,\n"
104+
+ " fromDate: date,\n"
105+
+ " toDate: date\n"
106+
+ " }]\n"
107+
+ "}")
108+
public void getEmployee(List<String> details) throws SQLException, IOException {
96109
String employeeNumber = getKey("employeeNumber");
97110

98111
Parameters parameters = Parameters.parse("SELECT emp_no AS employeeNumber, "
@@ -110,21 +123,33 @@ public void getEmployee() throws SQLException, IOException {
110123
try (ResultSet resultSet = statement.executeQuery()) {
111124
ResultSetAdapter resultSetAdapter = new ResultSetAdapter(resultSet);
112125

113-
resultSetAdapter.attach("salaries", "SELECT salary, "
114-
+ "from_date AS fromDate, "
115-
+ "to_date as toDate "
116-
+ "FROM salaries where emp_no = :employeeNumber");
126+
for (String detail : details) {
127+
switch (detail) {
128+
case "titles": {
129+
resultSetAdapter.attach("titles", "SELECT title, "
130+
+ "from_date AS fromDate, "
131+
+ "to_date as toDate "
132+
+ "FROM titles WHERE emp_no = :employeeNumber");
133+
134+
break;
135+
}
117136

118-
resultSetAdapter.attach("titles", "SELECT title, "
119-
+ "from_date AS fromDate, "
120-
+ "to_date as toDate "
121-
+ "FROM titles where emp_no = :employeeNumber");
137+
case "salaries": {
138+
resultSetAdapter.attach("salaries", "SELECT salary, "
139+
+ "from_date AS fromDate, "
140+
+ "to_date as toDate "
141+
+ "FROM salaries WHERE emp_no = :employeeNumber");
142+
143+
break;
144+
}
145+
}
146+
}
122147

123148
getResponse().setContentType("application/json");
124149

125150
JSONEncoder jsonEncoder = new JSONEncoder();
126151

127-
jsonEncoder.writeValue(resultSetAdapter, getResponse().getOutputStream());
152+
jsonEncoder.writeValue(resultSetAdapter.next(), getResponse().getOutputStream());
128153
}
129154
} finally {
130155
getResponse().flushBuffer();

‎httprpc-test/src/main/java/org/httprpc/test/mysql/PetService.java‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -71,7 +71,13 @@ public void init() throws ServletException {
7171
}
7272

7373
@RequestMethod("GET")
74-
@Response("[{name: string, owner: string, species: string, sex: string, birth: date}]")
74+
@Response("[{\n"
75+
+ " name: string,\n"
76+
+ " owner: string,\n"
77+
+ " species: string,\n"
78+
+ " sex: string,\n"
79+
+ " birth: date\n"
80+
+ "}]")
7581
public void getPets(String owner, String format) throws SQLException, IOException {
7682
Parameters parameters = Parameters.parse("SELECT name, species, sex, birth FROM pet WHERE owner = :owner");
7783

‎httprpc-test/src/main/webapp/index.jsp‎

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -173,11 +173,8 @@
173173
<a href="${pageContext.request.contextPath}/employees">Employees</a><br/>
174174
<a href="${pageContext.request.contextPath}/employees?name=bal*">Employees ("bal*")</a><br/>
175175
<br/>
176-
<a href="${pageContext.request.contextPath}/employees/10001">Employee 10001</a><br/>
177-
<a href="${pageContext.request.contextPath}/employees/10002">Employee 10002</a><br/>
178-
<a href="${pageContext.request.contextPath}/employees/10003">Employee 10003</a><br/>
179176
<a href="${pageContext.request.contextPath}/employees/10004">Employee 10004</a><br/>
180-
<a href="${pageContext.request.contextPath}/employees/10005">Employee 10005</a><br/>
177+
<a href="${pageContext.request.contextPath}/employees/10004?details=titles&details=salaries">Employee 10004 (details)</a><br/>
181178

182179
</body>
183180
</html>

‎httprpc/src/main/java/org/httprpc/sql/ResultSetAdapter.java‎

Lines changed: 19 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,7 @@ public class ResultSetAdapter implements Iterable<Map<String, Object>> {
3535
private ResultSet resultSet;
3636
private ResultSetMetaData resultSetMetaData;
3737

38-
private LinkedHashMap<String, String> queries = new LinkedHashMap<>();
38+
private LinkedHashMap<String, String> subqueries = new LinkedHashMap<>();
3939

4040
private LinkedHashMap<String, Object> row = new LinkedHashMap<>();
4141

@@ -93,7 +93,7 @@ public Map<String, Object> next() {
9393
throw new RuntimeException(exception);
9494
}
9595

96-
for (Map.Entry<String, String> entry : queries.entrySet()) {
96+
for (Map.Entry<String, String> entry : subqueries.entrySet()) {
9797
Parameters parameters = Parameters.parse(entry.getValue());
9898

9999
parameters.putAll(row);
@@ -146,24 +146,34 @@ public ResultSetAdapter(ResultSet resultSet) {
146146
}
147147

148148
/**
149-
* Attaches a nested query to the result set.
149+
* Attaches a subquery to the result set.
150150
*
151151
* @param key
152-
* The key to associate with the query results.
152+
* The key to associate with the subquery results.
153153
*
154-
* @param query
155-
* The nested query.
154+
* @param subquery
155+
* The subquery to attach.
156156
*/
157-
public void attach(String key, String query) {
157+
public void attach(String key, String subquery) {
158158
if (key == null) {
159159
throw new IllegalArgumentException();
160160
}
161161

162-
if (query == null) {
162+
if (subquery == null) {
163163
throw new IllegalArgumentException();
164164
}
165165

166-
queries.put(key, query);
166+
subqueries.put(key, subquery);
167+
}
168+
169+
/**
170+
* Returns the next result.
171+
*
172+
* @return
173+
* The next result, or <tt>null</tt> if there are no more results.
174+
*/
175+
public Map<String, Object> next() {
176+
return iterator.hasNext() ? iterator.next() : null;
167177
}
168178

169179
@Override

0 commit comments

Comments
 (0)