Efficiently Transforming JDBC Query Results to JSON

1/19/2019 Updated for HTTP-RPC 6.0

A lot of enterprise data is stored in relational databases and accessed via SQL queries. Many web services are little more than HTTP-based wrappers around such queries.

Unfortunately, transforming query results to JSON so it can be consumed by a client application often involves numerous inefficient steps, such as binding each row to a data object and loading the entire data set into memory before serializing it back to the caller. This type of approach has a negative impact on performance and scalabilty. Each row requires multiple heap allocations and constructor invocations, increasing latency and CPU load. Worse, the caller does not receive a response until the entire data set has been processed.

Further, since each response is loaded entirely into memory, high-volume applications require a large amount of RAM, and can only scale through the addition of more physical hardware. Eventually, the garbage collector has to run, slowing down the entire system.

A much more efficient approach is to stream response data. Instead of copying the query results into an in-memory data structure before sending the response, the web service can write a row of data to the output stream each time a row is read from the result set. This allows a client to begin receiving the data as soon as it is available, significantly reducing latency. Also, because no intermediate data structures are created, CPU and memory load is reduced, allowing each server to handle a higher number of concurrent requests. Finally, because fewer heap allocations are required, the garbage collector needs to run much less frequently, resulting in fewer system pauses.

Introducing HTTP-RPC

HTTP-RPC is an open-source framework for implementing REST 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 less than 70KB in size, making it an ideal choice for applications where a minimal footprint is desired.

WebService

HTTP-RPC’s WebService type provides an abstract base class for REST-based web services. It extends the similarly abstract HttpServlet class provided by the servlet API.

Service operations are defined by adding public methods to a concrete service implementation. Methods are invoked by submitting an HTTP request for a path associated with a servlet instance. Arguments are provided either via the query string or in the request body, like an HTML form. WebService converts the request parameters to the expected argument types, invokes the method, and writes the return value to the output stream as JSON.

The RequestMethod annotation is used to associate a service method with an HTTP verb such as GET or POST. The optional ResourcePath annotation can be used to associate the method with a specific path relative to the servlet. For example, the following class might be used to implement a web service that performs a simple addition operation:

@WebServlet(urlPatterns={"/math/*"})
public class MathServlet extends WebService {
    @RequestMethod("GET")
    @ResourcePath("/sum")
    public double getSum(double a, double b) {
        return a + b;
    }
}

The following request would cause the method to be invoked, and the service would return the value 6 in response:

GET /math/sum?a=2&b=4

JSONEncoder

The JSONEncoder class, which is used internally by WebService to serialize response data, converts return values to their JSON equivalents as follows:

  • CharSequence: string
  • Number: number
  • Boolean: true/false
  • Iterable: array
  • java.util.Map: object

Note that collection types are not required to support random access; iterability is sufficient. This is an important feature, as it allows service implementations to stream result data rather than buffering it in memory before it is written.

ResultSetAdapter

HTTP-RPC’s ResultSetAdapter class implements the Iterable interface and makes each row in a JDBC result set appear as an instance of Map, allowing query results to be efficiently serialized as an array of JSON objects.

For example, consider a web service that returns the result of a SQL query on this table, taken from the MySQL sample database:

CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);

A method to retrieve a list of all pets belonging to a given owner might be implemented as shown below. Note that the example uses HTTP-RPC’s Parameters class to simplify query execution using named parameters rather than positional values. Also note that the method uses JSONEncoder to explicitly write the results to the output stream rather than simply returning the adapter instance, to ensure that the underlying result set is closed and system resources are not leaked:

@RequestMethod("GET")
public void getPets(String owner) throws SQLException, IOException {
    try (Connection connection = DriverManager.getConnection(DB_URL)) {
        Parameters parameters = Parameters.parse("SELECT name, species, sex, birth FROM pet WHERE owner = :owner");

        HashMap<String, Object> arguments = new HashMap<>();

        arguments.put("owner", owner);

        try (PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
            parameters.apply(statement, arguments);

            try (ResultSet resultSet = statement.executeQuery()) {
                JSONEncoder jsonEncoder = new JSONEncoder();

                jsonEncoder.writeValue(new ResultSetAdapter(resultSet), getResponse().getOutputStream());
            }
        }
    } 
}

A response produced by the method might look something like this, where each object in the array represents a row from the result set:

