SQL Query In Java + Oracle

Question

I'm really struggling to get this SQL into my head while using java.

My problem is: I want to use a variable in my sql Query, and i cant seem to get it working, it catches the correct value(i'm showing it on a label), But it doesnt show any records, yet, if i replace the variable for a '5', it shows me the correct record...

        try {
             Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = MySqlConnect.ConnectDb();


           int idaca = Integer.parseInt(idhist.getText());
  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 a.Id_Academista = "+idaca+" AND h.Id_Tecnologia = t.Id_Tecnologia
                        AND (h.Valor_Atual || h.Valor_Antigo  || t.nome)  LIKE '%" + ValToSearch + "%'";

            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query1);

            historico history;

            while (rs.next()) {
                history = new historico(rs.getString("Nome"), rs.getInt("Valor_Antigo"),
                        rs.getInt("Valor_Atual"), rs.getString("Nome"));
                historicoList.add(history);
            } //END WHILE
        } //END TRY
        catch (Exception e) {
            JOptionPane.showInputDialog(null, e);
        }//END CATCH

Thats my code so far... The ValToSearch is working fine, tho...

Thank you in advance! Cheers

*The ValToSearch is working fine, tho* - If your query works when you manually type `'%5%'`, it means that `ValToSearch` is not working. However the issue might be elsewhere in your code, it's hard to tell with just two lines of code — BackSlash, Nov 22 '18 at 11:41
Im having problems with "+idaca+", if i replace it with 5, it works fine.. :/ — Eduardo Fernandes, Nov 22 '18 at 11:42
please use some query builder eg https://stackoverflow.com/questions/5620985/is-there-any-good-dynamic-sql-builder-library-in-java — bato3, Nov 22 '18 at 11:42
@EduardoFernandes Try to print `query1` by adding `System.out.println("sql query --> "+query1);` — Jacob, Nov 22 '18 at 11:48

score 4 · Answer 1 · answered Nov 22 '18 at 11:42

4

Put an space before AND h.Id_Tecnologia. That should solve your problem.

answered Nov 22 '18 at 11:42

Rafael Palomino

318
2
14

2

The error message you get from the database would have probably pointed at that problem. Always look at error messages. Make sure your program prints them (not just `try/catch/ignore`). Make sure to include them in Stackoverflow questions. – Thilo Nov 22 '18 at 11:44
There is no error message, thats the problem, it doesnt return anything – Eduardo Fernandes Nov 22 '18 at 11:46
Edited answer, but still no results :/ – Eduardo Fernandes Nov 22 '18 at 11:49
do you use error handling like this https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlexception.html – bato3 Nov 22 '18 at 11:50
Follow @Jåcob advice: print the String `query1` to see what do you send to BD. Maybe the variables are not well initialized... Pd: Pretty weird way to catch an exception. Usually you use println() or a logger system (log4j, slf4j, etc) to show it in console. Pd2: I suggest you a guide for debugging if you are using the Eclipse IDE. Understanding it will help you to find errors or at least to show us to locate your real problem [How to debug](http://www.vogella.com/tutorials/EclipseDebugging/article.html) – Rafael Palomino Nov 23 '18 at 14:58

score 0 · Answer 2 · answered Nov 22 '18 at 11:48

0

You are not afraid that in ValToSearch you get something like ' OR 1 IN (DELETE * FROM Tecnologias )? Use parametr escaping or better some query builder

answered Nov 22 '18 at 11:48

bato3

2,695
1
18
26

SQL Query In Java + Oracle

2 Answers2