CODEWITHSUNDEEP
javasqloracle-database
Eduardo Fernandes
Eduardo Fernandes

Reputation: 3

Oracle query in java

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!

Upvotes: 0

Views: 204

Answers (1)

Andreas
Andreas

Reputation: 159215

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

Upvotes: 3

Related Questions