Java sql with like

JDBC — LIKE Clause Example

This chapter provides an example on how to select records from a table using JDBC application. This would add additional conditions using LIKE clause while selecting records from the table. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.
• Your MySQL or whatever database you are using, is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.
• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to fetch records from a table which meet given condition. This Query makes use of LIKE clause to select records to select all the students whose first name starts with «za».
• Clean up the environment − try with resources automatically closes the resources.

Sample Code

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCExample < static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT id, first, last, age FROM Registration"; public static void main(String[] args) < // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement();) < System.out.println("Fetching records without condition. "); ResultSet rs = stmt.executeQuery(QUERY); while(rs.next())< //Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); >// Select all records having ID equal or greater than 101 System.out.println("Fetching records with condition. "); String sql = "SELECT id, first, last, age FROM Registration" + " WHERE first LIKE '%za%'"; rs = stmt.executeQuery(sql); while(rs.next()) < //Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); >rs.close(); > catch (SQLException e) < e.printStackTrace(); >> >

C:\>java JDBCExample Fetching records without condition. ID: 100, Age: 30, First: Zara, Last: Ali ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal Fetching records with condition. ID: 100, Age: 30, First: Zara, Last: Ali ID: 102, Age: 30, First: Zaid, Last: Khan C:\>

Источник

How to implement a sql like ‘like’ operator in java?

The SQL ‘LIKE’ operator is used to match a pattern with a string. In Java, you can implement this operator by using various methods, including regular expressions, string manipulation, and other libraries. Here are a few methods to achieve this:

Method 1: Using Java’s built-in String ‘matches’ method

To implement a SQL like ‘LIKE’ operator in Java using Java’s built-in String ‘matches’ method, you can use regular expressions. The ‘matches’ method checks if a string matches a regular expression. Here’s how you can do it:

Define the regular expression pattern. The pattern should include the ‘%’ wildcard character to match any number of characters.

String pattern = ".*searchString.*";

String text = "hello world"; boolean matches = text.matches(pattern);

String[] strings = "hello world", "goodbye", "hello there">; for (String s : strings)  if (s.matches(pattern))  System.out.println(s); > >

That’s it! With these steps, you can implement a SQL like ‘LIKE’ operator in Java using Java’s built-in String ‘matches’ method.

Method 2: Using the Apache Commons Lang ‘StringUtils’ library

To implement a SQL like ‘LIKE’ operator in Java using the Apache Commons Lang ‘StringUtils’ library, you can use the containsIgnoreCase method. This method checks if a String contains a specified substring while ignoring the case of the characters. Here’s an example code snippet that demonstrates how to use this method:

import org.apache.commons.lang3.StringUtils; public class LikeOperatorExample  public static void main(String[] args)  String str = "Hello World"; String substr = "world"; boolean result = StringUtils.containsIgnoreCase(str, substr); System.out.println(result); // Output: true > >

In this example, we first import the StringUtils class from the Apache Commons Lang library. We then create a String variable str with the value «Hello World» and another String variable substr with the value «world». We then use the containsIgnoreCase method to check if the str variable contains the substr variable while ignoring the case of the characters. The result is stored in a boolean variable result . Finally, we print out the value of the result variable, which is true in this case.

You can also use the contains method if you want to perform a case-sensitive search. Here’s an example code snippet that demonstrates how to use this method:

import org.apache.commons.lang3.StringUtils; public class LikeOperatorExample  public static void main(String[] args)  String str = "Hello World"; String substr = "world"; boolean result = StringUtils.contains(str, substr); System.out.println(result); // Output: false > >

In this example, we use the contains method instead of the containsIgnoreCase method. As a result, the search is case-sensitive, and the value of the result variable is false .

In summary, you can use the containsIgnoreCase or contains method from the Apache Commons Lang StringUtils library to implement a SQL like ‘LIKE’ operator in Java. The containsIgnoreCase method performs a case-insensitive search, while the contains method performs a case-sensitive search.

Method 3: Using Java’s built-in ‘Pattern’ and ‘Matcher’ classes

To implement a SQL like ‘LIKE’ operator in Java using Java’s built-in ‘Pattern’ and ‘Matcher’ classes, you can follow these steps:

1. Create a regular expression pattern to match the desired string pattern. For example, if you want to match any string that contains the word «hello», you can use the pattern «.hello.«.
2. Compile the pattern using the Pattern class. This will create a Pattern object that you can use to match against strings.
3. Use the Matcher class to match the compiled pattern against a string. The Matcher class provides methods to check if the pattern matches the entire string or just a part of it.

Here’s an example code snippet that demonstrates how to use these classes to implement a ‘LIKE’ operator in Java:

import java.util.regex.*; public class LikeOperator  public static void main(String[] args)  String pattern = ".*hello.*"; String input = "hello world"; Pattern p = Pattern.compile(pattern); Matcher m = p.matcher(input); if (m.matches())  System.out.println("Input matches the pattern"); > else  System.out.println("Input does not match the pattern"); > > >

In this example, the pattern «.hello.» matches any string that contains the word «hello». The input string «hello world» matches this pattern, so the output of the program will be «Input matches the pattern».

You can modify the pattern to match different string patterns, such as matching strings that start with a certain prefix or end with a certain suffix. The Pattern and Matcher classes provide many methods to customize the pattern matching behavior, such as ignoring case or matching only a certain number of characters.

Method 4: Using the Java Guava library’s ‘CharMatcher’ class

To implement a SQL like ‘LIKE’ operator in Java using the Java Guava library’s ‘CharMatcher’ class, follow these steps:

import com.google.common.base.CharMatcher;

String input = "Hello, world!"; String pattern = "%world%";

String regex = pattern .replaceAll("%", ".*") .replaceAll("_", ".");

boolean isMatch = CharMatcher .forRegex(regex) .matchesAnyOf(input);

Here is the complete code example:

import com.google.common.base.CharMatcher; public class LikeOperatorExample  public static void main(String[] args)  String input = "Hello, world!"; String pattern = "%world%"; String regex = pattern .replaceAll("%", ".*") .replaceAll("_", "."); boolean isMatch = CharMatcher .forRegex(regex) .matchesAnyOf(input); System.out.println(isMatch); > >

This code will output true , indicating that the input string matches the SQL pattern.

Источник

LIKE predicate

LIKE predicates are popular for simple wildcard-enabled pattern matching. Supported wildcards in all SQL databases are:

With jOOQ, the LIKE predicate can be created from any column expression as such:

TITLE LIKE '%abc%' TITLE NOT LIKE '%abc%'

BOOK.TITLE.like("%abc%") BOOK.TITLE.notLike("%abc%")

Concatenating wildcards

A common practice is to conatenate wildcards to the actual expression. While concatenation is dangerous in plain SQL, it is safe when creating dynamic bind values using the DSL API:

-- Generated SQL is using a bind variable TITLE LIKE '%abc%' TITLE NOT LIKE '%abc%'

// abc might be user input BOOK.TITLE.like("%" + abc "%") BOOK.TITLE.notLike("%" + abc + "%")

Escaping operands with the LIKE predicate

Often, your pattern may contain any of the wildcard characters «_» and «%» , in case of which you may want to escape them. jOOQ does not automatically escape patterns in like() and notLike() methods. Instead, you can explicitly define an escape character as such:

TITLE LIKE '%The !%-Sign Book%' ESCAPE '!' TITLE NOT LIKE '%The !%-Sign Book%' ESCAPE '!'

BOOK.TITLE.like("%The !%-Sign Book%", '!') BOOK.TITLE.notLike("%The !%-Sign Book%", '!')

In the above predicate expressions, the exclamation mark character is passed as the escape character to escape wildcard characters «!_» and «!%» , as well as to escape the escape character itself: «!!»

Please refer to your database manual for more details about escaping patterns with the LIKE predicate.

jOOQ’s convenience methods using the LIKE predicate

jOOQ also provides a few convenience methods for common operations performed on strings using the LIKE predicate. Typical operations are «contains predicates», «starts with predicates», «ends with predicates», etc.

-- case insensitivity LOWER(TITLE) LIKE LOWER('%abc%') LOWER(TITLE) NOT LIKE LOWER('%abc%') -- contains and similar methods TITLE LIKE '%' || 'abc' || '%' TITLE LIKE 'abc' || '%' TITLE LIKE '%' || 'abc'

// case insensitivity BOOK.TITLE.likeIgnoreCase("%abc%") BOOK.TITLE.notLikeIgnoreCase("%abc%") // contains and similar methods BOOK.TITLE.contains("abc") BOOK.TITLE.startsWith("abc") BOOK.TITLE.endsWith("abc")

Note, that jOOQ escapes % and _ characters in values in some of the above predicate implementations. For simplicity, this has been omitted in this manual.

Quantified LIKE predicate

In addition to the above, jOOQ also provides the synthetic [NOT] LIKE ANY and [NOT] LIKE ALL operators, which can be used to (positively resp. negatively) match a string against multiple patterns without having to manually string together multiple [NOT] LIKE predicates with AND or OR (learn about other synthetic sql syntaxes). The following examples show how these synthetic predicates translate to SQL:

(TITLE LIKE '%abc%' OR TITLE LIKE '%def%') (TITLE NOT LIKE '%abc%' OR TITLE NOT LIKE '%def%') (TITLE LIKE '%abc%' AND TITLE LIKE '%def%') (TITLE NOT LIKE '%abc%' AND TITLE NOT LIKE '%def%')

BOOK.TITLE.like(any("%abc%", "%def%")) BOOK.TITLE.notLike(any("%abc%", "%def%")) BOOK.TITLE.like(all("%abc%", "%def%")) BOOK.TITLE.notLike(all("%abc%", "%def%"))

All corresponding Java methods Field.like(QuantifiedSelect) and Field.notLike(QuantifiedSelect) return an instance of LikeEscapeStep, which can be used to specify an ESCAPE clause that will be applied to all patterns in the list. For brevity the examples above don’t show this.

Note that both the LIKE ANY and LIKE ALL predicates allow matching a string against an empty list of patterns. For example, in the case of LIKE ANY this is equivalent to a 1 = 0 predicate and in the case of NOT LIKE ALL this behaves like 1 = 1 .

Dialect support

Translates to the following dialect specific expressions:

-- All dialects BOOK.TITLE LIKE '%abc%'

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

Источник