java.sql.Sqlexception: Can not Issue data Manipulation statements with executeQuery()

Question

My key insertion is not being done. And the error that occurs 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";
}
}

Code Chavedao:

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

}

Answer 1

Diego Schmidt found another bug in his code in the comment he made, but it wasn’t his bug yet. After fixing the issue bug, your data would not be updated, or would give an error when entering null, or would give error by not finishing doing the bindings of PreparedStatement.

Your mistake is because you are using data update guidelines in your query. In this case, you are using insert. All data update guidelines (plus standards) are:

• insert
• update
• delete

That right there along with the DQL call forms the DML. Some prefer to say that DML would only be these updates, it will depend on the author/SGBD/documentation that is setting the terms.

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

The 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.

To be able to use the DML that is outside the DQL (let’s call this language DML DQL), you should use preparedStatement.executeUpdate.

Summary

• Listen to Diego, prepare before executing
• DQL => executeQuery
• DML DQL => executeUpdate
• more about these acronyms: What are the acronyms DDL, DML, DQL, DTL and DCL?

---

sidenotes

Another sublingual SQL is DDL, data Definition language. This is the part of SQL responsible for creating and changing tables and views. To run any DDL, you can’t even use the executeQuery nor the executeUpdate, but yes statement.execute. I don’t see much point in preparing a DDL either, so I put statement, nay preparedStatement.