java.sql.SQLException: Can not issue data manipulation statements with executeQuery ()

2

My key insertion is not being done. And the error it gives in the console is:

java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:502)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2224)
    at dao.ChaveDAO.adicionar(ChaveDAO.java:27)
    at logicas.AddChave.executa(AddChave.java:26)
    at servlet.ControllerServlet.service(ControllerServlet.java:35)

A linha 27 de ChaveDAO:             ResultSet rs = p.executeQuery();
A linha 26 de AddChave:             dao.adicionar(chave);
A linha 35 de ControllerServlet:            String pagina = logica.executa(req, resp);

ControllerServlet Code:

@WebServlet("/sistema")

public class ControllerServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    String parametro = req.getParameter("logica");
    String nomeDaClasse = "logicas." + parametro;

    System.out.println("Lógica: " + parametro);
    System.out.println("Comando: " + nomeDaClasse);

    try {
        Class<?> classe = Class.forName(nomeDaClasse);
        Logica logica = (Logica) classe.newInstance();

        String pagina = logica.executa(req, resp);

        req.getRequestDispatcher(pagina).forward(req, resp);
    } catch (Exception e) {
        throw new ServletException("A lógica causou uma exceção", e);
    }
}

}

AddChave Code:

public class AddChave implements Logica {
public String executa(HttpServletRequest req, HttpServletResponse res) throws Exception {
    Chave chave = new Chave();
    ChaveDAO dao = new ChaveDAO();

    String idTexto = req.getParameter("id");

    String nome = req.getParameter("nome");
    // int numero = Integer.parseInt(req.getParameter("numero"));
    String numero = req.getParameter("numero");

    chave.setNome(nome);
    chave.setNumero(numero);

    if (idTexto == null || idTexto.isEmpty()) {
        dao.adicionar(chave);
    } else {
        chave.setId(Long.parseLong(idTexto));

        dao.alterar(chave);
    }

    List<Chave> chaves = dao.getLista();
    req.setAttribute("chaves", chave);
    return "sistema?logica=ListaChaves";
}
}

ODBC Keyword:

public class ChaveDAO {
private Connection con;

public ChaveDAO() {
    con = ConexaoBanco.getConnection();
}

// Método ADICIONA
public void adicionar(Chave c) {

    String query = "insert into chaves (nome, numero) values (?, ?);";

    try {
        PreparedStatement p = con.prepareStatement(query);
        ResultSet rs = p.executeQuery();

        p.setString(1, c.getNome());
        p.setString(2, c.getNumero());

        p.execute();
        p.close();
        System.out.println("Gravado!");
        con.close();

        // issso aqui manda pro BD
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

// Método LISTA
public List<Chave> getLista() {
    try {

        List<Chave> chaves = new ArrayList<Chave>();
        PreparedStatement stmt = con.prepareStatement("select * from chaves");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Chave chave = new Chave();
            chave.setId(rs.getLong("id"));
            chave.setNome(rs.getString("nome"));
            chave.setNumero(rs.getString("numero"));
            chaves.add(chave);
        }
        rs.close();
        stmt.close();
        return chaves;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

}
    
asked by anonymous 18.04.2018 / 22:28

1 answer

1

Diego Schmidt found another bug in his code in the comment he made, but it still was not his bug. After fixing the issue bug, your data would not be updated, or it would give an error when inserting nulls, or it would give an error for not finishing the bindings of PreparedStatement .

Your mistake was because you were using data update guidelines in your query. In this case, you are using insert . All (more defaults) data update guidelines are:

  • insert
  • update
  • delete

This together with the DQL call forms the DML. Some prefer to say that DML would only be these updates, it will depend on the author / DBMS / documentation that is defining the terms.

  
  • DQL = > data query language
  •   
  • DML = > data manipulation language
  •   

DQL would include only the data selection / query part; everything involving SELECT would be here. Then the part of FROM , JOINS and WHERE would be classified as DQL. The preparedStatement.executeQuery only accepts DQL.

In order to be able to use DML that is outside of DQL (let's call this language DML \ DQL), you should use preparedStatement.executeUpdate .

Summary

sidenotes

Another sublinguation of SQL is the DDL, data definition language . This is the SQL part responsible for creating and changing tables and views . To run any DDL, you can not use either executeQuery nor executeUpdate , but statement.execute . I also do not see much sense in preparing a DDL, so I put statement , not preparedStatement .

    
19.04.2018 / 00:22