Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

I was a bit stumped by this one not long ago so here is an example of correctly binding a value to a LIKE clause inside a SQL statement with Java.

First, however, lets see the incorrect way that does not work...If you define your SQL statement to have the bind value and a LIKE wildcard, the JDBC engine will not be able to process it.
 Incorrect - Java SQL String
String sql = "select * from table where my_column like ':1%'";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, "My Value");


The above code throws this kind of exception...
 SQL Exception
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5331)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5319)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:282)
at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:910)


The correct way to do it is to treat the bind variable for the LIKE statement just as you would any other bind variable. The code then looks like this...
 Correct - Java SQL String
String sql = "select * from table where my_column like :1";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, "My Value%");




Note that the SQL string itself does not have the LIKE wildcard. The wildcard is defined when you bind the variable using the setString() method instead.

-i

Please leave your comments or feedback below!
comments powered by Disqus
Other posts you may like...
Hi! You can search my blog here ⤵
Or browse the recent top tags...

Recent Blog Posts

Coherence and weblogic.xml in different types of J2EE web app deployments

Atari Lynx repair - Part 5 - McWill LED screen mod installation

Atari Lynx repair - Part 4 - screen cover replacement

Atari Lynx repair - Part 3 - broken speaker replacement

Atari Lynx repair - Part 2 - re-capping the motherboard

Atari Lynx repair - Part 1 - introduction and case disassembly

jPhotoFrame updated to version 0.3.1 with an image rotation correction utility

iOS 11 pre-GM mini review before it gets revealed next week

Why you should never use Java enums as keys with Oracle Coherence caches

Hacking the VideoPak 7inch promotional brochure to work as a digital photo frame

Recent Galleries

Atari Lynx repair - Part 5 - McWill LED screen mod installation

Atari Lynx repair - Part 4 - screen cover replacement

Atari Lynx repair - Part 2 - re-capping the motherboard

Atari Lynx repair - Part 3 - broken speaker replacement

Atari Lynx repair - Part 1 - introduction and case disassembly

Building a custom Atari Lynx game box storage shelf unit in a day

Protecting old Atari Lynx game boxes with snug fit plastic sleeves

Monument Valley 2 is released and does not disappoint

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

Blogs and Friends

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity