Java中使用LIKE关键字查询Oracle数据库及MySQL代码适配修改

Great question! Let's break down the key changes you need to make, plus a critical best practice you shouldn't skip when working with user input.

1. Fix the string concatenation operator #

In MySQL, you can get away with using + to join strings (though CONCAT() is the better practice there), but Oracle treats + strictly as an arithmetic operator. If you try to use + with string values in Oracle, you’ll hit an error—it’ll try to convert your book title text to numbers, which obviously won’t work.

Oracle uses the || operator for string concatenation instead. Your original query line:

String query = "select * from books where BookName LIKE \"%\" + txt1.getText() + \"%\"";

Would need this quick syntax tweak if you were sticking to inline string building (though keep reading—you shouldn’t do this):

// Not recommended for production!
String query = "select * from books where BookName LIKE \"%\" || '" + txt1.getText() + "' || \"%\"";

I also added single quotes around the user input—Oracle enforces single quotes for string literals, whereas MySQL is more lenient about this.

2. Ditch direct string concatenation (security first!) #

Let’s be clear: splicing user input directly into your query is a massive security risk (it opens you up to SQL injection attacks). This is bad practice for any database, but it’s extra important to fix as you switch to Oracle.

Instead, use a PreparedStatement to safely pass parameters. This approach works for both MySQL and Oracle, avoids syntax headaches, and keeps your code secure:

String query = "select * from books where BookName LIKE ?";
PreparedStatement pstmt = connection.prepareStatement(query);
// Wrap the user input with % wildcards and pass as a parameter
String searchTerm = "%" + txt1.getText() + "%";
pstmt.setString(1, searchTerm);
ResultSet rs = pstmt.executeQuery();

Quick recap of key takeaways #

• Replace + with || if you ever need to concatenate strings directly in Oracle SQL (but avoid this with user input)
• Always use PreparedStatement for queries involving user input—this eliminates SQL injection risks and handles string literal formatting automatically
• Oracle requires string literals to be wrapped in single quotes, but prepared statements remove the need to manage this manually

内容的提问来源于stack exchange，提问作者Nikunjo Nil Shuvo