Skip to content

Commit 31b0d85

Browse files
gk-browngk-brown
committed
Add support for nested queries.
1 parent 7175762 commit 31b0d85

File tree

6 files changed

+224
-24
lines changed

6 files changed

+224
-24
lines changed

‎README.md‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -792,6 +792,8 @@ the values of the "first_name" and "last_name" columns would be returned in a ne
792792
]
793793
```
794794

795+
TODO Nested queries
796+
795797
### Typed Iteration
796798
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.
797799

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

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,133 @@
1+
/*
2+
* Licensed under the Apache License, Version 2.0 (the "License");
3+
* you may not use this file except in compliance with the License.
4+
* You may obtain a copy of the License at
5+
*
6+
* http://www.apache.org/licenses/LICENSE-2.0
7+
*
8+
* Unless required by applicable law or agreed to in writing, software
9+
* distributed under the License is distributed on an "AS IS" BASIS,
10+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
11+
* See the License for the specific language governing permissions and
12+
* limitations under the License.
13+
*/
14+
15+
package org.httprpc.test.mysql;
16+
17+
import java.io.IOException;
18+
import java.sql.Connection;
19+
import java.sql.DriverManager;
20+
import java.sql.PreparedStatement;
21+
import java.sql.ResultSet;
22+
import java.sql.SQLException;
23+
24+
import javax.servlet.ServletException;
25+
import javax.servlet.annotation.WebServlet;
26+
27+
import org.httprpc.RequestMethod;
28+
import org.httprpc.ResourcePath;
29+
import org.httprpc.Response;
30+
import org.httprpc.WebService;
31+
import org.httprpc.io.JSONEncoder;
32+
import org.httprpc.sql.Parameters;
33+
import org.httprpc.sql.ResultSetAdapter;
34+
35+
/**
36+
* Employee service.
37+
*/
38+
@WebServlet(urlPatterns={"/employees/*"}, loadOnStartup=1)
39+
public class EmployeeService extends WebService {
40+
private static final long serialVersionUID = 0;
41+
42+
private static final String DB_URL = "jdbc:mysql://db.local:3306/employees?user=root&password=password";
43+
44+
@Override
45+
public void init() throws ServletException {
46+
super.init();
47+
48+
try {
49+
Class.forName("com.mysql.jdbc.Driver");
50+
} catch (ClassNotFoundException exception) {
51+
throw new ServletException(exception);
52+
}
53+
}
54+
55+
@RequestMethod("GET")
56+
@Response("[{employeeNumber: integer, firstName: string, lastName: string}]")
57+
public void getEmployees(String name) throws SQLException, IOException {
58+
Parameters parameters = Parameters.parse("SELECT emp_no AS employeeNumber, "
59+
+ "first_name AS firstName, "
60+
+ "last_name AS lastName "
61+
+ "FROM employees "
62+
+ "WHERE first_name LIKE :name "
63+
+ "OR last_name LIKE :name");
64+
65+
parameters.put("name", (name == null) ? "%" : name.replace('*', '%'));
66+
67+
try (Connection connection = DriverManager.getConnection(DB_URL);
68+
PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
69+
70+
parameters.apply(statement);
71+
72+
try (ResultSet resultSet = statement.executeQuery()) {
73+
ResultSetAdapter resultSetAdapter = new ResultSetAdapter(resultSet);
74+
75+
getResponse().setContentType("application/json");
76+
77+
JSONEncoder jsonEncoder = new JSONEncoder();
78+
79+
jsonEncoder.writeValue(resultSetAdapter, getResponse().getOutputStream());
80+
}
81+
} finally {
82+
getResponse().flushBuffer();
83+
}
84+
}
85+
86+
@RequestMethod("GET")
87+
@ResourcePath("?:employeeNumber")
88+
@Response("{\n"
89+
+ " employeeNumber: integer,\n"
90+
+ " firstName: string,\n"
91+
+ " 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 {
96+
String employeeNumber = getKey("employeeNumber");
97+
98+
Parameters parameters = Parameters.parse("SELECT emp_no AS employeeNumber, "
99+
+ "first_name AS firstName, "
100+
+ "last_name AS lastName "
101+
+ "FROM employees WHERE emp_no = :employeeNumber");
102+
103+
parameters.put("employeeNumber", employeeNumber);
104+
105+
try (Connection connection = DriverManager.getConnection(DB_URL);
106+
PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
107+
108+
parameters.apply(statement);
109+
110+
try (ResultSet resultSet = statement.executeQuery()) {
111+
ResultSetAdapter resultSetAdapter = new ResultSetAdapter(resultSet);
112+
113+
resultSetAdapter.attach("salaries", "SELECT salary, "
114+
+ "from_date AS fromDate, "
115+
+ "to_date as toDate "
116+
+ "FROM salaries where emp_no = :employeeNumber");
117+
118+
resultSetAdapter.attach("titles", "SELECT title, "
119+
+ "from_date AS fromDate, "
120+
+ "to_date as toDate "
121+
+ "FROM titles where emp_no = :employeeNumber");
122+
123+
getResponse().setContentType("application/json");
124+
125+
JSONEncoder jsonEncoder = new JSONEncoder();
126+
127+
jsonEncoder.writeValue(resultSetAdapter, getResponse().getOutputStream());
128+
}
129+
} finally {
130+
getResponse().flushBuffer();
131+
}
132+
}
133+
}

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

Lines changed: 23 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -73,39 +73,39 @@ public void init() throws ServletException {
7373
@RequestMethod("GET")
7474
@Response("[{name: string, owner: string, species: string, sex: string, birth: date}]")
7575
public void getPets(String owner, String format) throws SQLException, IOException {
76-
try (Connection connection = DriverManager.getConnection(DB_URL)) {
77-
Parameters parameters = Parameters.parse("SELECT name, species, sex, birth FROM pet WHERE owner = :owner");
76+
Parameters parameters = Parameters.parse("SELECT name, species, sex, birth FROM pet WHERE owner = :owner");
7877

79-
parameters.put("owner", owner);
78+
parameters.put("owner", owner);
8079

81-
try (PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
82-
parameters.apply(statement);
80+
try (Connection connection = DriverManager.getConnection(DB_URL);
81+
PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
82+
83+
parameters.apply(statement);
8384

84-
try (ResultSet resultSet = statement.executeQuery()) {
85-
ResultSetAdapter resultSetAdapter = new ResultSetAdapter(resultSet);
85+
try (ResultSet resultSet = statement.executeQuery()) {
86+
ResultSetAdapter resultSetAdapter = new ResultSetAdapter(resultSet);
8687

87-
if (format == null || format.equals("json")) {
88-
getResponse().setContentType("application/json");
88+
if (format == null || format.equals("json")) {
89+
getResponse().setContentType("application/json");
8990

90-
JSONEncoder jsonEncoder = new JSONEncoder();
91+
JSONEncoder jsonEncoder = new JSONEncoder();
9192

92-
jsonEncoder.writeValue(resultSetAdapter, getResponse().getOutputStream());
93-
} else if (format.equals("csv")) {
94-
getResponse().setContentType("text/csv");
93+
jsonEncoder.writeValue(resultSetAdapter, getResponse().getOutputStream());
94+
} else if (format.equals("csv")) {
95+
getResponse().setContentType("text/csv");
9596

96-
CSVEncoder csvEncoder = new CSVEncoder(Arrays.asList("name", "species", "sex", "birth"));
97+
CSVEncoder csvEncoder = new CSVEncoder(Arrays.asList("name", "species", "sex", "birth"));
9798

98-
csvEncoder.writeValues(resultSetAdapter, getResponse().getOutputStream());
99-
} else if (format.equals("html")) {
100-
getResponse().setContentType("text/html;charset=UTF-8");
99+
csvEncoder.writeValues(resultSetAdapter, getResponse().getOutputStream());
100+
} else if (format.equals("html")) {
101+
getResponse().setContentType("text/html;charset=UTF-8");
101102

102-
TemplateEncoder templateEncoder = new TemplateEncoder(getClass().getResource("pets.html"));
103+
TemplateEncoder templateEncoder = new TemplateEncoder(getClass().getResource("pets.html"));
103104

104-
templateEncoder.setBaseName(getClass().getPackage().getName() + ".pets");
105-
templateEncoder.writeValue(resultSetAdapter, getResponse().getOutputStream(), getRequest().getLocale());
106-
} else {
107-
throw new UnsupportedOperationException();
108-
}
105+
templateEncoder.setBaseName(getClass().getPackage().getName() + ".pets");
106+
templateEncoder.writeValue(resultSetAdapter, getResponse().getOutputStream(), getRequest().getLocale());
107+
} else {
108+
throw new UnsupportedOperationException();
109109
}
110110
}
111111
} finally {

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -166,5 +166,18 @@
166166
<br/>
167167
<a href="${pageContext.request.contextPath}/pets/average-age">Average Age</a><br/>
168168

169+
<hr/>
170+
171+
<a href="${pageContext.request.contextPath}/employees?api">Employees (API)</a><br/>
172+
<br/>
173+
<a href="${pageContext.request.contextPath}/employees">Employees</a><br/>
174+
<a href="${pageContext.request.contextPath}/employees?name=bal*">Employees ("bal*")</a><br/>
175+
<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/>
179+
<a href="${pageContext.request.contextPath}/employees/10004">Employee 10004</a><br/>
180+
<a href="${pageContext.request.contextPath}/employees/10005">Employee 10005</a><br/>
181+
169182
</body>
170183
</html>

‎httprpc/build.gradle‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ plugins {
2020
}
2121

2222
group = 'org.httprpc'
23-
version = '5.9'
23+
version = '5.9.1'
2424

2525
repositories {
2626
mavenCentral()

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

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,11 +14,14 @@
1414

1515
package org.httprpc.sql;
1616

17+
import java.sql.Connection;
18+
import java.sql.PreparedStatement;
1719
import java.sql.ResultSet;
1820
import java.sql.ResultSetMetaData;
1921
import java.sql.SQLException;
2022
import java.util.Iterator;
2123
import java.util.LinkedHashMap;
24+
import java.util.LinkedList;
2225
import java.util.Map;
2326
import java.util.NoSuchElementException;
2427

@@ -32,6 +35,8 @@ public class ResultSetAdapter implements Iterable<Map<String, Object>> {
3235
private ResultSet resultSet;
3336
private ResultSetMetaData resultSetMetaData;
3437

38+
private LinkedHashMap<String, String> queries = new LinkedHashMap<>();
39+
3540
private LinkedHashMap<String, Object> row = new LinkedHashMap<>();
3641

3742
private Iterator<Map<String, Object>> iterator = new Iterator<Map<String, Object>>() {
@@ -88,6 +93,32 @@ public Map<String, Object> next() {
8893
throw new RuntimeException(exception);
8994
}
9095

96+
for (Map.Entry<String, String> entry : queries.entrySet()) {
97+
Parameters parameters = Parameters.parse(entry.getValue());
98+
99+
parameters.putAll(row);
100+
101+
LinkedList<Map<String, Object>> results = new LinkedList<>();
102+
103+
try {
104+
Connection connection = resultSet.getStatement().getConnection();
105+
106+
try (PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
107+
parameters.apply(statement);
108+
109+
try (ResultSet resultSet = statement.executeQuery()) {
110+
for (Map<String, Object> result : new ResultSetAdapter(resultSet)) {
111+
results.add(result);
112+
}
113+
}
114+
}
115+
} catch (SQLException exception) {
116+
throw new RuntimeException(exception);
117+
}
118+
119+
row.put(entry.getKey(), results);
120+
}
121+
91122
hasNext = null;
92123

93124
return row;
@@ -114,6 +145,27 @@ public ResultSetAdapter(ResultSet resultSet) {
114145
}
115146
}
116147

148+
/**
149+
* Attaches a nested query to the result set.
150+
*
151+
* @param key
152+
* The key to associate with the query results.
153+
*
154+
* @param query
155+
* The nested query.
156+
*/
157+
public void attach(String key, String query) {
158+
if (key == null) {
159+
throw new IllegalArgumentException();
160+
}
161+
162+
if (query == null) {
163+
throw new IllegalArgumentException();
164+
}
165+
166+
queries.put(key, query);
167+
}
168+
117169
@Override
118170
public Iterator<Map<String, Object>> iterator() {
119171
return iterator;

0 commit comments

Comments
 (0)