[
  {
    "name": "Claws",
    "species": "cat",
    "sex": "m",
    "birth": 763880400000
  },
  {
    "name": "Chirpy",
    "species": "bird",
    "sex": "f",
    "birth": 905486400000
  },
  {
    "name": "Whistler",
    "species": "bird",
    "sex": null,
    "birth": 881643600000
  }
]

With just a few lines of code, query results can be quickly and efficiently returned to the caller, with no intermediate buffering required.

More Information

Complete source code for this example can be found here. For more information, see the project README.

Implementing Auto-Complete with UITextField

11/13/2018 Updated for Xcode 10/Swift 4.2

I recently wanted to add a Safari-like auto-complete feature to an iOS app I was working on. Specifically, I wanted the app to proactively suggest a complete word based on some initial characters entered by the user, similar to how Safari suggests URLs based on the first few letters in a web address:

As in Safari, tapping Return would allow the user to confirm the suggestion.

Since this is not a feature that Apple provides “out of the box”, I thought I would share the approach I took in case it is of use to anyone.

In this example, the text field will suggest values for the user’s favorite color:

As the user types, a list of options is consulted to determine which value to suggest:

Suggestions are defined as an array of strings:

let suggestions = [
    "red",
    "orange",
    "yellow",
    "green",
    "blue",
    "purple"
]

To handle user input, the view controller assigns itself as the text field’s delegate and implements the textField(_:shouldChangeCharactersIn:replacementString:) method, as shown below:

func textField(_ textField: UITextField, shouldChangeCharactersIn range: NSRange, replacementString string: String) -> Bool {
    return !autoCompleteText(in: textField, using: string, suggestions: suggestions)
}

This method simply delegates to the following method, which searches the suggestion list for the first entry with a prefix that matches the user’s input. It then updates the text value with the matching suggestion and selects the remaining characters in the text field:

func autoCompleteText(in textField: UITextField, using string: String, suggestions: [String]) -> Bool {
    if !string.isEmpty,
        let selectedTextRange = textField.selectedTextRange, selectedTextRange.end == textField.endOfDocument,
        let prefixRange = textField.textRange(from: textField.beginningOfDocument, to: selectedTextRange.start),
        let text = textField.text(in: prefixRange) {
        let prefix = text + string
        let matches = suggestions.filter { $0.hasPrefix(prefix) }

        if (matches.count > 0) {
            textField.text = matches[0]

            if let start = textField.position(from: textField.beginningOfDocument, offset: prefix.count) {
                textField.selectedTextRange = textField.textRange(from: start, to: textField.endOfDocument)

                return true
            }
        }
    }

    return false
}

The method returns true if a match was found and false otherwise. The delegate method returns the inverse of this value so the text field will continue to process keystrokes when a match is not found.

Finally, the controller implements the delegate’s textFieldShouldReturn(_:) method to “confirm” the suggestion:

func textFieldShouldReturn(_ textField: UITextField) -> Bool {
    textField.resignFirstResponder()

    return true
}

Note that the text field’s autocapitalizationType and autocorrectionType properties were set to .none and .no, respectively. Disabling auto-capitalization ensures that the lookup logic will correctly identify matches, since all of the suggestions begin with lowercase letters. Turning off auto-correction ensures that iOS’s built-in suggestion bar is not displayed, since suggestions will be made by the text field itself.

Complete source code for this example can be found here.

Creating a Universal Framework in Xcode 10

11/13/2018 Updated for Xcode 10/Swift 4.2

The following script can be used to create a universal iOS framework (i.e. one that will run in both the simulator as well as on an actual device). It should work with both Swift and Objective-C projects:

FRAMEWORK=

BUILD=build
FRAMEWORK_PATH=$FRAMEWORK.framework

# iOS
rm -Rf $FRAMEWORK-iOS/$BUILD
rm -f $FRAMEWORK-iOS.framework.tar.gz

xcodebuild archive -project $FRAMEWORK-iOS/$FRAMEWORK-iOS.xcodeproj -scheme $FRAMEWORK -sdk iphoneos SYMROOT=$BUILD
xcodebuild build -project $FRAMEWORK-iOS/$FRAMEWORK-iOS.xcodeproj -target $FRAMEWORK -sdk iphonesimulator SYMROOT=$BUILD

