DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Why Database Migrations Take Months and How to Speed Them Up
  • Unmasking Entity-Based Data Masking: Best Practices 2025
  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems

Trending

  • A Complete Guide to Modern AI Developer Tools
  • Start Coding With Google Cloud Workstations
  • Is Agile Right for Every Project? When To Use It and When To Avoid It
  • Automatic Code Transformation With OpenRewrite
  1. DZone
  2. Data Engineering
  3. Databases
  4. Named Parameters in JDBC Queries

Named Parameters in JDBC Queries

A simple guide for how and when to use the Parameters class in your JDBC queries.

By 
Greg Brown user avatar
Greg Brown
·
Aug. 11, 16 · Tutorial
Likes (4)
Comment
Save
Tweet
Share
34.3K Views

Join the DZone community and get the full member experience.

Join For Free

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 toConnection#prepareStatement():

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


Parameter values are specified via a Map passed to theParameters#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.

Database

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

Opinions expressed by DZone contributors are their own.

Related

  • Why Database Migrations Take Months and How to Speed Them Up
  • Unmasking Entity-Based Data Masking: Best Practices 2025
  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!