Doubt with Preparedstatement with INNER JOIN in Java

Asked

Viewed 1,433 times

2

I have a question about using Preparedstatement with INNER JOIN, here’s my code:

public List<Emprestimo> pesquisar() throws SQLException {

    List<Emprestimo> listaEmprestimo = new ArrayList<Emprestimo>();

    String sql = "SELECT emp.cod_obra AS id_obra, ob.titulo, emp.dispo, emp.dataDevolu  "
            + "FROM emprestimo emp "
            + "INNER JOIN obra ob "
            + "ON ob.cod_obra = emp.cod_obra"
            + "ORDER BY id_obra";

    PreparedStatement ps = c.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();

    listaEmprestimo = new ArrayList<Emprestimo>();

    while (rs.next()) {
        Emprestimo emp = new Emprestimo();
        emp.getObra().setCodObra(rs.getInt("id"));
        emp.getObra().setTitulo(rs.getString("nome"));
        emp.setDisp(rs.getInt("status"));
        emp.setDataDevolucao(rs.getDate("dataDev"));
        listaEmprestimo.add(emp);
    }
    rs.close();
    ps.close();

    return listaEmprestimo;

}

However, when I compile the code this error is returned: inserir a descrição da imagem aqui

But when I do the query, it works. What goes wrong?

inserir a descrição da imagem aqui

java
  • 1

    You are not making space between things. Your query is being built like this: ... ON ob.cod_obra = emp.cod_obraORDER BY id_obra .... Missed space at the end of "ON ob.cod_obra = emp.cod_obra". - is the suggestion to print SQL on the screen when doing the debug of this kind of problem.

1 answer

11


Taking this snippet of your code exactly as it is:

String sql = "SELECT emp.cod_obra AS id_obra, ob.titulo, emp.dispo, emp.dataDevolu  "
        + "FROM emprestimo emp "
        + "INNER JOIN obra ob "
        + "ON ob.cod_obra = emp.cod_obra"
        + "ORDER BY id_obra";

We get a string like this (without line breaking, of course):

SELECT emp.cod_obra AS id_obra, ob.titulo, emp.dispo, emp.dataDevolu  FROM emprestimo emp
INNER JOIN obra ob ON ob.cod_obra = emp.cod_obraORDER BY id_obra";
                                                ↑
      Aqui está o problema, falta um espaço ─ ─ ┘

To correct the mentioned error, change this:

        + "ON ob.cod_obra = emp.cod_obra"

for this reason

        + "ON ob.cod_obra = emp.cod_obra "

This type of typing error can be detected with a simple SQL print generated on the screen.

Browser other questions tagged java

You are not signed in. Login or sign up in order to post.