cp -RL $FRAMEWORK-iOS/$BUILD/Release-iphoneos $FRAMEWORK-iOS/$BUILD/Release-universal
cp -RL $FRAMEWORK-iOS/$BUILD/Release-iphonesimulator/$FRAMEWORK_PATH/Modules/$FRAMEWORK.swiftmodule/* $FRAMEWORK-iOS/$BUILD/Release-universal/$FRAMEWORK_PATH/Modules/$FRAMEWORK.swiftmodule

lipo -create $FRAMEWORK-iOS/$BUILD/Release-iphoneos/$FRAMEWORK_PATH/$FRAMEWORK $FRAMEWORK-iOS/$BUILD/Release-iphonesimulator/$FRAMEWORK_PATH/$FRAMEWORK -output $FRAMEWORK-iOS/$BUILD/Release-universal/$FRAMEWORK_PATH/$FRAMEWORK

tar -czv -C $FRAMEWORK-iOS/$BUILD/Release-universal -f $FRAMEWORK-iOS.tar.gz $FRAMEWORK_PATH $FRAMEWORK_PATH.dSYM

When located in the same directory as the .xcodeproj file, this script will invoke xcodebuild twice on a framework project and join the resulting binaries together into a single universal binary. It will then package the framework up in a gzipped tarball and place it in the same directory.

However, apps that contain “fat” binaries like this don’t pass app store validation. Before submitting an app containing a universal framework, the binaries need to be trimmed so that they include only iOS-native code. The following script can be used to do this:

FRAMEWORK=$1
echo "Trimming $FRAMEWORK..."

FRAMEWORK_EXECUTABLE_PATH="${BUILT_PRODUCTS_DIR}/${FRAMEWORKS_FOLDER_PATH}/$FRAMEWORK.framework/$FRAMEWORK"

EXTRACTED_ARCHS=()

for ARCH in $ARCHS
do
    echo "Extracting $ARCH..."
    lipo -extract "$ARCH" "$FRAMEWORK_EXECUTABLE_PATH" -o "$FRAMEWORK_EXECUTABLE_PATH-$ARCH"
    EXTRACTED_ARCHS+=("$FRAMEWORK_EXECUTABLE_PATH-$ARCH")
done

echo "Merging binaries..."
lipo -o "$FRAMEWORK_EXECUTABLE_PATH-merged" -create "${EXTRACTED_ARCHS[@]}"
rm "${EXTRACTED_ARCHS[@]}"

rm "$FRAMEWORK_EXECUTABLE_PATH"
mv "$FRAMEWORK_EXECUTABLE_PATH-merged" "$FRAMEWORK_EXECUTABLE_PATH"

echo "Done."

To use this script:

  1. Place the script in your project root directory and name it trim.sh or something similar
  2. Create a new “Run Script” build phase after the “Embed Frameworks” phase
  3. Rename the new build phase to “Trim Framework Executables” or similar (optional)
  4. Invoke the script for each framework you want to trim (e.g. ${SRCROOT}/trim.sh)

For more ways to simplify iOS app development, please see my projects on GitHub:

  • Lima – Declarative UI for iOS and tvOS
  • Kilo – Lightweight REST for iOS and tvOS

Dynamically Loading Table View Images in iOS

11/13/2018 Updated for Xcode 10/Swift 4.2

iOS applications often display thumbnail images in table views alongside other text-based content such as contact names or product descriptions. However, these images are not usually delivered with the initial response, but must instead be retrieved separately afterward. They are typically downloaded in the background as needed to avoid blocking the main thread, which would temporarily render the user interface unresponsive.

For example, consider this web service, which returns a list of simulated photo data:

[
  {
    "albumId": 1,
    "id": 1,
    "title": "accusamus beatae ad facilis cum similique qui sunt",
    "url": "http://placehold.it/600/92c952",
    "thumbnailUrl": "http://placehold.it/150/92c952"
  },
  {
    "albumId": 1,
    "id": 2,
    "title": "reprehenderit est deserunt velit ipsam",
    "url": "http://placehold.it/600/771796",
    "thumbnailUrl": "http://placehold.it/150/771796"
  },
  {
    "albumId": 1,
    "id": 3,
    "title": "officia porro iure quia iusto qui ipsa ut modi",
    "url": "http://placehold.it/600/24f355",
    "thumbnailUrl": "http://placehold.it/150/24f355"
  },
  ...
]

Each record contains a photo ID, album ID, and title, as well as URLs for both thumbnail and full-size images; for example:

View Controller

A basic user interface for displaying results returned by this service is shown below:

Row data is stored in an array of Photo instances:

struct Photo: Decodable {
    let id: Int
    let albumId: Int
    let title: String?
    var url: URL?
    var thumbnailUrl: URL?
}

Previously loaded thumbnail images are stored in a dictionary that associates UIImage instances with photo IDs:

class ViewController: UITableViewController {
    // Row data
    var photos: [Photo]?

    // Image cache
    var thumbnailImages: [Int: UIImage] = [:]

    ...    
}

The photo list is loaded the first time the view appears. The WebServiceProxy class provided by the open-source Kilo framework is used to retrieve the data:

override func viewWillAppear(_ animated: Bool) {
    super.viewWillAppear(animated)

    // Load photo data
    if (photos == nil) {
        let serviceProxy = WebServiceProxy(session: URLSession.shared, serverURL: URL(string: "https://jsonplaceholder.typicode.com")!)

        serviceProxy.invoke(.get, path: "/photos") { (result: [Photo]?, error: Error?) in
            self.photos = result ?? []

            self.tableView.reloadData()
        }
    }
}

Table view cells are represented by the following class, implemented using the open-source Lima layout framework:

class PhotoCell: LMTableViewCell {
    var thumbnailImageView: UIImageView!
    var titleLabel: UILabel!

    override init(style: UITableViewCell.CellStyle, reuseIdentifier: String?) {
        super.init(style: style, reuseIdentifier: reuseIdentifier)

        setContent(LMRowView(
            UIImageView(contentMode: .scaleAspectFit, width: 50, height: 50) { self.thumbnailImageView = $0 },
            LMSpacer(width: 0.5, backgroundColor: UIColor.lightGray),
            LMColumnView(spacing: 0,
                UILabel(font: UIFont.preferredFont(forTextStyle: .body), numberOfLines: 2) { self.titleLabel = $0 },
                LMSpacer()
            )
        ), ignoreMargins: false)
    }

    required init?(coder decoder: NSCoder) {
        return nil
    }
}

Cell content is generated as follows. The corresponding Photo instance is retrieved from the photos array and used to configure the cell. If the thumbnail image is already available in the cache, it is used to populate the cell’s thumbnail image view. Otherwise, it is loaded from the server and added to the cache. If the cell is still visible when the image request returns, it is updated immediately:

override func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
    return photos?.count ?? 0
}

override func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
    let photoCell = tableView.dequeueReusableCell(withIdentifier: PhotoCell.description(), for: indexPath) as! PhotoCell

    guard let photo = photos?[indexPath.row] else {
        fatalError()
    }

    // Attempt to load image from cache
    photoCell.thumbnailImageView.image = thumbnailImages[photo.id]

    if photoCell.thumbnailImageView.image == nil,
        let url = photo.thumbnailUrl,
        let scheme = url.scheme,
        let host = url.host,
        let serverURL = URL(string: String(format: "%@://%@", scheme, host)) {
        // Request image
        let serviceProxy = WebServiceProxy(session: URLSession.shared, serverURL: serverURL)

        serviceProxy.invoke(.get, path: url.path, responseHandler: { content, contentType in
            return UIImage(data: content)
        }) { (result: UIImage?, error: Error?) in
            // Add image to cache and update cell, if visible
            if let thumbnailImage = result {
                self.thumbnailImages[photo.id] = thumbnailImage

                if let cell = tableView.cellForRow(at: indexPath) as? PhotoCell {
                    cell.thumbnailImageView.image = thumbnailImage
                }
            }
        }
    }

    photoCell.titleLabel.text = photo.title

    return photoCell
}

Finally, if the system is running low on memory, the image cache is cleared:

override func didReceiveMemoryWarning() {
    super.didReceiveMemoryWarning()

    thumbnailImages.removeAll()
}

Summary

This article provided an overview of how images can be dynamically loaded to populate table view cells in iOS. Complete source code for this example can be found here.

Caching Web Service Response Data in iOS

11/13/2018 Updated for Xcode 10/Swift 4.2

Many iOS applications obtain data via web APIs that return JSON documents. For example, the following table view controller uses the Kilo WebServiceProxy class to invoke a simple web service that returns a simulated list of users as JSON. The controller requests the user list when the view first appears, and reloads the table view once the data has been retrieved:

class ViewController: UITableViewController {
    var users: [User]?

    override func viewWillAppear(_ animated: Bool) {
        super.viewWillAppear(animated)
    
        // Load user data
        if (users == nil) {
            let serviceProxy = WebServiceProxy(session: URLSession.shared, serverURL: URL(string: "https://jsonplaceholder.typicode.com")!)
    
            serviceProxy.invoke(.get, path: "/users") { (result: [User]?, error: Error?) in
                if (error == nil) {
                    self.users = result ?? []
    
                    self.tableView.reloadData()
                }        
            }
        }
    }
    
    ...
}

User records are represented by instances of the following structure:

struct User: Codable {
    struct Address: Codable {
        let street: String
        let suite: String
        let city: String
        let zipcode: String

        struct Geo: Codable {
            let lat: String
            let lng: String
        }

        let geo: Geo
    }

    struct Company: Codable {
        let name: String
        let catchPhrase: String
        let bs: String
    }

    let id: Int
    let name: String
    let username: String
    let email: String
    let address: Address
    let phone: String
    let website: String
    let company: Company
}

The results are shown below:

This works fine when both the device and the service are online, but it fails if either one is not. In some cases this may be acceptable, but other times it might be preferable to show the user the most recent response when more current data is not available.

To facilitate offline support, the response data must be cached. However, since writing to the file system is a potentially time-consuming operation, it should be done in the background to avoid blocking the main (UI) thread. Here, the data is written using an operation queue to ensure that access to it is serialized:

class ViewController: UITableViewController {
    var userCacheURL: URL?
    let userCacheQueue = OperationQueue()

    var users: [User]?

    override func viewDidLoad() {
        super.viewDidLoad()

        title = "Response Data Cache"

        tableView.estimatedRowHeight = 2
        tableView.register(UserCell.self, forCellReuseIdentifier: UserCell.description())

        if let cacheURL = FileManager.default.urls(for: .cachesDirectory, in: .userDomainMask).first {
            userCacheURL = cacheURL.appendingPathComponent("users.json")
        }
    }

    override func viewWillAppear(_ animated: Bool) {
        super.viewWillAppear(animated)

        // Load user data
        if (users == nil) {
            let serviceProxy = WebServiceProxy(session: URLSession.shared, serverURL: URL(string: "https://jsonplaceholder.typicode.com")!)

            serviceProxy.invoke(.get, path: "/users") { (result: [User]?, error: Error?) in
                if (error == nil) {
                    self.users = result ?? []

                    self.tableView.reloadData()

                    // Write the response to the cache
                    if let userCacheURL = self.userCacheURL {
                        self.userCacheQueue.addOperation() {
                            let jsonEncoder = JSONEncoder()

                            if let data = try? jsonEncoder.encode(self.users) {
                                try? data.write(to: userCacheURL)
                            }
                        }
                    }
                } else {
                    ...
                }
            }
        }
    }
    
    ...
}

Finally, the data can be retrieved from the cache if the web service call fails. The data is read from the cache in the background, and the UI is updated by reloading the table view on the main thread:

class ViewController: UITableViewController {
    ...

    override func viewWillAppear(_ animated: Bool) {
        super.viewWillAppear(animated)

        // Load user data
        if (users == nil) {
            let serviceProxy = WebServiceProxy(session: URLSession.shared, serverURL: URL(string: "https://jsonplaceholder.typicode.com")!)

            serviceProxy.invoke(.get, path: "/users") { (result: [User]?, error: Error?) in
                if (error == nil) {
                    ...
                } else {
                    // Read the data from the cache
                    if let userCacheURL = self.userCacheURL {
                        self.userCacheQueue.addOperation() {
                            let jsonDecoder = JSONDecoder()

                            if let data = try? Data(contentsOf: userCacheURL) {
                                self.users = (try? jsonDecoder.decode([User].self, from: data)) ?? []

                                // Update the UI
                                OperationQueue.main.addOperation() {
                                    self.tableView.reloadData()
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    
    ...
}

Now, as long as the application has been able to connect to the server at least once, it can function either online or offline, using the cached response data.

Complete source code for this example can be found here.

Applying Style Sheets Client-Side in iOS

11/13/2018 Updated for Xcode 10/Swift 4.2

While native mobile applications can often provide a more seamless and engaging user experience than a browser-based app, it is occasionally convenient to present certain types of content using a web view. Specifically, any content that is primarily text-based and requires minimal user interaction may be a good candidate for presentation as HTML; for example, product descriptions, user reviews, or instructional content.

However, browser-based content often tends to look out of place within a native app. For example, consider the following simple HTML document:



    
    


    

Lorem Ipsum

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
  • Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.
  • Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
  • Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Rendered by WKWebView, the result looks like this:

Because the text is displayed using the default browser font rather than the system font, it is immediately obvious that the content is not native. To make it appear more visually consistent with other elements of the user interface, a stylesheet could be used to render the document using the system font:


    ...
    
    body {
        font-family: '-apple-system';
        font-size: 10pt;
    }
    

The result is shown below. The styling of the text now matches the rest of the UI:

However, while this approach may work for this simple example, it does not scale well. Different app (or OS) versions may have different styling requirements.

By applying the stylesheet on the client, the presentation can be completely separated from the content. This can be accomplished by linking to the stylesheet rather than embedding it inline:


    ...
    

However, instead of downloading the stylesheet along with the HTML document, it is distributed with the application itself and applied using the load(_:mimeType:characterEncodingName:baseURL:) method of the WKWebView class. The first argument to this method contains the (unstlyed) HTML content, and the last contains the base URL against which relative URLs in the document (such as stylesheets) will be resolved:

class ViewController: UIViewController {
    var webView: WKWebView!

    override func loadView() {
        webView = WKWebView()

        view = webView
    }

    override func viewDidLoad() {
        super.viewDidLoad()

        title = "Client-Side CSS"

        if let url = Bundle.main.url(forResource: "example", withExtension: "html"),
            let data = try? Data(contentsOf: url),
            let resourceURL = Bundle.main.resourceURL {
            webView.load(data, mimeType: "text/html", characterEncodingName: "UTF-8", baseURL: resourceURL)
        }
    }
}

In this example, the document, example.html, is loaded from the main bundle. In a real application, it would most likely be loaded from an actual web server.

The stylesheet, example.css, is stored in the resource folder of the application’s main bundle:

body {
    font-family: '-apple-system';
    font-size: 10pt;
}

The results are identical to the previous example. However, the content and visual design are no longer tightly coupled and can vary independently:

Complete source code for this example can be found here.

Named Parameters in JDBC Queries

1/12/2019 Updated for HTTP-RPC 6.0

Prepared statements are a common way to execute parameterized queries in JDBC. For example, the following SQL might be used to retrieve a list of all users whose first or last name matches a particular character sequence:

SELECT * FROM user 
WHERE first_name LIKE ? or last_name LIKE ?

Parameter values are supplied at runtime via indexed setter methods defined by the PreparedStatement class:

statement.setString(1, pattern);
statement.setString(2, pattern);

This works fine for simple queries, but it becomes increasingly difficult to manage as the number of parameters grows. It is also redundant – although this query only requires a single argument, two parameter values must be supplied.

The Java Persistence API (JPA) provides a more convenient alternative using named parameters. For example, the above query might be written as follows in JPQL:

SELECT u FROM User u 
WHERE u.firstName LIKE :pattern or u.lastName LIKE :pattern

This is more readable and less verbose, as the caller only needs to provide the value of the “pattern” parameter once. It is also more resilient to changes, as the arguments are not dependent on ordinal position. Unfortunately, it requires a JPA-compliant object-relational mapping (ORM) framework such as Hibernate, a dependency that may not be satisfiable in some situations.

The Parameters class provided by the HTTP-RPC framework brings named parameter support to JDBC. The parse() method of this class is used to create a Parameters instance from a JPA-like SQL query; for example:

SELECT * FROM user 
WHERE first_name LIKE :pattern or last_name LIKE :pattern

It takes a string or reader containing the query text as an argument:

Parameters parameters = Parameters.parse(query);

The getSQL() method of the Parameters class returns the processed query in standard JDBC syntax. This value can be used in a call to the connection’s prepareStatement() method:

PreparedStatement statement = connection.prepareStatement(parameters.getSQL());

Argument values are specified via the apply() method, as shown below:

HashMap arguments = new HashMap();

arguments.put("pattern", pattern);
    
parameters.apply(statement, arguments);

Once applied, the query can be executed:

ResultSet resultSet = statement.executeQuery();

Note that the Parameters class is not limited to queries; it can also be used for updates.

A complete example can be found here. It is a simple REST service that allows a caller to search a database of pets by owner name.

See the HTTP-RPC README for more information.