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!

What's Valor_Atual || Valor_Antigo || nome)? Java variables? What are you trying to do? — Ori Marko
– Ori Marko, Commented Nov 21, 2018 at 17:02
Its the variables that im using in the Search box in the JFrame, I've added the respective aliases like nik said below — Eduardo Fernandes
– Eduardo Fernandes, Commented Nov 21, 2018 at 17:05
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) — Carlo Sirna
– Carlo Sirna, Commented Nov 22, 2018 at 6:32

Andreas · Accepted Answer · 2018-11-21 19:02:28Z

3

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
        }
    }
}

edited Nov 21, 2018 at 19:02

answered Nov 21, 2018 at 17:19

Andreas

160k13 gold badges164 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Poole Over a year ago

|| 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 Over a year ago

@AlexPoole Very true. Answer adjusted.

Eduardo Fernandes Over a year ago

Thank you so much for all the help, this oracle is killing me :p

Collectives™ on Stack Overflow

Oracle query in java

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related