Oracle query in java

Question

I'm having some trouble using Oracle, since I was used to MySql syntax,

I'm trying to implement a query in my java program, but I keep getting the error: ora-0933 sql command not properly ended.

My Query is:

String query1 = "SELECT t.nome, h.Valor_Atual, h.Valor_Antigo, a.nome 
 FROM Tecnologias t, Historico h, Academista a
 WHERE h.Id_Academista = a.Id_Academista 
 AND h.Id_Tecnologia = t.Id_Tecnologia 
 AND (Valor_Atual || Valor_Antigo || nome) 
 LIKE '%" +ValToSearch + "%'";

Am I doing something wrong or is it Oracle syntax?

Thank you so much!

• Ori Marko: What's Valor_Atual || Valor_Antigo || nome)? Java variables? What are you trying to do?
• Eduardo Fernandes: Its the variables that im using in the Search box in the JFrame, I've added the respective aliases like nik said below
• Carlo Sirna: As Andreas has already pointed out in his answer: you should use "bind variables" (that is: the "?" markers you see in his solution) for value parameters: you should not try to convert user input into SQL literal strings: you are giving your user the possibility of generating invalid SQL queries. If a user tries to search the string "Guns'n'Roses" those ' characters in the middle of the string would break the sql syntax... And you get into similar troubles also with dates (not all countries write dates the same way) and numbers (not all countries use the "." char as decimal separator)

Answer 1

Although (Valor_Atual || Valor_Antigo || nome) LIKE '%" +ValToSearch + "%' is valid SQL syntax, it might match incorrectly, if the value to search happens to match a cross-over from value of one column to the next. So, you need to use OR, and you need to check columns separately.

Other issues:

• Use JOIN syntax
• Use PreparedStatement instead of string concatenation
• Use try-with-resources (assuming you're not)

That means your code should be like this:

String sql = "SELECT t.nome, h.Valor_Atual, h.Valor_Antigo, a.nome" +
 " FROM Historico h" +
 " JOIN Academista a ON a.Id_Academista = h.Id_Academista" +
 " JOIN Tecnologias t ON t.Id_Tecnologia = h.Id_Tecnologia" +
 " WHERE h.Valor_Atual LIKE ?" +
 " OR h.Valor_Antigo LIKE ?" +
 " OR a.nome LIKE ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
 stmt.setString(1, "%" + ValToSearch + "%");
 stmt.setString(2, "%" + ValToSearch + "%");
 stmt.setString(3, "%" + ValToSearch + "%");
 try (ResultSet rs = stmt.executeQuery()) {
 while (rs.next()) {
 // code here
 }
 }
}

• Alex Poole: || is the string concatenation operator in Oracle, so what the OP has would probably sort of work - checking if the search value appears anywhere in the three columns concatenated together. It's valid, anyway. But what you've done is safer and more appropriate, of course.
• Andreas: @AlexPoole Very true. Answer adjusted.
• Eduardo Fernandes: Thank you so much for all the help, this oracle is killing me :p