Java sql query named parameters

Named Parameters in JDBC Queries

Join the DZone community and get the full member experience.

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 all situations.

The org.httprpc.sql.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(sqlReader);

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

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

Parameter values are specified via a Map passed to the Parameters#apply() method ( mapOf() is a convenience method provided by HTTP-RPC for simplifying map creation):

parameters.apply(statement, mapOf(entry("pattern", pattern)));

Once applied, the query can be executed:

ResultSet resultSet = statement.executeQuery();

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

A complete example using the Parameters class 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 project README for more information.

Published at DZone with permission of Greg Brown , DZone MVB . See the original article here.

Opinions expressed by DZone contributors are their own.

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Provides named query parameter capability to JDBC PreparedStatements

License

axiom-data-science/jdbc-named-parameters

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Provides a NamedParameterPreparedStatement class which implements PreparedStatement and allows the use of named parameters in JDBC prepared SQL statements.

Credit to Adam Crume for his implementation of the SQL query parser:

 com.axiomalaska jdbc-named-parameters 1.1  

Then use in place of PreparedStatement, e.g.

PreparedStatement stat = NamedParameterPreparedStatement.createNamedParameterPreparedStatement( connection, "SELECT id FROM some_table WHERE name = :name"); 

Usually this library should be used internally by applications to parse queries containing named parameters. The jar is also executable and can be used to view the parsed results of a query containing named parameters.

Execute the jar and pass the path to a query file as the only argument:

java -jar jdbc-named-parameters.jar /home/you/test_query.sql 

About

Provides named query parameter capability to JDBC PreparedStatements

Источник